6 years ago (2015-04-08)  Software development |   First to comment  10 
post score 0 times, average 0.0

4.1 Creating a Database

Note: Connect to the Mysql server command before creating the database: create database <database name>

Example 1: Create a Database named xhkdb

   Mysql> create database xhkdb;

Example 2: Creating a Database and Assigning Users

1CREATE DATABASE database name; 2GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON database name. * TO database name @ localhost IDENTIFIED BY 'password'; 3SET PASSWORD FOR 'database name' @ 'localhost' = OLD_PASSWORD (' Password '); execute three commands in order to complete database creation.Note: The Chinese "password" and "database" are set by the user himself.

4.2 Display Database

Command: show databases (Note: the last s) mysql> show databases; Note: In order to no longer display garbled, you must modify the database default encoding.The following uses the GBK encoding page as an example to illustrate:

1, modify the MYSQL configuration file:

Modify the default-character-set=gbk in my.ini

2, code runtime modification:

   1Java code: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=gbk 2PHP code: header("Content-Type:text/html;charset=gb2312"); 3C language code: int mysql_set_character_set( MYSQL * mysql , char * csname); This function is used to set the default character set for the current connection.The string csname specifies a valid character set name.Connection proofing becomes the default proofreader for character sets.This function works like the SET NAMES statement, but it also sets the value of mysql->charset, which affects the character set set by mysql_real_escape_string().

4.3 Delete Database

Command: drop database <database name>

For example: delete the database named xhkdb

Mysql> drop database xhkdb;

Example 1: Deleting an Existing Database

   Mysql> drop database drop_database; Query OK, 0 rows affected (0.00 sec)

Example 2: Delete an Uncertain Database

   Mysql> drop database drop_database; ERROR 1008 (HY000): Can't drop database 'drop_database'; database doesn't exist // An error occurred and the 'drop_database' database could not be deleted. The database does not exist.
   Mysql> drop database if exists drop_database; Query OK, 0 rows affected, 1 warning (0.00 sec) //Generate a warning that this database does not exist mysql> create database drop_database; Query OK, 1 row affected (0.00 sec) mysql> drop Database if exists drop_database;//if exists to determine if the database exists, does not exist or does not generate an error Query OK, 0 rows affected (0.00 sec)

4.4 Connect to the database

Command: use <database name> For example: If the xhkdb database exists, try to access it: mysql> use xhkdb; Screen tip: The Database changeduse statement can tell MySQL to use the db_name database as the default (current) database for subsequent statements.The database remains the default database until the end of the segment, or until a different USE statement is issued: mysql> USE db1; mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable mysql> USE db2; mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable Uses the USE statement to mark a specific current database and does not prevent you from accessing tables in other databases.The following example accesses the author table from the db1 database and accesses the edit table from the db2 database: mysql> USE db1; mysql> SELECT author_name, editor_name FROM author, db2.editor -> WHERE author.editor_id = db2.editor.editor_id;USE Statements were set up to be compatible with Sybase.
Some netizens have asked how to withdraw after connecting.In fact, do not quit, use database, use show databases will be able to query all databases, if you want to jump to other databases, use other database names on it.

4.5 currently selected database

Command: mysql> select database(); The SELECT command in MySQL is similar to print or write in other programming languages. You can use it to display the results of a string, number, mathematical expression, and so on.How to use the special function of SELECT command in MySQL?

1 shows the version of MYSQL

Mysql> select version(); +-----------------------+ | version() | +------------ -----------+ | 6.0.4-alpha-community | +-----------------------+ 1 row in set (0.02 sec)

2. Show current time

Mysql> select now(); +---------------------+ | now() | +-------------- -------+ | 2009-09-15 22:35:32 | +---------------------+ 1 row in set (0.04 sec )

3. Display year, month, day

SELECT DAYOFMONTH(CURRENT_DATE); +--------------------------+ | DAYOFMONTH(CURRENT_DATE) | +--------- -----------------+ | 15 | +--------------------------+ 1 Row in set (0.01 sec) SELECT MONTH(CURRENT_DATE); +---------------------+ | MONTH(CURRENT_DATE) | +------- --------------+ | 9 | +---------------------+ 1 row in set (0.00 sec) SELECT YEAR(CURRENT_DATE); +--------------------+ | YEAR(CURRENT_DATE) | +---------------- ----+ | 2009 | +--------------------+ 1 row in set (0.00 sec)

4. Display string

Mysql> SELECT "welecome to my blog!"; +----------------------+ | welecome to my blog! | +------- ---------------+ | welecome to my blog! | +----------------------+ 1 row in Set (0.00 sec)

5. When the calculator is used

Select ((4 * 4) / 10 ) + 25; +----------------------+ | ((4 * 4) / 10 ) + 25 | +----------------------+ | 26.60 | +---------------------- + 1 row in set (0.00 sec)

6. String concatenation

Select CONCAT(f_name, " ", l_name) AS Name from employee_data where title = 'Marketing Executive'; +---------------+ | Name | +------- --------+ | Monica Sehgal | | Hal Simlai | | Joseph Irvine | +---------------+ 3 rows in set (0.00 sec) NOTE: To CONCAT () function, used to concatenate strings.In addition, we used the previously learned AS to give a pseudonym for the result column 'CONCAT(f_name, "", l_name)'.




转载注明原文出处:Baiyuan's Blog>>https://wangbaiyuan.cn/en/creates-deletes-and-displays-the-mysql-database-version-2.html

Post comment


No Comment


Forget password?