目录
- 表的增删查改简称CRUD:Create(新增),Retrieve(查找),Update(修改),Delete(删除)。
- CRUD的操作对象是对表当中的数据,是典型的DML(Data Manipulation Language)数据操作语言。
Create(新增)
基本语法:
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- SQL中的每个value_list都表示插入的一条记录,每个value_list都由若干待插入的列值组成。
- SQL中的column列表,用于指定每个value_list中的各个列值应该插入到表中的哪一列。
创建一个学生表,表当中包含自增长的主键id、学号、姓名和QQ号字段!!!
单行数据 + 全列插入
- 插入两条记录,value_list 数量必须和定义表的列的数量及顺序一致
- 注意,这里在插入的时候,也可以不用指定id(当然,那时候就需要明确插入数据到那些列了),那么mysql会使用默认的值进行自增。
也可以一次性插入多条数据!!!
多行数据 + 指定列插入
- 插入两条记录,value_list 数量必须和指定列数量及顺序一致
也可以指定列插入,指定 学号(sn),名字(name)字段插入表!!!
说明一下: 在插入记录时,只有允许为空的列或自增长字段可以不指定值插入,不允许为空的列必须指定值插入,否则会报错。
插入否则更新
可以选择性的进行同步更新操作!!!
- 如果表中没有冲突数据,则直接插入数据。
- 如果表中有冲突数据,则将表中的数据进行更新。
基本语法:
INSERT ... ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- SQL中INSERT之后语法与之前的INSERT语句相同。
- UPDATE后面的column=value,表示当插入记录出现冲突时需要更新的列值。
向学生表中插入记录时,如果没有出现主键冲突则直接插入记录,如果出现了主键冲突,则将表中冲突记录的学号和姓名进行更新。
执行插入否则更新的SQL后,可以通过受影响的数据行数来判断本次数据的插入情况:
- 0 rows affected:表中有冲突数据,但冲突数据的值和指定更新的值相同。
- 1 row affected:表中没有冲突数据,数据直接被插入。
- 2 rows affected:表中有冲突数据,并且数据已经被更新。
替换
- 如果表中没有冲突数据,则直接插入数据。
- 如果表中有冲突数据,则先将表中的冲突数据删除,然后再插入数据。
基本语法:
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- SQL中的每个value_list都表示插入的一条记录,每个value_list都由若干待插入的列值组成。
- SQL中的column列表,用于指定每个value_list中的各个列值应该插入到表中的哪一列。
插入一条数据!!!
执行替换数据的SQL后,也可以通过受影响的数据行数来判断本次数据的插入情况:
- 1 row affected:表中没有冲突数据,数据直接被插入。
- 2 rows affected:表中有冲突数据,冲突数据被删除后重新插入。
Retrieve(查找)
基本语法:
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- { }中的 | 代表可以选择左侧的语句或右侧的语句。
- 使用函数:例如日期函数、字符串函数等,如
SELECT DATE(column_date) FROM table_name;
- 条件筛选:通过
WHERE
子句,如SELECT * FROM table_name WHERE condition;
- 分组:使用
GROUP BY
子句对数据进行分组,如SELECT column1, SUM(column2) FROM table_name GROUP BY column1;
- 排序:通过
ORDER BY
子句对结果进行排序,如SELECT * FROM table_name ORDER BY column1 ASC/DESC;
- 连接表:可以实现内连接、外连接等多种连接操作来关联多个表获取数据。
- 选择特定列:SELECT column1, column2,... FROM table_name;
- 选择所有列:SELECT * FROM table_name;
为了进行演示,下面创建一个成绩表,表当中包含自增长的主键id、姓名以及该同学的语文成绩、数学成绩和英语成绩字段!!!
接下来向表中插入几条测试记录,以供我们进行测试查找!!!
SELECT 列
全列查询
SELECT * FROM table_name;
说明一下: 通常情况下不建议使用*
进行全列查询,因为被查询到的数据需要通过网络从MySQL服务器传输到本主机,查询的列越多也就意味着需要传输的数据量越大,此外,进行全列查询还可能会影响到索引的使用。
指定列查询
SELECT column1, column2,... FROM table_name;
查询字段为表达式
查询数据时,column列表中除了能罗列表中存在的列名外,我们也可以将表达式罗列到column列表中。
因为select不仅能够用来查询数据,还可以用来计算某些表达式或执行某些函数。
column列表中的表达式可以包含表中已有的字段,这时每当一条记录被筛选出来时,就会将记录中对应的列值提供给表达式进行计算。
column列表中的表达式中也可以包含多个表中已有的字段,这时我们就可以通过表达式计算出更多有意义的数据。
为查询结果指定别名
SELECT column [AS] alias_name [...] FROM table_name;
查询成绩表中的数据时,将每条记录中的三科成绩相加,然后将计算结果对应的列指定别名为“总分”。
结果去重
select后面带上distinct
如果想要对查询结果进行去重操作,可以在SQL中的select后面带上distinct。
WHERE 条件
- 如果在查询数据时没有指定where子句,那么会直接将表中所有的记录作为数据源来依次执行select语句。
- 如果在查询数据时指定了where子句,那么在查询数据时会先根据where子句筛选出符合条件的记录,然后将符合条件的记录作为数据源来依次执行select语句。
where子句中可以指明一个或多个筛选条件,各个筛选条件之间用逻辑运算符AND或OR进行关联,下面给出了where子句中常用的比较运算符和逻辑运算符。
比较运算符:
逻辑运算符:
查询英语不及格的同学及其英语成绩
select name,english from grade_table where english<60;
查询语文成绩在80到100分的同学及其语文成绩
select name,chinese from grade_table where chinese>=80 and chinese<100;
这里也可以使用BETWEEN a0 AND a1
来指明语文成绩的的所在区间。
查询数学成绩是30或40或50或100分的同学及其数学成绩
select name,math from grade_table where math=30 or math=40 or math=50 or math=100;
这里也可以通过IN(58, 59, 98, 99)
的方式来判断数学成绩是否符合筛选要求。
分别查询姓孙的同学和孙某同学
在where子句中通过模糊匹配来判断当前同学是否姓孙(需要用到%
来匹配多个字符),在select的column列表中指明要查询的列为姓名。
select name from grade_table where name like "孙%";
在where子句中通过模糊匹配来判断当前同学是否为孙某(需要用到_
来严格匹配单个字符),在select的column列表中指明要查询的列为姓名。
select name from grade_table where name like "孙_";
查询语文成绩好于英语成绩的同学
select name,chinese,english from grade_table where chinese>english;
查询总成绩在200分以下的同学
select name,chinese+math+english 总分 from grade_table where chinese+math+english<200;
where子句的执行是先于select语句的,所以在where子句中不能使用别名,如果在where子句中使用别名,那么在查询数据时就会产生报错。
查询语文成绩大于80分并且不姓孙的同学
select name,chinese from grade_table where chinese>80 and name not like "孙%";
查询孙某同学否则要求总成绩大于200分并且语文成绩小于数学成绩并且英语成绩大于80分
该题目的要求是查询成绩,被查询的人要么是孙某,要么总成绩大于200分并且语文成绩小于数学成绩并且英语成绩大于80分,查询时需要用到模糊匹配、表达式查询和逻辑运算符。
select name,chinese,math,english,chinese+math+english 总分 from grade_table
where (name like '孙_') or (chinese+math+english>200 and chinese<math and english>80);
NULL的查询
这里用之前演示新增数据的学生表来演示NULL查询
查询QQ号已知的同学
select name,qq from student_table where qq is not null;
查询QQ号未知的同学
注意:在与NULL值作比较的时候应该使用<=>
运算符,使用=
运算符无法得到正确的查询结果。
- 因为
=
运算符是NULL不安全的,使用=
运算符将任何值与NULL作比较,得到的结果都是NULL。 - 但是
<=>
运算符是NULL安全的,使用<=>
运算符将NULL和NULL作比较得到的结果TRUE(1),将非NULL值与NULL作比较得到的结果为FALSE(0)。
结果排序
SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC | DESC] [, ...];
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- ASC和DESC分别代表的是排升序和排降序,默认为ASC。
注意: 如果查询SQL中没有order by子句,那么返回的顺序是未定义的。
查询同学及其数学成绩,按数学成绩升序显示
select name,math from grade_table order by math asc;
查询同学及其QQ号,按QQ号排序显示
说明一下: NULL值视为比任何值都小,因此排升序时出现在最上面。
查询同学的各门成绩,依次按数学降序、英语升序、语文升序显示
SELECT name, math, english, chinese FROM grade_table ORDER BY math DESC, english, chinese;
- order by子句中可以指明按照多个字段进行排序,每个字段都可以指明按照升序或降序进行排序,各个字段之间使用逗号隔开,排序优先级与书写顺序相同。
- 比如上述SQL中,当两条记录的数学成绩相同时就会按照英语成绩进行排序,如果这两条记录的英语成绩也相同就会继续按照语文成绩进行排序,以此类推。
查询同学及其总分,按总分降序显示
select name,chinese+math+english 总分 from grade_table order by chinese+math+english desc;
需要注意的是,在order by子句中可以使用select中指定的别名:
- 查询数据时是先根据where子句筛选出符合条件的记录。
- 然后再将符合条件的记录作为数据源来依次执行select语句。
- 最后再通过order by子句对select语句的执行结果进行排序。
order by子句的执行是在select语句之后的,所以在order by子句中可以使用别名。
查询姓孙的同学或姓王的同学及其数学成绩,按数学成绩降序显示
筛选分页结果
从第0条记录开始,向后筛选出n条记录:
SELECT ... FROM table_name [WHERE ..] [ORDER BY ...] LIMIT n;
从第s条记录开始,向后筛选出n条记录:
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
从第s条记录开始,向后筛选出n条记录,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- 查询SQL中各语句的执行顺序为:where、select、order by、limit。
- limit子句在筛选记录时,记录的下标从0开始。
建议: 对未知表进行查询时最好在查询SQL后加上limit 1,避免在查询全表数据时因为表中数据过大而导致数据库卡死。
按id进行分页,每页3条记录,分别显示第1、2、3页
说明一下: 如果从表中筛选出的记录不足n个,则筛选出几个就显示几个。
Update(修改)
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- SQL中的column=expr,表示将记录中列名为column的值修改为expr。
- 在修改数据之前需要先找到待修改的记录,update语句中的where、order by和limit就是用来定位数据的。
将孙七同学的数学成绩修改为80分
将王五同学的数学成绩修改为60分,语文成绩修改为80分
将总成绩倒数前三的3位同学的数学成绩加上30分
在修改数据之前,先查看总成绩倒数前三的3位同学的数学成绩。
在update语句中指明要将筛选出来的记录的数学成绩加上30分,并在修改后再次查看数据确保数据成功被修改。
将所有同学的语文成绩修改为原来的2倍
注意: 更新全表的语句慎用!
Delete(删除)
删除数据
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...];
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- 在删除数据之前需要先找到待删除的记录,delete语句中的where、order by和limit就是用来定位数据的。
删除孙悟空同学的考试成绩
删除整张表数据
创建一张测试表,表中包含一个自增长的主键id和姓名。并且向表中插入一些测试数据用于删除。
在delete语句中只指明要删除数据的表名,而不通过where、order by和limit指明筛选条件,这时将会删除整张表的数据。
如果我们再向表中插入一些数据,在插入数据时不指明自增长字段的值,这时会发现插入数据对应的自增长id值是在之前的基础上继续增长的。
查看创建表时的相关信息时可以看到,有一个AUTO_INCREMENT=n
的字段,该字段表示下一次插入数据时自增长字段的值应该为n。
当通过delete语句删除整表数据时,不会重置AUTO_INCREMENT=n
字段,因此删除整表数据后插入数据对应的自增长id值会在原来的基础上继续增长。
注意: 删除整表操作要慎用!
截断表
TRUNCATE [TABLE] table_name;
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- truncate只能对整表操作,不能像delete一样针对部分数据操作。
- truncate实际上不对数据操作,所以比delete更快。
- truncate在删除数据时不经过真正的事务,所以无法回溯。
- truncate会重置AUTO_INCREMENT=n字段。
创建一张测试表,表中包含一个自增长的主键id和姓名,并且向表中插入一些测试数据用于删除
在truncate语句中只指明要删除数据的表名,这时便会删除整张表的数据,但由于truncate实际不对数据操作,因此执行truncate语句后看到影响行数为0。
再向表中插入一些数据,在插入数据时不指明自增长字段的值,这时会发现插入数据对应的自增长id值是重新从1开始增长的。因为当通过truncate语句删除整表数据时,会重置AUTO_INCREMENT
字段,因此截断表后插入数据对应的自增长id值会重新从1开始增长。
截断表和删除整张表的数据区别
- 操作类型和日志记录:DELETE 是 DML(数据操作语言)命令,会逐行删除数据,并为每行的删除操作生成相应的事务日志;而 TRUNCATE 是 DDL(数据定义语言)命令,它会放弃整个表的数据,并重新初始化表的状态,因此产生的日志较少。
- 执行速度:TRUNCATE 的执行速度比 DELETE 快,因为它不需要逐行删除数据。
- 触发器:DELETE 操作会触发表上定义的触发器,而 TRUNCATE 操作不会触发任何触发器。
- 完整性约束:DELETE 在删除过程中需要考虑外键约束等完整性约束条件,而 TRUNCATE 在某些情况下可以绕过外键约束限制。
- 自增序列和主键重置:DELETE 不会重置自增序列的计数器和主键值,而 TRUNCATE 会将自增序列计数器重置为初始值,并重置主键值。
- 索引重建:DELETE 不会重建表的索引,而 TRUNCATE 会移除数据并重建索引。
- 在实际应用中,选择使用哪种方法应根据具体情况进行考虑。如果需要保留表结构和相关约束,或者需要进行事务回滚等操作,可以使用 DELETE;
- 如果需要快速清空表数据,并且不关心数据的删除过程和日志记录,可以使用TRUNCATE。同时,在执行任何数据操作之前,建议先备份重要的数据,以防止意外情况的发生
插入查询结果
INSERT [INTO] table_name [(column1 [, column2] ...)]
SELECT ... [WHERE ...] [ORDER BY ...] [LIMIT ...];
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- SQL的作用是将筛选出来的记录插入到指定的表当中。
- SQL中的column,表示将筛选出的记录的各个列插入到表中的哪一列。
删除表中重复的记录,重复的数据只能有一份
要求删除测试表中重复的数据,思路如下:
- 创建一张临时表,该表的结构与测试表的结构相同。
- 以去重的方式查询测试表中的数据,并将查询结果插入到临时表中。
- 将测试表重命名为其他名字,再将临时表重命名为测试表的名字,实现原子去重操作。
创建一张测试表,表中包含id和姓名。并且向测试表中插入一些测试数据!!!
1、创建一张临时表,该表的结构与测试表的结构相同。由于临时表的结构与测试表相同,因此在创建临时表的时候可以借助like进行创建。
2、以去重的方式查询测试表中的数据,并将查询结果插入到临时表中。通过插入查询语句将去重查询后的结果插入到临时表中,由于临时表和测试表的结构相同,并且select进行的是全列查询,因此在插入时不用在表名后指明column列表。
3、将测试表重命名为其他名字,再将临时表重命名为测试表的名字,实现原子去重操作。
聚合函数
COUNT 函数
统计班级共有多少同学
在select语句中使用count函数,并将*
作为参数传递给count函数,这时便能统计出表中的记录条数。
在select语句中使用count函数,并将表达式作为参数传递给count函数,这时也可以统计出表中的记录条数。
相当于在查询表中数据时,自行新增了一列列名为特定表达式的列,我们就是在用count函数统计该列中有多少个数据,等价于统计表中有多少条记录。
统计班级收集的QQ号有多少个
在select语句中使用count函数统计qq列中数据的个数,这时便能统计出表中QQ号的个数。
说明一下: 如果count函数的参数是一个确定的列名,那么count函数将会忽略该列中的NULL值。
统计本次考试数学成绩的分数个数
在select语句中使用count函数统计math列中数据的个数,这时便能统计出表中的数学成绩的个数。
在使用count函数时(包括其他聚合函数),在传递的参数之前加上distinct,这时便能统计出表中数学成绩去重后的个数。
SUM 函数
统计数学成绩总分
在select语句中使用sum函数统计math列中数据的总和,这时便能统计出表中的数学成绩的总和。
统计不及格的数学成绩总分
AVG 函数
统计平均总分
在select语句中使用avg函数计算总分的平均值。
MAX 函数
返回英语最高分
MIN 函数
返回70分以上的英语最低分
分组查询
SELECT column1 [, column2], ... FROM table_name [WHERE ...]
GROUP BY column [, ...] [order by ...] [LIMIT ...];
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- 查询SQL中各语句的执行顺序为:where、group by、select、order by、limit。
- group by后面的列名,表示按照指定列进行分组查询。
雇员信息表中包含三张表,分别是员工表(emp)、部门表(dept)和工资等级表(salgrade)。
员工表(emp)中包含如下字段:
- 雇员编号(empno)
- 雇员姓名(ename)
- 雇员职位(job)
- 雇员领导编号(mgr)
- 雇佣时间(hiredate)
- 工资月薪(sal)
- 奖金(comm)
- 部门编号(deptno)
部门表(dept)中包含如下字段:
- 部门编号(deptno)
- 部门名称(dname)
- 部门所在地点(loc)
工资等级表(salgrade)中包含如下字段:
- 等级(grade)
- 此等级最低工资(losal)
- 此等级最高工资(hisal)
DROP database IF EXISTS `hello3`;
CREATE database IF NOT EXISTS `hello3` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `hello3`;
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);
上述SQL中创建了一个名为scott的数据库,在该数据库中分别创建了部门表(dept)、员工表(emp)和工资等级表(salgrade),并分别向三张表中插入了一些数据用于查询。
我们执行完文件中的SQL后查看数据库,就能看到多了一个名为hello3的数据库。进入该数据库,在该数据库中就可以看到雇员信息表中的三张表。
查看每个表的信息!部门表(dept)
员工表(emp)
工资等级表(salgrade)
显示每个部门的平均工资和最高工资
在group by子句中指明按照部门号进行分组,在select语句中使用avg函数和max函数,分别查询每个部门的平均工资和最高工资。
select deptno,avg(sal) 平均工资, max(sal) 最高工资 from emp group by deptno;
显示每个部门的每种岗位的平均工资和最低工资
在group by子句中指明依次按照部门号和岗位进行分组,在select语句中使用avg函数和min函数,分别查询每个部门的每种岗位的平均工资和最低工资。
select avg(sal),min(sal),job, deptno from emp group by deptno, job;
HAVING 条件
SELECT ... FROM table_name [WHERE ...] [GROUP BY ...]
[HAVING ...] [order by ...] [LIMIT ...];
- SQL中大写的表示关键字,[ ]中代表的是可选项。
- SQL中各语句的执行顺序为:where、group by、select、having、order by、limit。
- having子句中可以指明一个或多个筛选条件。
having子句和where子句的区别
where 子句:
- 作用于表中的每一行数据,在对结果集进行分组之前进行筛选,用于筛选出符合条件的原始行。
- 不能使用聚合函数来进行条件判断。
having 子句:
- 作用于分组之后的结果集,是对分组后的条件进行筛选。
- 可以使用聚合函数来设定条件。
例如,假设有一个表包含学生成绩等信息,要找出班级平均分大于 80 分的班级,就需要先使用GROUP BY
对班级进行分组,然后用having
子句来筛选出平均分大于 80 的分组,这里不能用where
来直接对平均分进行判断,因为在分组前还没有计算出平均分;而如果要筛选出某个具体分数段的学生记录,就可以在分组前用where
子句来实现。
SQL中各语句的执行顺序
查询数据时,SQL中各语句的执行顺序如下:
- 根据where子句筛选出符合条件的记录。
- 根据group by子句对数据进行分组。
- 将分组后的数据依次执行select语句。
- 根据having子句对分组后的数据进行进一步筛选。
- 根据order by子句对数据进行排序。
- 根据limit子句筛选若干条记录进行显示。
统计每个部门的平均工资
在group by子句中指明按照部门号进行分组,在select语句中使用avg函数查询每个部门的平均工资。
显示平均工资低于2000的部门和它的平均工资
在上述SQL的基础上,在having子句中指明筛选条件为平均工资小于2000。
select deptno,avg(sal) as myavg from EMP group by deptno having myavg<2000;