数据库的约束

约束类型

NOT NULL

指示某列不能存储null值,如果尝试在这里插入空值,就会直接报错…
(大家尽量在记事本上敲好了代码,直接粘上去,不建议直接在里面敲)

not null是可以给任意个列来进行设置的…

UNIQUE

保证某列的每行必须有唯一的值,如果尝试插入重复的值,就会报错…

DEFAULT

规定没有给列赋值的默认值

当然,默认值也可以自己设置

PRIMARY KEY(主键)

not null 和 unique 的结合,确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速的找到表中的一个特定的记录
主键约束: 相当于数据的唯一身份标识,类似于身份号码/手机号码(很重要)
对于一个表来说,只能有一个列被指定为主键

对于这个主键,有一种典型的用法,就是直接使用1,2,3,4这种整数递增的方式来进行表示,MySQL里面对于这种递增的主键,是有内置支持的,称为 自增主键

FOREIGN KEY (外键)

保证一个表中的数据匹配另一个表中的值的参照完整性(针对两张表进行了关联)

先构造一个班级表:

drop table if exists class;create table class(class_id int primary key auto_increment,name varchar(20));insert into class values(null,'高一(1班)');insert into class values(null,'高一(2班)');insert into class values(null,'高一(3班)');

接下来构造学生表:

drop table if exists student;create table student(student_id int primary key auto_increment,name varchar(20),class_id int,foreign key(class_id) references class(class_id));

insert into student values(null,'张三',1);

insert into student values(null,'李四',10);

同时,外键约束,同样也约束这父表,当父表中某个记录被子表依赖着时,尝试删除或修改,都会失败;

delete from class where class_id = 1;

表的设计

所谓"表的设计",“数据库的设计”,其实就是根据实际的问题场景,把表给创建出来,如何去设计? =>学会找到这个场景中涉及到的"实体",然后在分析实体之间的关系(实体可以视为需求中的一些 关键性的名词)

几种关系
一对一

在数据库中如何表示这种一对一的关系?
方法一:可以把这两个实体用一张表来表示…
方法二:可以用两张表来表示,其中一张包含另一个表的id,根据这个对应关系,就能随时找到某个人身份证,也可随时找到身份证对应谁.

一对多

一个学生应该处于一个班级中,一个班级可以包含多个学生.
两种典型方案:
方法一:在班级表中,新增一列,表示这个班级里的学生id都有啥

方法二:班级表不变,在学生表中,新增一列,class_id
像MySQL这种数据库只能采用方法二,因为MySQL没有采用数组这种存储方式,像 Redis 这样的数据库就可以

多对多

一个学生,可以选多个课程,一个课程也可包含多个学生(N个学生,可以选N 门课)
这个在数据库设计中,就一招,使用关联表,表示两个实体之间的关系…

新增

查询

聚合查询

聚合函数

常见的聚合函数:

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

count

先创建我们增删改查基础中的表:

DROP TABLE IF EXISTS exam_result; -- 这是如果存在这张表就先删除CREATE TABLE exam_result (id INT,name VARCHAR(20),chinese DECIMAL(3,1),math DECIMAL(3,1),english DECIMAL(3,1));

插入数据:

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

注意:NULL这样的值他是不会记录到count当中的

sum(求和)

把这一列的若干行相加;
统计数学总分

 select sum(amth) from exam_result;

注意:sum这个操作,只能针对数字进行运算,不能针对字符串来进行
当然,一个聚合函数里面的参数,也可以通过表达式的方式进行运算(聚合函数,也是表达式的一部分)

 select sum(chinese + english + math) from exam_result;

聚合函数还可以搭配where字句来使用

select sum(english) from exam_result where english > 70;

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;

group by(分组)

根据行的值,对数据进行分组,把值相同的行都归为一组…
准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水）

create table emp(id int primary key auto_increment,name varchar(20) not null,role varchar(20) not null,salary numeric(11,2));insert into emp(name, role, salary) values('马云','服务员', 1000.20),('马化腾','游戏陪玩', 2000.99),('孙悟空','游戏角色', 999.11),('猪无能','游戏角色', 333.5),('沙和尚','游戏角色', 700.33),('隔壁老王','董事长', 12000.66);

查询每个角色的最高工资、最低工资和平均工资

 select role,max(salary),min(salary),avg(salary) from emp group by role;

这就是先执行group by,把这里的查询结果进行分组

group by是可以使用where,只不过where是在分组之前执行,如果要对分组之后的结果进行条件筛选,就需要使用having…

select role,avg(salary) from emp where name != '马云' group by role;

这里就是先去掉马云,然后在分组(分组之前指定的条件,就要用where)

having

分组之后指定条件筛选:求每种角色,平均薪资,只保留平均薪资1 w以下…
这里就要用到having

select role,avg(salary) from emp group by role having avg(salary) < 10000;

联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：

如何在SQL中进行笛卡尔积?
最简单的做法就是直接select,from 后面跟上多个表名,表名之间用逗号分割;

这里先加入一组测试数据:

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

内连接

查询"许仙"同学的成绩
许仙选了很多的课,就需要在学生表中获取到学生姓名,在分数表中获取到分数信息,分析这样的问题,就要想清楚,要查询的数据都来自于哪些表里…这里就需要针对学生表和分数表进行笛卡尔积…

select * from student,score;

我们发现,里面的`东西太多了,有很多的无效数据,要筛选信息,我们先指定两个id匹配

select * from student,score where student.id = score.student_id;

这样就筛选去了大部分,我们在分析,进而只保留了许仙的

 select * from student,score where student.id = score.student_id and student.name = '许仙';

这里说的是只要许仙的成绩,再进一步筛选

select student.name,score.score from student,score where student.id = score.student_id and student.name = '许仙';

像这样一步一步的我们就能精确的筛选出来,所以我们尽量不要直接就一步写到位,可以一步一步来,让我们写起来更明朗一点

查询所有同学的总成绩,及同学的个人信息
下面我就不一一写了:

select student.name,sum(score.score) from student,score where student.id = score.student_id group by student.id;

查询所有同学的成绩,及同学的个人信息

select student.id,student.name,course.name,score.score from student,score,course where student.id = score.student_id and course.id = score.course_id;

还有一种写法:
基于join这样的关键字,也能实现多表查询
from 表1 join 表2 on 条件
三张表也可以进行

 select student.name,course.name,score.score from student join score on student.id = score.student_id join course on score.course_id = course.id;

外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
上面的写法,from 多个表 where 不能做到外连接,但join on可以…

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

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

select student.id,student.name,course.name,score.score from student left join score on student.id = score.student_id left join course on score.course_id = course.id;

自连接

就是自己把自己进行笛卡尔积,本质就是把行和行之间的比较条件,转换成列和列…
显示所有“计算机原理”成绩比“Java”成绩高的成绩信息

select s1.student_id from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

子查询

简称就叫套娃,把多个select 合并成一个
单行子查询：返回一行记录的子查询
查询与“不想毕业” 同学的同班同学

select name from student where classes_id = (select classes_id from student where name = '不想毕业');

多行子查询：返回多行记录的子查询
查询“语文”或“英文”课程的成绩信息

1:[NOT] IN关键字:

-- 使用inselect * from score where course_id in (select id from course where name = '语文' or name = '英文');-- 使用 not inselect * from score where course_id not in (select id from course where name != '语文' and name != '英文');

2:[NOT] EXISTS关键字：

-- 使用existsselect * from score where exists (select score.course_id from course where (name = '语文' or name = '英文') and course.id = score.course_id);-- 使用not existsselect * from score where not exists (select score.course_id from course where (name != '语文' and name != '英文') and course.id = score.course_id);

合并查询

就是把多个查询语句的结果合并到一起

union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行
查询id小于3，或者名字为“英文”的课程

select * from course where id < 3 union select * from course where name = '英文';

union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行
查询id小于3，或者名字为“Java”的课程

select * from course where id<3 union all select * from course where name='Java';

总结

数据库约束

约束类型	说明	示例
NULL约束	使用NOT NULL指定列不为空	name varchar(20) not null
UNIQUE唯一约束	指定列为唯一的、不重复的	name varchar(20) unique
DEFAULT默认值约束	指定列为空时的默认值	age int default 20
主键约束	NOT NULL 和 UNIQUE 的结合	id int primary key
外键约束	关联其他表的主键或唯一键	foreign key (字段名) references 主表(列)

表的关系

1.一对一：
2. 一对多：
3. 多对多：需要创建中间表来映射两张表的关系
新增

insert into table_name [(column [, column ...])] select ...

查询
1.聚合函数 : MAX、MIN、AVG、COUNT、SUM
2.分组查询：GROUP BY… HAVING …
3.内连接:

select ... from 表1，表2 where 条件-- inner可以缺省select ... from 表1 join 表2 on 条件 where 其他条件

4.外连接:

select ... from 表1 left/right join 表2 on 条件 where 其他条件

5 自连接：

select ... from 表1，表1 where 条件select ... from 表1 join 表1 on 条件

6.子查询:

-- 单行子查询select ... from 表1 where 字段1 = (select ... from ...);-- [NOT] INselect ... from 表1 where 字段1 in (select ... from ...);-- [NOT] EXISTSselect ... from 表1 where exists (select ... from ... where 条件);-- 临时表：form子句中的子查询select ... from 表1， (select ... from ...) as tmp where 条件

7.合并查询:

-- UNION：去除重复数据select ... from ... where 条件unionselect ... from ... where 条件-- UNION ALL：不去重select ... from ... where 条件union allselect ... from ... where 条件-- 使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致

SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >select > distinct > order by > limit

注意:以上内容后半部分实在不想写了,有很多直接搬运的案例,可能不是那么详细,但做题用的也不多,只是作为自己的复习资料…