[TOC] # MySQL 練習題 ## 第一題 表名 User | Name | Tel | Content | Date | | :--- | :--- | :--- | :--- | | 張三 | 13333663366 | 大專畢業 | 2006-10-11 | | 張四 | 13612312331 | 本科畢業 | 2006-10-15 | | 小明 | 13905333221 | 中專畢業 | 2006-10-15 | 1、有一新記錄（小王 13254748547 高中畢業2007-05-06），請用SQL語句新增至表中。 ``` insert into [user]([name],[tel],[content],[date]) values('小王','13254748547','高中畢業','2007-05-06'); ``` 2、請用 SQL 語句，把張三的時間更新為當前系統時間。 ``` update [user] set [date]=GETDATE() where name='張三'; ``` 3、請寫出刪除姓名為張四的全部記錄。 ``` delete from [user] where name='張四'; ``` ## 第二題 表名 student\_score | name | course | score | | :--- | :--- | :--- | | 張三 | 語文 | 81 | | 張三 | 數學 | 75 | | 李四 | 語文 | 76 | | 李四 | 數學 | 90 | | 王五 | 語文 | 81 | | 王五 | 數學 | 100 | | 王五 | 英語 | 90 | 1. 用一條 SQL 語句，查詢出每門課都大于 80 分的學生姓名。 ``` SELECT DISTINCT name FROM student_score WHERE name NOT IN (SELECT DISTINCT name FROM student_score WHERE score<=80); ``` 或者 ``` SELECT name FROM student_score GROUP BY name HAVING MIN(score)>80; ``` 2. 查詢出「張」姓學生中平均成績大于 75 分的學生信息。 ~~~ SELECT * FROM student_score WHERE name IN (SELECT name FROM student_score WHERE name LIKE '張%' GROUP BY name HAVING AVG(score) > 75) ~~~ ## 第三題 表名 team | ID | Name | | :--- | :--- | | 1 | a | | 2 | b | | 3 | b | | 4 | a | | 5 | c | | 6 | c | 要求:執行一個刪除語句，當 Name 列上有相同時,只保留 ID 這列上值小的 例如：刪除后的結果應如下: | ID | Name | | :--- | :--- | | 1 | a | | 2 | b | | 5 | c | 請寫出 SQL 語句。 ``` DELETE FROM team WHERE id NOT IN (SELECT a.id FROM (SELECT MIN(id) AS id FROM team GROUP BY name ) a) ``` ## 第四題 表名 students | id | sno | username | course | score | | --- | --- | --- | --- | --- | | 1 | 1 | 張三 | 語文 | 50 | | 2 | 1 | 張三 | 數學 | 80 | | 3 | 1 | 張三 | 英語 | 90 | | 4 | 2 | 李四 | 語文 | 70 | | 5 | 2 | 李四 | 數學 | 80 | | 6 | 2 | 李四 | 英語 | 80 | | 7 | 3 | 王五 | 語文 | 50 | | 8 | 3 | 王五 | 英語 | 70 | | 9 | 4 | 趙六 | 數學 | 90 | 查詢出只選修了一門課程的全部學生的學號和姓名。 ``` SELECT sno,username,count(course) as 選課數 FROM students GROUP BY sno,username HAVING count(course) = 1; ``` ## 第五題 在名為商品庫的數據庫中包含有商品規格表 Content 和商品特性表 Property，它們的定義分別為： Content（Code varchar（50），Class varchar（20），Price double，Number int） Property（Code varchar（50），Place varchar（20），Brand varchar（50）） （1）寫出下面查詢語句的作用； `SELECT Distinct Brand FROM Property; ` 答：從Property表中查詢出所有不同的品牌 （2）從商品規格表中查詢出每類商品的最高單價 `SELECT Class,max(Price) FROM Content GROUP BY Class;` （3） 從商品規格表中查詢出同一類商品多于一種的所有分類名 `SELECT Class FROM Content GROUP BY Class HAVING COUNT(Class)>1;` ## 第六題 表名 food： | 字段名 | 字段描述 | 數據類型 | 外鍵 | 非空 | 唯一 | 自增 | | --- | --- | --- | --- | --- | --- | --- | | id | 編號 | INT | 是 | 是 | 是 | 是 | | name | 食品公司 | VARCHAR(20) | | 是 | | | | company | 生產廠商 | VARCHAR(30) | | 是 | | | | price | 價格(單位:圓) | FLOAT | | | | | | produce_time | 生產年份 | YEAR | | | | | | validity_time | 保質期(單位:年) | INT | | | | | | address | 廠址 | VARCAHR(50 | | | | | 1.寫出創建表的 SQL 語句； ~~~ CREATE TABLE food( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, company VARCHAR(30) NOT NULL, price FLOAT, produce_time YEAR, validity_time INT, address VARCHAR(50) ); ~~~ 2.將下邊的記錄插入到 food 表中 | id | name | company | price | produce_time | validity_time | address | | --- | --- | --- | --- | --- | --- | --- | | 1 | AA餅干 | AA餅干廠 | 2.5 | 2013 | 3 | 北京 | | 2 | CC牛奶 | CC牛奶廠 | 3.5 | 2014 | 2 | 河北 | | 3 | EE果凍 | EE果凍廠 | 1.5 | 2015 | 1 | 北京 | | 4 | FF咖啡 | FF咖啡廠 | 20 | 2002 | 5 | 天津 | | 5 | GG奶糖 | GG奶糖廠 | 14 | 2017 | 3 | 廣東 | ~~~ -- 第一種方法不指定具體的字段 INSERT INTO food VALUES(NULL,'AA餅干','AA餅干廠', 2.5 ,'2013', 3 ,'北京'); -- 第二種方法是依次指定food表的字段 INSERT INTO food( name, company, price, produce_time, validity_time, address) VALUES('CC牛奶','CC牛奶廠', 3.5 ,'2014', 2 ,'河北'); -- 第三種方法是同時插入多條記錄 INSERT INTO food VALUES (NULL,'EE果凍','EE果凍廠', 1.5 ,'2015', 1 ,'北京') , (NULL,'FF咖啡','FF咖啡廠', 20 ,'2002', 5 ,'天津') , (NULL,'GG奶糖','GG奶糖', 14 ,'2017', 3 ,'廣東'); ~~~ 3.將「CC牛奶廠」的廠址（address）改為「內蒙古」，并且將價格改為 3.2。 ~~~ UPDATE food SET address='內蒙古',price=3.2 WHERE name='CC牛奶'; ~~~ 4.將廠址在北京的公司保質期（validity_time）都改為 5 年。 ~~~ UPDATE food SET validity_time=5 WHERE address='北京'; ~~~ 5.刪除過期食品的記錄。若當前時間-生產年份（produce_time）>保質期（validity_time），則視為過期食品。 ~~~ DELETE FROM food WHERE validity_time < (2017-produce_time); ~~~ 6.刪除廠址為北京的食品的記錄。 ~~~ DELETE FROM food WHERE address='北京'; ~~~