SQL练习题

以下所有操作在查询管理器中手工书写sql语句完成

1、按下列步骤创建数据库和表
1.1、创建一个名为 students 数据库
create database students CHARACTER set utf8

1.2、在这个数据库中创建一个名为[学生表(student)]的表，由[学号]、[姓名]、[专业]、[班级]
   字段组成。[学号] 字段为主键，类型为int；[姓名] 和 [专业] 字段类型为varchar,
   长度为6和20；[班级] 字段类型为char,长度为4。这些字段均不允许NULL值。
create table student
(
   sid int primary key,
   sname varchar(6) not null,
   major varchar(20) not null,
   class char(4) not null
)ENGINE=INNODB ;

1.3、在这个数据库中创建一个名为 [成绩表(score)] 的表，由 [学号] 、[课程编号]、 [成绩] 字段组成。
    [学号] 为外键。[学号] 类型为int；
    [课程编号] 类型为char,长度为2； [成绩] 类型为int
    这些字段均不允许NULL值。
create table score
(
   sid int not null,
   cid char(2) not null,
   mark decimal(8,2) not null, -- 123.45
   foreign key(sid) references student(sid)
);

2、在学生表中添加以下记录：
    学号    姓名    专业        班级
    2001001 吴小亮  计算机及应用     0101
    2001002 刘京生    计算机及应用    0101
    2001003 李向名    计算机及应用     0102
    2001004 高大山    计算机及应用    0102
    2001005 王前    网络应用    0103
    2001006 李云飞  网络应用        0103
   全部添加完毕后，显示该表中的所有记录

select * from student;
insert into student values(2001001,'吴小亮','计算机及应用','0101'),
(2001002,'刘京生','计算机及应用','0101'),
(2001003,'李向名','计算机及应用','0102'),
(2001004,'高大山','计算机及应用','0102'),
(2001005,'王前','网络应用','0103'),
(2001006,'李云飞','网络应用','0103')

3、在成绩表中添加以下记录：
    学号    课程编号    成绩        
    2001001 01        73
    2001001 02        88
    2001002    01        95
    2001002    02        64
    2001003    01        75
    2001003    02        90
    2001004 01        99
    2001004 02        50
    2001005 01        66
    2001005 02        80
    2001006 01        93
    2001006 02        75
    全部添加完毕后，显示该表中的所有记录
delete from score;
  insert into score values
(2001001,'01',73),
(2001001,'02',88),
(2001002,'01',95),
(2001002,'02',64),
(2001003,'01',75),
(2001003,'02',90),
(2001004,'01',99),
(2001004,'02',50),
(2001005,'01',66),
(2001005,'02',80),
(2001006,'01',93),
(2001006,'02',75)

select * from score;

4、在学生表中查找姓名为“王前”的记录并将这条记录中的班级更改为“0104”，
然后列出经过修改后的这一行记录
update student set class='0104' where sname='王前'

select * from student where sname='王前'

select * from student;

select * from score;

5、分别从学生表和成绩表中删除学号为2001006的记录(注意顺序，为什么？)
-- 先删外键所在的表  再删主键表
delete from score where sid=2001006;

delete from student where sid=2001006;

6、查找显示成绩表中"学号"字段的全部数据

select sid from score;

7、查找显示成绩表中"学号"字段的全部数据，要求查询结果中不包含重复记录

select distinct  sid from score;

8、从学生表和成绩表中查找记录，要求以两个表中的“学号”字段作为连接字段，
   结果显示学号,姓名,课程编号,成绩的内容
select student.sid,sname,cid,mark from student inner join score 
on student.sid=score.sid

9、从学生表和成绩表中查找记录，看看学生表中有哪些学生以及这些学生的成绩如何。
   要求使用左外部连接和别名
select * from student a left outer join score b on a.sid=b.sid

10、在成绩表中查找课程编号为02的成绩高于80分的学生记录

select * from score where cid='02' and mark>80;

11、在学生表中找出姓刘或姓李的学生

select * from student where sname like '刘%' or sname like '李%'

12、在成绩表中查找01号课程成绩介于60与80之间的学生记录

select * from score where cid='01' and mark between 60 and 80

13、在学生表中找出姓刘、姓李、姓王的学生记录
select * from student where sname like '刘%' or sname like '李%' 
or sname like '王%'
14、在学生表中找出学号末位数字位于3-5范围内的学生记录
select * from student where sid%10 in (3,4,5)
15、把成绩表中课程编号='01'的所有学生记录按成绩从高到低排列显示出来
select * from score where cid='01' order by mark desc
16、从学生表和成绩表中查找记录，统计0101班01号课程的总成绩
select sum(mark) from student inner join score on student.sid=score.sid
and class='0101' and cid='01'
17、统计学生表中的记录总数
select count(*) from student;
18、在成绩表中，找出02号课程成绩中的最高分
select max(mark) from score where cid='02';
19、在成绩表中，计算每个学生各门功课的总成绩和平均成绩，
并按照总成绩降序排列
select sid,sum(mark),avg(mark) from score group by sid;

20、从成绩表中查找记录，列出02号课程成绩高于此课程平均成绩的记录

select * from score where cid='02'  and mark >
(select avg(mark) from score where cid='02')

21、列出0102班的学生成绩
select score.* from student inner join score on student.sid=score.sid
and class='0102'

22、查询所有考试01号课程分数比所有02号课程分数高的学生学号，姓名；

select student.sid,sname from score,student  where student.sid=score.sid and cid='01' and mark >all

(select mark from score where cid='02')

23、查询所有考试01号课程分数不低于所有02号课程分数的学生学号，姓名；

select student.sid,sname from score,student  where student.sid=score.sid 
and cid='01' and mark >=all
(select mark from score where cid='02')

24、查询和01号成绩最高的同学在一个班的同学的姓名；
select * from student;
select * from score;

select sname from student where class=
(select class from student where sid=(
select sid from score where cid='01' 
and mark=(select max(mark) from score where cid='01')))
and sid!=(
select sid from score where cid='01' 
and mark=(select max(mark) from score where cid='01'))

25、查询01号成绩进前三名的同学的姓名，班级；

select sname,class from student inner join score on student.sid=score.sid
and cid='01' order by mark desc limit 0,3

26、查询02号成绩第三到第五名的所有同学的姓名；
select sname from student inner join score on student.sid=score.sid
and cid='02' order by mark desc limit 2,3

27、查询01号成绩不是第一名的所有同学的姓名；
select * from student where sid!=
(select sid from score where cid='01' and mark =
(select max(mark) from score where cid='01'))

28、查询01号成绩和02号成绩都能进前三名的同学姓名；

update score set mark='95' where sid=2001003 and cid='01'

select sname from student,

(select * from score where cid='01' order by mark desc limit 0,3) a, 

(select * from score where cid='02' order by mark desc limit 0,3 ) b

where student.sid=a.sid and a.sid=b.sid

-- 联合查询  union
   a int,
   b int
);
drop table aa;
create table aa
(
  id varchar(10),
  num int
);
drop table bb;
create table bb
(
  id varchar(10),
  num int
);

select * from aa;
select * from bb;

insert into aa values('a',10);
insert into aa values('b',20);
insert into aa values('c',20);
insert into aa values('d',40);

insert into bb values('b',10);
insert into bb values('e',20);
insert into bb values('f',20);
insert into bb values('d',40);

-- 去掉重复行
select * from aa 
union
select * from bb

-- 不去调重复行
select * from aa 
union all
select * from bb