MySQL 练习一

有一个“学生－课程”数据库，数据库中包括三个表：

(1) “学生”表Student由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept) Sno 为关键字。

(2) “课程”表Course由课程号Cno、课程名Cname、选修课号Cpno、学分Ccredit四个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit) Cno为关键字。

(3) “学生选课”表SC由学号Sno、课程号Cno、成绩Grade三个属性组成，可记为： SC(Sno,Cno,Grade) (SNO, CNO) 为关键字。

create database student_course default character set utf8;

create table student(

sno bigint primary key auto_increment,

sname varchar(32) not null,

ssex boolean default 1,

sage int,

sdept varchar(32)

)engine=innodb default charset utf8;

create table course(

cno bigint primary key,

cname varchar(32) not null,

cpno bigint,

ccredit int

)engine=innodb default charset utf8;

create table sc(

sno bigint,

cno bigint,

grade int,

primary key(sno,cno)

)engine = innodb default charset utf8;

1、建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。

create table student (

Sno bigint primary key auto_increment,

Sname varchar(32),

Ssex boolean default 1,

Sage int,

Sdept varchar(32)

)engine=innodb default charset = utf8;

2、向Student表增加“入学时间”列，其数据类型为日期型。

alter table student add column sdate date;

3、删除Student表

drop table if exists student;

4、查询全体学生的学号与姓名

select sname,sno from student;

5、查询全体学生的详细记录

select * from student;

6、查所有选修过课的学生的学号

select distinct sno from sc;

7、查所有年龄在20岁以下的学生姓名及其年龄

select sname,sage from student where age <20;

8、查考试成绩有不及格的学生的学号

select distinct sno from sc where score <60;

9、查询年龄在20至23岁之间的学生的姓名、系别、和年龄

select sname,sdept,sage from student where age between 20 and 23;

10、查所有姓刘的学生的姓名、学号和性别

select sname,sno,ssex from student where sname like ‘刘%’;

11、查姓“欧阳”且全名为三个汉字的学生的姓名

select sname from student where sname like ‘欧阳_’;

12、查询姓名中有李字的所有学生信息

select *from student where sname like ‘%李%’;

13、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列

select sno,grade from sc where cno = 3 order by grade desc;

14、计算1号课程的学生平均成绩

select avg(grade) from sc where cno = 1; 

15、查询学习1号课程的学生最高分数

select max(grade) from sc where cno = 1;

16、查询与“刘晨”在同一个系学习的学生

select sdept from student where sname = ‘刘晨’;

select sname from student where sname != ‘刘晨’ and sdept = (1);

select sname from student where sname != ‘刘晨’ and sdept = (select sdept from student where sname = ‘刘晨’);

17、将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入Student表中

insert into student(sno,sname,ssex,sdept,sage) values(95020,’陈冬’,1,’IS’,18);

18、将学生95001的年龄改为22岁

update student set age=22 where sno = 95001;

19、将计算机科学系全体学生的成绩置零

select sno from student where sdept = ‘计算机科学系’;

update sc set grade = 0 where sno in (1);

update sc set grade = 0 where sno in (select sno from student where sdept = ‘计算机科学系);

20、删除学号为95019的学生记录

delete from student where sno = 95019;

21、删除计算机科学系所有学生的选课记录

select sno from student where sdept = ‘计算机科学系’;

delete from sc where sno in(1);

delete from sc where sno in(select sno from student where sdept = ‘计算机科学系’);

22、查询选修了课程名为“信息系统”的学生学号和姓名

select cno from course where cname = ‘信息系统’;

select sno from sc where cno in(1);

select sno,sname from student where sno in(2);

select sno,sname from student where sno in (select sno from sc where cno in (select cno from course where cname = ‘信息系统’));

23、查询其他系中比IS系任一学生年龄小的学生名单。

select sage from student where sdept = ‘IS’;

select * from student where sdept !=‘IS’ and sage < any(1);

select * from student where sdept !=‘IS’ and sage < any(select sage from student where sdept = ‘IS’);

24、查询student表中的所有信息，将查询结果保存到当前数据库中的新数据表re_stu中。

create table re_stu as student;

insert into re_stu select * from student;

25查询出所有学生的学号、姓名、性别、年龄、所在系，而且请使用中文作为查询结果的各字段的名称

select sno ’学号’,sname ’姓名’,sage ’年龄’,sdept ’所在系’ from student;