[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='北京';
~~~
