一、数据库约束
1、not null
非空约束->哪些字段不能为空
eg:
2、unique
值唯一。每次插入或修改时,都会针对当前数据在表里进行查找,看该值是否能找到!如果能查到说明不是唯一的,就插入/修改失败。
使用unique约束,数据库就会自动给对应的列,创建索引。
3、default
本来表的默认值是null,使用default修改默认值为其他值。
4、primary key
->主键;针对每一条记录,作为身份标识。要求主键保证唯一性;主键不能为空。
->一个表中,主键只能有一个。
->自增主键
有了自增主键后,可以把该列的值设为空,会自动为其填充值。
当前这个自增主键,只有一个数据库服务器,自增主键没有问题;可以保证主键的唯一性。
但是数据库分布式部署。->分库分表,某个表太大,拆分成多个,分别在不同的主机上,搭建不同的mysql服务器,每个服务器中存储一份。虽然这些数据逻辑上来说还是同一张表,只不过在不同的机器上。此时,原有的自增主键就失效。
因此,mysql的自增主键,只能保证单机上不重复;不能保证分布式部署时不重复。【分布式系统下,生成id的算法。->思路:生成公式=时间戳+机房编号/主机编号+随机因子拼接,计算哈希值。保证主键不重复。但是,其中的随机因子不能保证完全不一样,但在工程实践上我们忽略,认为他就是不重复的随机。】
5、foreign key
外键,把两张表关联起来。
关键要素:当前表的哪一列,另外一张表,另外一张表的哪一列。
eg:
子表该列的值必须来自于父表中的对应字段。 父表对子表有约束。
此处也要求,子表中引用父表的这一列,必须得是primary key或者unique,保证唯一性.
子表对父表也有约束。在子表存在的情况下,直接删除父表,就会出现问题。
要删除父表的记录,也要看该记录是否被子表引用,,如果引用就不能随便删除。
eg:【删除与修改都可能会导致依赖关系出现错误】
一类问题:比如商场,有商品表(goodsId,name,price......),订单表(orderId,time,goodsId);其中,订单表中goodsId字段与商品表的goodsId有关联关系。现在商品表有一商品下架了,在商品表中是不能删除这个goodsId的【因为外键】。->解决办法:在商品表中添加一个字段,online(0/1),0则表示下架,1表示在线。查询时也只需查询在线商品即可。 【逻辑删除的思想:逻辑删除并不是把数据清空了,而是标记为无效。】
二、表的设计【数据库项目】
eg:数据库中有哪些表,每个表都是干什么的,每个表中有哪些列,这些列都是干什么的?
主要思路:根据需求,找到实体;梳理清楚实体之间的关系【一对一,一对多,多对多,没关系】
1、关系
一对一关系:
->把所有信息都放到一个表中
->通过外键相互关联
一对多关系:【采用外键】
一个班级包含多个学生,一个学生属于一个班级
-> student(id,name,gender......)
class(classId,name,studentList)
eg:【这种方法不太行,mysql没有数组类型】
-> student(id,name,gender,classId【外键】)
class(classId,name)
多对多关系:【通常需要使用“关联表”】
一个学生可以选多门课程;一门课程,可以有多个学生。
->student(studentId,name,gender)
course(courseId,name)
关联表:student_course(studentId,courseId)
三、新增
把查询的结果插入到另一张表中。
这里的插入,可以通过条件筛选,插入部分数据。
【但是要保证查询的列与及将插入的列要匹配(列的个数和类型)】
四、查询
之前学习,查询时有表达式的情况,把列和列放到一起计算。【eg:查询总成绩则把语文、数学、英语都相加】
聚合查询,是把行和行放到一起进行计算。
1、聚合函数:
sql中提供的这些函数,进行的是行和行的运算。【针对数值进行操作】
count ->计算行数
sum ->针对这一列的若干行进行求和
avg ->求这些行的平均值
max ->求这些行的最大值
min ->求这些行的最小值
eg:
1、计算行数
如果这一列有NULL,会跳过
2、分组查询/聚合
group by 列名
根据查询结果进行分组,把值相同的记录,分成一组。
(1)
(2)分组查询,还可以指定条件。
->分组之前,使用条件筛选 =》使用where条件;
->分组之后,使用条件筛选 =》使用having。
eg:查询每个岗位的平均薪资(除去 小宫 这个记录)
eg:查询平均薪资 >10000的岗位
eg:查询除了 小宫 之外,每个岗位的平均薪资,并且保留平均薪资>10000的岗位。
在进行分组查询的时候,只有用来分组的这一列可以进行查询,其他的列,则必须要搭配聚合函数来查询。
3、联合查询/多表查询
(1)联合查询
多表查询是对多张表的数据取笛卡尔积;
笛卡尔积:两个表记录,排列组合的结果。
笛卡尔积的列数,就是两张表的列数之和;
笛卡尔积的行数,就是两张表的行数之积。
eg:
由上可见,笛卡尔积的行数是一个相乘的关系;进行联表查询的过程,就是在计算笛卡尔积的过程。当表比较大时,进行多表查询,就会非常低效。
在笛卡尔积表中,有一些是无效的。
比如:
因此,可以把这些排列组合中的记录,合理的保存,不合理的删除掉。
因此,用来筛选 笛卡尔积有效数据的条件 =>连接条件。
联合查询/多表查询 = 笛卡尔积 + 连接条件 + 其他条件。
eg1:
计算出笛卡尔积:
加上连接条件:
加上其它条件【调整列名】:
eg2:使用关键字join on实现
相关练习:
准备数据:
->查询“许仙”同学的 成绩
方法1
计算笛卡尔积=》给笛卡尔积添加 连接条件=》其他条件=》对查询的列进行精简
a.
b.
c.
d.
方法2 :join on
->查询所有同学的总成绩,及同学的个人信息
->查询所有同学的成绩,及同学的个人信息,【带课程名的】
三张表的联表查询=》学生表,分数表,课程表
方法1:
a.
b.连接条件
c.精简列
方法2:join on
分成两步了,先student表与score联合,得到的结果在与score联合。
以上:通过 from 表1,表2...进行的,==》属于内连接
(2)内连接和外连接
from 表1,表2...进行的,==》属于内连接
from 表1 join 表2 on 条件==》可以作为内连接,也可以作为外连接:
join / inner join ==》属于 内连接
left join: 左外连接
right join: 右外连接
第二张表中情况,内外连接明显。
左外连接,以左侧表为主。右边没有的填为null;
右外连接,以右侧表为主。左侧没有的填为null。
全外连接 -> 把左外连接的结果与右外连接的结果并起来。在mysql中不支持。
3
(3)自连接
自己与自己 作 笛卡尔积。
把列转为行。
一个例子:显示计算机原理成绩比java高的信息
行与行之间的比较。
解决:通过自连接,把行的关系转换成列的关系。
步骤1:
步骤2:
步骤3:计算机原理id为3,java课程的id为1=》以此进行筛选
进行筛选:
继续添加条件,课程1的成绩< 课程3的成绩
(4)子查询
把多个查询语句,组合成一查询语句~
用一个查询的结果的临时表,基于这个临时表再发起另一组查询。
子查询结果返回了一个值,才能让class_id = 它。才能进行嵌套。
-> 如果子查询的结果是多条记录,可以使用in来进行子查询。
(5)合并查询
使用 union 或者 union all把多个查询结果集合,合并到一起。
-> union 把多个查询的结果集,合并成一个;
如果有重复数据,就会去重。
->union all 如果有重复的数据,不去重。