数据库实验二

--1.创建数据库“教学成绩管理数据库”，库中包含2个表：“班级信息表”和“学生信息表”--1.创建数据库“教学成绩管理数据库”，库中包含2个表：“班级信息表”和“学生信息表”usemastergocreatedatabase教学成绩管理数据库gouse教学成绩管理数据库gocreatetable班级信息表(编号char(8)notnullconstraintpk_编号primarykey,名称varchar(20)notnull,入学日期date,专业编号char(8))createtable学生信息表(学号char(8)notnullconstraintpk_学号primarykey,姓名varchar(8)notnull,性别char(2)constraintck_性别check(性别in('男','女')),出生日期date,民族varchar(20)constraintdf_民族default'汉族',籍贯varchar(20)constraintdf_籍贯default'陕西省',家庭地址varchar(50),邮政编码char(6),宿舍号intnotnullidentity(1001,1),宿舍电话char(8),身份证号char(18)notnullconstraintuqunique,政治面貌varchar(10)constraintck_政治面貌check(政治面貌in('中共党员','共青团员','群众','其他')),班级编号char(8)notnullforeignkeyreferences班级信息表(编号),备注varchar(200))--2、向班级信息表中插入不少于5条记录；向学生信息表中插入不少于10条记录insertinto教学成绩管理数据库.dbo.班级信息表(编号,名称,入学日期,专业编号)select'14000001','信管1班','2014-9-01','00000001'unionallselect'14000002','信管2班','2014-9-01','00000001'unionallselect'14000003','信管3班','2014-9-01','00000001'unionallselect'14000004','信管4班','2015-9-01','00000001'unionallselect'14000005','物流1班','2014-9-01','00000002'unionallselect'14000006','物流2班','2014-9-01','00000002'unionallselect'14000007','物流3班','2015-9-01','00000002'unionallselect'14000008','物流4班','2015-9-01','00000002'unionallselect'14000009','农经1班','2014-9-01','00000003'unionallselect'14000010','工程1班','2014-9-01','00000004'insertinto教学成绩管理数据库.dbo.学生信息表(学号,姓名,性别,出生日期,民族,籍贯,家庭地址,邮政编码,宿舍电话,身份证号,政治面貌,班级编号)select'14021001','刘德华','男','1995-01-01','汉族','陕西省','陕西','710100','01550001','111111111111111111','共青团员','14000001'unionallselect'14021002','孙俪','女','1994-11-12','汉族','湖南省','湖南','410001','01550022','222222222222222222','中共党员','14000001'unionallselect'14021003','鹿晗','男','1996-07-07','汉族','北京市','北京','110100','01557777','777777777777777777','共青团员','14000001'unionallselect'14021004','郭富城','男','1990-02-28','汉族','广东省','广东','510000','01558989','333333333333333333','其他','14000001'unionallselect'14021005','罗志祥','男','1994-03-24','汉族','台湾','台湾','222','01551923','555555555555555555','其他','14000002'unionallselect'14021006','赵丽颖','女','1995-02-13','汉族','北京市','北京','110100','01556666','666666666666666666','群众','14000003'unionallselect'14021007','潘长江','男','1989-06-11','汉族','河南省','河南','450000','01554545','888888888888888888','中共党员','14000003'unionallselect'14021008','冯巩','男','1966-01-01','汉族','四川省','四川','610016','01553333','999999999999999999','共青团员','14000004'unionallselect'14021009','小李子','男','1988-03-18','少数民族','陕西省','陕西','710100','01556767','111111111111111110','其他','14000005'unionallselect'14021010','丁日','男','1995-11-02','少数民','陕西省','陕西','710100','01555757','101010101010101010','其他','14000007'select*from学生信息表--3.删除没有学生的班级记录use教学成绩管理数据库deletefrom班级信息表where编号notin(select班级编号from学生信息表)select*from班级信息表--4、查询出学生信息表中的前5条记录use教学成绩管理数据库selecttop5*from学生信息表--5、查询出班级编号*的所有女生的学号、姓名和家庭地址use教学成绩管理数据库select学号,姓名,家庭地址from学生信息表where性别='女'and班级编号='14000001'--6、查询出某学生的宿舍号，要求显示为如：“张三的宿舍是1234”use教学成绩管理数据库select姓名,'的宿舍是',宿舍号from学生信息表where姓名='鹿晗'--7、查询所有来自“陕西”的学生use教学成绩管理数据库select*from学生信息表where籍贯='陕西省'--8、分别统计男生和女生的总人数，并为统计人数列命名use教学成绩管理数据库select性别,count(学号)as学生人数from学生信息表groupby性别--9、查询某学生的入学日期use教学成绩管理数据库select入学日期from班级信息表where编号in(select班级编号from学生信息表where姓名='鹿晗')--10、查询某学生的所在班级（列标题）use教学成绩管理数据库select班级编号from学生信息表where姓名='鹿晗'--11、查询某学生的所在班级和入学日期，要求显示三列：姓名、所在班级、入学日期use教学成绩管理数据库select姓名,班级编号as所在班级from学生信息表where姓名='鹿晗'--12、输出‘今天是：系统时间’use教学成绩管理数据库select'今天是',GETDATE()--13、查询系统当前日期前1个月，及后15天的日期，要求改变列标题use教学成绩管理数据库selectGETDATE()-30as系统当前日期前一个月,GETDATE()+15as系统当前日期后15天--14、查询某一个学生的年龄，要求输出两列‘姓名年龄’。（声明一个局部变量）use教学成绩管理数据库godeclare@姓名varchar(8),@年龄intselect@姓名=姓名,@年龄=datediff(year,出生日期,GETDATE())from学生信息表where姓名='刘德华'select@姓名as姓名,@年龄as年龄--15、使用USER返回当前用户的数据库用户名。（不声明变量）use教学成绩管理数据库declare@usrchar(30)set@usr=USERselect'当前用户的数据库用户名为'+@usrgo--16、查询自己姓名的ASCII码selectascii('李文静')--17、分别查询字符串‘大型数据库管理系统’的左边2个字符，右边4个字符，最中间的字符selectleft('大型数据库管理系统',2),RIGHT('大型数据库管理系统',4),substring('大型数据库管理系统',5,1)--18、定义一个自定义函数，并利用该函数显示学生信息表中的女生的姓名和年龄use教学成绩管理数据库gocreatefunction女生姓名年龄(@性别char(2))returnstableasreturn(select姓名,DATEDIFF(YEAR,出生日期,GETDATE())as年龄from学生信息表where性别=@性别and性别='女')goselect*from学生信息表where性别='女'--查看原表数据goselect*fromdbo.女生姓名年龄('女')--查看表值型函数--19、在班级信息表中，查找课程号为'20160101'的记录，如果有，则显示“此班级已存在”，如果无，则插入此班级信息（'20160101','工程','2016-9-1','333333'）declare@课程号char(8)set@课程号='20160101'ifexists(select*from教学成绩管理数据库.dbo.班级信息表where编号=@课程号)select'此班级已存在'elsebegininsertinto教学成绩管理数据库.dbo.班级信息表values('20160101','工程','2016-9-1','333333')select*from教学成绩管理数据库.dbo.班级信息表end--20、创建一个ASCII码对照表，表中包括个字段：一个字段是从~127的整数，另一个字段是其转换的字符。（用循环语句插入数值）use教学成绩管理数据库gocreatetableASCII码对照表(整数int,对应转换的字符char(8))declare@numintset@num=0while@num=127begininsertinto教学成绩管理数据库.dbo.ASCII码对照表values(@num,CHAR(@num))set@num=@num+1endselect*from教学成绩管理数据库.dbo.ASCII码对照表select*fromINFORMATION_SCHEMA.VIEWS--1、在学生信息表中的“身份证号”列上创建一个唯一性的非聚集索引，若该索引已存在则删除后重建use教学成绩管理数据库ifexists(SELECTnameFROMsysindexesWHEREname='身份证号_index')DROPINDEX学生信息表.身份证号_indexgocreateuniquenonclusteredindex身份证号_ixon学生信息表(身份证号)--2、在班级信息表中的“名称”和“入学日期”列上创建非聚集索引，并强制唯一性createuniquenonclu