表的增删查改简称CRUD：Create（新增），Retrieve（查找），Update（修改），Delete（删除）

CRUD的操作对象是对表当中的数据，是典型的DML（Data Manipulation Language）数据操作语言。

Create

insert

语法：INSERT INTO 表名 VALUES (值1,值2,值3,...);

示例：

mysql> create table if not exists user ( 
    -> name varchar(10), 
    -> age int, 
    -> gender char(1) 
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user (name, age) values ('lisi', 22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into user (name) values ('zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values ('wangwu', 20, '男');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values ('zhanliu', 24, '女'), ('qianqi', 19, '男');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select* from user;
+----------+------+--------+
| name     | age  | gender |
+----------+------+--------+
| lisi     |   22 | NULL   |
| zhangsan | NULL | NULL   |
| wangwu   |   20 | 男     |
| zhanliu  |   24 | 女     |
| qianqi   |   19 | 男     |
+----------+------+--------+
5 rows in set (0.00 sec)

插入否则更新

向表中插入记录时，如果待插入记录中的主键或唯一键已经存在，那么就会因为主键冲突或唯一键冲突导致插入失败。如下：

mysql> desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
| id    | int         | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into stu values ('zhangsan',23, 1022);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stu values ('lisi',25, 1222);
Query OK, 1 row affected (0.00 sec)

mysql> insert into stu values('zhaoliu', 27, 1222);
ERROR 1062 (23000): Duplicate entry '1222' for key 'stu.PRIMARY'

mysql> select* from stu;
+----------+------+------+
| name     | age  | id   |
+----------+------+------+
| zhangsan |   20 |    1 |
| zhangsan |   23 | 1022 |
| lisi     |   25 | 1222 |
+----------+------+------+
3 rows in set (0.01 sec)

mysql> insert into stu values('lisi', 25, 1222) on duplicate key update name='zhaoliu', age=29;
Query OK, 2 rows affected (0.00 sec)

mysql> select* from stu;
+----------+------+------+
| name     | age  | id   |
+----------+------+------+
| zhangsan |   20 |    1 |
| zhangsan |   23 | 1022 |
| zhaoliu  |   29 | 1222 |
+----------+------+------+
3 rows in set (0.00 sec)

replace

• 如果表中没有冲突数据，则直接插入数据
• 如果表中有冲突数据，则先将表中的冲突数据删除，然后再插入数据

要达到上述效果，只需要在插入数据时将SQL语句中的INSERT改为REPLACE即可。比如:

mysql> replace into stu values('qianqi', 25, 100);
Query OK, 1 row affected (0.00 sec)

mysql> select* from stu;
+----------+------+------+
| name     | age  | id   |
+----------+------+------+
| zhangsan |   20 |    1 |
| qianqi   |   25 |  100 |
| zhangsan |   23 | 1022 |
| zhaoliu  |   29 | 1222 |
+----------+------+------+
4 rows in set (0.00 sec)

mysql> replace into stu values('wuba', 26, 100);
Query OK, 2 rows affected (0.00 sec)

mysql> select* from stu;
+----------+------+------+
| name     | age  | id   |
+----------+------+------+
| zhangsan |   20 |    1 |
| wuba     |   26 |  100 |
| zhangsan |   23 | 1022 |
| zhaoliu  |   29 | 1222 |
+----------+------+------+
4 rows in set (0.00 sec)

Retrieve

语法：SELECT 字段 FROM 表名 [WHERE 条件] [ORDER BY 排序列表] [LIMIT 开始位置, 获取数量];

注意：查询SQL中各语句的执行顺序为：where、select、order by、limit

select 列

查询所有

语法：SELECT * FROM 表名;

指定列查询

语法：SELECT 字段1, 字段2, 字段3 FROM 表名;

查询字段为表达式

语法：SELECT 字段1, 字段2, 字段3, 字段1+字段2 AS 字段4 FROM 表名;

查询结果指定别名

语法：SELECT 字段1 AS 别名1, 字段2 AS 别名2, 字段3 AS 别名3 FROM 表名;

结果去重

语法：SELECT DISTINCT 字段1, 字段2, 字段3 FROM 表名;

示例：

mysql> create table if not exists grades (
    -> name varchar(10) not null,
    -> chinese float default 0.0,
    -> math float default 0.0.
    -> english float default 0.0
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into grades values('zhangsan', 23,56,98);
Query OK, 1 row affected (0.01 sec)

mysql> insert into grades values('lisi', 33,66,29);
Query OK, 1 row affected (0.00 sec)

mysql> insert into grades values('wangwu', 22,76,19);
Query OK, 1 row affected (0.01 sec)

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      33 |   66 |      29 |
| wangwu   |      22 |   76 |      19 |
+----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select chinese from grades;
+---------+
| chinese |
+---------+
|      23 |
|      33 |
|      22 |
+---------+
3 rows in set (0.00 sec)

mysql> select name, chinese+math+english from grades;
+----------+----------------------+
| name     | chinese+math+english |
+----------+----------------------+
| zhangsan |                  177 |
| lisi     |                  128 |
| wangwu   |                  117 |
+----------+----------------------+
3 rows in set (0.00 sec)

mysql> select name, chinese+math+english as 总分 from grades;
+----------+--------+
| name     | 总分   |
+----------+--------+
| zhangsan |    177 |
| lisi     |    128 |
| wangwu   |    117 |
+----------+--------+
3 rows in set (0.00 sec)

where 字句

where子句中可以指明一个或多个筛选条件，各个筛选条件之间用逻辑运算符AND或OR进行关联，下面给出了where子句中常用的比较运算符和逻辑运算符。 注意： 在where子句中不能使用select中指定的别名：查询数据时是先根据where子句筛选出符合条件的记录，然后再将符合条件的记录作为数据源来依次执行select语句。

也就是说，where子句的执行是先于select语句的，所以在where子句中不能使用别名，如果在where子句中使用别名，那么在查询数据时就会产生报错。

比较运算符：

运算符	描述
=	等于:NULL不安全，例如NULL=NULL的结果是NULL而不是TRUE(1)
<=>	等于:NULL安全，例如NULL<=>NULL的结果就是TRUE(1)
!=、<>	不等于
<	小于
>	大于
<=	小于等于
>=	大于等于
BETWEEN A AND B	在A和B之间
IN (A, B, C, ...)	在A,B,C,...中
LIKE	模糊匹配：%表示任意多个字符（包括0个），_表示任意一个字符
IS NULL	为NULL
IS NOT NULL	不为NULL

逻辑运算符：

运算符	描述
AND	逻辑与，同时满足两个条件
OR	逻辑或，满足其中一个条件
NOT	逻辑非，取反

示例：

mysql> select name, chinese from grades where chinese>=20 and chinese<=30;
+----------+---------+
| name     | chinese |
+----------+---------+
| zhangsan |      23 |
| wangwu   |      22 |
+----------+---------+
2 rows in set (0.00 sec)

mysql> select name, chinese from grades where chinese<=30 or math<=30 or english<=30;
+----------+---------+
| name     | chinese |
+----------+---------+
| zhangsan |      23 |
| lisi     |      33 |
| wangwu   |      22 |
+----------+---------+
3 rows in set (0.00 sec)

mysql> select name, math from grades where math in (66, 76);
+--------+------+
| name   | math |
+--------+------+
| lisi   |   66 |
| wangwu |   76 |
+--------+------+
2 rows in set (0.00 sec)

mysql> select name, math from grades where name like 'l%';
+------+------+
| name | math |
+------+------+
| lisi |   66 |
+------+------+
1 row in set (0.01 sec)

order by 子句

order by子句用于对查询结果进行排序，语法:

• ASC：升序排序，默认值
• DESC：降序排序

SELECT 字段1, 字段2, 字段3 FROM 表名 ORDER BY 字段1, 字段2, 字段3;
SELECT 字段1, 字段2, 字段3 FROM 表名 ORDER BY 字段1, 字段2, 字段3 DESC;
SELECT 字段1, 字段2, 字段3 FROM 表名 ORDER BY 字段1, 字段2, 字段3 ASC;
SELECT 字段1, 字段2, 字段3 FROM 表名 ORDER BY 字段1, 字段2, 字段3 DESC, 字段1, 字段2, 字段3 ASC;

注意： 在order by子句中可以使用select中指定的别名：查询数据时是先根据where子句筛选出符合条件的记录。 然后再将符合条件的记录作为数据源来依次执行select语句，最后再通过order by子句对select语句的执行结果进行排序。

也就是说，order by子句的执行是在select语句之后的，所以在order by子句中可以使用别名。

mysql> select name, chinese+math+english as 总分 from grades order by chinese+math+english desc;
+----------+--------+
| name     | 总分   |
+----------+--------+
| zhangsan |    177 |
| lisi     |    128 |
| wangwu   |    117 |
+----------+--------+
3 rows in set (0.00 sec)

mysql> select name, chinese, math, english from grades order by chinese asc, math desc, english asc;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| wangwu   |      22 |   76 |      19 |
| zhangsan |      23 |   56 |      98 |
| lisi     |      33 |   66 |      29 |
+----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select name, math from grades where math>=50 and math<=70 order by math asc;
+----------+------+
| name     | math |
+----------+------+
| zhangsan |   56 |
| lisi     |   66 |
+----------+------+
2 rows in set (0.00 sec)

limit 子句

limit子句用于限制查询结果的数量，语法：

• limit n：限制查询结果的数量为n条
• limit m,n：从第m条开始，限制查询结果的数量为n条

mysql> select* from grades limit 3;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      33 |   66 |      29 |
| wangwu   |      22 |   76 |      19 |
+----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select* from grades limit 2;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      33 |   66 |      29 |
+----------+---------+------+---------+
2 rows in set (0.00 sec)

mysql> select* from grades limit 2,3;
+--------+---------+------+---------+
| name   | chinese | math | english |
+--------+---------+------+---------+
| wangwu |      22 |   76 |      19 |
+--------+---------+------+---------+
1 row in set (0.00 sec)

Update

update语句用于更新表中的数据，对查询到的结果进行列值更新，语法：UPDATE table_name SET column1=expr1 [, column2=expr2] ... [WHERE ...] [ORDER BY ...] [LIMIT ...];

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      33 |   66 |      29 |
| wangwu   |      22 |   76 |      19 |
+----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select name, english from grades where english>=20 and english<=100 order by english desc;
+----------+---------+
| name     | english |
+----------+---------+
| zhangsan |      98 |
| lisi     |      29 |
+----------+---------+
2 rows in set (0.00 sec)

mysql> update grades set chinese=60 where name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      60 |   66 |      29 |
| wangwu   |      22 |   76 |      19 |
+----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> update grades set math=math+30 order by chinese+math+english asc limit 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      60 |   96 |      29 |
| wangwu   |      22 |  106 |      19 |
+----------+---------+------+---------+
3 rows in set (0.00 sec)

Delete

语法：DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      60 |   96 |      29 |
| wangwu   |      22 |  106 |      19 |
+----------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> delete from grades where name='wangwu';
Query OK, 1 row affected (0.01 sec)

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      60 |   96 |      29 |
+----------+---------+------+---------+
2 rows in set (0.00 sec)

插入查询结果

使用INSERT INTO ... SELECT语句将查询结果插入到表中，语法：INSERT INTO table_name SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];

示例：

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      60 |   96 |      29 |
+----------+---------+------+---------+
2 rows in set (0.00 sec)

mysql> insert into grades select name, chinese, math, english from grades where english=98;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| zhangsan |      23 |   56 |      98 |
| lisi     |      60 |   96 |      29 |
| zhangsan |      23 |   56 |      98 |
+----------+---------+------+---------+
3 rows in set (0.00 sec)

聚合函数

聚合函数用于对查询结果进行统计，常用的聚合函数有：

• count()：统计查询结果中的记录数
• sum()：统计查询结果中的字段的和
• avg()：统计查询结果中的字段的平均值
• max()：统计查询结果中的字段的最大值
• min()：统计查询结果中的字段的最小值

mysql> select * from grades;
+----------+---------+------+---------+
| name     | chinese | math | english |
+----------+---------+------+---------+
| lisi     |      60 |  100 |      29 |
| zhangsan |      23 |   56 |      98 |
+----------+---------+------+---------+
2 rows in set (0.00 sec)

mysql> select count(*) as 总数 from grades;
+--------+
| 总数   |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

mysql> select count(math) as 数学总数 from grades;
+-----------------+
| 数学总数      |
+-----------------+
|               2 |
+-----------------+
1 row in set (0.00 sec)

mysql> select sum(math) as 数学总成绩 from grades;
+-----------------+
| 数学总成绩      |
+-----------------+
|             156 |
+-----------------+
1 row in set (0.00 sec)

mysql> select avg(math) as 数学平均分 from grades;
+-----------------+
| 数学平均分      |
+-----------------+
|              78 |
+-----------------+
1 row in set (0.00 sec)

mysql> select max(math) as 数学最高分 from grades;
+-----------------+
| 数学最高分      |
+-----------------+
|             100 |
+-----------------+
1 row in set (0.00 sec)

mysql> select min(math) as 数学最低分 from grades;
+-----------------+
| 数学最低分      |
+-----------------+
|              56 |
+-----------------+
1 row in set (0.00 sec)

分组查询

分组查询，即对查询结果进行分组，然后对每个分组进行统计，常用的分组函数有：

• group by：对查询结果进行分组
• having：对分组后的结果进行过滤

使用测试表示例：

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

查看完整信息：

mysql> use scott;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.00 sec)

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)

mysql> select * from emp;
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| empno  | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
| 007369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
| 007499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
| 007521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
| 007566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
| 007654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
| 007698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
| 007782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
| 007788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |    NULL |     20 |
| 007839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
| 007844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
| 007876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |    NULL |     20 |
| 007900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
| 007902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
| 007934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
+--------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)

mysql> select * from salgrade;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

group by

语法：SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];

1. 显示每个部门的平均工资和最高工资

mysql> select deptno, avg(sal) as '平均薪资', max(sal) as '最高薪资' from emp group by deptno order by deptno asc;
+--------+--------------+--------------+
| deptno | 平均薪资     | 最高薪资     |
+--------+--------------+--------------+
|     10 |  2916.666667 |      5000.00 |
|     20 |  2175.000000 |      3000.00 |
|     30 |  1566.666667 |      2850.00 |
+--------+--------------+--------------+
3 rows in set (0.00 sec)

2. 显示每个部门的每种岗位的平均工资和最低工资

mysql> select deptno, job, avg(sal) as '平均薪资', min(sal) as '最低薪资' from emp group by deptno, job order by deptno;
+--------+-----------+--------------+--------------+
| deptno | job       | 平均薪资     | 最低薪资     |
+--------+-----------+--------------+--------------+
|     10 | CLERK     |  1300.000000 |      1300.00 |
|     10 | MANAGER   |  2450.000000 |      2450.00 |
|     10 | PRESIDENT |  5000.000000 |      5000.00 |
|     20 | ANALYST   |  3000.000000 |      3000.00 |
|     20 | CLERK     |   950.000000 |       800.00 |
|     20 | MANAGER   |  2975.000000 |      2975.00 |
|     30 | CLERK     |   950.000000 |       950.00 |
|     30 | MANAGER   |  2850.000000 |      2850.00 |
|     30 | SALESMAN  |  1400.000000 |      1250.00 |
+--------+-----------+--------------+--------------+
9 rows in set (0.00 sec)

having

语法：SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] HAVING condition [order by ...] [LIMIT ...];

having子句和where子句的区别

• where子句放在表名后面，而having子句必须搭配group by子句使用，放在group by子句的后面。
• where子句是对整表的数据进行筛选，having子句是对分组后的数据进行筛选。
• where子句中不能使用聚合函数和别名，而having子句中可以使用聚合函数和别名。

SQL中各语句的执行顺序:

• 根据where子句筛选出符合条件的记录。
• 根据group by子句对数据进行分组。
• 将分组后的数据依次执行select语句。
• 根据having子句对分组后的数据进行进一步筛选。
• 根据order by子句对数据进行排序。
• 根据limit子句筛选若干条记录进行显示。

3. 显示平均工资低于2000的部门和它的平均工资

mysql> select deptno, avg(sal) as avgdept from emp group by deptno having avgdept<2000;
+--------+-------------+
| deptno | avgdept     |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)