1. 数据库约束
约束,就是让数据库帮助程序员更好地检查数据是否准确!
1.1 约束类型
not null
-
指示某列不能存储
NULL 值。 (允许为null→选填项,不允许为null→必填项)
unique
- 保证某列的每行必须有唯一的值。 (表里存的数据不能有重复的值)
default
-
规定没有给列赋值时的默认值。
primary key
- not null
和
unique
的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。 (主键,每条记录的身份标识)
foreign key - 保证一个表中的数据匹配另一个表中的值的参照完整性。 (外键,多个表的关联关系,要求某个记录必须在另外一个表里存在)
check
-
保证列中的值符合指定的条件。对于
MySQL
数据库,对
check子句进行分析,但是忽略check子句。 (显示通过条件来描述字段的取值)
1.2 NULL约束
创建表时,可以指定某列不为空:
![](https://i-blog.csdnimg.cn/blog_migrate/455f15f6531e8e74a3bae840be3721ca.png)
1.3 UNIQUE:唯一约束
指定列是唯一的、不重复的:
![](https://i-blog.csdnimg.cn/blog_migrate/8d9b68d82e06b98e4e91f6d5def85b15.png)
1.4 DEFAULT:默认值约束
指定插入数据时,如果
name
列为空,那么就会填充默认值:
![](https://i-blog.csdnimg.cn/blog_migrate/779ff4ed0e9d492fd32cd59c1a522988.png)
1.5 PRIMARY KEY:主键约束
主键约束,就是not null + unique
![](https://i-blog.csdnimg.cn/blog_migrate/abeb97a91412f7b753d3ea73b88c3cd9.png)
因为 主键 和 unique 都是先查询再插入的过程,因此MySQL就会默认给primary key和unique这样的列自动添加索引,从而提高查询速度
在MySQL中,一个表里只能有一个主键,因此在实际开发中,主键往往是一个整数表示的id
![](https://i-blog.csdnimg.cn/blog_migrate/1224b5f51d66cdbd5c9cfb9077157326.png)
但是MySQL允许把多个列放到一起共同作为一个主键(联合主键)
主键有个很常用的方法:"
自增主键" → 自动生成不重复的主键值 (加上 auto_increment)
![](https://i-blog.csdnimg.cn/blog_migrate/9c67f31b78626eed8f35f880c179abb3.png)
插入id的时候可以手动指定,也可以MySQL自动生成,这里的null并不会生成控制,而是会交给数据库使用
自增主键。自动生成的数据是根据上一条记录的基础增加的
1.6 FOREIGN KEY:外键约束
针对两个表之间产生的约束
因为每次给子表插入数据时,都会查询夫表中对应的数值是否存在,默认情况下需要遍历整个表,因此需要使用索引(即想要创建外键,就要求父表对应的列得有 primary key 或者 unique 来约束,因为会自动添加索引)
![](https://i-blog.csdnimg.cn/blog_migrate/db7f333f518d25dd949831f67771f014.png)
此外键约束的含义:要求student里的classID要在class表的id列中存在
class就是父表,student就是子表
![](https://i-blog.csdnimg.cn/blog_migrate/12a5d79416440fac6306414db2f3cb20.png)
在class表中的id列存在的话就可以正常插入
否则不论是插入(insert)还是修改(update)都会报错
不仅仅是父表对子表有约束,子表也同样约束这父表
![](https://i-blog.csdnimg.cn/blog_migrate/67c37f500a9cd6fc5e7ace5138f517e1.png)
想要顺利的删除,得先删除子表再删除父表
2. 表的设计
表的设计/数据库的设计就是明确一个程序里,需要使用几个数据库,几个表,表里有哪些列
设计表/数据库 基本思路
以教务系统为例
2.1 先明确实体
和Java中的"对象"是类似的,需求中"关键的名词/概念"
比如教务系统的学生信息、老师信息、课程表...... 学生、老师、课程表就可以称为"实体"
2.2 再明确实体之间的关系
2.2.1一对一关系
一个学生只能有一个账号(一个学生不能有多个账号)
一个账号只能被一个同学使用(一个账号不能给多个同学共享)
针对这种关系可以按以下方式创建:
1.把学生和账号放同一个表里
student_account(id, name, username, password);
2.学生和账号在不同表里,相互关联
![](https://i-blog.csdnimg.cn/blog_migrate/66a3a91590c2b3a47a7a55c802ca18e8.png)
2.2.2一对多关系
一个学生只能存在一个班级中
一个班级可以有多个多个学生
针对这种关系可以按以下方式创建:
1.student(id, name, classId);
class(classId, name);
2.2.3一对多关系
一个学生可以选修多门课程
一个课程可以被多个学生选择
针对这种关系可以按以下方式创建:
![](https://i-blog.csdnimg.cn/blog_migrate/5ee2873c6424875d0502dd7b2dc5849c.png)
总结
1.找实体,给每个实体都安排个表(需求中的关键名词)
2.明确实体的关系(往固定造句格式中套)
3.根据这些关系使用固定的方法建表即可
3. 新增
插入查询结果:把查询的结果插入到另一个表中
要求 查询结果 的临时表的列数和列的类型,要和student2匹配
![](https://i-blog.csdnimg.cn/blog_migrate/45c680be6b3c02c1d12165b444bec4c4.png)
4. 查询
4.1 聚合查询
本质上是针对 行和行 之间进行运算(带表达式查询是 列和列)
4.1.1 聚合函数
常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
![](https://i-blog.csdnimg.cn/blog_migrate/7311c262904ccf29acb246de93bccbe7.png)
count
这个操作相当于先进行select *,然后针对这个返回结果再进行count运算
不一定非要写 *,还可以写任意的 列名/ 表达式
* 的计算会把NULL的行也计数进去
name 计算的时候会去掉
![](https://i-blog.csdnimg.cn/blog_migrate/8ac2bd67658911c9508a20c12f9f43ac.png)
sum
把这一列的所有行进行相加,要求都是数字(不能是字符串/日期)
sum会在运算的时候会对NULL进行特殊处理
![](https://i-blog.csdnimg.cn/blog_migrate/5726ec0b0c421e1ad5672c250eb27516.png)
这个表是临时表,不会受有效数字的约束
avg
求平均值
可以对某一列也可以针对表达式进行运算
![](https://i-blog.csdnimg.cn/blog_migrate/dcb84293e943747660be28848fd9bd55.png)
max,min
求最大最小值
![](https://i-blog.csdnimg.cn/blog_migrate/f2eddff19d35ad0ffe23ad6fdf49032b.png)
4.1.2 GROUP BY子句
不用group by分组的时候相当于只有一组,引入group by就可以针对不同的组进行聚合
准备测试表及数据:职员表,有
id
(主键)、
name
(姓名)、
role
(角色)、
salary
(薪水)
![](https://i-blog.csdnimg.cn/blog_migrate/fdf5e7e9d58bdf50297e9e080f172aa6.png)
这样求得的平均薪水其实是没啥价值的,把同种职位的薪水取平均值才是我们更希望看到的
![](https://i-blog.csdnimg.cn/blog_migrate/c11e3bc05d3cb942fa6017f641aec341.png)
把role这一列值相同的行分成一组,然后针对每个分组计算平均值
4.1.3 HAVING
指定条件有两组情况
1.分组之前,先筛选再分组 where
除去“老板”这个职位的数据
![](https://i-blog.csdnimg.cn/blog_migrate/b4b31fd8e42d93a2721ad989e03d1e06.png)
2.分组之后,先分组再筛选 having
平均薪水小于10000的数据
![](https://i-blog.csdnimg.cn/blog_migrate/431b0124d243942f17dfb949b7297e92.png)
也可以一个SQL中同时使用where和having
除去“周瑜”和“老板”再查询每个岗位的平均薪资
![](https://i-blog.csdnimg.cn/blog_migrate/32118d39558d9944cd8c190efa41aedd.png)
SQL语句执行的先后顺序表:
![](https://i-blog.csdnimg.cn/blog_migrate/468f137c4c608465436043acee51f4fd.png)
4.2 联合查询(多表查询)
多表查询的基本执行过程:笛卡尔积
![](https://i-blog.csdnimg.cn/blog_migrate/0dd931086f2390933d18f1d75c7a8dbe.png)
通过观察可以发现很多数据是无意义的,例如张三并不在Java102这个班级中
因此我们可以通过SQL代码来去除这些没意义的数据
通过表名 . 列名 → 把这个用来筛选有效数据的条件成为“连接条件”
![](https://i-blog.csdnimg.cn/blog_migrate/eb6229fceda9e27bdde1f138516b34aa.png)
4.2.1 内连接
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);
(
1
)查询
“
许仙
”
同学的成绩
如何具体进行联合查询?
1.先计算笛卡尔积
select * from student, score;
2.引入连接条件 select * from student, score where student.id = score.student_id;
3.再根据要求加入必要条件 select * from student, score where student.id = score.student_id and name = '许仙';
4.把不必要的列去掉 select student.name, score.score from student, score where student.id = score.student_id and name = '许仙';
![](https://i-blog.csdnimg.cn/blog_migrate/6f3ea76be366e8196f045632a44722a9.png)
(
2
)查询所有同学的总成绩,及同学的个人名字
1. select * from student, score;
2. select * from student, score where student.id = score.student_id;
3. select name,sum(score.score) from student, score where student.id = score.student_id group by student.name;
![](https://i-blog.csdnimg.cn/blog_migrate/8ad73cef7469f57a1ca28205bfb976de.png)
(
3
)查询所有同学的成绩,及同学的全部个人信息
1. select * from student, course,score;
2. select * from student, course,score where student.id = score.student_id and course.id = score.course_id;
3.select student.name, course.name, score.score from student, course,score where student.id = score.student_id and course.id = score.course_id;
![](https://i-blog.csdnimg.cn/blog_migrate/27ddc144c8414b7aac845b17aa256754.png)
4.2.2 外连接
如果要连接的两个表之间数据
不是一一对应的,外连接和内连接才有区别
外连接分为左外连接和右外连接。
左外连接:左侧的表完全显示
![](https://i-blog.csdnimg.cn/blog_migrate/2694b26a97ce24356645c4c694f73005.png)
右外连接:右侧的表完全显示
![](https://i-blog.csdnimg.cn/blog_migrate/e94cf4b62a58f4a82677333e68668939.png)
4.2.3 自连接(特殊情况下才用到)
自连接是指在同一张表连接自身进行查询。可以把行转成列,因为SQL无法对行和行之间进行条件比较,因此就可以使用自连接把行转成列
显示所有
“
计算机原理
”
成绩比
“Java”
成绩高的成绩信息
1. select * from score as s1, score as s2;
2. select * from score as s1, score as s2 where s1.student_id = s2.student_id;
3. select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1;
4. select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;
![](https://i-blog.csdnimg.cn/blog_migrate/5fe462fdee7a5b0c1fd76e786eb8fa2f.png)
4.2.4 子查询(嵌套查询)
把多个SQL组合成一个(不易读。效率也会低)
单行子查询:返回一条记录
查询与“不想毕业”同学一个班的同学
1.先查询“不想毕业”这个同学的班级id
2.再按照班级id来查询哪个同学和他一个班
子查询就是将1,2合并:select name from student where classes_id = (select classes_id from student where name = '不想毕业') and name != '不想毕业';
此时的 “=” 要求后面子查询只返回一条记录,否则就要多行子查询
![](https://i-blog.csdnimg.cn/blog_migrate/e0197e971572c2b25beeadc64775b825.png)
多行子查询:返回多行记录
查询
“
语文
”
或
“
英文
”
课程的成绩信息
1.先根据名字查询出课程id
2.根据课程id查询出课程分数
[NOT] IN:
select * from score where course_id in (select id from course where name = '语文' or name = '英文');
![](https://i-blog.csdnimg.cn/blog_migrate/6099ec1f0e3b6036ea65e320c6c55dd1.png)
[NOT] EXISTS:
这个可读性比较差而且执行效率也远低于in写法,只有当in查询的结果太大内存放不下才用exists,那还不如多步查询,不仅通俗易懂还好操作。
4.2.5 合并查询
把两个查询的结果集合并成一个(要求这两个集的列要相同,)
查询
id
小于
3
,或者名字为
“
英文”的课程:
union(可以去重)
![](https://i-blog.csdnimg.cn/blog_migrate/7b75c52e2cc824052dc8bcfe79191a75.png)
问题:为啥不用 or ??
因为 or 的查询只能来源于一个表,而 union 可以是来自不同的表,只要查询的结果的列相同即可
union all(不去重,全部显示)