MySQL基本操作2

一.数据库约束

1.not null：约束不能为空

加上not null,查看结构，id这一列权限就变成了不能为空。 

2.unique：保证某列的每一行都是唯一的值，不能重复。

一列可以加上多个约束，例如同时加上not null和unique

 观察 PRI为not null 和unique同时使用时的结果

PRI--->primary key 主键约束

3.主键primary key：not null和unique的结合

主键和unique一样都有查询的过程，MySQL会自动这样的列，自动添加索引来提高查询的速度。

确保某一列（或者某些列的结合）有唯一标识，有助于更容易和更快速的查询到表中的特定数据。

主键也是在插入数据时，先查询，再进行插入

注意：

1.）实际开发中，大部分的表都会有一个主键，主键往往是一个整数表示的id

2.）在MySQL中，一个表里面，只能有一个主键，不能有多个

3.）虽然主键不能有多个，但是MySQL允许把多个主键放到一起共同作为一个主键（联合主键）

4.）主键要保证不重复，手动指定比较麻烦，MySQL有“自增主键”自动生成

       自增主键： primary key auto_increment

加上自增主键后，插入id时，可以手动指定，也可以不手动指定（null)MySQL这时候自动生成。 

 自增主键不会重复利用中间的空隙，是依照之前的最大值来往后累加的。

 4.default：规定没有给列赋值时的默认值

 5.外键约束 foreign key

两个表之间的相互约束称为外键约束

 class100在class表中不存在，会被认为是非法的数据。为了避免这种情况，这个时候使用外键加强表之间的关联，减少出错的数据。

 这样就是约束了student里的classId必须是class里面的id 

正常插入范围内数据：

插入超出class的id范围的，则提示报错：

学生表中的数据要依赖班级表中的数据，班级表的数据约束学生表。

修改学生表的数据也不能超过范围

起到约束作用的班级表，叫做“父表”（parent），被约束的表，叫做“子表”（child）

父表中的id若是已经被子表引用过，则不能被删除：

没有被引用过，则可以删除掉：

如果想要成功删除两个表，则需要先删除子表，再删除父表。确保子表中没有引用父表的数据，再删除父表。

注意：

1.创建外键约束时，先创建父表，在创建子表。

2.想要创建外键，就要求父表对应的列，得有primary key或者unique约束。 

给子表插入数据，会先在父表中查询一下，看这个id是否存在。默认情况下需要遍历，所以需要索引 。

---

二.表的设计

表的设计/数据库的设计 就是明确一个程序里面，需要使用几个数据库，几个表，表里面都有那些列。

思路：

1.）明确实体（类比Java中的对象）

2.）明确实体间的关系（一对一，一对多，多对多）

3.）写成固定的关系“公式”，把数据带入即可

举例：教务管理系统

学生信息，老师信息，课程表，作业信息......

1.一对一关系：

学生和账号:一个学生只能有一个账号，一个账号只能对应于一个学生

创建关系的两种方法：

2.一对多关系

学生和班级：一个学生只能存在于一个班级中，一个班级可以包含多个学生。

student（id，name,classId);

class(classId,name);

3.多对多关系

学生和课程：一个学生可以选择多门课程，一门课程也可以被多个学生选择

把一个表的内容全部插入到另外一个表中的方法

插入语句：

---

 三.查询

1.聚合查询：针对行的运算，需要搭配聚合函数

  聚合函数：

对下表进行操作：

 1.）count操作

 求出的是集合的行数

count（）括号中还可以写列名/表达式

 注意：

1.）若是有一行全为null，计算总行数count（*）null也会加入计算；计算总名字数count（name)时，null这一行不计算加入。

2.）MySQL中聚合函数和后面的（）之间不能有空格。

2.）sum操作

注意：

1.）把一列的所有行相加，要求这个列是数字，不能是字符串，日期。

2.)null和任何数据相加结果都是null，sum会尽可能避免这种情况

3.）聚合函数所建立的表都是临时表，不对原始表数据做改变

3.）avg()

语文成绩的平均分，总分的平均分：

4.）max()和min()

查找语文成绩的最高分和最低分

2.分组查询: group by

 工资表

按组算平均工资

按照薪水排序：

分组查询，指定条件：

group by前后位置插入的不同条件

1.）分组之前，指定条件：先分组在筛选 ------>where

2.）分组之后，指定条件：先筛选在分组------->having

3.）分组之前和分组之后都指定条件

举例：

1.1分组之前指定条件：where

统计每个岗位的薪资，但是除开马云的工资

 1.2分组之后指定条件：having

查询每个岗位的平均工资，但是除去平均工资在十万以上的

 1.3分组之前和分组后都指定条件

除去程序员小王分组查询平均工资，再除去平均老板工资

3.联合查询：多表查询

（一）内连接

多表查询的基本过程：笛卡儿积

把第一张表中的每一行分别与第二个表的每一行匹配，得到的新表的列数是两个表的列数之和。 

例如：将以下两个表进行笛卡尔积

select *from 表1，表2；

 笛卡儿积得到的结果：新表的列数是两个表列数之和，新表的行数是两个表行数之积。

笛卡儿积得到的数据许多是无意义的，这时候需要根据条件筛选出有意义的数据。

再对表进行简化，合并相同的classId为一列：

只保留学生的名字和班级：

 连接条件：where之后的筛选有效数据的条件 

例子：多表查询

学生表，课程表，班级表，成绩表

 

 

学生表和班级表是一对多的关系

学生表和课程表是多对多的关系，多对多的关系用成绩表联系在一起

进行联合查询步骤：

1.）计算笛卡儿积

2.）引入连接条件

3.）根据需求加入其他条件

4.）去掉不必要的列，保留关注的点

 （1）查询许仙同学的成绩

分析：学生姓名在学生表里，学生成绩在成绩表里面

笛卡儿积：有许多无效数据

保留id与student_id相同的有效列

选定名字为许仙

去掉不必要的列 

 (2)查询每个同学的姓名和总分

同一个同学的行，合并到一个组里

 （3）查询所有同学的成绩，以及同学的个人信息

分析：个人信息来自学生表，课程名字来自课程表，分数来自于分数表

三张表进行笛卡儿积运算

寻找联系

 

对列精简

用from写

 用join....on也可以得到同样的结果，它更加细化表与表之间的联系。

join 表名1 前表有关系的表名2 on 关系表达式 join 有关系的表名3 on 关系表达式；

与from对比

（二）外连接

内连接和外连接在大多数情况下没有区别

当要连接的两个表里面的数据是一一对应的，这个时候没有区别

不是一一对应则有区别

一一对应关系的表

 修改表，当两个表不是一一对应时：

王五同学没有分数，70分不知道对应哪个同学

内连接，两种写法得到的结果相同：

外连接：左外连接，右外连接

join前加一个left/right

左外连接会把表左边的数据尽量列出来，右外连接会尽量把表右边的数据尽量列出来。 

（三）自连接

自己和自己进行笛卡尔积，SQL中无法针对行和行之间比较，可以使用自连接，把行转换成列。

例：显示所有”计算机原理“比”Java“成绩高的成绩信息

 变成行与行之间的比较

 以student_id作为连接条件，保证每行记录都是针对一个同学描述的

再挑出左侧是计算机原理，右侧是Java的行

最后挑出计算机原理大于Java的行

（四）子查询：实际上就是嵌套查询 

例：查出和”不写毕业“同学一个班的同学

先查出不想毕业同学的班级

再根据班级查出他的同班同学

 子查询就是把两部操作合并起来

括号里面的就是分部的第一步操作，要求嵌套的部分要返回一条记录

例：查询语文或者英语课程的成绩信息

1.查出语文和英语的课程id

2.根据课程id查出成绩

 子查询： 

实际生活中，不推荐使用子查询，直接按照多步查询就好。

（五）合并查询 

 把两个查询的结果集合并成一个：union

例：查询id<3，或者名字为‘英文’的课程

用or也可以得到相同结果

 用union和or的区别是：union可以查询来自不同的表的数据，or只能操作同一个表内的数据。

union和union all 的区别：union比union all多了一步去重操作，重复的部分只保留一份。