1.4.5 SQL99新特性：自然链接
- 使用关键词NATURAL JOIN
- 自然链接会帮我查询两张表中的所有相同的字段，然后进行等值链接。
SELECT employee_id, last_name ,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;
#如上还可以写成，结果相同。
SELECT employee_id, last_name ,department_name
FROM employees e NATURAL JOIN departments d
1.4.6 SQL99新特性：USING连接
- 使用关键词USING
- USING连接是在过滤条件为两个表中共有的字段时，进行的省略写法。
- 不适用自连接。
SELECT employee_id, last_name ,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
#如上还可以写成，结果相同。
SELECT employee_id, last_name ,department_name
FROM employees e JOIN departments d
USING (department_id);
#也支持多个查找
SELECT employee_id, last_name ,department_name
FROM employees e JOIN departments d
USING (department_id,manager_id);
1.5 课后练习
#1.所有有门派的人员信息
（ A、B两表共有）
SELECT *
FROM t_dept d JOIN t_emp e
ON d.id = e.deptId
#2.列出所有用户，并显示其机构信息
（A的全集）
SELECT *
FROM t_dept d RIGHT JOIN t_emp e
ON d.id = e.deptId
#3.列出所有门派
（B的全集）
SELECT* FROM t_dept;
#4.所有不入门派的人员
（A的独有）
SELECT *
FROM t_emp e LEFT JOIN t_dept d
ON e.deptID = d.ID
WHERE e.deptid is null;
select *
from t_emp e left join t_dept d
on e.deptId = d.id
where d.id is null;
#5.所有没人入的门派
（B的独有）
SELECT *
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptID = d.ID
WHERE e.deptid is null;
select *
from t_dept b left join t_emp a
on a.deptId = b.id
where a.deptId is null;
#6.列出所有人员和机构的对照关系
(AB全有)
SELECT*
FROM t_emp e LEFT JOIN t_dept d
ON e.deptId = d.id
WHERE d.id IS NULL
UNION ALL
SELECT*
FROM t_emp a RIGHT JOIN t_dept b
ON a.deptId = b.id;
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
#7.列出所有没入派的人员和没人入的门派
（A的独有+B的独有）
SELECT*
FROM t_emp e LEFT JOIN t_dept d
ON e.deptId = d.id
WHERE d.id IS NULL
UNION ALL
SELECT*
FROM t_emp e RIGHT JOIN t_dept d
ON e.deptId = d.id
WHERE e.id IS NULL
2 函数
- 函数就是JAVA中的方法。
- 不同的数据库管理系统，函数区别很大！
2.1 函数的类别
- 按照实现功能的角度：数值函数、字符串函数、日期和时间函数、流程控制函数、加密与加密函数、获取MySQL信息函数、聚合函数等。
- 将丰富的函数分为：单行函数、聚合函数（分组函数）
- 单行函数：将一行数据作为参数，返回一个结果。
- 多行函数：将多行数据作为参数，返回一个结果。
2.2 单行函数
2.2.1 数值函数
2.2.1.1 基本数值函数
| 函数 |
用法 |
| ABS(x) |
返回x的绝对值 |
| SIGN(X) |
返回X的符号。正数返回1，负数返回-1，0返回0 |
| PI() |
返回圆周率的值 |
| CEIL(x)，CEILING(x) |
返回大于或等于某个值的最小整数 |
| FLOOR(x) |
返回小于或等于某个值的最大整数 |
| LEAST(e1,e2,e3…) |
返回列表中的最小值 |
| GREATEST(e1,e2,e3…) |
返回列表中的最大值 |
| MOD(x,y) |
返回X除以Y后的余数 |
| RAND() |
返回0~1的随机值 |
| RAND(x) |
返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 |
| ROUND(x) |
返回一个对x的值进行四舍五入后，最接近于X的整数 |
| ROUND(x,y) |
返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位 |
| TRUNCATE(x,y) |
返回数字x截断为y位小数的结果 |
| SQRT(x) |
返回x的平方根。当X的值为负数时，返回NULL |
2.2.1.2 角度与弧度互相函数
| 函数 |
用法 |
| RADIANS(x) |
将角度转化为弧度，其中，参数x为角度值 |
| DEGREES(x) |
将弧度转化为角度，其中，参数x为弧度值 |
2.2.1.3 三角函数
| 函数 |
用法 |
| SIN(x) |
返回x的正弦值，其中，参数x为弧度值 |
| ASIN(x) |
返回x的反正弦值，即获取正弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| COS(x) |
返回x的余弦值，其中，参数x为弧度值 |
| ACOS(x) |
返回x的反余弦值，即获取余弦为x的值。如果x的值不在-1到1之间，则返回NULL |
| TAN(x) |
返回x的正切值，其中，参数x为弧度值 |
| ATAN(x) |
返回x的反正切值，即返回正切值为x的值 |
| ATAN2(m,n) |
返回两个参数的反正切值 |
| COT(x) |
返回x的余切值，其中，X为弧度值 |
2.2.1.4 指数和对数
| 函数 |
用法 |
| POW(x,y)，POWER(X,Y) |
返回x的y次方 |
| EXP(X) |
返回e的X次方，其中e是一个常数，2.718281828459045 |
| LN(X)，LOG(X) |
返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG10(X) |
返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL |
| LOG2(X) |
返回以2为底的X的对数，当X <= 0 时，返回NULL |
2.2.1.5 进制间的转换
| 函数 |
用法 |
| BIN(x) |
返回x的二进制编码 |
| HEX(x) |
返回x的十六进制编码 |
| OCT(x) |
返回x的八进制编码 |
| CONV(x,f1,f2) |
返回f1进制数变成f2进制数 |
2.2.2 字符串函数
| 函数 |
用法 |
| ASCII(S) |
返回字符串S中的第一个字符的ASCII码值 |
| CHAR_LENGTH(s) |
返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 |
| LENGTH(s) |
返回字符串s的字节数，和字符集有关 |
| CONCAT(s1,s2,......,sn) |
连接s1,s2,......,sn为一个字符串 |
| CONCAT_WS(x, s1,s2,......,sn) |
同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x |
| INSERT(str, idx, len, replacestr) |
将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr |
| REPLACE(str, a, b) |
用字符串b替换字符串str中所有出现的字符串a |
| UPPER(s) 或 UCASE(s) |
将字符串s的所有字母转成大写字母 |
| LOWER(s) 或LCASE(s) |
将字符串s的所有字母转成小写字母 |
| LEFT(str,n) |
返回字符串str最左边的n个字符 |
| RIGHT(str,n) |
返回字符串str最右边的n个字符 |
| LPAD(str, len, pad) |
用字符串pad对str最左边进行填充，直到str的长度为len个字符 |
| RPAD(str ,len, pad) |
用字符串pad对str最右边进行填充，直到str的长度为len个字符 |
| LTRIM(s) |
去掉字符串s左侧的空格 |
| RTRIM(s) |
去掉字符串s右侧的空格 |
| TRIM(s) |
去掉字符串s开始与结尾的空格 |
| TRIM(s1 FROM s) |
去掉字符串s开始与结尾的s1 |
| TRIM(LEADING s1 FROM s) |
去掉字符串s开始处的s1 |
| TRIM(TRAILING s1 FROM s) |
去掉字符串s结尾处的s1 |
| REPEAT(str, n) |
返回str重复n次的结果 |
| SPACE(n) |
返回n个空格 |
| STRCMP(s1,s2) |
比较字符串s1,s2的ASCII码值的大小 |
| SUBSTR(s,index,len) |
返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同 |
| LOCATE(substr,str) |
返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0 |
| ELT(m,s1,s2,…,sn) |
返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn |
| FIELD(s,s1,s2,…,sn) |
返回字符串s在字符串列表中第一次出现的位置 |
| FIND_IN_SET(s1,s2) |
返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串 |
| REVERSE(s) |
返回s反转后的字符串 |
| NULLIF(value1,value2) |
比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1 |
注意：MySQL中，字符串的位置是从1开始的。
2.2.3 日期函数
2.2.3.1 获取日期、时间
| 函数 |
用法 |
| CURDATE() ，CURRENT_DATE() |
返回当前日期，只包含年、月、日 |
| CURTIME() ， CURRENT_TIME() |
返回当前时间，只包含时、分、秒 |
| NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() |
返回当前系统日期和时间 |
| UTC_DATE() |
返回UTC（世界标准时间）日期 |
| UTC_TIME() |
返回UTC（世界标准时间）时间 |
举例：
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;
2.2.3.2 日期与时间戳转换
| 函数 |
用法 |
| UNIX_TIMESTAMP() |
以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() ->1634348884 |
| UNIX_TIMESTAMP(date) |
将时间date以UNIX时间戳的形式返回。 |
| FROM_UNIXTIME(timestamp) |
将UNIX时间戳的时间转换为普通格式的时间 |
2.2.3.3 获取月份、星期、星期数、天数等函数
| 函数 |
用法 |
| YEAR(date) / MONTH(date) / DAY(date) |
返回具体的日期值 |
| HOUR(time) / MINUTE(time) / SECOND(time) |
返回具体的时间值 |
| MONTHNAME(date) |
返回月份：January，... |
| DAYNAME(date) |
返回星期几：MONDAY，TUESDAY.....SUNDAY |
| WEEKDAY(date) |
返回周几，注意，周1是0，周2是1，。。。周日是6 |
| QUARTER(date) |
返回日期对应的季度，范围为1～4 |
| WEEK(date) ， WEEKOFYEAR(date) |
返回一年中的第几周 |
| DAYOFYEAR(date) |
返回日期是一年中的第几天 |
| DAYOFMONTH(date) |
返回日期位于所在月份的第几天 |
| DAYOFWEEK(date) |
返回周几，注意：周日是1，周一是2，。。。周六是7 |
2.2.3.4 日期的操作函数
| 函数 |
用法 |
| EXTRACT(type FROM date) |
返回指定日期中特定的部分，type指定返回的值 |
EXTRACT(type FROM date)函数中type的取值与含义：
![]()
![]()
2.2.3.5 时间和秒钟转换的函数
| 函数 |
用法 |
| TIME_TO_SEC(time) |
将 time 转化为秒并返回结果值。转化的公式为：小时*3600+分钟*60+秒 |
| SEC_TO_TIME(seconds) |
将 seconds 描述转化为包含小时、分钟和秒的时间 |
2.2.3.6 计算日期和时间的函数
第一组
| 函数 |
用法 |
| DATE_ADD(datetime, INTERVAL expr type)，ADDDATE(date,INTERVAL expr type) |
返回与给定日期时间相差INTERVAL时间段的日期时间 |
| DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type) |
返回与date相差INTERVAL时间间隔的日期 |
上述函数中type的取值：
![]()
第2组：
| 函数 |
用法 |
| ADDTIME(time1,time2) |
返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数 |
| SUBTIME(time1,time2) |
返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数 |
| DATEDIFF(date1,date2) |
返回date1 - date2的日期间隔天数 |
| TIMEDIFF(time1, time2) |
返回time1 - time2的时间间隔 |
| FROM_DAYS(N) |
返回从0000年1月1日起，N天以后的日期 |
| TO_DAYS(date) |
返回日期date距离0000年1月1日的天数 |
| LAST_DAY(date) |
返回date所在月份的最后一天的日期 |
| MAKEDATE(year,n) |
针对给定年份与所在年份中的天数返回一个日期 |
| MAKETIME(hour,minute,second) |
将给定的小时、分钟和秒组合成时间并返回 |
| PERIOD_ADD(time,n) |
返回time加上n后的时间 |
2.2.3.7 日期格式化与解析
| 函数 |
用法 |
| DATE_FORMAT(date,fmt) |
按照字符串fmt格式化日期date值 |
| TIME_FORMAT(time,fmt) |
按照字符串fmt格式化时间time值 |
| GET_FORMAT(date_type,format_type) |
返回日期字符串的显示格式 |
| STR_TO_DATE(str, fmt) |
按照字符串fmt对str进行解析，解析为一个日期 |
上述非GET_FORMAT函数中fmt参数常用的格式符：
| 格式符 |
说明 |
格式符 |
说明 |
| %Y |
4位数字表示年份 |
%y |
表示两位数字表示年份 |
| %M |
月名表示月份（January,....） |
%m |
两位数字表示月份（01,02,03。。。） |
| %b |
缩写的月名（Jan.，Feb.，....） |
%c |
数字表示月份（1,2,3,...） |
| %D |
英文后缀表示月中的天数（1st,2nd,3rd,...） |
%d |
两位数字表示月中的天数(01,02...) |
| %e |
数字形式表示月中的天数（1,2,3,4,5.....） |
|
|
| %H |
两位数字表示小数，24小时制（01,02..） |
%h和%I |
两位数字表示小时，12小时制（01,02..） |
| %k |
数字形式的小时，24小时制(1,2,3) |
%l |
数字形式表示小时，12小时制（1,2,3,4....） |
| %i |
两位数字表示分钟（00,01,02） |
%S和%s |
两位数字表示秒(00,01,02...) |
| %W |
一周中的星期名称（Sunday...） |
%a |
一周中的星期缩写（Sun.，Mon.,Tues.，..） |
| %w |
以数字表示周中的天数(0=Sunday,1=Monday....) |
|
|
| %j |
以3位数字表示年中的天数(001,002...) |
%U |
以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 |
| %u |
以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 |
|
|
| %T |
24小时制 |
%r |
12小时制 |
| %p |
AM或PM |
%% |
表示% |
GET_FORMAT函数中date_type和format_type参数取值如下：
![]()
)
浙公网安备 33010602011771号