文章目录
1.数据库约束
对于某一列的值能添加哪些内容做了一定限制,这种限制手段称为约束。
- NOT NULL:指示某列不能存储 NULL 值。
- UNIQUE:保证某列的每行必须有唯一的值。
- DEFAULT:规定没有给列赋值时的默认值。
- PRIMARY KEY:NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY:保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略CHECK子句。(MySQL 8.0.16版本后才支持check约束)
1.1非空约束 NOT NULL
若在某一属性定义时规定该属性not null,则不能将空值存放在该属性中。
create table test1(
id int,
name varchar(10) not null comment 'name属性不能存放空值'
);
只给name属性插入值:
若只给id属性插入值,约定了非空属性的name属性必须有默认值或显示插入:
Null的这一列表示是否允许字段为空:
如何在创建表之后添加浮空约束?
表约束属于表结构上的修改使用alter+ change
必须将当前表中要添加非空约束的属性所有为null的行先删除或者修改为非空
1.2唯一约束 UNIQUE
唯一约束指的是对应的字段是唯一的,不能重复。
当插入重复的id时,就会报错:
null值不受唯一键约束
查看唯一约束:
show keys from tb_name;
如何在创建表之后修改某个字段为唯一约束?
表约束属于表结构上的修改使用alter+ change
alter table tb_name add unique(属性名);
给name属性加上唯一约束
1.3默认值约束 DEFAULAT
规定了默认值的表,在插入时若没有指定该列,则使用默认值插入数据。
I.插入时没有显示指定的sex值,则读取sex默认值;
II.若显示对默认值插入null值,不会触发默认值。
1.4主键约束 PRIMARY KEY
1)主键索引
将学生学号设置为主键:
-
一张表只能有一个主键,primary = unique + not null
(若某个属性使用unique + not null ,默认会将第一个unique + not null 共同约束的属性属性作为主键)
-
主键约束的列值不能重复且不能为null
-
主键约束可以由多个列共同组成(联合主键)
如何在创建表之后修改某个字段为主键约束?
只有当表中没有主键时才能添加主键
alter table tb_name add primary key(作为主键的属性);
如何删除表中的主键?
alter table tb_name drop primary key;
2)复合主键
将id - name作为主键:
复合主键必须id - name完全相同才认为相同,部分属性不同就是不同的两条记录。
3)自增主键
因为主键不重复且不为空,一般作为主键的列都是int或者定长的char类型,因此我们可以将主键的增长交给数据库自动执行。
自增auto_increment,插入数据不给值时,使用最大值+1。
-
自增主键可以显示的插入null或者不写都会触发自增主键
-
关于自增主键删除后的自增情况
仍然以当前最大值+1
truncate之后的表会还原自增主键的值
-
显示的给自增主键插入一个值之后,下一次复发自增操作时,仍以当前最大值为基准+1
1.5外键约束 FOREIGN KEY
涉及多个表之间的关联约束,用于关联其他表的主键或唯一键:
foreign key (当前表中的某个属性) references 主表(列)
比如:当前有一个学生表,还有一个班级表,学生表中有一列属性为该学生的班级信息
drop table if exists class;
--MySQL中的关键字作为属性名称,加上``括起来
create table class(
id int primary key auto_increment,
name varchar(10),
`desc` varchar(100) comment '班级信息描述'
);
drop table if exists student;
create table student (
id int primary key auto_increment,
name varchar(30),
class_id int,
foreign key(class_id) references class(id)
);
学生表中的class id关联class表的id属性,学生表插入数据时,class id要能正确输入,必须在class表中id值时存在的
在class表中不存在id为1的值,因此插入失败。
在student从表中若有记录关联到了主表class的主键id,若要删除主表class的id行,必须确保所有表中关联的数据先删除
插入时先看主表,主表中有这个属性值才能在从表中插入;
删除时先看从表,从表中该属性关联的所有记录都删除后才能在主表中删除。
2.表的设计
数据库三范式:
-
一对一:一对一的两个属性一般都可以放在一个表中,例如学生和学号的关系都放在学生表中,姓名和身份证号的关系都放在公民信息表中;
-
一对多:例如一个同学只能在一个班级,一个班级可以包含多个同学,学生和班级就属于一(班级)对多(学生)的关系
班级(id,name)
学生(id,name,class_id)
一对多的关系都可以使用这个套路来创建两张表,使用一对多的关系来创建表;
-
多对多:例如学生和课程的关系,一个学生可以选择多个课程,一个课程也可以被多个学生选择
学生:
id name 1 小马 2 小林 3 小胡 课程:
id name 101 语文 102 数学 103 英语 多对多的关系——>创建一个学生-课程中间表来记录多对多的关系:stu_course(stu_id,course_id)
- 第一范式:确保每列的原子性(设计表时,每一列都不能再次分解)
- 第二范式:当前表中所有属性都和主键相关
- 第三范式:表中所有属性都和主键直接相关而不是间接相关
3.新增
根据查询结果插入:
insert into table_name (属性1,属性2,...) select 查询的属性...
案例:创建一张用户表,设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的学生数据复制进来,可以复制的字段为name、qq_mail
-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年龄',
email VARCHAR(20) comment '邮箱',
sex varchar(1) comment '性别',
mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;
4.查询
4.1聚合查询
4.1.1聚合函数
常见的聚合函数:
函数 | 说明 |
---|---|
COUNT | 返回查询到的数据的 数量 |
SUM | 返回查询到的数据的总和,不是数字没有意义 |
AVG | 返回查询到的数据的平均值,不是数字没有意义 |
MAX | 返回查询到的数据的最大值,不是数字没有意义 |
MIN | 返回查询到的数据的最小值,不是数字没有意义 |
聚合查询指的是把行之间数据进行聚合,和列无关。
案例:
-
count
-- 统计班级共有多少同学 SELECT COUNT(*) FROM student; SELECT COUNT(0) FROM student; -- 统计班级收集的 qq_mail 有多少个,qq_mail 为 NULL 的数据不会计入结果 SELECT COUNT(qq_mail) FROM student;
-
sum
-- 统计数学成绩总分 SELECT SUM(math) FROM exam_result; -- 不及格 < 60 的总分,没有结果,返回 NULL SELECT SUM(math) FROM exam_result WHERE math < 60;
-
avg
-- 统计平均总分 SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
-
max
-- 返回英语最高分 SELECT MAX(english) FROM exam_result;
-
min
-- 返回 > 70 分以上的数学最低分 SELECT MIN(math) FROM exam_result WHERE math > 70;
4.1.2 group by语句
一般聚合函数搭配group by语句分组查询使用
select中使用 group by 子句可以对指定列进行分组查询。需要满足:使用 group by进行分组查询时,select 指定的字段必须是“分组依据字段”,其他字段若想出现在select 中则必须包含在聚合函数中。
案例:
统计每个岗位的平均工资、最低工资和最高工资——>需要根据查询出来的结果按照role进行分组
select role,max(salary),min(salary),avg(salary) from emp group by role;
group by 可以使用别名
4.1.3 HAVING
分组之后进行条件查询必须使用having(和group by搭配使用,按照聚合后的条件过滤使用)
显示平均工资高于2000的角色和他的平均工资:
select role,avg(salary) from emp group by role having avg(salary) > 2000;
聚合之前的条件使用where
聚合之后的条件使用having
4.2 联合查询
笛卡尔积:实际上就是两个表之间的排列组合,多表查询得到的结果是两个表的组合,列数相加,行数相乘。然后根据两张表的关联关系,过滤掉不正确的数据,最后再对结果进行过滤。
4.2.1 内连接
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
就是多表的笛卡尔积根据关联关系严格筛选结果,内连接的结果两张表的数据都不为空。内连接一定都是多个表同时都存在关联数据的情况,不会出现假设表1存在数据,表2没有数据,此时这条记录关联后就不会显示。
4.2.2 外连接
外连接:两张表查询时,若某个表存在空数据,仍然显示出来。外连接分为左外连接和右外连接。
左外连接:左侧表完全显示
-- 左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
右外连接:右侧表完全显示
-- 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
4.2.3 自连接
一般 MySQL 筛选数据时都是不同行的列之间进行数据的比较,涉及到同一张表中行数据的筛选会用到自连接。
4.2.4 子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询。
-
单行子查询:内部的查询sql返回单条记录的查询
eg:查询与“小马”同学的同班同学:
- 查询出“小马”同学的班级id
- 根据查找出来的班级id去查询同班的其他同学
- 组装a和b两步
select * from student where classes_id = (select classes_id from student where name = '小马') and name != '小马';
-
多行子查询:内部嵌套的查询sql返回的多条记录的查询,内部的返回结果有多条的嵌套语句
eg:查询“语文”或“英文”的成绩信息:
-
in关键字
select score,course_id from score where course_id in (select id from course where name = '语文' or name = '英文');
-
exists关键字
select * from score sco where exists (select sco.id from course cou where (name='语文' or name='英文') and cou.id = sco.course_id);
多行子查询使用in或exists的区别:
- in执行过程中只有两次查询,先执行内部查询,根据内部查询的结果筛选外部条件,子查询结果会缓存到内存中,适用于子查询结果集比较小的情况,效率比较高;
- exists比较耗时,每次都是从外部查询中取出记录和内部查询匹配,内部子查询不会产生临时表,不耗费内存空间,适用于组查询结果集比较大,且内存放不下的情况。
-
-
在from子句中使用子查询:子查询语句出现在from子句中。这里要用到数据查询的技巧,把一个子查询当做一个临时表使用。