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

一、CRUD

1.1 SELECT（查询）

概念：

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

 语法：

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.2 INSERT（新增）

概念：

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

 语法：

• INSERT...VALUES语句
  INSERT INTO <表名> [ <列名> [,...<列名n>]]
  VALUES（值1）[...,（值n）];
• INSERT...SET语句
  INSERT INTO <表名>
  SET <列名1> = <值1>,<列名2> = <值2>，...

含义：

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

1.3 UPDATE（修改）

语法：

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

[ORDER BY 子句] [LIMIT 子句]

含义：

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

 1.4 DELETE（删除）

语法：

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

含义：

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

二、函数

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

数字函数 

• 四舍五入 —— 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 union&union all不同点

初始数据：
        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 结论 

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

四、思维导图