mysql的学习笔记（阶段二）

-- 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;