多表查询 union 纵向合并
两张表合并,需要对应关系,union有去重功能
select stuid,name
from students
union
select tid,name
from teachers;
多表查询 横向合并
一、cross join 交叉连接
笛卡尔乘积 横向乘积,横向合并
缺点:无效项太多
select s.name as student_name,s.age,t.name as teacher_name
from students as s
cross join
teachers as t;
避免标题一样,可以起别名,但可能太多,同时再给表起个别名
二、inner join …on 内连接
等值连接:让表之间的字段以“等值”建立连接关系;
不等值连接 平等关系,谁在前都可以
select s.name as stu_name,t.name as tea_name
from students as s
inner join
teachers as t
on s.teacherid = t.tid;
取出学生对应的老师,并把学生标题和老师标题改别名
老版本写法:
select
s.name as stu_name,t.name as tea_name
from
students as s,teachers as t
where
s.teacherid = t.tid;
三、left outer join … on左外连接 --可不加outer
select *
from students as s
left outer join
teachers as t
on
s.teacherid = t.tid;
左学生表全要,右老师表有的则匹配,没有则忽略
四、right outer join … on右外连接 --可不加outer
select stuid as id,s.name as stu_name,t.name as tea_name
from students as s
right join
teachers as t
on
s.teacherid = t.tid;
左学生表姓名对应右老师姓名,但只显示右边老师表有的
五、自连接:去掉重复列的等值连接
- 实验:一张表内如何体现出两张表的感觉
搭建实验环境:
MariaDB [db1]>
create table
emp (id int,name char(50),leaderid int);
MariaDB [db1]>
insert
emp values(1,'liuying',null),
(2,'dushan',1),
(3,'dudou',1),
(4,'duwen',2),
(5,'dusiying',1);
MariaDB [db1]> select * from emp;
+------+----------+----------+
| id | name | leaderid |
+------+----------+----------+
| 1 | liuying | NULL |
| 2 | dushan | 1 |
| 3 | dudou | 1 |
| 4 | duwen | 2 |
| 5 | dusiying | 1 |
+------+----------+----------+
实际举例:解题思路,把emp表想象成emp和leader两张表,筛选时把emp设置两次别名即可
MariaDB [db1]>
select
e.id,e.name as slave,l.name as master
from
emp as e
left join
emp as l
on
e.leaderid=l.id
order by
id;
+------+----------+---------+
| id | slave | master |
+------+----------+---------+
| 1 | liuying | NULL |
| 2 | dushan | liuying |
| 3 | dudou | liuying |
| 4 | duwen | dushan |
| 5 | dusiying | liuying |
+------+----------+---------+
六、变种右外连接取反举例
select
*
from
students as s
left join
teachers as t
on
s.teacherid = t.tid
where
t.tid is null;
去除右边表交集部分
七、full outer join …on** 完全外连接
最尴尬的是mysql不支持full outer join..on语句,
mysql解题思路:先用左外连接和右外连接合并,最后用union去重即可
其他数据库直接用full outer join 语句
select
*
from
students s
left join
teachers t
on
s.teacherid=t.tid
union
select
*
from
students s
right join
teachers t
on
s.teacherid=t.tid;
实战举例:
MariaDB [hellodb]>
select
s.stuid,s.name as student_name,t.name as teacher_name,s.teacherid
from
students s
left join
teachers t
on
s.teacherid=t.tid
union
select
s.stuid,s.name as student_name,t.name as teacher_name,s.teacherid
from
students s
right join
teachers t
on
s.teacherid=t.tid;
+-------+---------------+---------------+-----------+
| stuid | student_name | teacher_name | teacherid |
+-------+---------------+---------------+-----------+
| 1 | Shi Zhongyu | Miejue Shitai | 3 |
| 2 | Shi Potian | NULL | 7 |
| 3 | Xie Yanke | NULL | 16 |
| 4 | Ding Dian | Lin Chaoying | 4 |
| 5 | Yu Yutong | Song Jiang | 1 |
| 6 | Shi Qing | NULL | NULL |
| 7 | Xi Ren | NULL | NULL |
| 8 | Lin Daiyu | NULL | NULL |
| 9 | Ren Yingying | NULL | NULL |
| 10 | Yue Lingshan | NULL | NULL |
| 11 | Yuan Chengzhi | NULL | NULL |
| 12 | Wen Qingqing | NULL | NULL |
| 13 | Tian Boguang | NULL | NULL |
| 14 | Lu Wushuang | NULL | NULL |
| 15 | Duan Yu | NULL | NULL |
| 16 | Xu Zhu | NULL | NULL |
| 17 | Lin Chong | NULL | NULL |
| 18 | Hua Rong | NULL | NULL |
| 19 | Xue Baochai | NULL | NULL |
| 20 | Diao Chan | NULL | NULL |
| 21 | Huang Yueying | NULL | NULL |
| 22 | Xiao Qiao | NULL | NULL |
| 23 | Ma Chao | NULL | NULL |
| 24 | Xu Xian | NULL | NULL |
| 25 | Sun Dasheng | NULL | NULL |
| NULL | NULL | Zhang Sanfeng | NULL |
+-------+---------------+---------------+-----------+
八、去交集
select
*
from
(select
s.stuid,s.name as student_name,t.name as teacher_name,s.teacherid
from
students s
left join
teachers t
on
s.teacherid=t.tid
union
select s.stuid,s.name as student_name,t.name as teacher_name,s.teacherid
from
students s
right join
teachers t
on
s.teacherid=t.tid;)
as a
where
a.teacherid is null
or
a.stuid is null;
子查询
-
子查询:在查询语句嵌套着查询语句,性能较差
基于某语句的查询结果再次进行的查询 -
用在WHERE子句中的子查询
用于比较表达式中的子查询;子查询仅能返回单个值
SELECT
Name,Age
FROM
students
WHERE
Age>(SELECT
avg(Age)
FROM
students);
从学生列表中显示大于平均年龄的人员,然后用这个条件在学生表中显示出改学生的name和age
- 用于WHERE…IN中的子查询:子查询应该单键查询并返回一个或多个值从构成列表
- 用于EXISTS,两个表中得有符合条件才行
SELECT
Name,Age
FROM
students
WHERE
Age IN (SELECT
Age
FROM
teachers);
从老师列表中显示年龄,然后用这个条件在在学生表显示出符合该年龄的学生name和age
学生年龄和老师年龄一样才能才能搜选出结果
- 用于FROM子句中的子查询
使用格式:SELECT tb_alias.col1,… FROM (SELECT clause) AS tb_alias WHERE Clause;
SELECT
s.aage,s.ClassID
FROM
(SELECT
avg(Age) AS aage,ClassID
FROM
students
WHERE
ClassID IS NOT NULL
GROUP BY
ClassID)
AS s
WHERE
s.aage>30;
列出每个班平均年龄大于30岁的学生ID,先查平均年龄,在查大于30岁平均年龄
三张表:列出student_name,course_name,score 三项
一、学生表:
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
二、分数表:
MariaDB [hellodb]> select * from scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 11 | 6 | 1 | 96 |
| 12 | 7 | 1 | 86 |
| 13 | 7 | 7 | 83 |
| 14 | 8 | 4 | 57 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
15 rows in set (0.00 sec)
二、科目表
MariaDB [hellodb]> select * from courses;
+----------+----------------+
| CourseID | Course |
+----------+----------------+
| 1 | Hamo Gong |
| 2 | Kuihua Baodian |
| 3 | Jinshe Jianfa |
| 4 | Taiji Quan |
| 5 | Daiyu Zanghua |
| 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
7 rows in set (0.00 sec)
解题思路:看对应关系,两个表两个表做连接
先列出student_name和score
MariaDB [hellodb]>
select
*
from
students
inner join
scores
on
students.stuid=scores.stuid;
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ID | StuID | CourseID | Score |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | 1 | 2 | 77 |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | 1 | 6 | 93 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | 2 | 2 | 47 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 4 | 2 | 5 | 97 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 5 | 3 | 2 | 88 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 6 | 3 | 6 | 75 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 7 | 4 | 5 | 71 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 8 | 4 | 2 | 89 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 9 | 5 | 1 | 39 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 10 | 5 | 7 | 63 |
| 6 | Shi Qing | 46 | M | 5 | NULL | 11 | 6 | 1 | 96 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 12 | 7 | 1 | 86 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 13 | 7 | 7 | 83 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 14 | 8 | 4 | 57 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 15 | 8 | 3 | 93 |
+-------+-------------+-----+--------+---------+-----------+----+-------+----------+-------+
15 rows in set (0.01 sec)
select *后可知,后四个字段是scores表的,classid,teacherid,gender三个字段是students表的,只提取我们需要的student_name和score即可(顺便起别名)
MariaDB [hellodb]>
select
st.name as student_name , sc.score
from
students as st
inner join
scores as sc
on
st.stuid=sc.stuid;
+--------------+-------+
| student_name | score |
+--------------+-------+
| Shi Zhongyu | 77 |
| Shi Zhongyu | 93 |
| Shi Potian | 47 |
| Shi Potian | 97 |
| Xie Yanke | 88 |
| Xie Yanke | 75 |
| Ding Dian | 71 |
| Ding Dian | 89 |
| Yu Yutong | 39 |
| Yu Yutong | 63 |
| Shi Qing | 96 |
| Xi Ren | 86 |
| Xi Ren | 83 |
| Lin Daiyu | 57 |
| Lin Daiyu | 93 |
+--------------+-------+
由题可知分数表是学生表和科目表的纽带,再分析成绩表和科目表的关系,因为科目表和成绩表都有课程编号,这时候我们再挑出课程标号courseid
MariaDB [hellodb]>
select
st.name as student_name , sc.score,sc.courseid
from
students as st
inner join
scores as sc
on
st.stuid=sc.stuid;
+--------------+-------+----------+
| student_name | score | courseid |
+--------------+-------+----------+
| Shi Zhongyu | 77 | 2 |
| Shi Zhongyu | 93 | 6 |
| Shi Potian | 47 | 2 |
| Shi Potian | 97 | 5 |
| Xie Yanke | 88 | 2 |
| Xie Yanke | 75 | 6 |
| Ding Dian | 71 | 5 |
| Ding Dian | 89 | 2 |
| Yu Yutong | 39 | 1 |
| Yu Yutong | 63 | 7 |
| Shi Qing | 96 | 1 |
| Xi Ren | 86 | 1 |
| Xi Ren | 83 | 7 |
| Lin Daiyu | 57 | 4 |
| Lin Daiyu | 93 | 3 |
+--------------+-------+----------+
15 rows in set (0.00 sec)
最后取出的表再和courses表内连接即可取出题目索要的答案;
MariaDB [hellodb]>
select
st.name as student_name , sc.score, c.course
from
students as st
inner join
scores as sc
on
st.stuid=sc.stuid
inner join
courses as c
on
sc.courseid=c.courseid;