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

目录

一.CRUD

? ? ? ? 1.1 SELECT（查询）

? ? ? ? 1.2 INSERT（新增）

? ? ? ? 1.3 UPDATE（修改）

? ? ? ? 1.4 DELETE（删除）

二.函数

? ? ? ? 2.1 常见函数

? ? ? ? 2.2 流程控制函数

? ? ? ? 2.3 聚合函数

?三.union与union all

? ? ? ? 3.1 概念

? ? ? ? 3.2 语法

? ? ? ? 3.3 专业词解释

? ? ? ? 3.4 使用场景

? ? ? ? 3.5?实例

? ? ? ? ? ? ? ? 3.5.1 全列

? ? ? ? ? ? ? ? 3.5.2 结论

四.思维导图

---

一.CRUD

? ? ? ? 1.1 SELECT（查询）

1.概念：

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

2.语法：

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

3.含义：

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

? ? ? ? 1.2 INSERT（新增）

1.概念：

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

2.语法:

• INSERT...VALUES语句：INSERT INTO <表名> [<列名1>[,...<列名n>]] VALUES (值1)[...,(值n)]；
• INSERT...SET语句：INSERT INTO <表名> SET <列名> = <值1>,<列名> = <值2>, ....

3.含义:

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

? ? ? ? 1.3 UPDATE（修改）

1.语法：

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

2.含义：

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

? ? ? ? 1.4 DELETE（删除）

1.语法：

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

2.含义：

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

二.函数

? ? ? ? 2.1 常见函数

1.字符函数：

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

2.数字函数：

• 四舍五入——ROUND(45.926,2)——45.93
• 截断——TRUNC(45.926,2)——45.92
• 求余——MOD(1600,300)——100

3.日期函数：

• 获取当前日期——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 流程控制函数

1.使用场景：

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

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

3.示例：

• 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：用于合并两个或者多个SELECT语句的结果集，并去除重复的行。

• union all：用于合并两个或者多个SELECT语句的结果集，包括重复的行。

? ? ? ? 3.2 语法

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

? ? ? ? 3.3 专业词解释

• union：UNION操作符执行去除重复行的操作，它通过对所有的结果集进行排序和比较来实现
• union all：UNION ALL操作符不执行去除重复行的操作，它直接将所有的结果集合并在一起。

? ? ? ? 3.4 使用场景

• union：
  当需要合并多个查询结果，并且不希望出现重复行时，可以使用UNION操作符。它适用于数据需要去重的情况。
• union all：
  当需要合并多个查询结果，包括重复行时，可以使用UNION ALL操作符。它适用于不需要去重的情况，或者在已经确定结果不会有重复行的情况下。

? ? ? ? 3.5?实例

? ? ? ? ? ? ? ? 3.5.1 全列

1.初始数据：

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

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

2.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次

3.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.5.2 结论

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

四.思维导图