半个月时间把MySQL重新巩固了一遍，梳理了一篇几万字 “超硬核” 文章！（五）

2023-02-16 170

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： 半个月时间把MySQL重新巩固了一遍，梳理了一篇几万字 “超硬核” 文章！

7. DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)

作用：DAYNAME(d)返回d对应的工作日的英文名称，如Sunday、Monday等；DAYOFWEEK(d)返回的对应一周中的索引，1表示周日、2表示周一；WEEKDAY(d)表示d对应的工作日索引，0表示周一，1表示周二

8. WEEK(d)

计算日期d是一年中的第几周

SELECT WEEK(NOW())

9. DAYOFYEAR(d)、DAYOFMONTH(d)

作用：前者返回d是一年中的第几天，后者返回d是一月中的第几天

SELECT DAYOFYEAR(NOW())

SELECT DAYOFMONTH(NOW())

10. YEAR(date)、QUARTER(date)、MINUTE(time)、SECOND(time)

作用： YEAR(date)返回指定日期对应的年份，范围是1970~2069；QUARTER(date)返回date对应一年中的季度，范围是1~4；MINUTE(time)返回time对应的分钟数，范围是0~59；SECOND(time)返回制定时间的秒值

SELECT YEAR(NOW())

SELECT QUARTER(NOW())

SELECT MINUTE(NOW())

SELECT SECOND(NOW())

11. EXTRACE(type FROM date)

作用：从日期中提取一部分，type可以是YEAR、YEAR_MONTH、DAY_HOUR、DAY_MICROSECOND、DAY_MINUTE、DAY_SECOND

12. TIME_TO_SEC(time)

作用：返回以转换为秒的time参数，转换公式为"3600小时 + 60分钟 + 秒"

SELECT TIME_TO_SEC(NOW())

13. SEC_TO_TIME()

作用：和TIME_TO_SEC(time)互为反函数，将秒值转换为时间格式

SELECT SEC_TO_TIME(530)

14. DATE_ADD(date,INTERVAL expr type)、ADD_DATE(date,INTERVAL expr type)

作用：返回将起始时间加上expr type之后的时间，比如DATE_ADD(‘2010-12-31 23:59:59’, INTERVAL 1 SECOND)表示的就是把第一个时间加1秒

15. DATE_SUB(date,INTERVAL expr type)、SUBDATE(date,INTERVAL expr type)

作用：返回将起始时间减去expr type之后的时间

16. ADDTIME(date,expr)、SUBTIME(date,expr)

作用：前者进行date的时间加操作，后者进行date的时间减操作

四. 条件判断函数

1. IF(expr,v1,v2)

作用：如果expr是TRUE则返回v1，否则返回v2

2. IFNULL(v1,v2)

作用：如果v1不为NULL，则返回v1，否则返回v2

3. CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END

作用：如果expr等于某个vn，则返回对应位置THEN后面的结果，如果与所有值都不想等，则返回ELSE后面的rn

五. 系统信息函数

1. VERSION()

作用：查看MySQL版本号

SELECT VERSION()

2. CONNECTION_ID()

作用：查看当前用户的连接数

SELECT CONNECTION_ID()

3. USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()

作用：查看当前被MySQL服务器验证的用户名和主机的组合，一般这几个函数的返回值是相同的

SELECT USER()

SELECT CURRENT_USER()

SELECT SYSTEM_USER()

SELECT SESSION_USER()

4. CHARSET(str)

作用：查看字符串str使用的字符集

SELECT CHARSET(555)

5. COLLATION()

作用：查看字符串排列方式

SELECT COLLATION('sssfddsfds')

六. 加密函数

1. PASSWORD(str)

作用：从原明文密码str计算并返回加密后的字符串密码，注意这个函数的加密是单向的（不可逆），因此不应将它应用在个人的应用程序中而应该只在MySQL服务器的鉴定系统中使用

SELECT PASSWORD('mima')

2. MD5(str)

作用：为字符串算出一个MD5 128比特校验和，改值以32位十六进制数字的二进制字符串形式返回

SELECT MD5('mima')

3. ENCODE(str, pswd_str)

作用：使用pswd_str作为密码，加密str

SELECT ENCODE('fdfdz','mima')

4. DECODE(crypt_str,pswd_str)

作用：使用pswd_str作为密码，解密加密字符串crypt_str，crypt_str是由ENCODE函数返回的字符串

SELECT DECODE('fdfdz','mima')

七. 其他函数

1. FORMAT(x,n)

作用：将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串形式返回

SELECT FORMAT(446.454,2)

2. CONV(N,from_base,to_base)

作用：不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转换为to_base进制

3. INET_ATON(expr)

作用：给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数，地址可以使4或8比特

4. INET_NTOA(expr)

作用：给定一个数字网络地址（4或8比特），返回作为字符串的该地址的点地址表示

5. BENCHMARK(count,expr)

作用：重复执行count次表达式expr，它可以用于计算MySQL处理表达式的速度，结果值通常是0（0只是表示很快，并不是没有速度）。
另一个作用是用它在MySQL客户端内部报告语句执行的时间

6. CONVERT(str USING charset)

作用：使用字符集charset表示字符串str

【SQL实战练习】

题目来自互联网，建议每道题都在本地敲一遍巩固记忆！

创建数据库

创建表（并初始化数据）

-- 学生表
CREATE TABLE `student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);
-- 课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
insert into student values('01' , '赵信' , '1990-01-01' , '男');
insert into student values('02' , '德莱厄斯' , '1990-12-21' , '男');
insert into student values('03' , '艾希' , '1990-05-20' , '男');
insert into student values('04' , '德莱文' , '1990-08-06' , '男');
insert into student values('05' , '俄洛依' , '1991-12-01' , '女');
insert into student values('06' , '光辉女郎' , '1992-03-01' , '女');
insert into student values('07' , '崔丝塔娜' , '1989-07-01' , '女');
insert into student values('08' , '安妮' , '1990-01-20' , '女');
-- 课程表测试数据
insert into course values('01' , '语文' , '02');
insert into course values('02' , '数学' , '01');
insert into course values('03' , '英语' , '03');
-- 教师表测试数据
insert into teacher values('01' , '死亡歌颂者');
insert into teacher values('02' , '流浪法师');
insert into teacher values('03' , '邪恶小法师');
-- 成绩表测试数据
insert into score values('01' , '01' , 80);
insert into score values('01' , '02' , 90);
insert into score values('01' , '03' , 99);
insert into score values('02' , '01' , 70);
insert into score values('02' , '02' , 60);
insert into score values('02' , '03' , 80);
insert into score values('03' , '01' , 80);
insert into score values('03' , '02' , 80);
insert into score values('03' , '03' , 80);
insert into score values('04' , '01' , 50);
insert into score values('04' , '02' , 30);
insert into score values('04' , '03' , 20);
insert into score values('05' , '01' , 76);
insert into score values('05' , '02' , 87);
insert into score values('06' , '01' , 31);
insert into score values('06' , '03' , 34);
insert into score values('07' , '02' , 89);
insert into score values('07' , '03' , 98);

表结构

这里建的表主要用于sql语句的练习，所以并没有遵守一些规范。下面让我们来看看相关的表结构吧

学生表（student）

s_id = 学生编号，s_name = 学生姓名，s_birth = 出生年月，s_sex = 学生性别

课程表（course）

c_id = 课程编号，c_name = 课程名称，t_id = 教师编号

教师表（teacher）

t_id = 教师编号，t_name = 教师姓名

成绩表（score）

s_id = 学生编号，c_id = 课程编号，s_score = 分数

习题

开始之前我们先来看看四张表中的数据。

1. 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT
  st.*,
  sc.s_score AS '语文',
  sc2.s_score '数学' 
FROM
  student st
  LEFT JOIN score sc ON sc.s_id = st.s_id 
  AND sc.c_id = '01'
  LEFT JOIN score sc2 ON sc2.s_id = st.s_id 
  AND sc2.c_id = '02'