实验二 数据的查询、更新 徐龙琴设计制作实验二 数据的查询、更新 一、实验目的1、掌握用户自定义数据类型的方法2、掌握用T-SQL语句进行数据的插入、修改、删除的方法3、熟练掌握SELECT语句，能够运用该语句完成各种查询二、实验要求1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；2、能认真独立完成实训内容；3、实验后做好实验总结，根据实验情况完成总结报告。三、实验内容1、用T-SQL语句，创建一用户自定义数据类型：名称为“char20”，数据类型为varchar，长度为20，允许为空。sp_addtype char20, varchar(20),null提示：sp_addtype typename=用户自定义类型的名字 , phystype=系统类型名 , nulltype= not null | null , owner= 拥有该类型的用户名 例：自定义一个名为address的类型，其所属系统类型为varchar，长度为80，不能为空。sp_addtype address, varchar(80), not null 2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表，课程表，学生选修表，并向各表插入如下相应的数据。create database 学生课程数据库学生表:Student(Sno,Sname,Ssex,Sage,Sdept) 其中Sno 为主键、Ssex取值为男或女、Sage在15到30之间:SnoSnameSsexSageSdept95001李敏勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男18IScreate table student(sno int primary key, sname char(10), ssex char(2) constraint ssex_ch check(ssex in(男,女), sage int constraint sage_ch check(sage=15 and sage=30), sdept char(10) ) insert into studentvalues(95001,李敏勇,男,20,CS)insert into studentvalues(95002,刘晨,女,19,IS)insert into studentvalues(95003,王敏,女,18,MA)insert into studentvalues(95004,张立,男,18,IS)课程表:Course(Cno,Cname,Cpno,Credeit,remarks) 其中Cno为主键、Teacher的类型为char20即为用户定义的数据类型;CnoCnameCpnoCredit Teacher1数据库54王芳2数学NULL2刘新3信息系统14刘新4操作系统63高升5数据结构74宋明6数据处理NULL2张彬7Pascal语言64李磊create table course(cno int primary key, cname char(15), cpno int null, credit int,teacher char20 ) insert into coursevalues(1,数据库,5,4,王芳)insert into coursevalues(2,数学,NULL,2,刘新)insert into coursevalues(3,信息系统,1,4,刘新)insert into coursevalues(4,操作系统,6,3,高升)insert into coursevalues(5,数据结构,7,4,宋明)insert into coursevalues(6,数据处理,NULL,2,张彬)insert into coursevalues(7,PASCAL语言,6,4,李磊)学生选修表:SC(Sno,Cno,Grade) 其中Sno,Cno为主键同时又为外键、Grade值在0到100;SnoCnoGrade950011929500128595001388950022909500325595004270create table sc(sno int, cno int, grade int constraint grade_ch check(grade between 0 and 100),primary key(sno,cno),constraint fk_sno foreign key (sno) references student(sno),constraint fk_cno foreign key (cno) references course(cno), ) insert into scvalues(95001,1,92)insert into scvalues(95001,2,85)insert into scvalues(95001,3,88)insert into scvalues(95002,2,90)insert into scvalues(95003,2,55)insert into scvalues(95004,2,70)3、用T-SQL语句，修改上面所建学生课程数据库中数据:1) 向学生表:Student中加入一条记录：(95030,谢非,男,22,CS)并保存insert into studentvalues(95030,谢非,男,22,CS)2) 将李敏勇的数据库的成绩改为98分update sc set grade=98where sc.cno=(select course.cno from course where course.cname=数据库 and sno=(select sno from student where sname=李敏勇 ) )3) 删除学生表Student中谢非的记录并保存delete from studentwhere sname=谢非4) 能不能从Student表中删除李敏勇学生的记录,为什么?能不能删除王敏, 张立两个学生的记录?不能， sc表中列sno是外码，参照student表的sno列。4、用T-SQL语句，完成下面简单的查询1）查询全体学生的学号、姓名及年龄.use 学生课程数据库select *from student2）查询全体学生的姓名, 年龄及所在系（要用小写字母表示系名，并用“系名”来表示列名）。select sname,sage, lower(sdept) 系名from student3）查询选修了课程的学生学号select distinct sno from sc4）查询信息系全体学生的姓名select snamefrom studentwhere sdept=IS5）查询所有年龄在20岁以下的学生姓名及其年龄select sname,sagefrom studentwhere sage=70order by grade desc16)查询每个学生的学号、课程号及分数，同时统计每个学生的总分select sno,cno,gradefrom scorder by snocompute sum(grade) by sno17)查询每个学生的各科分数、最高分