7天快速掌握MySQL-DAY7

首先公布一下DAY6的作业答案。

项目九：

--code----建表并插入数据CREATE TABLE IF NOT EXISTS Trips (Id         INT, Client_Id  INT, Driver_Id  INT, City_Id    INT, Status     ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), Request_at VARCHAR(50));
CREATE TABLE IF NOT EXISTS Users (Users_Id INT, Banned   VARCHAR(50), Role     ENUM('client', 'driver', 'partner'));
TRUNCATE TABLE Trips;INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('1', '1', '10', '1', 'completed', '2013-10-01');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('3', '3', '12', '6', 'completed', '2013-10-01');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('5', '1', '10', '1', 'completed', '2013-10-02');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('6', '2', '11', '6', 'completed', '2013-10-02');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('7', '3', '12', '6', 'completed', '2013-10-02');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('8', '2', '12', '12', 'completed', '2013-10-03');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('9', '3', '10', '12', 'completed', '2013-10-03');INSERT INTO Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
TRUNCATE TABLE Users;INSERT INTO Users (Users_Id, Banned, Role) VALUES ('1',  'No',  'client');INSERT INTO Users (Users_Id, Banned, Role) VALUES ('2',  'Yes', 'client');INSERT INTO Users (Users_Id, Banned, Role) VALUES ('3',  'No',  'client');INSERT INTO Users (Users_Id, Banned, Role) VALUES ('4',  'No',  'client');INSERT INTO Users (Users_Id, Banned, Role) VALUES ('10', 'No',  'driver');INSERT INTO Users (Users_Id, Banned, Role) VALUES ('11', 'No',  'driver');INSERT INTO Users (Users_Id, Banned, Role) VALUES ('12', 'No',  'driver');INSERT INTO Users (Users_Id, Banned, Role) VALUES ('13', 'No',  'driver');--mysql> SELECT    -> Request_at AS Day,    -> FORMAT(sum(IF(Status LIKE 'cancelled%', 1, 0))/count(1), 2) AS 'Cancellation Rate'    -> FROM Trips as t    -> inner join Users as u on t.Client_Id = u.Users_Id    -> where u.Banned = 'NO'    -> GROUP BY Day;+------------+-------------------+| Day        | Cancellation Rate |+------------+-------------------+| 2013-10-01 | 0.33              || 2013-10-02 | 0.00              || 2013-10-03 | 0.50              |+------------+-------------------+3 rows in set (0.00 sec)

项目十：

--code--mysql> set @nn =3;  --设置前N高变量nn--Query OK, 0 rows affected (0.00 sec)
mysql> SELECT d.`Name` AS Department,e1.`Name` AS Employee,e1.Salary AS Salary    -> FROM employee e1    -> JOIN department d    -> ON e1.DepartmentId = d.id    -> WHERE (    -> SELECT COUNT(DISTINCT e2.Salary)    -> FROM employee e2    -> WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId    -> ) < @nn   --使用参数nn--    -> ORDER BY d.`Name`,e1.Salary desc;+------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT         | Max      |  90000 || IT         | Randy    |  85000 || IT         | Joe      |  70000 || Sales      | Henry    |  80000 || Sales      | Sam      |  60000 |+------------+----------+--------+5 rows in set (0.00 sec)

项目十一：

--code--mysql> SELECT FORMAT(Score,2) as Score,    -> (SELECT count(*) FROM scores AS s2 WHERE s2.Score > s1.Score)+1 AS Rank    -> FROM scores AS s1    -> ORDER BY Score DESC;+-------+------+| Score | Rank |+-------+------+| 4.00  |    1 || 4.00  |    1 || 3.85  |    3 || 3.65  |    4 || 3.65  |    4 || 3.50  |    6 |+-------+------+6 rows in set (0.00 sec)

通过一周的学习，我想你已经对mysql有一个基本的认识了，下面我们来回顾一下这一周都学习了什么内容：

mysql数据库安装及基础知识介绍

数据库导入，基本select语句，分组及排序用法

MySQL数据类型，增删改查基本语句

MySQL别名设置，不同表联结方式

navicat数据导入导出，项目实战（一）

项目实战（二）