【MySQL 4】DQL常见函数

函数：将解决某个问题的一系列命令集合封装在一起，对外仅暴露方法名，供外部调用--

、字符函数

1、concat 拼接字符

SELECT CONCAT('hello,',first_name,last_name) 备注 FROM employees;

2、length 获取字节长度

SELECT LENGTH('hello,wmx');

3、char_length 获取字符个数

SELECT CHAR_LENGTH('hello,wmx');

4、substr截取子串

substr（str，起始索引，截取的字符长度）

注意：起始索引从1 开始，若截取长度为空表示从起始索引开始直至结束。

SELECT SUBSTR('骨灰级玩家',1,3)；--》》骨灰级

SELECT SUBSTR('骨灰级玩家',4)；--》》玩家

5、instr截取字符第一次出现的索引

SELECT INSTR('骨灰级玩家gh玩家规划gg玩家骨灰级','玩家')--》》3，因为玩家第一次出现在3索引上

6、trim去前后指定字符，字符没指定就是默认去空格

SELECT TRIM(' 玩家 ') ; --》》玩家,前后空格去掉了，中间的没有去掉

SELECT TRIM(' x' FROM 'xxxxx玩xxxx家xxxxxx ');--》》玩xxxx家

7、lpad/rpad 左填充/右填充

SELECT LPAD('玩家'，5，‘a’);--》》玩家aaa，从左往右填充a直到充满5个字符。

8、upper/lower变大写/小写

如：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和

之间用_分割。(这些条件取个别名叫 output)

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name) "OUTPUT"

9、STRCMP 比较两个字符大小

SELECT STRCMP('mxw','wmx');--》》前面比后面小，结果是-1.大是1，相等是0

10、left/right截取子串

SELECT LEFT('骨灰级'，1)；--》》骨

SELECT RIGHT('骨灰级'，2)；--》》灰级

二、数学函数

1、ABS绝对值，如-2==》》2

2、CELL向上取整，如0.09--》》1

FLOOR向下取整

3、ROUND 四舍五入

4、TRUNCATE截断

SELECT TRUNCAT(1.873,1)把小数点1位之后的数都截断。--》》1.8

5、MOD取余

三、日期函数

1、NOW，获取到当前年月日时分秒

2、CURDATE,获取到当前年月日

3、CURTIME,获取到当前时分秒

4、DATEIFF，前面日期-后面日期=相差天数

SELECT DATEIFF(‘2019-7-13’，‘1998-7-16’)；--》》7667

5、DATE_FORMAT,把日期转换为特定的格式

SELECT DATE_FORMAT（hiredate,'%Y年%m月%d日 %H小时%i分钟%s秒'）入职日期--》》2018年9月1日 00小时00分钟00秒

6、STR_TO_DATA按指定格式解析字符串为日期类型

SELECT STR_TO_DATA（'3/15 1998','%m/%d %Y'）;--》》解析为1998年3月15日

四、流程控制函数

1、IF

SELECT IF (100>9,'好','坏');

SELECT IF (commission_pct IS NULL,0,salary*12*commission_pct)奖金;-》》如果有奖金，显示奖金数，如果没有奖金，则显示0。

2、CASE函数

case [表达式]

WHEN 值1 THEN 结果1

WHEN 值2 THEN 结果2

...

ELSE 结果n

END

案例：部门编号是30，工资显示为2倍；部门编号是50，工资显示为3倍；部门编号是60，工资显示为4倍；否则不变。显示部门编号、新工资、旧工资。

SELECT department_id,salary,

CASE department_id

WHEN 30 THEN salary*2

WHEN 50 THEN salary*3

WHEN 60 THEN salary*4

ELSE salary

END newSalary FROM employees;

五、分组函数

1、sum 求和

2、avg 求平均数

3、max 求最大值

4、min 求最小值

5、count 计算非空字段值的个数，可搭配distinct实现去重统计

如：查询有员工的部门个数

select count (distinct department_id) FROM employees;

6、group by 分组查询

语法：

select 查询列表（往往是分组函数和被分组的字段）

from 表名

where 筛选条件

group by分组列表

分组前筛选，是基于原始表筛选，在group by前用where加条件

分组后，是基于分组后筛选，在group by后用having

如：查询每个工种的员工平均工资

SELECT AVE （salary）,job_id

FROM employees

GROUP BY job_id;

如：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select job_id,max(salary)

FROM employees

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary)>12000;

7、连接查询

即多表查询：主要用于“查询的字段来自于多个表”。

没有有效的连接条件的话，会产生笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行。所以需要添加有效的连接条件。

一、内连接（92、99语法）

包括：等值、非等值、自连接

SQL92语法：

select 查询列表

from 表名1 别名1，表名2 别名2，...（起别名是为了避免多表字段重名的问题，表1和表2顺序可变）

where 等值连接的连接条件

and 筛选条件

group by 分组列表

having 分组后筛选

order by 排序列表

多表等值连接的结果为多表交集部门

如:

查询员工名和部门名

SELECT e.last_name,d.'department_name'

FROM employees e,department d

WHEREH e.'department_name'=d.'department_name'

查询城市名中第二个字符为o的部门名和城市名

SELECT 'department_name'，city

FROM departments d,locations l

WHEREH d.'locations_id'=l.'locations_id'

AND city LIKE '_o%'

二、外连接（仅99语法）

包括：左连接、右连接、全外连接（mysql不支持但oracle等支持）

左右连接：选定左or右表作为主表，主表中每个结果都会显示，从表中与主表中能匹配得上的就会显示匹配项，主表落单的显示null

一般用于查询主表中有但从表中没有的记录

特点：区分主从表，两表顺序不能互换；左连接的话左边为主表，右连接的话右边为主表

语法：

select 查询列表

from 表1 别名

left/right/full outer join 表2 别名（选左连接则表1是主表，右连接则表2是主表）

on 连接条件

where 筛选条件

比如:查哪个女生没有对象。

SELECT b.*,bo.*

FROM beauty b

LEFT JOIN boys bo ON b.'boyfriend_id'=bo.'id'

WHERE bo.'id' IS NULL;

图示如下：

三、子查询：

说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询，外面的select语句称为主查询或外查询。

按子查询出现的位置可分为：

1、select后面

要求：子查询的结果为单行单列（标量子查询）

2、from后面

要求：子查询的结果可以为多行多列

3、where或having后面

要求：子查询的结果必须为单列+单行/多行子查询

4、exisits后面

要求：子查询结果必须为单列（相关子查询）

特点：

子查询一般放在小括号中

放在条件中，必须放在条件的右侧

子查询的执行优先主查询

单行子查询对应了单行操作符：<> >= <= <>

多行子查询对应了多行操作符：any/some in all

1）当行子查询，如：查询工资比公司平均工资高的员工的员工号、姓名和工资

select employee_id,last_name,salary

from employees

where salary>{

salary AVG(salary)

from employees

};

2）多行子查询，如

in:判断某字段是否在指定列表内

x in (10,30,50)

any/some:判断某字段的值是否满足其中任意一个

x>any (10,30,50) 等价于 x>min()

x=any (10,30,50) 等价于 x in (10,30,50)

all:判断某字段的值是否满足里面所有的

x>all (10,30,50) 等价于 x>min()

如：

查询其他部门工资<任意一个IT部门员工工资的结果

select employee_id,last_name,job_id,salary

from employee

where salary<any

{select distinct salary

from employee

where job_id =‘IT_PROG’

};

等价于

select employee_id,last_name,job_id,salary

from employee

where salary<

{select MIN ( salary)

from employee

where job_id =‘IT_PROG’

};

1、select后面

查询部门编号是50的员工个数

select

{select count（*）

from employees

where depariment_id=50

} as 个数；

2、放在from后面

查询每个部门的平均工资的等级

select dep_ag.department_id, dep_ag.ag, g.grade

from sal_grade as g

join

{

select AVG（salary) as ag，department_id

from employees

group by department_id

} as dep_ag ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

3、放在exists后面

查询有无名字加‘张三丰’的员工信息

select exists （exists：存在即返回数据）

{select *

from employees

where last_name ='张三丰'

} as 有无张三丰；

四、分页查询

应用场景：当页面上的数据，一页显示不全，则需要分页显示

分页查询的sql命令请求数据库服务器——服务器响应查询到的多条数据——前台页面

执行顺序+语法：

7 select 查询列表

1 from 表1 别名

2 join 表2 别名

3 on 连接条件

4 where 筛选条件

5 group by 分组

6 having 分组后筛选

8 order by 排序

9 limit 起始条目索引(从0开始，可不写默认)，想显示几行

limit-公式:假如要显示的页数是page,每页显示的条目数是size

select *

from employees

limit (page-1)*size,size;

理解：

page size=10

1 limit 0 ，10

2 limit 10 ，10

3 limit 20 ，10

.......

五、联合查询

当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询

语法：

select 查询列表 from 表1 where 筛选条件

union

select 查询列表 from 表1 where 筛选条件

学习资料来源：李玉婷；链接：https://www.bilibili.com/video/av49181542?p=3

图来自：https://blog.csdn.net/qq_43952245/article/details/90485688