MySQl数据库：多表查询方法、MySQL补充方法

目录

• 一、多表查询的思路
• 二、多表查询的两种方法
  • 1.方式一：连表查询
    • （1）inner join内连接
    • （2）left join
    • （3）right join
    • （4）union
  • 2.方式二：子查询
    • （1）例子
    • （2）in与not in
      • • any和in运算符的不同之处：
• 三、补充
  • 1.字符串拼接—concat(str1,str2,...)与concat_ws(separator,str1,str2,...)
    • （1）concat()用于分组之前的拼接任意字符串
    • （2）concat_ws() 指定一个连接符号拼接字符串，第一个参数separator是指定的连接符
  • 2.exists条件
    • （1）语法
  • 3.表相关操作
    • • （1）修改表中的字段的类型
      • （2）修改表名
      • （3）修改字段
      • （4）添加新字段
      • （5）删除表
      • （6）删除字段
• 四、Navicat可视化数据库软件
• 五、多表查询练习题

一、多表查询的思路

表数据准备
create table dep(
  id int primary key auto_increment,
  name varchar(20) 
);

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

如果想要，查询员工名字及部门

select * from emp,dep; 

会将两张表中所有的数据对应一遍，这个现象我们也称之为笛卡尔积，这种没有逻辑的对应没有意义 ，应该将有关系的数据对应到一起才合理

mysql> select * from emp,dep;
+----+--------+--------+------+--------+-----+--------------+
| id | name   | sex    | age  | dep_id | id  | name         |
+----+--------+--------+------+--------+-----+--------------+
|  1 | jason  | male   |   18 |    200 | 200 | 技术         |
|  1 | jason  | male   |   18 |    200 | 201 | 人力资源     |
|  1 | jason  | male   |   18 |    200 | 202 | 销售         |
|  1 | jason  | male   |   18 |    200 | 203 | 运营         |
|  1 | jason  | male   |   18 |    200 | 205 | 财务         |
|  2 | dragon | female |   48 |    201 | 200 | 技术         |
|  2 | dragon | female |   48 |    201 | 201 | 人力资源     |
|  2 | dragon | female |   48 |    201 | 202 | 销售         |
|  2 | dragon | female |   48 |    201 | 203 | 运营         |
|  2 | dragon | female |   48 |    201 | 205 | 财务         |
|  3 | kevin  | male   |   18 |    201 | 200 | 技术         |
|  3 | kevin  | male   |   18 |    201 | 201 | 人力资源     |
|  3 | kevin  | male   |   18 |    201 | 202 | 销售         |
|  3 | kevin  | male   |   18 |    201 | 203 | 运营         |
|  3 | kevin  | male   |   18 |    201 | 205 | 财务         |
|  4 | nick   | male   |   28 |    202 | 200 | 技术         |
|  4 | nick   | male   |   28 |    202 | 201 | 人力资源     |
|  4 | nick   | male   |   28 |    202 | 202 | 销售         |
|  4 | nick   | male   |   28 |    202 | 203 | 运营         |
|  4 | nick   | male   |   28 |    202 | 205 | 财务         |
|  5 | owen   | male   |   18 |    203 | 200 | 技术         |
|  5 | owen   | male   |   18 |    203 | 201 | 人力资源     |
|  5 | owen   | male   |   18 |    203 | 202 | 销售         |
|  5 | owen   | male   |   18 |    203 | 203 | 运营         |
|  5 | owen   | male   |   18 |    203 | 205 | 财务         |
|  6 | jerry  | female |   18 |    204 | 200 | 技术         |
|  6 | jerry  | female |   18 |    204 | 201 | 人力资源     |
|  6 | jerry  | female |   18 |    204 | 202 | 销售         |
|  6 | jerry  | female |   18 |    204 | 203 | 运营         |
|  6 | jerry  | female |   18 |    204 | 205 | 财务         |
+----+--------+--------+------+--------+-----+--------------+
30 rows in set (0.00 sec)

基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来

涉及到两张及以上的表时，字段很容易冲突，我们需要在字段前面加上表名来指定

select * from emp,dep where emp.dep_id=dep.id;

mysql> select * from emp,dep where emp.dep_id=dep.id;
+----+--------+--------+------+--------+-----+--------------+
| id | name   | sex    | age  | dep_id | id  | name         |
+----+--------+--------+------+--------+-----+--------------+
|  1 | jason  | male   |   18 |    200 | 200 | 技术         |
|  2 | dragon | female |   48 |    201 | 201 | 人力资源     |
|  3 | kevin  | male   |   18 |    201 | 201 | 人力资源     |
|  4 | nick   | male   |   28 |    202 | 202 | 销售         |
|  5 | owen   | male   |   18 |    203 | 203 | 运营         |
+----+--------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)

员工表中的jerry与dep表没有联系，所以在查询后并未显示，作为冗余的信息被筛掉了

基于上述的操作，就可以将多张表合并到一起然后一次性获取更多的数据，这就是多表查询的思路

---

二、多表查询的两种方法

笛卡尔积的方式在实际操作中，用的很少，而常用的是以下两种方法

1.方式一：连表查询

所需结论来自于多张表的字段，建议使用连表操作

• 连表操作关键字

关键字	作用
inner join	内连接
left join	左连接
right join	右连接
union	全连接

• 连表查询中应该使用on作为过滤语法

（1）inner join内连接

只连接两张表中共有的数据

# 用on添加连接的依据
select * from emp inner join dep on emp.dep_id=dep.id;

inner join中的inner可以省略

（2）left join

以关键字left join左表数据为基准，展示左表所有数据，如果右表中没有对应的数据项，则以null填充

select * from emp left join dep on emp.dep_id=dep.id;

（3）right join

以关键字right join右表数据为基准，展示右表所有数据，如左表没有对应的数据项，则以null填充

select * from emp right join dep on emp.dep_id=dep.id;

（4）union

以左右表为基准，展示所有数据，没有对应的数据则用null补充

select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;

union会过滤重复的结果

union all 不过滤重复的结果

select * from emp left join dep on emp.dep_id=dep.id
union all
select * from emp right join dep on emp.dep_id=dep.id;

综上，当我们学会两张表的连表操作之后，就可以将无数张表连接起来

就是将每两张表拼接之后的表起别名当成一张表，基于这张表再去和别的表连接。

• 例1:求姓名是nick的员工部门名称--用连表操作
• 第一步：先将两张表连接起来，查询所有的部门名称dep.name

# 按照连接条件连接两张表
select dep.name from dep left join emp on dep.id=emp.dep_id；

• 第二步：在员工对应的部门名中，增加条件员工名为nick

select dep.name from dep left join emp on emp.dep_id=dep.id where emp.name='nick';

2.方式二：子查询

子查询：将一条sql语句的查询结果，当成另外一条sql语句的查询条件，所需结论来自于一张表的字段，可以使用子查询

子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS和 NOT EXISTS等关键字

子查询的逻辑，类似于我们生活中解决问题的方式：分步操作

（1）例子

• 例1:求姓名是nick的员工部门名称--不能用连表操作

• 第一步：

在员工表中查询，找到 姓名为nick的员工的部门编号

select dep_id from emp where name ='nick';

• 第二步：查询部门名称

  在部门表中查询，将第一步的结果当作第二步的条件

select name from dep where id=(select dep_id from emp where name ='jason');

（2）in与not in

in后跟的都是子查询，in()后面的子查询是返回结果集的

# 查询平均年龄在18岁以上的部门名
-- 1 查找员工年龄大于18岁其对应的部门id
select dep_id from emp group by dep_id having avg(age)>18;

-- 2 查找部门id对应的名称
select name from dep where id in (select dep_id from emp group by dep_id having avg(age)>18);

（3）any

any比较值与 子查询数据集中的任意值的结果

在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。

any和in运算符的不同之处：

any 必须和其他的比较运算符共同使用，而且any必须将比较运算符放在 any 关键字之前

# 查询平均年龄在18岁以上的部门名

select name from dep where id =any (select dep_id from emp group by dep_id having avg(age)>18);

=any 等价于in，而 >any 或者<any 则等价于 not in

any不能与固定数据集搭配

（4）all

类似于any，当all表示所有，any表示任意一个

# 查询 比 所有部门平均工资高的员工 的 姓名和工资
-- 查询比部门薪资的平均工资
select avg(salary) from emp group by post;

-- 查询比平均工资高的员工的姓名和工资
select name,salary from emp where salary >all(select avg(salary) from emp group by post);

+------+------------+
| name | salary     |
+------+------------+
| tom  | 1000000.31 |
+------+------------+

三、补充

1.字符串拼接—concat(str1,str2,...)与concat_ws(separator,str1,str2,...)

（1）concat()用于分组之前的拼接任意字符串

select concat(id,':',name,'-->',age) as 'id：员工姓名-->年龄' from emp;

# 结果展示
+----------------------------+
| id：员工姓名-->年龄        |
+----------------------------+
| 1:jason-->18               |
| 2:dragon-->48              |
| 3:kevin-->18               |
| 4:nick-->28                |
| 5:owen-->18                |
| 6:jerry-->18               |
+----------------------------+

（2）concat_ws() 指定一个连接符号拼接字符串，第一个参数separator是指定的连接符

select concat_ws(id,'|',name,'|',age) from emp;

# 展示结果: | id name id | id age，id字段成了填充符

+--------------------------------+
| concat_ws(id,'|',name,'|',age) |
+--------------------------------+
| |1jason1|118                   |
| |2dragon2|248                  |
| |3kevin3|318                   |
| |4nick4|428                    |
| |5owen5|518                    |
| |6jerry6|618                   |
+--------------------------------+

2.exists条件

（1）语法

sql 1 exists sql 2;

exists 判断 sql 2 语句是否有结果，有结果则执行 sql 1 语句，否则不执行 sql 1 语句，返回空数据

select name from emp where exists (select name from dep where id=200);

3.表相关操作

（1）修改表中的字段的类型

modify只能修改字段类型，不能修改其他信息

alter table 表名 modify 字段名 新的字段类型;

alter tabl1 t1 modify name char(10);

（2）修改表名

alter table t1 rename tt1;

（3）修改字段

change关键字

# 修改字段名
alter table 表名 change 旧名 新名 字段类型;

# 修改字段类型
alter table 表名 change 旧名 旧名 字段类型;

# 多次修改逗号隔开
alter table t1 change id ID tinyint, change name NAME char(4);

（4）添加新字段

add关键字，尾部追加，默认添加新字段是null

alter table 表名 add 字段 字段类型;

after关键字 指定在字段之后

alter table 表名 add 字段 字段类型 after 已有字段;

first关键字，将字段添加在最前面

alter table 表名 add 字段 字段类型 first;

（5）删除表

drop关键字

drop table db1.t1;
drop table t1;

（6）删除字段

drop关键字

alter table 表名 drop 字段名; -- 整个删除

alter table t1 drop name;

四、Navicat可视化数据库软件

第三方开发的是用来充当数据库客户端的简单快加的操作软件

第三方软件的底层还是SQL与nosql语句

常用操作
有些功能可能需要自己修改SQL预览
创建库、表、记录、外键
逆向数据库到模型、模型创建
新建查询可以编写SQL语句并自带提示功能

SQL语句注释语法

--、#、\**\

​ 运行、转储SQL文件

有些功能需要自己修改SQL预览

五、多表查询练习题

复杂的SQL语句一定要一步步分解，按照步骤来写：

1、先确定要使用的数据表

2、预览要使用的数据表

3、确定查询的思路，分步查询

0.查询的语法

SELECT DISTINCT(字段名1,字段名2) FROM 左表名 连接类型 JOIN 右表名
    ON 连表条件
    WHERE 筛选条件
    GROUP BY 分组字段
    HAVING 过滤条件
    ORDER BY 排序字段 asc/desc
    LIMIT 限制条数;

1.查询所有的课程的名称以及对应的任课老师姓名

# 1 先确定要使用课程表和分数表
# 2 预览表中的数据
-- SELECT * FROM course;
-- SELECT * FROM teacher;
# 3 确定多表查询的思路 
-- 先查找所有课程对应的老师的id
SELECT
	course.cname,
	teacher.tname 
FROM
	course
	JOIN teacher ON course.teacher_id = teacher.tid;-- 4 查询平均成绩大于八十的同学的姓名和平均成绩 

2、查询学生表中男女生各有多少人

SELECT
	gender,
	count( sid ) 
FROM
	student 
GROUP BY
	gender;

3、查询物理成绩等于100的学生的姓名

# 1 先确定要使用: 学生表 分数表 和课程表
# 2 预览表中的数据
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM course;
# 3 确定多表查询的思路 
-- 先查物理成绩等于100的学生的id
SELECT
	sname 
FROM
	student 
WHERE
sid IN ( 
  SELECT 
  score.student_id 
  FROM 
  course 
  INNER JOIN score ON score.course_id = course.cid 
  WHERE course.cname = '物理' 
  AND score.num = 100 
);

4、查询平均成绩大于八十分的同学的姓名和平均成绩

# 1 先确定要使用: 学生表 分数表 
# 2 预览表中的数据
SELECT * FROM student;
SELECT * FROM course;
SELECT * FROM course;
# 3 确定多表查询的思路 
-- 先查平均成绩大于八十分的同学的id
select student_id,avg(num)>80 from score group by student_id;

select student.sname,t1.avg_c from student join (select student_id,avg(num) as avg_c from score group by student_id having avg_c>80) as t1 on t1.student_id=student.sid;
# 美化
SELECT
	student.sname,
	t1.avg_c 
FROM
	student
	JOIN (
	SELECT
		student_id,
		avg( num ) AS avg_c 
	FROM
		score 
	GROUP BY
		student_id 
	HAVING
	avg_c > 80 
	) AS t1 ON t1.student_id = student.sid;

5、查询所有学生的学号，姓名，选课数，总成绩

# 1 先确定要使用: 学生表 分数表 
# 2 预览表中的数据
SELECT * FROM student;
SELECT * FROM score;

# 3 确定多表查询的思路 
-- 先查学生的选课数量和总成绩


SELECT
	student.sid '学号',
	sname '姓名',
	t1.co '选课数',
	t1.su '总成绩' 
FROM
	student
LEFT JOIN (
	SELECT
		student_id,
		count( course_id ) AS co,
		sum( num ) AS su 
	FROM
		score
	GROUP BY
	student_id 
	) AS t1 ON t1.student_id = student.sid;

6、 查询姓李老师的个数

# 1 先确定要使用: 老师表
# 2 预览表中的数据
SELECT * FROM teacher;

select count(tid) from teacher where tname like '%李%';
# 2

7、查询没有报李平老师课的学生姓名

# 1 先确定要使用: 学生表 分数表 课程表 老师表
# 2 预览表中的数据
SELECT * FROM student;
SELECT * FROM score;
SELECT * FROM course;
SELECT * FROM teacher;

# 3 确定多表查询的思路 
-- 先查报李平老师课的学生姓名
-- 1先找到李平老师教的课程id
select cid from course join teacher on teacher.cid=course.teacher_id where teacher.tname='李平老师';

-- 2 找到报李平老师课程的学生id
select distinct student_id from score where course_id in (select cid from course join teacher on teacher.tid=course.teacher_id where teacher.tname='李平老师')

-- 3 找到学生的姓名
select sname from student where sid in (select distinct student_id from score where course_id in (select cid from course join teacher on teacher.tid=course.teacher_id where teacher.tname='李平老师'));



SELECT
	sname 
FROM
	student 
WHERE
	sid NOT IN (
	SELECT DISTINCT
		student_id 
	FROM
		score 
	WHERE
		course_id IN (
		SELECT
			cid 
		FROM
			course
			JOIN teacher ON teacher.tid = course.teacher_id 
		WHERE
		teacher.tname = '李平老师' 
	));

8、查询没有同时选修物理课程和体育课程的学生姓名

# 1 先确定要使用: 学生表 分数表 课程表 
# 2 预览表中的数据
SELECT * FROM student;
SELECT * FROM score;
SELECT * FROM course;

-- 1 先获取物理和体育课程的id
select cid from course where cname in ('物理','体育');

-- 2 获取选修 物理 体育课程的学生id
select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) group by student_id having count(student_id) <2;

-- 3 获取没有同时选的
select sname from student where sid in (select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) group by student_id having count(student_id) <2)



SELECT
	sname 
FROM
	student 
WHERE
	sid IN (
	SELECT
		student_id 
	FROM
		score 
	WHERE
		course_id IN (
		SELECT
			cid 
		FROM
			course 
		WHERE
		cname IN ( '物理', '体育' )) 
	GROUP BY
		student_id 
HAVING
	count( student_id ) < 2)