练习 · 随堂笔记-Java105班

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