MySQL基础总结(二) 查询语句

---

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]) 左边是主表

• 右外连接(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最好多条语句的查询的字段顺序一致,如果不一致,不报错,但是会按照第一条查询语句的字段顺序显示字段名