MySQL阶段二——sql语句基础（2）

顺德公农庄

　　数据查询操作
　　01.创建数据表
　　（02-05练习）
　　（连接查询练习使用）
　　02.单表查询
　　03.分组统计
　　04.嵌套查询
　　05.集合查询
　　06.连接查询
　　07.连接查询与集合查询的不同
数据查询操作
01.创建数据表
　　1）创建Student表
　　（2）创建Course表
　　（3）创建SC表
（02-05练习）
　　createtablestudent
　　(
　　snochar(8)primarykey,
　　snamechar(8),
　　ssexchar(2)notnull,
　　sageint,
　　sdeptchar(20)
　　);
　　createtablecourse
　　(
　　cnochar(4)primarykey,
　　cnamechar(40)notnull,
　　cpnochar(4),
　　ccreditsmallint,
　　);
　　createtablesc
　　(
　　snochar(8)foreignkey(sno)referencesstudent(sno),
　　cnochar(4),
　　gradesmallint
　　);
（连接查询练习使用）
　　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 all=all