数据查询操作
01.创建数据表
（02-05练习）
（连接查询练习使用）
02.单表查询
03.分组统计
04.嵌套查询
05.集合查询
06.连接查询
07.连接查询与集合查询的不同
数据查询操作
01.创建数据表
1）创建Student表
（2）创建Course表
（3）创建SC表
（02-05练习）
create table student
(
sno char(8) primary key,
sname char(8),
ssex char(2)not null,
sage int,
sdept char(20)
);
create table course
(
cno char(4) primary key,
cname char(40)not null,
cpno char(4),
ccredit smallint,
);
create table sc
(
sno char(8)foreign key(sno)references student(sno),
cno char(4),
grade smallint
);
（连接查询练习使用）
CREATE TABLE `join_class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c_name` char(7) DEFAULT NULL,
`room` char(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
CREATE TABLE `join_teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_name` varchar(10) DEFAULT NULL,
`gender` enum('male','female','secret') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
CREATE TABLE `join_teacher_class` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t_id` int(11) DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
`days` tinyint(4) DEFAULT NULL,
`begin_date` date DEFAULT NULL,
`end_date` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
02.单表查询
01）按照目标列查询
select sno,sname,sage from student;
02）目标列包含表达式查询
select sname,'year of birth:',2014-sage,lower(sdept) from student;
03）查询结果集中修改列名称
select sname,'year of birth:'as birth,2000-sage BIRTHDAY,DEPARTMENT=lower(sdept) from student;
04）取消重复行
select sno from sc;
select DISTINCT sno from sc;
05）简单条件查询
select sname from student where sdept='cs';
06）按范围查询
select sname,sdept,sage from student where sage between 20 and 23
07）查询属性值属于指定集合的行
select sname,sno,ssex from student where sdept in('IS','MA','CS');
08）模糊查询
select sname,sno,ssex from student where sname like'刘%'
09）查询空值
select sno,cno from sc where grade is null;
10）结果集排序
select * from student order by sdept,sage desc;
升序|降序（asc|desc）
校对规则决定排序关系
允许多字段排序，先找第一个字段排序，如果不能区分，再按照第二个字段排序，以此类推。
注：如果是分组，则应该使用分组字段进行排序的group by语法。
11）多重条件查询
select sno,sname from student where sdept='IS'and sage>23
12）limit语法
限制获取的记录数量，放在最后。
语法：limit offset,row_count
Offset:偏移量（类似数组下标，索引位从0开始）
Row_count:总记录数，如果数量大于，则获取余下的。
03.分组统计
01）聚集函数的使用
Count(*) 统计元组的个数
Count([distinct|all] 列名) 统计一列中值的个数
Sum([distinct|all] 列名) 计算一列值的总和（此列必须是数值型）
Avg([distinct|all] 列名) 计算一列值的平均值（此列必须是数值型）
Max([distinct|all] 列名) 求一列值中的最大值
Min([distinct|all] 列名) 求一列值中的最小值
select count(*) from student;
select count(distinct sno)from sc
select max(grade) from sc
select sum(grade) 总分,avg(grade) 均分,max(grade) 最高分
from sc group by cno
02）分组统计
如果分组后还要求按一定的条件对这些组进行筛选，最终只输出满足指定条件的组，则可以使用having短语指定筛选条件。
Where子句和having短语的区别在于作用对象不同。Where子句作用与基本表或视图，从中选择满足条件的元组。Having短语作用与组，从中选择满足条件的组。
select cno 课程号,count(*) 人数,avg(grade) 均分,max(grade) 最高分
from sc group by cno
having avg(grade)>90
注意：where子句中是不能用聚集函数作为条件表达式的，这里条件表达式用having
04.嵌套查询
l 带有IN谓词的子查询
select Sno,Sname,Sdept from Student
where Sdept IN ( select Sdept from Student where Sname= '刘晨');
l 带有比较运算符的子查询
select Sno, Cno from SC x
where Grade >= ( select AVG(Grade) from SC y
where y.Sno=x.Sno);
l 带有修饰符的比较运算符引出的子查询
select Sname,Sage from Student
where Sage < ALL ( select Sage from Student where Sdept= 'CS')
AND Sdept <> 'CS' ;
>any <any >=any <=any
>all<all>=all <=all
l 带有exists引出的子查询
select Sname from Student
where EXISTS (select * from SC where Sno=Student.Sno AND Cno= '1')
05.集合查询
l 集合并
select * from Student where Sdept= 'CS'
UNION
select * from Student where Sage<=19
l 集合交
select * from Student where Sdept='CS'
INTERSECT
select * from Student where Sage<=19
l 集合差
select * from Student where Sdept='CS'
EXCEPT
select * from Student where Sage <=19;
注意：
规定，多个select语句的检索到的字段数，必须一致。
更加严格的是，数据类型上，应该也有要求一致；但是，MySQL内部会做类型转换处理，要求是能够转换成功；
提示：
在语句的括号不是必须的。
检索结果中的列名称问题：第一条select语句的列名而定。
排序：
子语句结果的排序：
01.将子语句包裹子括号内
02.子语句的order by，
只有在order bby配合limit是才生效。原因是：union在做子语句时，会对limit子句的order by优化（忽略）。
06.连接查询
01.连接概述
每个实体，一个表，一个业务逻辑，使用多个实体的数据，多张表应该一起使用，将多个表的记录连接起来。
02.总体思路
将所有的数据，按照某种条件，连接起来 ，再进行筛选处理。
03.连接的分类
根据连接的条件不同，分类如下：
内连接：数据内部的连接，要求，连接的多个数据都必须存在才能进行连接。
外连接：如果负责连接的一个或多个数据不真实存在，则称为外连接。
自然连接
内外连接的区别：
#内连接
SELECT join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days
FROM join_teacher INNER JOIN join_teacher_class
ON join_teacher.id=join_teacher_class.t_id;
#外连接
SELECT join_teacher.t_name,join_teacher_class.begin_date,join_teacher_class.days
FROM join_teacher LEFT OUTER JOIN join_teacher_class
ON join_teacher.id=join_teacher_class.t_id;
注意：多了一个孙武
04.连接过程
01）先拿左表数据依次与右表连接（左表第一条与右表第一条，左表第一条与右表第二条...）
02）判断条件 on，条件相等，保留结果。
03）循环1，2步
05.内连接
01）数据内部的连接，要求，连接的多个数据都必须存在才能进行连接。
02）语法：tb_left inner join tb_right on 连接条件；
03）内连接的处理：内连接，在连接的时候，是可以忽略连接条件的。意味着，所有的左表的数据，都要与右表的记录做一个连接。共存在m*n个连接。这种连接，就称之为，交叉连接，或者笛卡儿积连接。此时可以使用cross join代替inner join。
注意：MySQL中的crossjoin与innerjoin相同，但在数据库的定义上，交叉连接就是笛卡儿积，是没有条件的inner join。
MySQL inner join是默认的连接方案，可以省略inner。
04）有条件的内连接：
会在连接时过滤非法的连接。
Where的写法：数据过滤，理解上，数据安装交叉连接完成后，再做数据过滤。
On的写法：在连接时，就对数据进行判断。
Using的写法：using要求，负责连接的两个实体之间的字段名称一致。
建议是，在有同名字段时，使用using，而在通用条件的时候，使用on。
在数据过滤时（不是指的连接过滤）使用where。
注意：查询条件，与外连接通用（外连接，不能使用where作为连接条件）
Using条件进行连接：
05）多种组合连接，查询结果相同但是逻辑不同
06.外连接
如果负责连接的一个或多个数据不真实存在，则称为外连接。
01）分类
左外连接
右外连接
全外连接（MySQL暂时不支持）
02）左外连接（left outer join:outer可以省略）
在连接的时候，如果出现左边表数据连接不到右边表的情况，则左边表的数据在最终结果中保留，右边表采用虚拟数据。如果 出现右边表数据连接不到左边表数据，右边表数据被丢弃。
03）右外连接（right join）
与左外连接相反。
07.自然连接
通过MySQL自己的判断完成连接过程：
不需要指定连接条件。MySQL会使用多表内的，相同字段，作为连接条件。
分类：自然连接分成内外之分：
内，natural join
外，左外，右外
Natrual left join
Natural right join
例：
create table one(
One_id int,
Ont_data char(1),
Public_filed int
);
Create table two(
Two_id int,
Two_data char(1) not null default ‘t’,
Putblic_field int
);
注意：连接时，支持多表连接
08.练习1
01）表
查询某个班级所有学生的全部信息；
Select s.*,si.* from info_class as c left join info_student as s on c.id=s.clsss_id
Left join info_student_info as si on s.id=si.id where c.class_name=’php’;
09.练习2（重要面试题）
上面两个是表，下面是要得到的结果
查询4月份所有的比赛结果：
Match left join class on match.host_id=class.id left join class on match.guest_id=class.id
010.练习3
Subquery（子查询）+join
Id |
Dept_name |
Parent_id |
1 |
行政部 |
0 |
2 |
教学部 |
0 |
3 |
PHP |
2 |
4 |
Java |
2 |
5 |
.net |
2 |
6 |
平面 |
2 |
7 |
咨询 |
1 |
8 |
财务 |
1 |
任务一：获得所有的顶级部门
任务二：获得教学部门的所有子部门
07.连接查询与集合查询的不同
Union查询的时记录之间的组合
Join查询的是字段上的组合
本文转自 叫我北北 51CTO博客，原文链接:http://blog.51cto.com/qinbin/1953415
