数据库约束
数据库的约束其实就是校验,主要是用在建表时。
约束类型
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
把两个不同表中的数据合并到一个查询结果集中,如果有重复数据,会去重。
继续加油~