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不会出现此问题。