MySQL的学习【进阶】

一、数据库约束

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  如果有重复的数据，不去重。