SELECT高级操作

having
例4：统计1班的学生人数，列出班号和人数
select 1 班号,count() 人数 from stu where cno=1;
或者：
select cno 班号，count() 人数 from stu group by cno having cno=1;
having相对于where来说会有些浪费资源
视图view
创建视图语法：create view vtb_name as select_statement
视图主要作用：提高数据安全性，简化查询语句
视图仅仅保存select语句，其中select语句中的表称为基表，视图对应的数据放在基表中的，基表数据发生变化，视图的结果集也会产生变化。
查看有哪些视图：show table status\G;
查看视图的定义：show create table view_name;
多表连接查询
创建2个测试表a和b
create table a(id int,name varchar(10));
create table b(id int,loc char(10));

insert into a values(1,‘a’)
insert into a values(2,‘b’)
insert into a values(4,‘d’)

insert into b values(1,‘x’)
insert into b values(2,‘y’)
insert into b values(3,‘z’)
语法
-不使用表别名：select table1.column,table2.column from table1 [inner] [ringht][left] join table2 on table1.col1=table2.col1;
使用表别名：select a.column,b.column from table1 a[inner] [ringht][left] join table2 b on a.col1=b.col1;
分类及概念：
1：交叉连接，不指定连接条件，结果叫做笛卡尔积，记录数为a表记录数m乘以1b表记录数n
如：select *from a join b 或 select *from a cross join b
2：内连接，指定连接条件，结果集包含符合连接条件的记录数
如 select *from a inner join b on a.id=b.id或select *from a join b on a.id=b.id;
3：左连接，指定连接条件，结果包含左表全部记录，右表中符合连接条件的记录和右表中不符合连接条件的记录（用null填充）
如：select *from a left join b on a.id=b,id或select *from a left outer join b on a.id=b,id；
4：右连接，指定连接条件，结果包含右表全部记录，左表中符合条件的记录不符合条件记录的用null填充；
如：select *from a right join b on a.id=b.id;或select from a right outer join b on a.id
多表连接查询-交叉连接
无连接条件，结果记录数为33=9
select *from a join b;
多表连接查询-内连接
select *from a inner join b on a.id=b.id
多表连接查询-左连接
select *from a left join b on a.id=b.id
多表连接查询-右连接
select *from a right join b on a.id=b.id
多表连接查询-连接条件
多表链接条件可以分为等值和非等值两种：
等值连接条件：select *from a join b on a.id=b.id 2条记录
非等值连接条件：select *from a join b on a.id>b.id 4条记录
select *from a join b on a.id<b.id 3条记录
以上a.id=b.id;a.id>b.id;a.id<b.id三种查询记录数加起来等于两个表交叉连接数量
其他写法：select *from a join b on a.id between 1 and 3 and b.id<4;
多表连接查询-示例
例子1：显示学生的学号，姓名，性别，班号，班级名称以及他们的班主任姓名，按照班号和学号排序；
select s.sno 学号,s.sname 姓名,s.sex 性别,s.scno 班号,c.cname 班名,c.teacher 班主任 from stu s inner join class c on s.cno=c.cno order by s.cno,s.sno;

例子2：显示全部学生的学号，姓名，性别，班号，班级名称以及他们的班主任姓名，无班主任的显示‘暂无’，按照班号和学号排序
select s.sno 学号,s.sname 姓名,s.sex 性别,s.cno 班号,c.cname 班级名称,ifnull(c.teacher,‘暂无’) 班级名称 from stu s left join class c on s.cno=c.cno order by s.cno,s.sno;

例子3：显示所有学生的学号，姓名，性别，身高，体重，班号，BMI指数（体重/身高^2）,BMI表中对应的体态，低值，高值和性别
select s.sno 学号,s.sname 姓名,s.sex 性别,s.height 身高,s.weight 体重,s.cno 班号,round(weight/(height/100height/100),2)BMI指数,b.lval 低值,b.hval 高值,b.bname 体态,b.sex 性别 from stu s left join bmi b on s.sex=b.sex and round(weight/(height/100height/100),2) between lval and hval;

例子4：基于例3的结果，把肥胖的同学和他们的班主任找出来
select s.sno 学号,s.sname 姓名,s.sex 性别,s.height 身高,s.weight 体重,s.cno 班号,round(weight/(height/100height/100),2)BMI指数,b.lval 低值,b.hval 高值,b.bname 体态,b.sex 性别,c.cname 班级名, c.teacher 老师 from stu s left join bmi b on s.sex=b.sex and round(weight/(height/100height/100),2) between lval and hval left join class c on s.sno=c.sno where b.bname=‘肥胖’;
复合查询
复合查询指用集合运算符对多个查询结果进行运算，产生新的查询结果集。mysql常用集合运算符包含以下2种：
-union
对两个结果集进行并集操作，重复行只取一次，同时进行默认规则排序。
-union all
对两个结果集进行并集操作，包括所有重复行，不进行排序
复合查询示例
创建示例表
create table class1 as select *from class where cno=1;
insert into class1 values(5,‘5班’,‘董卓’)
select *from class;
select *from class1;
例1：求表class和class1的并集，重复记录只显示一次
select *from class union select *from class1;
默认排序为增序，顺序列1，列2，列3……
也可以自定义排序，order by 写在语句的最后
select *from class union select *from class1 order by 2 desc,3,1;
例2：求表class和class1的并集，重复记录重复显示
select *from class union all select *from class1;结果默认不排序
例3：求表class和class1的交集
mysql无intersect集合运算符，交集运算可以通过多表连接实现；
select c.cno,c.cname,c.teacher from class c join class1 c1 on c.cno=c1.cno;
例4：求表class和class1的差集，即显示class表中在class1表中没有的行记录
mysql无minus集合运算符，差集可以通过多表连接实现
第一步：select *from class c left join class1 c1 on c.cno=c1.cno;
第2步：select *from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;
第3步：select c.cno,c.cname,c.teacher from class c left join class1 c1 on c.cno=c1.cno where c1.cno is null;
例5：求表class1和class的差集，即显示class1表中在class表中没有的行记录
select c1.cno,c1.cname,c1.teacher from class c right join class1 c1 on c.cno=c1.cno where c.cno is null;
或者
select c1.sno,c1.cname,c1.teacher from class1 c1 left join class c on c1.cno=c.cno where c.cno is null;
子查询
select语句结构
SELECT 列1，列2 … from TABLE_NAME WHERE 列 = 值 GROUP BY 分组列 HAVING 分组列 = 值 ORDER BY 列
上面标红的位置还可以嵌套额外的SELECT语句（子查询），与外部SELECT语句（主查询）结合起来使用，用一个查询语句实现更为复杂的任务。那些嵌套的SELECT语句往往被称为子查询；子查询需要用括号( )括起来。
分类
非关联子查询：子查询可以单独于主查询执行，仅执行1次，效率较高
关联子查询：子查询不能单独于主查询执行，如果主查询有N行，子查询将执行N次，效率相对较低，但灵活度高
子查询-非关联子查询
例1：查询学生中哪些人比张飞的体重重？
select *from stu where weight>(select weight from stu where sname=‘张飞’)

例2：2班3班中哪些同学的身高比1班的平均身高高？
select *from stu where cno in(1,2) and height>(select avg(height) from stu where cno=1)
例3：每个班的高考状元都是谁？
select *from stu where (cno,score) in (select cno,max(score) in stu group by cno) and cno is not null;

例4：哪些同学的体重比所有班的平均体重都重？
select *from stu where weight>all(select avg(weight) from stu where cno is not null group by cno);
->all运算符：比所有的值都大；<all运算符，比所有值都小；
->any运算符：比最小的大就行；<any运算符，比最大的小就行；
例5：哪些同学的身高高于本班的平均身高？
select from stu s,(select cno,avg(height) havg from stu where cno is not null group by cno) a where s.cno=a.cno and height>havg and s.cno is not null;
例6：不用多表连接方式，列出3班学生姓名和3班的班主任
select sname 学生姓名,(select teacher from class where cno=3)班主任 from stu where cno=3;
子查询-关联子查询
例1：过程说明
特点：子查询不能单独执行，主查询必须起个别名
单独执行：select teacher from class i where i.cno=o.cno; 报错，不知道o.cno的o表是什么，o是外部主查询的别名，脱离了外部查询，子查询执行不了
例2：不用多表连接方式，根据学生赵云的性别、身高和体重，查看他的bmi指标是否合适？
select sname 姓名,sex 性别,height 身高,weight 体重,(select bname from bmi i where o.weight/(o.height/100o.height/100) between i.lval and i.hval and i.sex=o.sex )体态 from stu o where o.sname=‘赵云’；
例3：使用关联子查询，在已分班学生中列出身高高于本班平均身高的学生。
select from stu o where o.cno is not null and o.height>(select avg(height) from stu i where i.cno=o.cno )
练习1：体重最重的同学的班主任是谁?
select sname,weight,teacher from stu s,class c where s.cno=c.cno and weight=(select max(weight) from stu);
select sname,weight,teacher from (select * from stu where weight=(select max(weight) from stu)) a,class b where a.cno=b.cno;
select sname,weight,teacher from (select * from stu where weight>=all(select weight from stu)) a,class b where a.cno=b.cno;
select o.sname,o.weight,(select teacher from class i where i.cno = o.cno) teacher from stu o where weight = (select max(weight) from stu);
练习2：未分班的同学中哪些同学的体重比一些已分班的各班最重的同学重?
mysql> select * from stu where cno is null and weight>any(select max(weight) from stu where cno is not null group by cno);
练习3：从学生表和班级表中找出姓曹的人，并标明其角色，学生或者教师
mysql> select sname 姓名,‘学生’ 角色 from stu where sname like ‘曹%’ union all select teacher,‘教师’ 角色 from class where substring(teacher,1,1)=‘曹’;
练习4：所有新同学外出实训，单位要求学校给每个同学的名字制作工牌，不同字的用料不同，价格不同，本着不能浪费的原则统计一下新生名字中有多少个‘张’，多少个‘曹’，多少个‘刘’，多少个‘羽’，没有的字不要做。
算出名字最多几个字
mysql> select max(char_length(sname)) 最多字数 from stu;
用substring函数切割名字，用union all运算符叠加字，形成的结果集作为内联视图，再按照不同字分组统计每个字的个数
mysql> select zi 字,count() 数量 from (select substring(sname,1,1) zi from stu union all select substring(sname,2,1) from stu union all select substring(sname,3,1) from stu) t group by zi;