[MySQL]4.进阶操作

1 数据导入导出

1. 查看secure-file-priv

   SHOW VARIABLES LIKE '%secure%';
   

2. 二维表导出到csv

   SELECT * 
   FROM Person
   INTO OUTFILE '/path/to/csv'
   FIELDS TERMINATED BY ','           --字段以','分隔
   OPTIONALLY ENCLOSED BY ""          --字段用""括起
   ESCAPED BY ""                      --字段中使用的转义符为"
   LINES TERMINATED BY '\r\n';        --行以\r\n结束
   

3. 导入csv

   -- 需要对应字段的二维表存在
   LOAD DATA INFILE '/path/to/csv'
       INTO TABLE Person
       FIELDS TERMINATED BY ','
       OPTIONALLY ENCLOSED BY ""
       ESCAPED BY ""
       LINES TERMINATED BY '\r\n';
   

2 查找各部门工资最高的员工

1. 建表和插入数据

   CREATE TABLE Employee(
       Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       Name VARCHAR(10) NOT NULL,
       Salary INT NOT NULL,
       DepartmentId INT NOT NULL
   );
   
   CREATE TABLE Department(
       Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       Name VARCHAR(30) NOT NULL
   );
   
   INSERT INTO Employee
       VALUES  (1, 'Joe', 70000, 1),
               (2, 'Henry', 80000, 2),
               (3, 'Sam', 60000, 2),
               (4, 'Max', 90000, 1);
   
   INSERT INTO Department
       VALUES  (1, 'IT'),
               (2, 'Sales');
   

2. Query

   -- 去掉 sql_mode 的 ONLY_FULL_GROUP_BY 
   set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
   
   SELECT d.Name Department, e.Name Employee ,salary
   FROM Employee e 
   	JOIN Department d
   	ON e.DepartmentId = d.id
   WHERE salary IN
       (SELECT MAX(salary)
       FROM Employee
       GROUP BY DepartmentId);
   

3 换座位

1. 建表并插入数据

   CREATE TABLE seat(
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       student VARCHAR(40) NOT NULL
   );
   
   INSERT INTO seat
       VALUES  (1, 'Abbot'),
               (2, 'Doris'),
               (3, 'Emerson'),
               (4, 'Green'),
               (5, 'Jeams');
   

2. Query

   SELECT
   	(CASE
   	WHEN MOD(id, 2) = 1 AND id != (SELECT MAX(id) FROM seat) THEN id + 1
   	WHEN MOD(id, 2) = 0 THEN id -1
   	ELSE id
   	END)  id, student
   FROM seat 
   ORDER BY id;
   

4 分数排名

1. 建表并插入数据

   CREATE TABLE score(
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       Score FLOAT NOT NULL
   );
   
   INSERT INTO score
       VALUES  (1, 3.50),
               (2, 3.65),
               (3, 4.00),
               (4, 3.85),
               (5, 4.00),
               (6, 3.65);
   

2. Query

   -- 自定义rank函数, 降序后, 将Score与上一个Score对比, 若不同则Rank+1, 不降序Rank就失效了
   SELECT
     Score,
     @rank := @rank + (@prev <> (@prev := Score))  Rank
   FROM
     score,
     (SELECT @rank := 0, @prev := -1) init
   ORDER BY Score DESC;
   
   -- 提前计算出了每个无重复Score的Rank, 即使不降序每个Score的Rank依然准确, 比方法一要耗时
   SELECT
     Score,
     (SELECT COUNT(DISTINCT Score) FROM score WHERE Score >= s.Score) Rank
   FROM score s
   ORDER BY Score desc;
   

5 行程和用户

1. 建表并插入数据

   CREATE TABLE trips(
       Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       Client_Id INT NOT NULL,
       Driver_Id INT NOT NULL,
       City_Id INT NOT NULL,
       Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client') NOT NULL,
       Request_at DATE DEFAULT NULL
   );
   
   CREATE TABLE Users(
       Users_Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       Banned VARCHAR(10) NOT NULL,
       Role ENUM('client', 'driver', 'partnet') NOT NULL
   );
   
   INSERT INTO trips
        VALUES  (1,1,10,1,'completed','2013-10-01'),
                (2,2,11,1, 'cancelled_by_driver','2013-10-01'),
                (3,3,12,6,'completed','2013-10-01'),
                (4,4,13,6,'cancelled_by_client','2013-10-01'),
                (5,1,10,1,'completed','2013-10-02'),
                (6,2,11,6,'completed','2013-10-02'),
                (7,3,12,6,'completed','2013-10-02'),
                (8,2,12,12,'completed','2013-10-03'),
                (9,3,10,12,'completed','2013-10-03'),
                (10,4,13,12, 'cancelled_by_driver','2013-10-03');
   
   INSERT INTO Users
       VALUES  (1, 'No', 'client'),
               (2, 'Yes', 'client'),
               (3, 'No', 'client'),
               (4, 'No', 'client'),
               (10, 'No', 'driver'),
               (11, 'No', 'driver'),
               (12, 'No', 'driver'),
               (13, 'No', 'driver');
   

2. Query

   SELECT t.Request_at Day,
   	ROUND(SUM((CASE WHEN t.Status LIKE 'cancelled%' THEN 1 ELSE 0 END))/COUNT(*),2) 'Cancellation Rate'  
   FROM trips t  
   INNER JOIN Users u 
   ON u.Users_Id = t.Client_Id AND u.Banned = 'No'  
   WHERE t.Request_at BETWEEN '2013-10-01'and'2013-10-03' 
   GROUP BY t.Request_at;
   

6 查找各部门前3高工资的员工

1. 建表并插入数据

   CREATE TABLE Employee(
       Id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       Name VARCHAR(10) NOT NULL,
       Salary INT NOT NULL,
       DepartmentId INT NOT NULL
   );
   
   INSERT INTO Employee
       VALUES (1, 'Joe', 70000, 1),
               (2, 'Henry', 80000, 2),
               (3, 'Sam', 60000, 2),
               (4, 'Max', 90000, 1),
               (5, 'Janet', 69000, 1),
               (6, 'Randy', 85000, 1);
   

2. Query

   SELECT d.Name Department, e1.Name Employee, e1.Salary Salary
   FROM Employee e1
   JOIN Department d
   ON e1.DepartmentId = d.Id
   WHERE 3 >   (
               SELECT COUNT(DISTINCT e2.Salary) 
               FROM Employee e2
               WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
               )
   ORDER BY d.Name, e1.Salary DESC;
   

7 非连续分鼠标排名

1. 建表和插入数据

   CREATE TABLE score(
       id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
       Score FLOAT NOT NULL
   );
       
   INSERT INTO score
       VALUES  (1, 3.50),
               (2, 3.65),
               (3, 4.00),
               (4, 3.85),
               (5, 4.00),
               (6, 3.65);
   

2. Query

   -- 自定义rank函数, 降序后, 将Score与上一个Score对比, 若不同则Rank+count, count置1, 相同count+=1, 不降序Rank就失效了
   SELECT
     Score,
     @rank := @rank + (@prev <> (s.Score)) * @count  Rank,
   (CASE 
   WHEN (@prev <> (@prev := Score)) THEN @count := 1
   ELSE @count := @count + 1  END) Count
   FROM
     score s,
     (SELECT @rank := 0, @prev := -1, @count := 1) init
   ORDER BY Score DESC;
   

8 :=和=的区别

• =: 在SET和UPDATE时才和:=一样是赋值的作用，其它都是等于的作用。鉴于此，用变量实现行号时，必须用:=.

• :=: SELECT时也是赋值的作用