MySQL表的增删改查（基础）

1. CRUD

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

注释:在SQL中可以使用"--空格＋描述"来表示注释说明.

2. 新增（Create）

语法:

INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);

案例:

DROP TABLE IF EXISTS student
CREATE TABLE student(
ID INT,
SN INT COMMENT '学号',
NAME varchar(20) COMMENT '姓名',
QQ_Mail VARCHAR(20) COMMENT '邮箱'
);

2.1 单行数据 + 全列插入

--插入两条记录,数量和定义表的列的数量和顺序一致

INSERT INTO student VALUES(100, 10000,'张三', null);
INSERT INTO student VALUES(101, 10001,'李四','123456qq.com');
SELECT *from student;

插入后表中的数据为下图  

当你使用全列插入时，你不需要指定列名，但需要保证值的顺序与表中的列顺序相匹配。这种方式适用于当你想向所有列插入数据的情况。这种方式简单直接，但如果表结构发生变化（例如，添加或删除列），那么该语句可能需要调整以反映这些变化。 

2.2 多行数据 + 指定列插入

INSERT INTO student (ID,SN,NAME) VALUES 
                 (102,20001,'王五'),
				 (103,20002,'赵六');
SELECT *from student;

当你插入多行数据或者不想对所有列进行插入操作时，你需要明确指定列名，并对应地插入值。这在批量插入数据时非常有用，尤其是当某些列需要保持为NULL或者默认值时。这种方式提供了更大的灵活性，允许你指定哪些列需要被插入数据，以及每行数据的具体内容。 

2.3 总结

• 全列插入:通常用于单行数据的快速插入，要求提供所有列的值，而且依赖于表中列的顺序。
• 指定列插入:更加灵活，允许你插入多行数据，且可以只针对特定的列进行插入，不依赖于列的顺序，并且可以忽略某些列，让它们保持默认值或NULL。
• 在实际使用时，你应该根据具体的应用场景和需求来选择最合适的插入方式。

3. 查询（Retrieve）

语法:

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

• DISTINCT：可选关键字，用于去除结果集中的重复行。
• column：要查询的列名，可以是一个或多个列名，用逗号分隔。
• table_name：要查询的表名。
• WHERE：可选子句，用于设置查询条件，以便筛选出符合条件的行。
• ORDER BY：可选子句，用于根据一个或多个列对结果集进行排序，可以指定升序（ASC）或降序（DESC）。
• LIMIT：可选子句，用于限制查询结果的行数，可以用来实现分页功能。

案列:

--创建考试成绩表
DROP TABLE if EXISTS exam_result;
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,98,56),
(2,'李四',87.5,78,77),
(3,'王五',88,98.5,90),
(4,'赵六',82,84,67),
(5,'孙七',55.5,85,45),
(6,'周八',70,73,78.5),
(7,'吴九',75,65,30);

3.1 全列查询 

--通常情况下不建议使用 * 进行全列查询
--1. 查询的列越多, 意味着需要传输的数据量越大;
--2. 可能会影响到索引的使用.

select *from exam_result;

3.2 指定列查询 

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

select id,name,english FROM exam_result;

3.3 查询字段为表达式

--表达式不包含字段 

SELECT id, name, 10 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,chinese + math + english AS 总分 from exam_result;

提高可读性：虽然AS关键字在某些情况下可以省略，但为了保持语句的清晰和易读，建议还是加上AS关键字。

3.5 去重: DISTINCT

使用DISTINCT关键字对某列数据进行去重:

SELECT math FROM exam_result;

 

SELECT DISTINCT math FROM exam_result;

通过使用DISTINCT关键字后,我们对比两张表可以发现,使用DISTINCT的表少了一个98,只有留了第一个98分.

3.6 排序: ORDER BY

语法:

SELECT column1, column2, ... 
FROM table_name 
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

--ASC 为升序 (从小到大)
--DESC 为降序 (从大到小)
--默认为 ASC

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

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

-- 查询同学姓名和 qq_mail, 按qq_mai 排序显示
SELECT name, qq_mail FROM student ORDER BY qq_mail;
SELECT name, qq_mail FROM student ORDER BY qq_mail DESC;

使用表达式及别名排序 

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

SELECT name, chinese, + english + math FROM exam_result 
ORDER BY chinese + english + math DESC;

SELECT name, chinese +english + math total FROM exam_result;
ORDER BY total DESC;

 

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

-- 查询同学各门成绩, 依次按 数学降序, 英语升序, 语文升序的方式显示 

SELECT name, math, english, chinese FROM exam_result
ORDER BY math DESC, english, chinese;

 3.7 条件查询：WHERE

比较字符串:

逻辑运算符:

 

注意: 1. WHERE条件可以使用表达式, 但不能使用别名.

          2. 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 AS 总分 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.BETWEEN ... AND ...

-- 查询语文成绩在[80,90]分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 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;

 

3.模糊查询: LIKE

-- " % "匹配任意多个字符 (包括 0 个)字符
SELECT name FROM exam_result WHERE name LIKE '张%';-- 张某,张某某
-- " _ " 匹配严格的一个字符
SELECT name FROM exam_result WHERE name LIKE '张_';-- 张三

4.NULL的查询: IS[NOT]NULL

--查询 qq_mail 已知的同学姓名
SELECT name FROM student WHERE qq_mail IS NOT NULL;

--查询 qq_mail 已知的同学姓名
SELECT name FROM student WHERE qq_mail IS NOT NULL;

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） 

语法:

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;

 以上就是数据库的MySQL基本操作的所有内容，希望能够对大家有帮助。如果大家有什么解决不了的问题，欢迎大家评论区留言或者私信告诉我。如果感觉对自己有用的话，可以点个赞或关注鼓励一下博主，我会越做越好的，感谢各位的支持，我们下期见。