egon笔记:
1 单表查询
select distinct 字段1,字段2,字段3 from 表
where 约束条件
group by 分组字段
having 过滤条件
order by 排序字段
limit n; def from(file):
f=open(file)
return f def where(f,条件):
lines=[]
for line in f:
if 条件:
lines.append(line) def group():
dic={
'male':迭代器,
'female':迭代器
}
return dic def having():
pass def select():
res1=distinct(having_res)
res2=order(res1)
limit(res2) def distinct():
pass def order():
pass def limit():
pass f=from('emp')
lines=where(f,条件)
group_res=group(lines,字段)
having_res=having(group_res,条件)
select(having_res) 2 多表查询
select distinct 字段1,字段2,字段3 from 左表 left join 右表
on 链表条件
where 约束条件
group by 分组字段
having 过滤条件
order by 排序字段
limit n; #示例1：找出年龄大于25岁的员工以及员工部门名,
select emp.name emp_name,dep.name dep_name from emp inner join dep on emp.dep_id = dep.id
where age > 25; #查询平均年龄在25岁以上的部门名
select dep.name from emp inner join dep
on emp.dep_id = dep.id
group by dep_id
having avg(age) > 25
; select name from dep where id in
(select dep_id from emp group by dep_id having avg(age) > 25); #查看技术部员工姓名
select emp.name from dep inner join emp
on emp.dep_id = dep.id
where dep.name = '技术'
; #查看>1人的部门名
select dep.name from emp inner join dep
on emp.dep_id = dep.id
group by dep_id
having count(*) > 1; select t1.id,t1.name,t1.age,t1.hire_date,t1.post,t2.* from emp_old as t1
inner join
(select post,max(hire_date) max_hire_date from emp_old group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_hire_date
; id province city commit_time x y z
1 山东省 烟台市 2017-01-01 11:11:11 1 2 3
2 山东省 烟台市 2017-01-01 12:11:11 1 2 3
3 山东省 烟台市 2017-01-03 12:11:11 1 2 3 create view emp2dep as
select emp.*,dep.name as dname from emp inner join dep
on emp.dep_id = dep.id;
一般情况下,我们设计一个库一个表的时候,会尽可能科学的去考虑以后的使用过程中会出现的情况,因为一个表格或者一个库都是供很多人使用的,这种需要大家一起共同使用的东西都会尽可能设计的简单一些,方便交流,所以,一般一个表格会有10-30个字段,会尽可能的满足需求的情况下去原子化一个表格,尽可能去避免多表联查的情况,因为多表联查会很复杂,对使用者而言是一种挑战,工作效率会受到影响.
#建表
create table department(
id int,
name varchar()
); create table employee(
id int primary key auto_increment,
name varchar(),
sex enum('male','female') not null default 'male',
age int,
dep_id int
); #插入数据
insert into department values
(,'技术'),
(,'人力资源'),
(,'销售'),
(,'运营'); insert into employee(name,sex,age,dep_id) values
('egon','male',,),
('alex','female',,),
('wupeiqi','male',,),
('yuanhao','female',,),
('liwenzhou','male',,),
('jingliyang','female',,)
; #查看表结构和数据
mysql> desc department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int() | YES | | NULL | |
| name | varchar() | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+ mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int() | NO | PRI | NULL | auto_increment |
| name | varchar() | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int() | YES | | NULL | |
| dep_id | int() | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+ mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| | 技术 |
| | 人力资源 |
| | 销售 |
| | 运营 |
+------+--------------+ mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| | egon | male | | |
| | alex | female | | |
| | wupeiqi | male | | |
| | yuanhao | female | | |
| | liwenzhou | male | | |
| | jingliyang | female | | |
+----+------------+--------+------+--------+ 表department与employee
二 多表连接查询
#重点：外链接语法 SELECT 字段列表
FROM 表1 INNER|LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;
1 交叉连接：不适用任何匹配条件。生成笛卡尔积
笛卡尔乘积的形成,是同时select两个表格,然后按照前一个表格的每一行去关联后一个表格的每一行,这样就得到了笛卡尔乘积
mysql> select * from employee,department;
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| | egon | male | | | | 技术 |
| | egon | male | | | | 人力资源 |
| | egon | male | | | | 销售 |
| | egon | male | | | | 运营 |
| | alex | female | | | | 技术 |
| | alex | female | | | | 人力资源 |
| | alex | female | | | | 销售 |
| | alex | female | | | | 运营 |
| | wupeiqi | male | | | | 技术 |
| | wupeiqi | male | | | | 人力资源 |
| | wupeiqi | male | | | | 销售 |
| | wupeiqi | male | | | | 运营 |
| | yuanhao | female | | | | 技术 |
| | yuanhao | female | | | | 人力资源 |
| | yuanhao | female | | | | 销售 |
| | yuanhao | female | | | | 运营 |
| | liwenzhou | male | | | | 技术 |
| | liwenzhou | male | | | | 人力资源 |
| | liwenzhou | male | | | | 销售 |
| | liwenzhou | male | | | | 运营 |
| | jingliyang | female | | | | 技术 |
| | jingliyang | female | | | | 人力资源 |
| | jingliyang | female | | | | 销售 |
| | jingliyang | female | | | | 运营 |
+----+------------+--------+------+--------+------+--------------+
2 内连接：只连接匹配的行
两个表格里你有的部分,我也有,然后这一部分就会显示出来,就类似于集合里面的交集,两者共同拥有的部分拿出来合成一个表格.
#找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
#department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| | egon | | male | 技术 |
| | alex | | female | 人力资源 |
| | wupeiqi | | male | 人力资源 |
| | yuanhao | | female | 销售 |
| | liwenzhou | | male | 技术 |
+----+-----------+------+--------+--------------+ #上述sql等同于
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
3 外链接之左连接：优先显示左表全部记录
在上面的inner join 的基础上,把左边的表格的全部内容显示出来,也就是说我们先显示出来交集的部分,然后把非交集的部分里面左边表格的内容显示出来,剩余的部分就是右边表格里面无法与左表匹配的内容,就用null来填充,
#以左表为准，即找出所有员工信息，当然包括没有部门的员工
#本质就是：在内连接的基础上增加左边有右边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;
+----+------------+--------------+
| id | name | depart_name |
+----+------------+--------------+
| | egon | 技术 |
| | liwenzhou | 技术 |
| | alex | 人力资源 |
| | wupeiqi | 人力资源 |
| | yuanhao | 销售 |
| | jingliyang | NULL |
+----+------------+--------------+
4 外链接之右连接：优先显示右表全部记录
同理可得,在inner join的基础上,把右边的表格里面的内容显示出来,也就是说我们先显示出来交集的部分,然后把非交集的部分里面右边的表格的内容显示出来,剩余的部分就是左表里面无法与右表匹配的内容,就用null来填充,
#以右表为准，即找出所有部门信息，包括没有员工的部门
#本质就是：在内连接的基础上增加右边有左边没有的结果
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;
+------+-----------+--------------+
| id | name | depart_name |
+------+-----------+--------------+
| | egon | 技术 |
| | alex | 人力资源 |
| | wupeiqi | 人力资源 |
| | yuanhao | 销售 |
| | liwenzhou | 技术 |
| NULL | NULL | 运营 |
+------+-----------+--------------+
5 全外连接：显示左右两个表全部记录
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
#注意：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
;
#查看结果
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| | egon | male | | | | 技术 |
| | liwenzhou | male | | | | 技术 |
| | alex | female | | | | 人力资源 |
| | wupeiqi | male | | | | 人力资源 |
| | yuanhao | female | | | | 销售 |
| | jingliyang | female | | | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | | 运营 |
+------+------------+--------+------+--------+------+--------------+ #注意 union与union all的区别：union会去掉相同的纪录
三 符合条件连接查询
#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > ; #示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age >
order by age asc;
四 子查询
#：子查询是将一个查询语句嵌套在另一个查询语句中。
#：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
#：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#：还可以包含比较运算符：= 、 !=、> 、<等
带in关键字的子查询:
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > ); #查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术'); #查看不足1人的部门名
select name from department
where id in
(select dep_id from employee group by dep_id having count(id) <=);
带比较运算符的子查询:
#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | |
| wupeiqi | |
+---------+------+
rows in set (0.00 sec) #查询大于部门内平均年龄的员工名、年龄
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;
以上是egon博客里面的一道题,第24题
http://www.cnblogs.com/linhaifeng/articles/7267596.html#_label5 这里的博客地址,第24题
其余的题目解题思路以及方法答案如下
egon 博客练习题: http://www.cnblogs.com/linhaifeng/articles/7267596.html#_label5
use db1 or db2
1、查询所有的课程的名称以及对应的任课老师姓名
select cname,tname from course left join teacher on course.teacher_id=teacher.tid ;
select cname,tname from teacher right join course on course.teacher_id=teacher.tid; 2、查询学生表中男女生各有多少人
select sid,count(1) from student gruop by gender; 3、查询物理成绩等于100的学生的姓名
# select *from student inner join score on student.sid=score.student_id;
select sname from student inner join score on student.sid=score.student_id
group by course_id having course_id=2; 4、查询平均成绩大于八十分的同学的姓名和平均成绩
select avg(num),sname from student inner join score on student.sid=score.student_id
group by student_id having avg(num)>80; 5、查询所有学生的学号，姓名，选课数，总成绩
select student.sid,sname,sum(num),count(1)
from student inner join score on
student.sid=score.student_id group by sname ; 6、 查询姓李老师的个数
select count(1) from teacher where tname like '李%'; 7、 查询没有报李平老师课的学生姓名
select sname from student where sid not in (
select student_id from course inner join score on course.cid=score.course_id
where cid in (2,4) group by student_id ); 8、 查询物理课程比生物课程高的学生的学号
select A.student_id from (select student_id,num from score where course_id ='')
A ,(select student_id,num from score where course_id ='') B
where A.student_id = B.student_id and B.num > A.num;
=========================================================================================================
select student_id from (select student_id,num n1 from score where course_id=1)as a inner join
(select student_id,num n2 from score where course_id=2) as b on a.student_id=b.student_id where n2>n1; ---------这里报错,不知道怎么回事 select student_id,num n1 from score inner join student on student.sid=score.student_id
where course_id=1
select student_id,num n2 from score inner join student on student.sid=score.student_id
where course_id=2
# 要先查出来物理的成绩,然后再查出来生物的成绩,单独查出来还是可以操作的,然后就是
拼接的问题,拼接的时候需要使用到括号,然后括号里面需要把上面查出的物理成绩放进去,然后as 一个名字,
同理再来一个括号把生物的成绩拿出来放到一个括号里面,as 一个别名,然后再基于括号
里面的表格的基础上进行两个表格的关联,最后再在关联的表格上进行查询,确实需要三个
表格,两两关联形成了分别的两个表格,第三个就是把这前面的两两结果关联到一起.这个情况在王老师讲的
那些题里面有类似的题目,由于都没有整理,所以,看起来很陌生,接触的时候还是有很大障碍,
多做一做题目会缓解很多,所以egon的那24道题必须要做完,实在不行周末做也要搞定它.
这件事必须要做完,不可以再拖下去了,本来这个mysql就几乎没有做什么有难度的题目,老师讲的那些
难一点的题都没有做,也没有整理,这个题再不做完就真的是废掉了.
=========================================================================================================== 9、 查询没有同时选修物理课程和体育课程的学生姓名
select sname from student where sid not in (
select student_id from course inner join score on course.cid=score.course_id
where cid in (2,3) group by student_id ); 10、查询挂科超过两门(包括两门)的学生姓名和班级
select sname,caption from student inner join class on student.class_id=class.cid where sid=
(select student_id from score where num<60 group by student_id having count(1)>1); select student_id from score where num<60 group by student_id having count(1)>1; 11 、查询选修了所有课程的学生姓名
select sname from student where sid in
(select student_id from score group by student_id having count(1)=4); select student_id from score group by student_id having count(1)=4; 12、查询李平老师教的课程的所有成绩记录
select num from score where course_id in (select
cid from course inner join teacher on teacher_id=tid
where tname='李平老师'); 13、查询全部学生都选修了的课程号和课程名
select cid,cname from score inner join course on cid=course_id
group by course_id having count(1)=16; 14、查询每门课程被选修的次数
select count(1) from score group by course_id; 15、查询只选修了一门课程的学生姓名和学号
select sname,student_id from student left join score on student.sid=student_id
group by course_id having count(1)=1; 16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
select num from score group by num desc; 17、查询平均成绩大于85的学生姓名和平均成绩
select student_id,avg(num) from score left join student on
student.sid=student_id group by student_id; 18、查询生物成绩不及格的学生姓名和对应生物分数
select num,sname from student,(
select num,student_id from course left join score on cid=course_id
where cname="生物" and num<60) un where sid=student_id ; 19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，
不是所有课程)平均成绩最高的学生姓名
select sname from student where sid=(
select student_id from score ,(
select cid from teacher inner join course on tid=teacher_id where
tname="李平老师") te_co where cid=course_id group by
student_id order by avg(num) desc limit 1); 20、查询每门课程成绩最好的前两名学生姓名{这一题是最难的，先留着吧，后面再做，如果有时间的话,有时间,必须有时间,}
SELECT
score.student_id,
t3.course_id,
t3.first_num,
t3.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
score.num >= t3.second_num
AND score.num <= t3.first_num
ORDER BY
course_id;
这一道,第20题还是没有求出最终的结果,我们的学生姓名还没有得到,需要更新,,,,,,,,
现在就来更新它:
SELECT
student_id,
num,
course_id
FROM score r1
WHERE (SELECT count(1)
FROM (SELECT DISTINCT
num,
course_id
FROM score) r2
WHERE r2.course_id = r1.course_id AND r2.num > r1.num) < 2
ORDER BY course_id, num DESC;
我们先根据课程分组得到每门课程的成绩,然后根据分数去重,以免同时好几个人都是同一个分数,排序用倒序,得到前两个成绩(这样就是每门课程成绩最好的前两个成绩的分数)
再拿着分数去找对应的学生的id,然后去学生表格里面找到学生的姓名
select num from score where course_id=1 group by
num order by num desc limit 2; 这里我们得到了课程1的最高的两个分数结果, select num from score where course_id=2 group by
num order by num desc limit 2; 课程2的最高的两个分数
select num from score where course_id=3 group by
num order by num desc limit 2; 课程3最高的两个分数
select num from score where course_id=4 group by
num order by num desc limit 2; 课程4最高的两个分数 select student_id from score where course_id=1 and num=(
select num from score where course_id=1 group by
num order by num desc limit 1
); 这里我们不可以使用limit 因为得到的是两个分数的结果只能用in,然而我们的limit不能用in作为子查询方法 21、查询不同课程但成绩相同的学号，课程号，成绩
select DISTINCT s1.student_id s1si,s2.student_id s2si,s1.course_id s1ci,s2.course_id s2ci,
s1.num s1n,s2.num s2n from score as s1, score as s2 where s1.num = s2.num and
s1.course_id != s2.course_id; 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
select distinct sname,cname from (
select *from score where course_id not in (
select cid from teacher inner join course on
tid=teacher_id where tname="李平老师"))as uni
inner join student on uni.student_id=student.sid,course where cid=course_id;
==============================================================================
这里实现了学生姓名,但是还没有查出课程名,把课程表直接加到后面即可,
使用了联表以及两个表格一起查询,上面已经得到了最终的结果
select distinct sname from (
select *from score where course_id not in (
select cid from teacher inner join course on
tid=teacher_id where tname="李平老师"))as uni
inner join student on uni.student_id=student.sid;
============================================================================== 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
select course_id from score where student_id=1; select distinct student_id,sname from score inner join student
on student_id=student.sid where course_id in
(select course_id from score where student_id=1) ; 24、任课最多的老师中学生单科成绩最高的学生姓名 我们应该先找到任课最多的老师是谁,
然后从他教过的课里面反过来找他的课程都有哪些,
然后在score表格里面找到那些课程所对应的学生的分数,
最后在从中找出单科成绩最高的学生id,
拿着这个id去学生表格里面找对应的学生名字
select sname from student where sid in (
select distinct student_id from score where course_id in
(select cid from course where teacher_id=
(select teacher_id from course group by teacher_id order by count(1) desc limit 1)
) and
num in(select max(num) from score where course_id in (
select cid from course where teacher_id=
(select teacher_id from course group by teacher_id order by count(1) desc limit 1)
)group by course_id having max(num))); 解题思路:
①这里是找到了任课最多的老师id--->2
select teacher_id from course group by
teacher_id order by count(1)desc limit 1;
而这样写的话,即便我不知道表的内容我也一样可以得到想要的结果,这样更加严谨,写活了
select teacher_id from course group by teacher_id having count(1)>1;这样写是因为我知道结果,只有一个老师教了两门课程,所以这样写死了 ②这里是根据上面得到结果(找到了任课最多的老师)拿过来用得到的老师的课程
select cid from course inner join teacher on tid=teacher_id where
tname="李平老师"; ③根据上面的课程得到了这些课程的最高分数
select max(num) from score where course_id in (2,4)
group by course_id having max(num); select student_id,sname,num from score left join student s on
score.student_id=s.sid where course_id in (2,4)order by num desc limit 5;
这里的limit 5 是因为提前查过了score表格里面学了任课最多的老师,他的的课程分数最高的学生的名字和id,知道有的同学是 ④根据最高分数倒推得到这些分数的同学的id,要去重,
有可能有的同学这些课程都是最高分
select distinct(student_id) from score where course_id in (2,4) and
num in (select max(num) from score where course_id=2); ⑤根据同学的id去学生表格里找到学生名字{这里把上面的id合并到这里来了}
select sname from student where sid in(select distinct student_id from score where course_id in (2,4) and
num in (select max(num) from score where course_id=2)); [1得到课程,任课最多的老师的课程]
select cid from course where teacher_id=(
select teacher_id from course group by
teacher_id order by count(1)desc limit 3
);
[2得到分数,任课最多的老师的课程对应的学生的最高的分数]
select max(num)from score where course_id in (
select cid from course where teacher_id=(
select teacher_id from course group by
teacher_id order by count(1)desc limit 1
)
)
group by course_id having max(num);
[3根据最高分数倒推学生的id,直接在得到的id结果外面包一层select student然后就可以拿到学生名字了] select sname from student where sid in (
select distinct student_id from score where course_id in
(select cid from course where teacher_id=
(select teacher_id from course group by teacher_id order by count(1) desc limit 1)
) and
num in(select max(num) from score where course_id in (
select cid from course where teacher_id=
(select teacher_id from course group by teacher_id order by count(1) desc limit 1)
)group by course_id having max(num)));