【MySQL数据库】CRUD 增 删 改 查 超详解，有这一篇就够了！
🔥个人主页： 中草药
🔥专栏：【MySQL】探秘：数据库世界的瑞士军刀
目录
⚗️一.CRUD
🧪二.新增（Create）
🧫1.基本操作
🧬2.使用SELECT插入
🔬3.总结
🔭三.查询（Retrieve） （重点）*
📡1.基本查询
🍇2.别名
🍈3.条件查询
3.1.比较运算符
3.2逻辑运算符
3.3案例
🍉4.排序查询
🍊5.去重查询
🍋6.分页查询
🍌 7.聚合查询*
7.1聚合函数
7.3 GROUP BY子句
7.4 HAVING子句
7.5 WITH ROLLUP
🥭 8.关联查询
8.1 关联条件与笛卡尔积
8.2 内连接(INNER JOIN)
8.3 外连接
🍍9.合并查询
9.1. UNION
9.2. UNION ALL
🍎10.子查询（了解）
🍒四. 修改（Update）
🍓1.基本操作
🫐2. 修改表结构（ALTER TABLE）
🥝3.注意事项
🍅五. 删除（Delete）
💐1.基本操作
🌸2.快速清空表（TRUNCATE）
🍀3. 删除表或数据库对象（DROP）
🧬4.注意事项
🪥六.约束条件
🧽1.非空约束 (NOT NULL)
🧻2.唯一约束 (UNIQUE)
🪣3.主键约束 (PRIMARY KEY)
🫧4.外键约束 (FOREIGN KEY)
🛒5.检查约束 (CHECK - MySQL 8.0.16及以上版本支持)--了解
🪤七.总结与反思
⚗️一.CRUD
MySQL的CRUD操作指的是数据库操作中的四个基本动作：Create（创建）、Read（读取）、Update（更新）和Delete（删除）。这些操作构成了数据库管理系统中最核心的数据操作集合，用于管理关系型数据库中的记录。
CRUD操作是数据库管理的基础，它们共同构成了数据生命周期管理的闭环，从数据的产生到数据的最终废弃，每一步都离不开这四个操作。它们的重要性在于：
- 灵活性：使得数据库能够适应不断变化的数据需求，支持动态数据管理。
- 数据完整性：通过精确的增删改查操作，维护数据库数据的准确性与一致性。
- 性能优化：合理的CRUD操作策略可以提升查询速度，减少资源消耗，优化数据库性能。
- 数据安全：通过权限控制CRUD操作，确保数据的访问和修改符合安全策略。
在MySQL中，通过优化CRUD操作的执行效率和安全性，可以极大地提升数据库应用的性能和用户体验。
注释：在SQL中可以使用“--空格+描述”来表示注释说明
🧪二.新增（Create）
在MySQL中，"新增操作"通常指的是向数据库中插入新的记录，这主要通过INSERT语句来完成。新增操作是数据库管理中的基本操作之一，对于维护和更新数据库内容至关重要。以下是关于MySQL中新增操作的一些关键点和示例：
🧫1.基本操作
--插入单行数据 INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); --插入多行数据 INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...), (value4, value5, value6, ...), (...);- table_name是要插入数据的表的名称。
- (column1, column2, column3, ...)是表中的列名列表，列出你要插入数据的列。这可以省略，如果你插入所有列的值，并且顺序与表定义一致。
- (value1, value2, value3, ...)是你希望插入的具体值，与列名一一对应。
举例：
-- 创建一张学生表 drop table if exist student; create table student ( id int, sn int comment '学号', name varchar(20) comment '姓名', qq_mail varchar(20) comment 'QQ邮箱' ); -- 单行数据+全列插入 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致 insert into student values (100, 10000, '唐三藏', NULL); insert into student values (101, 10001, '孙悟空', '11111'); -- 多行数据+指定列插入 插入两条记录，value_list 数量必须和指定列数量及顺序一致 insert into student (id, sn, name) values (102, 20001, '曹孟德'), (103, 20002, '孙仲谋');
🧬2.使用SELECT插入
你还可以从另一个查询的结果插入数据，这在复制表数据或更新表结构时特别有用：
INSERT INTO table_name (column1, column2, column3, ...) SELECT columnA, columnB, columnC, ... FROM source_table WHERE some_condition;
🔬3.总结
新增操作在MySQL中是数据录入和数据库内容管理的核心部分，它支持数据库的动态增长和数据的实时更新。通过灵活运用INSERT语句的各种形式，开发者可以高效地实现数据的批量插入、条件插入和冲突处理，满足不同场景下的数据管理需求。
🔭三.查询（Retrieve） （重点）*
MySQL中的查询操作主要通过SQL语言中的SELECT语句来实现，这是数据查询语言(DQL)的一部分，用于从数据库中检索数据。查询操作是数据库管理中最频繁使用的功能之一，它支持从简单的数据检索到复杂的分析和数据汇总。以下是MySQL中查询操作的几个关键方面和示例：
📡1.基本查询
--单表查询 SELECT column1, column2 FROM table_name; --全列查询 SELECT * FROM table_name;
- column1, column2是要选择的列名。
- table_name是查询的表名。
- *表示选择所有列。
-
通常情况下不建议使用
*
进行全列查询
-- 1.
查询的列越多，意味着需要传输的数据量越大；
-- 2.
可能会影响到索引的使用。（索引待后面博客讲解）
举例
-- 创建考试成绩表 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);
查询字段为表达式-- 表达式不包含字段 SELECT id, name, 10 FROM exam_result; -- 表达式包含一个字段 SELECT id, name, english + 10 FROM exam_result; -- 表达式包含多个字段 SELECT id, name, chinese + math + english FROM exam_resul
🍇2.别名
为查询结果中的列指定别名，表示返回的结果集中，以别名作为该列的名称，语法SELECT column [AS] alias_name [...] FROM table_name
🍈3.条件查询
使用WHERE子句根据条件筛选数据：
SELECT column1, column2 FROM table_name WHERE condition;
condition是筛选条件，如column1 = 'value'。
3.1.比较运算符
运算符 说明 >, >=, 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; 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 70 分以上的数学最低分 SELECT MIN(math) FROM exam_result WHERE math > 70;
7.3 GROUP BY子句
- 作用：将结果集按照一个或多个列进行分组，每个组内可以应用聚合函数进行统计计算。
- 语法：GROUP BY column1, column2,...
举例
-- 准备测试表及数据：职员表，有id（主键）、name（姓名）、role（角色）、salary（薪水） create table emp( id int primary key auto_increment, name varchar(20) not null, role varchar(20) not null, salary numeric(11,2) ); insert into emp(name, role, salary) values ('马云','服务员', 1000.20), ('马化腾','游戏陪玩', 2000.99), ('孙悟空','游戏角色', 999.11), ('猪无能','游戏角色', 333.5), ('沙和尚','游戏角色', 700.33), ('隔壁老王','董事长', 12000.66); -- 查询每个角色的最高工资、最低工资和平均工资 select role,max(salary),min(salary),avg(salary) from emp group by role;7.4 HAVING子句
- 作用：对GROUP BY产生的结果进行过滤，类似于WHERE子句，但HAVING作用于分组后的数据。
- 语法：HAVING condition
举例
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary) select role, sum(salary) from emp group by role with rollup; +--------------+-------------+ | role | sum(salary) | +--------------+-------------+ | 服务员 | 1000.20 | | 游戏角色 | 2032.94 | | 游戏陪玩 | 2000.99 | | 董事长 | 12000.66 | | NULL | 17034.79 | +--------------+-------------+ 5 rows in set (0.00 sec)
🥭 8.关联查询
关联查询是MySQL中一种重要的查询技术，它允许用户从两个或多个表中同时检索数据，基于这些表之间的某种关联关系。关联查询通常使用 JOIN 语句来完成，通过指定关联条件来确保从不同表中取出的数据行是相互匹配的。下面是关联查询的一些关键概念和类型：
8.1 关联条件与笛卡尔积
在进行关联查询之前，了解笛卡尔积是很重要的。如果在关联查询中没有指定正确的关联条件，MySQL会执行一个笛卡尔积操作，即将第一个表的每一行与第二个表的每一行进行配对，生成的结果集大小将是两个表行数的乘积。在大多数情况下，这样的结果是没有意义的，因此我们需要通过关联条件来限制结果集，使其只包含有意义的匹配行。
8.2 内连接(INNER JOIN)
只返回两个表中匹配的行。如果在一个表中的某行在另一个表中找不到匹配，那么这行不会出现在结果集中。
举例
insert into classes(name, descri) values ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'), ('中文系2019级3班','学习了中国传统文学'), ('自动化2019级5班','学习了机械自动化'); insert into student(sn, name, qq_mail, classes_id, id) values ('09982','黑旋风李逵','xuanfeng@qq.com',1,1), ('00835','菩提老祖',null,1,2), ('00391','白素贞',null,1,3), ('00031','许仙','xuxian@qq.com',1,4), ('00054','不想毕业',null,1,5), ('51234','好好说话','say@qq.com',2,6), ('83223','tellme',null,2,7); insert into course 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);-- 查询许仙同学的成绩 select sco.course_id,sco.score from student stu inner join score sco on stu.id=sco.student_id and stu.name='许仙'; +-----------+-------+ | course_id | score | +-----------+-------+ | 1 | 67.0 | | 3 | 23.0 | | 5 | 56.0 | | 6 | 72.0 | +----------+-------+
-- 成绩表对学生表是多对1关系，查询总成绩是根据成绩表的同学id来进行分组的 SELECT stu.sn, stu.NAME, stu.qq_mail, sum( sco.score ) FROM student stu join score sco ON stu.id = sco.student_id GROUP BY sco.student_id; +-------+-----------------+-----------------+------------------+ | sn | NAME | qq_mail | sum( sco.score ) | +-------+-----------------+-----------------+------------------+ | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 300.0 | | 835 | 菩提老祖 | NULL | 119.5 | | 391 | 白素贞 | NULL | 200.0 | | 31 | 许仙 | xuxian@qq.com | 218.0 | | 54 | 不想毕业 | NULL | 118.0 | | 51234 | 好好说话 | say@qq.com | 178.0 | | 83223 | tellme | NULL | 172.0 | +-------+-----------------+-----------------+------------------+
8.3 外连接
- 左连接 (LEFT JOIN) / 左外连接: 返回左表的所有行，即使在右表中没有匹配。如果右表中没有匹配，则结果集中右表的部分将包含NULL值。
- 右连接 (RIGHT JOIN) / 右外连接: 与左连接相反，返回右表的所有行，即使在左表中没有匹配。左表中没有匹配的行将以NULL值填充。
- 全连接 (FULL JOIN) / 全外连接: 返回左表和右表中的所有行。如果某一边没有匹配，则另一边的对应值为NULL。需要注意的是，MySQL本身不直接支持 FULL JOIN，但可以通过 LEFT JOIN 和 UNION 或其他技巧来模拟。
-- 左外连接，表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件; -- 右外连接，表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件;
举例
-- “老外学中文”同学 没有考试成绩，也显示出来了 select * from student stu left join score sco on stu.id=sco.student_id; -- 对应的右外连接为： select * from score sco right join student stu on stu.id=sco.student_id; +-------+-----------------+-----------------+------------+------+-------+------------+----------+ | sn | name | qq_mail | classes_id | id | score | student_id | couse_id | +-------+-----------------+-----------------+------------+------+-------+------------+----------+ | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 | | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 98.5 | 1 | 3 | | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 33.0 | 1 | 5 | | 9982 | 黑旋风李逵 | xuanfeng@qq.com | 1 | 1 | 98.0 | 1 | 6 | | 835 | 菩提老祖 | NULL | 1 | 2 | 60.0 | 2 | 1 | | 835 | 菩提老祖 | NULL | 1 | 2 | 59.5 | 2 | 5 | | 391 | 白素贞 | NULL | 1 | 3 | 33.0 | 3 | 1 | | 391 | 白素贞 | NULL | 1 | 3 | 68.0 | 3 | 3 | | 391 | 白素贞 | NULL | 1 | 3 | 99.0 | 3 | 5 | | 31 | 许仙 | xuxian@qq.com | 1 | 4 | 67.0 | 4 | 1 | | 31 | 许仙 | xuxian@qq.com | 1 | 4 | 23.0 | 4 | 3 | | 31 | 许仙 | xuxian@qq.com | 1 | 4 | 56.0 | 4 | 5 | | 31 | 许仙 | xuxian@qq.com | 1 | 4 | 72.0 | 4 | 6 | | 54 | 不想毕业 | NULL | 1 | 5 | 81.0 | 5 | 1 | | 54 | 不想毕业 | NULL | 1 | 5 | 37.0 | 5 | 5 | | 51234 | 好好说话 | say@qq.com | 2 | 6 | 56.0 | 6 | 2 | | 51234 | 好好说话 | say@qq.com | 2 | 6 | 43.0 | 6 | 4 | | 51234 | 好好说话 | say@qq.com | 2 | 6 | 79.0 | 6 | 6 | | 83223 | tellme | NULL | 2 | 7 | 80.0 | 7 | 2 | | 83223 | tellme | NULL | 2 | 7 | 92.0 | 7 | 6 | +-------+-----------------+-----------------+------------+------+-------+------------+----------+
🍍9.合并查询
在MySQL中，合并查询通常指的是使用 UNION 或 UNION ALL 操作符来组合多个 SELECT 语句的结果，从而生成一个统一的结果集。这两种操作提供了灵活的方式来整合不同查询的数据，适用于多种场景，比如报表生成、数据分析等。下面是对这两种合并查询方式的详细说明：
9.1. UNION
UNION 操作符用于合并两个或多个 SELECT 语句的结果集，同时自动去除结果中的重复行。使用 UNION 时，需注意以下几点：
- 列数相同：所有参与合并的 SELECT 语句必须返回相同数量的列。
- 列类型兼容：虽然列不需要严格相同类型，但它们之间应能进行类型转换，以便比较和去重。
- 列顺序一致：各 SELECT 语句中的列顺序需要保持一致。
- 结果排序与去除重复：MySQL 自动对最终结果进行排序并去除完全相同的行。
- 列名继承：结果集的列名通常取自第一个 SELECT 语句中指定的列名。
select * from course where id
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!
