数据库的增删改查(一)_csdn 数据库增删-CSDN博客

本文链接：https://blog.csdn.net/m0_53714343/article/details/131031448

1、CRUD

*注释：在SQL中可以使用"--空格+描述"来表示注释说明

*CRUD即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写

2、新增(Create)

语法：

insert [into] table_name

[(column[，column] ...)]

values(value_list) [， (value_list)] …

注：

*[]：选填

*value_list：value，[，value] ...

示例：

-- 创建一张学生表

create table student01(

id int,

sn int comment '学号',

name varchar(20) comment '姓名',

qq_mail varchar(20) comment'QQ邮箱'

);

2.1、单行数据+全列插入

-- 插入三条记录，value_list 数量必须和定义表的列的数量及顺序一致

insert into student01 values(100,10000,'鸡你实在是太美',null);

insert into student01 values(101,10001,'只因坤坤爱坤坤',1234);

insert into student01 values(102,10002,'唉哟,你干嘛',12345);

2.2、多行数据+指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致

insert into student01(id,sn,name) values

(103,10003,'中分头'),

(104,10004,'背带裤'),

(105,10005,'我叫坤坤你记住');

3、查询(Retrieve)

语法:

select [distinct] {* | {column [，column] ...}

[from table_name]

[where ...]

[order by column [asc|desc]，...]
limit ...

示例：

-- 创建考试成绩表

create table exam_result(

id int,name varchar(20),

chinese decimal(3,1),

math decimal(3,1),

english decimal(3,1)

);

-- 插入测试数据

insert into exam_result(id,name,chinese,math,english) values

(1,'张三',67,74,90),

(2,'李四',10,28,10),

(3,'王五',99,98,99),

(4,'赵六',12,23,44),

(5,'坤坤',66,66,66),

(6,'他真的很努力',9,8,7);

3.1、全列查询

-- 通常情况下不建议使用*进行全列查询

-- 1.查询的列越多，意味着需要传输的数据量就会越大

-- 2.可能会影响到索引的使用

select * from exam_result;

3.2、指定列查询

-- 指定列的顺序不需要按照自定义的表的顺序来

select id,name,math from exam_result;

3.3、查询字段位表达式

-- 表达式不包括字段

select id,name,5 from exam_result;

-- 表达式包含一个字段

select id,name,english - 10 from exam_result;

-- 表达式包含多个字段

select id,name,chinese+math+english from exam_result;

3.4、别名

为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称

语法：

select column [as] alias_name [...] from table_name;

-- 结果集中，表头的列名等于别名

select id,name,math+chinese+english as 总分 from exam_result;

3.5、去重：DISTINCT

使用关键字distinct对数据去重

-- 插入一条重复数据记录

insert into exam_result values(7,'鸽鸽',40,66,99);

--66分重复了

select math from exam_result;

--去重结果

select distinct math from exam_result;

3.6、排序：ORDER BY

语法：

-- asc为升序排列

-- desc为降序排列

-- 系统默认为asc

select ... from table_name [where ...]

order by column [asc | desc],[...];

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

*NULL数据排序，视为比任何值都小，升序出现在最上面，降序为最下面

-- 查询同学姓名和qq_mail,按 qq_mail排序显示

select name,qq_mail from student01 order by qq_mail;

select name,qq_mail from student01 order by qq_mail desc;

*使用表达式和别名排序

-- 查询同学及总分，由高到低

select name,chinese+math+english as 总分 from exam_result order by 总分 desc;

*可以对多个字段进行排序，排序优先级随书写顺序

-- 查询同学们的各门成绩，一次按照数学降序，英语升序，语文升序的方式显示

select name,math,english,chinese from exam_result order by math desc,english,chinese;

3.7、条件查询

比较运算符：

运算符	说明
>,>=,<,<=	大于，大于等于，小于，小于等于
=	等于，NULL不安全，例如NULL=NULL的结果是NULL
<=>	等于，NULL安全，例如NULL<=>的结果是TRUE(1)
!=,<>	不等于
BETWEEN a AND b	范围匹配，[a0,a1],如果a<=value<=b,返回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条件可以使用表达式，但不能使用别名

*and的优先级别高于or，在同时使用时，需要使用小括号()包裹优先执行部分

示例：

*基本查询

-- 查询英语不及格的同学及英语成绩 ( < 60 )

select name,english from exam_result where english<60;

-- 查询语文成绩好于英语成绩的同学

select name,chinese,english from exam_result where chinese >english;

-- 查询总分在 200 分以下的同学

select name,chinese+math+english 总分 from exam_result where chinese+math+english<200;

*AND 与OR

-- 查询语文成绩大于80分，且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;

-- 查询语文成绩大于80分，或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;

-- 观察AND 和 OR 的优先级：
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;

*范围查询

1、BETWEND ... AND ...

-- 查询语文成绩在 [50, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 50 AND 90;

-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese
<= 90;

2、IN

-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

-- 使用 OR 也可以实现
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math
= 98 OR math = 99;

*模糊查询 LIKE

-- % 匹配任意多个（包括 0 个）字符
SELECT name FROM exam_result WHERE name LIKE '张%';-- 匹配到张三

-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE '坤_';-- 匹配到坤坤

3.8、分页查询 LIMIT

语法：

-- 起始下标为 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;

案例：按 id 进行分页，每页 3 条记录，分别显示第 1、2、3 页

-- 第 1 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 0;

-- 第 2 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 3;

-- 第 3 页，如果结果不足 3 个，不会有影响
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 6;

4、修改（UPDATE）

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

示例：

-- 将张三同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '张三';
-- 将坤坤同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '坤坤';
-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT
3;
-- 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

5、删除

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

示例：

-- 删除坤坤同学的考试成绩
DELETE FROM exam_result WHERE name = '坤坤';
-- 删除整张表数据
-- 准备测试表
DROP TABLE IF EXISTS for_delete;
CREATE TABLE for_delete (
id INT,
name VARCHAR(20)
);
-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
-- 删除整表数据
DELETE FROM for_delete;