目录
😘😘创作不易,多多支持嘞,如果能帮到你的话, 就点个赞吧!!!
基础目录
详细请点击目录跳转~
-
数据库约束
-
约束类型概览
-
not all
-
unique
-
default
-
primary key
-
foreign key
-
check
-
表的关系(设计)
-
一对一
-
一对多
-
多对多
-
进阶增删改查
-
新增：
-
删除
-
修改
-
查询
数据库约束
数据库约束其实是一种强制让数据库内容变得更加完整的方式, 就比如说有的字段里面可以为null, 但是为了其完整性, 可以使用not null 约束他来让他变得更加完整.
约束类型概览
-
not null
-
unique
-
default
-
primary key
-
foreign key
NOT NULL
唯一约束, 对应字段的记录不能为null
创建表的时候,可以指定某个字段不为空, 例如:
drop table if exists student;
create table student (
id int not null,
sn int,
name varchar(20)
);
对于表student的id字段,我们将其设置为not null, 如果此时插入的记录里面,如果id为null,则会插入失败:
有时候, 一些实体所抽象出来的数据, 必须有一个识别码,也可以成为sn码, 就如同人的身份证一样,用来标识这些实物或者信息, 这些sn码是必不可少的, 所以这些用来标识身份的sn码就可以设置为not null约束(当然也需要避免重复), 来避免sn码为空, 数据也就更有意义和价值了.
UNIQUE
唯一约束, 对应字段的记录不能与其他记录重复.
创建表的时候, 指定sn码为unique约束
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20),
);
被unique修饰的字段, 那么这个字段里面的每一条数据是唯一的,例如:
例如往里面插入:
然后插入具有相同sn码的"lisi":
发生异常:
Duplicate entry '1' for key 'sn': 键“sn”的重复条目“1”
注意: 所有的null 都遵循unique约束原则:
例如,对sn字段被unique修饰后, 连续插入两个null将不会抛出异常.
DEFAULT
默认值约束, 被default修饰的字段, 如果输入为null, 就会使用默认值来代替, 但是代替的类型必须一致
例如, 将name 设置为default约束, 如果输入name 的值没有被指定的时候, 那么就用 "unknown"代替(若name被指定为null, null不会被默认值替代)
PRIMARY KEY
主键约束, 指定字段约束为主键,能唯一的 标识表中的每一行
一个表中只能有一个primary key 主键列,被primary key 修饰的列不能为空,并且主键列的所有值都是唯一的.相当于同时被 not null 和 unique修饰
对于整形类型的主键, 一般搭配auto_increment 来使用, 插入字段的记录不给值的时候,使用表中主键的最大值 +1 .
例如:
create table student(
id int not null primary key,
sn int unique,
name varchar(20) default 'unknown'
);
表中已经有一个记录,如果继续插入:
搭配auto_increment :
插入两个记录,其中被primary key auto_increment 标记的字段传入值为null ,自动从0开始记录:
如果这个时候, 被 primary key auto_increment标记的id 传入一个指定 值的时候,然后再传空值:
结果是主键最大值2,直接跳过被设置为指定的数值,然后后面的空值会继续跟着主键最大值自增.
FOREIGN KEY
外键约束
使用语法:
foreign key 字段 references 主表
添加class表,classId为主键:
create table class(classId int primary key auto_increment, className varchar(20));
添加student表,studentId为主键:
create table student(studentId int primary key auto_increment, name varchar(20), classId int, foreign key( classId) references class( classId));
设置student中的classId字段被foreign key约束, 其约束项为class 表中的classId字段, 也就是说:
student 表中的classId 中的每一个数据, 都必须在class表中的classId 字段的记录中存在.
此时,student表被class表约束, 我们称student为class的子表, 称class 为student 的父表
如果向student里面插入或者修改的记录对应的foreign key 外键约束的字段数据在对应父表中不存在的话, 那么就会插入失败.
相反,如果要删除class表中的数据, 如果对应约束项的记录已经在子表中存在, 那么删除就会失败
更直观的来看个例子:
创建两个表:
商品表(Id, name, price....)
订单表(orderId, userId, goodsId, time.....)
其中商品id被外键约束, 其约束的内容为订单表中的goodsId, 用MySQL的语言就是:
create table orderList (orderId int primary key auto_increment , userId int, goodsId int, foreign key(id) references goodsList(Id));
假如这个商品表需要有一个商品下架功能, 但是, 这里面的id又有外键约束, 如果要在商品表中删除一个商品, 但是他的id 在订单表中还在被使用, 那么此时就无法直接删除, 那么这个功能该如何实现??
解决办法:给商品表加一个列字段, 用来表示该商品是否被下架,通过update 来修改这个字段, 此时外界约束就不会收到影响, 订单可以继续实时查看.
表的设计
如何设计表?
-
了解实际信息体的"实体"
-
明确实体之间的关系
-
按照实体的关系带入到表的体系当中.
无关联
各表之间没有关系, 只需要根据实体的个性 自行设计即可
一对一
例如, 一个学生表中 有许多关于学生的字段, 这些字段可以是姓名, 年龄等, 同时还存在一个账号表, 这两张表有很直接的关联, 那就是一个账号只能供一个学生使用, 同时 一个学生也只能拥有一个账号.
这种一对一的, 就可以直接设计成一张表:
student -- account ( studentId, studentName, studentAccount);
此外,更推荐将其设计成两个表, 然后让他们相互关联, 可以使用studentId进行关联, 也可以使用account进行关联.
一对多
一个班级可以包含多个学生, 但是一个学生只能存在于一个班级, 这就形成了一对多或者多对一的情况
student (studentID, name, age ....)
class (classID, className, studentList(每个记录为一个学生数据 集合 ) )
student表包含学生的基本数据, class 包含班级的数据, 同时也包含着具有的学生列表, 但是学生列表有很多行学生的数据, 不可能在一行里面显示, 但是使用数组的形式存放这些学生的记录的方法在MySQL是不支持的. 但是有些数据库支持以数组的形式存放, 比如redis ,
此外还可以使用外键约束来设计这个一对多的表:
多对多
例如, 一个学生可以上多个课程, 一个课程也可以有多个学生:
studentList (studentID, name );
course ( courseID, courseName );
将他们两个关联起来:
student_courseList (studentID, courseID );
进阶增删改查
增
与前面的直接使用insert into 表名 values (value1, value2, value3, ....)了不同的是, 这里的新增是直接插入查询结果. ;例如:
这里创建两张学生表:
student (id int, name varchar(20))
student2 (id int, name varchar(20))
student表中插入三个数据:
insert into student values (1, "张三"),(2, "李四"),(3, "王八");
如果这个时候, 我想把student表中的数据拷贝到student2 当中去, 使用将values改成select查询语句:
insert into student2 select * from student;
注意 :这并不代表任何两个表之间都可以这样操作, 查询的结果得到的列数, 对应字段的数据类型必须一样( 列名可以不一样, 但是类型必须一致 )
查
聚合查询
在查询的过程中, 对表的行与行之间进行一定的运算, 这种查询需要依赖SQL提供的库函数. 常见的有统计总数, 计算平均值等操作, 如下表:
| 函数 | 说明 |
| COUNT([DISTINCT] exam) | 返回查询到的数据的数量 |
| SUM([DISTINCT] exam) | 返回查询到的数据的总和 |
| AVG([DISTINCT] exam) | 返回查询到的数据的平均值 |
| MAX([DISTINCT] exam) | 返回查询到的数据的最大值 |
| MIN([DISTINCT] exam) | 返回查询到的数据的最小值 |
COUNT
新建一张学生表 :
student (id , name)
create table student(id int, name varchar(20));
然后向里面插入四个数据如下:
然后使用count, 语句如下:
select count(*) from student;
select count(id) from student;
select count(name) from student;
这个操作可以理解为, 先执行select* 操作去查询所有数据, 然后去计算select* 查询结果有多少行.例如上面的select count(id) 和 select count(*) 操作, 但是如果数据里面遇到的记录为NULL, 那么这一行记录是不会被count计算进去的, 就例如select count(name) .
如果你不想将NULL指定为有效行数, 那么就可以直接使用count来指定列查询, 如果你认为NULL也为有效数据, 那么你可以使用count(*) 来进行查询.
SUM
sum求和只是针对数字列有效, 如果是字符串或者其他类型的列, 将无法计算
例如我们创建这样的一张表:
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30)
(8,'鲁班七号', null, null, null);
使用sum函数来对所有记录的chinese 字段进行求和:
select sum(chinese) from exam_result;
说明: 如果里面数据为null, sum会自动跳过结果为null的数据.
如果对name这种非数字类型进行sum操作:
select sum(name) from exam_result;
结果显示:
1 row in set, 8 warnings (0.00 sec)
使用show warnings 来查看警告内容:
show warnings;
Truncated incorrect DOUBLE value : 当前类型向double类型转换失败.
AVG
计算平均数据,计算出来的数据为double类型
还是这个数据表:
对其chinese 的成绩进行AVG计算平均分操作:
select avg(chinese) from exam_result;
也可以搭配表达式使用: 例如求总分的成绩 :
select avg( chinese + math + english) from exam_result;
也可以使用as来对其命名:
select avg( chinese + math + english) as totalAVG from exam_result;
下面max和min的例子同样使用如下表格:
MAX
查询表中指定列的最大值, 例如查询chinese的最大值:
select max(chinese) from exam_result;
MIN
同MAX, 这里不做过多介绍.
GROUP BY
通常搭配聚合函数,select 语句中使用group by 可以对指定列进行分组查询,
例如这里创建一个员工表,如下:
create table emp (id int, name varchar(20), role varchar(20), salary int);
插入七条记录:
如何求出每个岗位的平均工资?
这个时候就需要使用分组查询了, 也就是group by操作.
使用group by语句, 可以让指定的列里面, 相同的值分到同一个组当中:
要求每个岗位的平均工资, 就需要指定岗位的类型来进行分组
然后对其进行平均工资计算:
select role, avg(salary) from emp group by role;
-
分组前查询
使用where.
例如求每个岗位的平均值, 但是不包括"张三".
select role, avg(salary) from emp where name != "张三" group by role;
2.分组后查询
使用having关键字
例如, 求所有岗位的平均薪资, 但是不包括老板:
select role, avg(salary) from emp group by having role != "老板";
3.同时在分组前分组后筛选
使用where在分组前筛选, 同时使用having关键字在分组后筛选.
select role, avg(salary) from emp where name != "张三" group by role having role != "老板";
联合查询
也成为多表查询, 这种多表查询依赖于一个笛卡尔积的前提.
笛卡尔积
定义:设A,B为集合, 用A中元素为第一元素, B中的元素为第二元素, 构成有序数对, 所有这样的有序数对组成的集合称作A和B的笛卡尔积,记作AXB = {<x,y>| x
A ^ y B}.
例如 A = {a, b}, B = {0, 1, 2}.
AXB = {<a,0>,<a,1>,<a,2>,<b,0>,<b,1>,<,2>}.
有序数对: 由两个元素x和y按一定的顺序成的二元组成为一个有序对, 记作<x,y>.
存在两张表:
studentList (studentID, name, class)
classList (classID, className )
里面存在着如上图的数据
让两张表进行笛卡尔积运行算,形成一张新表如下:
resultList (studentID, name, classID, className);
新的表的数据如图: 班级表里面的数据依次对学生表里面的每一个记录进行组合的一个排列
这份结果表, 就是学生表和班级表的笛卡尔积结果.
笛卡尔积出来的表格有很多数据,但并不是所有的数据都是有效数据, 就比如说上表数据中的记录:
这些记录的左边的班级ID和右边的班级ID不相同, 是没有意义的数据.
当我们去掉这些数据之后, 笛卡尔积的结果表就是两个表合并的结果.
要去掉这些数据, 可以增加where条件语句, 来过滤掉这些没有意义的数据.
联合查询
下面创建和插入以下表格和数据:
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;
create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));
create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) ,
classes_id int);
create table course(id int primary key auto_increment, name varchar(20));
create table score(score decimal(3, 1), student_id int, course_id int);
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);四张表的记录显示如下:
classes
student
course
score
各表之间的关联如下:
student和classes >一对多
student和course > 多对多关系
score相当于一张关联表, 起着关联student 和 course 的作用, 从图中可以明显看出来
我们需要实现以下功能:
查询"许仙"同学的成绩和其他相关信息
由于许仙同学的成绩和相关信息,存在于两张中表,就需要联合查询了, 首先我们需要score和student两张表进行笛卡尔积运算, 然后过滤掉无效信息
1.求两张表的笛卡尔积:
select * from student, score;
2.过滤无效数据:
去掉id 和student_id不匹配的情况,使用where语句
select * from student, score where student.id = score.student_id;(此处通过表名.列名的形式来指定列, 因为如果两张表中有重名的列则会存在指定不明确的问题)
得到下表,一张学生id能匹配正确的表格.
在过滤掉无效信息后, 还需要指定为许仙同学的数据:
select * from student, score where student.id = score.student_id and name = "许仙";于是就得到了我们所需要的许仙的成绩和相关信息表了.
也可以过滤掉除了name和score外的其他信息:
3.联合合并聚合查询
查询所有同学的总成绩, 及其个人信息
首先求笛卡尔积, 然后过滤掉无效数据
select * from student, score where student.id = score.student_id;
这个里面已经有了学神搞得各科成绩,但是我们需要的是总成绩 :
所以我们需要根据学生的名字或者学号进行分组(group by), 然后再针对分组进行求和:
select name, sum(score.score) from student, score where student.id = score.student_id group by student.id;
JOIN ON
有以下语句
select * from student, score;此操作为获取student和score的笛卡尔积表, student和score中间用","逗号连接, 但是除了用逗号, 还可以使用关键字join, 也可以完成笛卡尔积表的操作:
select * from student join score;不同于使用逗号获取的笛卡尔积表, join的条件筛选需要使用on关键字,而不是where
select student.name, score.score from student join score on student.id = score.student_id and student.name = "许仙";
以上这两种方法效果是一样的
内外连接
我们上面所说的例子, 都是两张表的记录都能一一对应, 但是在实际操作当中, 肯定存在数据量不一样的两张表, 或者是记录不能一一对应的两张表. 这种情况进行笛卡尔积, 没有一一对应的数据和其他数据进行笛卡尔积计算, 就必定只能产生无效数据,或者没有意义的数据, 这个时候不仅浪费空间,而且浪费性能,所以就要指定一个表为主的显示模式.
例如:
内连接
如果两张表的记录不一一对应, 这个时候使用内连接来连接两个表,两张表里面都有相互一一对应的记录的记录进行笛卡尔积.如 上图:将会忽略 student中id = 4, 和score表中student_id = 3 的记录.
内连接
select * from 表1, 表2 where 条件1 and 条件2;语法如上
外连接
外连接分两种, 一种是左外连接, 一种是右外连接.顾名思义, 左外连接就是以左表为基础, 与表2进行笛卡尔积计算. 如果表1中的数据没有与表2中与之对应的, 那么这个数据任然后存在于生成的临时表, 对于找不到的字段记录, 一般使用NULL填写:
例如: 存在两张表
使用左连接来进行笛卡尔积:
select name, score from student left join score on student.id = score.student_id;
右连接同理:
select name, score from student right join score on student.id = score.student_id;
多表连接
使用join语法:
select * from 表1 join 表2 on 条件1 join 表3 on 条件2......;执行逻辑为: 表1 先和 表2 进行笛卡尔积计算然后根据条件1筛选有效数据, 随后让表1和表2 生成的笛卡尔积表和表3进行笛卡尔积, 然后一句条件2进行有效数据的筛选. 根据情况进行左外连接或者右外连接或者是内连接
自连接
自连接是一种特殊情况的特殊操作, 如何理解? 也就是一张表, 自己和自己进行笛卡尔积表计算. 自己和自己笛卡尔积有什么用处呢??
首先存在一张表:
将这张表进行自连接:
select * from student, student;如果直接这样写, 会出现warnings:
因为生成的临时表, 系统不知道对应的字段为那个表的,需要使用 as 来指定这两张表的别名加以区分, 同时过滤掉无效数据:
select * from student as student1, student as student2 where student1.id = student2.id;
此时 想要进行行与行之间的条件查询, 现在转到一个列来了
SQL中的条件查询, 都是指定列进行查询, 他没办法进行行与行之间的条件查询
子查询
多个查询语句合并成一个.
单行子查询
例如有student表, 如下:
想查询 许仙的同班同学, 该如何查询? 首先使用条件查询找到许仙所在的班级, 然后通过班级搜索在这个班级里面的同学:
select class_id from student where name = "许仙";
得知许仙同学的班级号为1后, 开始查看class_id 为1 的班级, 来搜寻他的同班同学:
select name, class_id from student where class_id = 1;
子查询该如何进行? 也就是将这个class_id = 1 的这个1 替换为上面第一个的查询语句:
select name, class_id from student where class_id = (select class_id from student where name = "许仙");
这种子查询虽然语言简便, 但是不方便理解, 当有多层次的子查询嵌套的时候, 非常影响SQL语句的可行性.
多行子查询
有以下两张表:
要求: 查询"语文" 和"英文"课程的成绩信息
-
首先查询英文和语文的课程id,
select id from course where name = "英文" or name = "语文";
2.根据课程id到student表中去查询成绩
select * from score where course_id in(4,6);
使用多行子查询, 合并为一句:
select * from score where course_id in (select id from course where name = "语文" or name = "英文");
具有相同的效果
合并查询
在实际操作当中, 为了合并多个select语句的执行结果,可以使用集合操作符union.
有如下表:
要求查询:课程id 小于3 或者课程名称为 英文的记录
-
union
select * from course where id < 3 union select * from course where name = "英文";
-
union all
union all 会去重
联合查询的一般步骤
-
分析清楚需求,所设计到的信息都在哪些表中
-
针对分析出来的这些表,来进行笛卡尔积
-
根据实际情况(关联字段), 筛选出有效数据
-
进一步加强条件查询, 查询更加精确的记录
-
针对需要对表进行简化
注意事项
在实际操作当中我们应该慎重使用
联合查询的基础是笛卡尔积,笛卡尔积是把两个表进行重组, 例如第一张表的记录量为20, 第二张表记录量为30, 那么这两张表格进行笛卡尔积计算, 那么新的笛卡尔积表的记录量就会达到20 x 30 = 600 条, 因为其中还包含了许多无效数据需要我们去主动过滤, 但是当数据量来到千万, 甚至几亿的级别的时候, 那么笛卡尔积的操作就会产生大量的数据, 这些数据可能是当前外存无法存储的.
数据量大的时候, 笛卡尔积的开销是非常大的, 会对机器的性能造成很大的影响.
7244
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
