MySQL学习笔记篇八

本节主要内容：等值连接、非等值连接、自然连接、外连接（左、右连接）；

连接分为内连接、外连接、自然连接。

其中，内连接包括等值连接、非等值连接、自连接；外连接包括左外连接、右外连接；

1、等值连接：

1）两个表查询条件相等才显示；

2)与多表联查约束主外键是一样的；

3)ON后面只写主外键；

例如1：SELECT * FROM student st INNER JOIN score sc ON st.id=sc.st_id;

例如2：SELECT * FROM student st, score sc where st.id=sc.st_id;

例如3：等值连接查询成绩大于70的学生

SELECT * FROM student  st INNER JOIN score sc ON st.id=sc.st_id where sc.score>70;

2、非等值连接：

假设有员工表employer表如下：

id	name	job	shangji	hiredate	salary	reward	dep_id
1100	张一	经理	2001	2020-10-12	15000	300	00
1101	张二	客服	2002	2019-09-09	8000		01
1102	张三	客服	2003	2017-09-23	8000		02
1103	李四	业务人员	2004	2020-11-09	7800	500	03
1104	王五	经理	2005	2021-01-06	17800		04
1105	赵六	业务人员	2006	2021-03-28	7800		05
1106	赵七	客服	2007	2020-06-26	8000		06

部门表department表如下：

dep_id	name	local
00	客服部	北京
01	业务部	上海
02	运营部	广州
03	销售部	青岛

薪水等级表SG表如下：

grade	lowsalary	highsalary
1	1000	5000
2	5001	9000
3	9001	13000
4	13001	17000
5	17001	21000

查询所有员工的姓名、工资，所在部门的名称以及工资的等级

法一：SELECT e.name, e.salary, d.name  FROM employer e, department d , SG s where e.dep_id=d.dep_id and e.salary >= s.lowsalary and e.salary <= s.highsalary ;

法二：SELECT e.name, e.salary, d.name  FROM employer e join department on e.dep_id=d.dep_id join SG s on e.salary between s.lowsalary and  s.highsalary ;

3、自连接：自己连接自己，起别名；

假设有员工表employer表如下：

id	name	job	shangji	hiredate	salary	reward	dep_id
1100	张一	经理	1104	2020-10-12	15000	300	00
1101	张二	客服	1105	2019-09-09	8000		01
1102	张三	客服	1103	2017-09-23	8000		02
1103	李四	业务人员	1100	2020-11-09	7800	500	03
1104	王五	经理		2021-01-06	17800		00
1105	赵六	业务人员	1100	2021-03-28	7800		03
1106	赵七	客服	1104	2020-06-26	8000		02

部门表department表如下：

dep_id	name	local
00	客服部	北京
01	业务部	上海
02	运营部	广州
03	销售部	青岛

薪水等级表SG表如下：

grade	lowsalary	highsalary
1	1000	5000
2	5001	9000
3	9001	13000
4	13001	17000
5	17001	21000

求1106员工编号、姓名、上级编号及姓名；

解析：Select * from employer where id=(Select shangji from employer where id=1106) ;

查询语句：Select e1.id,e1.ename,e2.id,e2.name from employer e1,employer e2 where e1.shangji=e2.id and e1.id=1106;

4、外连接：

👈左连接：左边表当中的所有的数据都查出来，右边满足条件(ON)的数据查询出来；

SELECT * FROM student st LEFT OUTER JOIN score sc ON st.id=sc.st_id;

👈右连接：右边表当中的所有的数据都查询出来，左边满足条件(ON)的数据查询出来；

SELECT * FROM stu st RIGHT JOIN score sc ON st.id=sc.st_id;

5、自然连接：

连接查询会产生无用笛卡尔集，我们通常使用主外键关系等式来去除它；

而自然连接无需给出主外键等式，它会自动找到这一等式；

要求：两张连接的表中列名和类型完全一致的列作为条件；会去除相同的列；

假设学生表student表如下：

s_id	name	age
1	张一	21
2	张二	25
3	张三	28

成绩表score表如下：

s_id	score	name
1	80	张一
2	76	张二
3	69	张三

语句：SELECT * FROM student natural join score;

多表联查练习：

例如：学生表student如下：

id	name	age	address	gender
1000	张一	18	上海	女
1001	张二	20	北京	男
1002	张三	22	厦门	女
1003	李四	26	香港	男
1004	王五	27	青岛	男

课程表course如下：

id	NAME
1	语文
2	政治
3	英语
4	数学

成绩表score如下：

score	st_id	c_id
89	1000	2
90	1001	1
89	1002	4
79	1003	3
99	1004	1

查询学生考试的分数、学生的姓名及学生的课程名称全部查出：

方法一：

SELECT st.name,sc.score, c.name FROM student st, score sc ,course c where st.id=sc.st_id and sc.c_id=c.id;

方法二：

SELECT st.name,sc.score, c.name FROM student st INNER JOIN score sc ON st.id=sc.st_id JOIN course c ON sc.c_id=c.id;