该博客详细介绍了数据库的各类查询操作，包括整表查询、指定列名返回、条件查询、排序、分页查询、聚合查询、多表查询和连接查询等。阐述了每种查询的语法及使用场景，如条件查询中的等于、大于等比较，聚合查询中的count、sum等函数运用。
目录
1， 整表查询（*）
语法：select * from "表名";
# 查询dept表中所有数据
# 星号(*)：表示返回表的所有列
mysql> select * from dept;
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
4 rows in set (0.00 sec)2， 指定列名返回
语法：select 字段1， 字段2 from 表名；
# 将select后的*号指定后具体的列名，就可以按指定列名返回；列名之间用逗号隔开
mysql> select dname, loc from dept;
+------------+----------+
| dname | loc |
+------------+----------+
| ACCOUNTING | NEW YORK |
| RESEARCH | DALLAS |
| SALES | CHICAGO |
| OPERATIONS | BOSTON |
+------------+----------+
4 rows in set (0.00 sec)2-1， 给列名取别名
语法：select 字段1 字段1别名， 字段2 字段2别名 from 表名；
# 取别名后，返回的列名为别名
# 字段与别名之间用空格隔开
mysql> select dname dept_dname, loc dept_loc from dept;
+------------+----------+
| dept_dname | dept_loc |
+------------+----------+
| ACCOUNTING | NEW YORK |
| RESEARCH | DALLAS |
| SALES | CHICAGO |
| OPERATIONS | BOSTON |
+------------+----------+
4 rows in set (0.00 sec)2-2， 给字段名算数运算
语法：select 字段 * num from 表名;
mysql> select empno, ename, sal * 12 year_money from emp limit 3;
+-------+-------+------------+
| empno | ename | year_money |
+-------+-------+------------+
| 7369 | SMITH | 9600.00 |
| 7499 | ALLEN | 19200.00 |
| 7521 | WARD | 15000.00 |
+-------+-------+------------+
3 rows in set (0.00 sec)2-3， 去重(distinct)
给返回字段去重
语法：select distinct 字段1，...， 字段n from 表名;
# distinct用于去重
mysql> select distinct mgr, deptno from emp;
+------+--------+
| mgr | deptno |
+------+--------+
| 7902 | 20 |
| 7698 | 30 |
| 7839 | 20 |
| 7839 | 30 |
| 7839 | 10 |
| 7566 | 20 |
| NULL | 10 |
| 7788 | 20 |
| 7782 | 10 |
+------+--------+
9 rows in set (0.00 sec)3， 条件查询（where字句）
通过"where"字句可实现条件查询，返回满足条件的数据
语法： select 字段1, ..., 字段n from 表名 where 条件表达式
当字段名为字符串时，需要用引号括起来(单，双引号都可以), 字符串的比较是按照ACSII码进行的
3-1， 等于查询（=）
# 查询deptno = 30的数据
mysql> select deptno, loc from dept where deptno = 30;
+--------+---------+
| deptno | loc |
+--------+---------+
| 30 | CHICAGO |
+--------+---------+
1 row in set (0.00 sec)
# 查询loc = 'DALLAS'的数据
mysql> select deptno, loc from dept where loc = 'DALLAS';
+--------+--------+
| deptno | loc |
+--------+--------+
| 20 | DALLAS |
+--------+--------+
1 row in set (0.00 sec)3-2， 大于查询（>）
# 查询deptno > 20的记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where deptno > 20;
+--------+---------+
| deptno | loc |
+--------+---------+
| 30 | CHICAGO |
| 40 | BOSTON |
+--------+---------+
2 rows in set (0.00 sec)
# 注意：字母是按ASCII码的顺序进行比较的
# 查询loc > 'CHICAGO'的记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where loc > 'CHICAGO';
+--------+----------+
| deptno | loc |
+--------+----------+
| 10 | NEW YORK |
| 20 | DALLAS |
+--------+----------+
2 rows in set (0.00 sec)
3-3， 大于等于查询（>=）
# 查询deptno >= 20记录， 回显字段为deptno, loc
mysql> select deptno, loc from dept where deptno >= 20;
+--------+---------+
| deptno | loc |
+--------+---------+
| 20 | DALLAS |
| 30 | CHICAGO |
| 40 | BOSTON |
+--------+---------+
3 rows in set (0.00 sec)
# 查询loc >= 'CHICAGO'记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where loc >= 'CHICAGO';
+--------+----------+
| deptno | loc |
+--------+----------+
| 10 | NEW YORK |
| 20 | DALLAS |
| 30 | CHICAGO |
+--------+----------+
3 rows in set (0.00 sec)3-4， 小于查询（<）
# 查询deptno < 20记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where deptno < 20;
+--------+----------+
| deptno | loc |
+--------+----------+
| 10 | NEW YORK |
+--------+----------+
1 row in set (0.00 sec)
# 查询loc < 'CHICAGO'记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where loc < 'CHICAGO';
+--------+--------+
| deptno | loc |
+--------+--------+
| 40 | BOSTON |
+--------+--------+
1 row in set (0.00 sec)3-5， 小于等于查询（<=）
# 查询deptno <= 20记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where deptno <= 20;
+--------+----------+
| deptno | loc |
+--------+----------+
| 10 | NEW YORK |
| 20 | DALLAS |
+--------+----------+
2 rows in set (0.00 sec)
# 查询loc <= 'CHICAGO'记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where loc <= 'CHICAGO';
+--------+---------+
| deptno | loc |
+--------+---------+
| 30 | CHICAGO |
| 40 | BOSTON |
+--------+---------+
2 rows in set (0.00 sec)3-6， 不等于查询(<>)
# 查询deptno <> 20记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where deptno <> 20;
+--------+----------+
| deptno | loc |
+--------+----------+
| 10 | NEW YORK |
| 30 | CHICAGO |
| 40 | BOSTON |
+--------+----------+
3 rows in set (0.00 sec)
# 查询loc <> 'CHICAGO'记录，回显字段为deptno, loc
mysql> select deptno, loc from dept where loc <> 'CHICAGO';
+--------+----------+
| deptno | loc |
+--------+----------+
| 10 | NEW YORK |
| 20 | DALLAS |
| 40 | BOSTON |
+--------+----------+
3 rows in set (0.00 sec)还可以通过关键字not实现不等查询， select * from 表名 where not 字段名='xxx'
mysql> select deptno, loc from dept where not deptno = 20;
+--------+----------+
| deptno | loc |
+--------+----------+
| 10 | NEW YORK |
| 30 | CHICAGO |
| 40 | BOSTON |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select deptno, loc from dept where not loc = 'CHICAGO';
+--------+----------+
| deptno | loc |
+--------+----------+
| 10 | NEW YORK |
| 20 | DALLAS |
| 40 | BOSTON |
+--------+----------+
3 rows in set (0.00 sec)3-7， 模糊查询(like)
语法： select 字段1, ..., 字段n from 表名 where 字段x like 正则表达式;
3-7-1， 匹配任意个字符(%)
# %：表示匹配任意个字符
mysql> select * from dept where loc like '%LL%';
+--------+----------+--------+
| deptno | dname | loc |
+--------+----------+--------+
| 20 | RESEARCH | DALLAS |
+--------+----------+--------+
1 row in set (0.00 sec)3-7-2， 匹配任意单个字符(_)
mysql> select * from dept where dname like '_C%';
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
+--------+------------+----------+
1 row in set (0.00 sec)3-7-3， 指定字符范围匹配[REGEXP]
mysql中使用 regexp操作正则表达式
# 注意这里用的是regexp， 而不是like
# 匹配首字母是A或S的数据
mysql> select * from dept where dname regexp '^[A, S]';
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 30 | SALES | CHICAGO |
+--------+------------+----------+
2 rows in set (0.00 sec)
# 匹配首字母在A~R范围的数据
mysql> select * from dept where dname regexp '^[A-R]';
+--------+------------+----------+
| deptno | dname | loc |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
3 rows in set (0.00 sec)
注意：若^写在中括号里面表示匹配除中括号以外的数据3-8， 条件组合查询(and, or, not)
条件表达式中，当存在多个条件时，可以通过逻辑运算符来组合所有条件进行查询
逻辑运算符优先级：not > and > or
# 查询sal > 2000 and empno > 7700的员工信息
mysql> select empno, ename, job, mgr, sal from emp where sal > 2000 and empno > 7700;
+-------+-------+-----------+------+---------+
| empno | ename | job | mgr | sal |
+-------+-------+-----------+------+---------+
| 7782 | CLARK | MANAGER | 7839 | 2450.00 |
| 7788 | SCOTT | ANALYST | 7566 | 3000.00 |
| 7839 | KING | PRESIDENT | NULL | 5000.00 |
| 7902 | FORD | ANALYST | 7566 | 3000.00 |
+-------+-------+-----------+------+---------+
4 rows in set (0.00 sec)
# 查询sal > 5000 or empno > 7800的员工信息
mysql> select empno, ename, job, mgr, sal from emp where sal > 5000 or empno > 7800;
+-------+--------+-----------+------+---------+
| empno | ename | job | mgr | sal |
+-------+--------+-----------+------+---------+
| 7839 | KING | PRESIDENT | NULL | 5000.00 |
| 7844 | TURNER | SALESMAN | 7698 | 1500.00 |
| 7876 | ADAMS | CLERK | 7788 | 1100.00 |
| 7900 | JAMES | CLERK | 7698 | 950.00 |
| 7902 | FORD | ANALYST | 7566 | 3000.00 |
| 7934 | MILLER | CLERK | 7782 | 1300.00 |
+-------+--------+-----------+------+---------+
6 rows in set (0.00 sec)
# 通过过表达式加上括号，先计算括号中的结果，再利用该结果和外面的做计算
mysql> select * from emp where (sal > 3000 or sal < 2000) and empno > 7400;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| 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 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
8 rows in set (0.00 sec)3-9， 指定范围过滤(between)
语法： select 字段1, ..., 字段n from 表名 where 字段 between value1 and value2;
# 查询sal在1000~3000范围的员工信息
mysql> select * from emp where sal between 1000 and 3000;
+-------+--------+----------+------+---------------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+--------+----------+------+---------------------+---------+---------+--------+
| 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 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 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 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+-------+--------+----------+------+---------------------+---------+---------+--------+
11 rows in set (0.00 sec)3-10，枚举查询(in)
语法：select 字段1, ..., 字段n from 表名 where 字段 in (value1,..., valuen);
mysql> select empno, ename, sal from emp where sal in (800, 1250, 1600, 1850)
-> ;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
| 7654 | MARTIN | 1250.00 |
+-------+--------+---------+
4 rows in set (0.00 sec)
mysql>3-11，NULL值处理
处理NULL时，使用is null， is not null
# 查询comm=null的列
mysql> select empno, ename from emp where comm is null;
+-------+--------+
| empno | ename |
+-------+--------+
| 7369 | SMITH |
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7876 | ADAMS |
| 7900 | JAMES |
| 7902 | FORD |
| 7934 | MILLER |
+-------+--------+
10 rows in set (0.00 sec)
# 查询comm != null的列
mysql> select empno, ename from emp where comm is not null;
+-------+--------+
| empno | ename |
+-------+--------+
| 7499 | ALLEN |
| 7521 | WARD |
| 7654 | MARTIN |
| 7844 | TURNER |
+-------+--------+
4 rows in set (0.00 sec)4， 排序(order by 子句)
语法：select 字段1, ..., 字段n from 表名 where 条件表达式 order by 字段名 排序方式；
where字句是可选的
4-1， 升序排序(asc)
asc是默认排序方式，可以不写
# 过滤出sal>2000的员工信息，并按sal升序排序
mysql> select empno, ename, job, mgr, sal, deptno from emp where sal > 2000 order by sal;
+-------+-------+-----------+------+---------+--------+
| empno | ename | job | mgr | sal | deptno |
+-------+-------+-----------+------+---------+--------+
| 7782 | CLARK | MANAGER | 7839 | 2450.00 | 10 |
| 7698 | BLAKE | MANAGER | 7839 | 2850.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 2975.00 | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 3000.00 | 20 |
| 7902 | FORD | ANALYST | 7566 | 3000.00 | 20 |
| 7839 | KING | PRESIDENT | NULL | 5000.00 | 10 |
+-------+-------+-----------+------+---------+--------+
6 rows in set (0.00 sec)4-2， 降序排序(desc)
desc是降序排序方式
# 过滤出sal>2000的员工信息，并按sal降序排序
mysql> select empno, ename, job, mgr, sal, deptno from emp where sal > 2000 order by sal desc;
+-------+-------+-----------+------+---------+--------+
| empno | ename | job | mgr | sal | deptno |
+-------+-------+-----------+------+---------+--------+
| 7839 | KING | PRESIDENT | NULL | 5000.00 | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 3000.00 | 20 |
| 7902 | FORD | ANALYST | 7566 | 3000.00 | 20 |
| 7566 | JONES | MANAGER | 7839 | 2975.00 | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 2850.00 | 30 |
| 7782 | CLARK | MANAGER | 7839 | 2450.00 | 10 |
+-------+-------+-----------+------+---------+--------+
6 rows in set (0.00 sec)4-3， 多字段排序
语法：select 字段1, ..., 字段n， from 表名 where 条件表达式 order by "字段x"，"字段y"...;
# 先按mgr升序排序
# 再按sal升序排序sal(注意sal的升序部分仅为mgr数据相同的部分)
mysql> select empno, ename, job, mgr, sal, deptno from emp where sal > 1000 order by mgr, sal;
+-------+--------+-----------+------+---------+--------+
| empno | ename | job | mgr | sal | deptno |
+-------+--------+-----------+------+---------+--------+
| 7839 | KING | PRESIDENT | NULL | 5000.00 | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 3000.00 | 20 |
| 7902 | FORD | ANALYST | 7566 | 3000.00 | 20 |
| 7521 | WARD | SALESMAN | 7698 | 1250.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1250.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1500.00 | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 1600.00 | 30 |
| 7934 | MILLER | CLERK | 7782 | 1300.00 | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1100.00 | 20 |
| 7782 | CLARK | MANAGER | 7839 | 2450.00 | 10 |
| 7698 | BLAKE | MANAGER | 7839 | 2850.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 2975.00 | 20 |
+-------+--------+-----------+------+---------+--------+
12 rows in set (0.00 sec)
5， 分页查询
分页显示的作用是把查询结果放到多页中进行显示
语法：
select 字段1, ..., 字段n from 表名 where 条件表达式 order by 字段x limit 显示记录条数 offset 记录索引
记录索引从0开始， 表示第一条记录
由公式：记录索引 = 显示记录条数 * （查询第N页 - 1），可判断当前显示的是多少页
# 从0条记录开始显示三条记录
mysql> select empno, ename, job, mgr, sal, deptno from emp where sal > 1000 order by mgr limit 3 offset 0;
+-------+-------+-----------+------+---------+--------+
| empno | ename | job | mgr | sal | deptno |
+-------+-------+-----------+------+---------+--------+
| 7839 | KING | PRESIDENT | NULL | 5000.00 | 10 |
| 7902 | FORD | ANALYST | 7566 | 3000.00 | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 3000.00 | 20 |
+-------+-------+-----------+------+---------+--------+
3 rows in set (0.00 sec)
# 表示从第6号索引开始显示三条记录
mysql> select empno, ename, job, mgr, sal, deptno from emp where sal > 1000 order by mgr limit 3 offset 6;
+-------+--------+----------+------+---------+--------+
| empno | ename | job | mgr | sal | deptno |
+-------+--------+----------+------+---------+--------+
| 7844 | TURNER | SALESMAN | 7698 | 1500.00 | 30 |
| 7934 | MILLER | CLERK | 7782 | 1300.00 | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1100.00 | 20 |
+-------+--------+----------+------+---------+--------+
3 rows in set (0.00 sec)
# 表示从第7号索引开始显示三条记录
mysql> select empno, ename, job, mgr, sal, deptno from emp where sal > 1000 order by mgr limit 3 offset 7;
+-------+--------+---------+------+---------+--------+
| empno | ename | job | mgr | sal | deptno |
+-------+--------+---------+------+---------+--------+
| 7934 | MILLER | CLERK | 7782 | 1300.00 | 10 |
| 7876 | ADAMS | CLERK | 7788 | 1100.00 | 20 |
| 7566 | JONES | MANAGER | 7839 | 2975.00 | 20 |
+-------+--------+---------+------+---------+--------+
3 rows in set (0.00 sec)6， 聚合查询
通过聚合函数进行的查询就是聚合查询，如计算总数和平均数等聚合函数
语法：select 聚合函数(字段) from 表名 where 条件表达式；
6-1， count函数
用于统计记录条数
# 当count的统计字段为"*"时，统计整个表记录条数，结果中列名为count(*)
mysql> select count(*) from emp where sal > 1000;
+----------+
| count(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)
# 给返回字段取别名
mysql> select count(*) recode_num from emp where sal > 1000;
+------------+
| recode_num |
+------------+
| 12 |
+------------+
1 row in set (0.00 sec)
# 当count统计指定给出时，统计该列不包括Null的记录条数
mysql> select count(comm) comm_num from emp where sal > 1000;
+----------+
| comm_num |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
# 统计mgr不为Null, 且值不同的记录条数
mysql> select count(distinct mgr) distinct_mgr from emp where sal > 1000;
+--------------+
| distinct_mgr |
+--------------+
| 5 |
+--------------+
1 row in set (0.00 sec)6-2， sum函数
用于计算统计列的总和， 该列的值必须为数字类型
# 统计deptno=20的员工的工资总和，sum_sal为别名
mysql> select sum(sal) sum_sal from emp where deptno=20;
+----------+
| sum_sal |
+----------+
| 10875.00 |
+----------+
1 row in set (0.00 sec)6-3， avg函数
用于计算该列的平均值， 该列的值必须为数字类型
# 统计deptno=10的员工的平均工资， avg_sal为别名
mysql> select avg(sal) avg_sal from emp where deptno=10;
+-------------+
| avg_sal |
+-------------+
| 2916.666667 |
+-------------+
1 row in set (0.00 sec)6-4， min函数
用于计算该列的最小值， 数据类型没有要求
# 返回sal最小值，min_sal为别名
mysql> select min(sal) min_sal from emp;
+---------+
| min_sal |
+---------+
| 800.00 |
+---------+
1 row in set (0.00 sec)6-5， max函数
用于计算该列的最大值， 数据类型没有要求
返回sal列最大值，max_sal为别名
mysql> select max(sal) max_sal from emp;
+---------+
| max_sal |
+---------+
| 5000.00 |
+---------+
1 row in set (0.00 sec)
说明：若该列的数据为字符串类型，则按ASCII码排序6-6，分组聚合(group by)
分组聚合通过group by字句将相同字段数据分到一组，再对分组后数据通过聚合函数计算每个组的值
语法：select 字段1, ..., 字段n， 聚合函数(字段x) from 表名 where 条件表达式 group by 字段1, ..., 字段n;
注意： select中的字段必须包含于group by中的字段
# 按deptno进行分组，分组后计算各组的平均工资，不统计detp=10的部门
mysql> select deptno, avg(sal) sal_avg from emp where deptno <> 10 group by deptno;
+--------+-------------+
| deptno | sal_avg |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
2 rows in set (0.00 sec)
# 按deptno, mgr进行分组，分组后计算各组的平均工资
mysql> select deptno, mgr, avg(sal) sal_avg from emp group by deptno, mgr;
+--------+------+-------------+
| deptno | mgr | sal_avg |
+--------+------+-------------+
| 20 | 7902 | 800.000000 |
| 30 | 7698 | 1310.000000 |
| 20 | 7839 | 2975.000000 |
| 30 | 7839 | 2850.000000 |
| 10 | 7839 | 2450.000000 |
| 20 | 7566 | 3000.000000 |
| 10 | NULL | 5000.000000 |
| 20 | 7788 | 1100.000000 |
| 10 | 7782 | 1300.000000 |
+--------+------+-------------+
9 rows in set (0.00 sec)
6-7， 过滤聚合函数结果查询(having)
语法：select 字段1, ..., 字段n from emp where 条件表达式 group by 字段1, ..., 字段n having 聚合函数(字段x) order by 排序方式
注意： select中的字段必须包含于group by中的字段
having是用于过滤聚合函数结果的方式
# 过滤除sal > 1000的员工，然后在按deptno分组，分组后计算组平均工资，再过滤出平均值大于1000的
mysql> select deptno, avg(sal) sal_avg from emp where sal > 1000 group by deptno having avg(sal) > 1000;
+--------+-------------+
| deptno | sal_avg |
+--------+-------------+
| 30 | 1690.000000 |
| 20 | 2518.750000 |
| 10 | 2916.666667 |
+--------+-------------+
3 rows in set (0.00 sec)7， 多表查询
语法：select * from A表 A表别名, B表 B表别名;
多表查询又叫卡迪尔查询，一次查询所有表的数据；
A表每行与B表每行结合，总返回A表行*B表行条记录
每行的字段为A表的列+B表的列
mysql> select * from dept, emp;
+--------+------------+----------+-------+--------+-----------+------+---------------------+---------+---------+--------+
| deptno | dname | loc | empno | ename | job | mgr | hiredate | sal | comm | deptno |
+--------+------------+----------+-------+--------+-----------+------+---------------------+---------+---------+--------+
| 10 | ACCOUNTING | NEW YORK | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 20 | RESEARCH | DALLAS | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 30 | SALES | CHICAGO | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 40 | OPERATIONS | BOSTON | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 20 | RESEARCH | DALLAS | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 40 | OPERATIONS | BOSTON | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 20 | RESEARCH | DALLAS | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 40 | OPERATIONS | BOSTON | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 20 | RESEARCH | DALLAS | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 30 | SALES | CHICAGO | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 40 | OPERATIONS | BOSTON | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 |
| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 30 | SALES | CHICAGO | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 40 | OPERATIONS | BOSTON | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 30 | SALES | CHICAGO | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 40 | OPERATIONS | BOSTON | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 |
| 10 | ACCOUNTING | NEW YORK | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 20 | RESEARCH | DALLAS | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 40 | OPERATIONS | BOSTON | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 |
| 10 | ACCOUNTING | NEW YORK | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 20 | RESEARCH | DALLAS | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 30 | SALES | CHICAGO | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 40 | OPERATIONS | BOSTON | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 |
| 10 | ACCOUNTING | NEW YORK | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 20 | RESEARCH | DALLAS | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 30 | SALES | CHICAGO | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 40 | OPERATIONS | BOSTON | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 |
| 10 | ACCOUNTING | NEW YORK | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 20 | RESEARCH | DALLAS | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 30 | SALES | CHICAGO | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
| 40 | OPERATIONS | BOSTON | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 |
+--------+------------+----------+-------+--------+-----------+------+---------------------+---------+---------+--------+
56 rows in set (0.00 sec)7-1， 给表取别名
多表查询时，当多张表存在相同的列时，名称会重复，不利于观察，通过取别名方式进行优化
mysql> select e.empno emp_empno, e.ename emp_ename, e.sal emp_sal, d.deptno dept_deptno
-> from emp e, dept d # emp e：表示emp取别名为e, dept取别名为d
-> where e.sal > 1000 # 条件表达式e.sal表示通过别名获取sal字段
-> limit 5 offset 6; # 分页查询
+-----------+-----------+---------+-------------+
| emp_empno | emp_ename | emp_sal | dept_deptno |
+-----------+-----------+---------+-------------+
| 7521 | WARD | 1250.00 | 30 |
| 7521 | WARD | 1250.00 | 40 |
| 7566 | JONES | 2975.00 | 10 |
| 7566 | JONES | 2975.00 | 20 |
| 7566 | JONES | 2975.00 | 30 |
+-----------+-----------+---------+-------------+
5 rows in set (0.00 sec)8， 连接查询
连接查询也是一种多表查询，先确定一个主表结果集，在选择性的把其他表的行连接到主表结果集上
8-1， 内连接(INNER JOIN)
语法：select 字段1，字段2，... form 主表 inner join 其他表 on 条件表达式；
通过内连接返回的数据在两张表都能找到
mysql> select e.empno emp_empno, e.ename emp_ename, e.sal emp_sal, d.deptno dept_deptno
-> from emp e # 确定主表
-> inner join dept d # 确定连接表
-> on e.deptno = d.deptno # 条件表达式，即通过关联两张表的字段
-> where e.sal > 1000 # 过滤条件
-> order by e.sal; # 排序
+-----------+-----------+---------+-------------+
| emp_empno | emp_ename | emp_sal | dept_deptno |
+-----------+-----------+---------+-------------+
| 7876 | ADAMS | 1100.00 | 20 |
| 7521 | WARD | 1250.00 | 30 |
| 7654 | MARTIN | 1250.00 | 30 |
| 7934 | MILLER | 1300.00 | 10 |
| 7844 | TURNER | 1500.00 | 30 |
| 7499 | ALLEN | 1600.00 | 30 |
| 7782 | CLARK | 2450.00 | 10 |
| 7698 | BLAKE | 2850.00 | 30 |
| 7566 | JONES | 2975.00 | 20 |
| 7788 | SCOTT | 3000.00 | 20 |
| 7902 | FORD | 3000.00 | 20 |
| 7839 | KING | 5000.00 | 10 |
+-----------+-----------+---------+-------------+
12 rows in set (0.00 sec)8-2， 左连接（LEFT OUTER JOIN）
语法：select 字段1，字段2，... form 主表 left join 其他表 on 条件表达式；
左连接将以主表数据为准进行返回
mysql> select e.empno emp_empno, e.ename emp_ename, e.sal emp_sal, d.deptno dept_deptno
-> from emp e
-> left outer join dept d
-> on d.deptno = e.deptno
-> where sal > 1000
-> order by e.sal;
+-----------+-----------+---------+-------------+
| emp_empno | emp_ename | emp_sal | dept_deptno |
+-----------+-----------+---------+-------------+
| 7876 | ADAMS | 1100.00 | 20 |
| 7521 | WARD | 1250.00 | 30 |
| 7654 | MARTIN | 1250.00 | 30 |
| 7934 | MILLER | 1300.00 | 10 |
| 7844 | TURNER | 1500.00 | 30 |
| 7499 | ALLEN | 1600.00 | 30 |
| 7782 | CLARK | 2450.00 | 10 |
| 7698 | BLAKE | 2850.00 | 30 |
| 7566 | JONES | 2975.00 | 20 |
| 7788 | SCOTT | 3000.00 | 20 |
| 7902 | FORD | 3000.00 | 20 |
| 7839 | KING | 5000.00 | 10 |
+-----------+-----------+---------+-------------+
12 rows in set (0.00 sec)8-3， 右连接(RIGHT OUTER JOIN)
将以其他表的数据为准进行返回， 主表中的其他字段补NULL
# 因为使用的右连接，以dept表为准返回，所以返回了dept_deptno = 40的行，
# 由于主表中没有dept_deptno = 40的行， 所以主表的字段补NULL
mysql> select e.empno emp_empno, e.ename emp_ename, e.sal emp_sal, d.deptno dept_deptno
-> from emp e
-> right outer join dept d
-> on d.deptno = e.deptno
-> order by e.sal;
+-----------+-----------+---------+-------------+
| emp_empno | emp_ename | emp_sal | dept_deptno |
+-----------+-----------+---------+-------------+
| NULL | NULL | NULL | 40 |
| 7369 | SMITH | 800.00 | 20 |
| 7900 | JAMES | 950.00 | 30 |
| 7876 | ADAMS | 1100.00 | 20 |
| 7521 | WARD | 1250.00 | 30 |
| 7654 | MARTIN | 1250.00 | 30 |
| 7934 | MILLER | 1300.00 | 10 |
| 7844 | TURNER | 1500.00 | 30 |
| 7499 | ALLEN | 1600.00 | 30 |
| 7782 | CLARK | 2450.00 | 10 |
| 7698 | BLAKE | 2850.00 | 30 |
| 7566 | JONES | 2975.00 | 20 |
| 7788 | SCOTT | 3000.00 | 20 |
| 7902 | FORD | 3000.00 | 20 |
| 7839 | KING | 5000.00 | 10 |
+-----------+-----------+---------+-------------+8-4， 全连接查询（FULL OUTER JOIN）
在mysql中不支持full outer join，可以通过union方式实现
全连接查询返回的是左右表的并集数据
语法：
select 字段1, ..., 字段n from 主表 left outer join 其他表 on 条件表达式
union
select 字段1, ..., 字段n from 主表 right outer join 其他表 on 条件表达式；
mysql> select e.empno emp_empno, e.ename emp_ename, d.deptno dept_deptno from emp e left outer join dept d on d.deptno = e.deptno
-> union
-> select e.empno emp_empno, e.ename emp_ename, d.deptno dept_deptno from emp e right outer join dept d on d.deptno = e.deptno;
+-----------+-----------+-------------+
| emp_empno | emp_ename | dept_deptno |
+-----------+-----------+-------------+
| 7369 | SMITH | 20 |
| 7499 | ALLEN | 30 |
| 7521 | WARD | 30 |
| 7566 | JONES | 20 |
| 7654 | MARTIN | 30 |
| 7698 | BLAKE | 30 |
| 7782 | CLARK | 10 |
| 7788 | SCOTT | 20 |
| 7839 | KING | 10 |
| 7844 | TURNER | 30 |
| 7876 | ADAMS | 20 |
| 7900 | JAMES | 30 |
| 7902 | FORD | 20 |
| 7934 | MILLER | 10 |
| NULL | NULL | 40 |
+-----------+-----------+-------------+
15 rows in set (0.00 sec)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
