python多表查询

数据准备,创建两张表department与employee

create table department(
id int,
name varchar(20) 
);

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

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

insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;

多表连接查询

SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;

交叉连接: 不适用任何匹配条件,生成笛卡尔积(将两个表的数据全部对应了一遍,连表查询的基础)

select * from 表1,表2;

 inner join 内连接: 只连接匹配的行

select 字段 from 表1 inner join 表2 on 条件;
select * from employee inner join department on employee.dep_id = department.id and department.name='技术';

外链接

left join 左连接: 优先显示左表全部记录
本质: 以左表为准,在内连接的基础上增加左边有右边没有的结果

select 字段 from 表1 left join 表2 on 条件;
select employee.id,employee.name,department.name from employee left join department on employee.dep_id=department.id

right join 右连接: 优先显示右表全部记录

本质: 以右表为准,在内连接的基础上增加右边有左边没有的结果

select 字段 from 表1 right join 表2 on 条件;
select * from employee right join department on employee.dep_id=department.id;

union全外连接: 显示左右两个表全部记录

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
注意：mysql不支持全外连接 full JOIN
强调：mysql可以使用此种方式间接实现全外连接

select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id;
注意: union与union all的区别: union会去掉相同的纪录,因为union all是left join 和right join合并,所以有重复的记录,通过union就将重复的记录去重了

符合条件连接查询

#以内连接的方式查询employee和department表,并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department on employee.dep_id = department.id where age > 25;

# 以内连接的方式查询employee和department表,并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc;

子查询: 效率比连表查询低

1: 子查询是将一个查询语句嵌套在另一个查询语句中
2: 内层查询语句的查询结果，可以为外层查询语句提供查询条件
3: 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4: 还可以包含比较运算符：= 、 !=、> 、<等

子查询其实就是将一个查询结果用括号括起来,这个结果也是一张表,就可以将它交给另外一个sql语句,作为它的一个查询依据来进行操作

select id from department where name='技术';

把上面的查询结果用括号括起来，它就表示一条id=200的数据，然后我们通过员工表来查询dep_id=这条数据作为条件来查询员工的name
select name from employee where dep_id = (select id from department where name='技术');

带IN关键字的子查询

# 用子查询员工平均年龄在25岁以上的部门名
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

# 连表
select department.name from department inner join employee on department.id=employee.dep_id group by department.name having avg(age)>25;
总结: 子查询的思路和解决问题一样,先解决一个然后拿着这个的结果再去解决另外一个问题,连表的思路是先将两个表关联在一起,然后在进行group by过滤等等操作,两者的思路是不一样的

# 查看技术部员工姓名
select name from employee where dep_id in (select id from department where name='技术');

# 查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);

带比较运算符的子查询
比较运算符：=、!=、>、>=、<、<=、<>

#查询大于所有人平均年龄的员工名与年龄
select name,age from employee where age > (select avg(age) from employee);

#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

带EXISTS关键字的子查询
EXISTS关字键字表示存在,在使用EXISTS关键字时,内层查询语句不返回查询的记录.而是返回一个真假值.True或False当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询.还可以写not exists,和exists的效果就是反的

#department表中存在dept_id=203,Ture
select * from employee where exists (select id from department where id=200);

#department表中存在dept_id=205,False
select * from employee where exists (select id from department where id=204);

 练习题

1、查询男生、女生的人数；

2、查询姓“张”的学生名单；

3、课程平均分从高到低显示

4、查询有课程成绩小于60分的同学的学号、姓名；

5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名；

6、查询出只选修了一门课程的全部学生的学号和姓名；

7、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

9、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

10、查询平均成绩大于60分的同学的学号和平均成绩;

11、查询所有同学的学号、姓名、选课数、总成绩；

12、查询姓“李”的老师的个数；

13、查询没学过“张磊老师”课的同学的学号、姓名；

14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

15、查询学过“李平老师”所教的所有课的同学的学号、姓名；

1、查询没有学全所有课的同学的学号、姓名；

2、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；

3、删除学习“叶平”老师课的SC表记录；

4、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 

5、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；

6、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；

7、按各科平均成绩从低到高和及格率的百分数从高到低顺序；

8、查询各科成绩前三名的记录:(不考虑成绩并列情况) 

9、查询每门课程被选修的学生数；

10、查询同名同姓学生名单，并统计同名人数；

11、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

12、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；

13、查询课程名称为“数学”，且分数低于60的学生姓名和分数；

14、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 

15、求选了课程的学生人数

16、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；

17、查询各个课程及相应的选修人数；

18、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；

19、查询每门课程成绩最好的前两名；

20、检索至少选修两门课程的学生学号；

21、查询全部学生都选修的课程的课程号和课程名；

22、查询没学过“叶平”老师讲授的任一门课程的学生姓名；

23、查询两门以上不及格课程的同学的学号及其平均成绩；

24、检索“004”课程分数小于60，按分数降序排列的同学学号；

25、删除“002”同学的“001”课程的成绩；