1 排序与分页
1.1 数据排序
1.1.1 排序规则
- 使用 ORDER BY 子句排序
- ASC(ascend):升序
- DESC(descend):降序
- ORDER BY 子句在SELECT语句的结尾。
1.1.2 单列排序
SELECT last_name,job_id,department_id,hiredate
FROM employees
ORDER BY hiredate;
多列排序
SELECT last_name,department_id,salary
FROM employees
ORDER BY department_id,salary DESC;
- 可以使用不在SELECT列表中的列排序
- 在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序。如果第一列数据所有值都是唯一的,将不会对第二列进行排序
1.2 分页
1.2.1 背景
背景1:查询返回记录太对,查看起来很不方便,怎么样才能实现分页查询呢?
背景2:表里有四条数据,我们只想显示第2、3条数据该怎么办呢?
1.2.2 实现
- 分页原理
所谓分页显示,就是将数据库中的结果集,一段一段显示出来需要的条件。 - MySQL中使用 LIMIT 实现分页
- 格式:
LIMIT [位置偏移量,] 行数
第一个 “位置偏移量” 参数指示MySQL从哪一行开始显示,是一个可选参数,如果不指定位置偏移量,将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是1,以此类推);第二个参数 “行数” 指示返回的记录条数。
- 举例
--前十条记录:
SELECT * FROM 表名 LIMIT 0,10;
或
SELECT * FROM 表名 LIMIT 10;
--第11条至20条记录:
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录
SELECT * FROM 表名 LIMIT 20,10;
MySQL 8.0 中可以使用 “LIMIT 3 OFFSET 4” ,意思是获取从第五条记录开始后面的3条记录,和 “LIMIT 4,3” 返回的结构相同
- 分页显示公式:*(当前页数-1)每页条数,每页条数
SELECT * FROM table
LIMIT (pageNO - 1)*pageSize,pageSize;
- 注意:LIMIT 子句必须放在整个 SELECT 语句的最后!
2 增加和删除
2.1 DDL:操作表
操作表也就是对表进行增(Create)删(Delete)改(Update)查(Retrieve)。
2.1.1 查询表
- 查询当前数据库下所有表名称
SHOW TABLES;
- 查询表结构
DESC 表名称;
2.1.2 创建表
- 创建表
CREATE TABLE 表名(
字段名1 数据类型1,
字段名2 数据类型2,
...
字段名n 数据类型n
);
注意:最后一行末尾,不能加逗号
2.1.3 数据类型
MySQL 支持多种类型,可以分为三类:
- 数值
tinyint:小数整型,占一个字节
int:大整数类型,占四个字节
double:浮点类型
--使用格式:字段名 double(总长度,小数点后保留几位数)
- 日期
date:日期值。只包含年月日
datetime:混合日期和时间值。包含年月日时分秒
- 字符串
char:定长字符串
--优点:存储性能高
--缺点:浪费空间
varchar:变长字符串
--优点:节约空间
--缺点:存储性能低
2.1.4 删除表
- 删除表
DROP TABLE 表名;
- 删除表时判断是否存在
DROP TABLE IF EXISTS 表名;
2.1.5 修改表
- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
--将表名student修改为stu
ALTER TABLE student RENAME TO stu;
- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
--给stu表添加一列address,该字段类型是varchar(50)
ALTER TABLE stu ADD address varchar(50);
- 修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
--将stu表中的address字段的类型改为char(50)
ALTER TABLE stu MODIFY address char(50);
- 修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
--将stu表中的address字段名改为addr,类型改为varchar(50)
ALTER TABLE stu CHANGE address addr varchar(50);
- 删除列
ALTER TABLE 表名 DROP 列名;
--将stu表中的addr字段删除
ALTER TABLE stu DROP addr;
2.2 DML(Manipulation)
DML 主要是对数据进行增(insert)删(delete)改(update)操作。
2.2.1 添加数据
- 给指定列添加数据
INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...);
- 给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,...);
- 批量添加数据
INSERT INTO 表名(列名1,列名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...)...;
2.2.2 修改数据
- 修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,...[WHERE 条件];
注意:
- 修改语句中如果不加条件,则将所有数据都修改!
- 想上面的语句中的中括号,表示在写sql语句中可以省略这部分
2.2.3 删除数据
- 删除数据
DELETE FROM 表名 [WHERE 条件];
- 练习
--删除张三记录
DELETE FROM stu WHERE name = '张三';
--删除stu表中所有数据
DELETE FROM stu;
3 字符串函数
3.1 ASCII(S)
用法:返回字符串S中的第一个字符的ASCII码值
3.2 CHAR_LENGTH(S)
用法:返回字符串S的字符数。作用于CHARACTER_LENGTH(S)相同
3.3 LENGTH(S)
用法:返回字符串S的字节数,和字符集有关
3.3 CONCAT(s1,s2,…,sn)
用法:连接s1,s2,…,sn为一个字符串
3.4 CONCAT_WS(x,s1,s2,…,sn)
用法:同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上x
3.5 REPLACE(str,a,b)
用法:用字符串b替换字符串str中所有出现的字符串a
3.6 UPPER(s) 或 UCASE(s)
用法:将字符串s所有的字母转成大写字母
3.7 LOWER(s) 或 LCASE(s)
用法:将字符串s的所有字母转成小写字母
3.8 LEFT(str,n) 和 RIGHT(str,n)
LEFT(str,n):返回字符串str最左边的n个字符
RIGHT(str,n):返回字符串str最右边的n个字符
3.9 TRIM(s)
用法:去除字符串s开始与结尾的空格
4 日期和时间函数
4.1 获取日期、时间
函数 | 用法 |
---|---|
CURDATE(),CURRENT_DATE() | 返回当前日期,只包含年、月、日 |
CURTIME(),CURRENT_TIME() | 返回当前日期,只包含时、分、秒 |
NOW(),SYSDATE()/CURRENT_TIMESTAMP()/LOCALTIME()/LOCALTIMESTAMP() | 返回当前系统日期和时间 |
UTC_DATE() | 返回UTC(世界标准时间)日期 |
UTC_TIME() | 返回UTC(世界标准时间)时间 |
4.2 日期与时间戳的转换
函数 | 用法 |
---|---|
UNIX_TIMESTAMP() | 以UNIX时间戳的形式返回当前时间 |
UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换成普通格式的时间 |
4.3 获取月份、星期、星期数、天数等函数
函数 | 用法 |
---|---|
YEAR(date)/MONTH(date)/DAY(date) | 返回具体的日期值 |
HOUR(time)/MINUTE(time)/SECOND(time) | 返回具体的时间值 |
MONTHNAME(date) | 返回月份 |
DAYNAME(date) | 返回星期几 |
WEEKDAY(date) | 返回周几,注意:周一是0,周二是1,…,周日是6 |
QUARTER(date) | 返回日期对应的季度,范围是1~4 |
WEEK(date) , WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFYEAR(date) | 返回日期是一年中的第几天 |
DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
DAYOFWEEK(date) | 返回周几,注意:周日是1,周一是2,…,周六是7 |
4.4 日期的操作函数
EXTRACT() 函数用于返回日期/时间的单独部分,比如年、月、日、小时、分钟等等
EXTRACT(unit FROM date)
4.5 时间和秒钟转换的函数
函数 | 用法 |
---|---|
TIME_TO_SEC(time) | 将time转化为秒并返回结果值。转化公式为:小时 * 3600+分钟 * 60+秒 |
SEC_TO_TIME(seconds) | 将 seconds 描述转化为包含小时、分钟和秒的时间 |
4.6 计算日期和时间的函数
第一组
函数 | 用法 | 实例 |
---|---|---|
DATE_ADD(datetime, INTERVAL expr type),ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 | SELECT ADDDATE(“2017-06-15”, INTERVAL 10 DAY) |
DATE_SUB(datetime, INTERVAL expr type),SUBDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间间隔的日期 | SELECT SUBDATE(‘2011-11-11 11:11:11’, 1) |
4.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参数常用格式符:
5 流程控制函数
流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()、CASE()函数
函数 | 用法 |
---|---|
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 常量值2 THEN 值2 … [ELSE 值n] END | 相当于Java的switch…case… |
6 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取。这些函数在保证数据库安全时非常有用
函数 | 用法 |
---|---|
PASSWORD(str) | 返回字符串str的加密版本,41位长的字符串。加密结果不可逆,常用于用户的密码加密 |
MDS(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 |
可以看到,ENCODE(value,password_seed) 函数与 DECODE(value, password_seed)函数互为反函数 |
7 聚合函数
7.1 聚合函数介绍
- 什么是聚合函数
聚合函数作用于一组数据,并对一组数据返回一个值 - 聚合函数类型
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
- 聚合函数语法
SELECT [column,] group function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
- 聚合函数不能嵌套使用。比如不能出现类似 “AVG(SUM(字段名称))” 形式的调用
7.1.1 AVG 和SUM 函数
可以对数值型数据使用AVG 和 SUM 函数
SELECT AVG(salary),MAX(salary),MIN(salary),SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
7.1.2 MIN 和 MAX 函数
可以对任意数据类型的数据使用MIN 和 MAX 函数
SELECT MAX(hiredate),MIN(hiredate)
FROM employees;
7.1.3 COUNT 函数
- COUNT(*)返回表中所有记录总数,适用于任意数据类型
SELECT COUNT(*)
FROM employees;
- COUNT(expr)返回expr不为空的记录总数
SELECT COUNT(department_id)
FROM employees;
7.2 GROUP BY
7.2.1 基本使用
可以使用GROUP BY 子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
明确:WHERE一定要放在FROM后面
在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
包含在 GROUP BY 子句中的列不必包含在SELECT列表中
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
7.2.2 使用多个列分组
SELECT department_id AS '部门id',job_id, SUM(salary)
FROM employees
GROUP BY department_id,job_id;
7.2.3 GROUP BY 中使用 WITH ROLLUP
使用 WITH ROLLUP 关键字之后,在所有查询出的分组记录之后增加一条记录,该记录计算查询出的所有记录的总和,即统计记录数量
SELECT department_id,AVG(salary),COUNT(*)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
注意:
当使用ROLLUP时,不能同时使用 ORDER BY 子句进行结果排序,即 ROLLUP 和 ORDER BY是互斥的
7.3 HAVING
7.3.1 基本使用
过滤分组:HAVING子句
- 行已经被分组
- 使用了聚合函数
- 满足HAVING 子句中条件的分组将被显示
- HAVING 不能单独使用,必须要跟GROUP BY一起使用
SELECT column, group_function
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;
- **非法使用聚合函数:不能在WHERE 子句中使用聚合函数。**如下:
SELECT department_id,AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
7.3.2 WHERE 和 HAVING 的对比
区别1:WHERE 可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;HAVING 必须要与 GROUP BY 配合使用,可以把分组计算的函数和分组字段作为筛选条件。
区别2:如果需要通过连接从关联表中获取需要的数据,WHERE 是先筛选后连接,而HAVING 是先连接后筛选。
小结如下:
优点 | 缺点 | |
---|---|---|
WHERE | 先筛选数据再关联,执行效率高 | 不能使用分组中的计算函数进行筛选 |
HAVING | 可以使用分组中的计算函数 | 在最后的结果集中进行筛选,执行效率较低 |
开发中的选择:
WHERE 和 HAVING 也不是互斥的,我们可以在一个查询里面同时使用WHERE 和 HAVING。 包含分组统计函数的条件用HAVING,普通条件用WHERE。这样,我们既利用了WHERE条件的高效快速,有发挥了HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候,运行效率会有很大差别。
7.4 SELECT 的执行过程
7.4.1 查询的结构
#方式1:
SELECT ...,..., ...
FROM ..., ..., ...
WHERE 多表连接的条件
AND 不包含组函数的过滤条件
GROUP BY ..., ...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...;
#其中:
#(1) from:从哪些表中筛选
#(2)where:从哪些表中筛选条件
#(3)group by:分组依据
#(4)having:在统计结果中再次筛选
#(5)order by:排序
#(6)limit:分页
7.4.2 SELECT 执行顺序
需要记住 SELECT 查询时的两个顺序:
1. 关键字的顺序是不能颠倒的:
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
LIMIT ...;
2. SELECT 语句的执行顺序(在 MYSQL 和 Oracle 中,SELECT 执行顺序基本相同):
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT