select * from customers into outfile '/var/lib/mysql-files/customers.csv'
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';
再将CSV表导入数据库
load data infile '/var/lib/mysql-files/customers.csv'
into table test
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n';
7 各部门工资最高的员工(难度:中等)
创建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 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
题解
select d.name as department,e.name as employee,salary from employee e left join department d on e.departmentId = d.id where salary in (select max(salary) from employee group by departmentid);
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,users u
where (t.client_id = u.user_id) and t.request_at between '2013-10-01' and '2013-10-03'
group by t.request_at;
11 各部门前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 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;
数据导入导出将之前创建的任意一张MySQL表导出,且是CSV格式再将CSV表导入数据库7 各部门工资最高的员工(难度:中等)创建Employee 表,包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。+----+-------+--------+--------------+| Id | Name | Salary | Departmen...