MySQL 数据查询语言Data Query Language
MySQL基础知识
基础概念
主键需要满足的条件
1.任意两行都不具有相同的主键值
2.每一行都必须具有一个主键值且不能为NULL
3.主键列中的值不允许修改或更新
4.主键不能重用，如果某行从表中删除，它的主键也不能赋给以后的新行。
DB、DBMS、SQL
DB(database)数据库:存储数据的'仓库',保存了一系列有组织的数据
DBMS(Database Management System)数据库管理系统:用于创建或管理DB
SQL(Structure Query Language):结构化查询语言,专门用来与数据库通信的语言
SQL特性
- 多条SQL必须以分号结尾，多数DBMS不需要在单条SQL语句后面加分号，但有些单条SQL语句后必须加，所以建议都加上。
- 处理SQL语句时，其中所有空格都会被忽略。
- MySQL 在 Windows 环境下是大小写不敏感的
- MySQL 在 Linux 环境下是大小写敏感的
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
- 推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写
- SQL 关键字、函数名、绑定变量等都大写
SQL分类
- DDL（Data Definition Languages、数据定义语言这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
- 主要的语句关键字包括
CREATE、DROP、ALTER等。
- 主要的语句关键字包括
- DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
- 主要的语句关键字包括
INSERT、DELETE、UPDATE、SELECT等。 - SELECT是SQL语言的基础，最为重要。
- 主要的语句关键字包括
- DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
- 主要的语句关键字包括
GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。
- 主要的语句关键字包括
因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。
还有单独将COMMIT、ROLLBACK取出来称为TCL （Transaction Control Language，事务控制语言）。
注释
单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */
DQL数据查询语言(Data Query Language)
基础查询
语法:SELECT 查询列1，查询列2.. FROM 表名;
输出所有列:SELECT * FROM 表名;
# 基础查询
USE myemployees;
# 1.查询表中的单个字段
SELECT last_name FROM employees;
# 2.查询表中的多个字段
SELECT last_name,salary,email FROM employees;
# 3.查询表中的所有字段
# 方式一：使用*输出的顺序和原始表的顺序一样
SELECT * FROM employees;
# 方式二：可以自定义顺序
SELECT
`first_name`,
`last_name`,
`email`,
`phone_number`,
`manager_id`,
`salary`,
`job_id`,
`department_id`,
`hiredate`,
`commission_pct`
FROM
employees;
| 类型 | 关键字 | 案例 | 说明 |
|---|---|---|---|
| 去重 | DISTINCT | SELECT DISTINCT department_id FROM employees; | DISTINCT 需要放到所有列名的前面,不支持部分去重，对后面所有列名的组合进行去重 |
| 重命名一个列 | AS 别名 | SELECT a AS b FROM user_profile | 如果别名中包含空格或特殊的字符并区分大小写时，可以加双引号, AS可以省略但不建议 输出的是别名 |
| 着重号 | 用``框起来 | SELECT * FROM ORDER; |
我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，使用着重号框起来。 |
说明
1.mysql中，单引号和双引号都可以表示字符串，没有区别
2.mysql的别名可以不加引号，如果加引号防止有空格等识别不出来，单引号和双引号以及反引号都可以
3.mysql的字段名、表名通常不需要加任何引号，如果非要加上引号，必须加反引号
查询常数
SELECT 查询还可以对常数进行查询。就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
为什么我们还要对常数进行查询呢？
一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
比如说，我们想对 USER 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个字段固定值为"TX"
SELECT 'TX' AS corporation, NAME
FROM USER;
拼接函数concat/为空设置默认值函数 IFNULL
concat(参数1，参数2....) 拼接参数列表
IFNULL(表达式,默认值) 判断是否为NULL，如果为NULL使用默认值
# 查询员工名和姓连接成一个字段,并显示为姓名
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
SELECT CONCAT(last_name,",",first_name,",",IFNULL(commission_pct,0)) AS out_put FROM employees;
条件查询 WHERE子句
SELECT 字段1,字段2 FROM 表名 WHERE 过滤条件
WHERE子句紧跟FROM子句
分类
1.按条件表达式筛选(> < = !=/<> >= <=)
2.按逻辑表达式筛选(&&/and ||/or !/not)
3.模糊查询(like, between and, in, is null)
比较运算符
1.比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL
2.所有运算符或列值遇到null值，运算的结果都为null，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
| 操作符 | 说明 | NULL值 |
|---|---|---|
| = | 1.字符串比较每个字符的ANSI编码是否相等 2.如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。 |
不能判断NULL,意思是有NULL结果就为NULL |
| <=> | 安全等于，对NULL值处理不一样，其他返回结果与等于运算符相同 | 可以判断NULL和其他值，结果为0。如果两边都是NULL，结果为1 |
| <>/!= | 不等于 | 不能判断NULL |
| </<= | 小于与大于等于 | 不能判断NULL |
| >/>= | 大于与大于等于 | 不能判断NULL |
逻辑运算符
| 运算符 | 作用 |
|---|---|
| NOT 或 ！ | 逻辑非 否定跟着它后面的条件 |
| AND 或 && | 逻辑与 |
| OR 或 | |
| XOR | 逻辑异或(偶数个1) |
IS NULL为空运算符/IS NOT NULL不为空运算符
是否可以判断NULL
| 类型 | 普通类型的数值 | null型 |
|---|---|---|
| 赋值 =/!= | √ | × |
| 关键字 is | × | √ |
| 安全等于 <=> | √ | √ |
| IS NULL | ISNULL(参数) |
|---|---|
| IS关键字，判断是否为NULL | ISNULL(参数) 是一个函数，判断参数是否为空，返回函数结果1真0假 |
| IS NOT NULL，判断不为空 | NOT ISNULL(参数),意思是对ISNULL的 |
# 模糊查询
# 判断为空
SELECT last_name ,commission_pct FROM employees WHERE commission_pct IS NULL ;
SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct);
# 判断不为空
SELECT employee_id,commission_pct FROMemployees WHERE NOT ISNULL(commission_pct);
SELECT employee_id,commission_pct FROMemployees WHERE commission_pct IS NOT NULL;
BETWEEN AND 两值之间运算符
betwenn相当于>= and相当于<=
注意事项
1.包含临界值
2.顺序不能乱
# 查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
IN属于运算符/NOT IN不属于运算符
mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
| 1 | 0 | NULL | 1 |
+----------------------+------------+-------------------+--------------------+
IN 和 OR实现的相同功能
- IN的执行速度比OR快
- IN 后面可以跟SELECT子句
WHERE manager_id IN (100, 101);
WHERE manager_id=100 OR managerid_id=101;
like 模糊匹配运算符
语法: like 搜索模式
搜索模式:由字面值、通配符或两者组合构成的搜索条件
说明
1.一般和通配符搭配使用,
2.
| 通配符 | 说明 |
|---|---|
| % | 代表零个或多个字符 不可以匹配NULL |
| _ | 任意单个字符 |
3.ESCAPE关键字 自定义转移符
4.可以判断数值型和字符型,都写在引号里
通配符的使用注意
1.通配符搜索的处理比较慢，其他操作符能够达到相同的效果，尽量使用其他操作符
2.尽量不要放在搜索模式的开头处，搜索起来最慢
# 模糊查询
# 1.查询员工名中包含字符a(模糊条件)的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
# 2.查询员工名中第三个字符为e,第五个字符为a的员工名和工资
SELECT last_name,salary
FROM employees
WHERE last_name LIKE '__e_a%';
# 3.查询员工名中第二个字符为_的员工名
# 方法一:\转义字符
# 方法二：ESCAPE关键字 自定义转移符
SELECT last_name
FROM employees
# where last_name LIKE '_\_%'、
WHERE last_name LIKE '_S_%' ESCAPE 'S';
REGEXP 正则运算符
REGEXP运算符用来匹配字符串，语法格式为： expr REGEXP [BINARY] '匹配条件'
BINARY:MySQL中的正则表达式匹配不区分大小写，该关键字的意思是区别大小写
多数正则表达式实现使用单个反斜杠转义特殊字符，但MySQL需要两个反斜杠，MySQL自己解释一个，正则表达式库解释另一个
聚集函数
Mysql的聚合函数不支持嵌套使用
聚集函数：对一组数据进行汇总，输入的是一组数据的集合，输出的是单个值。
语法：selsect 函数名([DISTINCT] 参数) [from 表]
1.函数中的参数使用到了表中属性时需要添加form表
2.DISTINCT 可选，表示对参数去重，把不同的参数作为输入。只能作用于参数指定了列名的函数,COUNT(DISTINCT *)不可以使用。
使用案例
- 确定表中的总行数、符合条件的行数
- 获取表中行组的和
- 找出最大值、最小值、平均值
| 函数 | 说明 | 作用范围 | 是否忽略NULL |
|---|---|---|---|
| AVG() | 返回某列的平均值 | 对数值型数据使用 | √ |
| SUM() | 返回之和 | 对数值型数据使用 | √ |
| MAX() | 返回最大值 | 对任意数据类型使用，如果是文本数据，返回最后面的 | √ |
| MIN() | 返回最小值 | 对任意数据类型使用，如果是文本数据，返回最前面的 | √ |
| COUNT() | COUNT(*)返回表中记录总数， COUNT(column)对特定的列中具有值得行进行计数 COUNT(1或者其他常数值)相当于多了列，一列全部是常数值 |
对任意数据类型使用 | COUNT(*) × COUNT(column) √ COUNT(1或者其他常数值) √ |
问题：用count(*)，count(1)，count(列名)谁好呢?
其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。
Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O (n)，因为innodb真的要去数一遍。COUNT(*) = COUNT(1)>COUNT(列名)。
分组GROUP BY 和 HAVING
语法
SElECT 分组之后执行的函数[，列]
FROM table
[where condition]
[group by group_by_expression]
[order by 子句];
注意：
1.group by 的位置必须在where子句之后，order by 子句之前。
2.这里的列如果有，只能是在分组条件中出现的列，否则分组之后的行数，和这里写的列选出来的行数不一样。
单个列分组
案例:查询每个工种的最高工资 （分组条件每个工种）
SELECT MAX(salary)[,job_id] # 为了方法查看最高工资是哪个job_id可以写出来
FROM employees
GROUP BY job_id;
多个列分组
案例:查询每个部门(不为null)每个工种的员工的平均工资，并按平均工资的高低显示 (分组条件部门和工种)
多个字段之间用逗号隔开并没有顺序要求
SELECT AVG(salary) 平均工资[,department_id,job_id] # 基于可读性加上了
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
ORDER BY 平均工资 DESC;
SELECT 中出现的非组函数的字段必须声明在GROUP BY 中
# 按department_id分组，一个部门占一行数据
# 这个一个部门里面有好几种job_id，那么应该选哪一个呢？？？
# 这个是错误写法
SELECT AVG(salary) 平均工资,department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
WITH ROLLUP
作用：在所有查询出的分组记录之后，添加一条记录，该记录把分组之后的记录当作输入再执行一次聚合函数
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id WITH ROLLUP;
# job_id MAX(salary）
# ...
# NULL 把结果MAX(salary）列看成一组，找出其中最高的
# 如果是avg函数，相当于所有的平均值再求一次平局
分组过滤 HAVING
案例1：查询各个部分中最高工资比10000高的部门信息
# 错误写法
SELECT MAX(salary) ,department_id
FROM employees
WHERE MAX(salary)>10000;
GROUP BY department_id;
为什么错误？
WHERE比分组先执行
HAVING注意点
1.如果过滤条件中使用了聚合函数，必须使用HAVING
2.HAVING 必须声明在GROUP BY后面
3.开发中，使用HAVING的前提是GROUP BY使用。(可单独使用但是不推荐)
# 正确写法
SELECT MAX(salary) ,department_id
FROM employees
GROUP BY department_id;
HAVING MAX(salary)>10000;
案例2：查询部门id为10，20这2个部门中最高工资比10000高的部分信息。
# 方式1 推荐：执行效率更高
SELECT MAX(salary) ,department_id
FROM employees
WHERE department_id IN （10,20）
GROUP BY department_id;
HAVING MAX(salary)>10000;
# 方式2
SELECT MAX(salary) ,department_id
FROM employees
GROUP BY department_id;
HAVING MAX(salary)>10000 AND department_id IN （10,20）;
方式1在执行基础查询的时候只选出了department_id为10或20的数据进行分组。先筛选再分组
方式2在查询出了所有department_id的数据，最后选出了department_id为10或20的数据。先分组再筛选
WHERE与HAVING的对比
-|优点|缺点
WHERE|先筛选再关联，执行效率高|不能使用分组中的计算函数作为过滤条件，WHERE没有分组的概念
HAVING|可以使用分组中的计算函数为条件|先分组再筛选，执行效率低
排序 ORDER BY子句
使用 ORDER BY 子句排序
语法：ORDER BY xxx[ASC/DESC],yyy [ASC/DESC] ;
ASC（ascend）: 升序,默认
DESC（descend）:降序
知识点：
1.支持多个字段排序,先按第一个排，第一个相同按第二个排
2.order by 子句一般放在查询语句最后。limit子句除外
LIMIT
限制返回条数
语法
LIMIT x,y x默认0(可省略)，返回从x开始的y条数据
LIMIT x OFFSET y 从x开始取y行
说明：
LIMIT 子句必须放在整个SELECT语句的最后
案例：分页显示公式
# PageNo是当前页数，PageSize是每页的大小
LIMIT(PageNo - 1)\*PageSize,PageSize;
案例：与LIMIT结合，找最大/小
# 找到最贵的
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
多表查询
通常使用sql99标准
等值与非等值：
等值连接:连接条件 等号
非等值连接:连接条件 不是等号
自连接与非自连接：
自连接：多表是指自己与自己
使用场景：同一张表需要查询两遍
内连接与非内连接
内连接：只取交集
注意点：
1.给表起了别名之后，原名就被覆盖了，之后使用都使用别名。
2.建议:从sql优化的角度，建议多表查询时，每个字段前都指明所在的表
UNION 合并查询
把多条SELECT语句的结果组合成单个结果集。
每个表对应的列数和数据类型必须相同，并对应。
语法：
SELECT .. FROM t1
UNION [ALL]
SELECT .. FROM t2
- UNION: A ∪ B 会自动去重
- UNION ALL： A ∪ B 不会去重，显示所有
执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。
/*
案例：查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。
*/
SELECT device_id,gender,age,gpa
FROM user_profile
WHERE university = "山东大学" OR gender="male"; # 默认去重，一行又是male又是山东大学只会被匹配一次
# 不去重写法
SELECT device_id,gender,age,gpa
FROM user_profile
WHERE university = "山东大学" # 一行又是male又是山东大学这里匹配一次
UNION ALL
SELECT device_id,gender,age,gpa
FROM user_profile
WHERE gender = "male"; # 一行又是male又是山东大学这里又匹配一次
sql92语法
等值连接
说明:where后面添加连接条件
笛卡尔积:A表每一行都和B表的所有行匹配过
/*
sql92内连接写法如下，mysql不支持sql92外连接写法
案例1:查询员工名、工种号、工种名
*/
SELECT last_name,e.`job_id`,job_title
FROM employees e,jobs j
WHERE e.`job_id` = j.`job_id`;
# 有筛选条件使用and
/*
案例2.查询有奖金的员工名，部门名
*/
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`
AND commission_pct IS NOT NULL;
自连接和非自连接
自连接：多表是指自己与自己
使用场景：同一张表需要查询两遍
/*
案例1:查询员工名和上级的名称 同一张表需要寻找两遍
第一遍找员工的领导编号是是多少，第二遍找这个编号对应的人是谁
*/
SELECT
e.employee_id 员工编号,
e.last_name 员工名,
m.employee_id 领导编号,
m.last_name 领导名
FROM
employees e,employees m
WHERE m.employee_id =e.manager_id;
SQL99标准
- 内连接:用第一张的表的一行去匹配第二张表的每一行，匹配成功则留下来 --只取交集A∩B
- 外连接 一般题目有:所有
- 左外连接，除了交集还多包含了左表中不匹配的行 --AU(A∩B),左表为主表
- 右外连接，除了交集还多包含了右表中不匹配的行 --(A∩B)UB，右表为主表
- 全连接，AUB
sql99标准
select 查询列表
from 表1 别名
[连接类型] join 表2 别名
on 连接条件
| 连接类型 | 含义 | 案例 |
|---|---|---|
| 内连接 | AuB | FROM A [inner] JOIN B ON |
| 左外连接 | AU(A∩B) | FROM A LEFT JOIN B ON |
| 右外连接 | (A∩B)UB | FROM A RIGHt JOIN B ON |
| 全连接 | AUB | FROM A FULL JOIN B ON(mysql不支持) |
SQL99新特性1-自然连接NATURAL JOIN
自动查询两张表中所有相同字段，不用指出来，然后进行等值连接
# SQL92标准中
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`;
# SQL99中
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
SQL99新特性2-USING连接
USING() 在一下情况可以代替 ON
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id; //语句1
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id) # 当字段名一样的时候，可以用这句替换语句1
子查询
子查询的基本使用
- 子查询（内查询）在主查询之前一次执行完成。
- 子查询的结果被主查询（外查询）使用 。
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
子查询的分类
从子查询返回的结果的条目数
- 单行子查询，子查询值返回一行
- 多行子查询，子查询值返回多行
从内查询是否被执行多次
- 相关子查询：案例查询工资大于本部门平均工资的员工信息，子查询结果随着员工的部门改变。
- 不相关子查询:子查询的结果固定？和主查询没有相关性
子查询的位置
除了GROUP BY和LIMIT之外都可以使用
单行子查询
| 操作符 | 含义 |
|---|---|
| = | equal to |
|greater than
= |greater than or equal to
< |less than
<= |less than or equal to
<> |not equal to
子查询编写技巧：①从里往外写 ②从外往里写
案例1：查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，
manager_id，department_id
# 不成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN
(SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
# 成对比较，了解
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);
多行子查询
| 操作符 | 含义 |
|---|---|
| IN | 等于列表中的任意一个 |
| ANY | 需要和单行比较操作符一起使用，和子查询返回的某一个值比较成功 |
| ALL | 需要和单行比较操作符一起使用，和子查询返回的所有值比较成功 |
| SOME | 实际上是ANY的别名,作用相同，一般常使用ANY |
案例1：查询最低的平均工资
# MySQL中的聚合函数是不能嵌套使用
SELECT MIN(sav_sal)
FROM(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY depatment_id
) t # 必须给表起别名,不然是个无名表
# 方式2
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY depatment_id
ORDER BY avg_sal
LIMIT 1；
相关子查询
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件
关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为 关联子查询 。
相关子查询按照一行接一行的顺序执行，主查询的每一行都送给子查询并执行一次子查询。
子查询中使用主查询中的列
案例1：查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
uploading-image-506667.png
** EXISTS 与 NOT EXISTS关键字**
- 关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
- 如果在子查询中不存在满足条件的行
- 条件返回 FALSE
- 继续在子查询中查找
- 如果在子查询中存在满足条件的行：
- 不在子查询中继续查找
- 条件返回 TRUE
题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过
程中，对于自连接的处理速度要比子查询快得多。
可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表
进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。
执行顺序
SELECT子句的顺序
| 子句 | 说明 | 是否必须使用 |
|---|---|---|
| SElECT | √ | |
| FROM | 仅在表中选择数据时使用 | |
| where | 行级过滤 必须紧跟FROM |
× |
| group by | 必须在order by与where之间 | 仅在按组计算聚集时使用 |
| HAVING | 组级过滤 HAVING必须紧跟GROUP BY |
× |
| order by | 一般放最后面，有LIMIT就倒数第二 | × |
| Limit | 必须放在最后 | × |
# sql92语法
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
# sql99语法
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
执行顺序
- FROM---> ON 多表的连接条件，可以过滤部分数据 ---（LEFT/RIGHT JOIN）
- WHERE (不可以使用别名)
- GROUP BY
- HAVING (可以使用别名)
- SELECT 的字段
- DISTINCT
- ORDER BY
- LIMIT
注意:SQL对查询做了增强没有严格遵循SQL的执行顺序，where后面不能用select中的别名，但是group by ，order by都是可以的racle数据库严格遵循了SQL执行顺序在Oracle里group by是不能引用select里的别名的
单行函数
对一行进行变换，每行返回一个结果
流程控制语句
| 函数 | 用法 |
|---|---|
| IF(value,value1,value2) | 如果value的值为TRUE，返回value1，否则返回value2 |
| CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2.... ELSE END | 相当于Java的if...else if...else... |
| CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN值1 .... ELSE 值n END | 相当于Java的switch...case... |
SELECT IF(age>=25,'25岁及以上','25岁以下') AS age_cut,count(*)
FROM user_profile
group by age_cut;
/*
25岁及以上|4
25岁以下|3
*/
SELECT device_id, gender,CASE
WHEN age IS NULL THEN '其他'
WHEN age>=25 THEN '25岁及以上'
WHEN age>=20 THEN'20-24岁'
ELSE '20岁以下'
END AS age_cut
FROM user_profile
GROUP BY age_cut,gender,device_id
/*
2138|male|20-24岁
3214|male|其他
6543|female|20-24岁
2315|female|20-24岁
5432|male|25岁及以上
2131|male|25岁及以上
4321|male|25岁及以上
*/
SELECT CASE 1
WHEN 1 THEN '我是1'
WHEN 2 THEN '我是2'
ELSE '你是谁
日期函数
文本函数
MySQL中字符串的位置从1开始
| 函数 | 说明 |
|---|---|
| LTRIM(s) | 去掉字符串s左侧的空格 |
| RTRIM(s) | 去掉字符串s右侧的空格 |
| TRIM(s) | 去掉字符串s开始与结尾的空格 |
| TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
| TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
| TRIM(TRAILING s1FROM s) | 去掉字符串s结尾处的s1 |
| REPEAT(str, n) | 返回str重复n次的结果 |
| SUBSTRING_INDEX(str,delim,count) | str:要处理的字符串,delim:分隔符,count:计数。 如果count是正数，那么就是从左往右数，第N个分隔符的左边的所有内容，如果count是负数，那么就是从右往左数，第N个分隔符的右边的所有内容 |
| SUBSTRING(str,n ,m) | 返回字符串str从第n个字符截取到第m个字符； |
案例1:从180cm,75kg,25,male中提取出年龄25
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',-2),',',1) AS age # 先截取出25，male再截取出25
数学函数
| 函数 | 说明 |
|---|---|
| ROUND(x) | 四舍五入，相当与ROUND(X,0) |
| ROUND(x,y) | 四舍五入，并保留到小数点后面Y位 |
