SQL开发

Posted on | In

SQL开发

DML

  1. 新增表中的数据：insert into

  2. 删除表中的数据：delete

  3. 修改表中的数据：update

  4. 查询表中的数据：select

新增表中的数据

  1. 确认要把数据插入到那个表中。

  2. 确认表中的数据结构，那些列不能为NULL，那些列可以为NULL，对于不能为NULL的列是否有默认值。

  3. 确认对应插入列的插入值的清单。

1
2
3
4
5
// imc_class表中的class_name插入数据
INSERT into imc_class(class_name) VALUES('MYSQL'),('Redis')

// 当索引发生重复冲突，可选择更新其它字段
ON DUPLICATE KEY UPDATE add_time=CURRENT_TIME
1
2
// imc_class表中的class_name字段设置为索引
CREATE UNIQUE INDEX uqx_classname ON imc_class(class_name)

查询表中的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
// 查询表中所有的数据
SELECT * FROM tableName

// 查询表中只返回class_name的数据
SELECT class_name FROM tableName

// 查询表中class_id = 1的数据
SELECT * FROM tableName WHERE class_id = 1

// 查询表中class_name的MYSQL信息的模糊查询
SELECT class_name FROM tableName WHERE class_name LIKE '%MYSQL%'

// 比较运算符查询=、>、<、>=、<=、<>、!=, <>、!=都表示不等于
SELECT * FROM tableName WHERE num > 1

// 查询表中num的范围在1000-2000的数据
SELECT * FROM tableName WHERE num BETWEEN 1000 AND 2000

// IS NULL、IS NOT NULL判断的值是否为NULL
SELECT * FROM tableName WHERE c1 IS NULL

// LIKE、NOT LIKE %代表任何数量的字符_代表任何一个字符
SELECT 'this mysql' LIKE '%mysql%'

// IN、NOT IN 判断列的值是否在指定的范围内
SELECT * FROM test_is WHERE id IN (1)

// AND,&& AND运算符两边的表达式都为真时，返回的结果才为真，表中title含有mysql并且study_cnt大于5000
SELECT * FROM test_is WHERE title LIKE '%mysql%' AND study_cnt> 5000

// OR,|| OR运算符两边的表达式有一条为真，返回的结果就真，表中title含有mysql或study_cnt大于5000
SELECT * FROM test_is WHERE title LIKE '%mysql%' OR study_cnt> 5000


// XOR运算符两边的表达式一真一假时返回真，两真两假返回假。study_cnt > 5000并且title没有mysql和title含有mysql并且study_cnt < 5000
SELECT * FROM imc_course WHERE  study_cnt > 500 XOR title LIKE '%mysql%'


// 查询表别名a和b的内关联表查询
SELECT a.course_id, a.title, b.chapter_name  FROM imc_course a JOIN imc_chapter b ON b.course_id = a.course_id

// 左关联外联接查询
SELECT  a.course_id,a.title FROM imc_course a LEFT JOIN imc_chapter b ON b.course_id=a.course_id WHERE b.course_id IS NULL

// 分组统计查询,统计a表难度分类的课程统计
SELECT
	level_name,
	class_name,
	COUNT(*) 
FROM
	imc_course a
	JOIN imc_class b ON b.`class_id` = a.class_id
	JOIN imc_level c ON c.level_id = a.level_id 
GROUP BY
	level_name,class_name

// HAVING对聚合后的数据进行过滤,统计每个分类下的课程大于3门的难度
SELECT
	level_name,
	class_name,
	COUNT(*) 
FROM
	imc_course a
	JOIN imc_class b ON b.`class_id` = a.class_id
	JOIN imc_level c ON c.level_id = a.level_id 
GROUP BY
	level_name,
	class_name 
HAVING
	COUNT(*) > 3

// SUM统计分类下总学习人数
SELECT
	level_name,
	SUM( study_cnt ) 
FROM
	imc_course a
	JOIN imc_level b ON b.level_id = a.level_id 
GROUP BY
	level_name

// 学习人数最多的课程
SELECT
	title,
	study_cnt 
FROM
	imc_course 
WHERE
	study_cnt =(
	SELECT
	MAX( study_cnt )) 
GROUP BY
	title

// 使用ORDER By子句是对查询结果进行排序的最安全方法。增高ASC关键字进行升序排序，DESC进行降序排序。
SELECT title,study_cnt FROM imc_course ORDER BY study_cnt ASC

// Limit子句限制返回结果集的行数，常用于分页。
SELECT
	course_id,
	title,
	study_cnt 
FROM
	imc_course 
ORDER BY
	course_id ASC LIMIT 0,10
常用的聚合函数
聚合函数 说明
COUNT(*)/COUNT(col) 计算符合条件的数据行数
SUM(col_name) 计算表中符合条件的数值列的合计值
AVG(col_name) 计算表中符合条件的数值列的平均值
MAX(col_name) 计算表中符合条件的数值列的最大值
MIN(col_name) 计算表中符合条件的数值列的最小值

视图

视图是一个逻辑的存储的结果，也是一个虚拟的表，视图不存储任何数据。

1
2
// 创建视图的语法
CREATE VIEW vm_course

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW vm_course AS SELECT
a.course_id,
a.title,
b.class_name,
c.type_name,
d.level_name 
FROM
	imc_course a
	JOIN imc_class b ON b.class_id = a.class_id
	JOIN imc_type c ON c.type_id = a.type_id
	JOIN imc_level d ON d.level_id = a.level_id

表删除语句

  1. 确定要删除的数据存储在那张表中。 FROM子句

  2. 确定删除数据的过滤条件。 WHERE子句

  3. 确认是否只删除有限条数据。 ORDER BY …LIMIT子句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 根据条件删除数据
DELETE FROM imc_type  WHERE type_id=10

// 限制删除的最大行数,删除 actor_copy 中 actor_id 最大的 10 行
DELETE FROM actor_copy
ORDER BY actor_id DESC
LIMIT 10;

// 删除表中所有的行
DELETE FROM actor_copy;

//  多表删除,删除 t1 和 t2 表中满足条件的行
DELETE t1, t2
FROM t1 INNER JOIN t2
WHERE t1.id = t2.id;

更新表的数据

  1. 确定要更新的数据存储在那张表中。

  2. 确定要列新的列及值。

  3. 确定更新数据的条件。

1
2
3
4
5
// 更新user表中的状态为1
UPDATE user SET user_status = 1 WHERE user_nick = '程序员'

// 随机更新表中10条数据的状态
UPDATE course SET is_recommand=1 ORDER BY RAND() LIMIT 10

MySQL系统函数

  1. 常用的时间函数
函数名 说明
CURDATE()/CURTIME() 返回当前日期/返回当前的时间
NOW() 返回当前的日期和时间
DATE_FORMAT(date, fmt) 返回当前的日期和时间
SEC_TO_TIME() 把秒数转换成(小时：分：秒)
TIME_TO_SEC() 把(小时：分：秒)转换成秒数
TIME_TO_SEC() 把(小时：分：秒)转换成秒数
DATEDIFF(date1,date2) 返回date1和date2两个日期的相差的天数
DATE_ADD(date,INTERVAL expr unit) 给定的日期增加或减少指定的时间单元(unit:DAY天/HOUR小时/MINUTES/SECOND秒)
EXTRACT(unit FPOM date) 返回日期date的指定部分
UNIX_TIMESTAMP() 返回unix时间戳
FROM_UNIXTIME() 把Unix时间戳转换日期时间 
1
2
// 表中上线时间距当前时间的天数
SELECT title,DATEDIFF(NOW(),online_time) FROM course
  1. 常用的字符串函数
函数名 说明
CONCAT(str1,str2) 把字符串str1,str2连接成一个字符串
CONCAT_WS(‘’,str1,str2) 把字符串str1,str2用分割连接成一个字符串
CHAR_LENGTH(str) 返回字符串str的字符个数
LENGTH(str) 返回字符串str的字节个数
FORMAT(X,D[,locale]) 将数字N格式化为格式,如’#,###,####.##’,并舍入到D位小数
  1. 常用的字符串截取函数
函数名 说明
LEFT(str,len)/RIGHT(str,len) 从字符串的左/右边起返回len长度的子字符串
SUBSTRING(str,pos,len) 从字符串str的pos位置起返回长度为len的子串
SUBSTRING_INDEX(str,delim,count) 返回字符串str按delim分割的前count个字符串
LOCATE(substr,str) 在字符串str中返回子串substr第一次出现的位置
TRIM([remstr FROM] str) 从字符串str两端删除不需要的字符remstr
  1. 其它常用函数
函数名 说明
ROUND(X,D) 对数值X进行四舍五入保留D位小数
RAND() 返回一个0到1之间的随机数
CASE WHEN 用于实现其它语言中的case..when功能，提供数据流控制
MD5(str) 返回str的MD5值 
1
2
3
4
5
6
7
SELECT user_nick,CASE
	WHEN sex=1 THEN '男'
	WHEN sex=0 THEN '女'
	ELSE
		'未知'
END AS 'sex'
 FROM user

SQL高级特性

公用表表达式CTE

  1. MySQL8.0之后的版本才可以食用

  2. CTE生成一个命名临时表，并且只在查询期间有效

  3. CTE临时表在一个查询中可以多次引用及自引用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 简单的公用表达式
WITH cte AS (
	SELECT
		title,
		study_cnt,
		class_id 
	FROM
		course 
WHERE
	study_cnt > 2000)
	
SELECT * FROM cte 
UNION ALL
SELECT * FROM cte
ORDER BY title
窗口函数

一组记录所执行的函数，符合条件的每一条记录会执行函数。

函数名 说明
聚合函数 聚合函数都可以做为窗口函数使用
ROW_NUMBER() 返回窗口分区数据的行号
RANK() 类似于row_number，只是对于相同数据会产生重复的行号，之后的数据行号会产生间隔
DENSE_RANK() 类似于rank区别在于当组内某行数据重复时，虽然行号会重复，但后续的行号不会产生间隔