mysql学习笔记(4)

4.1 MySQL 实战

学习内容

数据导入导出
将之前创建的任意一张MySQL表导出,且是CSV格式


关于csv文件，navicat for mysql中不包含cav文件的格式。尝试用MySQL workbench，可以导出csv文件。


再将CSV表导入数据库

使用sql语句：
导出

/*
字段之间以逗号分隔;字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。
数据行之间以\r\n分隔；
*/
select * from test_info 
into outfile 'test.csv' 
fields terminated by ',' optionally enclosed by '"' escaped by '"' 
lines terminated by '\r\n'; 

导入

load data infile 'test.csv' 
into table test_info  
fields terminated by ','  optionally enclosed by '"' escaped by '"' 
lines terminated by '\r\n'; 

作业

项目七: 各部门工资最高的员工（难度：中等）

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

创建Department 表，包含公司所有部门的信息。

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

    USE test
CREATE TABLE IF NOT EXISTS Employee(
	Id INT UNSIGNED AUTO_INCREMENT,
	Name VARCHAR(100) NOT NULL,
	Salary INT DEFAULT 0,
  DepartmentId INT DEFAULT 0,
	PRIMARY KEY (Id)
);
INSERT INTO Employee(Name,Salary,DepartmentId) 
VALUES('Joe',70000,1),
      ('Henry',80000,2),
			('Sam',60000,2),
     	('Max',90000,1);
USE test
CREATE TABLE IF NOT EXISTS Department(
	Id INT UNSIGNED AUTO_INCREMENT,
	Name VARCHAR(100) NOT NULL,
	PRIMARY KEY (Id)
);
INSERT INTO Department(Name) 
VALUES('IT'),
     	('Sales');
select d.Name as Department,e.Name as Employee,e.Salary
from Department d,Employee e
where e.DepartmentId=d.Id and e.Salary=(Select max(Salary) from Employee where DepartmentId=d.Id)

筛选结果

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

项目八: 换座位（难度：中等）

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
请创建如下所示seat表：
示例：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

假如数据输入的是上表，则输出结果如下：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。

USE test
CREATE TABLE IF NOT EXISTS Seat(
	id INT UNSIGNED AUTO_INCREMENT,
	student VARCHAR(100) NOT NULL,
	PRIMARY KEY (id)
);
INSERT INTO Seat(student) 
VALUES('Adbot'),('Doris'),('Emerson'),('Green'),('James');
select s.id , s.student from  
(  
select id-1 as id ,student from seat where mod(id,2)=0  
union  
select id+1 as id,student from seat where mod(id,2)=1 and id !=(select count(*) 			from seat)  
union  
select id,student from seat where mod(id,2)=1 and id = (select count(*) from seat)  
) s order by id;  

项目九: 分数排名（难度：中等）
编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
创建以下score表：

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

sql语句

USE test
CREATE TABLE IF NOT EXISTS Scores(
	Id INT UNSIGNED AUTO_INCREMENT,
	Score FLOAT(5,2) NOT NULL,
	PRIMARY KEY (Id)
);
INSERT INTO Scores(Score) 
VALUES(3.50),(3.65),(4.00),(3.85),(4.00),(3.65);
select Score,
(select count(distinct Score) from Scores as s2 where s2.Score >= s1.Score) Rank 
from Scores as s1
order by Score DESC;

4.2 MySQL 实战 - 复杂项目

项目十：行程和用户（难度：困难）

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

Id	Client_Id	Driver_Id	City_Id	Status	Request_at
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

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

+----------+--------+--------+
| Users_Id | Banned |  Role  |
+----------+--------+--------+
|    1     |   No   | client |
|    2     |   Yes  | client |
|    3     |   No   | client |
|    4     |   No   | client |
|    10    |   No   | driver |
|    11    |   No   | driver |
|    12    |   No   | driver |
|    13    |   No   | driver |
+----------+--------+--------+

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

sql语句

USE test
CREATE TABLE IF NOT EXISTS Users(
	Users_Id INT DEFAULT 0,
	Banned VARCHAR(100) NOT NULL,
	Role ENUM('client','driver','partner') DEFAULT 'client'
);
INSERT INTO Users(Users_Id,Banned,Role) 
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');
SELECT Request_at Day, 
ROUND(COUNT(IF(Status != 'completed', TRUE, NULL)) / COUNT(*), 2) 'Cancellation Rate' 
            FROM Trips 
            WHERE (Request_at between '2013-10-01' and '2013-10-03') and Client_Id IN (SELECT Users_Id FROM Users WHERE Banned = 'No') 
            GROUP BY Request_at;

项目十一：各部门前3高工资的员工（难度：中等）
将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）：

Id	Name	Salary	DepartmentId
1	Joe	70000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	70000
Sales	Henry	80000
Sales	Sam	60000

此外，请考虑实现各部门前N高工资的员工功能。

SELECT P2.Name AS Department,P3.Name AS Employee,P3.Salary AS Salary
FROM Employee AS P3
INNER JOIN Department AS P2
ON P2.Id = P3.DepartmentId 
WHERE (
    SELECT COUNT(DISTINCT Salary)
    FROM Employee AS P4
    WHERE P3.DepartmentId = P4.DepartmentId
    AND P4.Salary >= P3.Salary
) <= 3
ORDER BY DepartmentId,Salary DESC

项目十二 分数排名 - （难度：中等）
依然是昨天的分数表，实现排名功能，但是排名是非连续的，如下：

Score	Rank
4.00	1
4.00	1
3.85	3
3.65	4
3.65	4
3.50	6

sql语句

SELECT Score,
			(SELECT COUNT(Score)
				FROM score AS s2 
				WHERE s2.Score > s1.Score
			)+1  AS rank
FROM score AS s1
ORDER BY Score DESC;