Mysql学习随笔--8

分页：
select * from tb12 limit 10;
select * from tb12 limit 0,10;
select * from tb12 limit 10,10;
select * from tb12 limit 20,10;
select * from tb12 limit 10 offset  20;

# page = input('请输入要查看的页码')
# page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; 1
# select * from tb12 limit 10,10;2

排序：
select * from tb12 order by id desc; 大到小
select * from tb12 order by id asc; 小到大
select * from tb12 order by age desc（优先）,id desc;

取后10条数据
select * from tb12 order by id desc  limit 10;

select count(id) from userinfo5;【取数量】

分组：
select count(id),max(id),part_id from userinfo5 group by part_id;
count
max
min
sum
avg

 

 

**** 如果对于聚合函数结果进行二次筛选时？必须使用having ****
select count(id),part_id from userinfo5 group by part_id having count(id) > 1;
select count(id),part_id from userinfo5 where id > 0 group by part_id having count(id) > 1;

连表操作：[两表之间有一定的关系]

select * from userinfo5,department5
select * from userinfo5,department5 where userinfo5.part_id = department5.id

select * from userinfo5 left join department5 on userinfo5.part_id = department5.id
select * from department5 left join userinfo5 on userinfo5.part_id = department5.id
# userinfo5左边全部显示[记住 left on  就好]

# select * from userinfo5 right join department5 on userinfo5.part_id = department5.id
# department5右边全部显示

select * from userinfo5 innder join department5 on userinfo5.part_id = department5.id
将出现null时一行隐藏

多表相连【★】
select  *  from
department5
left join userinfo5 on userinfo5.part_id = department5.id
left join userinfo6 on userinfo5.part_id = department5.id

select
score.sid,
student.sid
from
score

left join student on score.student_id = student.sid

left join course on score.course_id = course.cid

left join class on student.class_id = class.cid

left join teacher on course.teacher_id=teacher.tid