目录

  • 数据库约束
  • 约束类型
  • 实例
  • 聚合函数
  • 分组查询
  • 三大范式
  • 多表联查
  • 创建示例
  • 内连接
  • 外连接
  • 自连接
  • 子查询
  • 概念
  • 单行子查询
  • 多行子查询
  • 在from子句中使用子查询
  • 合并查询


数据库约束

约束类型
  • NOT NULL:指示某列不能存储 NULL 值,也就是必须赋值,否则报错;
  • UNIQUE:保证某一列的每一行都必须是唯一值;
  • DEFAULT:规定没有给列赋值时的默认值;
  • PRIMARY KEYNOT NULLUNIQUE的结合,确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录;
  • PRIMARY KEY(字段1, 字段2...):当主键由多个字段组成时,不能直接在字段后面声明主键约束,而是采用这种方式进行设置;
  • FOREIGN KEY (子表字段) REFERENCES 父表名称(父表字段):保证一个表中的数据匹配另一个表中的值的参照完整性,也就是说被设置为外键的字段,子表中该字段的值在父表中必须存在才可以,否则报错,另外在删除时,不需先删除子表,再删除父表,否则报错;
  • AUTO_INCREMENT:自增属性,只能用于整形字段的变量,被设置为该属性的字段可以不赋值,会按顺序从最大值自动增长;
  • CHECK:保证列中的值符合指定的条件,对于 MySQL 数据库,对 CHECK 子句进行分析,但是忽略 CHECK 子句;
实例
//班级表
CREATE TABLE classes (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
);
//学生表
CREATE TABLE student (
	//对于整数类型的主键,常配搭自增长auto_increment来使用。插入数据对应字段不给值时,使用最大值+1
	id INT PRIMARY KEY auto_increment,
	sn INT NOT NULL UNIQUE,
	name VARCHAR(20) DEFAULT 'unkown',
	qq_mail VARCHAR(20),
	classes_id int,//学生表中的班级信息为外键,关联班级id
	FOREIGN KEY (classes_id) REFERENCES classes(id),
	check (sex ='男' or sex='女')//忽略该约束
);

聚合函数

  • now():获取系统当前时间;
  • count(*):统计查询结果有多少条;
  • count(字段):统计指定字段为非 NULL 的有多少条;
  • sum(字段):获取某一字段值的总和;
  • max(字段):获取某一字段的最大值;
  • min(字段):获取某一字段的最小值;
  • avg(字段):获取某一字段的平均值;
-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;
-- 统计班级收集的 qq_mail 有多少个,qq_mail 为 NULL 的数据不会计入结果
SELECT COUNT(qq_mail) FROM student;

-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分,没有结果,返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;

-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;

-- 返回英语最高分
SELECT MAX(english) FROM exam_result;

-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

分组查询

  • select 要分组的字段, 聚合函数1, 聚合函数2... from 表名称 group by 要分组的字段1, 要分组的字段2...;:按照某一字段进行分组,然后使用指定的聚合函数对数据进行统计、分析;
  • select 要分组的字段, 聚合函数1, 聚合函数2... from 表名称 group by 要分组的字段1, 要分组的字段2... having 条件筛选;:分组查询中,不能使用where进行筛选,只能使用having来筛选;

三大范式

  1. 表中每个字段都必须具有不可分割原子特性,这是其他范式的基础,如果不遵循第一范式会导致某个字段在进行查询时效率降低;
  2. 表中每个字段都应该与主键完全关联,而不是部分关联,否则会导致表中存在大量冗余数据;
  3. 表中每个字段都应该与主键直接关联,而不是间接关联,否则会导致表中存在大量冗余数据;

多表联查

创建示例
  • 创建班级信息表并插入数据:
CREATE TABLE classes (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20),
	`desc` VARCHAR(100)
);

insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
  • 创建学生信息表并插入数据:
CREATE TABLE `student` (
  id int(11) PRIMARY KEY AUTO_INCREMENT,
  sn int(11) NOT NULL UNIQUE,
  name varchar(20) NOT NULL,
  qq_mail varchar(20),
  classes_id int(11) NOT NULL,
)

insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
  • 创建学生成绩表并插入数据:
CREATE TABLE score (
	id INT PRIMARY KEY auto_increment,
	score DECIMAL(3, 1),
	student_id int,
	course_id int,
	FOREIGN KEY (student_id) REFERENCES student(id),
	FOREIGN KEY (course_id) REFERENCES course(id)
);

insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
  • 创建学生班级表并插入数据:
CREATE TABLE course (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
);

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
内连接
  • 概念:假设 A 表与 B 表进行内连接,连接条件是 K,那么执行方式为,从 A 表中取出每一条数据,然后和 B 表中每一条数据进行尝试,如果 K 为真,那么就将这两行数据连接到一起;
  • 语法:
//使用...join...on...实现
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
//也可以由...where...来实现
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
  • 实例 1:查询每个同学所参加科目的总成绩以及个人信息;
select stu.sn, stu.name, stu.qq_mail, sum(score) total from student stu join score sco on stu.id = sco.student_id group by stu.id;

mysql找出每个班级排名前三的人_联合查询

  • 实例 2:查询每个同学的每一科成绩以及个人信息;
select stu.sn, stu.name, stu.qq_mail, cou.name, sco.score from student stu join score sco on stu.id = sco.student_id join course cou on sco.course_id = cou.id order by stu.id, cou.name;

mysql找出每个班级排名前三的人_联合查询_02

外连接
  • 概念:外连接有两种形式——左连接、右连接,假设 A 表与 B 表进行左(右)连接,连接条件是 K,那么执行方式为,从 A(B)表中取出每一条数据,然后和 B(A)表中每一条数据进行尝试,不管是否为真,左(右)边表的数据一定会显示完全;
  • 语法:
-- 左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
  • 实例:查询所有同学的各科成绩以及个人信息,如果该同学没有成绩,也需要显示;
#使用
select stu.sn, stu.name, stu.qq_mail, cou.name, sco.score from student stu left join score sco on stu.id = sco.student_id left join course cou on sco.course_id = cou.id order by stu.id, cou.name;

mysql找出每个班级排名前三的人_联合查询_03

自连接
  • 概念:同一张表连接自身进行查询;
  • 实例:查询所有“计算机原理”成绩比“Java”成绩高的学生信息;
select stu.sn, stu.name, stu.qq_mail, s1.score 计算机原理, s2.score Java from student stu join score s1 on s1.student_id = stu.id join score s2 on s2.student_id = stu.id join course c1 on c1.id = s1.course_id and c1.name = '计算机原理' join course c2 on c2.id = s2.course_id and c2.name = 'Java' and s1.score > s2.score;

mysql找出每个班级排名前三的人_数据库_04

子查询
概念
  • 概念:子查询是指嵌入在其他 SQL 语句中的select语句,把一个子查询当做一个临时表使用,也叫嵌套查询;
单行子查询
  • 实例:查询与"不想毕业"同班级的同学信息;
select * from student where classes_id = (select classes_id from student where name='不想毕业');

mysql找出每个班级排名前三的人_mysql_05

多行子查询
  • [NOT] IN:方括号中NOT为可选项,(不)在某个查询结果中;
  • 实例 1:查询"语文"或"英文"课程的成绩信息;
//in
select * from score where course_id in (select id from course where name = '语文' or name = '英文');
//not in
select * from score where course_id not in (select id from course where name != '语文' and name != '英文');

mysql找出每个班级排名前三的人_数据库_06

  • 实例 2:查询成绩表中科目重复的分数;
//首先先插入一些重复的成绩信息
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),
-- 菩提老祖
(60, 2, 1);

select * from score where (student_id, course_id) in (select student_id, course_id from score group by student_id, course_id having count(*) > 1);

mysql找出每个班级排名前三的人_聚合查询_07

  • [NOT] EXISTS:先执行前面的语句得到结果,拿结果中的某些字段去进行后面的查询(后面的查询中某些条件需要用到这些字段);
  • 实例:查询"语文"或"英文"课程的成绩信息;
//exists
select * from score where exists (select * from course where id = score.course_id and (name = '语文' or name = '英文'));
//not exists
select * from score where not exists (select * from course where id = score.course_id and (name != '语文' and name != '英文'));

mysql找出每个班级排名前三的人_聚合查询_08

在from子句中使用子查询
  • 技巧:把一个子查询当做一个临时表使用;
  • 实例:查询所有比"中文系2019级3班"平均分高的成绩信息;
select * from score join (select avg(score) avg_score from score sco join student stu on sco.student_id = stu.id join classes cla on cla.name = '中文系2019级3班' and cla.id = stu.classes_id) tmp on score.score > tmp.avg_score;

mysql找出每个班级排名前三的人_mysql找出每个班级排名前三的人_09

合并查询
  • 概念:在实际应用中,为了合并多个select的执行结果,可以使用集合操作符UNIONUNION ALL,使用UNIONUNION ALL时,前后查询的结果集中,字段需要一致;
  • UNION:该操作符用于取得两个结果集的并集,当使用该操作符时,会自动去掉结果集中的重复行;
  • UNION ALL:该操作符用于取得两个结果集的并集,当使用该操作符时,不会去掉结果集中的重复行;
  • 实例:查询 id 小于 3,或者名字为"Java"的课程;
//union
select * from course where id < 3 union select * from course where name = 'Java';
//union all
select * from course where id < 3 union all select * from course where name = 'Java';

mysql找出每个班级排名前三的人_联合查询_10