SQL操作作业0614.docx

SQL Server 2008数据库创建、建表、查询语句一、创建数据库 创建数据库student的T-SQL语句如下：create database JXGLon primary(name=jxgl_data,filename=E:workjxgl_data.mdf,size=3MB,maxsize=unlimited,filegrowth=1MB)Log on(name=jxgl_log,filename=E:workjxgl_log.ldf,size=1MB,maxsize=20MB,filegrowth=10%)二、创建数据表1、学生基本信息表：create table student(st_id varchar(9) primary key,st_name char(8) null,st_birth date null,st_sex nchar(2) constraint checksex check(Stu_Sex in(男,女),st_add nvarchar(20) null,depid_id char(3) not null,st_pwd varchar(10) null) 2、课程信息表： create table subject(sub_no nvarchar(5) primary key,sub_name varchar(20) null,sub_preno nvarchar(5) null,sub_credit int null)3、学生成绩表： create table score(st_id varchar(9),sub_no nvarchar(5),st_score int,primary key(st_id,sub_no),foreign key(st_id) references student(st_id),foreign key(sub_no) references subject(sub_no)4、系别表：create table depid(depid_id char(3),depid_name nvarchar(10) null,)5、数据库关系图：三、查询语句单表查询查询全体学生的基本信息select*from student2、查询学生姓名及其出生年份select st_name,YEAR(st_birth)as 出生年份 from student3、查询年龄小于等于25岁的学生姓名和年龄select st_name,YEAR(GETDATE()-YEAR(st_birth) from student where YEAR(GETDATE()-YEAR(st_birth)254、查询考试成绩有不及格的学生的学号select distinct st_id from score where st_score605、查询年龄不在1922岁之间的学生姓名和年龄select st_name,year(GETDATE()-year(st_birth) as 年龄 from studentwhere year(GETDATE()-year(st_birth) not between 19 and 226、查询数据库、软件技术课程的学分和先修课程号select sub_no,sub_credit,sub_preno from subjectwhere sub_name in(数据库,软件技术)7、查询所有姓张的学生信息select * from student where st_name like 张%8、查询学生名字中第二个字为“洁”字的学生的姓名和学号select * from student where st_name like _洁%9、查询名字为“英”且全名为两个汉字的学生的姓名select st_name from student where st_name like _英10、查询所有不姓王的学生信息select * from student where st_name not like 王%11、查询缺少学习成绩的学生的学号和相应的课程号select st_id,sub_no,st_score from score where st_score is null12、查询选修课程号为“012”的不及格的学生学号select st_id from score where sub_no=012 and st_score6023、各系部按平均分排序select depid_id,AVG(st_score) from student,scorewhere student.st_id=score.st_idgroup by depid_idorder by AVG(st_score)24、查询选修了课程号名为“数据库”的学生的学号和姓名select st_id,st_name from studentwhere st_id in(select st_id from score where sub_no in(select sub_no from subject where sub_name=数据库)25、查询与汪卫在同一个系学习的学生学号和姓名select st_id,st_name from student as st1where st1.depid_id in (select depid_id from student as st2 where st2.st_name=汪卫)26、查询所有选修课程号为“015”的课程的学生学号及姓名select st_id,st_name from studentwhere EXISTS(select * from score,student where score.st_id=student.st_id and score.sub_no=015)27、查询未被选修的课程号和课程名称select sub_no,sub_name from subject as suwhere not exists(select * from score where sub_no=su.sub_no)28、查询选修课程号为“016”成绩前5位同学的学号、姓名和成绩信息select top 5 student.st_id,st_name,st_score from student,scorewhere score.st_id=student.st_id and sub_no=016order by st_score desc29、查询60%同学的学号、姓名和成绩信息。select top 60 percent st_id,st_name,st_birth from student30、查询这周过生日的人select st_id, st_birth from studentwhere ( datediff(d,getdate(),Rtrim(year(getdate()+-+Rtrim(month(st_birth)+-+Rtrim(day(st_birth)=0 )31、在加入年月日select CONVERT(date, st_birth, 120 ),left(CONVERT(varchar, st_birth, 120 ),4)+年+SUBSTRING(CONVERT(varchar, st_birth, 120 ),6,2)+月+right(CONVERT(varchar, st_birth, 120 ),2)+日,str(year(CONVERT(date, st_birth, 120 ),4)+年+str(month(CONVERT(date, st_birth, 120 ),2)+月+ltrim(str(day(CONVERT(date, st_birth, 120 )+日from student32、生日33、生日大于平均年龄declare a intset a= (select AVG(YEAR(GETDATE()-YEAR(st_birth) from student)select st_name,YEAR(GETDATE()-YEAR(st_birth)from studentwhere YEAR(GETDATE()-YEAR(st_birth)agroup by St_Name,YEAR(GETDATE()-YEAR(st_birth)select AVG(YEAR(GETDATE()-YEAR(st_birth) from student四、数据更新数据插入1、向学生信息表中插入一条新纪录insert into student values(115011201,高达,1991-11-1,男,云南,01,lyx)插入一条选课记录insert into score values(105071011,015,null)对每一个系，查询学生的平均年龄，并把结果存入数据库create table classage(depid_id char(3) not null,avgage int);insert into classage(depid_id,avgage)select depid_id,AVG(YEAR(getdate()-year(st_birth) from student group by depid_id将所有女生的信息存进表stutt里select * into stutt from student where st_sex=女select * from stutt数据修改把学号为“115011201”的学生的性别修改为“女”update student set st_sex=女 where st_id=115011201将低于平均成绩的课程号为“015”的课程的成绩提高10%update score set st_score=st_score*1.1 where sub_no=015andst_score=60 and sub_no=0153、撤销view2视图drop view view24、向视图view1中插入一条新纪录insert into view1 values(115042203,华容,1992-9-8)5、将view1中学号为“115042203”的学生的出生日期改为1991-9-8update view1 set st_birth=1991-9-8 where st_id=1150422036、删除视图view1中学号为“115042203”的记录delete from view1 where st_id=115042203查询“01”系选修“013”课程号的学生的学号和姓名select view1.st_id,st_name from view1,scorewhere view1.st_id=score.st_id and sub_no=013添加视图检查Create View view009AsSelect *From studentWhere st_sex=女WITH CHECK OPTION视图加密Create View stuidentviewWITH ENCRYPTION -视图加密语句AsSelect *From score六、索引，约束1、在student表的st_name列上创建一个索引create unique index studentName on student(st_name)2,、删除student表的studentName索引drop index student.studentName3、在student表中加入身份证号identity_id列，数据类型为字符型alter table student add identity_id char(18)4、为新添加的identity_id列添加缺省约束，约束名为checkide，缺省值为ALTER TABLE student ADD CONSTRAINT checkide defaultfor identity_id5、删除student表中的checkide缺省值约束alter table student drop constraint checkide删除student表中新增的列identity_idalter table student drop column identity_id七、游标1、使用游标，将游标变量值打印declare a varchar(10)declare b varchar(10)declare stu_cur scroll cursor for -声明游标(这个是可回滚的)select st_id,st_name from studentopen stu_cur -打开游标fetch last from stu_cur into a,b -提取数据集的最后一行print a+bfetch prior from stu_cur into a,b -提取当前游标所在行的上一行print a+bfetch absolute 4 from stu_cur into a,b -提取数据集中的第行print a+b fetch relative -1 from stu_cur into a,b -提取当前行的前行 print a+bclose stu_cur -关闭游标deallocate stu_cur -释放游标2、生日declare stu_cur cursorforselect st_name from student where month(st_birth)=month(getdate()open stu_curdeclare who char(10)fetch next from stu_cur into whowhile(FETCH_STATUS=0)beginprint who+这个月过生日祝福吧fetch next from stu_cur into whoendclose stu_curdeallocate stu_cur3、超过100改为100useJXGLgodeclare mini intwhile (select MIN(st_score) from stutest)60beginset mini=(select MIN(st_score) from stutest)UPdate stutest Set st_score = st_score + 5 where mini =100if (select MIN(st_score) from stutest)=60break八、存储过程1、无参数的存储过程1、查找系别为“04”的学生create proc od_sp -存储过程名asselect * from studentwhere depid_id=042、有一个参数的存储过程2、查询“03”系的人的全部信息create proc odp_sp a intasselect * from studentwhere depid_id=a3、带默认参数的存储过程3、查询某系的人的信息create proc checka result int=nullasif result is nullbeginselect * from studentwhere depid_id=04endelsebeginselect * from studentwhere depid_id=resultend4、带输出参数的存储过程4、输出“04”的人的信息和总人数create proc stu_sumstu_spe varchar(20)=%,sum int outputasselect * from studentwhere depid_id like stu_speselect count(st_id) from studentwhere depid_id like stu_spego5、带输出结果的存储过程5、查询“03”“04”的总人数create proc op_spa int,b int,out int outputasselect out=COUNT(st_id) from studentwhere depid_id=a or depid_id=bdeclare c intexec op_sp 03,04,c outputprint 总人数print c九、触发器1、Delete触发器create trigger studelete on student after deleteasselect * from deleted2、Insert 触发器create trigger stuinserton studentafter insertasdeclare type intset type=(select depid_id from inserted)if type=1print 这个专业是空elsebegin print The type is :转专业的 end3、Update 触发器create trigger stuupdateon studentafter updateasif update(St_id)begin raiserror(数据不能被修改,10,1) rollback transactionend4、使表上的触发器无效或重新有效4.1 使之无效alter table stutestdisable trigger stuupdate4.2 使重新有效alter table stutestenable trigger stuupdate十、函数1、计算长方体体积create function cube(cubeL decimal(4,1),cubeW decimal(4,1),cubeH decimal(4,1)returns decimal(12,3)asbeginreturn(cubeL*cubeW*cubeH)end2、返回一个直接表格数据Create Function hand (Storeid varchar(30)Returns TableAS Return (Select st_name,st_score From student s1,score s2 Where s1.st_id=s2.st_id and s1.st_id=Storeid )3、计算两个日期之间相差的年数Create Function DInterval (SomeDay datetime, CurrentDay datetime)Returns tinyintAS Begin Return DateDiff(yy, SomeDay , CurrentDay ) -返回两个日期之间的差值 ENDSelect 学号=st_id , 姓名=st_name, 年龄=STR(dbo.DInterval(st_birth,GetDate() From student十一、t-sql语句声明变量declare a intset a=(select AVG(st_score) from score)select student.st_id,st_name,a from student,scorewhere student.st_id=score.st_idif.else语句3、beginend语句if(select AVG(st_score) from score where sub_no=011)60beginprint 及格select st_id from score where st_score(select AVG(st_score) from score where sub_no=011 and sub_no=011)endwhilebreak/continuewhile(select AVG(st_score) from stutest)80breakendcase语句select st_id,成绩等级=casewhen st_score=90 then 优秀when st_score=80 then 良好when st_score=70 then 优秀when st_score=60 then 优秀else 不及格endfrom scoreif exists语句if not exists(select * from score where sub_no=012)begin print 没有该课returnend十二、编辑维护把student表中的系别是04的数据插入到stutest表中，并且把student表中的st_name字段前加上字