首页分享 mysql学习笔记(4)

mysql学习笔记(4)

来源：萌宠菠菠乐园时间：2024-10-04 12:48

4.1 MySQL 实战

学习内容

数据导入导出
将之前创建的任意一张MySQL表导出,且是CSV格式
在这里插入图片描述

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

再将CSV表导入数据库

使用sql语句：
导出

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

导入

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

作业

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

创建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 | +----+-------+--------+--------------+ 12345678

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

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

编写一个 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)

12345678910111213141516171819202122232425

筛选结果

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+ 123456 项目八: 换座位（难度：中等）

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

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

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

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

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

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;

12345678910111213141516

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

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

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

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

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

4.2 MySQL 实战 - 复杂项目

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

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

IdClient_IdDriver_IdCity_IdStatusRequest_at11101completed2013-10-0122111cancelled_by_driver2013-10-0133126completed2013-10-0144136cancelled_by_client2013-10-0151101completed2013-10-0262116completed2013-10-0273126completed2013-10-02821212completed2013-10-03931012completed2013-10-031041312cancelled_by_driver2013-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 | +----------+--------+--------+ 123456789101112

写一段 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 | +------------+-------------------+ 1234567

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

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

IdNameSalaryDepartmentId1Joe7000012Henry8000023Sam6000024Max9000015Janet6900016Randy850001

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

DepartmentEmployeeSalaryITMax90000ITRandy85000ITJoe70000SalesHenry80000SalesSam60000

此外，请考虑实现各部门前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 1234567891011

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

ScoreRank4.0014.0013.8533.6543.6543.506

sql语句

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