Mysql学习笔记-检索表记录

Mysql / 作者：

参考书籍：Mysql核心技术与最佳实践 /孔祥盛编著. -北京：人民邮电出版社,2014.5 ISBN 978-7-115-33769-6

一、select语句概述

语法形式：

select 字段列表
from 数据源
[where 条件表达式]
[group by 分组字段|having 条件表达式]
[order by 排序字段[asc|desc]]

1、select子句指定字段列表

  • * 字段列表为数据源全部字段
  • 表名.* 多表查询时，指定表的全部字段
  • 字段列表 指定字段

注意：

  1. 可以使用as 关键字指定别名
  2. 多表查询时必须指定前缀。

特殊用法

select version(),now() as nowtime,pi(),1+2,null=null,null!=null,null is null;

clavesScreenShot_20160124_182908

：

2、使用谓词过滤记录

2.1使用 谓词distinct 过滤结果集中重复记录

select distinct department_name from classes;

 

2.2 使用谓词limit 查询某几行

select * from student limit 0,3
#limit [start,]length;
#start表示从第几行检索，初始值为0
#length 表示需要检索的总数

3、使用from子句指定数据源

实际应用中，为了避免数据冗余，需要将一张大表划分为很多小表。检索是通常将小表组成大表。

在select中可以指定多个数据源，轻松实现从多张数据表中提取数据。

语法规则：

from 表名1 [连接类型] join 表名2 on 表1和标2连接条件

说明：SQL标准中，连接类型主要分为inner(内连接)和outer(外连接，又分left、right、full)

3.1 内连接（inner join）

内连接将两个表中满足指定连接条件的记录连接成新的结果集，并舍弃所有不满足连接的记录。内连接是默认的连接类型。

语法形式：

from 表1 [inner] join 表2 on 表1和表2之间的连接条件

例如：检索分配有班级的学生信息(内连接为默认类型)

select student_no,student_name,student.class_no,class_name,department_name from student join classes on student.class_no=classes.class_no;

clavesScreenShot_20160124_185239

3.2外连接（outer join）

外连接又分为左连接（left join）、右连接（right join）、完全连接（full join）

左连接或右连接时，只能过滤一张表，另外一张表不能能过滤。

完全连接时，所有结果都出现在结果集中。

3.2.1左连接语法

from 表1 left join 表2 on 表1 表2之间的连接条件

说明：结果集中包含表1的全部记录。若表中没有满足连接条件的记录，则结果集表2相应字段为NULL

例如：检索所有学生对于的班级信息

select student_no,student_name,student.class_no,class_name,department_name from student left join classes on student.class_no=classes.class_no;

 

clavesScreenShot_20160124_190232

3.2.2右连接

语法形式：

from 表1 l join 表2 on 表1 表2之间的连接条件

例如：检索所有班级的学生信息

 select classes.class_no,student_no,student_name,student.class_no,class_name,department_name from student right join classes on student.class_no=classes.class_no;

clavesScreenShot_20160124_190550

总结：内连接、外连接区别在于内连接去除所有不符合连接条件的记录，外连接保留其中一个表所有记录。

4、多表链接

语法形式：

from 表1 [连接类型] join 表2 on 表1和表2之间的连接条件
[连接类型] join 表3 on 表2和表3之间的连接条件

例如：从student、score、choose表中查询学生成绩信息：

select student.student_no,student_name,course.course_no,course_name,score from student inner join choose on student.student_no=choose.student_no inner join course on choose.course_no=course.course_no;

clavesScreenShot_20160124_192418

二、使用where子句过滤

1、单一条件过滤集

常见表达式：= > >= < <= != !<(不小于) !>(不大于)

2、is NULL 运算符

判断是否为NULL，和is not NULL 相反。

 select * from choose where score is NULL;

clavesScreenShot_20160124_194005

3、逻辑运算符

3.1 逻辑非！

select * from course where !(up_limit=60);

 

3.2and逻辑运算符

连接两个布尔表达式，当两个表达式都为true 时，整个逻辑表达式才为true

布尔表达式1 and 布尔表达式2
表达式 [not] between 起始值 and 终止值

比如成绩在70~90之间：

where score between 80 and 90

3.2 or 逻辑运算符

连接两个布尔表达式，两个都为false 时，整个逻辑表达式才为false

4、使用like模糊查找

like 运算符用于判断一个字符串是否与给定的模式相匹配。

模式是一种特殊的字符串，包含普通字符和通配符。

语法形式：

字符表达式 [not] like 模式

Mysql中常用通配符：

  • % 匹配多个或多个字符组成的任意字符串
  • _  匹配任意一个字符
 select * from student where student_name like '张_';

 select * from student where student_name like '%三%';

clavesScreenShot_20160124_195243

三、使用order by 子句对结果集排序

语法格式：

order by 字段名 1 [asc|desc] [...,字段名 n [asc|desc]]

例如：成绩降序排序

select * from choose order by score desc;

四、聚合函数汇总结果集

1、count() 统计结果集中行数

select count(*) 学生人数 from student;

clavesScreenShot_20160124_195958

2、sum() 用于数值型字段累加求和

3、avg() 用于数值型字段求平均值

4、max() min() 最大值 最小值

五、group by 子句对记录分组统计

语法格式：

group by 字段列表[having 条件表达式] [with rollup]

单独使用group by子句对记录分组时，仅仅显示分组中某一条记录。因此单独使用group 子句分组实际意义不大，通常与聚合函数一起使用。

例如：

select * from student group by class_no;

clavesScreenShot_20160124_200503

1、group by 子句与聚合函数

例如：统计每个班学生人数

select class_name ,count(student_no) from classes left join student on student.class_no = classes.class_no group by classes.class_no;

clavesScreenShot_20160124_200837

例如：统计每个学生已经选了多少门课程、以及该生最高分、总分、平均成绩

select student.student_no,student_name,count(course_no),max(score),min(score),sum(score),avg(score) from student left join choose on student.student_no=choose.student_no group by student.student_no;

clavesScreenShot_20160124_201241

2、group by 子句与having 子句

having 子句用于设置分组或聚合函数的过滤筛选条件，通常与group by子句一起使用。

having 与where 格式相似，语法格式如下：

having 条件表达式

例如：检索平均成绩高于70分的学生及平均成绩（having 不能替换为where）

select choose.student_no,student_name,avg(score) from choose join student on choose.student_no=student.student_no group by student.student_no having avg (score)>70;

 

clavesScreenShot_20160124_202047

重要：对于包含where group by having 的语句执行顺序为：where=>group by=>having

3、group by 与 group_concat()

3.1 group_concat()可以把集合中字符串连接起来，如：

select group_concat('JAVA1','JAVA2','JAVA3');

clavesScreenShot_20160124_202428

3.2 group_concat还可以按照分组字段，将另一个字段的值使用逗号连接起来。

例如：统计所有班级的学生名单

 select class_name ,group_concat(student_name) , concat(student_name) from classes left join student on student.class_no = classes.class_no group by classes.class_no;

clavesScreenShot_20160124_202810

4、group by 与with rollup 选项

如果分组后，需要在分组后加上一条汇总记录，这个任务可以通过with rollup选项实现。

例如：统计每个班学生人数，并在查询结果集最后一条记录附上所有班级的总人数。

select classes.class_no,count(student_no) from classes left join student on student.class_no=classes.class_no group by classes.class_no with rollup;

 

clavesScreenShot_20160124_204113

六、合并结果集

使用union 科技将多个select 语句查询结果集合成一个结果集，语法格式：

select 字段列表1 from table1
union [all]
select 字段列表2 from table2 ...

说明：字段列表1与字段列表2字段个数必须相同，且具有相同数据类型。

union与union all 区别: union时，数据库会筛选掉重复记录，但是用union all时，Mysql会直接合并两个结果集，效率高于union。

例如：检索所有学生、教师信息

select student_no,student_name,student_contact from student union all select teacher_no ,teacher_name,teacher_contact from teacher;

clavesScreenShot_20160124_205039

七、子查询

定义：一个select语句能够返回单个值或者多个值，且该select语句嵌套在另一个SQL语句中。那么该Select语句称为‘子查询’，包含了子查询的SQL语句叫做‘主查询’。

  • 通常将子查询写在小括号内
  • 子查询一般用于主查询where having 子句中，与逻辑运算符一起构成where having 筛选条件。

1、子查询与比较运算符

如果子查询返回单个值，则可以将一个表达式的值与子查询结果集进行比较。

例如：

 select class_name,student.student_no,student_name,course_name,score from classes join student on student.class_no = classes.class_no join choose on choose.student_no=student.student_no join course on choose.course_no = course.course_no where score > (select avg(score) from student ,choose where student.student_no = choose.student_no and student_name='张三');

clavesScreenShot_20160124_210941

2、子查询与in运算符

子查询金昌鱼in运算符一起使用，用于将一个表达式的值与子查询返回的一列值进行比较，如果表达式的值是此列值中任何一个值，则条件表达式为true，否则为false。

例如：检索2012 自动化1班所有学生成绩：

select student.student_no,student_name,course_name,score 
from course join choose on choose.course_no=course.course_no
join student on choose.student_no=student.student_no
where student.student_no in (
select student_no
 from student join classes on student.class_no=classes.class_no
 where classes.class_name='2012 自动化1班'
 );

clavesScreenShot_20160125_154333

3、子查询与exists逻辑运算符

exists 逻辑运算符用来检测子查询结果集中是否包含记录。如果结果集中恰好包含一条记录，则exists的结果为true，否则为false。在exists前面加上not 时，恰好相反。

例如：检索没有申请选修课的教师的信息：

select * from teacher
where not exists(
select *from course where course.teacher_no=teacher.teacher_no
);

4、子查询与any运算符

any运算符通常与比较运算符一起使用。使用any运算符时，通过比较运算符将一个表达式的值与子查询返回的一列值逐一比较，若某次比较结果为true，则整个表达式为true，否则为false。

例如：检索‘2012 自动化2班’比‘2012 自动化1班’最低分高的学生信息。

select student.student_no,student_name,class_name
from student join classes on student.class_no = classes.class_no
join choose on choose.student_no=student.student_no
where class_name='2012 自动化2班' and score> any(
select score
from choose join student on student.student_no = choose.student_no
join classes on classes.class_no=student.class_no
where class_name='2012 自动化1班'
);

 

clavesScreenShot_20160125_155816

5、子查询与all运算符

all运算符通常与比较运算符一起使用。使用all运算符时，通过比较运算符将一个表达式的值与子查询返回的一列值逐一比较，若每次的比较结果都为true ，则整个表达式的值为true，否则为false。

例如：检索‘2012 自动化2班’比‘2012 自动化1班’最高分高的学生信息。

select student.student_no,student_name,class_name
from student join classes on student.class_no = classes.class_no
join choose on choose.student_no=student.student_no
where class_name='2012 自动化2班' and score> all(
select score
from choose join student on student.student_no = choose.student_no
join classes on classes.class_no=student.class_no
where class_name='2012 自动化1班'
);

clavesScreenShot_20160125_160309

九、正则表达式

语法结构：

字段名 [not] regexp [binary] '正则表达式'
 select * from course where course_name regexp 'java';

clavesScreenShot_20160125_161121