目录
一. 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>... ` :表 1和表 2表示查询数据的来源,可以是单个或多个。
(3)`WHERE <表达式>` :是可选项,如果选择该项,那么限定查询数据必须满足该查询条件。
(4)`GROUP BY< 字段 >` :该子句告诉 MySQL 如何显示查询出来的数据,并按照指定的字段分组。
(5)`[ORDER BY < 字段 >]` :该子句告诉 MySQL 按什么样的顺序显示查询出来的数据,可以进行的排序有升序(ASC)和降序(DESC),默认情况下是升序。
(6)`[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) ];
含义:
(1)`<表名>`:指定被操作的表名。
(2)`<列名>`:指定需要插入数据的列名。若向表中的所有列插入数据,则全部的列名均可省略,直接采用 INSERT <表名> VALUES(...)即可。
(3)`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 子句]
含义:
(1)`<表名>`:用于指定要更新的表名称。
(2)`SET` 子句:用于指定表中要修改的列名及其列值。其中,每个指定的列值可以是表达式,也可以是该列对应的默认值。如果指定的是默认值,可用关键字 DEFAULT 表示列值。
(3)`WHERE` 子句:可选项。用于限定表中要修改的行。若不指定,则修改表中所有的行。
(4)`ORDER BY` 子句:可选项。用于限定表中的行被修改的次序。
(5)`LIMIT` 子句:可选项。用于限定被修改的行数。
1.5 DELETE(删除)
语法:
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
含义:
(1)`<表名>`:指定要删除数据的表名。
(2)`ORDER BY` 子句:可选项。表示删除时,表中各行将按照子句中指定的顺序进行删除。
(3)`WHERE` 子句:可选项。表示为删除操作限定删除条件,若省略该子句,则代表删除该表中所有行。
(4)`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-04
将日期转换成字符:DATE_FORMAT (‘2004/10/14’,‘%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 具体不同
初始数据:
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 结论
相当于数学中的交集和并集,所谓去重并不需要所有列相同。