mysql简单实例

第一题

1.有一新记录（小王 13254748547 高中毕业2007-05-06），请用SQL语句新增至表中。

CREATE TABLE `User`(
	id int(12) PRIMARY KEY,
	Name varchar(10),
	Tel varchar(20),
	Content VARCHAR(10),
	Date date
	
);

insert User (id,Name,Tel,Content,Date) VALUES (1,"张三","13333663344","大专毕业","2006-01-01");
insert User (id,Name,Tel,Content,Date) VALUES (2,"张四","13612312331","本科毕业","2006-10-15");
insert User (id,Name,Tel,Content,Date) VALUES (3,"小明","13905333221","中专毕业","2006-10-15");
insert user (id,name,tel,content,date) VALUES (4,"小王","13254748547","高中毕业","2007-05-06");

2.请用 SQL 语句，把张三的时间更新为当前系统时间。

update user set date = SYSDATE() where name = "张三";

3.请写出删除姓名为张四的全部记录。

DELETE from user where name = "张四";

第二题
表名 student_score

1.用一条 SQL 语句，查询出每门课都大于 80 分的学生姓名。

CREATE table `student_score` (
		name varchar(10),
		course varchar(10),
		score int(10)
);

INSERT student_score (name,course,score) VALUES ("张三","语文",81),
("张三","数学",75),
("李四","语文",76),
("李四","数学",90),
("王五","语文",81),
("王五","数学",100),
("王五","英语",90);

SELECT DISTINCT name from 
student_score where 
name not in (SELECT DISTINCT name from student_score  where 
score < 80);

2.查询出「张」姓学生中平均成绩大于 75 分的学生信息。

SELECT * from student_score 
where name = (SELECT name from (SELECT name,avg(score) e 
from student_score where name like ("张%") f 
where e > 75) ;

第三题

CREATE table team(
	id int(10),
	name VARCHAR(10)
);

INSERT team (id,name) VALUES(1,"a"),(2,"b"),(3,"b"),(4,"a"),(5,"c"),(6,"c");

DELETE from team 
	where id not in (
	SELECT a.id from -- 不能先select出同一表中的某些值，再update这个表(在同一语句中)。
	(SELECT id from team GROUP BY name) a 
	-- 将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql，mssql和oracle不会出现此问题。
);

第四题

查询出只选修了一门课程的全部学生的学号和姓名。

CREATE table students(
	id int(10),
	sno int(10),
	username varchar(10),
	course varchar(10),
	score int(10)
);

insert students (id,sno,username,course,score) VALUES(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 from students GROUP BY username having count(*) = 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;

查询商品特性表中不重复的brand

（2）从商品规格表中查询出每类商品的最高单价

create table content	(
	code varchar(50),
	class varchar(20),
	price double,
	number int(10)
);

create table property	(
	code varchar(50),
	place varchar(20),
	brand varchar(50)
);


SELECT Class,max(Price) FROM Content GROUP BY Class;

（3） 从商品规格表中查询出同一类商品多于一种的所有分类名

SELECT Class FROM Content GROUP BY Class HAVING COUNT(Class)>1;

第六题

1.写出创建表的 SQL 语句；

create table food (
	id int(10) PRIMARY KEY auto_increment not null,
	name VARCHAR(20) not null,
	company varchar(20) not null,
	price float,
	produce_time year,
	validity_time int,
	address VARCHAR(50)
);
-- EXECUTE sp_addextendedproperty id'编号';

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 food (id,name,company,price,produce_time,validity_time,address) VALUES(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,"广东");

3.将「CC牛奶厂」的厂址（address）改为「内蒙古」，并且将价格改为 3.2。

UPDATE food set address = "内蒙古",price = 3.2 where company = "CC牛奶厂";

4.将厂址在北京的公司保质期（validity_time）都改为 5 年。

UPDATE food set validity_time = 5 where address = "北京";

5.删除过期食品的记录。若当前时间-生产年份（produce_time）>保质期（validity_time），则视为过期食品。

SELECT name from food where (year(now()) - produce_time) > validity_time;

6.删除厂址为北京的食品的记录。

DELETE from food where address = "北京";