MySQL经典练习题（一）

• 建立学生成绩表scores

create table if not exists scores(
name varchar(2) not null default ' ',
course varchar(4) not null default ' ');

 

改变表结构，插入列：

 alter table scores add grade tinyint(1) not null default '0';

  

• 插入数据

单行插入：

 insert into scores values('张三', '语文', 81);

多行插入：

insert into scores values
('张三', '数学', 75),
('李四', '语文', 76),
('李四', '数学', 90),
('王五', '语文', 81),
('王五', '数学', 100),
('王五', '英语', 90);

表数据：

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

分析：只要把小于80的人去掉，剩下的就是都大于80的学生

select distinct(name) from scores
where name not in (select distinct(name) from scores where grade < 80);

第二种：找到每个人的最小值，然后找到最小值大于80的人

select name from scores 
group by name
having min(grade) > 80;

• 建立学生表scores2

增加自动编号、学号、课程编号：

create table if not exists scores2
(id tinyint(1) auto_increment primary key,
stu_num varchar(7) not null default ' ',
name varchar(2) not null default ' ',
cou_num varchar(4) not null default '0000',
course varchar(4) not null default ' ',
grade tinyint(1) not null default '0');

表结构：

插入数据：

由于id是自增型，在插入时要指明插入的列，要是默认全部的话会出错。

insert into scores2(stu_num, name, cou_num, course, grade)
values
('2005001', '张三', '0001', '数学', 69),
('2005002', '李四', '0001', '数学', 89),
('2005001', '张三', '0001', '数学', 69);

表数据：

• 删除除了自动编号不同, 其他都相同的学生冗余信息

分析：先分组把列属性一样的分一块，再找出id，这些就是不重复的。最后删除重复的信息。

#先分组把列属性一样的分一块，再找出id，这些就是不重复的

select id from scores2
group by stu_num, name, cou_num, course, grade;

  

#删除重复的信息

delete from scores2 where id not in
(
	select temp.id from
	(
		select id from scores2
		group by stu_num, name, cou_num, course, grade
	)as temp
);

结果：

遇到的问题：

在MySQL中是不允许在同一张表上，先进行检索在进行更新的。因此代码

delete from scores2 where id not in 
(
	select id from scores2
	group by stu_num, name, cou_num, course, grade
);

出现You can’t specify target table for update in FROM clause错误，即不能先select出同一表中的某些值，再update这个表(在同一语句中)。

解决：在select外边套一层，让数据库认为你不是查同一表的数据作为同一表的更新数据。

这个问题只出现于mysql，mssql和oracle不会出现此问题。