携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第3天，点击查看活动详情
多表查询
笛卡尔积
select * from a, b, ... where 筛选条件
#A表有m条记录，B表有n条记录，则会产生m*n条记录的一个结果集
连接查询
又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。注意，一个表可以与自己连接，为避免笛卡尔乘积现象，必须添加有效的连接条件，使用on关键字。
语法
select 查询列表 from 表1 别名 【连接类型】 inner/left outer/right outer/full join 表2 别名 on 连接条件【where 筛选条件】【group by 分组】【having 筛选条件】【order by 排序列表】
内连接
#查询交集部分，即完全符合关联条件的数据
#不会查询值为null的数据 inner 可省略
select * from 表1 [inner] join 表2 on 表1.字段 = 表2.字段 [and 或 or]
外连接
#保证一个表的数据是完整的
#左外:left 【outer】保证join左边或上边的表数据是完整的
#右外：right 【outer】保证join右边或下边的表数据是完整的
select * from 表1 left / right [OUTER] join 表2 on 表1.字段 = 表2.字段 [and 或 or]
子查询
出现在查询语句中的select、insert、update、delete，create子句，称为子查询或内查询，外部的查询语句，称为主查询或外查询或父查询，外查询可以和内查询进行关联，子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
按结果集分类
- 标量子查询（结果集只有一行一列）配合=号使用
- 列子查询（结果集只有一列多行） 配合in来使用
- 行子查询（结果集有一行多列）配合元组使用
- 表子查询（结果集一般为多行多列） 配合join 或 exists使用
按子查询位置分类
- select后面：标量子查询（结果集只有一行一列）查询结果将做为列显示
- from后面：表子查询（结果集一般为多行多列）
- where或having后面
- 标量子查询（结果集只有一行一列）
- 列子查询（结果集只有一列多行）
- 行子查询（结果集有一行多列）
ALL操作符
- <>ALL：与任何一个都不相等，等价于 NOT IN
- >ALL :比子查询中最大的值还要大
- <ALL :比子查询中最小的值还要小
any/some操作符
- = ANY：与任何一个相等，相当于in
- >ANY : 比最低的高
- <ANY : 比最高的低
exists后面（相关子查询）
- 判断子查询是否有返回结果（不关心具体行数和内容），如果返回则为TRUE，否则为FALSE。
- 先执行外部查询
- 表子查询（结果集一般为多行多列）
联合查询
union 联合 合并：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时，将多条查询语句的结果合并成一个结果
查询语句1 union 查询语句2 union ...
注意：
- 要求多条查询语句的查询列数是一致的！
- 要求多条查询语句的查询的每一列的类型和顺序最好一致
- union关键字默认去重，如果使用union all 可以包含重复项
常见函数
字符函数
| 函数 | 描述 | 实例 |
|---|---|---|
| CHAR_LENGTH(s) | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString; |
| CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString; |
| INSERT(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 | 从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com |
| LCASE(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LCASE('RUNOOB') -- runoob |
| LOWER(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob |
| REPEAT(s,n) | 将字符串 s 重复 n 次 | 将字符串 runoob 重复三次：SELECT REPEAT('runoob',3) -- runoobrunoobrunoob |
| REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc |
| REVERSE(s) | 将字符串s的顺序反过来 | 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba |
| SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO |
| TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串 RUNOOB 的首尾空格：SELECT TRIM(' RUNOOB ') AS TrimmedString; |
| UCASE(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UCASE("runoob"); -- RUNOOB |
| UPPER(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB |
数字函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| ABS(x) | 返回 x 的绝对值 | 返回 -1 的绝对值：SELECT ABS(-1) -- 返回1 |
| AVG(expression) | 返回一个表达式的平均值，expression 是一个字段 | 返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products; |
| CEIL(x) | 返回大于或等于 x 的最小整数 | SELECT CEIL(1.5) -- 返回2 |
| COUNT(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 | 返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; |
| FLOOR(x) | 返回小于或等于 x 的最大整数 | 小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1 |
| GREATEST(expr1, expr2, expr3, ...) | 返回列表中的最大值 | 返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob |
| LEAST(expr1, expr2, expr3, ...) | 返回列表中的最小值 | 返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple |
| MAX(expression) | 返回字段 expression 中的最大值 | 返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products; |
| MIN(expression) | 返回字段 expression 中的最小值 | 返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products; |
| MOD(x,y) | 返回 x 除以 y 以后的余数 | 5 除于 2 的余数：SELECT MOD(5,2) -- 1 |
| POW(x,y) | 返回 x 的 y 次方 | 2 的 3 次方：SELECT POW(2,3) -- 8 |
| RAND() | 返回 0 到 1 的随机数 | SELECT RAND() --0.93099315644334 |
| SQRT(x) | 返回x的平方根 | 25 的平方根：SELECT SQRT(25) -- 5 |
| SUM(expression) | 返回指定字段的总和 | 计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; |
| TRUNCATE(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） | SELECT TRUNCATE(1.23456,3) -- 1.234 |
日期函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| MONTHNAME(d) | 返回日期当中的月份名称，如 November | SELECT MONTHNAME('2011-11-11 11:11:11')-> November |
| str_to_date | 将字符串转变为日期 | SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10 |
| DATE_FORMAT(d,f) | 按表达式 f的要求显示日期 d | SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')-> 2011-11-11 11:11:11 AM |
| DATEDIFF(date1,date2) | 返回两个日期之间的天数。 | SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32 |
| TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差 | mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少天 -> 89 mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月 -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年 -> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟 -> 128885 |
| ADDDATE(d,n) | 计算起始日期 d 加上 n 天的日期 | SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25 |
| ADDTIME(t,n) | n 是一个时间表达式，时间 t 加上时间表达式 n | 加 5 秒：SELECT ADDTIME('2011-11-11 11:11:11', 5); ->2011-11-11 11:11:16 (秒)添加 2 小时, 10 分钟, 5 秒:SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26 |
| curDate() | 返回当前日期 | SELECT CURDATE(); -> 2022-07-29 |
| CURTIME() | 返回当前时间 | SELECT CURTIME(); -> 23:27:43 |
| DATE() | 从日期或日期时间表达式中提取日期值 | SELECT DATE("2022-07-29");-> 2022-07-29 |
| DAY(d) | 返回日期值 d 的日期部分 | SELECT DAY("2022-07-29"); -> 29 |
| DAYNAME(d) | 返回日期 d 是星期几，如 Monday,Tuesday | SELECT DAYNAME('2011-11-11 11:11:11') ->Friday |
| DAYOFMONTH(d) | 计算日期 d 是本月的第几天 | SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11 |
| DAYOFWEEK(d) | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 | SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6 |
| DAYOFYEAR(d) | 计算日期 d 是本年的第几天 | SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315 |
| HOUR(t) | 返回 t 中的小时值 | SELECT HOUR('1:2:3') -> 1 |
| MINUTE(t) | 返回 t 中的分钟值 | SELECT MINUTE('1:2:3') -> 2 |
| MONTH(d) | 返回日期d中的月份值，1 到 12 | SELECT MONTH('2011-11-11 11:11:11') ->11 |
| NOW() | 返回当前日期和时间 | SELECT NOW() -> 2018-09-19 20:57:43 |
| SECOND(t) | 返回 t 中的秒钟值 | SELECT SECOND('1:2:3') -> 3 |
| TIME(expression) | 提取传入表达式的时间部分 | SELECT TIME("19:30:10"); -> 19:30:10 |
| YEAR(d) | 返回年份 | SELECT YEAR("2017-06-15"); -> 2017 |
高级函数
| 函数名 | 描述 | 实例 |
|---|---|---|
| BIN(x) | 返回 x 的二进制编码 | 15 的 2 进制编码:SELECT BIN(15); -- 1111 |
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END | CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 | SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END ->1 > 0 |
| CAST(x AS type) | 转换数据类型 | 字符串日期转换为日期：SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29 |
| DATABASE() | 返回当前数据库名 | SELECT DATABASE(); -> runoob |
| IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 | SELECT IF(1 > 0,'正确','错误') ->正确 |
| IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 | SELECT IFNULL(null,'Hello Word') ->Hello Word |
| ISNULL(expression) | 判断表达式是否为 NULL | SELECT ISNULL(NULL); ->1 |
| USER() | 返回当前用户 | SELECT USER(); -> guest@% |
| VERSION() | 返回数据库的版本号 | SELECT VERSION() -> 5.6.34 |
