MySql学习day03 where子句 order by子句 聚合函数(组函数) group by子句 子查询 索引 index

1.where子句
1.1 作用
根据条件筛选数据库。符合条件表达式的行被筛选出来，不符合条件的数据行被过滤掉。
1.2 语法
where 条件表达式
1.3 比较运算符

< >= <= = !=(<>)
– 列出’张三’的成绩
select s.student_no,s.student_name,c.score
from student s join choose c
on s.student_no=c.student_no
where s.student_name=‘张三’;

– 使用where子句实现内连接

1. 语法
   select 字段列表
   from 表1,表2 where 连接条件;
2. 列出学生及其考试成绩的信息
   – 使用 join…on
   select s.student_no,s.student_name,c.score
   from student s join choose c
   on s.student_no=c.student_no;
   – 使用where
   select s.student_no,s.student_name,c.score
   from student s,choose c
   where s.student_no=c.student_no;

– 带有筛选条件的内连接
select s.student_no,s.student_name,c.score
from student s,choose c
where s.student_no=c.student_no
and s.student_name=‘张三’;

练习：列出成绩不及格的信息，包括课程编号、课程名称、学号、成绩
select c.course_no,c.course_name,ch.student_no,ch.score
from course c join choose ch
on c.course_no=ch.course_no
where ch.score<60;

select c.course_no,c.course_name,ch.student_no,ch.score
from course c,choose ch
where c.course_no=ch.course_no and ch.score<60;

1.4 SQL提供的运算符
1.4.1 between…and运算符
判断表达式的值是否在指定的闭区间
1) 语法
where 表达式 between 值1 and 值2
2) 列出成绩在[60,90]之间的信息
select * from choose
where score between 60 and 90;
3) 列出2018年选课的信息
select * from choose
where choose_time between ‘2018-01-01 00:00:00’
and ‘2018-12-31 23:59:59’;

1.4.2 is null
– 结果为null null的判断不能使用比较运算符
select * from choose where score=null;
select null=null,null!=null;

1. 语法
   where 表达式 is null
2. 示例
   – 列出成绩为null的信息
   select * from choose where score is null;

-- 列出没有班级的学生
select * from student where class_no is null;
-- 列出没有学生的班级
select c.*
   from classes c left join student s
     on c.class_no = s.class_no
       where s.student_no is null;

1.4.3 in
判断表达式的值是否位于一个列表中

1. 语法
   where 表达式 in(值1,值2,…);
2. 示例
   – 列出班号为1或2的学生的信息
   select * from student where class_no in(1,2);
   – 列出information_schema和choose数据库中表的信息
   select table_schema,table_name
   from information_schema.tables
   where table_schema in(‘information_schema’,‘choose’);

1.4.4 模糊查询
1) 运算符
like
2) 语法
where 表达式 like ‘模式’
3) 通配符
% 匹配任意长度的任意字符
_ 匹配一位任意字符
4) 示例
– 列出学生姓名中包含’王’的学生的信息
select * from student
where student_name like ‘%王%’;
– 列出姓王的学生
select * from student
where student_name like ‘王_%’;
– 需要检索的数据中包含通配符
– 列出information_schema.tables中表名中包含’’
解决方式1：
使用 \ 作为转义字符
select table_name from information_schema.tables
where table_name like ‘%_%_%’;
解决方式2：
自定义转义字符： escape ‘转义字符’
select table_name from information_schema.tables
where table_name like '%#%#_%’ escape ‘#’;

1.5 逻辑运算符
and or not
1.5.1 and运算符(逻辑与)
– 列出’张三’的课程编号为1的成绩
select s.student_no,s.student_name,c.score
from student s,choose c
where s.student_no=c.student_no
and (s.student_name=‘张三’ and c.course_no=1);
– 使用where子句实现三表内连接
– 语法
select 字段列表
from 表1,表2,表3
where 连接条件1 and 连接条件2;

select s.student_no 学号,s.student_name 姓名,
  c.course_name 课程,ch.score 成绩
 from course c,choose ch,student s
   where c.course_no=ch.course_no 
      and ch.student_no=s.student_no;

1.5.2 or运算符(逻辑或)
– 使用或运算符 列出班号为1或2的学生的信息
select * from student
where class_no=1 or class_no=2;
1.5.3 逻辑非
! not
– 列出选修课上限人数不是60的课程的信息
select * from course where up_limit!=60;
select * from course where !(up_limit=60);
select * from course where not(up_limit=60);

运算符取反：
> <=
< >=
= !=(<>)
between…and not between…and
in not in(注意NULL)
is null is not null
like not like

c_no in(1,2,null) <> c_no=1 or c_no=2 or c_no=null
<> c_no=1 or c_no=2
c_no not in(1,2,null)
<> not(c_no=1 or c_no=2 or c_no=null)
<> c_no!=1 and c_no!=2 and c_no!=null
<==> null

– 列出有成绩的信息
select * from choose where score is not null;

2. order by子句
   2.1 作用
   对结果集进行排序
   2.2 语法
   order by 排序标准 [排序方式][,排序标准 [排序方式],…]
   其中：
   排序标准：用于排序的字段或表达式
   排序方式：
   asc 升序 默认
   desc 降序
   2.3 示例
   – 降序显示成绩
   select * from choose order by score desc;
   – NULL值默认按照最小值处理
   select * from choose order by score;
   – 带where子句的排序
   select s.student_no,s.student_name,c.score
   from student s,choose c
   where s.student_no=c.student_no
   order by s.score asc;
   – 多列排序
   select s.student_no,s.student_name,c.score
   from student s,choose c
   where s.student_no=c.student_no
   order by s.student_no,c.score desc;

#错误 where子句不能字段或表达式的别名
select s.student_no 学号,s.student_name,c.score 成绩
from student s,choose c
where 学号=c.student_no
order by s.student_no,成绩 desc;

3.聚合函数(组函数)
聚合函数用于对一组数据进行汇总统计，返回一个统计结果
3.1 常用的聚合函数
count: 统计一组数据的个数
参数可以是任意类型，而且可以使用*
select count(*) from student where class_no=1;
max: 统计一组数据的最大值
min: 统计一组数据的最小值
参数可以是数值、字符串、日期等
select max(score),min(score) from choose;
select max(choose_time),min(choose_time) from choose;
sum: 统计一组数据的累加和
avg: 统计一组数据的平均值
参数要求是数值类型
select sum(score),avg(score)
from student s,choose c
where s.student_no=c.student_no and
s.student_name=‘张三’;

3.2 聚合函数对null的处理 – 忽略
select count(student_no),count(class_no) from student;
3.3 聚合函数的参数可以用distinct修饰(排重后汇总)
select count(table_type),count(distinct table_type)
from information_schema.tables;

4 group by子句
4.1 作用
将查询结果按照一个或多个字段进行分组并汇总统计(通常和聚合函数一起使用)
4.2 语法
select
from
where
group by 字段列表
4.3 示例
– 统计每个班级的人数
–注意：在分组语句中，select子句的字段列表只能是分组标准或聚合函数
select class_no,count(*) from student
group by class_no;
– 多列分组
select s.class_no,c.class_name,count(s.student_no)
from classes c,student s
where c.class_no=s.class_no
group by s.class_no,c.class_name;
#错误 where子句中不能使用聚合函数
select s.class_no,c.class_name,count(s.student_no) cnt
from classes c,student s
where c.class_no=s.class_no and count(s.student_no)>1
~~~~~~~~~~~~~~~~~~~
group by s.class_no,c.class_name;

4.4 having子句

1. 语法
   having 条件表达式
   在分组语句中，根据条件筛选符合条件的组
2. 示例
   – 列出人数超过1人的班级的信息
   select s.class_no,c.class_name,count(s.student_no) cnt
   from classes c,student s
   where c.class_no=s.class_no
   group by s.class_no,c.class_name
   having cnt>1
   order by s.class_no;

执行顺序：
from
where
select
group by
having
order by

5. 子查询
   5.1 概念
   如果一个select语句嵌套在另一条sql语句(例如select、update、delete)中，那么该select语句就称为子查询，包含子查询的sql语句就称为主查询(外层查询).
   为了标记主查询和子查询的关系，通常把子查询放到小括号中。
   子查询可以在主查询的where子句、having子句、select子句、from子句等。
   5.2 where子句中使用子查询
   5.2.1 子查询返回单值
   条件表达式可以使用比较运算符
   – 列出’张三’的成绩

   1. 查询’张三’的学号
      select student_no from student
      where student_name=‘张三’;
      – 2017001
   2. 根据学号查询成绩
      select * from choose where student_no=‘2017001’;
   3. 合并
      select * from choose where student_no=(
      select student_no from student
      where student_name=‘张三’
      );
      5.2.2 子查询返回多个值
      运算符可以使用in、not in等

   insert into student
   values(‘2017007’,‘张三’,‘21000000000’,2);

   – 如果班级中有多个’张三’,上面的语句应该改为：
   select * from choose where student_no in(
   select student_no from student
   where student_name=‘张三’
   );

练习：检索’2017自动化1班’的学生的成绩
1> 使用表连接
select ch.student_no,ch.course_no,ch.score
from classes c,student s,choose ch
where c.class_no=s.class_no and s.student_no=ch.student_no
and c.class_name=‘2017自动化1班’;
2> 使用子查询获取班号
select ch.student_no,ch.course_no,ch.score
from student s,choose ch
where s.student_no=ch.student_no
and s.class_no=(
select class_no from classes
where class_name=‘2017自动化1班’
);
3> 使用子查询获取学号
select * from choose
where student_no in(
select student_no from student
where class_no=(
select class_no from classes
where class_name=‘2017自动化1班’
)
);

5.3 在from子句中使用子查询
每一个select语句可以看成一个虚拟的内存表，可以在结果集上进一步查询
– 列出班级人数超过1人的信息
select class_no,count() cnt
from student
group by class_no
having count()>1;

select * from
(select class_no,count(student_no) cnt
from student group by class_no) s
where cnt>1;

– 列出比自己的平均分高的成绩的信息
select a.s_no,ch.score,a.a_score from choose ch,(
select student_no s_no,avg(score) a_score
from choose group by s_no) a
where ch.student_no=a.s_no and ch.score>a.a_score;

6. 索引 index
   6.1 作用
   提高查询效率
   6.2 索引和约束的之间
   系统会自动为主键、唯一、外键字段创建索引