Common SQL commands

First, the MySQL database daily operation.
1. Start Mysql:/etc/init.d/mysql Start (the installation path in front of MySQL)
2. Restart MySQL:/etc/init.d/mysql restart (the installation path in front of MySQL)
3. Turn off MySQL:/etc/init.d/mysql stop (the installation path in front of MySQL)
4. Connect to MySQL on this computer:
Mysql-u User Name –p (press ENTER, then enter the password)
Example: Mysql–u root–p
5. Exit Mysql:exit
6. Modify the MySQL password:
(1) mysqladmin-u username-p Old password password new password
Example: Mysqladmin–u root–p 123456 Password 654321
(2) Enter MySQL command line set PASSWORD for user name @ host =password ("root");
Example: set password for [email Protected]=password ("123456");
(3) Change your password:
Set Password=password ("123456");
7. Add new users:
Grant operation on database. * To User name @ login host identified by "password"
Example: Grant select on Yingyu to [e-mail protected] indentify by "123456";
8. Build the Library:
Create database name;
Example: Create database Yingyu;
9. Display the list of databases: show databases;
10. Open the database:
Use database;
Example: Use Yingyu;
11. Delete the library:
drop database name;
Example: Drop database Yingyu;
12. Display the data table in the library:
Show tables;
13. Display the structure of the data table:
describe table name;
Example: describe student;
14. Changes to the code:
(1) Change the entire MySQL encoding format:
When you start MySQL, the Mysqld_safe command line joins
--default-character-set= encoding Format
Example:--default-character-set=utf-8
(2) Change the encoding format of a library:
Enter the command after the MySQL prompt:
ALTER DATABASE default character set encoding format;
Example: ALTER DATABASE student default character set UTF-8;
Second, SQL common commands
1. Build a table:
CREATE TABLE table name (field settings list);
Example: CREATE TABLE Student
(Stuid char (primary) key,
Name Char (20),
grade int (3),
Age Int (3)
);
2. Deletion of the table:
drop table name;
Example: drop table student;
3. Empty the records in the table:
Delete from table name;
Example: delete from student;
4. Display the records in the table:
SELECT * from table name;
Example: SELECT * from student;
5. Renaming the table:
Rename table name to new table name;
Example: Rename table student to people;
6. Modify the Field properties:
(1) ALTER TABLE name change field Name field Name field type [whether non-null allowed];
Example: ALTER TABLE student change name newname char () null;
(2) ALTER TABLE name modify field Name field type [allow non-null];
Example: ALTER TABLE student modify name char () null;
7. Modify table Settings default fields:
(1) ALTER TABLE name modify field Name field type default defaults;
Example: ALTER TABLE student modify name char (TEN) Defalt 7;
(2) ALTER TABLE name ALTER FIELD name set default value;
Example: ALTER TABLE student ALTER name set default 7;
8. Add a field to the table:
ALTER TABLE name Add column field Name field type (default defaults);
Example: ALTER TABLE student add column sex char (default 1);
9. Delete a field from a table
ALTER TABLE Name drop column field name;
Example: ALTER TABLE student drop column name;
10. Delete Table PRIMARY key
ALTER TABLE name drop PRIMARY key;
Example: ALTER TABLE student drop PRIMARY key;
11. Add a new primary key:
ALTER TABLE name Add primary key (field);
Example: ALTER TABLE student add primary key (STUID);
12. Insert a row of data into the table:
Insert into Table name values (field data 1, field data 2,???)
Example: INSERT into student values (' 123 ', ' qqqq ', ' 80 ');
13. Insert multiple rows of data into the table:
Insert into Table name values (field data 1, field data 2,???), (field data 1, field data 2,???),??? ；
Example: INSERT into student values (' 123 ', ' qqqq ', ' n '), (' 124 ', ' yyyy ', ' 90 ');
14. Modify the table's data:
Update table name set field name =value where scope
Example: Update student set name= ' qqq1111 ' where stuid= ' 123 ';
15. Fuzzy Query
SELECT * from table name where field like '%value1% ';
Example: SELECT * from student where name like ' q% ';
16. Sort queries:
SELECT * FROM table name order By field name 1, field name 2 [desc]
Example: Selec * from student order by grade; (ascending)
SELECT * from student order by grade DESC; (Descending)
17. Left connection query:
Select Table 1. field 1, table 1. Field 2, table 2. Field 1, Table 2. fields from table 2 left (outer) Join table 1 on table 2. field = table 1. field;
Example: Select Student.num,student.name,people.name,people.age from student left (outer) joins people on Student.name= People.name;
18. Right Connection query:
Select Table 1. field 1, table 1. Field 2, table 2. Field 1, Table 2. fields from table 2 right (outer) Join table 1 on table 2. field = table 1. field;
Example: Select Student.num,student.name,people.name,people.age from Student Right (outer) joins people on Student.name= People.name;
19. Full connection query (MySQL does not support full connection, so connect with left join Union right)
Select Table 1.*, table 2.* from table 1 left (outer) Join table 2 on table 1. field = Table 2. Field Union Select Table 1.*, table 2.* from table 1 Right (outer) Join table 2 on table 1. Field = Table 2. Fields;
Example: Select s.*,p.* from student s left join people p in s.name = P.name Union select s.*,p.* from student s right join People p on s.name = P.name;
20. Inquiries about the year
Example: Find out all the information about a student born between 1990-1993 years
SELECT * FROM student where year (Sbirthday) between 1990 and 1993;
Find out all the information of students born before December 5, 1990
SELECT * FROM student where birthday < date (' 1990-12-05 ');
Third, backup and restore
1. Back up the database:
Mysqldump–u User name –p database name > Save path + file name;
Example: Mysqldump–u root–p yingyu >/home/yingyu/yingyu.sql;
2. Restore the database:
Mysql–u User name –p database name < file path + filename;
Example: Mysql–u root–p Yingyu 3. Compress the MySQL database directly backup
Mysqldump–u User name –p database name | Gzip > Save path + file name
Example: Mysqldump–u root–p Yingyu | gzip >/home/yingyu/yingyu.sql.gz;
4. Restore the compacted MySQL database
Gunzip < file path + filename | Mysql–u User name –p database name
Example: Gunzip 5. Back up some tables in the database:
Mysqldump–u User name –p database name Table name 1 Table Name 2 > Save path + file name
Example: Mysqldump–u root–p Yingyu student >/home/yingyu/yingyu.sql;
6. Back up some databases in the database:
Mysqldump–u User name –p–b Library 1 Library 2 > Save path + file name
Example: Mysqldump–u root–p–b yingyu1 yingyu2>/home/yingyu/yingyu.sql;
7. Restore some databases in the database:
Mysqldump–u User name –p–d Library 1 Library 2 < file path + filename;
Example: Mysqldump–u root–p–d Qiuyingyu yingyu8. Restore some tables in the database:
Mysql–u User name –p database name < save path + Table file name
Example: Mysql–u root–p Yingyu

Time format interval query:
Between To_date (' 2011-07-01 ', ' yyyy-mm-dd ') and to_date (' 2017-07-25 ', ' yyyy-mm-dd ')

Common SQL commands