CDA数据分析师 - SQL数据库基础 查询&连接

• SQL 查询

【应用】

查询单表语句

select语句、from 语句、where 语句、group by 语句、having 语句、order by 语句、limit语句&子查询等

聚合函数，包括 count、sum、avg、max&min 等

【DEMO】

-- 查询单表数据
SELECT *
FROM `score_info` AS SCO
WHERE SCO.id > 0
GROUP BY SCO.id
HAVING SCO.id = 2
ORDER BY SCO.id DESC
-- LIMIT a, b -- a代表起始值（默认为0），b代表限制总数
LIMIT 0, 5;

• SQL 连接

【领会】

表的连接类型，包括内连接（等值、不等值）、外连接（左、右、全）&交叉连接（笛卡尔连接）

查询的集合操作，只包括并集操作

数据的更新及删除

视图的概念及创建

【应用】

查询多表语句

inner join 的用法、left/right/full join 的用法、cross join 的用法&union 的用法等

【DEMO】

-- 查询多表数据 横向连接 （备注：MySQL不支持FUll JOIN）
CREATE OR REPLACE VIEW `v_left_score` AS
SELECT
-- *
STU.id AS '学号',
`STU`.`name` AS '姓名',
SUM( SCO.score ) AS '总分',
AVG( SCO.score ) AS '均分',
ROW_NUMBER ( ) OVER ( ORDER BY SUM( SCO.score ) DESC ) AS '排名' 
FROM
	`student_info` AS STU
	LEFT JOIN `score_info` AS SCO ON STU.id = SCO.stu_id 
GROUP BY
	STU.id 
ORDER BY
	SUM( SCO.score ) DESC;
	
-- 交叉连接（笛卡尔连接）
SELECT * FROM `student_info` 
CROSS JOIN `subject_info`;
-- OR
SELECT * FROM `student_info`, `subject_info`;
	
-- 查询多表数据 纵向连接
SELECT SCO.score FROM `score_info` AS SCO 
UNION
SELECT SCO.score FROM `score_info` AS SCO;

-- 更新数据
UPDATE `student_info`
SET `name` = '华盛顿'
WHERE `id` = 3;

-- 删除数据
DELETE FROM `student_info`
WHERE `id` = 4;

-- 新建视图
CREATE VIEW `v_subject_info` AS SELECT
* 
FROM
	subject_info;

-- 更新视图
CREATE OR REPLACE VIEW `v_subject_info` AS SELECT
	* 
FROM
	subject_info;

-- 删除视图
DROP VIEW `v_subject_info`;

• 其它 SQL 语句中常用的关键字、运算符&函数

【领会】

关键字

运算符

函数

其他函数&Oracle / Hive row number over partition by

MySQL正则匹配

【解析&DEMO】

1.常用关键字 AND、OR、NOT、IN、BETWEEN、LIKE、IS NULL、DISTINCT等 ，DISTINCT为去重；

2.常用运算符包括比较运算符、算数运算符、逻辑运算符；

3.常用函数包括数值类型函数、字符串类型函数、日期时间函数&其他函数；

【DEMO】

-- 数值类型函数
SELECT
-- N的平方根
SQRT(121),
-- N的符号值（正数1 负数-1 0为0）
SIGN(11),
-- N的截断M位小数
TRUNCATE(1.5555, 3),
-- N的四舍五入M位小数
ROUND(1.5555, 3),
-- 大于N的最小整数
CEILING(10),
-- 小于N的最大整数
FLOOR(11),
-- 0至1的随机数
RAND(),
-- π值
PI(),
-- 取余数
MOD(8,3),
-- 以2为底8的对数
LOG(2,8),
-- N的自然对数
LN(EXP(11)),
-- 最小值
LEAST(22,22,11),
-- 最大值
GREATEST(1,2,11),
-- e的N次方
EXP(3),
-- 十六机制
HEX(17),
-- 八进制
OCT(9),
-- 二进制
BIN(3),
-- 绝对值
ABS(-100);

-- 字符串类型函数
SELECT
-- 去前后空格
TRIM(' abcd '),
-- 去后空格
RTRIM(' abcd '),
-- 去前空格
LTRIM(' abcd '),
-- 颠倒顺序
REVERSE('abcd'),
-- str重复N次
REPEAT('a',4),
-- 引用转义
QUOTE('a''b''c'),
-- substr in str 的位置
POSITION('c'IN'abcd'),
-- 长度
LENGTH('1234'),
-- 后第N位的字符
RIGHT('abcd',1),
-- 前第N位的字符
LEFT('abcd',1),
-- 转大写
UPPER('aBc'),
UCASE('aBc'),
-- 转小写
LOWER('AbC'),
LCASE('AbC'),
-- str in strlist的位置
FIND_IN_SET('c','a,b,c'),
-- 替换str中b为f
REPLACE('abcdBBBbef','b','f'),
-- 替换 ostr的第a到b位 为nstr 
INSERT('a',1,4,'bb'),
-- 用 连接strlist
CONCAT_WS(' ','aa','bb','cc'),
-- 连接strlist
CONCAT('aa','bb','cc'),
-- str的比特长度（二进制位数）
BIT_LENGTH(' '),
-- str的ASCII码值
ASCII(NULL);

-- 时间日期函数
SELECT
-- fmt格式日期
DATE_FORMAT(NOW(),'%a'),
-- fmt格式UNIX
FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()),'%a'),
-- 月
MONTHNAME(NOW()),
-- 星期
DAYNAME(NOW()),
-- 日期
DATE(NOW()),
-- 时间
TIME(NOW()),
-- 当前日期
CURDATE(),
CURRENT_DATE(),
-- 当前时间
CURTIME(),
CURRENT_TIME(),
-- 一年的第几天
DAYOFYEAR(NOW()),
-- 一月的第几天
DAYOFMONTH(NOW()),
-- 一周的第几天（范围1-7，周日为第一天）
DAYOFWEEK(NOW()),
-- 年
YEAR(NOW()),
-- 季度
QUARTER(NOW()),
-- 月
MONTH(NOW()),
-- 周
WEEK(NOW()),
-- 日
DAY(NOW()),
-- 时
HOUR(NOW()),
-- 分
MINUTE(NOW()),
-- 秒
SECOND(NOW());

-- 其他函数
-- 结果聚合
SELECT
    GROUP_CONCAT(STU.`name`)
FROM
	`student_info` STU 
GROUP BY
    STU.remarks;
-- 数据类型转换
SELECT
CAST('2019-01-01' AS DATE),
CAST(12+13 AS CHAR);

4.正则表达式匹配。

正则表达式是使用某种模式匹配一类字符串的方法，使用REGEXP关键字来匹配查询正则表达式。

正则表达式模式字符	说明
^	匹配字符串开始
$	匹配字符串结束
.	代表字符串中任意字符（包括/n,/t）
[字符集合]	匹配字符集中任意一个字节
[^字符集合]	匹配除字符集中任意一个字节
*	匹配前面的子表达式零次或多次，str* 等价于str{0,N}。
+	匹配前面的子表达式一次或多次，str+ 等价于str {1,N}。
字符{M,N}	匹配字符出现>=M次
字符{N}	匹配字符出现>=N次
S1|S2|S3	匹配S1、S2和S3中任意一个

-- 正则匹配
SELECT *
FROM student_info STU
WHERE STU.`name` 
-- 匹配学生姓名以‘张’开始的学生
-- REGEXP '^张'
-- 匹配学生姓名以‘强’结尾的学生
-- REGEXP '强$'
-- 匹配学生姓名包含‘欣’的学生
-- REGEXP '.欣|欣.'
-- REGEXP '[欣]'
-- 匹配学生姓名不叫‘李四’的学生
-- REGEXP '[^李四]'
-- 匹配学生姓名中‘李’出现0,1……次的学生
-- REGEXP '李*'
-- 匹配学生姓名中‘李’出现大于0次的学生
-- REGEXP '李+'
-- 匹配学生姓名中‘李’大于等于1的学生
-- REGEXP '李{1,2}'
-- 匹配学生姓名中‘李’大于等于2的学生
-- REGEXP '李{2}'
-- 匹配学生姓名中含‘李’或‘强’或‘张三’的学生
REGEXP '李|强|张三'
;

 备注：正则表达式模式字符解释说明中部分为自己尝试实践后重新定义，与部分参考资料不太一致，若有兴趣请参加作者的帖子讨论 https://bbs.csdn.net/topics/392551271 ，非常感谢您的指正！