MySql-增查改删【二】

数据库约束

数据库的约束其实就是校验，主要是用在建表时。

约束类型
NOT NULL ： 指示某列不能存储 NULL 值。
UNIQUE ：保证某列的每行必须有唯一的值。
DEFAULT ：规定没有给列赋值时的默认值。
PRIMARY KEY ：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY ： 保证一个表中的数据匹配另一个表中的值的参照完整性。

非空约束(NOT NULL)

--设置新的学生表结构
create table stu1(
	id bigint not null,
    name varchar(255)
);

设置完成之后查看表结构可以发现此时id这个字段不允许为null,

当我们要在id插入空值时，就会报错：

唯一约束(UNIQUE)

指定了唯一约束列的值，在列中是唯一的，相当于身份证号。

create table stu2(	
	id bigint unique,
    name varchar(255)
);

此时往这张表中添加id字段重复的数据就会报错：

默认约束(DEFAULT)

create table stu3(
	id bigint,
    name varchar(255) default '无名氏'
);

在没指定默认值之前，默认为NULL，当指定默认值之后，就会用默认值填充。

查看此时的表结构：

主键约束(PRIMARY KEY)

主键约束具备非空和唯一的特性，标记每一行记录，生成一个对于当前表来说唯一的数字。可以通过auto_increment这个关键字实现自增操作，从而实现自增主键。

create table stu4(
	id bigint primary key auto_increment,
    name varchar(255)
);

查看这张表的结构：

在写入数据时，不指定主键的值时，自增主键会自动完成自增操作，

新写入的值总是以当前表中主键的最大值+1作为新值插入。

注意：一个表只能有一个主键，不能有多个主键，但是可以用多个列合并为一个主键，称为复合主键(多个列组成的组合不能重复)。

外键约束(FOREIGN KEY)

外键用于关联其他表的主键，主外键关系是两个表之间的关系。

foreign key(字段名) reference 主表(列);

-- 创建班级表
create table classes(
	id bigint primary key auto_increment,
    name varchar(255)
);
--插入两条数据
insert into classes(name) values('class_11');
insert into classes(name) value('class_13');
--学生表
create table stu6(
	id bigint primary key auto_increment,
    name varchar(255),
    classesId bigint not null,
    foreign key(classesId) references classes(id)
);

此时在学生表中插入的数据关联了班级表中的班级，如果需要修改班级名称只需要到班级表中修改即可，对学生表没有影响。

如果往学生表写入一个classesId在班级表中不存在的数据，就会违反外键约束。

注意：当需要删除主表的记录时，必须要先删除完所有与这条数据相关联的子表数据，才能成功删除主表的记录。

表的设计

表的设计和设计类的思路是一样的，就是一个抽象的过程，根据需求文档或现实世界中的类(实体)转换成一张张表。 表对应到Java中的类，表中的字段对应到Java类中的属性。设计数据库时有一些固定的关系模型。
① 一对一关系
1.用一张表把所有字段全部包含起来；
2.按业务把相关的字段拆分出两张表，再为两张表建立主外键关系。
② 一对多关系
对于多的一方可以和主表建立主外键关系。比如多个学生同属于同一个班级，在学生表中建立和班级表的关系。
③多对多关系
对于多对多关系，可以新建一张关系表来记录每条数据的关联关系。比如查看所有学生的课程成绩，可以通过建立一张得分表关联起来学生表和课程表，查出每个同学每门课程的成绩。

新增

插入查询结果

可以把一个查询结果直接插入到另一个表里。

--把后面select出来的数据插入到前面指定的表中
INSERT INTO table_name [(column [, column ...])] SELECT ... 

注意：写入的列名和顺序与查询中的列名和顺序必须保持一致，同时新写入的表要存在。

查询

聚合查询

聚合函数

函数	说明
COUNT()	统计个数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

注意：在使用聚合函数时，如果有null，相关数据不参与计算。

Count

--统计学生人数
select * from student1;

Sum

--统计数学成绩总分
select sum(math) from exam;

Avg

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

> 注意，数据库表中不存在的列，都是以临时表的形式呈现。

Max & Min

--返回英语最高分
SELECT MAX(english) FROM exam;
--返回语文最低分
SELECT MIN(chinese) FROM exam;

Group By子句

语法：

-- column1表示要分组的字段，sun(column2)表示分组之后的操作。
select column1, sum(column2), .. from table group by column1,column3;

示例：

--查看不同角色的平均工资
select role,avg(salary) from emp group by role;

Having

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要HAVING 。因为 SQL 的执行过程是FROM ---> WHERE ---> SELECT ---> GROUP BY, 所以当需要对分完组的结果操作时，不能用WHERE来操作。

--过滤掉平均工资小于3000的且不是游戏角色的人
select role,avg(salary) from emp where role != '游戏角色' group by role having avg(salary) < 3000;

总结：过滤结果有两种
1.where对表中的数据做整体过滤，跟在from表名的后面；
2.having子句跟在 group by 字段后面。

联合查询

联合查询是面试和笔试中常考的部分。实际开发中往往数据来自不同的表，所以需要多表联合查询。
联合查询是两个表做笛卡尔积，相当于做全排列，但不是所有数据都有效，只有当主外键相等时，数据才有效。

内连接

语法：

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
--推荐使用
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

示例：查询’许仙’同学的成绩
1.先确定表：student，score；
2.做笛卡尔积；

3.确定连接条件:stu.id = s.student_id

4.加入查询条件:stu.name = ‘许仙’

5.精简查询结果

外连接

外连接分为左外连接和右外连接。左连接以左表为基准，也就是说左边表的所有数据都会显示，右边中如果没有匹配到数据，以NULL填充当前行。右连接则正好相反。
语法：

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

示例：查询所有学生的考试成绩及个人信息，如果该同学没有成绩，也需要显示。

select * from student stu left join score s on stu.id = s.student_id;

进一步直接将缺考的学生查出来：

自连接

连接的两张表是同一张表。自连接的主要作用是把列转换为行，从而实现数据之间的运算。比如一张表中每条数据记录的是某个学生一门课的成绩，但要得到这个同学所有课程的总成绩，就可以使用自连接。

示例：显示所有“计算机原理”成绩比“Java”成绩高的成绩信息。
1.找出涉及的表：course，score。从课程表中找到对应的课程编号。

2.通过自连接，在分数表中找出对应的课程成绩，可以是s1.course_id = 3且s2.course_id = 1(或s1.course_id = 1且s2.course_id = 3)，也就是说两门课程的成绩要在同一行。

3.过滤，得到有效数据

4.最终加入查询条件

子查询

一个查询的条件依赖于另一个查询的结果。
单行子查询：只返回一个结果

--查询名为"不想毕业"同学的同班同学
select * from student where classes_id=(select classes_id from student where name='不想毕业') and name != '不想毕业';

多行子查询：返回一个结果集

--查询语文或英文的课程成绩信息
--使用[NOT]IN关键字
select * from score where course_id in (select id from course where name='语文' or name='英文');

合并查询

合并多个select的执行结果,可以使用集合操作符 union和union all,使用时前后查询的结果集中，字段需要一致。
1.UNION ALL
合并显示所有数据，不会去重（两行字段的数据都相同）。

2.UNION
把两个不同表中的数据合并到一个查询结果集中，如果有重复数据，会去重。

---

继续加油~