MySQL表进阶操作

数据库约束

约束类型

• NOT NULL：指示某列不能存储 NULL 值，也就是必须赋值，否则报错；
• UNIQUE：保证某一列的每一行都必须是唯一值；
• DEFAULT：规定没有给列赋值时的默认值；
• PRIMARY KEY：NOT NULL和UNIQUE的结合，确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录；
• 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;

• 实例 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;

外连接

• 概念：外连接有两种形式——左连接、右连接，假设 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;

自连接

• 概念：同一张表连接自身进行查询；
• 实例：查询所有“计算机原理”成绩比“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;

子查询

概念

• 概念：子查询是指嵌入在其他 SQL 语句中的select语句，把一个子查询当做一个临时表使用，也叫嵌套查询；

单行子查询

• 实例：查询与"不想毕业"同班级的同学信息；

select * from student where classes_id = (select classes_id from student where name='不想毕业');

多行子查询

• [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 != '英文');

• 实例 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);

• [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 != '英文'));

在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;

合并查询

• 概念：在实际应用中，为了合并多个select的执行结果，可以使用集合操作符UNION，UNION ALL，使用UNION和UNION 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';