★★★数据库练习★★★

1. 创建数据库

create database test on primary

(name = testdata,

filename = 'D:\\java\\Database\\DATA\estdata.mdf', size = 10, maxsize = 500, filegrowth = 5 ) log on

(name = testLog,

filename = 'D:\\java\\Database\\DATA\estLog.ldf', size = 3,

filegrowth = 10% )

2. --创建数据表 use class --选择数据库 go

create table Department --创建表 ( )

use class go

create table Class (

ClassID char(8) primary key, ClassName varchar(20) not null, Monitor char(8),

DepartmentID char(4) primary key, --primary key 是设置主键 DepartmentName varchar(20) not null unique, --unique是设置唯一 DepartmentHeader varchar(8) not null, --not null 是非空 TeacherNum int

StudentNum int check(StudentNum >= 0), --check约束 DepartmentID char(4) foreign key references Department(DepartmentID), --foreign key 定义外键references用于指定外键所引用的表及表的主键 )

use class go

create table Student ( 法

ClassID char(8) references Class(ClassID) --行级约束时可以省略foreign key )

create table Course ( )

create table Grade (

CourseID char(8) references Course(CourseID), StudentID char(12) references Student(StudentID), Semester int not null, SchoolYear int,

Grade numeric(5,1) check(Grade>= 0),

primary key(CourseID, StudentID) --在定义完CourseID和StudentID列后定CourseID char(8) primary key, CourseNmae varchar(60) not null, BookName varchar(80) not null, Credit int not null

StudentID char(12) primary key, StudentName char(8) not null,

Sex char(2) not null check(Sex = '男' or Sex = '女'), Birth date not null, HomeAddr varchar(80),

EntranceTime date default getdate(), -- getdate（）是得到系统时间的方

义主键约束，这种约束为表级约束 )

use class go

create table Teacher (

TeacherID char(8) primary key, TeacherName char(8) not null,

Sex char(2) not null check(Sex = '男' or Sex = '女'), Birth date,

Profession char(8) check(Profession in('教授', '副教授','讲师', '助教

')),--还可以这样写(Profession = '教授' or Profession = '副教授' or Profession = '讲师' or Profession = '助教') Telephone varchar(20),

)

HomeAddr varchar(50),

DepartmentID char(4) references Department(DepartmentID)

create table Schedule ( ) /*

非空not null 唯一unique

默认default 默认值 检查check （约束表达式） 外键reference 表名或列名 标示identity （起始值，增量）

TeacherID char(8) references Teacher(TeacherID), CourseID char(8) references Course(CourseID), ClassID char(8) references Class(ClassID), Semester int not null, SchoolYear int not null,

ScheduleIdent varchar(40) not null, Classroom varchar(20) not null

primary key(TeacherID, CourseID, ClassID)

2 创建表练习

1.创建学生数据库，并在其中创建表： 学生表：Student

(Sno, --主键，10位全为数字，第一位非0 Sname,--唯一

Ssex, --‘男’或‘女’ Sage, --大于0

Sdept—计算机、软件、网络，信息之一 )

课程表：Course (

Cno,--主键，4位全为数字，第一位非0 Cname,--非空

Cpno,--外键—>Cno Ccredit --默认为2 )

学生选课表： SC (

Sno,--外键 ，主键 Cno,--外键 ，主键

pscj—0~100，默认为0.00 qkcj—0~100，默认为0..00 zp pscj*0.4+qkcj*0.6 )

3. 插入数据练习

CREATE TABLE Student

(Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件*/ Sname CHAR(20) UNIQUE, /* Sname取唯一值*/ Ssex CHAR(2), Sage SMALLINT, Sdept CHAR(20) )

CREATE TABLE Course

( Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4) , Ccredit SMALLINT,

FOREIGN KEY (Cpno) REFERENCES Course(Cno) )

CREATE TABLE SC

(Sno CHAR(9) REFERENCES Student(Sno), Cno CHAR(4) REFERENCES Course(Cno), Grade SMALLINT,

PRIMARY KEY (Sno,Cno), ) 200212127 200215121 200215122 200215123 200515125 200515126 1 2 数据库 5 数学 4 2 李四 男 21 21 19 18 20 25 cs CS CS MA IS IS

李勇 男 刘晨 女 王敏 女 张立 男 张三 男 3 4 5 6 7 200215121 200215121 200215121 200215122 200215122 200215122

信息系统 1 操作系统 6 数据结构 7 数据处理 PASCAL语言 6 1 2 3 2 3 5 92 85 88 95 88 80 4 3 4 2 5 4. 查询练习1

1、 查询Student表中的所有记录的S_NAME、S_SEX和Class列。

2、 查询教师所有的单位即不重复的Depart列。 3、 查询Student表的所有记录。

4、 查询Score表中成绩在60到80之间的所有记录。 5、 查询Score表中成绩为85，86或88的记录。

6、 查询Student表中“95031”班或性别为“女”的同学记录。 7、 以Class降序查询Student表的所有记录。

8、 以C_NO升序、Degree降序查询Score表的所有记录。 9、 查询“95031”班的学生人数。

10、查询Score表中的最高分的学生学号和课程号。 11、查询„3-105‟号课程的平均分。

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。 13、查询最低分大于70，最高分小于90的S_NO列。 14、查询所有学生的S_NAME、C_NO和Degree列。 15、查询所有学生的S_NO、C_NAME和Degree列。 16、查询所有学生的S_NAME、C_NAME和Degree列。 17、查询“95033”班所选课程的平均分。

SQL语句练习题参

1、 select S_NAME,S_SEX,Class from Student; 2、 select distinct depart from teacher;

3、 select S_NO as '学号',S_NAME as '姓名',S_SEX as '性别',S_BIRTHDAY as'出生日期',Class as'班号'from student; 或

select S_NO as 学号,S_NAME as 姓名,S_SEX as 性别,S_BIRTHDAY as 出生日期,Class as 班号 from student;

4、 select * from score where degree between 60 and 80; 或select * from score where degree>=60 and degree<=80; 5、 select * from score where degree in (85,86,88); 6、 select * from student where class='95031'or S_SEX='女'; 7、 select * from student order by class desc;

8、 select * from score order by C_NO asc ,degree desc; 或select * from score order by C_NO ,degree desc;

9、 select count(*) as CNT from student where class='95031';

10、select S_NO as '学号',C_NO as '课程号', degree as '最高分' from score where degree=(select max(degree) from score)

11、select avg(degree)as 课程平均分 from score where C_NO='3-105';

12、select C_NO,avg(degree) from score where C_NO like'3%'group by C_NO having count(*) >5; 13、select S_NO from score group by S_NO having min(degree)>70 and max(degree)<90;

14、select student.S_NAME,score.C_NO,score.degree from student,score where student.S_NO=score.S_NO; 15、select x.S_NO,y.C_NAME,x.degree from score x,course y where x.C_NO=y.C_NO;

16、select x.S_NAME,y.C_NAME,z.degree from student x,course y,score z where x.S_NO=z.S_NO and z.C_NO=y.C_NO;

17、select y.C_NO,avg(y.degree) from student x,score y where x.S_NO=y.S_NO and x.class='95033'group by y.C_NO;

5. 查询练习2

1. 2. 3. 4.

列出所有人的信息，包括姓名、课程名、和成绩； 列出李诚所教课程的成绩包括姓名、课程名、和成绩； 列出数据电路的任课老师姓名及所教学生的姓名和成绩； 列出计算机系老师姓名及所教学生的姓名，课程名，成绩；

6 查询练习3

1. 按学号统计每人的平均分；

2. 按学号统计成绩大于80的每人的平均分； 3.按学号统计平均分大于80的每人的平均分； 4. 按姓名统计每人的平均分； 5. 按班级统计每科的平均分； 6. 按班级统计每班的人数； 7. 列出每人的各科成绩及总分

8. 列出每人的各科成绩及每人、每科的总分

7 查询练习4

1、学生表(S_NO，S_NAME，S_SEX，S_BIRTHDAY，CLASS) 2、教师表（T_NO，T_NAME，T_SEX，T_BIRTHDAY，PROF，DEPART）

3、课程表（C_NO， C_NAME， T_NO） 4、成绩表（S_NO ，C_NO ， DEGREE）

1 查询95031班的学生成绩(s_no,c_no,degree) 2 查询王姓学生成绩(s_no,c_no,degree) 3 查询各学生最高成绩(s_no,c_no,degree) 4 查询各科最高成绩(s_no,c_no,degree) 5 按成绩从高到低排名，并显示名次

6 按成绩从高到低排名，并显示名次，只显示前 7 按成绩从高到低进行各科排名，并显示名次 8. 查询最高成绩(s_no,c_no,degree) 9. 查询除最高成绩外的成绩

10. 查询每人最高成绩(s_no,c_no,degree) 11.查询选修了全部课程的学生

12.查询选修了课程门数最少（多）的学生

1 查询95031班的学生成绩 select * from score

where s_no in( select s_no from student where class='95031') 2 查询王姓学生成绩 select * from score

where s_no in( select s_no from student s where s_name like'王%')

3 查询各学生最高成绩(s_no,c_no,degree) SELECT * FROM score

WHERE s_no+str(degree,5,1) IN (SELECT s_no+str(MAX(degree),5,1) FROM score GROUP BY s_no)

4 查询各科最高成绩(s_no,c_no,degree) SELECT * FROM score

WHERE c_no+str(degree,5,1) IN (SELECT c_no+str(MAX(degree),5,1) FROM score GROUP BY c_no)

5 按成绩从高到低排名，并显示名次

select *,row_number() over( ORDER BY degree desc) rank from score sc

6 按成绩从高到低排名，并显示名次，只显示前3 select * from (

select *,row_number() over( ORDER BY degree desc) rank from score sc

) t where rank <4

7 按成绩从高到低进行各科排名，并显示名次

select *,row_number() over( PARTITION BY c_no ORDER BY degree desc) rank from score sc

8. 查询最高成绩(s_no,c_no,degree) select * from score

where degree >=all (select degree from

score)

select * from score

where degree = (select max(degree) from score)

9. 查询除最高成绩外的成绩(s_no,c_no,degree) select * from score

where degree select * from score

where degree <> (select max(degree) from score)

10. 查询每人最高成绩(s_no,c_no,degree) select * from score sc1

where degree = (select max(degree) from score sc2 where sc2.s_no=sc1.s_no) select * from score sc1

where degree >=all (select degree from score sc2 where sc2.s_no=sc1.s_no) 11.查询选修了全部课程的学生 select s_no,s_name

from student where not exists (select * from course where not exists (select *

from score sc

where sc.s_no=student.s_no and sc.c_no=course.c_no))

select s_no,s_name from student s

where (select count(*) from score sc where s.s_no=sc.s_no)=(select count(*) from course)

12.查询选修了课程门数最少（多）的学生 select s_no,s_name from student s

where (select count(*) from score sc where s.s_no=sc.s_no)<=all(select count(*) from score group by s_no)

select s_no,s_name from student s

where (select count(*) from score sc where s.s_no=sc.s_no)>=all(select count(*) from score group by s_no)

select s_no,s_name from student s

where (select count(*) from score sc where s.s_no=sc.s_no)

=( select max(c)from ( select count(*)from score group by s_no) t)

select s_no,s_name from student s

where (select count(*) from score sc where s.s_no=sc.s_no)

=( select min(c)from ( select count(*)from score group by s_no) t)

7 修改数据练习

c c

1. 把教师表中职称为助教的教师的职称改为讲师 2. 把陆君的出生日期改为1995-8-20

3. 把所有人的成绩增加5，最多不超过100 （case...） 4. 把所有人计算机导论的成绩增加10%，最多不超过100 5. 把曾华的数据电路成绩增加10%，最多不超过100 6. 把操作系统成绩男生的减10，女生的减5，最低不小于0 7. 把李诚职称改为教授，系改为信息系 把score中的数据备份到sc

8. 删除sc中学号为‘101’课号为‘3-105’和‘3-245’的成绩

9. 删除sc中李诚所授课程的成绩 10.删除sc中曾华的数据电路成绩

6. 把操作系统成绩男生的+10，女生的+5，最高不大于100

update sc set degree=case s_sex when '男' then

case when degree+10<100 then degree+10 else 100 end

else

case when degree+5<100 then degree+5 else 100 end end

from student s join sc on s.s_no=sc.s_no

8 创建视图练习

1.创建视图V_cj,包括姓名，课程名、成绩及任课教师姓名

create view V_cj (姓名,课程名,成绩,教师姓名) as

select s_name,c_name,degree ,t_name from score sc join student s on s.s_no=sc.s_no

join course c on c.c_no=sc.c_no join teacher t on t.t_no=c.t_no

select * from V_cj

2. 创建视图V_js, 包括教师姓名、性别、部门、所授课程名（WITH ENCRYPTION，并查看WITH ENCRYPTION结果） select * from teacher

create view V_js WITH ENCRYPTION as

select t_name,t_sex,depart ,c_name from teacher t

join course c on c.t_no=c.t_no

select * from syscomments c join sysobjects o on o.id=c.id where name in('V_js','V_cj')

3. 创建视图V_yx, 包括成绩大于的S_NO，C_NO，DEGREE（WITH CHECK OPTION，并检验该选项） create view V_yx as

select * from score where degree>80 WITH CHECK OPTION

4创建视图V_ts, 包括所有教师和学生的编号、姓名、性别

create view V_ts as

select s_no,s_name,s_sex from student union

select t_no,t_name,t_sex from teacher

9 创建存储过程练习

1.创建存储过程p_cj,列出所有人的姓名，课程名、成绩

create proc p_cj as

select s_name,c_name,degree from score sc join student s on s.s_no=sc.s_no

join course c on c.c_no=sc.c_no

exec p_cj

2创建存储过程p_ccj,列出给定班级的姓名，课程名、成绩（班级为参数，未给值，取）

create procedure p_ccj @class varchar(10) as

select s_name,c_name,degree from score sc join student s on s.s_no=sc.s_no

join course c on c.c_no=sc.c_no where class=@class

p_ccj '95031'

3 创建存储过程p_acj,列出给定班级的姓名，课程名、成绩（班级为参数，未给值，取所有班级） create procedure p_acj @class varchar(10)='%' as

select s_name,c_name,degree from score sc join student s on s.s_no=sc.s_no

join course c on c.c_no=sc.c_no where class like @class p_acj '95031' p_acj

4 创建存储过程p_bcj,列出给定班级的姓名，课程

名、成绩（班级为参数，未给值，取），并输出班级的最高分（output选项）

create procedure p_bcj @class varchar(10)='95031' ,@mc decimal(4,1) output as

select s_name,c_name,degree from score sc join student s on s.s_no=sc.s_no

join course c on c.c_no=sc.c_no where class = @class

select @mc=max(degree) from score sc join student s on s.s_no=sc.s_no where class = @class

declare @mcj decimal(4,1)

--execute p_bcj '95033', @mc=@mcj output

--execute p_bcj @mc=@mcj output --execute p_bcj @mc=@mcj execute p_bcj @mc=@mcj output,@class='95033'

select @mcj

5 自己查看WITH ENCRYPTION ，WITH RECOMPILE选项的意思

10 创建函数练习

1.创建函数f_max返回两数的大者

create function f_max(@a int,@b RETURNS int as begin

declare @c int if @a>@b set @c=@a else

select @c=@b RETURN(@c) end

) intselect dbo.f_max(11,23)

2 .创建函数f_cj, 返回包括姓名，课程名、成绩的成绩表

CREATE FUNCTION f_cj () RETURNS TABLE AS

RETURN (select s_name,c_name,degree from score sc join student s on s.s_no=sc.s_no

join course c on c.c_no=sc.c_no)

select * from dbo.f_cj()

3. 创建函数f_stc, 返回学生、教师、课程的信息，包括编号（学号、教师号、课号）、名称（姓名，课程名），并且学生的编号前加S，教师的编号前加T，课程的编号前加C

CREATE FUNCTION f_stc()

RETURNS @stc TABLE ([No] char(10) primary key,

[name] nchar(10)) AS BEGIN insert @stc

select s_no,s_name from student update @stc set [No]='s_'+rtrim([no])

insert @stc

select t_no,t_name from teacher

update @stc set [No]='t_'+rtrim([no]) where substring([No],1,1)<>'s'

insert @stc

select c_no,c_name from course

update @stc set [No]='c_'+rtrim([no]) where [No] like '[0-9]%' RETURN end

select * from f_stc()

11创建触发器练习

1. 当某商品库存量小于，给出预警信息，该商品库存不多，准备进货；

CREATE TRIGGER T_jg on Orderdetail for update,insert as

if EXISTS (

select *from inserted I join kc on kc.pid=i.pid WHERE kc.Quantity -i.Quantity <10) print '该商品库存不多，准备进货'

insert Orderdetail values('101','102',3)

2. 有进货时，自动增加相应的库存；

CREATE TRIGGER T_jh on Stockdetail

for update,insert,delete as

insert kc select pid, 0 from inserted i

where not exists ( select * from kc where i.pid=kc.pid )

update kc set kc.Quantity=kc.Quantity - d.Quantity from kc join deleted d on d.pid=kc.pid

update kc set kc.Quantity=kc.Quantity + i.Quantity from kc join inserted i on i.pid=kc.pid

insert dbo.Stockdetail values('101','102',5)

3. 有出货时，自动减少相应的库存；

CREATE TRIGGER T_ch on dbo.Orderdetail for update,insert,delete as

update kc set kc.Quantity=kc.Quantity +d.Quantity from kc join deleted d on d.pid=kc.pid

update kc set kc.Quantity=kc.Quantity - i.Quantity from kc join inserted i on i.pid=kc.pid

4. 当某商品库存存量小于销售量时，提示相关信息，并回滚本次操作。

CREATE TRIGGER T_check on dbo.Orderdetail for update,insert as

if not EXISTS (

select *from kc join inserted i on i.pid=kc.pid) begin rollback

print '该商品库存存在，本次订单无效' end

if EXISTS (

select *from kc join inserted I on i.pid=kc.pid WHERE kc.Quantity- i. Quantity <0) begin rollback

print '该商品库存不够，本次订单无效' end

5.对于视图V_CJ(姓名，课程名，成绩)，同时修改姓名和课程名

create TRIGGER Vcj_update ON V_cj instead of UPDATE AS

SET NOCOUNT ON

IF (SELECT COUNT(*) FROM Deleted) > 1 BEGIN

RAISERROR( 'You cannot delete more than one record at a time.', 16, 1) ROLLBACK TRANSACTION END

update stuDENT set S_name=(select 姓名from inserted)

where S_name =(select 姓名from deleted) update COURSE set c_name=(select 课程名from inserted)

where c_name =(select 课程名from deleted)

select * from V_cj

update V_cj set 姓名='陆君', 课程名='操作系统'

where 姓名='陆君君' and 课程名='操作系统'