Introduction to the "MySQL" syntax

This article mainly introduces the query syntax of MySQL in Where,group by, order BY, Limit,join,union, union All, child table and so on.

Test data preparation

CREATE TABLE EMP (empno numeric (4) NOT NULL, ename varchar, job varchar (9), Mgr Numeric (4), HireDate D Atetime, Sal Numeric (7, 2), Comm Numeric (7, 2), Deptno numeric (2)); CREATE TABLE Dept (deptno Numeric (2), D  Name varchar, loc varchar (in); CREATE table Salgrade (grade numeric, losal numeric, hisal numeric); insert into dept values (' ACCOUNTING ', ' NEW YORK '), insert INTO dept values ("" "," DALLAS "); INSERT INTO Dept value S (+, ' SALES ', ' CHICAGO '); INSERT into dept values (+, ' OPERATIONS ', ' BOSTON '); insert into salgrade values (1, 700, 1200) INSERT into Salgrade values (2, 1201, 1400), insert into Salgrade values (3, 1401, +), insert into Salgrade values (4, 2  001, insert into Salgrade values (5, 3001, 9999), insert into EMP values (7369, ' SMITH ', ' clerk ', 7902, ' 1980-12-17 '), INSERT into EMP values (7499, ' ALLEN ', ' salesman ', 7698, ' 1981-02-20 ', ","); INSERT into EMP valu ES (7521, ' WARD ', ' SALESMan ', 7698, ' 1981-02-22 ', 1250, Max, +); INSERT into EMP values (7566, ' JONES ', ' MANAGER ', 7839, ' 1981-04-02 ', 2975, NULL,  INSERT into EMP values (7654, ' MARTIN ', ' salesman ', 7698, ' 1981-09-28 ', 1250, 1400, +); INSERT into EMP values (7698, ' BLAKE ', ' manager ', 7839, ' 1981-05-01 ', 2850, NULL, +); INSERT into EMP values (7782, ' CLARK ', ' manager ', 7839, ' 1981-06-  (2450, NULL, +), insert into EMP values (7788, ' SCOTT ', ' ANALYST ', 7566, ' 1982-12-09 ', +, NULL, +); INSERT INTO EMP VALUES (7839, ' KING ', ' president ', NULL, ' 1981-11-17 ', ' max ', ' null '); INSERT into EMP values (7844, ' TURNER ', ' salesman ') , 7698, ' 1981-09-08 ', 0, +); INSERT into EMP values (7876, ' ADAMS ', ' clerk ', 7788, ' 1983-01-12 ', 1100, NULL, +); ins ert into EMP values (7900, ' JAMES ', ' clerk ', 7698, ' 1981-12-03 ', 950, NULL, +); INSERT into EMP values (7902, ' FORD ', ' ANA LYST ', 7566, ' 1981-12-03 ', +, NULL, +); INSERT into EMP values (7934, ' MILLER ', ' clerk ', 7782, ' 1982-01-23 ', 1300, NULL , 10);

1. Fuzzy query

Mysql> SELECT * from emp where ename like '%s% '; +-------+-------+---------+------+---------------------+---------+------+--------+| Empno | ename | Job | Mgr | HireDate | Sal | Comm |  Deptno |+-------+-------+---------+------+---------------------+---------+------+--------+| 7369 | SMITH | Clerk | 7902 |  1980-12-17 00:00:00 | 800.00 |     NULL |  20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 |     NULL |  20 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 |     NULL |  20 | | 7876 | ADAMS | Clerk | 7788 | 1983-01-12 00:00:00 | 1100.00 |     NULL |  20 | | 7900 | JAMES | Clerk | 7698 |  1981-12-03 00:00:00 | 950.00 |     NULL | |+-------+-------+---------+------+---------------------+---------+------+--------+5 rows in Set (0.00 sec) MySQL > select * from emp where ename like ' s% '; +-------+-------+---------+------+---------------------+---------+------+--------+| Empno | ename | Job | Mgr |          HireDate  | Sal | Comm |  Deptno |+-------+-------+---------+------+---------------------+---------+------+--------+| 7369 | SMITH | Clerk | 7902 |  1980-12-17 00:00:00 | 800.00 |     NULL |  20 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 |     NULL | |+-------+-------+---------+------+---------------------+---------+------+--------+2 rows in Set (0.01 sec) MySQL > select * from emp where ename like '%s ', +-------+-------+---------+------+---------------------+---------+------+ --------+| Empno | ename | Job | Mgr | HireDate | Sal | Comm |  Deptno |+-------+-------+---------+------+---------------------+---------+------+--------+| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 |     NULL |  20 | | 7876 | ADAMS | Clerk | 7788 | 1983-01-12 00:00:00 | 1100.00 |     NULL |  20 | | 7900 | JAMES | Clerk | 7698 |  1981-12-03 00:00:00 | 950.00 |     NULL | |+-------+-------+---------+------+---------------------+---------+------+--------+3 rows in Set (0.00 sec) mysql> SELECT * from emp where ename like ' _o% '; +-------+-------+---------+------+-------- -------------+---------+------+--------+| Empno | ename | Job | Mgr | HireDate | Sal | Comm |  Deptno |+-------+-------+---------+------+---------------------+---------+------+--------+| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 |     NULL |  20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 |     NULL | |+-------+-------+---------+------+---------------------+---------+------+--------+2 rows in Set (0.00 sec) Summary:% Represents any 0 or more characters that match any type and length, and _ represents any single character that matches a single character.

2. Sorting

Mysql> SELECT * from emp ORDER by sal;+-------+--------+-----------+------+---------------------+---------+------- --+--------+| Empno | ename | Job | Mgr | HireDate | Sal | Comm |  Deptno |+-------+--------+-----------+------+---------------------+---------+---------+--------+| 7369 | SMITH | Clerk | 7902 |  1980-12-17 00:00:00 |    800.00 |     NULL |  20 | | 7900 | JAMES | Clerk | 7698 |  1981-12-03 00:00:00 |    950.00 |     NULL |  30 | | 7876 | ADAMS | Clerk | 7788 | 1983-01-12 00:00:00 |    1100.00 |     NULL |  20 | | 7521 | WARD | Salesman | 7698 | 1981-02-22 00:00:00 |  1250.00 |     500.00 |  30 | | 7654 | MARTIN | Salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 |     1400.00 |  30 | | 7934 | MILLER | Clerk | 7782 | 1982-01-23 00:00:00 |    1300.00 |     NULL |  10 | | 7844 | TURNER | Salesman | 7698 | 1981-09-08 00:00:00 |    1500.00 |     0.00 |  30 | | 7499 | ALLEN | Salesman | 7698 | 1981-02-20 00:00:00 |  1600.00 | 300.00|  30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 |    2450.00 |     NULL |  10 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 |    2850.00 |     NULL |  30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 |    2975.00 |     NULL |  20 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 |    3000.00 |     NULL |  20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 |    3000.00 |     NULL |  20 | | 7839 | KING | President | NULL | 1981-11-17 00:00:00 |    5000.00 |     NULL | |+-------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in Set (0.00 sec ) mysql> SELECT * from emp order by Sal asc;+-------+--------+-----------+------+---------------------+---------+--- ------+--------+| Empno | ename | Job | Mgr | HireDate | Sal | Comm |  Deptno |+-------+--------+-----------+------+---------------------+---------+---------+--------+| 7369 | SMITH | Clerk | 7902 | 1980-12-17 00:00:00 |    800.00 |     NULL |  20 | | 7900 | JAMES | Clerk | 7698 |  1981-12-03 00:00:00 |    950.00 |     NULL |  30 | | 7876 | ADAMS | Clerk | 7788 | 1983-01-12 00:00:00 |    1100.00 |     NULL |  20 | | 7521 | WARD | Salesman | 7698 | 1981-02-22 00:00:00 |  1250.00 |     500.00 |  30 | | 7654 | MARTIN | Salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 |     1400.00 |  30 | | 7934 | MILLER | Clerk | 7782 | 1982-01-23 00:00:00 |    1300.00 |     NULL |  10 | | 7844 | TURNER | Salesman | 7698 | 1981-09-08 00:00:00 |    1500.00 |     0.00 |  30 | | 7499 | ALLEN | Salesman | 7698 | 1981-02-20 00:00:00 |  1600.00 |     300.00 |  30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 |    2450.00 |     NULL |  10 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 |    2850.00 |     NULL |  30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 |    2975.00 |     NULL |  20 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 |  3000.00 |  NULL |  20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 |    3000.00 |     NULL |  20 | | 7839 | KING | President | NULL | 1981-11-17 00:00:00 |    5000.00 |     NULL | |+-------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in Set (0.00 sec ) mysql> SELECT * from emp order by Sal desc;+-------+--------+-----------+------+---------------------+---------+-- -------+--------+| Empno | ename | Job | Mgr | HireDate | Sal | Comm |  Deptno |+-------+--------+-----------+------+---------------------+---------+---------+--------+| 7839 | KING | President | NULL | 1981-11-17 00:00:00 |    5000.00 |     NULL |  10 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 |    3000.00 |     NULL |  20 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 |    3000.00 |     NULL |  20 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 |    2975.00 |     NULL |  20 | | 7698 | BLAKE | MANAGER |7839 | 1981-05-01 00:00:00 |    2850.00 |     NULL |  30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 |    2450.00 |     NULL |  10 | | 7499 | ALLEN | Salesman | 7698 | 1981-02-20 00:00:00 |  1600.00 |     300.00 |  30 | | 7844 | TURNER | Salesman | 7698 | 1981-09-08 00:00:00 |    1500.00 |     0.00 |  30 | | 7934 | MILLER | Clerk | 7782 | 1982-01-23 00:00:00 |    1300.00 |     NULL |  10 | | 7521 | WARD | Salesman | 7698 | 1981-02-22 00:00:00 |  1250.00 |     500.00 |  30 | | 7654 | MARTIN | Salesman | 7698 | 1981-09-28 00:00:00 | 1250.00 |     1400.00 |  30 | | 7876 | ADAMS | Clerk | 7788 | 1983-01-12 00:00:00 |    1100.00 |     NULL |  20 | | 7900 | JAMES | Clerk | 7698 |  1981-12-03 00:00:00 |    950.00 |     NULL |  30 | | 7369 | SMITH | Clerk | 7902 |  1980-12-17 00:00:00 |    800.00 |     NULL | |+-------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in Set (0.00 sec Summary: ORDER by Sort by default in ASC ascending order or DESC descending order

3. How many lines are restricted

Mysql> SELECT * from emp limit 3;+-------+-------+----------+------+---------------------+---------+--------+---- ----+| Empno | ename | Job | Mgr | HireDate | Sal | Comm |  Deptno |+-------+-------+----------+------+---------------------+---------+--------+--------+| 7369 | SMITH | Clerk | 7902 |  1980-12-17 00:00:00 |   800.00 |     NULL |  20 | | 7499 | ALLEN | Salesman | 7698 | 1981-02-20 00:00:00 | 1600.00 |     300.00 |  30 | | 7521 | WARD | Salesman | 7698 | 1981-02-22 00:00:00 | 1250.00 |     500.00 | |+-------+-------+----------+------+---------------------+---------+--------+--------+3 rows in Set (0.00 sec) Mysql> SELECT * from emp ORDER BY sal DESC limit 3;+-------+-------+-----------+------+---------------------+-------- -+------+--------+| Empno | ename | Job | Mgr | HireDate | Sal | Comm |  Deptno |+-------+-------+-----------+------+---------------------+---------+------+--------+| 7839 | KING | President | NULL | 1981-11-17 00:00:00 | 5000.00 |     NULL |  10 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 |     NULL |  20 | | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 |     NULL | |+-------+-------+-----------+------+---------------------+---------+------+--------+3 rows in Set (0.00 sec) Summary: Limit limits display the number of rows that can be used in conjunction with ORDER by

4. Aggregation functions

Count () sum () function usage: #1. Salary and mysql> for each department select Deptno,sum (SAL) from EMP Group by deptno;+--------+----------+| Deptno |     Sum (SAL) |+--------+----------+|  10 |     8750.00 | | 20 |     10875.00 | |  30 | 9400.00 |+--------+----------+3 rows in Set (0.01 sec) #2. The Group By field must appear in the Payroll and mysql> Select Deptno for each of the departments behind the Select field, Job, SUM (SAL) from the EMP Group by DEPTNO, job;+--------+-----------+----------+| Deptno | Job |     Sum (SAL) |+--------+-----------+----------+| 10 |  Clerk |     1300.00 | | 10 |  MANAGER |     2450.00 | | 10 |  President |     5000.00 | | 20 |  ANALYST |     6000.00 | | 20 |  Clerk |     1900.00 | | 20 |  MANAGER |     2975.00 | | 30 |   Clerk |     950.00 | | 30 |  MANAGER |     2850.00 | | 30 |  Salesman | 5600.00 |+--------+-----------+----------+9 rows in Set (0.01 sec) #3. Have a salary and >5000 various positions in various departments mysql> select DEPTN O,job, sum (SAL), from EMP-Group by DEPTNO, job-to have sum (SAL) >5000; +--------+----------+----------+| Deptno | Job |     Sum (SAL) |+--------+----------+----------+| 20 |  ANALYST |     6000.00 | | 30 |  Salesman | 5600.00 |+--------+----------+----------+2 rows in Set (0.00 sec) #4. Common combinations where order limit select Deptno,job, sum (SAL) a S sum_salfrom emp where job= ' salesman ' GROUP by DEPTNO, jobhaving sum (SAL) >5000 ORDER by sum (SAL) DESC LIMIT 1;

Below is a description of the use of join and union
Data preparation:

create table testa(aid int,aname varchar(40));create table testb(bid int,bname varchar(40),age int);insert into testa values(1,‘xiaoming‘);insert into testa values(2,‘LY‘);insert into testa values(3,‘KUN‘);insert into testa values(4,‘ZIDONG‘);insert into testa values(5,‘HB‘);insert into testb values(1,‘xiaoming‘,10);insert into testb values(2,‘LY‘,100);insert into testb values(3,‘KUN‘,200);insert into testb values(4,‘ZIDONG‘,110);insert into testb values(6,‘niu‘,120);insert into testb values(7,‘meng‘,130);insert into testb values(8,‘mi‘,170);

5.left Join

mysql> select    -> a.aid,a.aname,    -> b.bid,b.bname,b.age    -> from testa as a    -> left join testb as b on a.aid=b.bid; +------+----------+------+----------+------+| aid  | aname    | bid  | bname    | age  |+------+----------+------+----------+------+|    1 | xiaoming |    1 | xiaoming |   10 ||    2 | LY       |    2 | LY       |  100 ||    3 | KUN      |    3 | KUN      |  200 ||    4 | ZIDONG   |    4 | ZIDONG   |  110 ||    5 | HB       | NULL | NULL     | NULL |+------+----------+------+----------+------+5 rows in set (0.00 sec)总结：a left join b  a表全，用b表去匹配a表LEFT JOIN 关键字会从左表 (a) 那里返回所有的行，即使在右表 (b) 中没有匹配的行,匹配不到的列用NULL代替

6.right Join

mysql> select    -> a.aid,a.aname,    -> b.bid,b.bname,b.age    -> from testa as a    -> right join testb as b on a.aid=b.bid;+------+----------+------+----------+------+| aid  | aname    | bid  | bname    | age  |+------+----------+------+----------+------+|    1 | xiaoming |    1 | xiaoming |   10 ||    2 | LY       |    2 | LY       |  100 ||    3 | KUN      |    3 | KUN      |  200 ||    4 | ZIDONG   |    4 | ZIDONG   |  110 || NULL | NULL     |    6 | niu      |  120 || NULL | NULL     |    7 | meng     |  130 || NULL | NULL     |    8 | mi       |  170 |+------+----------+------+----------+------+7 rows in set (0.00 sec)总结：a right join b  b表全，用a表去匹配b表RIGHT JOIN 关键字会右表 (b) 那里返回所有的行，即使在左表 (a) 中没有匹配的行,匹配不到的列用NULL代替

7.inner Join

Mysql> Select, A.aid,a.aname, B.bid,b.bname,b.age, from Testa as a inner join TESTB a s B on a.aid=b.bid; +------+----------+------+----------+------+| Aid | Aname | Bid | bname |    Age |+------+----------+------+----------+------+| 1 |    Xiaoming | 1 |   Xiaoming |    10 | | 2 |    LY | 2 |  LY |    100 | | 3 |    KUN | 3 |  KUN |    200 | | 4 |    Zidong | 4 |  Zidong |  |+------+----------+------+----------+------+4 rows in Set (0.00 sec) mysql> Select, A.aid,a.aname, B.bid,b.bname,b.age, from Testa as a, join TESTB as B on a.aid=b.bid; +------+----------+------+----------+------+| Aid | Aname | Bid | bname |    Age |+------+----------+------+----------+------+| 1 |    Xiaoming | 1 |   Xiaoming |    10 | | 2 |    LY | 2 |  LY |    100 | | 3 |    KUN | 3 |  KUN |    200 | | 4 |    Zidong | 4 |  Zidong | |+------+----------+------+----------+ +-----+4 rows in Set (0.00 sec) Summary: Inner JOIN is the same as the join effect when there is at least one match in the table, the INNER JOIN keyword returns a row 

8.union and UNION All

mysql> select aid,aname from testa    -> union    -> select bid,bname from testb;+------+----------+| aid  | aname    |+------+----------+|    1 | xiaoming ||    2 | LY       ||    3 | KUN      ||    4 | ZIDONG   ||    5 | HB       ||    6 | niu      ||    7 | meng     ||    8 | mi       |+------+----------+8 rows in set (0.01 sec)mysql> select aid,aname from testa    -> union all    -> select bid,bname from testb;+------+----------+| aid  | aname    |+------+----------+|    1 | xiaoming ||    2 | LY       ||    3 | KUN      ||    4 | ZIDONG   ||    5 | HB       ||    1 | xiaoming ||    2 | LY       ||    3 | KUN      ||    4 | ZIDONG   ||    6 | niu      ||    7 | meng     ||    8 | mi       |+------+----------+12 rows in set (0.00 sec)总结：union 会去重 union all不去重

Introduction to the "MySQL" syntax