DW集訓(xùn)營數(shù)據(jù)庫Mysql梳理[五]

• • 1 任務(wù)
  • 2 數(shù)據(jù)導(dǎo)入導(dǎo)出
  • • 2.1將Excel文件導(dǎo)入MySQL表
    • 2.2 MySQL表導(dǎo)出表到Excel文件
  • 3 作業(yè)
  • • 3.1 各部門工資最高的員工
    • 3.2 換座位
    • 3.3 分?jǐn)?shù)排名
  • 參考

1 任務(wù)

任務(wù)五學(xué)習(xí)內(nèi)容 數(shù)據(jù)導(dǎo)入導(dǎo)出 (見附件)將Excel文件導(dǎo)入MySQL表MySQL導(dǎo)出表到Excel文件 作業(yè) 項目七: 各部門工資最高的員工（難度：中等） 創(chuàng)建Employee 表，包含所有員工信息，每個員工有其對應(yīng)的 Id, salary 和 department Id。 +----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | +----+-------+--------+--------------+ 創(chuàng)建Department 表，包含公司所有部門的信息。 +----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+ 編寫一個 SQL 查詢，找出每個部門工資最高的員工。例如，根據(jù)上述給定的表格，Max 在 IT 部門有最高工資，Henry 在 Sales 部門有最高工資。 +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+項目八: 換座位（難度：中等） 小美是一所中學(xué)的信息科技老師，她有一張 seat 座位表，平時用來儲存學(xué)生名字和與他們相對應(yīng)的座位 id。 其中縱列的 id 是連續(xù)遞增的 小美想改變相鄰倆學(xué)生的座位。 你能不能幫她寫一個 SQL query 來輸出小美想要的結(jié)果呢？請創(chuàng)建如下所示seat表： 示例： +---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +---------+---------+ 假如數(shù)據(jù)輸入的是上表，則輸出結(jié)果如下： +---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +---------+---------+ 注意： 如果學(xué)生人數(shù)是奇數(shù)，則不需要改變最后一個同學(xué)的座位。項目九: 分?jǐn)?shù)排名（難度：中等） 編寫一個 SQL 查詢來實現(xiàn)分?jǐn)?shù)排名。如果兩個分?jǐn)?shù)相同，則兩個分?jǐn)?shù)排名（Rank）相同。請注意，平分后的下一個名次應(yīng)該是下一個連續(xù)的整數(shù)值。換句話說，名次之間不應(yīng)該有“間隔”。 創(chuàng)建以下score表： +----+-------+ | Id | Score | +----+-------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | +----+-------+ 例如，根據(jù)上述給定的 Scores 表，你的查詢應(yīng)該返回（按分?jǐn)?shù)從高到低排列）： +-------+------+ | Score | Rank | +-------+------+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | +-------+------+

2 數(shù)據(jù)導(dǎo)入導(dǎo)出

附件excel文件已存百度網(wǎng)盤：附件excel

2.1將Excel文件導(dǎo)入MySQL表

將Excel文件導(dǎo)入到Navicat for MySQL數(shù)據(jù)庫

根據(jù)excel的文件類型，在數(shù)據(jù)庫的表頁面運行’導(dǎo)入向?qū)А?#xff0c;選擇導(dǎo)入數(shù)據(jù)的類型：

選擇Excel文件的位置，選擇文件數(shù)據(jù)源，并導(dǎo)入數(shù)據(jù)源中的表，這里我導(dǎo)入xlsx失敗，于是將xlsx文件保存為xls進(jìn)行導(dǎo)入：

選擇目標(biāo)表，也可以新建一個表將數(shù)據(jù)插入：

設(shè)定主鍵

選擇導(dǎo)入模式，如果是新建表，則選擇第一項–添加記錄到目標(biāo)表：

導(dǎo)入成功

對表進(jìn)行操作
通過上述步驟將excel文件導(dǎo)入到Navicat數(shù)據(jù)庫中，在數(shù)據(jù)表窗口中，通過’+‘和’-'可以實現(xiàn)插入和刪除一條記錄，當(dāng)要遍歷每一列添加內(nèi)容時，使用’Tab’鍵即可依次往下添加數(shù)據(jù)。

2.2 MySQL表導(dǎo)出表到Excel文件

根據(jù)表的右鍵選擇導(dǎo)出向?qū)?#xff1b;

選擇要導(dǎo)出的數(shù)據(jù)表格式，這里我們選擇的是xls表格格式；

接下來選擇需要導(dǎo)出的表，并設(shè)置導(dǎo)出文件的位置，然后點擊下一步：

導(dǎo)出成功

在保存的位置，可以看到導(dǎo)出的數(shù)據(jù)表成功

3 作業(yè)

3.1 各部門工資最高的員工

先建立表并插入數(shù)據(jù)：

CREATE TABLE IF NOT EXISTS Employee(Id SMALLINT PRIMARY KEY AUTO_INCREMENT,Name VARCHAR(20) NOT NULL,Salary INT NOT NULL,DepartmentId TINYINT NOT NULL ); CREATE TABLE IF NOT EXISTS Department(Id SMALLINT PRIMARY KEY AUTO_INCREMENT,Name VARCHAR(20) NOT NULL );INSERT Employee(Name,Salary,DepartmentId) Values('Joe',70000,1),('Henry',80000,2),('Sam',60000,2),('Max',90000,1);INSERT Department(Name) Values('IT'),('Sales');

在處理這個問題的思路上，我首先先將表連接，再對連接好的表來設(shè)置條件，設(shè)置的條件為：對應(yīng)的id相同，并且使用子查詢的方式篩選出了最大值，這里要注意的是，因為題目有最終數(shù)據(jù)表的格式，所以再select中的命名需要起別名：

SELECT d.Name as Department,e.Name as Employee,e.Salary FROM Employee e,Department d WHERE e.DepartmentId = d.Id and e.Salary = (SELECT MAX(Employee.Salary)FROM EmployeeWHERE Employee.DepartmentId = d.Id);

3.2 換座位

同樣，我們先創(chuàng)建表結(jié)構(gòu)并且插入數(shù)據(jù)：

CREATE TABLE IF NOT EXISTS seat(id TINYINT PRIMARY KEY AUTO_INCREMENT,student VARCHAR(20) NOT NULL ); INSERT seat(student) VALUES('Abbot'),('Doris'),('Emerson'),('Green'),('Jeames');

接下來考慮交換相鄰的座位，如果是奇數(shù)的話，則最后一個座位不用變，這里考慮的是使用case when的條件語句：

SELECT (CASE WHEN id=counts THEN id WHEN MOD(id,2)=0 THEN id-1 ELSE id+1 END) AS id,student FROM seat ,(SELECT COUNT(id) AS counts FROM seat) ORDER BY id;

3.3 分?jǐn)?shù)排名

編寫一個 SQL 查詢來實現(xiàn)分?jǐn)?shù)排名。如果兩個分?jǐn)?shù)相同，則兩個分?jǐn)?shù)排名（Rank）相同。
首先創(chuàng)建表以及插入語句：

-----創(chuàng)建表 CREATE TABLE IF NOT EXISTS score(Id TINYINT PRIMARY KEY AUTO_INCREMENT,Score FLOAT(5,2) NOT NULL ); -----插入表 INSERT score(Score) VALUES('3.50'),('3.65'),('4.00'),('3.85'),('4.00'),('3.65');

現(xiàn)在的問題是，如何select出score，并且還select出score的排名？現(xiàn)在有一個思路是：所謂的現(xiàn)在的排名，實質(zhì)上表示的意思是當(dāng)前大于或等于其對應(yīng)分?jǐn)?shù)的數(shù)字，比如4，還有相同的分?jǐn)?shù)4，那么其對應(yīng)的排名應(yīng)該是1。于是我們需要在score表中找出大于或者等于該分?jǐn)?shù)的不重復(fù)的分?jǐn)?shù)，然后降序排列：

SELECT Score,(SELECT COUNT(DISTINCT Score)FROM score AS s2 WHERE s2.Score>=s1.Score) FROM score AS s1 ORDER BY Score DESC;

參考

MySQL實現(xiàn)

總結(jié)

以上是生活随笔為你收集整理的DW集训营数据库Mysql梳理[五]的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。