目录
多表查询的两种方法
方式1:连表操作
inner join 内连接
select * from l1 inner join l2 on l1.l2_id = l2.id;
# 表 l1 内连接 表 l2 连接条件是 l1的字段L2_ID = L2的字段id
left join 左连接
select * from l1 left join l2 on l1.l2_id = l2.id;
# 表 l1 内连接 表 l2 连接条件是 l1的字段L2_ID = L2的字段id
# 左连接是已 左表l1 显示为准 右表没有则用null 空填充
左连接：保证左表数据可以全部显示
右链接：可以保证右表全部显示
right join 右链接
select * from l1 right join l2 on l1.l2_id = l2.id
# 表 l1 内连接 表 l2 连接条件是 l1的字段L2_ID = L2的字段id
# 右连接是已 右表l2 显示为准 左表没有则用null 空填充
union 全连接
select * from l1 left join l2 on l1.l2_id = l2.id;
union
select * from l1 right join l2 on l1.l2_id = l2.id;
将左右连接全部生效 展示两个表的所有数据 各自没有的全部用null填充
'''
学会了连表操作之后也就可以连接N多张表
思路： 将拼接之后的表起别名 然后当做一张表 再去连接其他表，依次反复可以达到3 4 5表连接
'''
方式2：子查询
将一条sql语句用括号括起来 可以当成另外一条SQL语句的查询条件
题目:求姓名是jason的员工部门名称
# 子查询类似我们生活中解决问题的方式 分步操作
步骤1:先根据 jason 获取到部门编号
select l1_id from l2 where name = 'jason'
步骤2:在根据部门编号 获得部门名称
select name from l1 where id = (select l1_id from l2 where name = 'jason');
# 这样就获得了 jason的部门名称
'''
很多时候多表查询需要结合实际情况判断用哪种 更多时候甚至是相互配合使用
'''
sql语句基础语法补充
concat / existe / 表字段 增加 修改 删除
1.concat
concat 用于展示分组之前的字段拼接
select concat(name,'|',password) from l1;
+---------------------+
| concat(id,'|',name) |
+---------------------+
| 200|技术 |
| 201|人力资源 |
+---------------------+
可以把需要展示的数据拼接进行展示
多字段拼接可以使用 concat_ws 方法 因为这样就不需要输入多个字符
select concat_ws('|',id,name,age,sex) from l1;
# 意思 使用'|'拼接所有字段
# 得到结果 10001|张无忌|18|男
2.existe
sql语句1 existe sql语句2
只有当sql语句成立成功运行的情况下才会执行sql语句1
否则不执行sql语句1 返回空数据
类似一点点 python语法中的if
3.修改表名
alter table l1 rename l2;
4.添加表字段
alter table 表名 add 添加字段名 字段类型 字段约束
alter table l1 add name char(4) unique;
5.修改表字段信息
alter table 表名 change 旧字段名 新字段名 字段类型 约束条件;
# 修改字段信息 比如 原字段名 name 类型 char 条件 非空
# 新字段名 age 类型 char 条件不填写，默认是会只修改字段名
# 字段类型 约束条件等 没有修改的还是会保留下来的
6.删除字段
alter table 表名 drop 字段名；
alter table l1 drop age;
复杂sql练习题
"""
编写复杂的SQL不要想着一口气写完
一定要先明确思路 然后一步步写一步步查一步步补
"""
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT cname,tname FROM course INNER JOIN teacher on course.teacher_id = teacher.tid;
4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
student.sname,
T1.AVG_NUM
FROM
student
INNER JOIN ( SELECT student_id, avg( num ) AS AVG_NUM FROM score GROUP BY student_id HAVING AVG_NUM > 80 ) AS T1 ON student.SID = T1.student_id;
7、查询没有报李平老师课的学生姓名
SELECT
student.sname
FROM
student
WHERE
student.class_id NOT IN (
SELECT DISTINCT
score.student_id
FROM
score
WHERE
score.course_id IN (
SELECT
course.cid
FROM
course
WHERE
course.teacher_id = ( SELECT teacher.tid FROM teacher WHERE teacher.tname = '李平老师' )));
8、查询没有同时选修物理课程和体育课程的学生姓名
SELECT
student.sname
FROM
student
WHERE
student.sid IN (
SELECT
score.student_id
FROM
score
WHERE
score.course_id IN (
SELECT
course.cid
FROM
course
WHERE
course.cname IN ( '物理', '体育' ))
GROUP BY
score.student_id
HAVING
COUNT( course_id ) = 1
);
9、查询挂科超过两门(包括两门)的学生姓名和班级
SELECT student_id, FROM score WHERE score.num < 60 GROUP BY student_id HAVING COUNT(course_id) >= 2;
SELECT
student.sname,
class.caption
FROM
student
INNER JOIN class ON student.class_id = class.cid
WHERE
student.sid IN (SELECT student_id FROM score WHERE score.num < 60 GROUP BY student_id HAVING COUNT( course_id ) >= 2 )