（十三）MySQL实用技术合集（更新中）

-- round(), truncate(), format()
USE crashcourse;
SELECT order_num, prod_id, quantity, item_price, 
item_price*quantity AS total_price,
round(item_price*quantity, 1) AS total_price1,
truncate(item_price*quantity, 1) AS total_price2,
format(item_price*quantity, 1) AS total_price3
FROM orderitems
WHERE order_num = 20005;

-- lag(), lead(), rank(), sum()...
SELECT sales_employee, fiscal_year, sale AS 'this_year_sale', 
lag(sale, 1, 0) over(partition by sales_employee order by fiscal_year) AS 'last_year_sale', 
lead(sale, 1, 0) over(partition by sales_employee order by fiscal_year) AS 'next_year_sale', 
Rank() Over(partition by fiscal_year order by sale desc) AS 'ranking_year', 
sum(sale) over(partition by fiscal_year) AS 'total_year', 
sum(sale) over(partition by sales_employee) AS 'total_employee'
FROM sales;

-- 创建表 department
CREATE TABLE department(
  id INT AUTO_INCREMENT PRIMARY KEY,
    name varchar(50) NOT NULL
);
-- 向 department中插入数据
INSERT INTO department(name) 
VALUES('IT'), ('Sales');
-- 创建表 employee
CREATE TABLE employee(
  id int auto_increment,
    name varchar(50) NOT NULL,
    salary int NOT NULL,
    departmentid int NOT NULL,
    PRIMARY KEY(id)
);
-- 添加外键约束
ALTER TABLE employee ADD constraint fk_employee_department FOREIGN KEY (departmentid) REFERENCES department(id);
-- 向 employee表中插入数据
INSERT INTO employee(name, salary, departmentid)
VALUES('Joe',85000, 1),
       ('Henry', 80000, 2),
       ('Sam', 60000, 2),
       ('Max', 90000, 1),
       ('Janet', 69000, 1),
       ('Randy', 85000, 1),
       ('Will', 70000, 1),
       ('Vincent', 70000, 2),
       ('Galary', 55000, 2);

-- 查询每个部门工资前三的员工 
-- 临时表创建方式1：在FROM子句中用AS关键字封装结果集为临时表，适用于简单查询 
SELECT department, employee, salary, depart_inner_ranking, depart_outer_ranking
FROM
(SELECT department.name AS 'department', 
employee.name AS 'employee', 
employee.salary AS 'salary',
Dense_Rank() Over(PARTITION BY departmentid ORDER BY salary DESC) AS 'depart_inner_ranking',
dense_rank() over(ORDER BY salary DESC) AS 'depart_outer_ranking'
FROM employee INNER JOIN department ON employee.departmentid = department.id) AS temp_table
WHERE depart_inner_ranking <= 3
ORDER BY department ASC, salary DESC;
-- 临时表创建方式2（推荐）：用WITH...AS()建立临时视图，适用于复杂查询 
WITH temp_table AS(
SELECT department.name AS 'department', 
employee.name AS 'employee', 
employee.salary AS 'salary',
Dense_Rank() Over(PARTITION BY departmentid ORDER BY salary DESC) AS 'depart_inner_ranking',
Dense_Rank() over(ORDER BY salary DESC) AS 'depart_outer_ranking'
FROM employee INNER JOIN department ON employee.departmentid = department.id
) -- 用WITH封装起来的临时表类似于临时视图 
SELECT department, employee, salary, depart_inner_ranking, depart_outer_ranking
FROM temp_table
WHERE depart_inner_ranking <= 3
ORDER BY department ASC, salary DESC;

-- CAST(value AS type), convert(value, type)
-- type支持的数据类型包括：
-- BINARY, CHAR(), DATE, TIME, DATETIME, DECIMAL
-- 整型 SIGNED, 无符号整型 UNSIGNED
SELECT CAST('2021-08-28 19:31:00' AS date) AS 'cast1',
CAST('2021-08-28 19:31:00' AS time) AS 'cast2',
CAST('-2021' AS signed) AS 'cast3',
convert('2021', unsigned) AS 'convert1',
convert('-2021', decimal(10,2)) AS 'convert2',
convert(2021, char(4)) AS 'convert3';