MySQL Basic operation

1. Login and exit

(1) Login

Mysql–u User name –p password

This command password is clear text

Mysql–u User name –p

Enter Password

Log in at the same time indicate the host number:

Mysql–h Host name –u user name –p password

Specify port number while logging in

Mysql–h Host name –u user name –p password –p port number (3306)

While logging in, modify the command prompt:

Mysql–h Host name –u user name –p password –p port number (3306)--prompt=

The command prompt includes:

\u Current user Name

\h Current host Name

\d Current Date Time

\d the currently open database

You can also modify the command prompt after logging in:

Promptkaikeba>

Command prompt Change to kaikeba>

Change the command delimiter while logging in:

mysql–h hostname –u User name –p password –p port number (3306)--prompt=--delimiter=

You can also change the command delimiter after logging in

Delimiter @

The command terminator changes to: @

Open the database while logged in:

Mysql-u User name-p password-H host name-D database name

Eg:

Mysql-uroot-proot-hlocalhost-dtest;

(2) Exit

Exit

\q

Quit

CTRL + C shortcut keys

(3) SQL specification

Database name, table name lowercase, keyword, reserved word, function name uppercase;

The SQL command supports wrap operations, but cannot write words, names, or pairs of quotation marks

When a name conflicts with a reserved word, you need to enclose the name

(4) Turn on the output log form

\ t file save location and file name

\ t End output log

Press the up and down keys to investigate previously written commands

2. DDL operations

(1) Create a database

create{database| SCHEMA} db_name;

create{database| SCHEMA} [IF not EXISTS] db_name;

create{database| SCHEMA} [IF not EXISTS] db_name [[DEFAULT] CHARACTER Set [=] Character Set];

(2) View existing databases under current server

show{databases| SCHEMA};

(3) View the warnings from the previous action

SHOW WARNINGS

(4) View the encoding of the created database

SHOW CreateDatabase db_name;

Comprehensive Case 1:

Mysql> CreateDatabase Randongmei;

Query OK, 1 rowaffected (0.00 sec)

Mysql> showdatabases;

+--------------------+

| Database |

+--------------------+

|information_schema |

| MySQL |

|performance_schema |

|randongmei |

| Test |

+--------------------+

5 rows in Set (0.00 sec)

Mysql> Createschema King;

Query OK, 1 rowaffected (0.00 sec)

Mysql> Showschemas;

+--------------------+

| Database |

+--------------------+

|information_schema |

| King |

| MySQL |

| performance_schema|

|randongmei |

| Test |

+--------------------+

6 rows in Set (0.00 sec)

Mysql> Createschema King;

ERROR 1007 (HY000): Can ' t create database ' King '; Database exists

Mysql> Createschema IF not EXISTS king;

Query OK, 1 rowaffected, 1 Warning (0.00 sec)

Mysql> showwarnings;

+-------+------+-----------------------------------------------+

| Level | Code | Message |

+-------+------+-----------------------------------------------+

| Note | 1007 | Can ' t create database ' King ';d atabase exists |

+-------+------+-----------------------------------------------+

1 row in Set (0.00 sec)

Mysql> showdatabases;

+--------------------+

| Database |

+--------------------+

| information_schema|

| King |

| MySQL |

|performance_schema |

|randongmei |

| Test |

+--------------------+

6 rows in Set (0.00 sec)

mysql> showcreate DATABASE Randongmei;

+------------+---------------------------------------------------------------------+

| Database | Create Database |

+------------+---------------------------------------------------------------------+

| Randongmei | CREATE DATABASE ' Randongmei '/*!40100 DEFAULT CHARACTER SET UTF8 */| |

+------------+---------------------------------------------------------------------+

1 row in Set (0.00 sec)

mysql> createdatabase IF not EXISTS test1 DEFAULT CHARACTER SET = ' GBK ';

Query OK, 1 rowaffected (0.00 sec)

mysql> showcreate DATABASE test1

;

+----------+---------------------------------------------------------------+

| Database | Create Database |

+----------+---------------------------------------------------------------+

| Test1 | CREATE DATABASE ' test1 '/*!40100 defaultcharacter SET GBK */|

+----------+---------------------------------------------------------------+

1 row in Set (0.00 sec)

Mysql> Createschema IF not EXISTS test3 CHARACTER SET ' GBK ';

Query OK, 1 rowaffected (0.00 sec)

mysql> \ t

(5) Modify the encoding method of the database

alter{database| SCHEMA} db_name [[DEFAULT] CHARACTER Set [=] Character Set];

Mysql> showdatabases;

+--------------------+

| Database |

+--------------------+

|information_schema |

| King |

| MySQL |

|performance_schema |

|randongmei |

| Test |

| Test1 |

| Test3 |

+--------------------+

8 rows in Set (0.02 sec)

mysql> showcreate DATABASE test1;

+----------+---------------------------------------------------------------+

| Database | Create Database |

+----------+---------------------------------------------------------------+

| Test1 | CREATE DATABASE ' test1 '/*!40100 defaultcharacter SET GBK */|

+----------+---------------------------------------------------------------+

1 row in Set (0.00 sec)

mysql> alterdatabase test1 DEFAULT CHARACTER SET ' UTF8 ';

Query OK, 1 rowaffected (0.00 sec)

mysql> showcreate DATABASE test1;

+----------+----------------------------------------------------------------+

| Database | Create Database |

+----------+----------------------------------------------------------------+

| Test1 | CREATE DATABASE ' test1 '/*!40100 DEFAULT CHARACTER SET UTF8 */| |

+----------+----------------------------------------------------------------+

1 row in Set (0.00 sec)

mysql> showcreate SCHEMA test3;

+----------+---------------------------------------------------------------+

| Database | Create Database |

+----------+---------------------------------------------------------------+

| Test3 | CREATE DATABASE ' test3 '/*!40100 defaultcharacter SET GBK */|

+----------+---------------------------------------------------------------+

1 row in Set (0.00 sec)

mysql> alterdatabase test3 CHARACTER SET ' UTF8 ';

Query OK, 1 rowaffected (0.00 sec)

mysql> showcreate DATABASE test3;

+----------+----------------------------------------------------------------+

| Database | Create Database |

+----------+----------------------------------------------------------------+

| Test3 | CREATE DATABASE ' test3 '/*!40100 defaultcharacter SET UTF8 */|

+----------+----------------------------------------------------------------+

1 row in Set (0.00 sec)

mysql> \ t

(6) Open the specified database

Use db_name;

(7) Get the database that is currently open

Select{database () | SCHEMA ()};

Mysql> showdatabases;

+--------------------+

| Database |

+--------------------+

|information_schema |

| King |

| MySQL |

|performance_schema |

|randongmei |

| Test |

| Test1 |

| Test3 |

+--------------------+

8 rows in Set (0.00 sec)

Mysql> USEtest1;

Database changed

Mysql> selectdatabase ();

+------------+

| DATABASE () |

+------------+

| Test1 |

+------------+

1 row in Set (0.00 sec)

Mysql> Userandongmei;

Database changed

Mysql> Selectschema ();

+------------+

| SCHEMA () |

+------------+

| Randongmei |

+------------+

1 row in Set (0.00 sec)

mysql> \ t

(8) Delete the specified database

DROP {database| SCHEMA} db_name;

DROP {database| SCHEMA} [IF EXISTS] db_name;

You cannot delete multiple databases at one time

MySQL Basic operation