SQL之SELECT使用篇

运算符

1. 算术运算符

运算符名称作用
+加法运算符计算两个值或表达式的和
-减法运算符计算两个值或表达式的差
*乘法运算符计算两个值或表达式的乘积
/除法运算符计算两个值或表达式的商
%求模（求余）运算符计算两个值或表达式的余数

加法与减法运算符

结论：

  • 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数
  • 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数
  • 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的
  • Mysql中+只表示数值相加，如果遇到非数值类型，先尝试转成数组，如果转失败，就按0计算。（补充：Mysql中字符串拼接要使用字符串函数CONCAT()实现）

乘法与除法运算符

  • 一个数乘以整数1和除以整数1仍得原数
  • 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等
  • 一个数除以整数后，不管是否能除尽，结果都为一个浮点数
  • 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位
  • 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同
  • 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL

2.比较运算符

运算符名称作用
=等于运算符判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0
<=>安全等于运算符安全地判断两个值、字符串或表达式是否相等
<>(!=)不等于运算符判断两个值、字符串或表达式是否不相等
<小于运算符判断前面的值、字符串或表达式是否小于后面的值、字符串或表达式
<=小于等于运算符判断前面的值、字符串或表达式是否小于等于后面的值、字符串或表达式
>大于运算符判断前面的值、字符串或表达式是否大于后面的值、字符串或表达式
>=大于等于运算符判断前面的值、字符串或表达式是否大于等于后面的值、字符串或表达式

等号运算符

  • 等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0
  • 在使用等号运算符时，遵循如下规则
    • 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等
    • 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小
    • 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较
    • 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL
  • 使用安全等于运算符时，两边的操作数的值都为NULL时，返回的结果为1而不是NULL，其他返回结果与等于运算符相同

不等于运算符

  • 不等于运算符用于判读两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符蹦年判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL

3.非符号运算符

运算符名称作用
IS NOT NULL不为空运算符判断值、字符串或表达式是否不为空
LEAST最小值运算符在多个值中返回最小值
GREATEST最大值运算符在多个值中返回最大值
BETWEEN AND两值之间的运算符判断一个值是否在两个值之间
IS NULL为空运算符判断一个值、字符串或表达式是否为空
IN属于运算符判断一个值是否为列表中的任意一个值
NOT IN不属于运算符判断一个值是否不是一个列表中的任意一个值
LIKE模糊匹配运算符判断一个值是否符合模糊匹配规则
REGEXP正则表达式运算符判断一个值是否符合正则表达式的规则
RLIKE正则表达式运算符判断一个值是否符合正则表达式的规则

最小值运算符

  • 当参数是整数或者浮点数时，LEAST将返回其中最小的值；当参数为字符串时，返回字母表中顺序最靠前的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL

最大值运算符

  • 当参数中是整数或者浮点数时，GREATEST将返回其中最大的值；当参数为字符串时，返回字母表中顺序最靠后的字符；当比较值列表中有NULL时，不能判断大小，返回值为NULL

IN运算符

  • IN运算符用于判断给定的值是否是IN列表中的一个值，如果是则返回1，否则返回0。如果给定的值为NULL，则结果为NULL。

NOT IN运算符

  • NOT IN 运算符用于判断给定的值是否不是IN列表中的一个值，如果不是IN列表中的一个值，则返回1，否则返回0

LIKE运算符

  • LIKE运算符主要用来匹配字符串，通常用于模糊匹配，如果满足条件则返回1，否则返回0.如果给定的值或者匹配条件为NULL，则返回结果为NULL

ESCAPE

  • 回避特殊符号的：使用转义符（默认是 \）

REGEXP运算符

  • REGEXP运算符用来匹配正则表达式字符串

4.逻辑运算符

  • 逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL
运算符作用
NOT 或 ！逻辑非
AND 或 &&逻辑与
OR 或 ||逻辑或
XOR逻辑异或

逻辑非运算符

  • 逻辑非(NOT或!)运算符表示当给定的值为0时或返回1；当给定的值为非0值时返回0；当给定的值为NULL时，返回NULL

逻辑与运算符

  • 逻辑与（AND或&&）运算符是当给定的值均为非0值，并且都不为NULL时，返回1；当给定的一个值或者多个值为0时则返回0；否则返回NULL

逻辑或运算符

  • 逻辑或(OR或||)运算符是当给定的值都不为NULL，并且任何一个值为非0值时，则返回1，否则返回0；当一个值为NULL，并且另一个值为非0值时，返回1，否则返回NULL；当两个值都为NULL时，返回NULL

逻辑异或运算符

  • 逻辑异或(XOR)运算符是当给定的值中任意一个值为NULL时，则返回NULL；如果两个非NULL的值都是0或者都不等于0时，则返回0；如果一个值为0，另一个值不为0时，则返回1

排序与分页

排序数据

  • 使用ORDER BY 子句排序
    • ASC （ascend）：升序
    • DESC（descend）：降序
  • ORDER BY 子句在SELECT语句的结尾

分页

  • MySQL使用LIMIT实现分页
    • 格式：LIMIT[位置偏移量,]行数
  • 分页显示公式：（当前页数-1）*每页条数，每页条数

拓展

  • 针对不同的数据库，可以统一使用rownum函数配合子查询加上where条件实现

多表查询

内连接和外连接

  • 内连接：左右表匹配的数据
  • 左外连接：左右表匹配的数据+左表没有匹配到的数据
  • 右外连接：左右表匹配的数据+右表没有匹配到的数据
  • 满外连接：左右表匹配的数据+左表没有匹配到的数据+右表没有匹配到的数据

单行函数

  1. MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。函数也可以分为单行函数、聚合函数

数值函数

函数用法
ABS(x)返回x的绝对值
SIGN(x)返回x的符号。整数
PI()返回圆周率的值
CEIL(X),CEILING(x)返回大于或等于某个值的最小整数
FLOOR(X)返回小于或等于某个值的最大整数
LEAST(e1,e2,e3...)返回列表中的最小值
GREATEST(e1,e2,e3....)返回列表中的最大值
MOD(x,y)返回X除以Y后的余数
RAND()返回0-1的随机值
ROUND(x)返回一个对x的值进行四舍五入后，最接近于x的整数
ROUND(x,y)返回一个对x的值进行四舍五入后最接近x的值，并保留到小数点后面Y位
TRUNCATE(x,y)返回数字x截断为y位小数的结果
SQRT(x)返回x的平方根。当x的值为负数时，返回NULL
POW(x,y),POWER(x,y)返回x的y次方
EXP(x)返回e的x次方，其中e是一个常数
LN(x),LOG(x)返回以e为底的x的对数，当x <= 0 时，返回的结果为NULL
LOG10(x)返回以10为底的x的对数，当x <= 0 时，返回的结果为NULL
LOG2(x)返回以2为底的x的对数，当 x <= 0 时，返回 NULL
BIN(x)返回x的二进制
HEX(x)返回x的十六进制

三角函数

函数用法
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为弧度值

字符串函数

函数用法
CHAR_LENGTH(s)返回字符串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个空格
SUBSTR(s,index,len)返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同
LOCATE(substr,str)返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substr,str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,...,sn)返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
REVERSE(s)返回s反转后的字符串
NULLIF(value1,value2)比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

日期和时间函数

函数用法
CURDATE(),CURRENT_DATE()返回当前日期，只包含年、月、日
CURTIME(),CURRENT_TIME()返回当前时间，只包含时、分、秒
NOW()返回当前系统日期和时间
UTC_DATE()返回UTC(世界标准时间)日期
UTC_TIME()返回UTC(世界标准时间)时间

日期与时间戳的转换

函数用法
UNIX_TIMESTAMP()以UNIX时间戳的形式返回当前时间。
UNIX_TIMESTAMP(date)将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp)将UNIX时间戳的时间转换为普通格式的时间

获取月份、星期、星期数、天数等函数

函数用法
YEAR(date)/MONTH(date)/DAY(date)返回具体的日期值
HOUR(time)/MINUTE(time)/SECOND返回具体的时间值
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，。。。。周六是？

特殊函数EXTRACT

函数用法
EXTRACT(type FROM date)返回指定日期中特定的部分，type指定返回的值
  • EXTRACT(type FROM date)函数中type的取值与含义：
type取值含义
MICROSECOND返回毫秒数
SECOND返回秒数
MINUTE返回分钟数
HOUR返回小时数
DAY返回天数
WEEK返回日期在一年中的第几个星期
MONTH返回日期在一年中的第几个月
QUARTER返回日期在一年中的第几个季度
YEAR返回日期的年份
SECOND_MICROSECOND返回秒和毫秒值
MINUTE_MICROSECOND返回分钟和毫秒值
MINUTE_SECOND返回分钟和秒值
HOUR_MICROSECOND返回小时和毫秒值
HOUR_SECOND返回小时和秒值
HOUR_MINUTE返回小时和分钟值
DAY_MICROSECOND返回天和毫秒值
DAY_SECOND返回天和秒值
DAY_MINUTE返回天和分钟值
DAY_HOUR返回天和小时
YEAR_MONTH返回年和月

时间和秒钟转换的函数

函数用法
TIME_TO_SEC(time)将time转化为秒并返回结果值。转化的公式为：小时*3600+分钟* 60+秒
SEC_TO_TIME(seconds)将seconds描述转化为包含小时、分钟和秒的时间

计算日期和时间的函数

函数用法
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的取值：
间隔类型含义
HOUR小时
MINUTE分钟
SECOND秒
YEAR年
MONTH月
DAY日
YEAR_MONTH年和月
DAY_HOUR日和小时
DAY_MINUTE日和分钟
DAY_SECOND日和秒
HOUR_MINUTE小时和分钟
HOUR_SECOND小时和秒
MINUTE_SECOND分钟和秒

日期的格式化与解析

函数用法
DATE_FROMAT(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参数常用的格式符：
字符说明字符说明
%Y4位数字表示年份%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 和 %l两位数字表示小时，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一周中的星期缩写(Sum,Mon,Trues,...)
%w以数字表示周中的天数(0=Sunday,1=Monday)
%j以3位数字表示年中的天数(001,002...)%U以数字表示年中的第几周,(1,2,3...)其中Sunday为周中第一天
%u以数字表示年中的第几周，(1,2,3...)其中Monday为周中第一天
%T24小时制%r12小时制
%pAM或PM%%表示%
  • GET_FORMAT函数中date_type和format_type参数取值如下:
date_typeformat_type
DATEISO%Y-%m-%d
DATETIMEISO%Y-%m-%d %H:%i:%s

加密与解密函数

函数用法
PASSWORD(str)返回字符串str的加密版本，41位长的字符串。加密结果不可逆，常用于用户的密码加密
MD5(str)返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL,则返回NULL
SHA(STR)从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL.

MySQL信息函数

函数用法
VERSION()返回当前MySQL的版本号
CONNECTION_ID()返回当前MySQL服务器的连接ID
DATABASE(),SCHEMA()返回MySQL命令行当前所在的数据库
USER()、CURRENT_USER()、SYSTEM_USER()、SESSION_USER()返回当前连接MySQL的用户名，返回结果格式为"主机名@用户名"
CHARSET(calue)返回字符串value自变量的字符集
COLLATION(value)返回字符串value的比较规则

其他函数

函数用法
FORMAT(value,n)返回对数字value进行1格式化后的结果数据。n表示 四舍五入 后保留到小数点后n位
CONV(value,from,to)将value的值进行不同进制之间的转换
INET_ATON(ipvalue)将以点分割的IP地址转化为一个数字
INET_NTOA(value)将数字形式的IP地址转化为以点分割的IP地址
BENCHMARK(n,expr)将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code)将value所使用的字符编码修改为char_code

聚合函数

  • 聚合函数作用于一组数据，并对一组数据返回一个值
函数用法
AVG()平均值
SUM()求和
MAX()最大值
MIN()最小值
COUNT()总数
group_concat()对于Group By查询结果，经常要把同一个分组中的值连接起来，返回一个字符串结果
  • group_concat函数用法
## 语法
group_concat( [distinct]要连接的字段[orderby排序字段asc/desc ] [separator '分隔符'] )
#通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

搭配GROUP BY 和 HAVING BY

  • 过滤分组：HAVING子句
    • 行已经被分组
    • 使用了聚合函数
    • 满足HAVING子句中条件的分组将被显示
    • HAVING不能单独使用，必须要跟GROUP BY 一起使用

ROLLUP函数

  • WITH ROLLUP在group分组字段的基础上再进行统计数据。作用在group by 的第一个字段

  • 用户需要对N个维度进行聚合查询操作，普通的group by语句需要N个查询和N次groupby操作。而rollup一次可以进行N次group by的结果，这样可以提高查询效率。

  • 列子

SELECT sno,cno,SUM(grade) FROM coursegrade GROUP BY cno,sno WITH ROLLUP;

##使用多个group by实现方式：
select sno,cno,sum(grade) from coursegrade
group by sno,cno
union
select sno,null,sum(grade) from coursegrade
group by sno
union
select null,null, sum(qty) from coursegrade；

窗口函数

  • MySQL 8 开始支持窗口函数。在之前的版本中已存在的大部分聚合函数在 MySQL8.0中也可以作为窗口函数来使用
函数分类函数名称描述
序号函数ROW_NUMBER()顺序排序
序号函数DENSE_RANK()并列序列，会跳过重复的序号，比如序号1、1、3
序号函数RANK()并列排序，不会跳过重复的序号，比如序号为1、1、2
分布函数CUME_DIST()累计的分布值,主要用于查询小于或等于某个值的比例
分布函数PERCENT_RANK()返回 0 到 1 之间的小数，表示某个字段值在数据分区中的排名
按照如下公式计算：
( rank-1) /(rows -1)
首尾函数FIRST_VALUE(expr)返回窗口首行记录的对应字段值
首尾函数LAST_VALUE(expr)返回窗口尾行记录的对应字段值
前后函数LEAD(expr,n)返回对应字段的后N行记录
前后函数LAG(expr,n)返回对应字段的前N行记录
其他函数NTH_VALUE(expr,n)返回第n个expr的值
其他函数NTILE(n)将分区中的有序数据分为n个桶，记录桶编号
  • LAG(expr,n)函数
    • LAG（expr,n)函数返回当前行的前n行的expr的值
  • LEAD(expr,n)函数
    • LEAD(expr,n)函数返回当前行的后n行的expr的值
  • FIREST_VALUE(expr)函数
    • FIRST_VALUE(expr)函数返回第一个expr的值
  • LAST_VALUE(expr)函数
    • LAST_VALUE(expr)函数返回最后一个expr的值
  • NTH_VALUE(expr,n)函数
    • NTH_VALUE(expr,n)函数返回第n个expr的值
  • NTILE(n)函数
    • NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号

子查询

单行子查询

操作符含义
=equal to
>greater than
>=greate than or equal to
<less than
<=less than or equal to
<>not equal to

多行子查询

操作符含义
IN等于列表中的任意一个
ANY需要和单行比较操作符一起使用，和子查询返回的某一个值比较
ALL需要和单行比较操作符一起使用，和子查询返回的所有值比较
SOME实际上是ANY的别名，作用相同，一般常使用ANY

相关子查询

  • 如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询称为关联子查询
SELECT last_name,salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP
BY department_id) e2
WHERE e1.`department_id` = e2.department_id
AND e2.dept_avg_sal < e1.`salary`;

EXISTS与NOT EXISTS关键字

  • 关联子查询通常会和EXISTS操作符一起使用，用来检查在子查询中是否存在满足条件的行
    • 如果在子查询中不存在满足条件的行：
      • 条件返回FALSE
      • 继续在子查询中查找
    • 如果在子查询中存在满足条件的行：
      • 不在子查询中继续查找
      • 条件返回TRUE
    • NOT EXISTS 关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

共用表表达式

  • 共用表表达式简称为 CTE (Common Table Expressions)
  • 公用表表达式分别为
    • 普通共用表表达式
    • 递归共用表表达式
  • 普通共用表表达式
    • 类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通共用表表达式所引用
WITH CTE名称
AS (子查询)
  • 递归公用表表达式
    • 递归公用表表达式也是一种公用表表达式，只不过，可以调用自己，它的语法结构是
    • 递归公用表表达式由2部分组成，分别是种子查询和递归查询，中间通过关键字UNION [ALL]进行连接。这里的种子查询，意思就是获得递归的初始值。
    • 这个查询只会运行一次，以创建初始数据集，之后递归查询会一直执行，直到没有任何新的查询数据产生，递归返回
WITH RECURSIVE CTE名称
AS (子查询)
  • 列子
WITH RECURSIVE cts
AS
(
SELECT employee_id,last_name,manager_id,1 AS n FROM employees WHERE employee_id = 100 --种子查询
UNION ALL
SELECT a.employee_id,a.last_name,a.manager_id,n+1 
FROM employees AS a 
JOIN cte 
ON (a.manager_id = cte.employee_id) --递归查询，找出以递归公用表达式的人为领导的人
)

SELECT employee_id,last_name FROM cte WHERE n >= 3;