【数据库】03_sql语句02

分页查询

数据太多。就要找是第几页。

语法：limit a,b;

意思：a是从哪里开始偏移，索引从0开始计算，b是从个数

select * from student limit 2,3;
 
# 结果就是从下标2开始的3个人，也就是012开始算起，查找出234，编号就是345

聚合函数

count：查个数

sum：求和

min：求最小

max：求最大

avg：求平均值

select count(*) from ttable;
select count(sex) from ttable;
 
select sum(score) from ttable where id='01';
select min(score) from ttable where id='01';
select max(score) from ttable where id='01';
select avg(score) from ttable where id='01';

分组查询

语法：group by 分组的依据 having 分组的条件。

select c1,sum(c2) from table group by c1;
# 输出的结果是两列，第一列是c1，第二列是求和
# 而结果是在c1的结果中分得组
 
select x,sum(score) sum from ttbale group by x having sum>=200;
# 以x为分组，也就是说后续都是以x为主要的标识，然后第二列是求和，起临时别名叫做sum
# 然后和大于200的才能出现

分组排序：order by 排序根据 asc/desc，升降序。如果不写，默认是asc

where和having

where是查询之前就存在，having是查询之后计算出来的，例如sum之后就得用having了。

select id,sum(score) sum from ttable group by sum having sum>=200 order by sum desc;
select sex, count(sex) from ttable group by sex; 

联合查询

把查询结果拼到一起。

语法：一次的查询结果 union all 另一次的查询结果

select count(*) from ttable where sex='男'
union all
select count(*) from ttable where sex='女';

分组查询快，但是联合查询适合与其他情况 

---

练习

假设有表ttable，列名分别是学生学号id、性别sex、分数score、课程名project、学生名student

表：studenttable学生信息表，coursetable课程表，sctable学生选课表

问题汇总：

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

2、查询至少选修两门课程的学生学号

3、求上一题学生对应的信息

4、查询选修了全部课程的学生信息

5、查询没有选修了全部课程的学生信息

6、查询学过张三老师课程同学的信息

7、查询没学过张三老师课程同学的信息

8、查询每个同学01课程的成绩和个人信息

9、查询01课程分数>02课程分数的同学的学生信息

# 查询每门课程被选修的学生数
# 一般有每，就是说明要分组了
select project,count(student) from ttable group by project;
 
# 查询至少选修两门课程的学生学号
# 可以分两步：
# 查询每个学生选修的课程数
select student,count(projet) num from ttable group by student;
# 课程数>=2的学生学号
select student,count(projet) num from ttable group by student having num>=2;
 
# 求上一题学生对应的信息
select * from ttable where student in (
    select student from ttable group by student having count(projet)>=2
);
# 如果没有num这一列需要显示，那么就在后面出现
# select查询结果可以作为一列或者一个结果集出现在另一个查询语句当中
 
# 选修课程少于两门课程的学生信息
select * from ttable where student not in(
    select student from ttable group by project having count(project)>=2
);
 

select时候，可以作为一个数据集。

# 查询选修了全部课程的学生信息
# 先查询全部课程
select count(project) from ttable;
# 每个学生选修的课程个数
select count(project) from ttable group by student;
# 两个个数相等的学生id
select student from ttable where count(project)=(select count(project) from ttable);
# 注，其中的ttable表不是一张
# 根据学生id查询学生信息
select * from ttable where student in (
    select student from ttable where count(project)
    =(select count(project) from ttable)
);

# 查询学过张三老师课程同学的信息
#
select teacher from teachertable where teacher='张三';
#
select project from coursetable where teacher in (#1);
# 
select student from sctable where course in (#2);
# 
select * from ttable where student in (#3);

根据老师名字查询老师id，根据老师id查课程id，根据课程id查谁选了，再查这些人的信息。

# 查询每个同学01课程的成绩和个人信息
select student,score from sctable where course='01';
select course from sctable where course='01';
 
select *,(
    select student,score from sctable where course='01' 
    and
    sctable.student=studentable.student
) 01课程成绩 
from studenttable where student in (
    select course from sctable where course='01'
);

这里注意的是，如果是表的拼接，那么就要让对应信息相同，即两个表.列名想等。

# 查询01课程分数>02课程分数的同学的学生信息
select score from scoretable where course='01' and scoretable.student=coursetable.student;
 
select *,(#1) 01score,(#2),02score from studenttable having 01score>02score;

 having表示的就是，之前没有的条件，我们的条件是新增的。

还可以：将select查询的结果，作为一个新的表

select *,(#1) 01score,(#2),02score from studenttable having 01score>02score;
 
select * from (
select *,(#1) 01score,(#2),02score from studenttable having 01score>02score
) a where a.01>a.02;
 
having 01score is not null and 02score is not null;

判断非空，is not null

视图

优化sql语句的。

是虚拟表，可以不满足范式要求。

视图；由一个或多个表，导出的临时表，不需要满足范式的要求。

视图view，视图创建好之后，会长久存在于数据库中，查询语句就可以通过使用视图来代替

# 创建视图
create view 视图名 as (select语句);
create view myview as (select...);
# 使用视图
select * from myview where 
# 删除视图
drop view myview;

视图的优缺点：

数据库是通过服务器传，进行数据库传递，因此数据库的语句都是在内部

优点：减少网络流量，保证sql语句吧刽有问题

缺点：视图并没有提高查询效率，一般是查询内容较多的情况用视图