[MySQL]MySQL表中数据的增删查改(CRUD)

已于 2023-07-12 18:57:23 修改
阅读量1.9k 收藏 19
点赞数 17
分类专栏： MySQL 文章标签： mysql 数据库 linux centos 阿里云
于 2023-07-12 15:48:23 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/csdn_myhome/article/details/131683301
版权

[MySQL]MySQL表中数据的增删查改(CRUD)

文章目录


CRUD : Create(创建), Retrieve(读取)，Update(更新)，Delete（删除），也就是增删查改。

1. 新增数据

语法：

INSERT [INTO] table_name
	[(column [, column] ...)]
	VALUES (value_list) [, (value_list)] ...

value_list: value, [, value] ...
  • column为属性列。
  • values为数据。

1.1 单列插入

方式1：

省略属性列

insert [into] table_name values (data1, data2 …);

由于省略了属性列，因此需要包含每个属性列的对应数据。

image-20230710180232483

方式2：

不省略属性列

insert [into] table_name ([column1], [column2]…) values ([data1], [data2]);

插入时只需要将指定的属性列的对应数据包含即可。

image-20230710180852786

两种插入方式结果查询：

image-20230710181214704

1.2 多列插入

多列插入的方式与单列插入相同，只是在一条语句内写出多条记录，并用逗号隔开。

image-20230710181530167

1.3 插入否则更新

直接插入主键或者唯一键对应的值已经存在会造成数据冲突，进而选择替换操作。

语法：

INSERT [INTO] table_name
	[(column [, column] ...)]
	VALUES (value_list) [, (value_list)] ...
	ON DUPLICATE KEY UPDATE
column = value [, column = value] ...
  • – 通过ROW_COUNT()函数可以获取受到影响的数据行数。

情况1：

表中有冲突数据，但冲突数据的值和update的值相等 – 0 row affected

image-20230710220744556

image-20230710220605460

情况2：

表中没有冲突数据，数据被插入 – 1 row affected

image-20230710214417693

情况3：

表中有冲突数据，并且数据已经被更新 – 2 row affected

image-20230710220446141

1.4 替换

主键或者唯一键 没有冲突，则直接插入；主键或者唯一键如果冲突，则删除后再插入

语法：

REPLACE INTO [(column [, column] ...)] students  VALUES VALUES (value_list) [, (value_list)] ...;

情况1：

表中没有冲突数据，数据被插入 – 1 row affected

image-20230710220209115

情况2：

表中有冲突数据，删除后重新插入 – 2 row affected

image-20230710220359136

2. 基本查询数据

语法：

SELECT
	[DISTINCT] {* | {column [, column] ...}
	[FROM table_name]
	[WHERE ...]
	[ORDER BY column [ASC | DESC], ...]
LIMIT ...

2.1 全列查询

select * from table_name;

  • 通常不建议全列查询，因为查询的列越多，意味着需要传输的数据量越大。
  • 可能会影响到索引的使用。

image-20230711125808002

2.2 指定列查询

select [column1,[column2]…] from table_name;

  • 指定列的顺序不需要按定义表的顺序来。

image-20230711130140162

2.3 查询字段为表达式

select [column1,[column2]…] [表达式 …] from table_name;

情况1：表达式不包含字段

image-20230711130417898

image-20230711130710575

情况2：表达式包含一个字段

image-20230711131151445

情况3：表达式包含多个字段

image-20230711131538791

2.4 为查询结果指定别名

语法：

SELECT column [AS] alias_name [...] FROM table_name;

image-20230711131828188

2.5 结果去重

SELECT DISTINCT {* | {column [, column] …} FROM table_name

image-20230711132107775

2.6 where子句

where子句会进行条件判断筛选数据行。

语法：

SELECT ... FROM table_name WHERE ... [ORDER BY column [ASC | DESC], ...]
  • where子句中可以使用表达式
  • 别名不能用在 WHERE 条件中

含有where子句的SQL语句的执行顺序：

  1. 找表(from table_name),

  2. 筛选(where子句)，

  3. 显示数据(select)并且只是在最后显示数据的时候修改别名

因此where子句筛选不到表中起别名的属性列，并且where子句不能起别名，因此where子句无法使用别名。

比较运算符：

运算符说明
>, >=, <, <=大于，大于等于，小于，小于等于
=等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL，等号两侧的NULL不参与比较
<=>等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)，等号两侧的NULL参与比较
!=, <>不等于
BETWEEN a0 AND a1范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)如果是 option 中的任意一个，返回 TRUE(1)
IS NULL是 NULL
IS NOT NULL不是 NULL
LIKE模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

逻辑运算符：

运算符说明
AND多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT条件为 TRUE(1)，结果为 FALSE(0)

where子句使用示例：

示例1： 英语不及格的同学及英语成绩 ( < 60 )

示例2： 语文成绩在 [80, 90] 分的同学及语文成绩

image-20230711165503347

示例3： 数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

image-20230711165532512

示例4： 姓孙的同学 及 孙某同学

image-20230711165557998

image-20230711140808264

示例5： 语文成绩好于英语成绩的同学

image-20230711165616020

示例6： 总分在 200 分以下的同学

在这里插入图片描述

下面的语句是错误的(where子句不能使用别名)：

image-20230711142304610

image-20230711143046852

示例7： 语文成绩 > 80 并且不姓孙的同学

image-20230711143455973

示例8： 孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

![iimage-20230711165705897

示例9： NULL的查询

image-20230711144650421

image-20230711144725860

image-20230711144803950

image-20230711144851737

以下语句为错误的：

image-20230712153910234

2.7 order by 子句

order by子句能够将查询结果进行排序。

语法：

SELECT ... FROM table_name [WHERE ...]
	ORDER BY column [ASC|DESC], [...];
  • ASC 为升序（从小到大）
  • DESC 为降序（从大到小）
  • 默认为 ASC
  • NULL 视为比任何值都小，升序出现在最上面
  • NULL 视为比任何值都小，降序出现在最下面
  • 多字段排序，排序优先级随书写顺序
  • ORDER BY 中可以使用表达式
  • ORDER BY 子句中可以使用列别名

含有order by子句的SQL语句的执行顺序：

  1. 找到表(from table_name)，
  2. 筛选数据(where子句)
  3. 找出表中数据(select)，
  4. 对最后得到的数据进行排序(order by)

因此order by子句执行时能够看到别名。先得筛选得到最后数据然后排序的方法相比于先排序后筛选得到最后数据数据的方法，能够减少对不必要数据的的排序，减少时间和空间开销。

注意: 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。

示例1： 将数据按姓名的升序、降序查询

image-20230711152235148

示例2： 查询同学各门成绩，依次按 数学降序，英语降序，语文升序的方式显示(数学相同，按照英语降序排列，数学、英语相同按照语文升序排序)

image-20230711165832310

示例3： 查询同学及总分，由高到低

image-20230711165811018

示例4： 查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

image-20230711165754387

2.8 筛选分页结果

筛选分页结果是在显示数据时只显示某几行的数据。

语法：

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死。

筛选分页结果示例：

image-20230711165735263

image-20230711163608249

3. 修改数据

update是对查询到的结果进行列值更新

语法：

UPDATE table_name SET column = expr [, column = expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]
  • update语句不使用where子句筛选会对对应属性列所有数据进行修改。

示例1： 将孙悟空同学的数学成绩变更为 80 分

示例2： 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

image-20230711170032168

示例3： 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

image-20230711171310747

示例4： 将所有同学的语文成绩更新为原来的 2 倍

image-20230711171537506

4. 删除数据

4.1 删除数据

delete能够删除表中若干行数据。

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
  • delete语句不使用where子句筛选会将表中所有数据删除。
  • 删除表中所有数据，不影响表结构。

示例1： 删除孙悟空同学的考试成绩

image-20230711190617401

示例2： 删除表中的所有数据

image-20230711190922054

4.2 截断表

truncate清空表中数据，重置auto_increment;

语法：

TRUNCATE [TABLE] table_name;
  • 只能对整表操作，不能像 DELETE 一样针对部分数据操作 。
  • 实际上TRUNCATE不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚 。
  • 会重置 AUTO_INCREMENT项。

示例：

image-20230711192448694

5. 插入查询结果

将从表中查询出来的数据插入到表中。

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

示例: 删除表中的的重复复记录，重复的数据只能有一份

image-20230711200459278

6. 聚合函数

聚合函数用于对对查询到的数据进行计算并返回一个汇总值。

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

示例1： 统计班级共有多少同学

image-20230711201222215

image-20230711201357308

示例2： 统计有数学成绩的人数

image-20230711201333765

示例3： 统计不重复的数学成绩个数

image-20230711201733276

示例4： 统计数学成绩总分

image-20230711201945837

示例5： 统计英语成绩平均分

image-20230711202010550

示例6： 统计英语不及格人数

image-20230711202111515

示例7： 统计英语不及格同学英语总分

image-20230711202204378

示例8： 返回英语最高分

image-20230711202703738

示例9： 返回英语最低分

image-20230711202726647

以下语句为错误的：

image-20230712153640426

7. group by子句的使用

7.1 group by子句

group by将表中数据根据指定属性列的数据不同而分成不同的组，分组之后能够方便聚合统计。

语法：

SELECT column1 [, column2], ... FROM table_name [WHERE ...] GROUP BY column [, ...] [order by ...] [LIMIT ...];
  • 查询SQL中各语句的执行顺序为：from、where、group by、select、order by、limit。
  • group by后面的列名，表示按照指定列进行分组查询。
  • 指定列内数据相同的被分为一组。
  • select 后只能跟group by中出现的属性或者聚合函数。

分组查询测试表 – 雇员信息表

雇员信息表的SQL语句：

DROP database IF EXISTS `scott`;
CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `scott`;

DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
  `deptno` int(2) unsigned zerofill NOT NULL COMMENT '部门编号',
  `dname` varchar(14) DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) DEFAULT NULL COMMENT '部门所在地点'
);


DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);


DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
  `grade` int(11) DEFAULT NULL COMMENT '等级',
  `losal` int(11) DEFAULT NULL COMMENT '此等级最低工资',
  `hisal` int(11) DEFAULT NULL COMMENT '此等级最高工资'
);


insert into dept (deptno, dname, loc)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept (deptno, dname, loc)
values (20, 'RESEARCH', 'DALLAS');
insert into dept (deptno, dname, loc)
values (30, 'SALES', 'CHICAGO');
insert into dept (deptno, dname, loc)
values (40, 'OPERATIONS', 'BOSTON');

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698,'1981-09-08', 1500, 0, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

insert into salgrade (grade, losal, hisal) values (1, 700, 1200);
insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);
insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);
insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);
insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

上述SQL语句中创建了一个名为scott的数据库，在该数据库中分别创建了部门表（dept）、员工表（emp）和工资等级表（salgrade），并分别向三张表中插入了一些数据用于查询。

将上述SQL保存到文件中：

image-20230712142431587

使用Linux的指令将存有SQL的文件导入：

image-20230712142557013

image-20230712142708625

在MySQL中使用source命令依次执行文件中的SQL:

image-20230712142853932

执行完文件中的SQL后查看数据库，就能看到多了一个名为scott的数据库:

image-20230712143017444

进入scott数据库，能够查询到有关雇员信息的三张表：

image-20230712143100092

部门表（dept）表的结构和数据：

表中属性如下，部门编号（deptno）、部门名称（dname）、部门所在地点（loc）。

image-20230712143232229

员工表（emp）的表结构和表中的内容：

表中属性如下，雇员编号（empno）、雇员姓名（ename）、雇员职位（job）、雇员领导编号（mgr）、雇佣时间（hiredate）、工资月薪（sal）、奖金（comm）、部门编号（deptno）。

image-20230712143352002

工资等级表（salgrade）的表结构和表中的内容：

表中属性如下，等级（grade）、此等级最低工资（losal）、此等级最高工资（hisal）。

image-20230712143417622

示例1： 显示每个部门的平均工资和最高工资

image-20230712144924991

示例2： 显示每个部门的每种岗位的平均工资和最低工资

image-20230712150442546

7.2 HAVING 条件

HAVING条件能够将聚合统计的结果做筛选。

  • 查询SQL中各语句的执行顺序为：from、where、group by、having、select、order by、limit。

HAVING 和 WHERE的区别：

  • 条件筛选的阶段不同。
  • where是对表中具体列进行筛选。
  • 对分组聚合后的结果进行条件筛选。

示例： 显示平均工资低于2000的部门和它的平均工资

image-20230712151709704

8. 补充

SQL查询中各个关键字的执行先后顺序 from > on> join > where > group by > with > having > select > distinct > order by > limit

好想写博客
关注
  • 17
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 16
    评论
专栏目录
评论 16
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

好想写博客

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值