大学mysql实训报告手册,我的数据库实验报告册

实验二：基础数据如下

create database XSGL

go

use XSGL

go

create table student

(

sno char(8) primary key,

sname char(4) not null,

ssex char(2) default ‘男’ check(ssex=’男’ or ssex=’女’),

sage int,

sdept char(10) not null

)

create table course

(

cno char(2)constraint PK_course primary key,

cname char(30),

credit int,

cpno char(3)

)

create table sc

(

sno char(8),

cno char(2),

grade int check(grade<=100 and grade>=0),

constraint PK_sc primary key(sno,cno),

constraint Fk1 foreign key(sno) references student(sno),

constraint FK2 foreign key(cno) references course(cno)

)

insert into student(sno,sname,ssex,sage,sdept) values(‘95001’, ‘李勇’, ‘男’, 20, ‘CS’)

insert into student(sno,sname,ssex,sage,sdept) values(‘95002’, ‘刘晨’, ‘女’, 19, ‘IS’)

insert into student(sno,sname,ssex,sage,sdept) values(‘95003’, ‘王敏’, ‘女’, 18, ‘MA’)

insert into student(sno,sname,ssex,sage,sdept) values(‘95004’, ‘张立’, ‘男’, 19, ‘IS’)

insert into student(sno,sname,ssex,sage,sdept) values(‘95005’, ‘刘云’, ‘女’, 18, ‘CS’)

insert into course(cno, cname,credit,cpno) values(‘1’, ‘数据库’, 4, ‘5’)

insert into course(cno, cname,credit,cpno) values(‘2’, ‘数学’, 6, null)

insert into course(cno, cname,credit,cpno) values(‘3’, ‘信息系统’, 3, ‘1’)

insert into course(cno, cname,credit,cpno) values(‘4’, ‘操作系统’, 4, ‘6’)

insert into course(cno, cname,credit,cpno) values(‘5’, ‘数据结构’, 4, ‘7’)

insert into course(cno, cname,credit,cpno) values(‘6’, ‘数据处理’, 3, null)

insert into course(cno, cname,credit,cpno) values(‘7’, ‘PASCAL语言’, 4, ‘6’)

insert into sc(sno,cno,grade) values(‘95001’, ‘1’ ,92)

insert into sc(sno,cno,grade) values(‘95001’, ‘2’ ,85)

insert into sc(sno,cno,grade) values(‘95001’, ‘3’ ,88)

insert into sc(sno,cno,grade) values(‘95002’, ‘2’ ,90)

insert into sc(sno,cno,grade) values(‘95002’, ‘3’ ,80)

insert into sc(sno,cno,grade) values(‘95003’, ‘2’ ,85)

insert into sc(sno,cno,grade) values(‘95004’, ‘1’ ,58)

insert into sc(sno,cno,grade) values(‘95004’, ‘2’ ,85)

实验代码：

alter table student

add scome char(30);

alter table student drop column sdept

alter table sc drop constraint Fk2

alter table sc add constraint FK2 foreign key(cno) references course(cno)

create table a

(

aa char(8) primary key,

ab int

)

drop table a

create unique index sy_sname on student(sname desc)

drop index student.sy_sname

select sno,sname from student

select * from student

select sname,sage,sdept from student where sdept=’CS’–cs是软件，is是计科，ma是数学

select distinct sno from sc

select distinct sno from sc where grade<60

select ssex,sage,sdept from student where sdept not in(‘CS’,’IS’)

select sno,sname,sdept,sage from student where sage between 18 and 20

select * from student where sname like ‘刘%’

select * from student where sname like ‘刘%’ or  sname like ‘李%’

select * from student where sname like ‘刘_’

select sname from student where 2011-sage>1983

create table studentgrad

(

sno char(8) primary key,

mathgrade int,

englishigrade int,

chinesegrade int,

constraint Pk_ks1 foreign key(sno) references student(sno),

constraint ys check (mathgrade>=0 and mathgrade<=100 and englishigrade<=100 and englishigrade>=0 and chinesegrade<=100 and chinesegrade>=0)

)

insert into studentgrad (sno,mathgrade,englishigrade,chinesegrade) values(‘95002’,56,85,90)

insert into studentgrad (sno,mathgrade,englishigrade,chinesegrade) values(‘95003’,89,85,85)

insert into studentgrad values(‘95001′,70,56,89)

select sno,mathgrade+englishigrade+chinesegrade as zong from studentgrad

select year(birth) as birthday from student where sdept=’CS’

由于建立表的时候生日的数据类型不是datetime所以不能查到(基本语法如此，birth是datetime的)

select sname+’年龄为’+str(sage)+’岁’ from student—-此处注意多了一个c

select * from student order by sdept asc,sage desc

select count(*) as ‘学生人数’ from student–或者把*换成sno

select count(distinct(sno))as ‘选了课程人数’ from sc

select count(sno) as ‘选了7号课程的人数’,avg(grade)as ‘平均成绩’ from sc where cno=7

select max(grade) as’选了6号课程的最好成绩’ from sc where cno=6

select sdept as ‘系名’,count(sno) as ‘人数’ from student group by sdept

select distinct(cno),count(sno),avg(grade) from sc group by cno

select cno,cname,credit from course where cpno is null

insert into student(sno,sname,sage) values(‘95030′,’李莉’,18)

insert into sc(sno,cno)values(95030,1)

update student set sage=20 where sdept=’IS’

update sc set grade=0 where sno in(select sno from student where sdept=’MA’)

update sc set grade=grade+5 where sno in(select sno from sc where sno in(select sno from student where ssex=’女’) and grade

update sc set grade=(grade+grade*5/100) where sno in(select sno from sc where cno=2 and grade<75);

update sc set grade=(grade+grade*4/100) where sno in(select sno from sc where cno=2 and grade>75)

delete student where sno=95030

delete sc where grade is null

delete sc where sno in(select sno from student where sname=’张娜’)

delete sc where sno in(select distinct(sno) from sc where grade<60)

delete sc where sno in(select sno from student where sdept=’MA’)

delete course where cno not in(select distinct(cno) from sc)

create table stu(sno char(8),sname char(4),ssex char(2))

insert into stu select sno,sname,ssex from student where sno in(select distinct(sno) from sc) and sno not in(select sno from sc where grade<80)

create table sdeptgrade(sdept char(10),avgvrade int)

insert into sdeptgrade select student.sdept,avg(sc.grade) from student,sc where student.sno=sc.sno group by sdept

select student.*,sc.* from student,sc where student.sno=sc.sno

select A.cno,A.cname,B.cpno from course A,course B where A.cpno=B.cno

select student.*,sc.* from student,sc where (student.sno =* sc.sno) –(部分版本的不支持这句查询)

select student.*,sc.* from student right outer join sc on(student.sno=sc.sno)

select student.sname,student.sno from student where sno in(select sno from sc where cno=2)  intersect select student.sname,student.sno from student where sno in(select sno from sc where cno=3)

select * from student where sage=(select sage from student where sname=’刘晨’)

select sname,sage from student where sno in (select sno from sc where cno=(select cno from course where cname=’数据库’))

select sname from student where sage

select sname from student where sage

select sname from student where sno in (select sno from sc group by sno having count(*)= (select count(*) from course))

select sname from student where not exists (select * from course where not exists (select * from sc where sno=student.sno and cno=course.cno))–这个也可以

select * from student where ssex=’男’ and sdept=’IS’

select * from sc where cno=1 and sno not in(select sno from sc where cno=2)

select cno from course where cno not in (select cno from sc where sno=(select sno from student where sname=’李丽’))

select avg(sage) from student where sno in(select sno from sc where cno=3)

select avg(grade),cno from sc group by cno

select cno,count(*) as ‘rs’ from sc group by cno having count(*)>3 order by rs desc,cno asc

select sname from student where sno>(select sno from student where sname=’刘晨’) and sage

select sname,sage from student where ssex=’男’ and sage>all(select sage from student where ssex=’女’)