代码语言：javascript复制
-- mysql函数，控制流函数
SELECT
IF
( 5 > 3, "大于", "小于" );-- 5 >3 显示大于，否则就是小于
SELECT
*,
IF
( score >= 85, '优秀', '及格' ) flag
FROM
score;
SELECT
ifnull( 5, 0 );-- 如果第一个值是null，那么就当作0处理
SELECT
*,
ifnull( comm, 0 ) com_flag
FROM
emp;
SELECT
isnull( 5 );
SELECT
nullif( 12, 12 ) -- 判断两个值是否相等，一样的话就会返回null，否则就会返回第一个值
-- case when语句
SELECT
CASE
5
WHEN 1 THEN
'你好' -- 看case后面的语句是否满足when中的某一个值，如果相等就输出对应的值，否则就else后面的语句
WHEN 2 THEN
'hello'
WHEN 3 THEN
'hi' ELSE '。。。'
END AS info;-- 另一种格式
SELECT
CASE
WHEN
2 > 1 THEN
"你好" -- 直接在when后面写语句，当前面的某一个语句满足的时候后面的语句就不会执行了
WHEN 2 < 1 THEN
"hello" ELSE '其他'
END AS info;-- mysql 窗口函数
-- row_number(),rank(),dense_rank()
SELECT
id,
NAME,
salary,
dense_rank() over ( PARTITION BY id ORDER BY salary DESC ) AS rn1
FROM
employee;
CREATE TABLE `emp` (
`id` INT ( 11 ) NOT NULL,
`emp_name` VARCHAR ( 255 ) DEFAULT NULL,
`dept_no` VARCHAR ( 255 ) DEFAULT NULL,
`emp_salary` INT ( 10 ) DEFAULT NULL,
`emp_hire_date` date DEFAULT NULL,
PRIMARY KEY ( `id` )
);
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 1, '张三', '0001', 5000, '2017-01-11' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 2, '李四', '0002', 1000, '2018-10-10' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 3, '王五', '0003', 2000, '2018-12-19' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 4, '赵六', '0002', 4000, '2019-09-11' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 5, '王强强', '0001', 3000, '2019-03-14' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 6, '刘阳', '0002', 6000, '2019-08-08' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 7, '周心怡', '0003', 500, '2015-06-10' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 8, '毛志宇', '0004', 4500, '2016-09-20' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 9, '刘德仁', '0002', 3500, '2016-02-25' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 10, '范德武', '0001', 3000, '2020-02-12' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 11, '梅婷婷', '0005', 8000, '2013-07-07' );
INSERT INTO `emp` ( `id`, `emp_name`, `dept_no`, `emp_salary`, `emp_hire_date` )
VALUES
( 12, '郑冰', '0005', 1000, '2014-11-17' );
SELECT
dept_no,
emp_name,
emp_salary,
emp_hire_date,
sum( emp_salary ) over ( PARTITION BY dept_no ORDER BY emp_hire_date ) AS pv1
FROM
emp;-- 求topn
-- 求出每个部门薪资排在前三位的员工-分组求topn
SELECT
*
FROM
( SELECT emp_name, id, emp_salary, dept_no, dense_rank() over ( PARTITION BY dept_no ORDER BY emp_salary DESC ) AS rn FROM emp ) t
WHERE
t.rn <= 3;-- 对所有员工进行全局排序
SELECT
emp_name,
dept_no,
emp_salary,
dense_rank() over ( ORDER BY emp_salary DESC ) AS rn
FROM
emp;-- 开窗聚合函数
SELECT
emp_name,
dept_no,
emp_salary,
sum( emp_salary ) over ( PARTITION BY dept_no ) AS c1
FROM
emp;-- rank() 函数对分区的每行分配一个排名
-- 还是按照部门进行分组，然后每组(部门号一样的按照薪资进行排序)，然后rank给一个排名号码
SELECT
emp_name,
dept_no,
emp_salary,
emp_hire_date,
rank() over ( PARTITION BY dept_no ORDER BY emp_salary DESC ) AS "部门排名"
FROM
emp;-- 头尾函数
-- first_value()
-- last_value()
-- 按照薪资排序后获取每次获取到行后到目前为止的第一个值或者最后一个值（排序后的）
SELECT
emp_name,
dept_no,
emp_salary,
emp_hire_date,
first_value( emp_salary ) over ( PARTITION BY dept_no ORDER BY emp_salary ) AS FIRST,
last_value( emp_salary ) over ( PARTITION BY dept_no ORDER BY emp_salary ) AS last
FROM
emp;-- 获取排在第几值
SELECT
emp_name,
dept_no,
emp_salary,
emp_hire_date,
nth_value( emp_salary, 1 ) over ( PARTITION BY dept_no ORDER BY emp_salary ) AS FIRST,
nth_value( emp_salary, 2 ) over ( PARTITION BY dept_no ORDER BY emp_salary ) AS SECOND
FROM
emp;-- 等级排序函数
SELECT
emp_name,
dept_no,
emp_hire_date,
emp_salary,
ntile( 3 ) over ( PARTITION BY dept_no ORDER BY emp_hire_date ) AS rn
FROM
emp;-- 取排在第几的数值
SELECT
emp_name,
dept_no,
emp_hire_date,
emp_salary,
nth_value( emp_salary, 2 ) over ( PARTITION BY dept_no ORDER BY emp_salary ) AS SECOND,
nth_value( emp_salary, 3 ) over ( PARTITION BY dept_no ORDER BY emp_salary ) AS third
FROM
emp;-- 取出每一个部门的第一组员工(相当于分组后再进行部分取样)
SELECT
*
FROM
( SELECT emp_name, dept_no, emp_salary, emp_hire_date, ntile( 3 ) over ( PARTITION BY dept_no ORDER BY emp_hire_date ) AS nt FROM emp ) t
WHERE
t.nt = 1;-- mysql 视图(view)虚拟的逻辑
-- 创建试图
CREATE
OR REPLACE VIEW view1_emp AS SELECT
emp_name,
emp_salary
FROM
emp;-- 查看表和视图
SHOW TABLES;
SHOW FULL TABLES;-- (视图不是真正的表，只是记录表的逻辑)(但是还是会基于原表的数据)
SELECT
*
FROM
view1_emp;-- 对视图进行修改
-- 其实 create or replace 本身也是修改
ALTER VIEW view1_emp AS SELECT
*
FROM
emp;-- 更新视图的时候也会对原表的数据产生变化
-- 视图
-- 1：包含聚合函数不能跟新
-- 2：包含distinct 不能跟新
-- 3：包含 having不能跟新
-- 4： 包含union 也不行
-- 5： 包含子查询不能更新
-- 6：视图包含join不可跟新
-- 一般情况下就不要去通过视图进行跟新了
-- 重命名视图
RENAME TABLE view1_emp TO myview1;-- 删除视图
DROP VIEW
IF
EXISTS myview1;
