Mysql

基本练习

• 创建一个student表，要求如下：
  (1)student(sno,sname,sage,ssex,sdept),其中sno为学号，设置为主键；
  （2）sage为年龄字段，要求只能输入0-120之间的数值。
  （3）ssex为性别字段，该字段的约束条件为：只能为男或女。
  （4）sdept为所在系别字段，默认值为”计算机”。
  （5）sname字段不能为空。
  2、向student表中添加一个属性列：籍贯，默认值为：安阳
  3、向student表中插入一条记录：学号为‘00110’，姓名为：“刘畅”，其它为默认值。
  4、将课程号为‘c02’的所占学分设置为‘3’学分。
  5、将男生信息表中的年龄不小于24岁的学生信息删除。
  6、查询单名且姓“刘”的同学的信息
  7、查询选修了“C03”号课程的学生学号及其成绩，查询结果按成绩升序排列，成绩相同的按年龄的降序排序。
  8、查询CS系所有男同学考C05课程的成绩，列出这些学生的学号，姓名，成绩并按成绩降序排列。
  9、检索选修了‘数据库’课程的学生的姓名。
  10、检索选修了课程号为C01或C02的课程，且成绩高于或等于70分的学生的姓名，课程名和成绩。
  11、查询3人以上选修的每一门课的平均分，最高分，最低分；
  12、查询每门课程的平均分，最高分和最低分。
  13、查询年龄不大于吕凯的男生的学生信息。

create database Test2

use Test2

create table student(
    sno char(8) primary key,
    sage smallint check (sage between 0 and 120),
    ssex char(4) check (ssex in ('男','女')),
    sdept char(8) default '计算机',
    sname char(8) not null
)

alter table student add 籍贯 char(10) default '安阳'

select * from student

insert into student (sno,sname) values ('00110','刘畅')

use Test

update course set credit=3 where cno='c02'

delete from student where ssex='m' and sage>=24

select * from student where sname like '刘_'

select sc.sno,grade from student,sc where student.sno=sc.sno and cno='c03' order by grade,sage DESC

select sc.sno,sname,grade from student,sc where student.sno=sc.sno and sdept='cs' and ssex='m'
and cno='c05' order by grade DESC

select sname from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
and cname='数据库'

select sname from student where sno in 
(select sno from sc where cno in 
(select cno from course where cname='高数'))

select sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
and sc.cno in ('c01','c02') and grade>=70

select AVG(grade),MAX(grade),MIN(grade) from sc group by cno having COUNT(*)>3

select cno,AVG(grade) 平均分,MAX(grade) 最高分,MIN(grade) 最低分 from sc group by cno

select * from student where sage < (select sage from student where sname='吕凯') and ssex='m'

存储过程

1、建立一个存储过程P1，查询某门课程平均分数（课程号，平均成绩）(带一个参数)。
2、建立一个存储过程P2可以给COURSE表中添加记录（三个参数）。

1、创建一个sc表，要求如下：
（1）sc(sno,cno,grade),其中主键为sno和cno；
（2）成绩grade取值只能在0-100之间。
（3）成绩的默认值为0

2、将student表中女生的年龄均增加一岁（使用update）

3、向sc表中插入一条记录：学号为‘00110’，课程号为：“c03”，其它为默认值。

4、将课程号为‘c08’的所占学分设置为‘3’学分。

5、将学生信息表student中男生的年龄在21-24岁的学生信息删除。

6、查询姓“王”的同学的信息

7、查询班内年龄最小的三位同学的信息

8、查询选修了“C03”号课程的学生学号及选修课程及相应成绩，查询结果按成绩升序排序。

8、查询ms系所有女同学考C05课程的成绩，列出这些学生的学号，姓名，成绩并按成绩降序排列。

9、检索选修了‘数据库’课程的学生的学号，年龄，所选课程名及相应成绩等信息。

10、检索同时选修了课程号为C03或C05两门课程，且两科成绩均不低于70分的学生的相应信息。

11、查询选修了2门以上课程的学生学号。

12、查询每位同学的的平均分，最高分和最低分。

13、查询年龄不小于刘姗姗的男生的学生信息。

14、查询选修了课程的学生人数。

15、查询没有选课的学生的信息。

use Test

create proc p1(@cno char(8)) as 
select cno, AVG(grade)平均分数 from sc where cno=@cno group by cno

exec p1 @cno='c02'

create proc p2(@cno char(10),@cname varchar(20),@credit smallint) as
insert into course (cno,cname,credit) values (@cno,@cname,@credit)

exec p2 @cno='c24', @credit=99, @cname='蒋正阳'
exec p2 'c23', '蒋正阳', 8
select * from course where cname='蒋正阳'

create table sc2(
    sno char(10),
    cno char(10),
    grade smallint check(grade between 0 and 100) default 0,
    primary key(sno,cno)
)

drop table sc2

update student set sage=sage+1 where ssex='f'
update student set sage=sage-1 where ssex='f'
select * from student where ssex='f'

insert into sc (sno,cno) values ('00110','c03')

update course set credit=3 where cno='c08'

select * from student where ssex='m' and sage between 21 and 24
delete student where ssex='m' and sage between 21 and 24

select * from student where sname='王%'

select top 3 * from student order by sage

select sno,cno,grade from sc where cno='c03' order by grade

select student.sno,sname,grade from student, sc where student.sno=sc.sno and sdept='cs' and ssex='f' and cno='c05'
 
select sc.sno,sage,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno and cname='毛泽东思想'

select * from student,sc where student.sno=sc.sno and cno in ('c03','c05') and grade > 70

select sno from sc group by sno having count(*)>2

select avg(grade),max(grade),min(grade) from sc group by sno

select * from student where sage >= (select sage from student where sname='刘珊珊')

select count(cno) from student left join sc on student.sno=sc.sno where cno is not null

select * from student left join sc on student.sno=sc.sno where cno is null