MySQL增删改查【进阶篇】万字图文超详细讲解

妖狐艹你老母 2024-04-03 09:42 91阅读 0赞

#### MySQL进阶增删改查\[图文超详细讲解\] ####

*  一.创建数据库约束
 *   *  1.1约束类型
     *  1.2not null约束
     *  1.3unique :唯一约束(约束列)
     *  1.4default :默认值约束
     *  1.5primary key : 主键约束(常用)
     *  1.6foreign key : 外键约束(两表进行关联)
 *  二.表的顺序
 *   *  2.1 一对一
     *  2.2 一对多
     *  2.3 多对多
     *  2.4语句示例
 *  三.新增
 *   *  3.1新增进阶操作
 *  四.查询
 *   *  4.1聚合查询(单表查询)
     *   *  4.11聚合函数
         *  4.12group by 子句查询
         *  4.1.3having
     *  4.2联合查询(多表查询)
     *   *  4.2.1内链接
         *  4.2.2外连接
         *  4.2.3自连接（了解）
         *  4.2.4合并查询
 *  五.内容总结

## 一.创建数据库约束 ##

### 1.1约束类型 ###

**not null - 指示某列不能存储 null值。  
unique - 保证某列的每行必须有唯一的值。  
default - 规定没有给列赋值时的默认值。  
primary key - 是 (not null 和 unique )的结合确保某列（或两个列多个列的结合）有唯一标识有助于更容易更快速地找到表中的一个特定 的记录。  
foreign key - 保证一个表中的数据匹配另一个表中的值的参照完整性。  
check - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。**

### 1.2not null约束 ###

**创建表时，可以指定某列不为空：**  
**如果插入空值就会报错**  
**可以多设置not null来限制多列**

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

![在这里插入图片描述][8dbeba943e5049ddb66cf379e6157797.png]

### 1.3unique :唯一约束(约束列) ###

\*\*指定sn列为唯一的、不重复的：

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

**重复输入带有 unique列约束列的值会插入失败**  
![在这里插入图片描述][453c20082d614d11a96a835f5feadc6f.png]

### 1.4default :默认值约束 ###

**指定插入数据时，name列为空，默认值可以自己设置例如"匿名"：**

create table student (
    id int not null,
    sn int unique,
    name varchar(20) default 'unkown'
    );

![在这里插入图片描述][d66c224fb543450a98ea0eb7c51a7ae1.png]

\*\*

### 1.5primary key : 主键约束(常用) ###

**指定id列为主键:(有primary key 约束条件存在就可以省去null约束和唯一约束)**  
**但是主键只能对一个列进行约束**

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

![c37cadab654343a0ae9c31c4a77ec6fe.png][]

**对于整数类型的主键，常配搭自增长auto\_increment来使用,插入数据对应字段不给值时，使用最大  
值+1。**

create table student (
    id int  primary key auto_increment,
    sn int unique,
    name varchar(20) default 'unkown'
    );

![在这里插入图片描述][5c298e696cdd45df9263ffafaa7d6482.png]  
**我们可以在插入时输入id可是不输入,再插入后数据库的表会自己生成自增主键约束那个列来进行自增长+1**  
![在这里插入图片描述][9a99f802fdd446b89e38d0323fd8e447.png]  
**如果我们在插入中没有进行默认自增长插入,当我们想插入的某一值后,再进行默认自增主键插入后,会在我们刚插入的数据后进行自增长**  
![在这里插入图片描述][a64e7ea80f994dde8400feeadce1af6b.png]

### 1.6foreign key : 外键约束(两表进行关联) ###

**创建学生表student，一个学生对应一个班级，一个班级对应多个学生  
我们可以创建两个表,班级表来当做"父表" 学生表来当成"子表"**  
![在这里插入图片描述][12c5e320cd7b4d48a52727f25af0db30.png]

classes_id为外键，关联班级表id
    create table student (
    id int  primary key auto_increment,
    name varchar(20),
    classId int,
    foreign key (classesId) references class(classId)
    );

**这里我们就用classId来进行班级和学生进行了关联**  
![在这里插入图片描述][bb41f892966b4ec5987d6c440a7c3750.png]  
**这里我们在student子表进行插入班级Id为100时,当创建父表没有这个classId列中没有100就会报错,所以student子表插入失败**  
![在这里插入图片描述][1fa0116a302c43b3b8710080614b7999.png]  
**当我们进行将原classId进行修改后100可以发现,会发生修改错误,修改的classId在附表class中存在的classId中存在,我峨嵋你就会插入成功**  
![在这里插入图片描述][bf8d67ce9b3a4c8f9c296d59f6d14885.png]  
**当我们想要删除父表class中的classId时我们会发现,我们并不能删除或者修改,也就是当class 与 student中由classId这已关联就不可以在父表class进行删除或者修改有关联的classId了,同理我们可以删除class 与 student中由classId这无关联的数据**  
![在这里插入图片描述][76963b10fbb44046be7040df524a849e.png]

## 二.表的顺序 ##

### 2.1 一对一 ###

**一对一就是输入一个信息就可以找到这个人:如这身份证就是这个人的独特唯一身份信息**  
![在这里插入图片描述][29c094669c25499a89bea9d660557122.png]

### 2.2 一对多 ###

**就是一条信息对应一个群体,例如:一个班级了有很多学生,我们可以知道这个班级里学生的学号,如上述外键约束同理**  
![在这里插入图片描述][9bc58000fe8c4f0c9345acf2231cf980.png]

### 2.3 多对多 ###

**代表不同的对象相互之间都是由关系,但不是相互的唯一的信息,如: 大学中高数这一门课程,这节课可以由很多班级或者院校的同学去上课,学生也可以选择去上一些大学物理,大学英语这样课.**

### 2.4语句示例 ###

– 创建课程表:

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

创建学生-课程的中间表:

create table score (
    student_id int,
    course_id int,
    foreign key (student_id) references student(id),
    foreign key (course_id) references course(id)
    );

## 三.新增 ##

### 3.1新增进阶操作 ###

**创建两张表,当我们发现这两张表 列个数相同,数据类型相同,我们就可以执行 insert into B select \* from A;  
这样我们就可以完成插入,成功将A表的数据插入B表中**  
![在这里插入图片描述][71fcff53c3f84ce8b798f8104d4c212d.png]  
**我们删除B表重现创建与A表列相同数据类型想反的表B;输入insert into B select name,id from A;**  
注解:当我们进行两个表插入时,我们只需要列表个数相同,并且列表数据类型相等就可以进行插入,但是这样操作大多数没有意义,新增语句操作后是存储到硬盘中,所以我们要注意此操作,防止弄巧成拙.  
![在这里插入图片描述][df9042716b53481c9fd12705073e343a.png]

## 四.查询 ##

### 4.1聚合查询(单表查询) ###

#### 4.11聚合函数 ####

函数 ------------------------ 说明  
COUNT(expr) 返回查询到的数据的 数量  
SUM(expr) 返回查询到的数据的 总和，不是数字没有意义  
AVG(expr) 返回查询到的数据的 平均值，不是数字没有意义  
MAX(expr) 返回查询到的数据的 最大值，不是数字没有意义  
MIN(expr) 返回查询到的数据的 最小值，不是数字没有意义

select count(列名) from 表名;

**count 是我们在查询中查询输入列名的总行数**  
![在这里插入图片描述][4f03fa7e6a1143b5b8e312d00d71e54d.png]  
**我们发现chinese这一列也是8行,但是在我们查询中只会显示出7行,这就代表带有null的行数是不会计入count查询结果中**  
![在这里插入图片描述][acaa4e7dc88f43b09cca6dc3dd2fe6df.png]  
**当我们进行有null的行和无null行的两个列进行count查询会发现只会查询没有null行的数据所以id有10行但是count只查询9行**  
![在这里插入图片描述][2a03ebdf5e144bba9874b0c833c68cc3.png]

**sum操作:根据这(一列或者多列)进行整体相加(只能针对数字进行运算,不能针对字符串进行运算)**  
**我们也可以用where来进行条件操作**  
![在这里插入图片描述][dd81d1ddc006437ea7c59522c8745163.png]  
![在这里插入图片描述][8328b34853b74e51ad2dcde975ed62cf.png]  
![在这里插入图片描述][11bf10151e654c18af97820c5ba7ff1f.png]  
**avg(平均数)  
max(最大数)  
min(最小数)  
这些查询语句都和上述类似就简单略过了.**  
![在这里插入图片描述][ffde98fb001c4a8fbd90d55284b9a70a.png]

#### 4.12group by 子句查询 ####

**group by(列名) 根据行的值来进行分组,将行相同值分为一组**  
**我们先执行group by(列名)后在执行前面的操作**  
![在这里插入图片描述][88763c39e9724b20ae16b7343e7ef31d.png]  
我们可以执行where语句来进行条件查询  
![在这里插入图片描述][442d86acdda54f55ae4a9ae2b6123413.png]

#### 4.1.3having ####

**having查询语句时时在group by 分组后进行条件查询  
我们也可以用where 和 having来进行搭配条件查询**  
![在这里插入图片描述][f0051d096e3e4dfb934cdc8a1cf068ac.png]  
![在这里插入图片描述][f0a033db8fea4383b45b95a7e516d95c.png]

### 4.2联合查询(多表查询) ###

**实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积：**  
**也就是将两个表每一行和另一表的每一行进行联合查询**  
![在这里插入图片描述][57b73a825c6f40fd970c26d1c055c125.png]  
**如下**  
![在这里插入图片描述][c61e6ea2dd664ae5aa3f0a4c99c54100.png]

**在查询中我们会发现有我们不需要的数据，这样我们就可以来进行where来筛选查询**  
![在这里插入图片描述][680e83993bd04695b8c1f9d4ae115261.png]

#### 4.2.1内链接 ####

**我们先创建一些表**  
![在这里插入图片描述][5f02c95c950b49a6a6a6f233aa4738a4.png]  
**我们在多表查询的会发现有很多数据时多余的**  
![在这里插入图片描述][847a2c38a64a4199ba381e058043351b.png]

**例如我们可来查询许仙的成绩**  
![在这里插入图片描述][b34f692f212f4c3ba076910c83a3cf9d.png]  
**利用select 列名,列名from(表名)join(表名) on 条件  
join on 和where 应用类似（join on了解即可，一般我们使用两个表查询情况下更多会使用where）**  
![在这里插入图片描述][f9fb16f345114e7fbcb9ea5bd3e632af.png]  
**我们来进行多表查询**  
![在这里插入图片描述][2f09efd04687478c851253b813756250.png]  
**rom 表名 join 表名 on 条件 join 表名 on 条件**  
![在这里插入图片描述][062f90b86d8f47c0b15165ffc34e6c64.png]

#### 4.2.2外连接 ####

**我们发现join on 这个条件很麻烦，容易出错  
其实我们可以用join on来构造其他链接  
select 列名,列名from(表名) inner join(表名) on 条件  
select 列名,列名from(表名) left join(表名) on 条件  
select 列名,列名from(表名) right join(表名) on 条件**  
**我们来创建两个列与列没有相关联的表**

![在这里插入图片描述][8b8070fcd0a5483ead2f2bcd38d94cd8.png]  
![在这里插入图片描述][fdc42ede8d70485f859c176245830782.png]  
**当我们进行联合查询**  
![在这里插入图片描述][99e2df247f14402bac12dbe63dd584ee.png]  
**当我们进行where语句查询后会发现我们有一些需要的数据会丢失**  
![在这里插入图片描述][8c00fc28c9a043519a2f8a28a73d0409.png]  
**select 列名,列名from(表名) left join(表名) on 条件**  
**我们进行做查询之后jiukey 找到我们需要的数据了**  
![在这里插入图片描述][81b036dc91614382ac2c75f9e32a53aa.png]  
**select 列名,列名from(表名) right join(表名) on 条件**\*\*  
**与左链接同理**  
![在这里插入图片描述][572efa9327024c839c5d7ad788b4b301.png]  
**我们发现内连接和外连接是有表与表的结构不同**  
**如图1.内2.左外3.右外**  
![在这里插入图片描述][5cab5ccf4e1f4360b10f5875a42c1adf.png]

#### 4.2.3自连接（了解） ####

**自连接是将本身一张表进行行和行转化成列联合查询**  
![在这里插入图片描述][f95e6dee17634aa88169c54a5b322800.png]  
**同理可以根据where语句进行查询来减少多余数据得到我们想要得到想要的数据**  
![在这里插入图片描述][3fc2b557f3f844178166e44f223fb98c.png]

#### 4.2.4合并查询 ####

**我们将两个select查询用 union 来进行合并查询**  
**union all是不去重的，我们了解就可以了**  
![在这里插入图片描述][fe6673307ee14ecb94e701257b9b6130.png]  
**当我们在一张表进行查询可以用or**  
![在这里插入图片描述][a551036896b04d38a7a4eb7cf8f8c08b.png]

## 五.内容总结 ##

**约束类型**

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

**聚合查询**

*函数                                          说明
    COUNT(expr)                            返回查询到的数据的 数量
    SUM(expr)                       返回查询到的数据的 总和，不是数字没有意义
    AVG(expr)                      返回查询到的数据的 平均值，不是数字没有意义
    MAX(expr)                      返回查询到的数据的 最大值，不是数字没有意义
    MIN(expr)                      返回查询到的数据的 最小值，不是数字没有意义

![在这里插入图片描述][3da1d6fdf71c481bb679445bd6426575.png]

[8dbeba943e5049ddb66cf379e6157797.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/b984bfad4dc64848a9928032b9159bcc.png
[453c20082d614d11a96a835f5feadc6f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/c35c84d6c4ca4d6d860969d5d132cfde.png
[d66c224fb543450a98ea0eb7c51a7ae1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/a1b2ba39006b42ee89d81108da5e2014.png
[c37cadab654343a0ae9c31c4a77ec6fe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/ed57f5ab079346d19cb64ce752853c68.png
[5c298e696cdd45df9263ffafaa7d6482.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/b8386ec379514be987931be6a1c4d4c5.png
[9a99f802fdd446b89e38d0323fd8e447.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/2e912cbb0da5457d8e7c7c4c8bc097e6.png
[a64e7ea80f994dde8400feeadce1af6b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/8db5929a472f4053a4885fcc3e0e9d03.png
[12c5e320cd7b4d48a52727f25af0db30.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/ec60adf46708474094b90126ffceddac.png
[bb41f892966b4ec5987d6c440a7c3750.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/97c3f94511d445ae9ff31e0b7d2e5bd9.png
[1fa0116a302c43b3b8710080614b7999.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/066680669a8843f5a20cd7536398a4e1.png
[bf8d67ce9b3a4c8f9c296d59f6d14885.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/1de026fba9894f768dda84b8135175b8.png
[76963b10fbb44046be7040df524a849e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/bec5c164264c49f983bc5b33b35907cc.png
[29c094669c25499a89bea9d660557122.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/d70d35049451439b9b035a8941709972.png
[9bc58000fe8c4f0c9345acf2231cf980.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/a2e12f67b15a433390912f56f921c0f0.png
[71fcff53c3f84ce8b798f8104d4c212d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/4f710d4880584fb7a28215be68e4f38f.png
[df9042716b53481c9fd12705073e343a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/ce11269230f34683b353687506ef6a64.png
[4f03fa7e6a1143b5b8e312d00d71e54d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/e60f1ee07d514a6b941f98b4f3603b0c.png
[acaa4e7dc88f43b09cca6dc3dd2fe6df.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/ea74fa9b0bbd432bbe972d2eb17364c4.png
[2a03ebdf5e144bba9874b0c833c68cc3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/c13f3a9af7134770bcd739e1b6a4662e.png
[dd81d1ddc006437ea7c59522c8745163.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/328d86f9c21a41f6ad4260a55bbedb00.png
[8328b34853b74e51ad2dcde975ed62cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/8b64718e7a4f4c5fab1bae484bc68778.png
[11bf10151e654c18af97820c5ba7ff1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/4a1cff9b59514014bceb1c66e7144f22.png
[ffde98fb001c4a8fbd90d55284b9a70a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/1bb18565b168470aab75073bf2c11ff6.png
[88763c39e9724b20ae16b7343e7ef31d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/18188542a96a4eb0875e6779131465b6.png
[442d86acdda54f55ae4a9ae2b6123413.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/c3cf932db2d54d0ca25a0a5b8d455950.png
[f0051d096e3e4dfb934cdc8a1cf068ac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/289fbae3e6f5452281b4654630cc15d2.png
[f0a033db8fea4383b45b95a7e516d95c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/99afcb74ff8a4c34ba55874592c51a67.png
[57b73a825c6f40fd970c26d1c055c125.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/fa2ecdc098d74b9ea40a079cc3513ca8.png
[c61e6ea2dd664ae5aa3f0a4c99c54100.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/efc0ffe1d74e444e89004d99cfd3776c.png
[680e83993bd04695b8c1f9d4ae115261.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/4837d11ee89a43d2933d65f8c7302956.png
[5f02c95c950b49a6a6a6f233aa4738a4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/72e29d72592845508a778f2d5d77c7d8.png
[847a2c38a64a4199ba381e058043351b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/efc8a7eb822447efa15f993f9f823b1c.png
[b34f692f212f4c3ba076910c83a3cf9d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/174a0daa15f247f6806fc9aedea4fce4.png
[f9fb16f345114e7fbcb9ea5bd3e632af.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/052b0c717c8d492a95f7089b93aedcea.png
[2f09efd04687478c851253b813756250.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/f42ccdba52f44d00bfccfcfbbec75cd8.png
[062f90b86d8f47c0b15165ffc34e6c64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/7049a96bc3c646dfbcb9fc96177f50bb.png
[8b8070fcd0a5483ead2f2bcd38d94cd8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/41a2cda4d1d14ad9b12fa0b598631c3b.png
[fdc42ede8d70485f859c176245830782.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/1d442cad693d4c3ba07a01374c401313.png
[99e2df247f14402bac12dbe63dd584ee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/6b113ba9da6c4085b78136fb5e97f273.png
[8c00fc28c9a043519a2f8a28a73d0409.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/8a1e2feba0bd41929cd661ebc2badcaf.png
[81b036dc91614382ac2c75f9e32a53aa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/99bb3a757ff3467095fb2ff03ce4e8ae.png
[572efa9327024c839c5d7ad788b4b301.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/7c1ca8d348c941c79cafd11a1fe144d6.png
[5cab5ccf4e1f4360b10f5875a42c1adf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/aca79b1cdffe47058d1db230e9ce8a1b.png
[f95e6dee17634aa88169c54a5b322800.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/5a963ea9d95d4c4ebf9076db7b028593.png
[3fc2b557f3f844178166e44f223fb98c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/1c5318739da8434b96551a2b5dcc6e63.png
[fe6673307ee14ecb94e701257b9b6130.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/a2be3292178546518d73cceb158bf7cc.png
[a551036896b04d38a7a4eb7cf8f8c08b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/4aa4d4a6a5394243a864e15cfdcc1876.png
[3da1d6fdf71c481bb679445bd6426575.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/03/6174cf3f979f461e96af39d8f0458940.png