MySQLゼロ基礎入門---プロジェクト実戦
12043 ワード
MySQL入門基礎 3.1 MySQL実戦 学習内容 作業 3.2 MySQL実戦-複雑なプロジェクト 作業 3.1 MySQL実戦
学習内容
データインポートエクスポート以前に作成した任意のMySQLテーブルをエクスポートし、CSVフォーマット です.さらにCSVテーブルをデータベース にインポートする.
さぎょう
(項目一)各部門で最も給料の高い従業員(難易度:中等)
Employeeテーブルを作成します.すべての従業員情報が含まれています.各従業員には対応するId、salary、departmentIdがあります.
企業のすべての部門の情報を含むDepartmentテーブルを作成します.
SQLクエリーを作成し、各部門で最も給料の高い従業員を見つけます.例えば、上記の表によれば、MaxはIT部門で最高賃金、HenryはSales部門で最高賃金を有する.
Employeeテーブルを作成し、データを入力します.
Departmentテーブルを作成し、データを入力します.
SQLクエリーを作成し、各部門で最も給料の高い従業員を見つけます.
(項目二)席替え(難易度:中等)
小美は中学校の情報科学技術の先生で、彼女はseat席表を持っていて、普段学生の名前と彼らに対応する席idを保存するために使っています.その中で縦列のidは連続的に増加した小美が隣接する2人の学生の席を変えたいと思っている.彼女にSQL queryを書いて美ちゃんが望んでいる結果を出力してもらえませんか?次のseatテーブルを作成してください:例:
データ入力が上表の場合、出力結果は次のようになります.
注意:学生数が奇数であれば、最後の同級生の席を変える必要はありません.
seatテーブルを作成し、データを入力します.
隣の2人の学生の席を変えるためにSQLを作成します.
(項目3)スコアランキング(難易度:中等)SQLクエリーを作成してスコアランキングを実現します.2つのスコアが同じ場合、2つのスコアランキング(Rank)は同じです.平分後の次の順位は次の連続する整数値であることに注意してください.言い換えれば、順位の間に「間隔」があるべきではない.次のscoreテーブルを作成します.
たとえば、上記のscoresテーブルに基づいて、クエリーを返します(スコアが高いものから低いものまで):
scoresテーブルを作成し、データを入力します.
スコアランキングを実現するためにSQLクエリーを作成します.
3.2 MySQL実戦-複雑なプロジェクト
さぎょう
(項目四)行程とユーザー(難易度:困難)Trips表にはすべてのタクシーの行程情報が格納されている.各行程にワンタッチId,Client_があるIdとDriver_IdはUsersテーブルのUsers_Idの外部キー.Statusは列挙タイプであり、列挙メンバーは(‘completed’,‘cancelled_by_driver’,‘cancelled_by_client’)である.
Usersテーブルにはすべてのユーザーが格納されます.ユーザーごとにユニークなUsers_Id.Bannedはこのユーザーが禁止されているかどうかを示し、Roleは(「client」,「driver」,「partner」)を表す列挙タイプである.
SQL文を書くと、2013年10月1日から2013年10月3日までの間、ユーザーを禁止しないキャンセル率が検出されます.上記の表に基づいて、SQL文は次の結果を返します.キャンセル率(Cancelation Rate)は小数2桁を保持します.
Usersテーブルを作成し、データを入力します.
Tripsテーブルを作成し、データを入力します.
SQL文を書くと、2013年10月1日から2013年10月3日までの間、ユーザーを禁止しないキャンセル率が検出されます.
(項目五、)各部門の上位3高賃金の従業員(難易度:中等)は、項目1のemployee表を空にし、以下のデータを再挿入します(実際には5、6の2行を多く挿入します).
SQLクエリーを作成し、各部門の給与の上位3社の従業員を見つけます.たとえば、上記のテーブルに基づいて、クエリの結果を返します.
また、各部署の前にN高賃金の従業員機能を実現することも考えてください.
SQLクエリーを作成し、各部門の給与の上位3社の従業員を見つけます.
(項目六、)点数ランキング-(難易度:中等)は依然として項目三中の点数表であり、順位機能を実現しているが、順位は非連続で、以下の通りである.
SQLでランキング機能を実現し、ランキングは非連続です.
学習内容
データインポートエクスポート
さぎょう
(項目一)各部門で最も給料の高い従業員(難易度:中等)
Employeeテーブルを作成します.すべての従業員情報が含まれています.各従業員には対応するId、salary、departmentIdがあります.
+----+-------+--------+--------------+
| 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 |
+------------+----------+--------+
Employeeテーブルを作成し、データを入力します.
CREATE TABLE Employee (
Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(25) NOT NULL,
Salary INT NOT NULL,
DepartmentId INT NOT NULL
);
insert into Employee(Id,Name,Salary,DepartmentId) values(1,'Joe',70000,1);
insert into Employee(Id,Name,Salary,DepartmentId) values(2,'Henry',80000,2);
insert into Employee(Id,Name,Salary,DepartmentId) values(3,'Sam',60000,2);
insert into Employee(Id,Name,Salary,DepartmentId) values(4,'Max',90000,1);
Departmentテーブルを作成し、データを入力します.
CREATE TABLE Department (
Id INT NOT NULL PRIMARY KEY,
Name VARCHAR(25) NOT NULL
);
insert into Department(Id,Name) values(1,'IT');
insert into Department(Id,Name) values(2,'Sales');
SQLクエリーを作成し、各部門で最も給料の高い従業員を見つけます.
SELECT
d.Name AS Department, e.Employee, e.Salary
FROM
Department d
JOIN
(SELECT
Name AS Employee, Salary, DepartmentId
FROM
Employee
WHERE
Salary IN (SELECT
MAX(salary)
FROM
Employee
GROUP BY DepartmentId)) e ON d.Id = e.DepartmentId
ORDER BY Salary DESC;
(項目二)席替え(難易度:中等)
小美は中学校の情報科学技術の先生で、彼女はseat席表を持っていて、普段学生の名前と彼らに対応する席idを保存するために使っています.その中で縦列のidは連続的に増加した小美が隣接する2人の学生の席を変えたいと思っている.彼女に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 |
+---------+---------+
注意:学生数が奇数であれば、最後の同級生の席を変える必要はありません.
seatテーブルを作成し、データを入力します.
CREATE TABLE seat (
id INT NOT NULL PRIMARY KEY,
student VARCHAR(25) NOT NULL
);
insert into seat(id,student) values(1,'Abbot');
insert into seat(id,student) values(2,'Doris');
insert into seat(id,student) values(3,'Emerson');
insert into seat(id,student) values(4,'Green');
insert into seat(id,student) values(5,'Jeames');
隣の2人の学生の席を変えるためにSQLを作成します.
SELECT (CASE
WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
WHEN MOD(id,2) = 1 THEN id+1
ElSE id-1
END) AS id, student
FROM seat
ORDER BY id;
(項目3)スコアランキング(難易度:中等)SQLクエリーを作成してスコアランキングを実現します.2つのスコアが同じ場合、2つのスコアランキング(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 |
+-------+------+
scoresテーブルを作成し、データを入力します.
CREATE TABLE scores (
Id INT NOT NULL PRIMARY KEY,
Score float(5,2) NOT NULL
);
insert into scores(Id,Score) values(1,3.50);
insert into scores(Id,Score) values(2,3.65);
insert into scores(Id,Score) values(3,4.00);
insert into scores(Id,Score) values(4,3.85);
insert into scores(Id,Score) values(5,4.00);
insert into scores(Id,Score) values(6,3.65);
スコアランキングを実現するためにSQLクエリーを作成します.
SELECT
Score,
(SELECT
COUNT(DISTINCT Score)
FROM
scores
WHERE
Score >= s.Score) AS Rank
FROM
Scores s
ORDER BY Score DESC;
3.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文は次の結果を返します.キャンセル率(Cancelation Rate)は小数2桁を保持します.
+------------+-------------------+
| Day | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
+------------+-------------------+
Usersテーブルを作成し、データを入力します.
CREATE TABLE Users (
Users_Id INT NOT NULL PRIMARY KEY,
Banned varchar(3) NOT NULL,
Role ENUM('client', 'driver','partner')
);
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');
Tripsテーブルを作成し、データを入力します.
CREATE TABLE Trips (
Id INT NOT NULL PRIMARY KEY,
Client_Id int NOT NULL,
Driver_Id int not null,
City_Id int not null,
Status ENUM('completed', 'cancelled_by_driver','cancelled_by_client'),
Request_at date,
foreign key(Client_Id) references Users(Users_Id),
foreign key(Driver_Id) references Users(Users_Id)
);
insert into Trips values(1,1,10,1,'completed','2013-10-01');
insert into Trips values(2,2,11,1,'cancelled_by_driver','2013-10-01');
insert into Trips values(3,3,12,6,'completed','2013-10-01');
insert into Trips values(4,4,13,6,'cancelled_by_client','2013-10-01');
insert into Trips values(5,1,10,1,'completed','2013-10-02');
insert into Trips values(6,2,11,6,'completed','2013-10-02');
insert into Trips values(7,3,12,6,'completed','2013-10-02');
insert into Trips values(8,2,12,12,'completed','2013-10-03');
insert into Trips values(9,3,10,12,'completed','2013-10-03');
insert into Trips values(10,4,13,12,'cancelled_by_driver','2013-10-03');
SQL文を書くと、2013年10月1日から2013年10月3日までの間、ユーザーを禁止しないキャンセル率が検出されます.
SELECT T2.DAY,IFNULL(ROUND((T1.num/T2.num),2),0) AS 'Cancellation Rate'
FROM
(SELECT Request_at as Day,count(*) as num
FROM Trips t
LEFT JOIN Users u
ON t.Client_Id = u.Users_Id
WHERE u.Banned != 'Yes'
AND t.status != 'completed'
AND Request_at >='2013-10-01' AND Request_at <= '2013-10-03'
GROUP BY Day) AS T1
RIGHT JOIN
(SELECT Request_at as Day,count(*) as num
FROM Trips t
LEFT JOIN Users u
ON t.Client_Id = u.Users_Id
WHERE u.Banned != 'Yes'
AND Request_at >='2013-10-01' AND Request_at <= '2013-10-03'
GROUP BY Day) AS T2
ON T1.DAY = T2.DAY;
(項目五、)各部門の上位3高賃金の従業員(難易度:中等)は、項目1のemployee表を空にし、以下のデータを再挿入します(実際には5、6の2行を多く挿入します).
+----+-------+--------+--------------+
| 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クエリーを作成し、各部門の給与の上位3社の従業員を見つけます.たとえば、上記のテーブルに基づいて、クエリの結果を返します.
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
また、各部署の前にN高賃金の従業員機能を実現することも考えてください.
SQLクエリーを作成し、各部門の給与の上位3社の従業員を見つけます.
SELECT
d.name AS Department, e.Name AS Employee, Salary
FROM
Employee e
JOIN
Department d ON e.DepartmentId = d.Id
WHERE
(SELECT
COUNT(DISTINCT em.Salary)
FROM
Employee em
WHERE
em.Salary >= e.Salary
AND em.DepartmentId = e.DepartmentId) <= 3
GROUP BY Department , Salary DESC;
(項目六、)点数ランキング-(難易度:中等)は依然として項目三中の点数表であり、順位機能を実現しているが、順位は非連続で、以下の通りである.
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 3 |
| 3.65 | 4 |
| 3.65 | 4 |
| 3.50 | 6 |
+-------+------
SQLでランキング機能を実現し、ランキングは非連続です.
SELECT
s.Score,
(SELECT
COUNT(*) + 1
FROM
Scores AS s1
WHERE
s1.Score > s.Score) AS Rank
FROM
scores s
ORDER BY Score DESC;