MySQL复习2

高级查询

准备

create database greatselect;
use greatselect;

drop table if exists `class`;
create table `class` (
    `cid` int(11) not null auto_increment,
    `caption` varchar(32) not null,
    primary key (`cid`)
)engine = innoDB AUTO_INCREMENT=5 default charset = utf8;

create table if not exists `teacher` (
    `tid` int(11) not null auto_increment,
    `tname` varchar(32) not null ,
    primary key (`tid`)
) engine = innoDB auto_increment=6 default charset = utf8;

drop table if exists `course`;
create table `course` (
    `cid` int(11) not null auto_increment,
    `cname` varchar(32) not null,
    `teacher_id` int(11) not null,
    primary key (`cid`),
    key `fk_course_teacher` (`teacher_id`),
    constraint `fk_course_teacher` foreign key (`teacher_id`) references `teacher` (`tid`)
)engine = innoDB auto_increment=5 default charset = utf8;

drop table if exists `student`;
create table `student` (
    `sid` int(11) not null auto_increment,
    `gender` char(1) not null ,
    `class_id` int(11) not null ,
    `sname` varchar(32) not null ,
    primary key (`sid`),
    key `fk_class` (`class_id`),
    constraint `fk_class` foreign key (`class_id`) references `class` (`cid`)
)engine = innoDB auto_increment=17 default charset = utf8;

drop table if exists `scocre`;
create table `score` (
    `sid` int(11) not null auto_increment,
    `student_id` int(11) not null ,
    `course_id` int(11) not null ,
    `num` int (11) not null ,
    primary key (`sid`),
    key `fk_score_student` (`student_id`),
    key `fk_course_id` (`course_id`),
    constraint `fk_score_student` foreign key (`student_id`) references `student` (`sid`),
    constraint `fk_score_course` foreign key (`course_id`) references `course` (`cid`)
)engine = innoDB auto_increment=53 default charset = utf8;

show tables;

基础查询

-- 全部查询
select * from student;
-- 只查询部分字段
select `sname`, `class_id` from student;
-- 别名 列名 不要用关键字
select `sname` as ‘姓名’, `class_id` as '班级ID' from student;
-- 把查询出来的结果的重复记录去掉
select distinct `class_id` from student;

条件查询

-- 查询姓名为lennlouis的学生信息
select * from `student` where `name` = 'lennlouis';
-- 查询性别为 男，且班级为 2 的学生信息
select * from `student` where `gender` = '男' and `class_id` = 2;

范围查询

-- 查询班级id 1 到 3 的学生信息
select * from `student` where `class_id` between 1 and 3;

判空查询

-- is null 判断造成索引失效
select * from `student` where `class_id` is not null;
select * from `student` where `class_id` is null;

-- 字符串不为空
select * from `student` where `gender` <> '';
-- 字符串为空
select * from `student` where `gender` = '';

模糊查询

-- 使用 like关键字，“%”代表任意数量的字符，“_”代表占位符
-- 查询名字为 m开头的学生信息
select * from `teacher` where `tname` like 'l%';
-- 查询姓名里第二个字为 ‘e’的小学生的信息
select * from `teacher` where `tname` like '_e%';

分页查询

-- 分页查询主要用于查看第 N条 到第 M条的信息，通常和排序查询一起使用
-- 使用limit关键字，第一个参数表示从条记录开始显示，第二个参数表示要显示的数目。表中默认第一条记录的参数为0
-- 查询第二条到第三条内容
select * from `student` limit 1, 2;

查询后排序

-- 关键字：order by field, asc：升序， desc：降序
select * from `score` order by `num` asc;
\-- 多个字段排序
select * from `score` order by `course_id` desc, `num` desc

聚合查询

聚合函数	描述
sum ()	计算某一列的总和
avg ()	计算某一列的平均值
max ()	某一列的最大值
min ()	某一列的最小值
count ()	某一列的行数

select sum(`num`) from `score`;
select avg(`num`) from `score`;
select max(`num`) from `score`;
select min(`num`) from `score`;
select count(`num`) from `score`;

分组查询

-- 分组加 group_concat
select `gender`, group_concat(`age`) as ages from `student` group by `gender`;
-- 可以把查询出来的结果根据某个条件来分组显示
select `gender` from `student` group by `gender`;
-- 分组加聚合
select `gender`, count(*) as num from `student` group by `gender`;
-- 分组条件
select `gender`, count(*) as num from `student` group by `gender` having num > 6;

联表查询

INNER JOIN

取两张表有对应关系的记录

select
	cid
from
	`course`
inner join `teacher` on course.teacher_id = teacher.tid;

LEFT JOIN

在内联的基础上保留左边表上没有对应关系的记录

select 
	course.cid
from
	`course`
left join `teacher` on course.teacher_id = teacher.tid;

RIGHT JOIN

在内联的基础上保留右边表上没有对应关系的记录

select
	course.cid
from
	`course`
right join `teacher` on course.teacher_id = teacher.tid;

子查询/合并查询

单行子查询

select * from course where course.teacher_id = (select tid from teacher where tname = 'wood');

多行子查询

多行子查询返回多行记录的子查询

• IN 关键字：运算符可以检测结果集中是否存在特定的值，如果检测成功就执行外部的查询。
• EXISTS 关键字：内层查询语句不返回查询记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true），否则，将返回一个假值（false）。当返回的值为 true 时，外层查询语句将进行查询；当返回的值为 false 时，外层查询语句不进行查询或查询不出任何记录。
• ALL 关键字：表示满足所有条件。使用 ALL 关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。
• ANY 关键字：允许创建一个表达式，对子查询的返回值列表，进行比较，只要满足内层子查询条件中的任意一个比较条件，就返回一个结果作为外层查询条件。
• 在 FROM 子句中使用子查询：子查询出现在 from 子句中，这种情况下将子查询当做一个临时表使用。

select * from student where class_id in (select cid from course where teacher_id = 2)

select * from student where exists(select cid from course where cid = 5);

select student_id, sname from (select * from score where course_id = 1 or course_id = 2) as A left join student on A.student_id = student.sid;