SQL commands commonly used in the work!!!

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[email 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

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 (installation path for MySQL in front)      3. Turn off MySQL:  /etc/init.d/mysql stop (the installation path in front of MySQL)      4. Connect to mysql:        mysql-u username –p (press ENTER, enter password)          Example: Mysql–u ro ot–p     5. Exit Mysql:exit       6. Modify the MySQL password:      (1) mysqladmin-u user name-P Old password Password new password               Example: mysqladmin–u root–p 123456 password 654321  &nbs P   (2) go to MySQL command line set PASSWORD for username @ host =password ("root");              Example: Set Passwo RD for [Email protected]=password ("123456");       (3) Modify your password:              Set Password=password ("123456");    7. Add new user:       grant operation on database. * To User name @ Login host identified by Password         Example: Grant Select on Yingyu to [email protected] indentify by "123456";    8. Built Library:       create database name;         Example: Create DATABASE yingyu;    9. Display Database list:  show databases;   10. Open Database:       use database;        Example: Use Y ingyu;  11. Delete Library:      &NBSP;DROP database name;         Example: Drop database YINGYU;&N Bsp 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. Code Changes:      (1) Change the entire MySQL encoding format:               start MySQL, mysqld_safe command line add               &NBSP --default-character-set= encoding format                 Example:--default-character-set=utf-8       (2) Change the encoding format of a library:  &nbsp           entering commands at the MySQL prompt:              alter Database Default character set encoding format;               Example: ALTER DATABASE student default CHARACTE R set UTF-8; second, SQL common commands     1. Build table:       create table name (Field settings list);      Example: Create T Able student            (Stuid char (TEN) Primary key,              name Char,              grade int (3),       & nbsp      age int (3)               ;   2. Delete Table:    &nbs P drop table name;       Example: Drop table student;   3. Empty the records in the tables:      Delete from table names; nbsp;      Example: Delete from student;   4. Show records in table:      SELECT * FROM table name;     &NBsp Example: SELECT * from student;   5. Renaming a table:       Rename table old table name to new table name;      Example: Rena Me table student to people;   6. Modify Field Properties:      (1) ALTER TABLE name change field Name field Name field type [Allow non-null];&NBS p;            Example: ALTER TABLE student change name NewName char (null;     &NB SP; (2) ALTER TABLE name modify field Name field type [Allow non-empty];             Example: ALTER TABLE student MODIF Y name Char (null; ) 7. Modify table settings default fields:      (1) ALTER TABLE table name modify field Name field type default value;    & nbsp     Example: ALTER TABLE student modify name char (TEN) Defalt 7;      (2) ALTER TABLE name ALTER FIELD name Set DEF Ault value;           Example: ALTER TABLE student ALTER NAME set default 7;  8. Add a field to the table: &nbsp ;      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 of a table         ALTER TABLE name drop column field name;      &N Example: ALTER TABLE student drop column name;  10. Delete Table primary key         ALTER TABLE name drop primary Key;&nbs p;        Example: ALTER TABLE student drop primary key;  11. Add new primary key:        ALTER TABL E table name Add primary key (field);        Example: ALTER TABLE student add primary key (Stuid);  12. Insert a row of data into the list: &nbsp ;       INSERT into table name values (field data 1, field data 2,)          Example: INSERT into student values ( ' 123 ', ' qqqq ', ';  ') 13. Insert multiple rows of data into the table:        INSERT into List values (field data 1, field data 2,), (field data 1, field data 2 , •),;        Example: INSERT into student values (' 123 ', ' qqqq ', ' P '), (' 124 ', ' yyyy ', ' all ');  14. Modify table Data:        Update table name set field name =value where scope         Example: Update student set Name= ' qqq1111 ' where stuid= ' 123 '; &nbSp 15. Fuzzy query         SELECT * from table name where field like '%value1% ';         Example: SELECT * From student where name is like ' q% ';  16. Sort query:        SELECT * FROM table name order By field name 1, field name 2 [DESC]&NB sp;        Example: Selec * from student order by grade; (ascending)              & nbsp 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. Word Segment 2 from table 1 Left (outer) Join table 2 on table 1. field = Table 2. Fields;        Example: Select Student.num,student.name,people.name,p Eople.age from student left (outer) joins people on student.name=people.name;  18. Right connection query:        SE Lect table 1. Field 1, table 1. Field 2, table 2. Field 1, table 2. The From table 2 Right (outer) Join table 1 on table 2. field = Table 1. Fields;        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 use left JOIN to connect to 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  l EFT  join people p on  s.name = p.name Union select s.*,p.* from student S  right  join people p on &N Bsp;s.name = p.name;  20. Inquiries about the year       Example: query all information about students born between 1990-1993 years     SELECT * FROM student W Here year (Sbirthday) between 1990 and 1993;     Search all information for students born before December 5, 1990     SELECT * from Studen T where birthday < date (' 1990-12-05 '); third, backup and restore   1. Back up the database:    &NBSP;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 username –p database name < file path + file name;      Example: Mysql–u root–p Yingyu

SQL commands commonly used in work!!!