1. 基础查询
1.1 语法
select 查询列表 from 表名;
1.2 特点
- 查询列表可以是字段、常量、表达式、函数,也可以是多个
- 查询结果是一个虚拟表
1.3 案列
-
查询单个字段
select 字段名 from 表名; -
查询多个字段
select 字段名,字段名 from 表名; -
查询所有字段
select * from 表名 -
查询常量
select 常量值;
注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要 -
查询函数
select 函数名(实参列表); -
查询表达式
select 100/1234; -
起别名
①as
②空格 -
去重
select distinct 字段名 from 表名; -
加法 +
作用:做加法运算
select 数值+数值; 直接运算
select 字符+数值;先试图将字符转换成数值,如果转换成功,则继续运算;
否则转换成0,再做运算
select null+值;结果都为null
2. 条件查询
2.1 语法
select 查询列表 from 表名 where 筛选条件
2.2 筛选条件的分类
简单条件运算符:> < = != <> >= <=
2.3 逻辑运算符
作用:用于连接条件表达式
&& || !
and or not
&&和and:两个条件都为true,结果为true,反之为false
||或or: 只要有一个条件为true,结果为true,反之为false
!或not: 如果连接的条件本身为false,结果为true,反之为false
2.4 模糊查询
like: 一般搭配通配符使用,可以判断字符型或数值型
通配符:%任意多个字符,_任意单个字符
between and
in
is null /is not null:用于判断null值
is null PK <=>
普通类型的数值 null值 可读性
is null × √ √
<=> √ √ ×
3. 排序查询
3.1 语法
select 查询列表 from 表 where 筛选条件 order by 排序列表 【asc}desc】
3.2 特点
- asc :升序,如果不写默认升序 desc:降序
- 排序列表 支持 单个字段、多个字段、函数、表达式、别名
- order by的位置一般放在查询语句的最后(除limit语句之外)
4. 常见函数
4.1 概述
功能:类似于java中的方法
好处:提高重用性和隐藏实现细节
调用:select 函数名(实参列表);
4.2 单行函数
4.2.1 字符函数
- concat:连接
- substr:截取子串
- upper:变大写
- lower:变小写
- replace:替换
- length:获取字节长度
- trim:去前后空格
- lpad:左填充
- rpad:右填充
- instr: 获取子串第一次出现的索引
4.2.2 数学函数
- ceil:向上取整
- round:四舍五入
- mod:取模
- floor:向下取整
- truncate:截断
- rand:获取随机数,返回0-1之间的小数
4.2.3 日期函数
- now:返回当前日期+时间
- year: 返回年
- month:返回月
- day: 返回日
- date_format: 将日期转换成字符
- curdate: 返回当前日期
- str_to_date: 将字符转换成日期
- curtime: 返回当前时间
- hour: 小时
- minute: 分钟
- second: 秒
- datediff: 返回两个日期相差的天数
- monthname: 以英文形式返回月
格式 描述
- %a 缩写星期名
- %b 缩写月名
- %c 月,数值
- %D 带有英文前缀的月中的天
- %d 月的天,数值(00-31)
- %e 月的天,数值(0-31)
- %f 微秒
- %H 小时 (00-23)
- %h 小时 (01-12)
- %I 小时 (01-12)
- %i 分钟,数值(00-59)
- %j 年的天 (001-366)
- %k 小时 (0-23)
- %l 小时 (1-12)
- %M 月名
- %m 月,数值(00-12)
- %p AM 或 PM
- %r 时间,12-小时(hh:mm:ss AM 或 PM)
- %S 秒(00-59)
- %s 秒(00-59)
- %T 时间, 24-小时 (hh:mm:ss)
- %U 周 (00-53) 星期日是一周的第一天
- %u 周 (00-53) 星期一是一周的第一天
- %V 周 (01-53) 星期日是一周的第一天,与 %X 使用
- %v 周 (01-53) 星期一是一周的第一天,与 %x 使用
- %W 星期名
- %w 周的天 (0=星期日, 6=星期六)
- %X 年,其中的星期日是周的第一天,4 位,与 %V 使用
- %x 年,其中的星期一是周的第一天,4 位,与 %v 使用
- %Y 年,4 位
- %y 年,2 位
4.2.4 其他函数
- version 当前数据库服务器的版本
- database 当前打开的数据库
- user当前用户
- password(‘字符’):返回该字符的密码形式
- md5(‘字符’):返回该字符的md5加密形式
4.2.5 流程控制函数
①if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2
②case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
…
else 值n
end
③case情况2
case
when 条件1 then 值1
when 条件2 then 值2
…
else 值n
end
4.2.6 分组函数
分类
- max 最大值
- min 最小值
- sum 和
- avg 平均值
- count 计算个数
特点
-
①语法
select max(字段) from 表名; -
②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型 -
③以上分组函数都忽略null
-
④都可以搭配distinct使用,实现去重的统计
select sum(distinct 字段) from 表; -
⑤count函数
count(字段):统计该字段非空值的个数
count():统计结果集的行数
案例:查询每个部门的员工个数
1 xx 10
2 dd 20
3 mm 20
4 aa 40
5 hh 40
count(1):统计结果集的行数
效率上:
MyISAM存储引擎,count()最高
InnoDB存储引擎,count(*)和count(1)效率>count(字段) -
⑥ 和分组函数一同查询的字段,要求是group by后出现的字段
4.2.7 其他函数
-
concat函数
功能:拼接字符
select concat(字符1,字符2,字符3,…); -
ifnull函数
功能:判断某字段或表达式是否为null,如果为null 返回指定的值,否则返回原本的值
select ifnull(commission_pct,0) from employees; -
isnull函数
功能:判断某字段或表达式是否为null,如果是,则返回1,否则返回0
5. 分组查询
5.1 语法
select 分组函数,分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
5.2 特点
- 和分组函数一同查询的字段必须是group by后出现的字段
- 筛选分为两类:分组前筛选和分组后筛选
分组前筛选 where 原始表 group by的前面
分组后筛选 having 分组后的结果 group by 的后面
一般来讲,能用分组前筛选的,尽量使用分组前筛选,提高效率
6. 连接查询
6.1含义
当查询中涉及到了多个表的字段,需要使用多表连接
select 字段1,字段2 from 表1,表2,…;
笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接
如何解决:添加有效的连接条件
6.1 分类
按年代分类:
-
sql92:
- 等值
- 非等值
- 自连接
- 也支持一部分外连接(用于oracle、sqlserver,mysql不支持)
-
sql99【推荐使用】
- 内连接
- 等值
- 非等值
- 自连接
- 外连接
- 左外
- 右外
- 全外(mysql不支持)
- 交叉连接
-
连接查询的分类:年代分为sql192标准仅仅支持内连接,sql199标准支持内连接,左外连接,右外连接,交叉连接
-
按功能分为内连接,外连接,交叉连接
内连接 只包含AB交集部分
6.3 举例
内连接
select * from A inner join B on A.key=B.key
左连接 只包含A部分
select * from A left join B on A.key=B.key
右连接 只包含B部分
select * from A right join B on A.key=B.key
左外连接 只包含A部分且不包含AB交集部分
select * from A left join B on A.key=B.key where B.key is null
右外连接 只包含B部分且不包含AB交集部分
select * from A rigth join B on A.key=B.key where A.key is null
全连接 AB都包含
select * from A full join B on A.key=B.key
全外连接 AB都包含且不包含AB交集
select * from A full join B on A.key=B.key where A.key is null or B.key is null
脑图如下:
7. 子查询
7.1 含义
出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询
按子查询出现的位置:
- select后面:
仅仅支持标量子查询 - from后面:
支持表子查询 - where或having后面:
标量子查询(单行)
列子查询 (多行)
行子查询 - exists后面(相关子查询):
表子查询
按结果集的行列数不同:
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)
8. 分页查询
当要查询的条目数太多,一页显示不全
8.1 语法
select 查询列表 from 表 limit 【offset,】size;
注意:
- offset代表的是起始的条目索引,默认从0开始
- size代表的是显示的条目数
8.2 案例
#案例1:查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
#案例2:查询第11条——第25条
SELECT * FROM employees LIMIT 10,15;
#案例3:有奖金的员工信息,并且工资较高的前10名显示出来
SELECT
*
FROM
employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;
9. 联合查询
9.1 含义
union:合并、联合,将多次查询结果合并成一个结果
9.2 语法
查询语句1
union 【all】
查询语句2
union 【all】
9.3 意义
- 将一条比较复杂的查询语句拆分成多条语句
- 适用于查询多个表的时候,查询的列基本是一致
9.4 特点
- 要求多条查询语句的查询列数必须一致
- 要求多条查询语句的查询的各列类型、顺序最好一致
- union 去重,union all包含重复项
10. 关键字执行顺序
mysql执行sql的顺序从 From 开始,以下是执行的顺序流程:
FROM
table1 left join table2 on 将table1和table2中的数据产生笛卡尔积,生成Temp1JOIN
table2 所以先是确定表,再确定关联条件ON
table1.column = table2.columu 确定表的绑定条件 由Temp1产生中间表Temp2WHERE
对中间表Temp2产生的结果进行过滤 产生中间表Temp3GROUP BY
对中间表Temp3进行分组,产生中间表Temp4HAVING
对分组后的记录进行聚合 产生中间表Temp5
对中间表Temp5进行列筛选,产生中间表 Temp6SELECT
:执行SELECT操作,选择指定的列;DISTINC
SELECT T 对中间表 Temp6进行去重,产生中间表 Temp7ORDER BY
对Temp7中的数据进行排序,产生中间表Temp8LIMIT
对中间表Temp8进行分页,产生中间表Temp9