【MySQL】增删查改CURD（进阶版）

最新推荐文章于 2024-05-24 00:18:13 发布

'Dream是普通小孩耶

最新推荐文章于 2024-05-24 00:18:13 发布

阅读量957

点赞数

分类专栏： Note-数据库文章标签： mysql 数据库增删查改聚合函数联合查询

本文链接：https://blog.csdn.net/weixin_54150521/article/details/127471374

版权

Note-数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

前言

一定会自由的你说是吧

本文主要内容是：数据库的约束、新增、聚合查询（分组+聚合函数）、联合查询等。

一、数据库约束

数据库约束：对于数据库中的数据作出更详细的检查。
数据库约束类型：

NOT NULL - 指示某列不能存储 NULL 值。
UNIQUE - 保证某列的每行必须有唯一的值。
DEFAULT - 规定没有给列赋值时的默认值。
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
CHECK - 保证列中的值符合指定的条件。对于MySQL5数据库，对CHECK子句进行分析，但是忽略CHECK子句.

一）NULL约束:要求指定列非空

在创建表时，在该列之后进行指定，也就是增加 not null 语句
语法：create table 表名(列名类型 not null,列名类型…);

二）UNIQUE唯一约束

UNIQUE唯一约束：值唯一，每次在该列插入/修改数据都会针对当前的数据在表里进行查询，如果能够查到该值，则说明不是唯一的，插入/修改失败。
当使用unique之后，每次插入操作都会涉及到先查询、后插入。而查询很可能要进行遍历，效率较低，故引入了索引
【使用unique约束，数据库就会自动的给对应的列创建索引。】
语法：create table 表名(列名类型 unique,列名类型…);

三）DEFAULT默认值约束

DEFAULT修改/指定默认值
本来数据表的默认值是NULL，可以通过DEFAULT修改默认值为其他值。
修改方式：在创建表时对列进行默认值的设置
语法： ==create table 表名(列名类型 default 值,列名类型…); ==
默认值的地方看数据类型决定要不要使用引号
【默认值一般是在指定列插入，没有指定具体值的列就会设成数据库的默认值】

四）PRIMARY KEY主键

PRIMARY KEY 主键：
针对每一条记录作身份标识（唯一性！！+非空性！）
要求主键不能够重复，保证主键的唯一性，并且主键不能为空！
【primary key相当于 unique + not null；但是一个表里只有一个primary key，可以有多个unique】
Primary key的申明是在创建表时，钱并且一张表只能有一个primary key
语法：create table 表名(列名类型 primary key, …);
【使用主键需要给该表设置唯一值，但是如果该分配工作自己完成的话就会比较麻烦；故：MySQL提供了一个“自增主键”的机制，通过自增这种简单策略，当主键处手动设为NULL或不进行设置时数据库就会对主键值进行自动分配，避免为null -->== auto_increment== ：
语法：create table 表名(列名类型 primary key auto_increment, …);
desc 表名; 就会在该列Extra处看到 auto_increment 标识
在auto_increment 下也可以手动设置主键值，但是自增主键不能是0，否则就相当于给NULL自动生成主键值
【自增值是接着手动主键值+1的（其实也就是最大值+1）），但是如果是起始位置，自增值就是1开始】
Ps. ①当前这个自增主键，如果服务器就一个，则能够保证主键的唯一性；
②但是如果数据库分布式部署（数据太大，一台机器存不下使用多台机器），则需要分库分表，分别在不同的主机上搭建不同的MySQL服务器，每个服务器存储其中的一份（但是这些数据逻辑上来说还是同一张表，只不过是存储于不同的机器上而已），原有的自增主键就失效了，因为：MySQL的自增主键只能保证单机上不重复，不能保证分布式部署时不重复。
【分布式系统下，生成唯一ID的算法：（有很多种不同的实现方式）
生成公式=（时间戳+机房编号/主机编号 + 随机因子）=> 拼接后，计算哈希值】
（虽然理论上无法保证两次生成的随机因子/随机数是不同的，但是在实践中随机因子冲突的概率是非常低的，所以在工程上就忽略不计）

五）FOREIGN KEY外键

foreign key 把两张表关联起来，即：外键用于关联其他表的主键或唯一键。
语法： create table 表名(列名类型, …, 列名类型, foreign key(该表中将要与另一张表有关联的列名) references 参考表名(参考的列名));
当使用了外键之后，当前表中的该列一定要存在于参考表中的列，参考表就对当前表产生了约束。
（参考表，也就是约束别人的表称为父表；被约束的当前表称为子表）
在外键的约束下插入数据也会触发查询：往当前表中插入新的记录，就会自动在参考表中进行查询，看该父表的参考列中是否有该值，有则插入成功，无则插入失败
【故要求：子表中引用父表这一列务必是 primary key 或者是unique，也就是说必须唯一！】
子表对父表也是有约束的：如果在子表存在的情况下是不能直接删除父表的；删除记录也要看当前记录是否被子表引用，如果被引用就不能随便删除
子表限制父表修改或删除，父表限制子表修改或新增）
【例题：某店铺有商品表和订单表，那么当我在该店铺订购了一件商品之后该店铺就多了一条记录，一久之后该店铺想要下架该商品，那就需要在商品表中取消该商品的编号，然而将订单编号同样删除是不太现实的，那该怎么办呢？
——逻辑删除：并不是真的把数据清空，而是简单的标记成“无效”，故所谓的删除操作其实就是将该标记位标识为“无效”（标记位可以自己在创建表的时候添加一列）】
【注意：父类在子类之前创建！！！子类在父类之前删除】

六）CHECK约束

在MySQL5上使用是不会报错的
使用示例：

drop table if exists test_user;
create table test_user (
   id int,
   name varchar(20),
   sex varchar(1),
   check (sex ='男' or sex='女')
);

二、表的设计

题：谈谈你的xx项目里，数据库时如何设计的？
答：数据库里有几个表；每个表都是干啥的；每个表里又有哪些列；这些列又是干啥的。
数据库设计的主要思路：

① 根据需求找到“实体”：实体其实就是类似于面向对象程序设计中的“找对象”，找到需求中关键性的名词；
② 梳理清楚实体间的关系：一对一、一对多、多对多、没关系（一般来说，每个实体都会安排一个表，但是表里的内容需要受到实体间关系的影响）

一）一对一关系

一对一关系，在设计表时有多种方式：
①把两张表合并成一张表，即把所有信息放到一张表里；
②在一个表中加入另一个表的主键作为该表的外键

二）一对多关系

一对多关系设计表：把一的关系的表的主键加入到多的表中作为新的一列

三）多对多关系

多对多表结构通常需要使用一个“关联表”（创建一个新的表）把两个实体的表给联系起来

四）没关系

没有关系的表就进行正常设计，不需要特殊处理

（注意：E-R实体-关系图！）

三、新增

可以把一个表的查询结果插入到另一个表中：
语法：==insert into 表名1 select * from 表名2; ==
// 查询表2的所有记录插入到表1中
注：这里的插入不一定就是插入全部数据，也可能插入其中一部分，具体看查询的条件。
查询结果的列要和插入的表的列匹配！：列名无所谓，但是列的个数和类型要匹配！

四、查询

一）聚合查询

1. 聚合函数

查询的时候带表达式是把列和列放到一起进行计算；聚合查询是把行和行放到一起进行计算。
【聚合函数：SQL里面提供了一些函数，通过这些函数就可以进行 行和行的运算】
常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

2. 分组GROUP BY子句

group by 列名
// 把该列对应的记录中值相同的记录分成一组；然后就可以针对每一组分别聚合（也就是使用聚合函数）了。
【group by 列名在select*之后展示的相当于是每组的第一条记录；这样其实是不合理的，所以除了分组的这一列进行查询之外，其他的列都要搭配聚合函数来查询】
分组查询是可以指定条件的：分组之前使用条件筛选还是分组之后使用条件筛选。
Eg. ①查询每个岗位的平均薪资（除去“张三”这个记录）——分组之前条件筛选（where）
select role, avg(salary) from 表名 where name!=”张三” group by role;
【where写到group by前面，先条件再分组】
②查询平均薪资大于10000的岗位 ——分组之后条件筛选（having）
select role, avg(salary) from 表名 group by role having avg(salary)>10000;
【having写到group by后面，先分组再条件】
——SQL中语句的位置是固定的！！
针对同一个SQL中，是可以同时使用having 和 where的！
③求除了张三之外每个岗位的平均薪资，并保留平均薪资大于10000的岗位
select role, avg(salary) from 表名 where name!=”张三” group by role having avg(salary)>10000;
group by 之前的条件使用where，之后的条件使用 having。
【补充】byte是1字节，也就是8位，但是其是有符号数，符号位占一位，实际有效的只有7位，所以：范围【-128，127】

二）联合查询：多表查询

注意：关联查询可以对关联表使用别名。

1. 内连接

“笛卡尔积”：其实就是简单的排列组合，把两张表的记录在一起进行简单排列组合出所有可能的结果（也就是说：m*n种可能的结果）
“笛卡尔积”的列数是两个表的列数之和，“笛卡尔积”的行数是两个表的行数之积。
【进行联合查询的过程其实就是计算笛卡尔积的过程，当表比较大的时候笛卡尔积后就会更大，此时如果进行多表查询就是非常低效的，甚至会成为“危险操作”】
【笛卡尔积的结果中，有些数据是有效的，而有些数据是无效的，我们要剔除无效数据
——联合查询/多表查询=笛卡尔积+连接条件（用来“筛选笛卡尔积有效数据”的条件）+其他条件（根据其他需求）】
笛卡尔积：select * from 表名1,表名2 ;
// 输出的就是两表的列和行之积的一张新临时表
笛卡尔积+条件筛选： select * from 表名1,表名2 where 表名1.列名=表名2.列名;
同一个表中列名不可以相同，但是多个不同表中列名是可以相同的：此时通过【表名.列名】进行访问，但是如果想要访问的这两个表中列名不一样，那么表名可加可不加
笛卡尔积还可以使用【join on 这样的关键字】
语法：表1 join 表2 [on 条件];

// [ on 条件] 部分可以省略，省略就是全部笛卡尔积
// [on 条件]部分的条件其实跟where中类似，多个条件间也是用逻辑运算符进行连接

Eg. select 想要查看的列名,列名,… from 表名1 join表名2[ on 表名1.列名=表名2.列名];

有些特定场景下只能使用[join on]：
1）（注：where 条件中的并且要使用and，在条件中直接,不可行！！！）
2）（复杂SQL语句：①笛卡尔积 ②加上连接条件 ③加上其他条件 ④对结果的列按要求进行精简）
3）【求和】要注意：按行求和（聚合函数sum） or 按列求和（直接：列名+列名+…）
多个表的笛卡尔积：select * from 表名1,表名2,表名3,…表名n;
// 此时会发现在屏幕上显示很慢，主要是因为：打印比较慢。
12.例：有三张表：学生表student、课程表course、分数表score；查询每个同学每门课程的成绩？
①笛卡尔积：

select * from student,course,score;

②加上连接条件（分数表是关联学生表和课程表的，其分别与两个表都有关联关系）：

select * from student,course,score where student.id=score.student_id and course.id=score.course_id;

③加上其他条件：注意此时已经不用再使用其他条件了，已经基本符合条件；如果再加上group by ，则除了排序的列外其他涉及的列都要使用聚合函数。
④精简输出：

select student.name as student,course.name as course,score from student,course,score where student.id=score.student_id and course.id=score.course_id;

Ps.使用join on : 一个join on 只针对两个表，两个表联合+条件完成后相当于串成一个表，然后再继续 join 表 on 条件 ——更体现两两联合的特点

select student.name as student,course.name as course,score from student join course on student.id=score.student_id join score on course.id=score.course_id;

【注意书写顺序：select --> from --> join on --> where --> group by --> having】

2. 外连接

使用join可以做外连接也可以做内连接。
直接写join 或 inner join就是内连接，写作left join左外连接、 right join右外连接
如果两张表上的数据是一一对应的，那么外连接和内连接其实看不出来区别；但是当两张表不再一一对应，而且有一个表的数据要完全显示的时候，就需要使用外连接
全外连接在MySQL中不支持—> 全外连接：也就是两个表中的信息不管在对方表里有无都全部显示
在工作中使用联合查询不多，出现也多以内连接为主
面试：回答左外连接、右外连接和内连接的区别。
如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

3. 自连接（了解：开发中一般不使用）

自连接：也就是自己和自己连接（同一张表作连接）；其目的是：把行转换为列，然后可以进行行之间的比较。
两个表的自连接不能直接使用原来的表名进行连接，而是需要使用别名进行连接。
自连接的代价：运行的开销、可读性的成本

4. 子查询

子查询类似套娃/嵌套：本质上就是嵌入在其他sql语句中的select语句，即：基于一个结果的临时表再发起另一组查询
——其实就是：嵌套查询
但是注意子查询不要嵌套太复杂
【子查询结果是一条记录就可以使用=连接，但是子查询结果是多条记录则使用in连接】
除了in之外，exists也可以进行多行子查询（exists的写法执行效率和可读性都远低于in这个写法；）
但是在极端情况下exists可以代替in： in里的子查询结果是在内存中的，但是如果子查询结果集特别大，内存放不下就需要使用exists
关键字：[not] in 、 [not] exists
示例：

select * from score where course_id not in (select id from course where name!='语文' and name!='英文');

5.合并查询

使用 union 或者 union all 来完成把两个查询的结果集合合并到一起。
【union 在合并时如果有重复的数据就进行去重， union all 在合并时如果有重复的数据也不去重】
合并查询其实就相当于两个已经查询好的临时表进行合并
eg. select * fron 表名1 where 条件 union/union all select * fron 表名2 where 条件;
使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

THINK

【聚合查询和联合查询是重点！】
聚合函数（group by、having等的使用）
约束（自增等）进行简单了解