1. DQL数据查询语言
1.1 基本查询结构
SELECT [DISTINCT 去重] 列名1 AS 别名1, 列名2 AS 别名2
FROM 表名1 AS 表别名1 [INNER | LEFT(OUTER) | RIGHT(OUTER)] JOIN 表名2 AS 表别名2
ON 关联条件
WHERE 过滤条件
GROUP BY 列名
ORDER BY 列名字(默认升序ASC) DESC
LIMIT [位置偏移量,] 行数
1.2 运算符
1.2.1算术运算符：
- 加（+）、减（-）、乘（*）、除（/）和取模（%）运算
- 在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数
CONCAT()实现） - 在MySQL中，一个数除以0为NULL。
1.2.2 比较运算符：
-
如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
-
如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
-
安全等于运算符（<=>）与等于运算符（=）的作用是相似的， 唯一的区别 是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。
-
不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。
-
LIKE运算符：LIKE运算符通常使用如下通配符
-
“%”：匹配0个或多个字符。
-
“_”：只能匹配一个字符。
-
ESCAPE：如果使用\表示转义，要省略ESCAPE。如果不是\，则要加上ESCAPE。
SELECT job_id FROM jobs WHERE job_id LIKE ‘IT$_%‘ escape ‘$‘;
-
-
REGEXP运算符：
expr REGEXP 匹配条件
1.2.3 逻辑运算符
- 优先级：OR可以和AND一起使用，但是在使用时要注意两者的优先级，由于AND的优先级高于OR，因此先对AND两边的操作数进行操作，再与OR中的操作数结合。
1.2.4 位运算符
1.3 多表查询
1.3.1 外连接
-
如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表
如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。
-
合并查询结果：
-
UNION操作符：合并并去重
-
UNION ALL操作符：合并但不去重
-
-
7种SQL JOINS的实现
#中图：内连接 A∩B 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 LEFT JOIN departments d ON e.`department_id` = d.`department_id`;#右上图：右外连接 SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;#左中图：A - A∩B SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL#右中图：B-A∩B SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL#左下图：满外连接 # 左中图 + 右上图 A∪B SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL UNION ALL #没有去重操作，效率高 SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id`;#右下图 #左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B） SELECT employee_id,last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id` WHERE d.`department_id` IS NULL UNION ALL #没有去重操作，效率高 SELECT employee_id,last_name,department_name FROM employees e RIGHT JOIN departments d ON e.`department_id` = d.`department_id` WHERE e.`department_id` IS NULL
1.4 单行函数
1.4.1 数值函数
-
基本函数
-
角度与弧度互换函数
-
三角函数
-
指数与对数
-
进制间的转换
1.4.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开始的。
1.4.3 日期与时间函数
-
获取日期、时间
函数 用法 CURDATE() ，CURRENT_DATE() 返回当前日期，只包含年、月、日 CURTIME() ， CURRENT_TIME() 返回当前时间，只包含时、分、秒 NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() 返回当前系统日期和时间 UTC_DATE() 返回UTC（世界标准时间）日期 UTC_TIME() 返回UTC（世界标准时间）时间 -
日期与时间戳的转换
函数 用法 UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884 UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回。 FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间 -
获取月份、星期、星期数、天数等函数
函数 用法 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 -
日期的操作函数
函数 用法 EXTRACT(type FROM date) 返回指定日期中特定的部分，type指定返回的值 EXTRACT(type FROM date)函数中type的取值与含义：
-
时间和秒钟转换的函数
函数 用法 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的取值：
第二组：
函数 用法 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后的时间 -
日期的格式化与解析
格式化：DATE对象===》字符串
解析：字符串===》DATE对象
函数 用法 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参数常用的格式符：
GET_FORMAT函数中date_type和format_type参数取值如下：
1.4.4 流程控制函数
| 函数 | 用法 |
|---|---|
| IF(value,value1,value2) | 如果value的值为TRUE，返回value1，否则返回value2 |
| IFNULL(value1, value2) | 如果value1不为NULL，返回value1，否则返回value2 |
| CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END | 相当于Java的if…else if…else… |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END | 相当于Java的switch…case… |
1.4.5 加密与解密函数
| 函数 | 用法 |
|---|---|
| PASSWORD(str) | 返回字符串str的加密版本，41位长的字符串。加密结果 不可 逆 ，常用于用户的密码加密 |
| MD5(str) | 返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL |
| SHA(str) | 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 SHA加密算法比MD5更加安全 。 |
| ENCODE(value,password_seed) | 返回使用password_seed作为加密密码加密value |
| DECODE(value,password_seed) | 返回使用password_seed作为加密密码解密value |
1.4.6 MySQL信息函数
| 函数 | 用法 |
|---|---|
| VERSION() | 返回当前MySQL的版本号 |
| CONNECTION_ID() | 返回当前MySQL服务器的连接数 |
| DATABASE()，SCHEMA() | 返回MySQL命令行当前所在的数据库 |
| USER()，CURRENT_USER()、SYSTEM_USER()，SESSION_USER() | 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名” |
| CHARSET(value) | 返回字符串value自变量的字符集 |
| COLLATION(value) | 返回字符串value的比较规则 |
1.4.7 其他函数
| 函数 | 用法 |
|---|---|
| FORMAT(value,n) | 返回对数字value进行格式化后的结果数据。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 |
1.5 聚合函数
1.5.1 简单函数
- AVG和SUM函数
- MIN和MAX函数
- COUNT函数
1.5.2 GROUP BY
-
单列分组
SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ; -
多列分组
SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ; -
GROUP BY中使用WITH ROLLUP
SELECT department_id,AVG(salary) FROM employees WHERE department_id > 80 GROUP BY department_id WITH ROLLUP;注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。
1.5.3 HAVING
-
与WHERE类似，对分组结果进行筛选
-
使用注意
- 行已经被分组。
- 使用了聚合函数
- 满足HAVING 子句中条件的分组将被显示
- HAVING 不能单独使用，必须要跟 GROUP BY 一起使用
-
与WHERE对比
- 区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件；HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
- 区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。
1.5.4 SELECT的执行过程
-
语法结构
-
执行顺序
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
689
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
