《MySQL》增删查改(进阶)
文章目录
- 《MySQL》增删查改(进阶)
- 前言:
- 1. 数据库约束
- 1.1 约束的概念与作用
- 1.2 约束的类型
- 1.3 not null 约束
- 1.4 unique 约束
- 1.5 default 约束
- 1.6 primary key 约束
- 关于自增主键在数据库分布式部署的问题及解决方法
- 1.6 foreign key (外键)
- 如何解决电商表与订单表的下架问题
- 1.7 check 约束
- 2 表的设计
- 2.1 一对一
- 2.2 一对多
- 2.3 多对多
- 将查到的数据直接插入表
- 3. 聚合查询
- 3.1 聚合函数
- 3.2 分组查询
- 4. 联合查询
- 4.1 笛卡尔积的概念
- 4.2 多表查询
- 4.2.1 多表查询实例
- 4.2.2 链接方式:
- 4.2.3 子查询:
- 4.2.4 合并查询:
- 总结:
前言:
本章虽然是SQL进阶,但更多是对于查询方面的进阶学习.同时加入相关表的设计方面的知识
1. 数据库约束
1.1 约束的概念与作用
在没有约束的情况下,数据库对输入的数据限制十分的小,所以引入了约束的概念,使得数据库可以让程序员定义对输入数据的约束条件,数据库在已经被约束的情况下会校验数据,如果校验不通过则直接报错,使得保证数据的安全性.
约束的本质其实就是使得数据报错及时,更好地保证数据的安全性.
1.2 约束的类型
约束的类型共有6种:
not null : 指定某列不能存储null值
unique : 指定某列不能有重复的值,确保数据的唯一性
default : 规定没有给列赋值的数据一个默认值, 数据库中默认的默认值为null.
primary key : 主键,为not null与unique的结合,确保某列(或多个列的集合)有唯一标识,使得更容易快速地找到这个表中的一个特定的记录
foreign key : 外键,保证一个表中的数据与另一个表的数据能实现匹配.
check: 保证数据按照指定的条件进行筛选,但是check 语句在MySQL5系列并不支持.
1.3 not null 约束
一般对于没有进行null的表,我们可以查到:
-- 创建一个普通学生表:create table exam_result1 ( id int , name varchar(20), chinese decimal (3 , 1), math decimal (3 , 1), english decimal (3 , 1));-- 查看表结构:desc exam_result1;
初始情况下,一个表是允许数据为null的,我们可以插入值为null的数据.
但是在数据已经被not null 约束后,则不可以插入值为null的数据.
-- 创建一个普通学生表:create table exam_result1 ( id int not null, name varchar(20), chinese decimal (3 , 1), math decimal (3 , 1), english decimal (3 , 1));-- 查看表结构:desc exam_result1;
插入为null 值,则会报错:
1.4 unique 约束
在没有unique约束的表中,我们可以插入许多相同的数据.
-- 创建一个成绩结果表create table result ( id int, name varchar(20), chinese decimal (3 , 1), math decimal (3 , 1), english decimal (3 , 1));-- 插入3条数据相同的数据insert into result values(1,"张三",99,99,99),(1,"张三",99,99,99),(1,"张三",99,99,99);-- 查询数据:select * from result;-- 查看表结构:desc result;
-- 创建一个id被unique约束的成绩结果表create table result ( id int unique, name varchar(20), chinese decimal (3 , 1), math decimal (3 , 1), english decimal (3 , 1));-- 插入3条数据相同的数据insert into result values(1,"张三",99,99,99),(1,"张三",99,99,99),(1,"张三",99,99,99);-- 插入3条id不同的数据insert into result values(1,"张三",99,99,99),(2,"张三",99,99,99),(3,"张三",99,99,99)-- 查询数据:select * from result;-- 查看表结构:desc result;当插入id相同的数据时,会报错:
插入id不同,但是其他数据相同时.插入成功:
表结构:
结论: 被unique约束的列,必须保持数据的相异性,插入相同数据则会报错.
1.5 default 约束
default 比较简单,作用在于为没有赋值的列给予一个自定义的默认值.
对于一个普通表而言,数据默认的默认值为null
-- 创建一个修改default值的表create table result ( id int unique, name varchar(20) default "未命名", chinese decimal (3 , 1), math decimal (3 , 1), english decimal (3 , 1));表结构:
1.6 primary key 约束
primary key约束后 ,这个列则作为主键:
不能为空,相当于 not null
不能重复,相当于 unique
一个表只能有一个主键
-- 创建一个有主键的表create table result ( id int primary key, name varchar(20), chinese decimal (3 , 1), math decimal (3 , 1), english decimal (3 , 1));查看表结构: 
自增主键:在主键后面修饰 : auto_increment,功能如下
如果写指定内容,这是手动指定,如果写入null,则会按mysql的自增规则自动生成
自增规则为,保存目前所有数据的最大值,下一个自增则为最大值+1.,目的也是为了保证数据不出现重复性的问题.
当然自增主键的缺点也是较为明显的:当出现海量数据时,则会出现自增主键数字十分庞大的情况.
关于自增主键在数据库分布式部署的问题及解决方法
如果数据库是分布式部署的话:
则会出现问题: 数据库必须多个节点的数据结合在一起才是一个完整的数据库,mysql在设置自增主键时,只能确保自己的节点的数字是唯一的,而不能确保多个节点的数字都是唯一的.这就会导致数据库出现重复性数据.
解决方案一(代价较大)
在生成数据时,使多个节点直接通过网络相互沟通,在了解彼此情况之下,再来生成唯一id.
但这样的代价就十分明显了,浪费了大量的时间在于沟通,使得效率十分低下.
解决方案二
通过时间撮形成随机数,在拼接上主机数,再在同一时间撮情况下,拼接上一个随机值
即
唯一id = 时间撮(ms) + 主机号码 + 随机数 (不是相加,而是字符串拼接)
保证了不同时间进入数据库的唯一id不同
保证了同一时间进入数据库但存入不同主机的唯一id不同
保证了同一个时间进入数据库并存入相同主机的唯一id不同(随机值的作用就体现在这里)
当然,如果同一个时间进入数据库并存入相同主机的随机值还相同的话,确实理论上会产生相同的id.但是概率极低,工程一般通过设置十分巨大的随机数,已达到几乎不可能出现这种情况.
1.6 foreign key (外键)
在商品表与仓库表之间
先写完所有的列,在进行外键约束语句.
create table warehouse( wareid int unique; name varchar(20); );create table product( id int primary key auto_increment, name varchar(20), wareid int; foreign key (wareid) references warehouse(wareid););形成外键关联后, 仓库表则称为父表,商品表则称为子表
如果父表为空,则直接往子表插入信息则会报错.
在外键的约束下,子表插入数据时,都会触发父表的查询,父表中出现才会查询成功
父表对子表产生的约束为:不能随意插入与修改.(需要根据父表内容)
子表对父表产生的约束为:不能随意修改与删除.(避免影响子表数据)
如何解决电商表与订单表的下架问题
在商品表为父表,订单表为子表
商品表(goodsid,name,price)
订单表(orderid,name,goodsid)
如何下架商品表中goodsid数据为100的商品,但是不影响订单表的数据呢” />
注意count() 要紧挨着哦,不然会语法错误.
sum() 返回查询到的数据的总和
- 如果某行中需要计算的列有null,对于null 不会影响计算结果
avg() 返回查询到的数据的平均值
- avg不会计算进null的值,也只除以非null 的行数.确保平均值的正确
max() 返回查询到的数据的最大值
min() 返回查询到的数据的最小值
3.2 分组查询
使用group by 子句实现.
将表中的若干行,分为多组,通过某一列作为分组依据,分组依据这一列相同的值,则归为一组.
例如:学生成绩表.查询男女各自的最大值,最小值,平均值
select gender,max(score),min(score),avg(score) from studnet group by gender;
分组查询的条件筛选
在聚合查询之前进行筛选,针对筛选后的结果再进行聚合.使用where 子句
去除了”李四”同学后的学生成绩表.查询男女各自的最大值,最小值,平均值
select gender,max(score),min(score),avg(score) from studnet where name!="李四"group by gender;
在聚合之后,进行筛选,使用having 子句
查询男女之间平均分大于80性别
select gender,max(score),min(score),avg(score) from studnet group by gender having avg(score) > 80;
同时使用
查询去除了”李四”同学后的学生成绩表,查询男女各自的最大值,最小值,平均值且男女之间平均分大于80性别.
select gender,max(score),min(score),avg(score) from studnet where name!= "李四"group by gender having avg(score) > 80;
4. 联合查询
4.1 笛卡尔积的概念
对多份数据进行全排列
例如: 
笛卡尔积的列数 = 两个表的列数之和
笛卡尔积的行数 = 两个表的行数之积
因此在实际开发中,由于笛卡尔积导致的表数据会巨大所以实际开发中,多表查询操作一定要慎重.
4.2 多表查询
select * from 表1,表2,表3.......;select * from 表1 join 表2 join 表3 ....on.....创建多个表:
create table classes( id int, name varchar(20), descrime varchar(20));create table student ( id int primary key auto_increment, sn int unique, name varchar(20) default 'unkown', qq_mail varchar(20), classes_id int);create table course( id int, name varchar(20));create table score ( id int primary key auto_increment, score decimal(3, 1), student_id int, course_id int);insert into classes(name, descrime) values ('计算机系2021级1班', '学习了计算机原理,c语言'),('中文系2021级1班','学习了中国传统文学'),('自动化2021级1班','学习了机械自动化');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','李七',null,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),(80, 7, 2),(92, 7, 6);4.2.1 多表查询实例
查询”李一”同学的成绩:
先查询学生表与成绩表的笛卡尔积
select * from student,score;
十分巨大
再过滤掉无用重复数据
select * from student,score where student.id = score.student_id;
再查找许仙同学的成绩
select * from student,score where student.id = score.student_id and name = "李一";图片4.18
4.2.2 链接方式:
内链接:
只提取两表中互相对应的数据进行多表查询
select 字段 from 表1 别名1 join 表2 别名2 on 连接条件 and 其他条件;select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;
2. 左外链接:联合查询，左侧的表完全显示我们就说是左外连接```sqlselect 字段名 from 表名1 left join 表名2 on 连接条件;右外链接:
联合查询，右侧的表完全显示我们就说是右外连接
select 字段 from 表名1 right join 表名2 on 连接条件;自链接:
自己与自己链接,一般用于行比较
4.2.3 子查询:
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询
查询李四的同班同学: (单列查询)
select * from student where classes_id=(select classes_id from student where name='李四');查询语文和英语的课程成绩信息: (多列查询)
select * from score where course_id in (select id from course where name='语文' or name='英文');
4.2.4 合并查询:
为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION
和UNION ALL时，前后查询的结果集中，字段需要一致
select * from course where id<3 or name='英文';-- 两者相同select * from course where id<3 union select * from course where name='英文';使用union会去重,union all不会去重
总结:
内容相对复杂,需要重点关注数据库约束和聚合与联合查询.
对于子查询与合并查询在实际开发中较少使用,但仍然要会使用.
表的设计一般较少使用,在学校课程中会涉及ER图的使用,有兴趣可以了解一下ER图.