约束+表的设计+进阶查询

MySQL的增删查改进阶版(主要是查)

数据库约束❓

在使用数据库时,对于里面能够存放的数据提出的一些要求和限制,程序员就可以借助约束来更好的校验.

常见约束

约束类型	说明
not null	指示这一列的数据都不可为null
unique	指示这一列的的数据,每一个都是唯一的
default	指示这一列,如果插入记录时没给数据的话,所设定的默认值
primary key	主键,相当于not null和unique的结合,下面会介绍使用
foreign key	外键,使两个表的某两列之间建立一个主从依赖关系
check	保证列的值符合指定的条件,对于MySQL数据库,对check子句进行分析,但忽略check子句

约束的建立:即在建表时对指定字段加约束即可.

1. not null

   create table student(id int not null,name varchar(20));
   

   对学生id加了not null的约束之后,查看表结构(desc student;)时就可以发现,id之一列的数据不可为null

2. unique 与not null用法类似,当然也是想给哪个字符加,就给哪个字段加,不赘述

   但是MySQL是如何实现插入的记录针对指点的字段都不能存在重复的呢?这里就要联想到二叉搜索树进行插入时那样的机制了!在二叉搜索树中,插入一个元素时,先要找到这个新节点所在的位置,在比较的过程中:如果某个已有的节点的值与我们新节点的值相等,那这个新节点是不可以插入这棵搜索树的!MySQL的表中插入记录时与搜索树的插入节点的机制非常的像,只不过MySQL中使用的是B+树来组织数据的,即索引,说到索引时,在重点介绍.

3. default 使用方法和not null一样,也是针对字段的名称和类型写好了之后,后面直接跟一个default …即可,如:

   create table student(id int,name varchar(20) default '匿名');
   

   表名没名字字段的记录,对应的名字字段将会被视作默认值,此处就是我们所设定的"匿名"

4. primary key主键📦

   我们可以将主键理解成每条记录的一个身份标识,就类似于人的身份证,一人一个,一一对应

   先使用:最常使用的就是自增主键,插入的记录中,设了自增主键的字段如果数据为null也没事,此处的数据就会被默认设置成递增的数,插入的第一条记录,就会被默认设置成1,第二条设置成2,依次类推,当然此处数据我们也是可以指定的,比如我插入一个5,那后续此处数据我又懒得插入时,mysql又会依据这个5进行递增式的自行分配.

   create table student(id int primary key auto_increment,name varchar(20));
   

5. foreign key 外键Ⓜ️

   之所以叫外,是因为表外的外,也就是说关联了两张表,举个例子,有三个学生,每个学生都有自己所属的班级,另外还有一张班级表,囊括了整个学校的所有班级,那三个学生的班级id应当存在于班级表当中,否则逻辑上也说不过去啊,除非有学生不是这个学校的,或者班级表统计不完整啥的,但这都不是最重要的;这样一来学生表和班级表相当于就有了一个依赖关系:学生表的每个学生的所属班级的id,依赖于班级表的班级id

   create table student(id int,name varchar(20),class_id int,foreign key(class_id) references classes(class_id));
   //注意references和写在前面的是依赖别人的字段,写在后面的是被依赖的人
   

   这么操作有什么用?

   1. 对于子表(依赖父表中某字段的表)来说,再新增记录时,如果新记录的学生id那一项,在班级表中是不存在的,那就没法插入了
   2. 对于父表(被依赖者)来说:一旦有子表依赖了我父表,我这个附表就是删不掉的!父表中被依赖的记录无法删除,整个父表想删除也删不掉.

   外键约束的工作原理🍼

   在子表中插入记录时,就会先根据对应的值在父表中先查询,能查的到,才会允许在子表中插入该条记录.所以说父表的倍依赖的这一个字段,必须建立索引,提高查询效率.所以,建表时:有foreign key修饰的字段,自动就有了索引,当然对索引来说,有primary key 和 unique修饰的字段也会自动有索引.

---

表的设计🤒

两部曲:找实体->分析实体间的关系

举例:

1. 一对一的实体关系

   一个账户对应一个学生,一个学生对应一个账户,这种关系证明建立表呢?

   法一:把两个实体用一张表表示

   法二:在学生表中加一列:账户id,在账户表中加一列:学生id

2. 一对多的关系

   一个学生只能属于一个班级,而一个班级却可以有很多的学生

   法一:在班级表中加一列,写入每个班级都有哪些学生,学生表不变

   法二:班级表不变,在学生表中新增一列,表名每个学生都属于哪个班级

   因为mysql中是没有数组类型的,所以法一作废,使用法二

3. 多对多的关系

   学生和课程之间的关系,一个学生可以选很多课程,一个课程也可以被很多学生选择

   只有一种方法:对二者建立一个关联表

   将学生id拿出来,和课程id拿出来,根据实际的选课情况两者对应好之后,新建成一张表,这样就可以表名一个学生学了哪些课程,每个课程又都被哪些学生选择了,一目了然

---

和查询结合在一起的新增🌊

create table A (id int,name varchar(20));
create table B (id int,name varchar(20));

insert into B select * from A;
//将A全列查询到的结果统统插入到B中!

本新增方法需要保证B的字段类型和顺序(从左到右),要和在A中查找出来的临时表的结构一一对应,否则就插入失败咯.

既然是select嘛,所以结合之前所学的where,order by,limit,去重…都是可以滴.这里只是那全列查询做演示,只要能保证获得的临时表的结构和被插入的表格结构一致,就可以插入啦!一次插入多少条记录由你自己控制.当然被插入的表格也是可以指定列的,指定方法:

insert into 表名 (指定的被插入的列名,...) select ..from 表名 [where][order by][limit]...;

---

聚合查询🗡

首先区别于表达式查询,表达式查询是针对选中的记录进行记录中若干字段进行一个表达式运算,而此处是针对某一个字段的若干行进行计算.

聚合函数🎲

函数	功能
count([distincct] expr)	返回查询到的记录的数量
sum([distinct] expr)	针对某字段进行一列的求和
avg([distinct] expr)	求平均值
max([distinct] expr)	求最大值
min([distinct] expr)	求最小值

演示:

1. count:

   select count(*) from student;
   //可查询全列查询到的记录的数量
   

   注意:只有一列时,如果这个列的某些数据时null,那这些数据都不参与计数

2. sum:

   select sum(chinese) from student;
   //针对chinese进行求和
   

   打印的话,也只会打印出select 的内容:表达头和值

   其次,mysql不支持非数字类型的求和

   当然聚合查询也可以搭配where,order,limit等进行使用

3. avg,max.min都与之类似,不赘述

---

聚合函数搭配group by使用⚡️

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

上述sql语句执行的顺序是:先按照role进行分组,然后才是对每一组的数据进行聚合计算,并且打印的话,只会打印每一组的第一条算出来的记录,也就是说如果只分组不作聚合计算,那打印的话只会打印组内的第一条记录,作了聚合计算,就会打印出聚合计算的结果.

所以说:sql的执行顺序并不一定就是从左往右依次执行

当然在分组之前可以先用where过滤掉一部分数据,再分组,再聚合,再打印.

搭配having进行使用:此时是针对临时表再过滤.

总的语法顺序是:where->group by->having->order by->limit

---

联合查询🏎

顾名思义:把多个表的记录往一起合并,一起进行查询,也称多表查询

笛卡尔积🚶

其实就是排列组合,别被名字唬住了

一张表的所有记录和另一张表的所有记录横向进行排列组合,两两记录并成一条记录,组合完毕,笛卡尔积计算完毕.

所以:

1. 笛卡尔积的列数是两张表的列数之和
2. 笛卡尔积的行数是两张表的行数之积

如何使用sql表示笛卡尔积

select * from A,B;

A,B表示A在前,B被组合到后面.

---

为什么要进行笛卡尔积:

因为组合了新的表的记录,所以在笛卡尔积中查询到的每条记录的字段都更"详尽"了.这也是做笛卡尔积的意义所在.

---

笛卡尔积结果庞大,无用记录很多:

需用通过连接条件进行过滤,即在上述sql后面加上过滤的条件,如:

select * from A,B where A.id=B.id;

此时不满足条件的将不会被选择,当然这也合情合理,有些记录组合出来天生就没有任何的意义.其次注意到,条件写法中,用到了"表名.字段名"的语法,这样是为了组合后的笛卡尔积有些字段的名字相同,此时就可以加以区分了.

多表查询的步骤:做笛卡尔积观察结果->使用连接条件过滤无用记录->筛选目的记录(也就是再增加一些条件过滤记录)->根据不同的需求添加新的条件->让结果的数量一点点的靠近预期.

---

多表查询的另一种方法:

使用关键字join:

select * A join B on 条件;
select * A join B join C on 条件..;
select * A join B on 条件1 join c on 条件 2...;

---

一条较复杂sql的翻译:

select name,sum(score) from student,score where student.id=score.id group by name having name!=''张三' order by sum(score) desc limit 3;

1. 连接条件where最先执行,过滤掉了很多无用信息
2. 再根据name进行分组,要知道,只要有分组操作,聚合函数将只对组内的指定列进行聚合计算
3. 然后组与组之间根据每一组的聚合计算结果进行降序排序
4. limit分页查询结果:只要前3组
5. having对临时表进行再过滤,即舍弃掉名字是张三的学生信息

总的含义就是:手上拿着学生表和分数表,借助多表查询的方式将排除张三之外的所有同学的总分前三名进行打印(每一组只打印第一条信息).

---

为什么有from 表1,表2,… 这样的方式进行多表查询,干嘛还要有join这样的用法?:

因为连接有内外连接之分,而from只能用于内连接,而join既可以用到内连接,又可以用到外连接.

内连接:

表与表之间没有交集的记录将不参与笛卡尔积,用图形表示的话,大致是这样一个意思:

外连接

1. 左外连接:因为内连接自动摒弃了无交集的记录,我想保留在笛卡尔积中,那就要使用外连接,当使用的是左外连接的时候,笛卡尔积将以左侧的表为基准,后续的表在非交集的记录上没有记录能通过连接条件进行组合时,就使用null进行填补.图形表示大致是:

   

2. 右外连接:以右侧的表为基准.

   

自连接💇‍♂

自己和自己进行笛卡尔积

为什么要这样做?:

原本行与行之间的数据的比较,转换成列与列之间的关系比较

注意:

通常使用别名用于字段区分:

select * from score as s1,score as s2 where s1.student_id=s2.student_id;

当然还可以继续增加条件进行筛选.