MySQL表的增删查改（进阶）

所有操作：主要讲了数据库的约束，表之间的关系，新增，聚合查询，联合查询等内容。是一篇博客所有操作的记录。 · 844d186 · 风夏/mysql_learning - Gitee.com

1. 数据库约束

1.1 约束条件

• not null -指定某个列不能储存null值。

• unique -保证某一列的每一行必须有唯一的值。

• default -规定没有给列赋值时的默认值，如果不修改,是null.

• primary key -not null和unique的结合，确保某一列（或两个列多个列的结合）有唯一标识，有助于更容易更快速的找到表中的一个特定记录。

• foreign key -保证一个表中的数据匹配另一个表中的值的参照完整性。

1.2 null约束

创建表的时候，可以指定某一列不为空：

1.3 unique：唯一约束

指定sn列为唯一的，不可重复的：

1.4 default:默认值约束

在指定插入数据的时候，如果没有插入到某一列，将会用默认值填充

【错误】stdent 应当为student;

1.5 primary key:主键约束

指定id列为主键：

对于整数类型的主键，我们经常搭配着自动增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1（如果中间我们手动赋值后，再插入数据时，系统会使用最大值+1）

id int primary key auto_increment,

1.6 foreign key:外键约束

外键用于关联其他表的主键或者唯一键，语法

foreign key(字段名）references 主表（列）

【案例】

• 先创建班级表classes,id为主键

• 创建学生表，一个学生对应一个班级，一个班级对应多个学生。使用id作为主键，使用classes_id作为外键，关联到班级表id。

• 展示效果

当我们在查询的时候，我们可以通过对应的班级序号，找到相应的班级。

如果学生表中插入的班级序号在班级表里不存在，也是不被系统允许的。

【缺点】

1. 在插入学生的信息时，由于关联到班级表，需要检查classes_id的合法性，这可能会拖慢效率。

2. 在删除班级表的信息时，如果在学生表中被使用，是不允许删除的。

2.表的设计

表之间的关系

2.1一对一

一对一的关系：人和身份号，学生和学号，员工和工号等，都是一一对应的，每一个人学生都有且仅有一个学号，一个学号仅对应一名学生。

2.2一对多

一对多的关系：一个学生对应一个班级，一个班级拥有很多学生；一个人拥有一个家庭，一个家庭又有多名成员等。

2.3多对多

多对多的关系：一个学生有很多课程，每个课程有很多学生。

3.新增

将已有的（部分）数据转移到新的表格中。

语法：

Insert into table_name [(column)] select();

【实例】创建一张用户表，设计有name, email, sex, mobile字段，需要把已有的学生信息复制过来，可以复制的字段有name, qq_mail

【优势】这样我们能更加快速的插入已有的信息到新的表格中，大大减少的操作的复杂度。

4.查询

4.1聚合查询

4.1.1聚合函数

常见的统计总数、计算平均值等操作，可以使用聚合函数来实现，常见的聚合函数有：

函数	说明
count([distinct] expr)	返回查询到的数据的数量；
sum([distinct] expr)	返回查询到的数据的总和，不是数字就没有意义；
avg([distinct] expr)	返回查询到的平均值，不是数字没有意义；
max([distinct] expr)	返回查询到的数据的最大值，不是数字没有意义；
min([distinct] expr)	返回查询的数据的最小值，不是数字没有意义；

【案例】

• count()

【注意】

1. 第一种和第二种查询方式，没有区别，统计的都是全部学生的条数，即使有一个学生的任何信息都为null，也会被统计。

2. 第三种只能统计某一列不为null的记录条数，如果有一个学生的qq_mail为null,这条记录也会被统计，如果记录时默认值，不是null,同样也会被统计。

• sum()

• avg()

• max()和min()

4.1.2 group by语句

select 中使用group by子句可以对指定列进行分组查询。但是需要满足以下条件：使用group by 进行分组查询时，select指定的字段必须是'分组依据字段’,其他字段若想出现在select中则必须包含在聚合函数中。

select column1, sum(column2),... from table_name group by column1,...;

【案例】

4.1.3 having

group by子句进行分组以后，可以对分组结果进行条件过滤，这是我们就用having。

having和where的区别：
where是对整张表中的数据根据条件进行逐条的过滤，然后在进行分组。
having是先对表进行分组，然后根据having后面的条件进行过滤。
有些是有我们只能使用having，比如：只输出公司部门员工的平均工资大于20000元的部门，这是只有先分组进行统计，然后然后才能筛选。
但是很多时候having和where是通用的。

4.2联合查询

实际开发过程总一个表的信息是不够的，需要多张表联合查询。多表查询是对多张表的数据取笛卡尔积：

【注意】关联查询可以对关联表使用别名

初始化数据：

-- 联合查询
-- 初始化数据
insert into classes(name, desc) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

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

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

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),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

4.2.1内连接

语法:

select 字段 from 表1 as 别名1 [inner] join 表二 as 别名二 on 链接条件 and 其他条件；
select 字段 from 表1 别名，表2 别名2 where 链接条件 and 其他条件；

因为内连接使用到了笛卡尔积，就会产生很多的无效数据（比如：同学1的信息对应到同学2的成绩），我们需要使用on来排除无效数据。

【案例】

1. 查询许仙同学的成绩。

2. 查询所有同学的总成绩，以及同学的个人信息：

3. 查询所有同学的成绩，以及同学的信息

4.2.2外连接

外连接分为左外链接和右外链接。如果联合查询，左侧的表完全显示我们就说是左外链接；右侧的表完全显示我们就说是左外链接。

语法：

-- 左外链接，表1完全显示
select 字段名 from 表1 left join 表2 on链接条件；
-- 有外链接，表2完全显示
select 字段名 from 表1 right join 表2 on 链接条件;

【案例】查询所有同学的成绩，及同学的个人信息，如果某同学没有成绩，也需要显示

【解析】3表关联查询，前两张表先进行笛卡尔积，然后进行筛选，然后再与第三张表进行笛卡尔积，再进行筛选，得到最后的结果。

【注意】进行笛卡尔积的时候，要谨慎，不能两张表都有相当大的数据量，这样可能导致程序崩溃。

4.2.3自链接

自链接是指在同一张表链接自身进行查询，即：对自己进行笛卡尔积.

【案例】

显示所有计算机原理成绩比Java成绩高的成绩信息

【难点】所有的成绩信息都是在同一列，不能使用列与列之间的比较。

4.2.4子查询

子查询是指嵌入在其他sq;语句中的select语句，也叫嵌套查询

• 单行子查询：返回一行记录的子查询

查询与 不想毕业 同学的同班同学

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

查询 语文 或 英文 课程的信息

4.2.5合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符union, union all。使用union和union all时，前后查询的结果几种，字段需要一致。

• union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

案例：查询id<3或者名字为英文的课程：

-- 合并查询
-- 查询id<3或者名字为英文的课程
select * from course where id < 3
union
select * from course where name = '英文';

-- 或者使用or
select * from course where id < 3 or name = '英文';

• union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

案例：查询id<3，或者名字为java的课程