MySQL SQL command quick review

1. MySQL server related commands

Start the MySQL service
net start MySQL service name

Close the MySQL service
net stop MySQL service name

Log in
mysql -h host name -u user name [-P port] -p

Quit
quit; or exit;

Set the access character
set names utf8;
Generally, cmd will set gbk
PHP script will set utf8

Backup database
mysqldump -u username -p database name > file name
No need to connect to mysql

Restore database
mysql -u username-p database name No need to connect to mysql

2. MySQL database related commands

Query all existing databases
SHOW DATABASES;

Query a single database Create information
SHOW CREATE DATABASE database name;

3. MySQL data table related commands

Create table
CREATE TABLE [IF NOT EXITST] table name (field list [, constraint or index list]) [table option list];
Field form: field name field type [field modification]
Field modification: NOT NULL / AUTO_INCREMENT / PRIMARY KEY / UNIQUE / DEFAULT / COMMENT / UNSIGNED (only valid in numeric types)
Table options: ENGINE=InnoDB,CHARSET=utf8,COMMENT ='table comment';

Modify table (rarely used)
ALTER TABLE table name multiple statements;

Delete table
DROP TABLE [IF EXISTS] table name;

Query table structure
DESC table name;

Query all tables in the current database
SHOW TABLES;

Query single table creation information
SHOW CREATE TABLE table name;

Four. MySQL data insertion related commands

The first form
INSERT INTO table name (field name 1, field Name 2,...) VALUES (value 1, value 2, value 3,...), ((value 1, value 2, value 3,...),...;

Second type Form
INSERT INTO table name set field name 1 = value expression 1, field name 2 = value expression 2,...;

The third form
INSERT INTO table name (field name 1, field name 2,. ..) SELECT field name 1, field name 2,... FROM other table names;

Note
1, form 1 and form 3, you can add multiple records at one time
2. Fields and values ​​must correspond one to one
3 , Value form: Numbers and function calls are written directly, string and date types must be added with single quotes
4. Auto_increment/timestamp fields do not need to be inserted

5. MySQL data deletion related commands

6. MySQL data modification related commands

7. MySQL data search related commands

8. MySQL stored procedure related commands

Create a stored procedure
CREATE PROCEDURE process name ([process parameters [,...]]) [features...] process body

mysql> DELIMITER //  
mysql> CREATE PROCEDURE proc1(OUT s int)  
     > BEGIN 
     > SELECT COUNT(*) INTO s FROM user;  
     > END 
     > //  
mysql> DELIMITER ;

9. MySQL stored function related commands

10. MySQL trigger related commands

Create trigger

CREATE TRIGGER trigger name (BEFORE|AFTER) (INSERT|UPDATE|DELETE) ON table name FOR EACH ROW trigger body