MySQL增删改查(万字详解基础+进阶)

程序猿小马

已于 2023-07-19 11:50:22 修改

阅读量4.5k

点赞数 7

分类专栏： MySQL 文章标签： mysql

于 2022-11-28 18:10:34 首次发布

本文链接：https://blog.csdn.net/qq_54469537/article/details/128007608

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

🐳今日良言:眼里有不朽的光芒心里有永恒的希望

🐳今日良言:眼里有不朽的光芒心里有永恒的希望

🐕一、MySQL增删改查(基础)

🐂1.初识CURD

首先,在使用SQL语句进行增删改查(CURD)之前,先来认识一下CURD

C:Create 增加

U:Update 修改

R:Retrieve 查询

D:Delete 删除

所谓CURD,其实就是对数据库的数据进行增删改查操作,

需要注意的是,在SQL语句中,查询数据(R) 使用select进行查询操作.增加数据(C),使用insert进行增加数据操作.

🐍2.使用CURD

接下来,详细介绍一下CURD的基础操作

1).C(create) 新增数据

例:创建一个学生表(有学号和姓名) ,然后新增两条记录

先选中在操作哪个数据库,然后在该数据库中进行操作.

创建学生表:

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

id (列名) 代表学号, int 表示id的类型是整型.

name(列名) 代表姓名 , varchar(20) 表示name的类型是字符串,说明这个列最多可以存储20个字符,但是不是说写了20,就固定分配这么多内存,会动态适应,但是不会超过20个字符.

新增记录:

基本语法: insert into 表名 values (值,值,值,……);

insert into 表名 values (值,值),(值,值),(值,值)……;

insert into 表名(列名,列名,……) values(值,值,……);

当插入成功后,会如上提示,说明一行受到影响.

在SQL中, ' 和 " 都表示字符串(SQL中没有字符类型,只有字符串类型).

values后面() 里面的内容,个数和类型都要与表的结构匹配

学生表有两列,那么插入数据也必须有两列,如果不是两列,会出现如下错误:

注:当插入数据时,如果出现如下报错:

此时说明你当前数据库的字符集是有问题的,正确的做法是将字符集设置成UTF8.

创建数据库时,如果手动指定了字符集,那么以手动指定的字符集为准,如果没有手动指定字符集,此时就会读取mysql的配置文件(my.ini),该配置文件中也有一个字符集

配置文件如果没有改动过,默认情况下,是拉丁文.

当插入中文数据报错时,可以删除之前的库,然后重新创建一个数据库,然后将字符集设置成UTF8即可.

指定字符集的基本语法:create database 数据库名 charset utf8;

上述操作一次性插入一行,接下来使用下面语句可以一次性插入多行

insert into 表名 values (值,值),(值,值),(值,值)……;

插入两条记录:

注意,每一行的记录之间使用逗号分隔开

insert 除了可以完整的插入一行数据外,还可以指定列插入,此时未被指定的列,则使用NULL进行填充.

insert into 表名(列名,列名,……) values(值,值,……);

插入id为3的记录,但是不插入名字

效果,此时数据库中有三条记录

如果想要指定多个列,则每个列名直接使用逗号分隔开即可.

2).R(Retrieve) 查询数据

查询操作是CURD中使用最频繁的语句,相关操作也比较复杂,接下来,先来讲讲简单的查询(单表查询).复杂查询放在后面的进阶内容中.

a.全列查询

(查询表里的所有列)

基本语法:select * from 表名;

这里的'*'叫做通配符,代表了所有的列.

例:查询学生表中的数据

需要注意的是,全列查询在数据量很大的时候,慎重使用.

b.指定列查询

基本语法: select 列名 from student;

例:查询学生表中的学生姓名

指定列相对于全列查询而言,精简很多

c.带表达式的查询

基本语法: select 表达式 from 表名;

在举例之前,我们重新创建一个学生表,包含的列有:学号,姓名,语文,数学,英语成绩

然后在该学生表中插入几条记录

接下来进行带表达式的查询

例:查询所有同学的数学成绩+10的结果

通过对比原来刚插入的数学成绩,会发现,这就是预期的结果

小技巧: 1.复制代码:选中想要复制的代码,然后点击鼠标右键即可复制成功,再次点击鼠标右键即可粘贴.

2.键盘上的方向键(上下)可以切换之前输入的SQL语句

3.ctrl + C 终止当前输入的SQL语句,当sql执行时间过长时,也可通过该操作结束.

注意: 查询操作所得到的表是临时表,这个临时表并不是写到硬盘中去,这个临时表的类型和原始的表也不是完全一致(会尽可能把数据给表示进去),并不会改变原来表的数据

经过之前的表达式查询,如果查询操作会改变表中的数据,那么每个同学的数学成绩都应该+10,但是,显而易见,并没有

d.带别名的查询

(该查询操作也就是给查询结果的列,起一个别名)

基本语法:select 列名/表达式 as 别名 from 表名

例:查询所有同学的总分并将总分命名为total

指定别名也就是起个小名.

注意:这里的 as 是可以省略的,可以用空格代替,但是这样的SQL语法很容易引起误会,不推荐这样写

e.去重查询

(查询的时候,针对列进行去重(把有重复的记录,给合并成一个))

基本语法:select distinct 列名 from student;

这里再插入一条记录

例:查询去重后的英语成绩

首先查询去重前的英语成绩

再来查询去重后的英语成绩

通过对比,可以看出,之前的查询结果有5行,经过去重后,只有4行

f.排序

(对查询结果进行排序)

基本语法:select 列名 from 表名 order by 列名/表达式/别名 asc/desc

例:对所有同学的语文成绩进行排序

不指定排序方式时,默认是升序排序 (asc) ,如果想要降序排序的话,在SQL语句的最后加上desc即可.

例:对所有同学的语文成绩进行降序排序

关于排序的注意事项:

1.如果SQL中没有显式的写order by,则认为查询结果的顺序是不可预期的.

2.如果要排序的列中,有NULL,则认为NULL是最小值(比0要小)

插入一条记录,所有成绩都为NULL

此时对语文成绩进行降序排序:

比较 0 和 NULL 的大小 (将赵云同学的语文成绩先修改为0(后续会讲修改操作))

显而易见,NULL是要比0还要小的

注:如果要是多个记录,排序的列值相同,此时先后顺序也是不确定的

3.排序也可以针对表达式/别名来进行

例:按照所有同学的总成绩进行降序排序

注:NULL与任何值进行运算都是NULL

先将猪八戒同学的语文和数学成绩都修改为88.5

然后再对所有同学总成绩进行降序排序

由此可以看出:NULL与任何值进行运算都是NULL

4.排序还可以指定多个列来进行排序

排序指定多个列的时候,先以第一个为准,如果第一个值相同,再比较第二个

上述查询结果中,赵云同学和关羽同学的数学成绩相同,此时开始比较这两位同学的语文成绩, 由于是升序排序,所以说,赵云同学要排在关羽同学的前面

g.条件查询

(针对查询结果,按照一定的条件进行筛选)

基本语法:select 列名/表达式 from 表名 where 条件

例:查询语文成绩大于90的学生

通过where 指定一个'条件',把查询到的每一行,都带入到条件中去,看条件是真是假,把条件为真的行保留(作为临时表的结果),条件为假的舍弃

为了描述where后面的条件,下面列出一些比较运算符和一些逻辑运算符

比较运算符:

<=> 是对NULL进行特殊处理了,如果使用 = 比较某个值和NULL的相等关系,结果仍然是NULL,NULL又会被当做是false

例:查询英语成绩小于80的同学(猪八戒同学的英语为NULL)

观察查询结果,并没有猪八戒同学,这是因为 NULL 与 60比较,得到的结果是NULL,NULL被当做false,条件为假,所以查询结果没有猪八戒同学.

逻辑运算符

针对上述比较运算符和逻辑运算符中一些比较常用的,进行一些练习:

1.查询英语成绩小于80的同学包括NULL值(使用or)

2.and 和 or的优先级

and 的优先级是高于or的,如果担心记错,可以添加()

3.查询语文成绩在80到90之间的同学

4.查询语文成绩为80.5 90.0 78.5 其中之一的同学

5.查询姓张的同学的成绩

注:张% 是以张为开头的 %张是以张为结尾的, %张% 只要是包含张的都可以

6.查询姓张的同学两个字的

_ 代表一个字

7.查询英语成绩为NULL的同学

is null 要求只能比较一个列是否为空

<=> 可以直接比较两个列,万一某一行,两列都是NULL,也能查询出来

select * 之所以危险,是因为不确定查询结果有多少,如果太多,就会把机器的硬盘/带宽给吃满.

保证查询操作'不危险'的关键在于控制一次查询操作查出来的结果的数量,就是靠where子句,通过条件来针对结果集合进行限制

查询操作中,引入了一个limit,通过limit来限制查询结果的数量

直接在查询语句的末尾加上limit指定N,这个N就表示这次查询最大结果的数量

MySQL通过limit实现分页查询

limit搭配offset 就可以指定从第几条开始进行筛选了(offset的值从0开始算起)

3).U(Update) 修改数据

基本语法:update 表名 set 列名= 值 where 条件;

例:将赵云同学的语文成绩修改为90.5

修改前

修改后

修改操作是在切实的修改服务器的硬盘数据了,修改完成后是'持久生效'

修改操作还可以使用'表达式'来进行修改

例:将所有同学的英语成绩-5;

修改前

修改后

如果没写where子句,就是匹配所有行,空值无法进行算术运算

update 还可以同时修改多个列,多个列之间用逗号隔开

例:修改猪八戒同学的语文和数学成绩为78.5;

修改前

修改后

4).D(Delete) 删除数据

基本语法:delete from 表名 where 条件;

例:删除猪八戒同学的所有信息;

删除前:

删除后:

删除操作也是在修改数据库服务器硬盘上的数据,也是持久有效的

delete from 表名;

不带where子句时默认是所有列,此时删除表中所有数据,但是表还存在,是空表

drop table 表名;

这种删除是将整个表连同里面的数据一起删除,表不存在.

🐇二、MySQL增删改查(进阶)

🐝1.数据库约束

1).约束类型:

2).NULL约束

创建表时,可以指定某列不能为空

例:重新创建学生表,id不能为空

此时,当我们向学生表中插入数据,如果id为null的话会报错

3).UNIQUE

唯一约束,指定某一列只能有唯一的值

例:重新创建学生表,id列为唯一的、不重复的

此时,当我们向表里插入两条id相同的记录时,会报错

约束是可以组合在一起来使用的

例:重新创建学生表,id列为唯一的、不重复的且不为空

此时查看一下当前学生表的结构

NULL这一列:描述了是否允许为NULL,当加上not null 之后,就不允许为NULL.

Key这一列: id 这一行是PRI pri = primary key 主键

4).DEFALUT

设置默认值

创建一个class 表(包括id 和 name),设置name默认值为无名氏

此时,往该表中插入数据(指定id列插入数据)

通过查询可以看出,name的默认值是无名氏

5).主键约束

主键约束 = not null + unique

主键在进行插入记录的时候,需要先查询,然后再进行真正的插入操作

正因为主键和unique都有要先查询的过程,所以mysql就会默认给 primary 和 unique这样的列,自动添加索引,来提高查询的速度

1.大部分的表,一般都会带一个主键,主键往往是一个整数表示的id.

2.在mysql 中,一个表中只能有一个主键,不能有多个

3.虽然主键不能有多个,但是mysql允许把多个列放到一起共同作为一个主键(联合主键)

4.主键另外一个非常常用的用法,就是使用mysql自带的'自增主键'作为主键的值

注意语法:在primary key 后面加auto_increment

此时查看一下学生表的结构:

Extra (额外描述) 这里就描述 id 是自增主键

此时,当再次往学生表中插入记录时,可以让id这一列为空

看到这里,肯定会有小伙伴有疑问,不是说主键不能为空吗?为什么这里却可以让id为空?

这是因为:id 这一列使用了自增主键,id为null这个操作,不是说将id设为空值,而是交给数据库使用自增主键(默认是从1开始)

自增主键并不会利用中间间隙的值,是依照之前最大的值进行累加的

显而易见,当插入的关羽的id为10时,下一次插入记录id是用自增主键时,是从11开始累加的

6)外键约束

foreign key 外键约束,针对两个表之间产生的约束,外键用于关联其他表的主键或唯一键

以学生表和班级表为例

创建班级班(id 为主键,name 班级名称) ,然后插入两条记录

然后创建学生表(id 学生学号, name 名字, class_id 所属班级)

先来解释一下

引入外键约束的目的就是为了避免出现非法数据.

此时,当往学生表中插入数据是,class_id就需要是class表id中的一个

因为class表中id没有3,所以插入刘备这条记录失败

班级表中的数据要对学生表产生约束力(类似于:父亲对孩子有约束力)

此处起到约束作用的班级表就叫做'父表'(parent), 被约束的这个学生表就叫做'子表'(child)

表面上看,父表对子表有约束,实际上,子表对父表也有约束

例:删除id为1的class中的记录

class id为1 被子表引用了,如果删除成功,子表的数据就有问题了.

只有当class 表 id没有被子表引用时,才可以删除

如果想顺利删除的话,可以先删除子表,然后再删除父表

注:如果想创建外建,必须要求父表对应的列,得有primary key 或者 unique 约束.

例:班级表没有主键,在学生表中创建外键约束(会失败)

🐑2.表的设计

设计表的基本思路:

a.先明确实体 b.确定实体之间的关系 c.根据上述关系,套入'公式'中即可

实体间的关系:

三大范式

1.一对一

一个人只能对应一个身份证,而一个身份证只能对应一个人

针对这种关系如何建表?

a.将人和身份证这两个实体放入同一个表中

b.人和实体在不同的表中,相互关联(外键放在哪个表中都可以)

2. 一对多

一个学生对应一个班级,一个班级对应多个学生

针对这种关系如何建表?

将外键放在1这个表中,比如学生表中的外键是班级表中的id

3.多对多

一个学生对应多门课程,一门课程对应多个学生

针对这种关系如何建表?

此时就需要分割出一个中间表,比如成绩表

此时在这个成绩表(中间表)中,有两个外键,一个是学生id,一个是课程id

🐭3.查询

3.1聚合查询

1).聚合函数:

上述聚合函数,都是针对,某一列的所有行进行操作.

接下来,针对聚合函数来举例操作,先创建如下员工表

1.count(*)

这个操作,相当于先进行select * ,然后针对返回的结果,再进行count运算,求结果集合的行数.

count() 里面可以写任意的列名/表达式

注:如果有一行都为null,count(*) 会将null 行也计算进去.

如果count(列名),该列名为null,count不会将这一行计算进去

2.sum(列名/表达式)

例:计算所有薪水和

如果某一行的薪水为null,进行sum运算时,由于null和任何数据计算都为null,sum会避免这种情况,所以不会与null进行运算

3.avg(列名/表达式)

例:求薪水的平均值

4.max(列名/表达式) min(列名/表达式)

例:求最大薪水和最小薪水

2). GROUP BY 子句

使用group by 表中的行进行分组

不用group by 分组的时候,相当于就只有一组,把所有的行进行聚合.

引入group by 就可以针对不同的组,来分别进行聚合

例:根据岗位分组,然后计算平均薪资

把role这一列,值相同的行,给分成一组,然后计算平均值.

分组查询也可以指定条件,有两种情况

1.分组之前,指定条件先筛选,再分组使用where

例:统计每个岗位的平均薪资降序排序,但是刨除孙悟空的数据

2.分组之后,指定条件先分组,再筛选使用having

例:统计每个岗位的平均薪资降序排序,但是刨除平均薪资在10000以上的

3.2联合查询

联合查询也就是多表查询

1).

多表查询的执行过程实际上就是对两个表进行笛卡尔积

笛卡尔积就是把两个表放到一起计算,分别取出第一张表的每一行和第二张表的每一行,配对得到新的记录

有如下两张表

然后对这两张表进行笛卡尔积:

使用逗号连接两张表, 笛卡尔积得到一个更大的表

列数就是两个表列数之和,行数就是两个表行数之积

观察通过笛卡尔积得到的这个表,会发现,表中有些数据是错误的,比如张三同学,他不可能既在一班又在二班,所以说,需要去掉不合法数据

student.class_id 这里的 . 叫做成员访问运算符

用.来指明当前列是哪个表中的

把这个(student.class_id = class.id)用来筛选有效数据的条件,,称之为连接条件

总结,联合(多表)查询的步骤:

1.进行笛卡尔积

2.引入连接条件

3.根据需求,加入必要条件

4.去掉不必要的列

这里创建四张表,源代码如下:

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

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

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

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

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','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',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),
-- tellme
(80, 7, 2),(92, 7, 6);

创建好后的四张表中的数据如下:

班级表