函数：将解决某个问题的一系列命令集合封装在一起，对外仅暴露方法名，供外部调用--
、字符函数
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
网友评论