MySQL query syntax summary

MySQL query syntax summary

Preface:

This article mainly introduces the query syntax such as where, group by, order by, limit, join, union, union all, subtable, etc. in MySQL.

Test data preparation

create table emp ( empno numeric(4) not null, ename varchar(10), job varchar(9), mgr numeric(4), hiredate datetime, sal numeric(7, 2), comm numeric(7, 2), deptno numeric(2));
create table dept ( deptno numeric(2), dname varchar(14), loc varchar(13));
create table salgrade ( grade numeric, losal numeric, hisal numeric);
insert into dept values ​​(10, 'ACCOUNTING', 'NEW YORK');insert into dept values ​​(20, 'RESEARCH', 'DALLAS');insert into dept values ​​(30, 'SALES', 'CHICAGO');insert into dept values ​​(40, 'OPERATIONS', 'BOSTON');
insert into salgrade values ​​(1, 700, 1200);insert into salgrade values ​​(2, 1201, 1400);insert into salgrade values ​​(3, 1401, 2000);insert into salgrade values ​​(4, 2001, 3000);insert into salgrade values ​​(5, 3001, 9999);
insert into emp values ​​(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);insert into emp values ​​(7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);insert into emp values ​​(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);insert into emp values ​​(7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);insert into emp values ​​(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);insert into emp values ​​(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);insert into emp values ​​(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);insert into emp values ​​(7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);insert into emp values ​​(7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);insert into emp values ​​(7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);insert into emp values ​​(7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);insert into emp values ​​(7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);insert into emp values ​​(7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);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 | 30 |+-------+-------+---------+------+---------------------+---------+------+--------+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 | 20 |+-------+-------+---------+------+---------------------+---------+------+--------+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 | 30 |+-------+-------+---------+------+---------------------+---------+------+--------+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 | 20 |+-------+-------+---------+------+---------------------+---------+------+--------+2 rows in set (0.00 sec)

Summary: % represents any 0 or more characters, and can match characters of any type and length; _ represents any single character, and matches a single arbitrary 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 | 10 |+-------+--------+-----------+------+---------------------+---------+---------+--------+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 | 10 |+-------+--------+-----------+------+---------------------+---------+---------+--------+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 | 20 |+-------+--------+-----------+------+---------------------+---------+---------+--------+14 rows in set (0.00 sec)

Summary: order by sorting is sorted in ascending order by default, and you can also specify desc descending order

3. Limit the number of rows

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 | 30 |+-------+-------+----------+------+---------------------+---------+--------+--------+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 | 20 |+-------+-------+-----------+------+---------------------+---------+------+--------+3 rows in set (0.00 sec)

Summary: limit limits the number of rows to be displayed, and can be used in conjunction with order by

4. Aggregation function

count() sum() function usage: #1. Salaries and salaries of various departmentsmysql> 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)
: : : : : : : : : : : : : : :
#3.having various positions in various departments with salary>5000mysql> select deptno,job, sum(sal) -> from emp -> group by deptno ,job -> having sum(sal)>5000; +--------+----------+----------+| deptno | job | sum(sal) |+--------+----------+----------+| 20 | ANALYST | 6000.00 || 30 | SALESMAN | 5600.00 |+--------+----------+----------+2 rows in set (0.00 sec)#4.Common combinationswhere order limit select deptno,job, sum(sal) as sum_salfrom emp where job='SALESMAN'group by deptno ,jobhaving sum(sal)>5000 order by sum(sal) desc limit 1;

The following describes the usage 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 |+------+----------+------+----------+------+5 rows in set (0.00 sec)

Summary: a left join b a table is complete, and b table is used to match a table. The LEFT JOIN keyword will return all rows from the left table (a), even if there are no matching rows in the right table (b). The unmatched columns are replaced with 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)

Summary: a right join b b table is complete, use table a to match table b RIGHT JOIN keyword will return all rows from the right table (b), even if there is no matching row in the left table (a), the unmatched columns are replaced with NULL

7. Inner join

mysql> select -> a.aid,a.aname, -> b.bid,b.bname,b.age -> from testa as a -> inner 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 |+------+----------+------+----------+------+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 | 110 |+------+----------+------+----------+------+4 rows in set (0.00 sec)

Summary: Inner join has the same effect as join. The INNER JOIN keyword returns rows when there is at least one match in the table.

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)

Summary: union will remove duplicates, while union all will not.

The above is the detailed content of the MySQL query syntax summary. For more information about MySQL query syntax, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:
  • How to query date and time in mysql
  • MySQL database advanced query and multi-table query
  • A brief discussion on the implementation of fuzzy query using wildcards in MySQL
  • A brief discussion on using Cartesian product principle to query multiple tables in MySQL
  • Several implementation methods and advantages and disadvantages of SQL paging query in MySQL
  • An article to master MySQL index query optimization skills
  • Mysql query database capacity method steps
  • Python pymysql link database query results converted to Dataframe instance
  • A brief discussion on the problem of passing parameters when using in in pymysql query statements
  • Example of how to retrieve the latest data using MySQL multi-table association one-to-many query

<<:  Idea deployment tomcat service implementation process diagram

>>:  Detailed explanation of Vuex overall case

Recommend

CSS scroll bar style modification code

CSS scroll bar style modification code .scroll::-...

The w3c organization gives style recommendations for html4

This is the style recommendation given by the W3C ...

Detailed introduction to deploying k8s cluster on centos7 system

Table of contents 1 Version and planning 1.1 Vers...

Vue+ECharts realizes the drawing of China map and automatic rotation and highlighting of provinces

Table of contents Achieve results Complete code +...

Network configuration of Host Only+NAT mode under VirtualBox

The network configuration of Host Only+NAT mode u...

A practical record of encountering XSS attack in a VUE project

Table of contents Preface Discover the cause Cust...

Solve the problem of Navicat for Mysql connection error 1251 (connection failed)

Because what I wrote before was not detailed enou...

Best Practices Guide for MySQL Partitioned Tables

Preface: Partitioning is a table design pattern. ...

How to change the database data storage directory in MySQL

Preface The default database file of the MySQL da...

The space is displayed differently in IE, Firefox, and Chrome browsers

&nbsp;&nbsp;The display effects on IE, Fir...

Recommend several MySQL related tools

Preface: With the continuous development of Inter...

Nginx configuration 80 port access 8080 and project name address method analysis

Tomcat accesses the project, usually ip + port + ...

HTML+CSS+JS realizes the scrolling gradient effect of the navigation bar

Table of contents First look at the effect: accom...

Learn MySQL in a simple way

Preface The database has always been my weak poin...

Sample code of uniapp vue and nvue carousel components

The vue part is as follows: <template> <...