DQL语言-数据查询语言

dql语言是用来检索表中数据的语言

语法

                      执行顺序

SELECT 子句             6
FROM 子句               1
JOIN.. ON.. 子句        2
WHERE 子句              3
GROUP BY 子句           4
HAVING 子句             5
ORDER BY 子句           7
LIMIT 子句              8

导入一个装有数据的表格用于练习查询

在idea中导入sql脚本文件（填写好的数据，用于练习查询）

。鼠标右键选择tedu.sql文件，并进行配置

。在弹出的配置窗口中点击"+"来设置数据库源,目的是在哪个数据库中执行这个tedu.sql脚本（将文件方入idea中点击运行即可）

。选择配置的数据源后点击OK完成配置

基础查询

一条dql语句中至少要包含的子句有两个；select 子句和from子句

。select（选择）子句用来指定检索表中哪些字段

。from（从...起）子句用来指定检查哪些表

具体语法

select 字段1【，字段2，...】  字段可以是具体字段名，也可以是“*”，也可以是表达式
from 表1【，表2】

列

。select *from 表名 ，  可以检索表中所有的记录，并且结果集会列出该表每条记录所有字段

检索teacher表中所有记录
select *from teacher

上述 SQL 中 SELECT 子句中我们使用 "*" , "*" 表达查询该表中的所有字段 .

注

实际开发中，我们不要使用select*，

原因；当数据库看到select*时，会去检索数据字典了解该表的结构，从而确定表中的字段名然后进行检索，这会消耗不必要的性能开销。

如果是临时手动执行sql可以使用select*

后期我们都是在java代码中执行sql语句，此时不能使用select*

。检索表中某些字段的值

查看所有老师的名字，性别，工资，年龄

select name，gender，salary，age
from teacher

结果集中会将teacher表所有记录列出来，但是每条记录仅列出select要求的字段

2.where子句

在dql中添加where子句，可以仅将满足where子句要求记录检索出来

列

。查看职称为“一级讲师”的老师的名字，性别，年龄，工资，职称

1:查询的是老师的信息             确定数据来自teacher表.因此确定了FROM子句
2:查询名字,性别,年龄,工资,职称    确定SELECT子句
3:查看职称为"一级讲师"           确定WHERE子句
select name,gender,age,salary,title
from teacher 
where title='一级讲师'

查看除刘苍松以外的老师的名字，工资，年龄，性别

select name，age，gender，salary
from teacher
where name<>'刘苍松'

。查看职位是“大队长”的学生的名字，年龄，性别

select name，age，gender
from student
where job='大队长'

。查看年龄在30岁以上（含30）的老师的名字，职称，工资，奖金

select name，title,salary,comm
from teacher
where age>=30

3.连接多个条件

。and：“与”，都为真时才为真

。or：“或”，都为假时才为假

列

。查看7岁的“大对长”都有谁？列出这些学生的名字，年龄，性别和职位

select name，age，gender，job
from student
where age=7 and job=‘大队长’

。查看班级编号小于6的所有中队长都有谁？列明名字，年龄性别，班级编号（class_id），职位

select name,age,gender,class_id,job
from student
where class_id<6 and job='中队长'

。查看所有一级讲师和三级讲师的名字，职称，工资

select name，title，salary
from teacher
where title=‘一级讲师’ or title=‘三级讲师’

查看所有大队长，中队长和小队长的名字，性别，年龄和职位

select name，gender，age，job
from student
where job=大队长 or job=‘中队长’ or job=‘小队长’

and优先级高于or

为了提高or的优先级，可以使用（）

。查看班级编号在6以下的所有大队长的名字，年龄，性别，班级和编号

select name，age，gender，class_id,job
from student
where class_id<=6 and job='大队长' or job=‘中队长’

上面不能达到查询需求，数据库理解为；要查询所有班级编号在6以下的大对长，或者所有班级的中队长

SELECT name,age,gender,class_id,job
FROM student
WHERE class_id<=6 AND (job='大队长' OR job='中队长')
要使用()来提高OR的优先级达到需求

in（列表）

IN( 列表 ): 判断是否等于列表中的其中之一

例

查看所有大队长，中队长和小队长的名字，性别，年龄和职位 ?

SELECT name,gender,age,job
FROM student
WHERE job='大队长' OR job='中队长' OR job='小队长'
换成
SELECT name,gender,age,job
FROM student
WHERE job IN ('大队长','中队长','小队长')         job字段的值只要等于列表中其中之一即可,等效OR

查看所有一级讲师，二级讲师，三级讲师的名字，职称，工资和性别

SELECT name,title,salary,gender
FROM teacher
WHERE title IN ('一级讲师','二级讲师','三级讲师')

NOT IN( 列表 )

NOT IN( 列表 ): 判断不在列表中

例

查看除一级讲师和二级讲师之外的所有老师的名字，职称，工资

SELECT name,title,salary
FROM teacher
WHERE title<>'一级讲师' AND title<>'二级讲师'
替换
SELECT name,title,salary
FROM teacher
WHERE title NOT IN ('一级讲师','二级讲师')

查看除大队长，中队长，小队长的其他学生的名字，职位，性别，年龄

SELECT name,job,gender,age
FROM student
WHERE job NOT IN('大队长','中队长','小队长');

BETWEEN...AND...

BEWTEEN...AND... 用于判断是否在一个区间范围内

例

查看工资在 2000 到 5000 之间的老师的名字 , 性别 , 年龄 , 工资

SELECT name,gender,age,salary
FROM teacher
WHERE salary>=2000 AND salary<=5000
替换
SELECT name,gender,age,salary
FROM teacher
WHERE salary BETWEEN 2000 AND 5000
下限 上限

查看年龄在 20 到 35 之间的男老师都有谁 ? 列出名字，性别，年龄，职称

SELECT name,gender,age,title
FROM teacher
WHERE age BETWEEN 20 AND 35
AND gender='男'

查看所有在 3-5 层的班级都有哪些 ? 列出班级名称和所在楼层

SELECT name,floor
FROM class
WHERE floor BETWEEN 3 AND 5

DISTINCT 去重

DISTINCT 关键字要紧跟在 SELECT 关键字之后

作用 : 可以将 DISTINCT 后面指定的字段值相同的记录从结果集中去除 .

例

查看老师的职称都有哪些 ?

SELECT title
FROM teacher
上述SQL表达的是查看每个老师的职称,并不是查看所有老师都有哪几种职称(会有重复值)
SELECT DISTINCT title
FROM teacher
在结果集中对title去除重复值后才能达到需求

查看学生都有哪些职位 ?

SELECT DISTINCT job
FROM student

查看个年龄段的学生都有哪些职位 ?

SELECT DISTINCT age,job 多字段去重,不保证任何一字段没有重复值,但是这些字段组合一定没有重复
FROM student

综合练习

题干

1. 查看负责课程编号 ( subject_id ) 为 1 的男老师都有谁 ?

2. 查看工资高于 5000 的女老师都有谁 ?

3. 查看工资高于 5000 的男老师或所有女老师的工资？

4. 查看所有 9 岁学生的学习委员和语文课代表都是谁 ?

5. 查看工资在 6000 到 10000 之间的老师以及具体工资 ?

6. 查看工资在 4000 到 8000 以外的老师及具体工资 ?

7. 查看老师负责的课程编号都有什么 ?

8. 查看所有女老师的职称都是什么 ?

9. 查看 7 - 10 岁的男同学的职位都有哪些 ?

10. 查看一级讲师和二级讲师的奖金 ( comm ) 是多少 ?

11. 查看除老板和总监的其他老师的工资和奖金是多少 ?

12. 查看 '3 年级 2 班 ' 和 '5 年级 3 班 ' 在那层楼

答案

1.查看负责课程编号(subject_id)为1的男老师都有谁?
SELECT name,gender,subject_id
FROM teacher
WHERE subject_id=1 AND gender='男'
2.查看工资高于5000的女老师都有谁?
SELECT name,salary,gender
FROM teacher
WHERE salary>5000 AND gender='女'
3.查看工资高于5000的男老师或所有女老师的工资？
SELECT name,salary,gender
FROM teacher
WHERE salary>5000 AND gender='男' OR gender='女'
4.查看所有9岁学生的学习委员和语文课代表都是谁?
SELECT name,age,job
FROM student
WHERE age=9
AND job IN('学习委员','语文课代表')
5.查看工资在6000到10000之间的老师以及具体工资?
SELECT name,salary
FROM teacher
WHERE salary BETWEEN 6000 AND 10000
6.查看工资在4000到8000以外的老师及具体工资?
SELECT name,salary
FROM teacher
WHERE salary NOT BETWEEN 4000 AND 8000
7.查看老师负责的课程编号都有什么?
SELECT DISTINCT subject_id
FROM teacher
8.查看所有女老师的职称都是什么?
SELECT DISTINCT title
FROM teacher
WHERE gender='女'
9.查看7-10岁的男同学的职位都有哪些?
SELECT DISTINCT job
FROM student
WHERE age BETWEEN 7 AND 10
AND gender='男'
10.查看一级讲师和二级讲师的奖金(comm)是多少?
SELECT comm
FROM teacher
WHERE title IN ('一级讲师','二级讲师')
11.查看除老板和总监的其他老师的工资和奖金是多少?
SELECT name,salary,comm
FROM teacher
WHERE title NOT IN('老板','总监')
12.查看'3年级2班'和'5年级3班'在那层楼?
SELECT name,floor
FROM class
WHERE name IN('3年级2班','5年级3班')

LIKE, 模糊查询

LIKE 中有两个通配符 :

_: 下划线表示任意一个字符 (1 个 )

%: 百分号表示任意个字符 (0- 多个 )

组合示范

LIKE '%X%':字符串中含有X(X前面可以有任意个字符,后面也可以有任意个字符)
LIKE '_X%':字符串中第二个字符是X
LIKE 'X%' :字符串以X开头
LIKE '%X' :字符串以X结尾
LIKE '%X_Y':字符串倒数第三个字符是X,最后一个字符是Y

例

查看名字中含有 ' 苍 ' 的老师又有谁 ?

SELECT name,age,salary
FROM teacher
WHERE name LIKE '%苍%'

查看姓张的学生都有谁 ?

SELECT name
FROM student
WHERE name LIKE '张%'

查看三个字名字中第二个字是 ' 平 ' 的学生都有谁 ?

SELECT name
FROM student
WHERE name LIKE '_平_'

查看最后一个字是 ' 晶 ' 的老师都有谁 ?

SELECT name
FROM teacher
WHERE name LIKE '%晶'

查看哪些学生是课代表 ? 列出他的名字和职位

SELECT name,job
FROM student
WHERE job LIKE '%课代表'

查看所有的 2 班都在哪层 ?

SELECT name,floor
FROM class
WHERE name LIKE '%2班'

练习

题干

1.查询名字姓"李"的学生姓名
2.查询名字中包含"江"的学生姓名
3.查询名字以"郭"结尾的学生姓名
4.查询9-12岁里是"课代表"的学生信息
5.查询名字第二个字是"苗"的学生信息
6.查询姓"邱"的课代表都是谁?

答案

1.查询名字姓"李"的学生姓名
SELECT name
FROM student
WHERE name LIKE '李%'
2.查询名字中包含"江"的学生姓名
SELECT name
FROM student
WHERE name LIKE '%江%'
3.查询名字以"郭"结尾的学生姓名
SELECT name
FROM student
WHERE name LIKE '%郭'
4.查询9-12岁里是"课代表"的学生信息
SELECT name,job,age
FROM student
WHERE age BETWEEN 9 AND 12
AND job LIKE '%课代表'
5.查询名字第二个字是"苗"的学生信息
SELECT name
FROM student
WHERE name LIKE '_苗%'
6.查询姓"邱"的课代表都是谁?
SELECT name,job
FROM student
WHERE name LIKE '邱%'
AND job LIKE '%课代表'

NULL 值判断

判断一个字段的内容是否为空 :

IS NULL : 判断字段值为空

IS NOT NULL : 判断字段值不为空

不可以使用 = 或 <> 判断 NULL 值

例

查看哪些老师的奖金为空 ?

SELECT name,salary,comm
FROM teacher
WHERE comm IS NULL

查看有奖金的老师 ?

SELECT name,salary,comm
FROM teacher
WHERE comm IS NOT NULL

ORDER BY-排序

 order by字句，用于对结果集按照指定的字段进行升序或降序，

。order by 字段名 【asc】；将结果集按照指定的字段进行升序排序（从小到大）

。order by 字段名 desc；将结果集按照指定的字段降序排（从小到大）

。可以按照多字段排序，排序存在优先级，优先按照第一个字段排序结果集，当第一个字段值相同的记录再按照第二个字段排序以此类推。

。不指定排序方式。默认为升序

order by 字句应当是sql中最后一个子句（由于方言存在，在Mysql，MariaDB中分页使用时会在其后追加limit字句）

列

。查看老师的工资排名，从多到少

order顺序 by通过 desc降序

select name ，salary
from teacher
order by salary desc

。查看老师奖金的排名

select name，comm
from teacher
order by comm desc

。查看学生的生日，按照从运到进

（日期是可以比较的，规则；越远的日子越小）

select name，birth
from student
order by birth

。查看7-10岁的学生信息，学生按照年龄从大到小排序（同年龄的看生日）

between之间

select name，age，birth
from student
where age between 7 and 10
order by birth 

。查看老师的工资和奖金，首先按照奖金的升序，再按照工资的降序

select name，comm，salary
from teacher
order by comm asc，salary desc

select name，comm，salary
from teacher
order by comm ，salary desc 

。升序的关键字可以不写

。多字段排序时，首先按照结果集第一个字段的排序方式对结果集排序，排序当第一个字段有相同值时这些记录再按照第二个字段的值排序，以此类推。

分页查询

将一个dql的查询结果集分段查询出来

场景

当一个dql92中定义规定的语法，因此分页是方言，不同的数据库分页语法可能完全不一样

在mysql和mariadb中使用limlt子句来完成，而oracle（甲骨文）则是使用rownum伪列来完成

语法

limit限制

order by 字段
limit m，n

。m表示跳过结果集中的多少条记录

。n检索出多少条记录

。在分页中常见的两个参数；

        。每页显示的记录数

        。当前要显示第几页

。分页的计算公式；

         

M:( 当前页数 -1)* 每页显示的记录数

N: 每页显示的记录数

当前显示第3页,每页显示5条记录
M:(当前页数-1)*每页显示的记录数 -> m=(3-1)*5 m=10 跳过10条记录
M:每页显示的记录数 n=5
LIMIT 10,5

列

。查看老师工资的前5名

每页显示5条记录，显示第一页，就可以达到此效果

limit限制     跳过0个显示5条

select name，salary
from teacher
order by salary desc
limit 0,5

查看老师奖金信息，按照降序排序后，每页显示3条，显示第五页

limit m,n
M:(当前页数-1)*每页显示的记录数 (5-1)*3
N:每页显示的记录数
select name,comm
from teacher
order by comm desc
limit 12,3

dql中使用函数或表达式

在select子句中使用表达式

列

。查看每个老师工资和年薪

select salary，salary*12
from teacher

在select子句中使用函数

ifnull（是否为空）函数

定义：ifnull(arg1,arg2)

当arg1不为null时，函数返回arg1的值，如果age1为null则返回arg2的值。

ifnull的功能就是将一个null值替换为一个非null值

内部逻辑，用java代码表示

ifnull（srg1，arg2）{
if(arg1!=nuill){
return arg1
}else{
return arg2
}
}

列

。查看每个老师的工资+奖金的总和是多少

ifnull（comm，0）如果奖金为null则替换为0

select name，salary，comm，salary+comm
from teacher
执行dql后发现，null值与数字运算符的结果为null

使用ifnull函数将null值替换为非null值后再进行运算
swlect name，salary，comm，salary+ifnull（comm，0）
from teacher

在where子句中使用表达式

列

。查看年薪少于60000的老师都有谁

select name，salary
from teacher
where salary*12<60000

在where子句中使用函数

列

。查看奖金少于3000的老师都有谁

select name，comm
from teacher
where comm<3000
橡：=，<>,<,<=,>，>=都是忽略null值的

应当写成
select name，comm
from teacher
where ifnull（comm，0）<3000

 别名

别名通常使用在select子句和from子句中

。在select子句中可以为字段取别名，这样在查询的结果集中对应的的字段的名字使用该别名通常当字段含有表达式或函数时,为了增加可读性我们会使用别名

。在 FROM 子句中我们可以为表取别名

语法

。字段名<空格>别名

select name，salary，salary*12 annsal
from teacher；

。字段as别名
select name，salary，salary*12 asannusal
from teacher

。字段【as】‘别名’

select name，salary，salary*12 ‘annusal’
from teacher

select name，salary，salary*12 as ‘annusal’
from teacher

如果别名含sql关键字或者含有空格时，要使用引号括起来

select name，salary，salary*12 from        希望from作为别名此时会出现语法错误
from teacher

应当
select name，salary，salary*12 ‘from’
from teacher



select name，salary，salary*12 annusal sal    此时数据库理解annu是salary*12的别名，sql不能识别
from teacher


应当
select name，salary，salary*12 ‘annu sal’
from teacher 

 

综合练习

题干

1. 查询所有 10 岁学生的生日 , 按生日对应的年纪从大到小 .

2. 查询 8 岁同学中名字含有 " 苗 " 的学生信息

3. 查询负责课程编号 1 和 2 号且工资高于 6000 的老师信息

4. 查询 10 岁以上的语文课代表和数学课代表

5. 查询不教课程编号 1 的老师信息 , 按照工资降序排序

6. 查询没有奖金的老师信息

7. 查询所有老师的奖金，并按照奖金降序排序

8. 查看工资高于 8000 的老师负责的课程编号都有那些 ?

9. 查看全校年龄最小学生的第 6 - 10 名

1.查询所有10岁学生的生日,按生日对应的年纪从大到小.
select name，age，birth
from student
where age=10
order by birth
2.查询8岁同学中名字含有"苗"的学生信息
select name，age
from student
where age=8 and name like ‘%苗%’
3.查询负责课程编号1和2号且工资高于6000的老师信息
select name，subject—id，salary
from teacher
where subject_id in (1,2)
and salary>6000
4.查询10岁以上的语文课代表和数学课代表
select name,age,job
from student
where age>10
and job in('语文课代表'，‘数学课代表’)
order by salary desc
5.查询不教课程编号1的老师信息,按照工资降序排序
select name，subject_id,salary
from techer 
where subject_id<>1
order by salary desc
6.查询没有奖金的老师信息
select name,comm
from teacher
where ifnull(comm,0)=0
7.查询所有老师的奖金，并按照奖金降序排序
select name,comm
from teacher 
order by comm desc 
8.查看工资高于8000的老师负责的课程编号都有那些?
select distinct subject_id
from teacher
where salary>8000
9.查看全校年龄最小学生的第6-10名
select name,age,birth
from student 
order by birth desc
limit 5,5

 注:第9题不添加LIMIT时查看的排名中6-10与添加LIMIT后实际获取6-10不同

原因 :

MySQL 官网上对此有解释

If multiple rows have identical values in the ORDER BY columns, the server is
free to return those rows in any order, and may do so differently depending on
the overall execution plan. In other words, the sort order of those rows is
nondeterministic with respect to the nonordered columns.
One factor that affects the execution plan is LIMIT, so an ORDER BY query with
and without LIMIT may return rows in different orders.
翻译:
如果多行在 ORDER BY 列中具有相同的值，则服务器可以自由地以任意顺序返回这些行，并且可能会根据整
体执行计划以不同的方式返回这些行。换句话说，这些行的排序顺序对于无序列是不确定的。
影响执行计划的一个因素是 LIMIT，因此带有和不带 LIMIT 的 ORDER BY 查询可能会返回不同顺序的
行

解决办法 : 排序时指定字段值相同的记录无论是否添加 LIMIT 子句 , 要保证这些记录的顺序始终一致即可 . 我

们要额外的追加一个辅助字段进行排序 , 确保顺序一定一致 . 通常辅助字段值不要用重复的 , 赋值字段首选

ID

select id,name,age,birth
from student
order by birth desc ,id
limit 5,5
查询时当birth字段值相同的记录,在按照id排序,那么birth字段指向相同的记录在结果集中的顺序也一定是
一致的