MySQL基础总结(二)
查询语句
文章目录
DQL语言
基础查询
- 查询表中数据
select <查询列表> from <表名>;
注意
查询列表中间使用 , 隔开
- 查询常量值
select 常量
注意
常量包括数字,字符
- 查询表达式
select 表达式
注意
表达式可以理解为运算式,例如1+1
- 查询函数
select <函数名>
- 别名
select <字段> as <别名>
或者
select <字段> <别名>
注意
1. 查询时输出的表格可以区分开来
2. 查询时可以避免字段重名
3. 如果给表起了别名,则查询的字段就不能使用原来的表名去限定了
- 去重
select distinct <表名>
注意
使用distinct去重只能查询一个字段,如果对两个或多个字段,去重的是所有字段都一样的行
- 拼接
select concat(<字段列表>)
注意
1. 字段列表中间用逗号隔开
2. null和任何字段拼接,结果都是NULL,这种情况下可以使用 ifnull()函数来避免与null拼接
- 判断为空
ifnull(字段名,如果为null则显示的值)
注意
数值型和日期型的常量必须用 ‘ ’ 引起来,数字型的可用可不用
条件查询
语法
select <字段列表> from <表名> where <条件>;
- 按表达式筛选
运算符 | 含义 |
---|---|
> | 大于 |
< | 小于 |
= | 等于 |
<= | 小于等于 |
>= | 大于等于 |
!= | 不等于 |
<> | 不等于(建议使用这个) |
- 按逻辑表达式查询
运算符 | 关键字 | 含义 |
---|---|---|
&& | and | 与 |
|| | or | 或 |
! | not | 非 |
-
模糊查询
-
like
运算符 含义 % 任意多个字符,包括0个 _ 任意一个字符,表示必须有这个字符 \ 转义字符 escape ‘转义字符’ 设置转义字符,如 ‘$_xxx‘ escape ‘$’ (表示在前面的语句中用$作为转义字符,建议使用这种方式转义) -
[not] between…and…
在…和…之间,注意大小顺序不可以颠倒
- in(<字符列表>)
筛选满足指定字符任意一项,字符列表之间用 , 隔开
- is [not] null
判断为(非)空
注意 不建议使用 is null ,因为这个会触发全局查询,降低效率
- <=>
安全等于,判断是否等于,可读性较低,但是除了null之外还可以判断其他的数值
-
排序查询
语法
select <字段列表> from <表名> where <条件> order by <排序字段列表> <排序规则>
- 排序规则(默认adc)
关键字 | 含义 |
---|---|
adc | 生序 |
adsc | 降序 |
注意
如果order by后面跟多个字段,则按照字段的先后顺序进行排序
order by不论是从书写上还是运行上,都是最后一个
举例
select *,月薪*12*(1+ifnull(年终奖,0)) 年薪
from employees
order by 年薪 desc
常见函数查询
字符函数
- 查询字节长度
length(<字符串>)
- 拼接字符串
concat(<字符串 1>,<字符串 2>)
- 字符串大小写转换
upper(<字符串>)
lower(<字符串>)
- 截取字符串
substr(字符串,开始截取的位置)
substr(字符串,开始截取的位置,截取的长度)
- 返回字符串2在字符串1中首次出现的索引,如果找不到,返回0
instr(<字符串1>,<字符串2>)
- 去掉字符串前后的空格
trim(<字符串>) #去掉前后的空格
trim(<字符> from <字符串>) #去掉字符串前后指定的字符
- 设置字符串长度
lpad(<字符串>,<字符串长度n>,<字符>) #设置字符串长度为n,如果不够n个,则向左填充指定字符,如果字符串超过n,则截取前n个
rpad(<字符串>,,<字符串长度n>,,<字符>,) #设置字符串长度为n,如果不够n个,则向右填充指定字符,如果字符串超过n,则截取前n个
· replace(<字符串1>,,<字符串2>,,<字符串3>,) #将字符串1中的字符串2,替换为字符串3
数学函数
- 四舍五入
round(数值)
round(数值,小数点后保留的位数)
- 取整
ceil(数值) #向上取整(>=)
floor(数值) #向下取整(<=)
- 从指定位置截断(后面都置为0)
truncate(<数值>,<阶段的位置>)
- 取余
mod(<数值>)
注意
被除数是正数结果就是正数,被除数是负数结果就是负数
取余算法 a%b a-a/b*b
- 返回一个0~1之间的随机数
rand()
日期函数
格式 | 功能 |
---|---|
%Y | 四位年份 |
%y | 二位年份 |
%m | 月份(01,02…) |
%c | 月份(1,2…) |
%d | 日 |
%H | 小时(24小时制) |
%h | 小时(12小时制) |
%i | 分钟 |
%s | 秒 |
- 返回当前系统日期+时间
now()
- 返回当前系统日期,不包括时间
curedate()
- 返回当前的时间,不包括日期
curtime()
- 获取指定部分的年 月 日 小时 分 秒.
year month monthname day hour minute second
- 将日期格式的字符转换成指定格式的日期
str_to_date(<日期字符串>,<格式>)
举例
select * from employees where hiredata=str_to_date(‘4-3 1992’,’%c-%d %Y’);
- 将日期转换成字符
date_format(<日期>,<格式>)
- 计算2个日期之间相差的天数
datediff(<日期1>,<日期2>)
流程控制函数
- if
类似与三元运算符
语法
if(<表达式>,<表达式正确返回的值>,<表达式错误返回的值>)
-
case
- 精确匹配
语法
case 表达式 when 常量1 then 要显示的内容; #如果不想要结束,就不加分号 when 常量2 then 要显示的内容; when 常量3 then 要显示的内容; else 如果没有匹配默认显示的内容; end
- 条件匹配
case when 条件1 then 要显示的内容; #如果不想要结束,就不加分号 when 条件2 then 要显示的内容; when 条件3 then 要显示的内容; else 如果没有匹配默认显示的内容;
分组函数
语法
select <字段列表> from <表名> group by <分组的列表> order by <子句>
注意
group by 分组之后显示的每组内容默认是每个分组的第一行数据
能用分组前的条件筛选的尽量使用分组前的条件,即尽量使用where
,而不是使用having
-
having
group by 后面的条件使用having
举例 查询领导编号>102且员工的最低最低工资>5000的领导编号和其员工的最低工资
select 领导编号,min(工资) from 员工表 where 领导编号> 102 group by 领导编号 having min(工资)>5000
-
多个字段分组
group by <字段列表>
注意 使用多个字段分组,会将字段列表的每个字段合成一个字段来看,同distinct
关联查询
-
92语法的内连接
- 等值连接
语法
select 字段列表 from 表名1,表名2 where表名1.字段=表名2.字段 [and 条件] [group by 分组字段列表] [having 分组后的筛选条件] [order by 排序字段列表]
注意
1. 如果给表起了别名,则查询的字段就只能使用别名,不能使用原来的表名去限定了,select后面的字段列表前限定的表名也是
2. 92语法(即1992年的标准)只支持内连接,现在通用的是99语法,下面的内连接,外连接都是99语法
- 自连接
把一张表看成两张表,然后进行关联查询
举例 在全体员工表中,有id,员工姓名,领导id,查询每个员工的领导
select 员工表.员工姓名,领导表.领导姓名 from 表名 as 员工表,表名 as领导表 where 员工表.领导id=领导表.id;
-
内连接(inner)
语法
select 查询字段列表
from 表1 [别名]
[]inner] join 表2
on 连接条件
-
外连接(outer)
- 全外连接(full []outer]) MySQL不支持
左外连接(left [outer]) 左边是主表
- 全外连接(full []outer]) MySQL不支持
- 右外连接(right [outer]) 右边是主表
- 交叉连接
就是使用99语法的标准实现的笛卡尔积
子查询
出现在其他语句中的select语句,称为子查询或外查询
出现在外部的查询语句,称为主查询或外查询
标量子查询
概念 结果只有一行一列
举例 1 查询谁的工资比李明高
select 姓名
from 员工表
where 工资>(
select 工资
from 员工表
where 姓名=’李明’
)
举例 2 查询职位编号与员工id为141号员工相同,工资比员工id为143号高的员工姓名
select 姓名
from 员工表
where 职位编号=(
select 职位编号
from 员工表
where 员工id=141
)and 工资>(
select 工资
from 员工表
where 员工id=143
)
举例 3 查询最低工资>50号部门的最低工资的部门
select 部门名
from 员工表
group by部门名
having min(工资) >(
select min(工资)
from员工表
where 部门号=50
)
列子查询
概念 结果集只有多行一列
- 常用的多行操作符
操作符 | 含义 |
---|---|
(NOT) IN | (不)等于列表中的任意一个 |
ANY|SOME | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
行子查询(不常用)
概念 结果集只有一行多列
语法
#查询符合字段1=字段3,字段2=字段4的行
select *
from 表名
where (字段1,字段2) = (
select 字段3,字段4
from 表名
)
表子查询
概念 结果集一般为多行多列
举例 1 查询每个部门的员工个数
select 部门表.* (
select count(*)
from 员工表
where 员工表.部门id=部门表.部门id
) from 部门表;
举例 2 查询每个部门的平均工资的等级
select 部门平均工资表.*,工资等级表.等级
from (
select 部门,avg(工资)
from 员工表
group by 部门
) 部门平均工资表 #这是给from后面的表起一个别名,不然无法表示该表
inner join 工资等级表
on 部门平均工资.平均工资
between 工资等级表.等级上限 and 工资等级表.等级下限
注意 from后面的子查询语句,必须起一个别名
分页查询
概念 即将查询的结果分页处理,每一页只显示一部分内容
语法
select 查询列表
fron 表名
[join类型 join 表名2]
[on 连接条件]
[group by]
[having 分组后的筛选]
[order by 排序的字段]
limit 起始索引(从0开始),显示个数;
注意
如果从0开始,可以省略起始索引,只写显示个数
分页公式: (页数-1)*size,size
联合查询
概念 将多条查询语句的结果合并成一个结果
语法
>查询语句1
union
查询语句2
union
...... #可以联合查询多条语句
注意
1. 使用union关键字会自动去重,如果不想去重,就使用union all
2. 使用union要求多条语句的查询列数是一致的
3. 使用union最好多条语句的查询的字段顺序一致,如果不一致,不报错,但是会按照第一条查询语句的字段顺序显示字段名
group by 部门
) 部门平均工资表 #这是给from后面的表起一个别名,不然无法表示该表
inner join 工资等级表
on 部门平均工资.平均工资
between 工资等级表.等级上限 and 工资等级表.等级下限
**注意** _from后面的子查询语句,必须起一个别名_
<br />
### 分页查询
**概念** 即将查询的结果分页处理,每一页只显示一部分内容
**语法**
```mysql
select 查询列表
fron 表名
[join类型 join 表名2]
[on 连接条件]
[group by]
[having 分组后的筛选]
[order by 排序的字段]
limit 起始索引(从0开始),显示个数;
注意
如果从0开始,可以省略起始索引,只写显示个数
分页公式: (页数-1)*size,size
联合查询
概念 将多条查询语句的结果合并成一个结果
语法
>查询语句1
union
查询语句2
union
...... #可以联合查询多条语句
注意
1. 使用union关键字会自动去重,如果不想去重,就使用union all
2. 使用union要求多条语句的查询列数是一致的
3. 使用union最好多条语句的查询的字段顺序一致,如果不一致,不报错,但是会按照第一条查询语句的字段顺序显示字段名