MySQL之CRUD、常见函数及union查询

目录

一.?CRUD

? ? ? ? 1.1 什么是crud

? ? ? ? 1.2?SELECT(查询)

? ? ? ? 1.3?INSERT(新增)

? ? ? ? 1.4?UPDATE(修改)

? ? ? ? 1.5?DELETE(删除)

二.?函数

? ? ? ? 2.1?常见函数

? ? ? ? 2.2?流程控制函数

? ? ? ? 2.3?聚合函数

三.?union与union all

? ? ? ? 3.1?union

? ? ? ? 3.2?union all

? ? ? ? 3.3 具体不同

? ? ? ? 3.4 结论

四. 思维导图

---

一.?CRUD

? ? ? ? 1.1 什么是crud

????????crud是指在做计算处理时的增加(Create)、读取(Read)、更新(Update)和删除(Delete)几个单词的首字母简写。crud主要被用在描述软件系统中数据库或者持久层的基本操作功能。

? ? ? ? 1.2?SELECT(查询)

概念：

????????查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。

顺序：

????????条件查询执行顺序：where(条件)? group by(分组)? having(筛选)? order by(排序)? limit(记录数)

语法：

SELECT
{* | <字段列名>}
[FROM <表1>,<表2>...
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}...]]
[ORDER BY <order by definition>]
[LIMIT?[<offset>,] <row count>]
]

含义：

• `{*|<字段列名>}`：包含星号通配符的字段列表，表示所要查询字段的名称。
• `<表 1>，<表 2>... ` ：表 1和表 2表示查询数据的来源，可以是单个或多个。
• `WHERE <表达式>` ：是可选项，如果选择该项，那么限定查询数据必须满足该查询条件。
• `GROUP BY< 字段 >` ：该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
• `[ORDER BY < 字段 >]` ：该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。
• `[LIMIT[<offset>，]<row count>]` ：该子句告诉 MySQL 每次显示查询出来的数据条数。

? ? ? ? 1.3?INSERT(新增)

概念：

????????使用 INSERT 语句向数据库已有的表中插入一行或者多行元祖数据。

语法：

插入单行：INSERT... SET语句

INSERT INTO <表名>
SET <列名1> = <值1>,
? ?<列名2> = <值2>,
? ?...

插入多行：INSERT...VALUES语句

INSERT INTO <表名> [ <列名1> [ , ...<列名n>] ]
VALUES (值1) [.... , (值n) ];

含义：

• `<表名>`：指定被操作的表名。
• `<列名>`：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可省略，直接采用 INSERT <表名> VALUES(...)即可。
• `VALUES` 或 `VALUE` 子句：该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。

总结：?

由 INSERT 语句的两种形式可以看出：

1. - 使用 INSERT...VALUES 语句可以向表中插入一行数据，也可以插入多行数据；
2. - 使用 INSERT...SET 语句可以指定插入行中每列的值，也可以指定部分列的值；
3. - INSERT...SELECT 语句向表中插入其他表中的数据。
4. - 采用 INSERT ... SET 语句可以向表中插入部分列的值，这种方式更为灵活；
5. - INSERT...VALUES 语句可以一次插入多条数据。

? ? ? ? 1.4?UPDATE(修改)

语法：

UPDATE <表名> SET 字段 1= 值 1 [, 字段 2 = 值 2] [WHERE 子句]
[ORDER BY 子句] [LIMIT 子句]

含义：

• `<表名>`：用于指定要更新的表名称。
• `SET` 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
• `WHERE` 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
• `ORDER BY` 子句：可选项。用于限定表中的行被修改的次序。
• `LIMIT` 子句：可选项。用于限定被修改的行数。

? ? ? ? 1.5?DELETE(删除)

语法：

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

含义：

• `<表名>`：指定要删除数据的表名。
• `ORDER?BY`?子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
• `WHERE`?子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中所有行。?
• `LIMIT`?子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

二.?函数

? ? ? ? 2.1?常见函数

字符函数：

如果忘了函数的意思可以直接 SELECT + 你想要测试的函数就好，就能看到函数的效果

• 转小写：LOWER?('SQL?Course') -----?sql course
• 转大写：UPPER?('SQL?Course') ------ SQL COURSE
• 拼接：CONCAT?('Hello','World') ------?HelloWorld
• 截取：SUBSTR?('HelloWorld',1,5) ----?Hello
• 长度：LENGTH?('HelloWorld') ----?10
• 字符出现索引值：amcdINSTR?('HelloWorld','W') ----- 6
• 字符截取后半段：TRIM?('H' FROM 'HelloWorld') -----?elloWorld
• 字符替换：REPLACE?('abcd','b','m') -----?amcd

数字函数：

• 四舍五入：ROUND (45.926, 2) -----?45.93
• 截取：TRUNC (45.926, 2) ------?45.92
• 求余：MOD (1600, 300) -----?100

日期函数：

• 获取当前日期：now()
• 将日期格式的字符转换成指定格式的日期：STR_TO_DATE?('9-13-1999','%m-%d-%Y') ----?1999-09-13
• 将日期转换成字符：DATE_FORMAT (‘2018/6/6’,‘%Y年%m月%d日’) ----?2018年06月06日

? ? ? ? 2.2?流程控制函数

使用场景：

行转列：
????????把基本不需要改动的数据放在行
????????把可能要进行变化的数据放在列

语法：

CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END

示例：

01）查询同时存在" 01 "课程和" 02 "课程的情况

SELECT
t3.*,
(CASE WHEN t1.cid = '01' THEN t1.score END) 语文,
(CASE?WHEN?t2.cid?=?'02'?THEN?t2.score?END) 数学
FROM
( SELECT * FROM t_mysql_score sc WHERE sc.cid = '01') t1,
(?SELECT?*?FROM?t_mysql_score?sc?WHERE?sc.cid?=?'02')?t2,
t_mysql_student t3
WHERE
t1.sid = t2.sid
AND t1.sid = t3.sid

02）查询会议信息（包含会议信息表数据，主持人姓名、审批人姓名、会议状态）

SELECT a.id,a.title,a.content,a.canyuze,a.liexize,a.zhuchiren,b.`name`,a.location
? ? ?,DATE_FORMAT(a.startTime,'%Y-%m-%d %H:%i:%s') as startTime
? ? ?,DATE_FORMAT(a.endTime,'%Y-%m-%d?%H:%i:%s')?as?endTime
? ? ?,a.state
? ? ?,(case a.state
? ? ?when 0 then '取消会议'
? ? ?when?1?then?'新建'
? ? ?when?2?then?'待审核'
? ? ?when?3?then?'驳回'
? ? ?when?4?then?'待开'
? ? ?when?5?then?'进行中'
? ? ?when?6?then?'开启投票'
? ? ?else '结束会议' end
? ? ?) as meetingState
? ? ?,a.seatPic,a.remark,a.auditor,c.`name` as auditorName
? ? ?FROM t_oa_meeting_info a
? ? ?inner join t_oa_user b on a.zhuchiren = b.id
? ? ?left JOIN t_oa_user c on a.auditor = c.id where 1 = 1 ;

? ? ? ? 2.3?聚合函数

• sum 求和
• avg 平均值
• max 最大值
• min 最小值
• count 计算个数

三.?union与union all

? ? ? ? 3.1?union

• 概念：

? ? ? ? 1. 用于合并两个或者多个SELECT语句的结果集，并去除重复的行。

? ? ? ? 2. UNION操作符执行去除重复行的操作，它通过对所有的结果集进行排序和比较来实现

• 语法：

????????将多个SELECT语句放在一起，并使用UNION关键字将它们连接起来。每个SELECT语句的列数、列名和数据类型必须相同。

• 使用场景：

????????当需要合并多个查询结果，并且不希望出现重复行时，可以使用UNION操作符。它适用于数据需要去重的情况。

? ? ? ? 3.2?union all

• 概念：

? ? ? ? 1. 用于合并两个或者多个SELECT语句的结果集，包括重复的行。

? ? ? ? 2.?UNION ALL操作符不执行去除重复行的操作，它直接将所有的结果集合并在一起。

• 语法：

????????将多个SELECT语句放在一起，并使用UNION ALL关键字将它们连接起来。每个SELECT语句的列数、列名和数据类型可以不同。

• 使用场景：

????????当需要合并多个查询结果，包括重复行时，可以使用UNION ALL操作符。它适用于不需要去重的情况，或者在已经确定结果不会有重复行的情况下。

? ? ? ? 3.3 具体不同

我们可以在数据库使用sql语句进行直观的差别展示

• 初始数据：

????????SELECT * from t_oa_meeting_info where id>=1 and id <= 8;

?????????SELECT?*?from?t_oa_meeting_info?where?id>=6 and?id?<= 10;

• UNION：

????????SELECT?*?from?t_oa_meeting_info?where?id>=1?and?id?<=?8;

????????UNION

????????SELECT?*?from?t_oa_meeting_info?where?id>=6?and?id?<=?10;

????????678只出现了1次

• UNION ALL：

????????SELECT?*?from?t_oa_meeting_info?where?id>=1?and?id?<=?8;

????????UNION ALL

????????SELECT?*?from?t_oa_meeting_info?where?id>=6?and?id?<=?10;

????????678重复出现? ? ? ?

? ? ? ? 3.4 结论

????????相当于数学中的交集和并集，所谓去重并不需要所有列相同。

四. 思维导图