本文详细介绍了DQL（数据查询语言）的各种操作，包括基础查询、WHERE子句、连接多个条件、使用IN和NOTIN、BETWEEN...AND...、DISTINCT、LIKE和模糊查询、NULL值判断、ORDERBY排序、分页查询、函数和表达式使用、别名、聚合函数如MIN、MAX、SUM和AVG、GROUPBY分组、HAVING子句以及子查询的应用。此外，还讲解了关联查询，涉及一对一、一对多、多对多的关系，以及内连接、外连接和自连接的概念。
目录
DQL语言-数据查询语言
DQL语言是用来检索表中数据的语言
语法
执行顺序
SELECT 子句 6
FROM 子句 1
JOIN.. ON.. 子句 2
WHERE 子句 3
GROUP BY 子句 4
HAVING 子句 5
ORDER BY 子句 7
LIMIT 子句 8
基础查询
一条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要求的字段
WHERE子句
在DQL中添加WHERE子句时，可以仅将满足WHERE子句要求的记录检索出来
查看除"刘五"以外的老师的名字，年龄，性别，工资?
SELECT name,age,gender,salary
FROM teacher
WHERE name<>'刘五'
连接多个条件
-
AND："与"，都为真时才为真
-
OR："或"，都为假时才为假
例
查看班级编号小于6的所有团员都有谁?列明名字，年龄，性别，班级编号(class_id)，职位
SELECT name,age,gender,class_id,job
FROM student
WHERE class_id<6 AND job='团员'
查看所有班长，委员和组长的名字，性别，年龄和职位?
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='组长')
要使用()来提高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
NOT IN(列表)
NOT IN(列表)：判断不在列表中
例
查看除班长，委员，组长的其他学生的名字，职位，性别，年龄
SELECT name,job,gender,age
FROM student
WHERE job NOT IN('班长','委员','组长');
BETWEEN...AND...
BEWTEEN...AND...用于判断是否在一个区间范围内
例
查看年龄在7到10岁的学生的名字，性别，年龄
SELECT name,gender,age
FROM student
WHERE age BETWEEN 7 AND 10下限 上限
DISTINCT去重
DISTINCT关键字要紧跟在SELECT关键字之后
作用：可以将DISTINCT后面指定的字段值相同的记录从结果集中去除。
例
查看学生都有哪些职位?
SELECT job
FROM student上述SQL表达的是查看每个学生的职位,并不是查看所有学生都有哪几种职位(会有重复值)
SELECT DISTINCT job
FROM student在结果集中对job去除重复值后才能达到需求
LIKE，模糊查询
LIKE中有两个通配符：
-
_：下划线表示任意一个字符(1个)
-
%：百分号表示任意个字符(0-多个)
组合示范
LIKE '%X%'：字符串中含有X(X前面可以有任意个字符，后面也可以有任意个字符)
LIKE '_X%'：字符串中第二个字符是X
LIKE 'X%' ：字符串以X开头
LIKE '%X' ：字符串以X结尾
LIKE '%X_Y'：字符串倒数第三个字符是X,最后一个字符是Y
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 子句应当是DQL中最后一个子句(由于“方言”的存在,在Mysql,MariaDB中分页使用时会在其后追加LIMIT子句)
例
查看老师的工资排名，从多到少
SELECT name,salary
FROM teacher
ORDER BY salary DESC
查看学生的生日，按照从远到近
日期是可以比较的,规则:越远的日子越小
SELECT name,birth
FROM student
ORDER BY birth
查看老师的工资和奖金，首先按照奖金的升序，再按照工资的降序
SELECT name,comm,salary
FROM teacher
ORDER BY comm ASC,salary DESCSELECT name,comm,salary
FROM teacher
ORDER BY comm ,salary DESC 升序的关键字可以不写多字段排序时，首先按照结果集第一个字段的排序方式对结果集排序，排序后当第一个字段有相同值时这些记录再按照第二个字段的值排序，以此类推。
分页查询
将一个DQL的查询结果集分段查询出来。
场景
当一个DQL语句查询的结果集记录数很多时，我们就可以分段将数据查询数来，提高查询速度减少资源开销
方言
分页没有在SQL92中定义规定的语法，因此分页是方言，不同的数据库分页语法可能完全不一样。
在Mysql和MariaDB中使用LIMIT子句来完成，而ORACLE则是使用ROWNUM伪列来完成。
语法
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条记录，显示第1页，就可以达到此效果.
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
注：
LIMIT 会出现的问题：
查看全校年龄最小学生的第6-10名
SELECT name,age,birth
FROM student
ORDER BY birth DESC
LIMIT 5,5
不添加LIMIT时查看的排名中6-10与添加LIMIT后实际获取6-10不同
原因：
如果多行在 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字段指向相同的记录在结果集中的顺序也一定是一致的
DQL中使用函数或表达式
在SELECT子句中使用表达式
例
查看每个老师的工资和年薪
SELECT salary,salary*12
FROM teacher
在SELECT子句中使用函数
IFNULL函数
定义：IFNULL(arg1,arg2)
当arg1不为NULL时，函数返回arg1的值，如果arg1为NULL则函数返回arg2的值。
IFNULL的功能就是将一个NULL值替换为一个非NULL值
例
查看每个老师的工资+奖金的总和是多少
SELECT name,salary,comm,salary+comm
FROM teacher
执行DQL后发现，NULL值与数字运算的结果为NULL。使用IFNULL函数将NULL值替换为非NULL值后再进行计算
SELECT 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 annusal
FROM teacher
- 字段 AS 别名
SELECT name,salary,salary*12 AS annusal
FROM teacher
- 字段 [AS] '别名'
SELECT name,salary,salary*12 'annusal'
FROM teacherSELECT name,salary,salary*12 AS 'annusal'
FROM teacher
- 字段 [AS] "别名"
SELECT name,salary,salary*12 "annusal"
FROM teacherSELECT 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 teacherSELECT name,salary,salary*12 annu sql 此时数据库理解为annu是salary*12的别名,sql不能识别
FROM teacher
应当:
SELECT name,salary,salary*12 'annu sql'
FROM teacher
聚合函数
概念
聚合函数又称为：分组函数、多行函数。对结果集进行统计的
将结果集中所有的记录按照指定的字段进行统计并最终得到一条结果。
聚合函数分类:
-
MIN：求最小值
-
MAX：求最大值
-
SUM：求和
-
AVG：求平均数
-
COUNT：统计记录数的
MIN，MAX，SUM，AVG是对值的统计，而COUNT是对记录数的统计。
聚合函数忽略NULL值，尤其在AVG和COUNT中体现的比较明显。
要现将参与统计的记录查询出来，然后再基于聚合函数对结果集进行统计。
例
- 查看老师的平均工资是多少?
1.现将参与统计的记录查询出来
查询所有老师的工资分别是多少?
SELECT salary
FROM teacher
2.在上述SQL的基础上添加聚合函数进行统计
SELECT AVG(salary)
FROM teacher
- 查看老师的最高工资,最低工资,平均工资和工资总和都是多少?
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM teacher
- 查看负责课程编号1的老师的平均工资是多少?
1:查询出负责课程编号1的老师工资分别是多少
SELECT salary
FROM teacher
WHERE subject_id=12:再对salary添加聚合函数求平均值
SELECT AVG(salary)
FROM teacher
WHERE subject_id=1
- 查看总共多少位老师?
1:先将所有老师查询出来
SELECT name FROM teacher2:使用COUNT对记录数进行统计
SELECT COUNT(name) FROM teacherSELECT COUNT(comm) FROM teacher 聚合函数忽略NULL值,记录数不全
SELECT COUNT(*) FROM teacher DBMS基本都对COUNT(*)进行了优化,因此统计记录数用这个
- 查看所有老师的平均奖金是多少?
SELECT AVG(comm),SUM(comm) 聚合函数忽略NULL值,对于AVG统计结果不精确
FROM teacherSELECT AVG(IFNULL(comm,0)) 先将NULL使用IFNULL替换为0,然后再进行统计即可
FROM teacher
GROUP BY子句 -分组
GROUP BY 子句可以将结果集按照指定的字段值相同的记录分组，然后在组内根据聚合函数进行统计并得出结果
- GROUP BY子句是配合聚合函数使用的,如果SELECT子句中没有聚合函数,通常不会使用GROUP BY子句
- 当SELECT子句中出现了聚合函数,那么不在聚合函数中的其它字段都要出现在GROUP BY子句中.
按照单字段分组
例
- 查看每种职位的老师平均工资是多少?
SELECT AVG(salary),title 每组都统计平均工资,因为按照title分组,所以统计结果可以显示title
FROM teacher
GROUP BY title 将结果集按照title字段值相同的记录分组
- 查看每个班级各多少人?
student表中class_id字段记录了该学生的班级号.那么班级号相同的记录就可以看作同一个班的学生,统计记录数
SELECT COUNT(*),class_id
FROM student
GROUP BY class_id
- 查看学生每种职位各多少人，以及最大生日和最小生日?
SELECT COUNT(*), MIN(birth) '最大生日',MAX(birth) '最小生日',job
FROM student
GROUP BY job
按照多字段分组
GROUP BY子句中后面指定多个字段，此时会将结果集按照这些字段值组合相同的记录看作一组
例
- 查看同班级同性别的学生分别多少人?
SELECT COUNT(*),gender,class_id
FROM student
GROUP BY gender,class_id 结果集中性别相同并且班级号相同的记录会被划分为一组
- 查看每个班每种职位各多少人?
SELECT COUNT(*),job,class_id
FROM student
GROUP BY job,class_id
按照聚合函数排序
- 查看每个科目老师的平均工资排名?
SELECT AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
ORDER BY AVG(salary) DESC好的书写习惯:在SELECT子句中凡是出现了聚合函数,函数或表达式时都应当取别名来增加可读性
并且当我们使用了别名时,在ORDER BY子句中还可以按照该别名排序
SELECT AVG(salary) avg_sal,subject_id
FROM teacher
GROUP BY subject_id
ORDER BY avg_sal DESC
分组中的过滤条件
问题
- 查看每个科目老师的平均工资?但是仅查看平局工资高于6000的那些。
SELECT AVG(salary),subject_id
FROM teacher
WHERE AVG(salary)>6000
GROUP BY subject_id
错误
聚合函数不能出现在WHERE子句中
原因
WHERE子句的过滤时机：在检索表中每条记录时用于过滤使用的，在生成结果集时WHERE会发挥作用。
我们希望将统计结果进行过滤，这应当实在检索表(WHERE发挥作用)之后生成结果集，并将结果集分组统计后才能得到统计结果从而进行过滤(这个过滤时机已经是在WHERE之后进行了)。
HAVING子句
HAVING与WHERE一样都是用来添加过滤条件进行过滤的
他们的区别：
- WHERE是对记录进行过滤的，而HAVING是对分组过滤的
只有满足HAVING要求的分组才会被保留
- 过滤时机不同
WHERE是在检索表时进行过滤的，产生结果集之前进行过滤的
HAVING是在产生结果集并根据GROUP BY对结果集分组后进行过滤的
- HAVING可以使用聚合函数作为过滤条件，WHERE不可以。
例
查看每个科目老师的平均工资?但是仅查看平局工资高于6000的那些?
数据库对各子句的执行顺序
SELECT AVG(salary),subject_id 4 根据筛选出的分组再进行统计并得到实际的结果集
FROM teacher 1 确定数据来自哪张表
GROUP BY subject_id 2 确定结果集按照那个字段分组,执行后结果集已经分号组
HAVING AVG(salary)>6000 3 根据HAVING的过滤条件筛选出符合要求的分组
查看每个科目老师的平均工资，前提是该科目老师最高工资要超过9000
SELECT AVG(salary) avg_sal,subject_id 4 将确定的两个分组按照AVG进行统计并生成结果集
FROM teacher 1 确定数据来自哪张表
GROUP BY subject_id 2 确定分组,一共6个分组
HAVING MAX(salary)>9000 3 仅有2个分组满足过滤要求
ORDER BY avg_sal 5 对结果集按照指定的字段排序
子查询
概念
嵌套在其他SQL语句中的一条DQL语句被称为子查询
应用场景
- DQL中使用:基于一个查询结果集进行二次查询
- DML中使用:基于一个查询结果集对表数据进行增,删,改操作
- DDL中使用:基于结果集创建数据库对象(表,视图)
子查询分类
-
单行单列子查询：查询结果集为一个值。仅一行一列
-
多行单列子查询：查询结果集为一组数据
-
多列子查询：结果集检索出来是一张表。多用于DDL语句.
在DQL中使用子查询
单行单列子查询
例
- 查看比张三工资高的老师都有谁?
1:未知条件：张三的工资是多少?
SELECT salary FROM teacher WHERE name='张三' 通过这条DQL可以得知张三的工资:3000
2:查看谁的工资高于张三的工资?
SELECT name,salary
FROM teacher
WHERE salary>(SELECT salary FROM teacher WHERE name='张三') 子查询要用()括起来,语法要求
- 查看哪些老师的工资是高于平均工资的?
1:未知条件:老师的平均工资是多少?
SELECT AVG(salary) FROM teacher
2:查询要求:工资高于平均工资即可
SELECT name,salary
FROM teacher
WHERE salary>(SELECT AVG(salary) FROM teacher)
- 查看和'李四'在同一个班的学生都有谁?
未知条件:李四的班级号是多少?
SELECT class_id FROM student WHERE name='李四'SELECT name,gender,class_id
FROM student
WHERE class_id=(SELECT class_id FROM student WHERE name='李四')
- 查看工资最高的老师的工资和奖金是多少?
未知条件:最高工资是多少钱
SELECT MAX(salary) FROM teacherSELECT name,salary,comm
FROM teacher
WHERE salary=(SELECT MAX(salary) FROM teacher)
多行单列子查询
多行单列子查询会检索出一组数组，此时如果作为过滤条件使用时：
- 判断相等，不能再使用"="，任何一个值都不可能同时等于多个值，只能等于其中之一。
因此判断相等时要伴随IN使用：IN(列表)
IN和NOT IN都可以使用
-
判断>,>=,<,<=时,要伴随ANY和ALL使用
>ANY(列表):大于列表中的其中之一即可.判断标准:大于最小的即可
>ALL(列表):大于列表中所有.判断标准:大于最大的
<ANY(列表):小于列表其中之一,小于最大的即可
<ALL(列表):小于列表所有,小于最小的
例
查看与"张三"和"李四"在同一个班的学生都有谁?
未知条件:这两个人的班级号是多少?
SELECT class_id FROM student WHERE name IN ('张三','李四')SELECT name,gender,class_id
FROM student
WHERE class_id=(SELECT class_id
FROM student
WHERE name IN ('张三','李四'))
报错：子查询返回了多于一行的记录。因为等于判断不能同时等于多个值。
实际写法：
SELECT name,gender,class_id
FROM student
WHERE class_id IN (SELECT class_id
FROM student
WHERE name IN ('张三','李四'))
- 查看比教科目2和科目4老师工资都高的老师都有谁?
未知条件:科目2和科目4老师的工资都是多少?
SELECT salary FROM teacher WHERE subject_id IN(2,4)SELECT name,salary,subject_id
FROM teacher
WHERE salary>ALL(SELECT salary FROM teacher WHERE subject_id IN(2,4))
另一种思路
未知条件:科目2和科目4老师的最高工资是多少?
SELECT MAX(salary) FROM teacher WHERE subject_id IN (2,4)SELECT name,salary,subject_id
FROM teacher
WHERE salary>(SELECT MAX(salary) FROM teacher WHERE subject_id IN (2,4))
在DML语句中使用子查询
例
给与'范五'负责同一科目的所有老师工资涨500
未知条件:'范五'负责的科目id?
SELECT subject_id FROM teacher WHERE name='范五'基于上述DQL进行DML操作
UPDATE teacher
SET salary = salary + 500
WHERE subject_id=(SELECT subject_id FROM teacher WHERE name='范五')
在DDL语句中使用子查询
以创建表为例,可以将一个查询结果集当做表创建出来
例
- 创建一张表，该表中记录了每个科目老师的工资情况，要求展示：最高，最低，总和和平均工资以及该科目id
SELECT MAX(salary),MIN(salary),SUM(salary),AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
创建表：
CREATE TABLE teacher_salary_info(
max_sal DOUBLE(7,2),
min_sal DOUBLE(7,2),
sum_sal DOUBLE(7,2),
avg_sal DOUBLE(7,2),
subject_id INT
)
表创建后还要进行DML操作
执行若干次的 INSERT ……
上述操作太麻烦将子查询的结果集当做表创建出来
SELECT MAX(salary),MIN(salary),SUM(salary),AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
该子查询的结果集就是我们期望的表的样子
那么此时我们可以执行如下DDL
CREATE TABLE teacher_salary_info
AS
SELECT MAX(salary),MIN(salary),SUM(salary),AVG(salary),subject_id
FROM teacher
GROUP BY subject_id
上述SQL中生成的表字段名是使用SELECT子句中字段名，如果SELECT中含有函数或表达式时要指定别名，此时生成的表的字段名会采用该别名：
CREATE TABLE teacher_salary_info
AS
SELECT
MAX(salary) max_sal,MIN(salary) min_sal,
SUM(salary) sum_sal,AVG(salary) avg_sal,
subject_id
FROM teacher
GROUP BY subject_id
关联查询(重点)
定义
联合多张表查询数据。查询结果集中的数据来自于多张表。
关联关系
表与表中的记录在设计时会产生一种对应关系，它称为关联关系
关联关系的分类
-
一对一：A表的一条记录仅对应B表的一条记录，称为A与B表存在一对一关系
-
一对多：A表中的一条记录可以对应B表中的多条记录，称为A与B表存在一对多关系
-
多对多：当A与B表双向都存在一对多关系时就称为多对多关系
连接条件
在关联关系中，非常重要的一点就是指定连接条件。
作用是让数据库清楚两张表间记录与记录之间的对应关系。
如果指定的连接条件无效或没有指定连接条件时，会产生笛卡尔积，这通常是一个无意义的结果集，要尽量避免.
语法
SELECT 各表中的字段
FROM A表,B表[,C表...]
WHERE 连接条件 用来让数据库清楚表与表之间记录的对应关系
AND 过滤条件 用来筛选数据的.
注:连接条件要与过滤条件同时满足!!!
例
查看每个老师以及其负责课程科目名?
SELECT teacher.name,teacher.age,subject.name
FROM teacher,subject
WHERE teacher.subject_id=subject.id为了区分字段属于哪张表,我们需要在字段前添加表名,格式为:表名.字段名
当表名过长时,这样写过于臃肿.对此我们可以为表取别名,此时我们可以用:表别名.字段名
SELECT t.name,t.age,s.name
FROM teacher t,subject s
WHERE t.subject_id=s.id
数据库在进行关联查询时，会遍历A表每一条记录并与B表每条记录连接一次，但是只有满足连接条件的记录才会将它们中需要查询的字段提取出来形成结果集中的一条记录。
不指定连接条件时，会产生笛卡尔积
SELECT t.name,t.age,s.name
FROM teacher t,subject s
笛卡尔积的产生
当不指定连接条件时，数据库在进行关联查询时，仍然会用A表一条记录与B表每条记录连接一次，并产生结果集中的一条记录。此时的数据量为A表记录数与B表记录数的乘积。
当表中数据量大时，这样的结果集开销巨大，甚至可能导致服务器宕机。因此要尽量避免。
连接条件要与过滤条件同时满足
王五是哪个班的班主任?列出:班级名称，楼层，老师名称，工资
1:数据来自哪些表?
teacher t,class c
2:连接条件?
c.teacher_id=t.id
3:过滤条件?
t.name='王五'SELECT c.name,c.floor,t.name,t.salary
FROM teacher t,class c
WHERE c.teacher_id=t.id 连接条件
AND t.name='王五' 过滤条件
N张表关联查询
N张表关联查询就要有至少N-1个连接条件，并且这些连接条件要同时满足
查看"范五"所带班级的学生都有谁?要列出:学生名字，年龄，班级名称，老师名字
1:数据来自哪些表
student s,class c,teacher t
2:关联关系-3张表关联就要有2个连接条件
学生表与班级表的关联关系:s.class_id=c.id
班级表与老师表的关联关系:c.teacher_id=t.id
3:过滤条件
老师的名字:t.name='范五'
SELECT s.name,s.age,c.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id 关联条件 关联条件之间一定是AND连接
AND c.teacher_id=t.id 关联条件
AND t.name='范五' 过滤条件
关联条件要与过滤条件同时满足
关联查询中使用聚合函数
将关联查询的结果集进行统计
例
查看范五所带班级的学生共多少人?
1:查询出参与统计的所有记录
1.1:查询出学生信息,查询条件"范五"所带班级(关联查询)
1.2:查询的数据以及过滤条件来自哪些表?
student s,class c,teacher t
1.3:连接条件
s.class_id=c.id
c.teacher_id=t.id
1.4:过滤条件
老师的名字:t.name='范五'
2:在查询的结果集基础上添加聚合函数统计实际结果1:查询待统计的记录--"范五"所带学生都有谁?
SELECT s.name,c.name,t.name
FROM student s,class c,teacher t
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND t.name='范五'2:在上述SQL基础上添加聚合函数
SELECT COUNT(*)
FROM student s,class c,teacher t
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND t.name='范五'
主键与外键
主键：一张表中通常第一个字段为主键字段，用来唯一标识表中的一条记录。主键要求的条件是非空且唯一。
外键：一张表中一个字段保存了另一张表中主键字段的值，那么这个字段就是外键字段。
在关联关系中，两张表通常就是使用主外键进行关联的。并且在关联查询中总是用等值连接主键与外键来建立两张表中记录的对应关系。
通常：定义外键字段的表在关联关系中处于"多"的一方。
例如:学生与班级表，学生表中有一个字段class_id保存着class表主键id字段的值，此时class_id字段就是外键字段。因此班级表与学生表存在一对多的关系，即：班级表的一条记录可以对应学生表的多条记录。
外键约束
外键约束是专门用来对外键字段施加的约束，它要求外键字段：
- 外键字段保存的值必须是它所对应的主键字段中已有的值或NULL
当我们想删除主键字段表中某条记录时，很可能失败，因为外键字段表中某些条记录中外键字段保存着这个主键时，此时会因为违反外键约束，导致主键字段表中无法删除记录 ，除非将该主键值在外键表中所有对应记录更新为NULL后方可进行。这在实际业务场景下开销巨大。
因此：实际开发中几乎不会施加外键约束。
多对多关系
多对多关系：A表与B表双向都是一对多，就是多对多关系。
多对多关系需要提供一张关联关系表来维系多对多关系。该表保存着两张产生关系表的外键字段。
多对多还是关联查询
例:
查看学习语文的学生都有谁?
SELECT s.name,su.name,ss.score
FROM subject su,student s,t_stu_subject_score ss
WHERE su.id=ss.subject_id
AND s.id=ss.stu_id
AND su.name='语文'
内连接
内连接是关联查询的另一种写法，与关联查询效果一样。
特点
将连接条件放在ON子句中，而WHERE子句仅用于添加过滤条件。因此结构清晰。
语法
SELECT 子句
FROM 表A
JOIN 表B ON 连接条件(A与B的连接条件)
JOIN 表C ON 连接条件(A与C或B与C的连接条件)
...
WHERE 过滤条件
例
查看1年级1班的学生信息？列出学生名字，年龄，所在班级
关联查询写法
SELECT s.name,s.age,c.name
FROM class c,student s
WHERE c.id=s.class_id 连接条件
AND c.name='1年级1班' 过滤条件内连接写法
SELECT s.name,s.age,c.name
FROM class c
JOIN student s ON c.id=s.class_id
WHERE c.name='1年级1班'
外连接
外连接在关联查询中可以将不满足连接条件的记录也查询出来
-
左外连接：以JOIN左侧表为驱动表，该表中所有记录都会查询出来，当来自JOIN右侧表中的记录有不满足连接条件时，那么结果集中对应字段值全为NULL
-
右外连接：与左外连接相反
例
查看所有班级信息和对应的班主任信息，如果该班没有班主任也要将班级信息列出来。
SELECT c.name,c.floor,t.name,t.salary
FROM class c
JOIN teacher t ON c.teacher_id=t.id
因为class表中有三个班级的teacher_id为99，而teacher表中没有id为99的记录。因此这三条班级记录由于不满足连接条件，所以在结果集中没有被查询出来。需求是要将class表中记录完整展现(不满足连接条件的记录也要展现)
SELECT c.name,c.floor,t.name,t.salary
FROM class c
LEFT JOIN teacher t ON c.teacher_id=t.id
查看所有班级信息和对应的班主任信息，无论是班级还是老师不满足连接条件也要展示
结合左连接与右连接
关键字UNION可以对结果集取并集。前提是这些结果集的字段(个数,类型,顺序)要完全一致SELECT c.name,c.floor,t.name,t.salary
FROM class c
LEFT JOIN teacher t ON c.teacher_id=t.id
UNION
SELECT c.name,c.floor,t.name,t.salary
FROM class c
RIGHT JOIN teacher t ON c.teacher_id=t.id取并集时,重复的记录仅会被保留一次
注:在ORACLE中有FULL OUTER JOIN可以直接实现上述操作
自连接
定义
同一张表的一条记录可以对应多条记录。
自己这张表上定义的外键对应自己这张表中主键字段的值。
场景
当数据存在上下级关系的树状结构数据时，可以使用自连接进行关联。
例
查看刘五的下属都有谁?
teacher表中记录了所有老师的信息，而manager字段记录了该老师的上级老师的id
manager字段记录了teacher表主键字段id的值.SELECT t.name,t.age,t.salary,m.name
FROM teacher t,teacher m 将teacher当做两张表看待,t表示保存老师 m表示保存领导
WHERE t.manager=m.id
AND m.name='刘五'内连接写法
SELECT t.name,t.age,t.salary,m.name
FROM teacher t
JOIN teacher m ON t.manager=m.id
WHERE m.name='刘五'
年龄最大的学生所在班的班主任的上司是谁?
SELECT m.name
FROM student s,class c,teacher t,teacher m
WHERE s.class_id=c.id
AND c.teacher_id=t.id
AND t.manager=m.id
AND s.birth=(SELECT MIN(birth) FROM student)内连接写法:
SELECT m.name
FROM student s
JOIN class ON s.class_id=c.id
JOIN teacher t ON c.teacher_id=t.id
JOIN teacher m ON t.manager=m.id
WHERE s.birth=(SELECT MIN(birth) FROM student)
扫一扫
1149
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
