实验源码.docx

1、实验源码实验18、（3）select type,avg(price)from titleswhere royalty=10group by type实验24、create database studbexec sp_helpdb5、alter database studbmodify file(NAME=studb,SIZE= 5MB,MAXSIZE=20MB,FILEGROWTH=1MB)6、alter database studbADD LOG FILE(NAME= studb_log2,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQLda

2、tastudb_log2.ldf,SIZE= 5MB,MAXSIZE=10MB)7、alter database studbMODIFY NAME =student_db（错误：未能排它地锁定数据库以执行该操作。：退出后重新执行）8、drop database student_db实验3 5、use StudentsDB create table grade(学号char(4),课程编号char(4),分数decimal(5)8、use studentsdbinsert into gradevalues(0004,0001,80)9、alter table curriculum alter c

3、olumn 课程编号 char(4) not null10、use studentsdbalter table grade alter column 分数 real11、alter table student_info alter column 学生姓名 varchar(10) not null12、Delete grade where 学号=000413、use studentsdbupdate grade set 分数=90 where 学号=0003 and 课程编号=000514、use studentsdbalter table gradeadd 备注varchar(20) NULL

4、实验思考：1、use studentsdbalter table gradedrop column 备注实验41、use studentsdbselect count(*) from gradeselect substring(姓名,1,2) from student_infoselect upper(kelly)select replicate(kelly,3)select sqrt(分数) from grade where 分数=85select 2,3,power(2,3)select year(getdate(),month(getdate(),day(getdate()2、use s

5、tudentsdbselect 学号,姓名,出生日期from student_infoselect 姓名,家庭住址from student_info where 学号=0002select 学号,姓名from student_info where 性别=男3、use studentsdbselect 学号,分数from grade where 分数between 80 and 90select avg(分数) from grade where 学号=0003select 课程编号,count(*) from grade group by 课程编号select * from Student_in

6、fo order by 出生日期select 学号,姓名from student_info where 姓名like 张%4、use studentsdbselect 姓名,出生日期from student_info where 性别= (select 性别from student_info where 姓名=刘卫平)select 学号,姓名,性别from student_info where 学号 in (select 学号from grade where 课程编号in (0002,0005)select 课程编号,分数from grade where 学号=0001 and 分数 any

7、(select 分数from grade where 学号=0002)select 课程编号,分数from grade where 学号=0001 and 分数 all (select 分数from grade where 学号=0002)5、use studentsdbselect student_info.学号,姓名,分数from student_info,grade where student_info.学号=grade.学号and 分数between 80 and 90select student_info.学号,姓名,分数from student_info,grade,curricu

8、lum where student_info.学号=grade.学号and grade.课程编号=curriculum.课程编号and 课程名称=C语言程序设计select student_info.学号,姓名,分数from student_info inner join grade on student_info.学号=grade.学号 inner join curriculum on 课程名称=C语言程序设计 and grade.课程编号=curriculum.课程编号select student_info.学号,姓名,课程名称,分数from student_info,grade,curr

9、iculum where student_info.学号=grade.学号and grade.课程编号=curriculum.课程编号and 性别=男错：select student_info.学号,姓名,课程编号,分数from student_info,grade where student_info.学号=grade.学号and 分数in (select max(分数) from grade group by 学号)select student_info.学号,姓名,课程编号,分数 from grade x inner join student_info on x.学号=student_i

10、nfo.学号 where 分数=(select max(分数) FROM grade y where y.学号=x.学号)select student_info.学号,student_info.姓名,sum(分数) as 总分数 from student_info LEFT OUTER JOIN grade ON student_info.学号=grade.学号 group by student_info.学号,student_info.姓名insert into gradevalues(0004,0006,76,NULL)select grade.课程编号,课程名称,count(*) as

11、选修人数 from curriculum RIGHT OUTER JOIN grade ON curriculum.课程编号=grade.课程编号 group by grade.课程编号,课程名称select 学号 as u_编号,姓名 as u_名称 from student_info where 姓名 like 张%UNIONselect 课程编号 as u_编号,课程名称 as u_名称 from curriculum7、CREATE TABLE totalgrade(学号 char(4) NOT NULL,姓名 varchar(8) NULL,总成绩 decimal(5,2) NULL

12、)INSERT INTO totalgrade SELECT 学号,姓名,总成绩=0 FROM student_infoUPDATE totalgrade SET 总成绩= (SELECT SUM(分数) FROM grade WHERE totalgrade.学号=grade.学号)DELETE totalgrade WHERE 总成绩 IS NULL实验思考：1、select 学号,姓名,性别 from student_info where 学号 not in (select 学号 from grade)2、select * from student_info cross join gra

13、de3、select * from grade order by 学号 compute sum(分数) by 学号实验53、CREATE INDEX grade_index ON grade(分数)4、CREATE UNIQUE INDEX grade_id_c_ind ON grade(学号,课程编号)5、sp_helpindex gradesp_helpindex student_info7、sp_rename grade.grade_index,grade_ind,INDEX8、DROP INDEX grade.grade_ind16、CREATE VIEW v_stu_cAS SELE

14、CT student_info.学号,姓名,课程编号 FROM student_info INNER JOIN grade ON student_info.学号=grade.学号SELECT * FROM v_stu_c WHERE 学号=000317、CREATE VIEW v_stu_gAS SELECT student_info.学号,姓名,课程名称,分数 FROM student_info,curriculum,grade WHERE student_info.学号=grade.学号 and grade.课程编号=curriculum.课程编号select * from v_stu_g

15、 where 学号=000118、ALTER VIEW v_stu_c(学号,姓名,课程数目) AS SELECT student_info.学号,姓名,count(*) as 课程数目 FROM student_info,grade WHERE student_info.学号 = grade.学号 group by student_info.学号,姓名19、ALTER VIEW v_stu_i(学号,姓名,性别)AS SELECT 学号,姓名,性别 FROM student_info20、Sp_rename v_stu_i,v_stu_info21、INSERT INTO v_stu_inf

16、oVALUES(0015,陈婷,女)22、delete v_stu_info where 学号=001523、UPDATE v_stu_g SET 分数=84 WHERE 姓名=刘卫平 and 课程名称=高等数学24、DROP VIEW v_stu_cDROP VIEW v_stu_g实验61、(1)SELECT * INTO stu_phone FROM student_infoALTER TABLE stu_phone ADD 电话号码 CHAR(7) NULL(2)CREATE RULE phone_rule ASphone LIKE 0-90-90-90-90-90-90-9(3)sp

17、_bindrule phone_rule,stu_phone.电话号码(4)INSERT INTO stu_phone(学号,姓名,电话号码) VALUES(0009,王国强,1234yyy)服务器: 消息 513，级别 16，状态 1，行 1列的插入或更新与先前的 CREATE RULE 语句所强制的规则冲突。该语句已终止。冲突发生于数据库 studentsdb，表 stu_phone，列 电话号码。语句已终止。3、CREATE RULE stusex_ruleAS sex IN (男,女)sp_bindrule stusex_rule, stu_phone.性别4、sp_help stus

18、ex_rulesp_helptext stusex_rulesp_rename stusex_rule,stu_s_rule5、sp_unbindrule stu_phone.性别drop rule stu_s_rule6、CREATE DEFAULT df_date AS 2006-4-12GOCREATE DEFAULT df_charAS unknownGOCREATE DEFAULT df_moneyAS $100GOCREATE TABLE stu_fee(学号 char(10) NOT NULL,姓名 char(8) NOT NULL,学费 money,缴费日期 datetime,

19、电话号码 char(7)GOsp_bindefault df_money,stu_fee.学费GOsp_bindefault df_date,stu_fee.缴费日期GOsp_bindefault df_char,stu_fee.电话号码GOINSERT INTO stu_fee(学号,姓名) values(0001,刘卫平)INSERT INTO stu_fee(学号,姓名,学费) values(0001,张卫民,$120)INSERT INTO stu_fee(学号,姓名,学费,缴费日期) values(0001,马东,$110,2006-5-12)sp_unbindefault stu_

20、fee.电话号码DROP DEFAULT df_charsp_unbindefault stu_fee.缴费日期DROP DEFAULT df_dateGOsp_unbindefault stu_fee.学费DROP DEFAULT df_money8、ALTER TABLE student_infoADD 院系 char(10)CREATE DEFAULT stu_d_dfAS 信息院GOsp_bindefault stu_d_df,student_info.院系GOINSERT INTO student_info(学号,姓名,性别)VALUES(0010,asfga,女)9（1）CREAT

21、E TABLE studentsdb.dbo.stu_con(学号 char(4) NOT NULL,姓名 varchar(10) NOT NULL CONSTRAINT uk_name UNIQUE NONCLUSTERED,性别 varchar(2) NOT NULL CONSTRAINT df_sex DEFAULT 男 ,出生日期 datetime NOT NULL CONSTRAINT ck_bday CHECK (出生日期1988-1-1),家庭住址 varchar(50) NOT NULL,CONSTRAINT pk_sid PRIMARY KEY (学号)INSERT INTO

22、 stu_con(学号,姓名,性别,出生日期,家庭住址) values (0009,张小东,1989-4-6,)INSERT INTO stu_con(学号,姓名,性别,出生日期,家庭住址) values (0010,李梅,女,1989-4-6,)INSERT INTO stu_con(学号,姓名,性别,出生日期,家庭住址) values (0011,王强,1989-4-6,)INSERT INTO stu_con(学号,姓名,性别,出生日期,家庭住址) values (0012,王强,1989-4-6,)服务器: 消息 2627，级别 14，状态 2，行 1违反了 UNIQUE KEY 约束

23、 uk_name。不能在对象 stu_con 中插入重复键。语句已终止。不一致，学号列设为主键约束，不允许为空值，并且不能有相同学号出现。姓名为唯一约束，则不能出现相同姓名，性别为默认约束，在没有指定性别时，默认为男，出生日期为属性值约束，限制年龄输入值。（3）ALTER TABLE stu_conDROP CONSTRAINT pk_sid,uk_name,df_sex,ck_bday实验71、DECLARE stu_name varchar(10)SELECT stu_name=姓名FROM student_infoWHERE 姓名 LIKE 张%SELECT stu_name2、DECL

24、ARE grademax int,grademin int,gradesum intSELECT grademax = max(分数),grademin = min(分数),gradesum = sum(分数)FROM gradeSELECT grademax,grademin,gradesum3、DECLARE rows intSET rows = (SELECT COUNT(*) FROM grade)SELECT rows4、DECLARE intCId int,intErrorCode intINSERT INTO curriculum(课程编号,课程名称,学分)VALUES(0006

25、,VB程序设计,2)SELECT intCId=identity,intErrorCode=errorSELECT intCId,intErrorCode5、DECLARE Msum int,Fsum intSELECT Msum=COUNT(*) FROM student_info WHERE 性别=男SELECT Fsum=COUNT(*) FROM student_info WHERE 性别=女SELECT Msum,Fsum6、DECLARE grademax int,grademin int,gradeavg intSELECT grademax = max(分数),grademin

26、 = min(分数),gradeavg = avg(分数)FROM grade INNER JOIN curriculum ON grade.课程编号 = curriculum.课程编号 and 课程名称=高等数学SELECT grademax,grademin,gradeavg9、DECLARE stu_id char(4)SET stu_id = 0001IF (SELECT AVG(分数) FROM grade where 学号=stu_id)60) PRINT 你的成绩不及格ELSE PRINT 你的成绩及格了，恭贺你10、DECLARE counter intSET counter=

27、1WHILE counter=90 THEN AWHEN 分数=80 AND 分数=70 AND 分数=60 AND 分数70 THEN DELSE EENDFROM grade12、WHILE (SELECT AVG(分数) FROM grade)95 BREAKELSE CONTINUEEND13、DECLARE n int,counter intSET counter=1SET n=0WHILE counter=20BEGIN SET n=n+counter SET counter=counter+1ENDSELECT n14、DECLARE n intSET n=1WHILE n=10

28、BEGIN SELECT n*n SET n=n+1END16、DECLARE stu_cursor CURSORSCROLLFOR SELECT * FROM student_info WHERE 性别=男 FOR UPDATEOPEN stu_cursorFETCH NEXT FROM stu_cursorWHILE fetch_status = 0BEGIN FETCH NEXT FROM stu_cursorENDCLOSE stu_cursor17、OPEN stu_cursorDECLARE sno char(4),sname varchar(10)FETCH NEXT FROM stu_cursor into sno,snameWHILE fetch_status = 0BEGIN if (sname like 马%) update student_info set 出生日期=dateadd(yy,1,出生日期) WHERE CURRENT OF stu_cursor FETCH NEXT FROM stu_cursor into sno,snameENDCLOSE stu_cursor18、DECLARE stu_cursor CURSOR