sqlserver实验题(定时更新，目前已到实验 7)

不怂zizz

已于 2023-11-23 13:15:49 修改

阅读量824

点赞数

文章标签： sqlserver 数据库

于 2023-09-21 18:23:35 首次发布

本文链接：https://blog.csdn.net/2301_77705360/article/details/133141294

版权

实验 7 子查询

--实验 7  子查询
--1、查询李明的同乡同学信息
--select *
--from Student
--where address = (select address from Student where(studentName='李明')) and studentName <> '李明'
--2、查询学生的姓名以及所在班级名称
--select studentName,className
--from Student join Class on Student.classID = Class.classID
--3、--查询选过课的所有学生名单。
--select *
--from Student
--where studentID  in (select distinct studentID from Grade)
--4、查询选修了“程序设计基础”课程的学生学号和姓名
--select Student.studentID,Student.studentName
--from Student join Grade on Student.studentID = Grade.studentID
--			join Course on Course.courseID = Grade.courseID
--where coursename = '程序设计基础'

--5、查询大于李明年龄的学生信息。
--select *
--from Student
--where birthday <  (select birthday from Student where studentName='李明')
--6、查询大于来自合肥的所有学生年龄的学生信息。
--select *
--from Student
--where birthday <all  (select birthday from Student where address = '合肥')
--7、删除没有学生的班级信息
--delete
--from Class
--where classID not in (select classID from Student)

--8、查询从未选修过任何课程的学生信息。
--select *
--from Student
--where studentID  not in (select distinct studentID from Grade)

--9、查询每个班级学分最高的学生信息。
--SELECT *
--FROM Student AS S1
--WHERE credithour =(SELECT MAX(credithour) FROM Student WHERE classID=S1.classID)

--10、多表查询回顾
--查询每个班级六安学生人数，显示班级名称和学生人数两列

--select distinct Class.className,(select count(*) from Student where Student.classId = Class.classId)
--from Class,Student
--where Student.address='六安'

课堂记录：多表连接查询、集合操作、子查询

-----------------------------------------------多表连接查询------------------------------------------------------------

--一、查询学生姓名和班级名称
---方法一：
--select studentName,className
--from Student,Class
--where Student.classID = Class.classID
--order by studentName

---方法二：
--默认内连接，返回完全符合条件的值
--select studentName,className
--from Student insert join Class on Student.classID=Class.classID

--左外连接 
--select studentName,className
--from Student left join Class on Student.classID=Class.classID

--右外连接
--select studentName,className
--from Student right join Class on Student.classID=Class.classID

--全外连接
--select studentName,className
--from Student full join Class on Student.classID=Class.classID

--交叉连接 没有条件 笛卡儿积
--select studentName,className
--from Student cross join Class

--二、查询学生姓名、课程名称、成绩
--select studentName,coursename,Grade.score
--from Student,Course,Grade
--where Student.studentID = Grade.studentID and Grade.courseID= Course.courseID
--order by studentName

--三、练习：查询教师姓名和部门名称
--select teacherName,DepartmentName
--from Teacher,Department
--where Teacher.departmentID = Department.DepartmentID

--内连接 相交部分
--select teacherName,DepartmentName
--from Teacher join Department on Teacher.departmentID = Department.DepartmentID
--外连接
--左外连接 右外连接 全连接 略
--select teacherName,DepartmentName
--from Teacher left join Department on Teacher.departmentID = Department.DepartmentID

-----------------------------------------------集合操作-------------------------------------------------------------------
--union intersert except  ==> 并、交、差
--一、查询全校师生的信息
--select studentID as '学工号',studentName 姓名,备注='学生'
--from Student
--union
--select TeacherID,teacherName ,备注='教师'
--from Teacher

---------------------------------------------------
--1、交集：intersect 交集：union 差集：except 
--select Student.studentID,Student.studentName
--from Student join Grade on Student.studentID = Grade.studentID
--			join Course on Course.courseID = Grade.courseID
--where coursename = 'JAVA程序设计'

---- intersect 
----except
--union
--select Student.studentID,Student.studentName
--from Student join Grade on Student.studentID = Grade.studentID
--			join Course on Course.courseID = Grade.courseID
--where coursename = '程序设计基础'

--------------------------子查询（1、比较、IN、相关）-------------------
--1、比较子查询
--查询木子同班同学信息
--select *
--from Student
--where classID = (select classID from Student where studentName='木子') and studentName<>'木子'
--!=  相当于 <>

--练习：查询学分比木子学分高的学生信息
--select *
--from Student
--where credithour > (select credithour from Student where studentName='木子') 

--	<any：小于最小值 <>any：不等于某一值
--	<all:小于最大值  <>all：不等于任何值
--练习：查询学分比所有合肥同学学分高的学生信息
--select *
--from Student
--where credithour >any (select credithour from Student where address='合肥')  --大于最小值即可
--where credithour   >all (select credithour from Student where address='合肥')   -- 大于最大值才可
--方法二：
--select *
--from Student
--where credithour   > (select max(credithour) from Student where address='合肥')   -- 使用聚合函数

--2、IN 子查询
--查询没有学生的班级信息
--select *
----delete
--from Class
--where classID not in (select classID from Student)

--练习：查询没有选修过任何课程的学生信息
--select *
--from Student
--where studentID not in (select distinct studentID from Grade)
--注：distinct加上与否导致结果不一样

--3、相关子查询
--查询每个班级学分最高的同学信息
--SELECT *
--FROM Student AS S1
--WHERE credithour =(SELECT MAX(credithour) FROM Student WHERE classID=S1.classID)

--查询学生姓名，班级名称
--方法一：多表查询
--select studentName,className
--from Student join Class on Student.classID = Class.classID

--方法二：相关子查询
--select studentName,(select className from Class where Class.classID=Student.classID)
--from Student 

--查询木子同班同学信息

作业6 分组汇总

--1). student表统计每个班级的人数，显示人数大于5的班级
--select count(*)as 人数,classID from Student
----group by classID
--having count(*)>5


----2). 统计每个学生平价成绩，显示平价成绩>80的记录
--select AVG(score) 平均成绩,studentID as 学号 from Grade
--group by studentID
--having AVG(score)>80

----3）统计每个部门的人数，显示部门名称，人数
--select count(*)as 人数,DepartmentName as 部门名称 from Department
--group by DepartmentName

----4）统计每门课程的最高成绩，最低成绩
--select max(score) 最高分,min(score)最低分,courseID as 课程号
--from Grade
--group by courseID

------5）统计teacher 表中，每种职称教师人数
--select count(*) 人数,technicalPost 职称
--from Teacher
--group by technicalPost

------6）统计每个部门讲师的人数
----注解：需要先where过滤，再分组，因为having过滤的是分组后的整体信息，详细信息需要where先过滤
----select count(*) 人数,departmentID 部门
----from Teacher
----where technicalPost = '讲师'
----group by departmentID

--------7）使用CUBE统计各班级男、女生人数。
----select count(*) 人数,sex 性别,classID 班级 
----from Student
----group by classID,sex with cube

--------8）使用ROLLUP统计各班级男、女生人数。
----select count(*) 人数,sex 性别,classID 班级 
----from Student
----group by classID,sex with ROLLUP

----9）查询学生姓名和班级名称。
--select studentName,className
--from Student,Class
--where Student.classID = Class.classID
--order by studentName

实验5 基础查询


--			实验六 数据基本检索
--一、实验目的：
--1.	熟记SELECT语句的语法；
--2.	掌握数据筛选的方法；
--3.	掌握数据排序、基本格式设置的方法；
--二、实验内容：
-- 附加数据sgms
--1、查询指定列的数据
--单表查询：查询student表中的学号、姓名、联系电话这几列的数据  
select studentID,studentName,telephone from dbo.Student
--2、查询指定行的数据
--查询student表中的学号、姓名、联系电话这几列的数据
--指定条件性别为女
select studentID,studentName,telephone from dbo.Student
where sex='女'


--3、字符串比较
--1).查询student表中姓名姓‘张’的职工信息
select * from Student where studentName  like '张%'
--2).查询student表中中姓名中包含‘玉’的职工信息
select * from Student where studentName like '%玉%'
--2).查询student表中姓名中第二个字为‘玉’的职工信息
select * from Student where studentName like '_玉%'



--4、检索一定范围内的数据
--1）查询grade 中成绩在80-100的课程编号，学号
select courseID,studentID from Grade where score between 80 and 100

--2）
--查询student表中的学号、姓名、联系电话这几列的数据
--条件1998-1-1 到1998-12-31
--要求使用 BETWEEN AND 
select studentID,studentName,telephone from dbo.Student
where birthday>='1998-1-1' and birthday<='1998-12-31'  

select studentID,studentName,telephone from dbo.Student
where year(birthday)=1998
--5、使用值列表作为搜索条件
--查询student表中家庭住址为合肥和六安的学生信息
-- 
select * from Student where address in ('合肥','六安')




--6、对结果集进行排序
--查询student表中数据
--按照出生日期降序排序
select * from Student order by birthday desc

--7、消除重复行
--检索student表中地址
select distinct address from Student 



--8、返回结果集前N条数据
----查询student表，返回年龄最大的3条记录
select top 3  with ties * from Student order by birthday 


----查询student表，返回年龄最大的15%条记录
select top 15 percent  with ties * from Student order by birthday

练习基础查询

--学生管理人员需要从“学生成绩管理系统”中筛选如下信息：
use SGMS
--1.学生成绩管理人员需要查看所有学生的详细信息。
--select * from Student

--2.返回学生学号与姓名两列数据。
--select studentID as '学号',studentName from dbo.Student
--3.学生成绩管理人员需要查看学号为“1811080”的学生的信息。

--select * from Student where studentID='1811080'
--练习：查询学分低于20的学生姓名和学号
--      查询少数民族学生的信息
--select studentID ,studentName from dbo.Student where credithour<20
--select * from Student where nation<>'汉'
--select * from Student where nation != '汉'

--4.教学管理人员需要查找所有姓“李”的学生的信息。
--select * from Student where studentName like '李%'
-- %：任意字符 _：单个字符 []:指定范围,例如 like '张李%' ^:非
--练习：查询课程表中包含数据库的课程信息。
--select * from Course where coursename like '%JAVA%'

--5.学生管理人员需要查询所有2001年出生的所有男生的详细信息。
--select * from Student where year(birthday)=2001 and sex='男'

--6.学生管理人员需要查询1999年到2001年之间出生的学生的详细信息。
--select * from Student where year(birthday) between 1999 and 2001

--7.学生管理人员需要查询来自合肥和芜湖的学生的详细信息。
--select * from Student where address in ('合肥','芜湖')

--8.学生管理人员需要查询未登记电话的学生的信息。
--select * from Student where telephone is null 

--9.教师需要按学分升序查询学生信息。
--select * from Student order by credithour

--10.查询学生学分最高的5人信息。
--select top 5 * from Student order by credithour desc

--11.查询学生的姓名和年龄。
select studentName,DATEDIFF(yy,birthday,GETDATE()) as '年龄' from dbo.Student

作业4 关系表的创建与管理

1、使用SQL Server中SSMS创建LIBRARY数据库，在此数据库中创建“图书”表和“出版社”表。

图书表

（1）设置主键：书名

（主键约束：不允许重复元素避免了数据的冗余唯一标识）

（2）设置CHECK约束：数量>0

（check约束：保证事务属性的取值在合法的范围之内）

（3）设置外键：出版社编号

（外键：事物和事物之间的关系是通过外键来体现的，通过外键约束从语法上保证了本事物所关联的其他事物一定存在）

注意：

出版社表

（1）主键：出版社编号（略）

（2）唯一约束：出版社名称

unique：保证了事物属性的取值不允许重复，但允许其中一列且只能有一列为空

（3）六位数字：邮编

2、使用T-SQL脚本在LIBRARY数据库中创建“学生”表和“借阅”表

学生表

借阅表

3、使用T-SQL脚本在LIBRARY数据库中进行以下修改

（1）在学生表中增加列“民族”，数据类型为varchar(6),默认值为“汉”

（2）在学生表中修改列“姓名”的数据类型为char(20)

（3）在借阅表中增加列“到期时间”，数据类型为datetime

4、使用T-SQL脚本在LIBRARY数据库中添加以下约束

（1）为“学生表”定义主键列，主键列为“学号”

（2）在“借阅表”添加default约束，借书日期默认值为getdate()

（3）为“借阅表”中的“学号”列设foreign key约束，使“学号”列外键约束于“学生表”中的“学号”列

（4）为“学生表”中的“学号”列设为check约束，检查输入的学号是否为数字

--（1）
alter table 学生表
 add constraint pk_学生表 primary key(学号)

 --（2）
 alter table 借阅表
 add constraint pk_借阅表 default getdate() for 借书日期

 --（3）
 alter table 借阅表
 add constraint pk_借阅表2 foreign key(学号) references 学生表(学号) 

 --（4）
  alter table 学生表
 add constraint pk_学生表2 check(学号 like '[0-9]')

实验三创建和管理数据库

1、利用SSMS和T-SQL语句创建“JWGL”数据库，相关属性为默认值。

2、使用SSMS和T-SQL查看JWGL数据库的属性。如当前数据库文件、文件组及数据库大小等信息。

3、在D盘student目录下新建一个名为LIBRARY数据库，其中：
（1）主文件逻辑名称为“LIBRARY_DATA”，物理文件名为“LIBRARY_DATA.mdf”，初始大小为5MB，最大大小为10MB，增长方式为1MB；

（2）事务日志文件逻辑名为“LIBRARY_LOG”，物理文件名为“LIBRARY_LOG.ldf”，初始大小为2MB，最大大小为10MB，增长方式为5%。

4、修改LIBRARY数据库，修改要求如下：
（1）为其增加一个文件组DY，其中包含两个数据文件，逻辑名分别为“DYA”和“DYB”其它属性采用默认值。

alter database LIBRARY add filegroup DY
alter database LIBRARY 
add file(
	filename = "D:\student\DYA_DATA.ndf",
	name = DYA
),
(
	filename = "D:\student\DYB_DATA.ndf",
	name = DYB
) 
to filegroup DY

（2）为其增加一个事务日志文件，其中逻辑名为“DY_LOG”，物理文件名为“DY_LOG.ldf”，初始大小为1MB，最大大小为不限制，增长方式为1MB。

（3）修改数据文件“LIBRARY_DATA”，将其最大大小修改为不受限制（UNLIMITED）。

--4、（2 ）（3）
alter database LIBRARY 
add file (
	filename = "D:\student\DY_LOG.ldf",
	name = DY_LOG,
	size = 1mb,
	maxsize = unlimited,
	filegrowth = 1mb
)

alter database LIBRARY 
modify file (
	name = LIBRARY_DATA,
	maxsize = unlimited
)

exec sp_helpdb LIBRARY

5、将“LIBRARY”数据库分离出来后，尝试删除其中的事务日志文件，然后附加到当前计算机SQL Server服务器上，检查一下事务日志文件相关属性前后变化。

显然是因为找不到事务日志文件，所以

附加成功，所以想附加，就不能删除事务日志文件

好像没有变化，可能是我弄错了什么，不管了，就这吧。

实验二编写T-SQL脚本

1、1+2+...+1000

declare @i int, @sum int
set @i = 1
set @sum = 0

while @i<=1000
	begin
		set @sum += @i
		set @i += 1
	end
print @sum

2、n! (n=20)

declare @n int,@sum bigint
set @n = 1
set @sum = 1
while @n<=20
	begin
		set @sum *= @n
		set @n += 1
	end
print @sum

3、水仙花数

declare @n int,@x int,@y int,@z int
set @n = 100
while @n<1000
	begin
		set @x = CAST(SUBSTRING(CAST( @n as varchar(3)),1,1) AS int)
		set @y = CAST(SUBSTRING(CAST( @n as varchar(3)),2,1) AS int)
		set @z = CAST(SUBSTRING(CAST( @n as varchar(3)),3,1) AS int)
		if POWER(@x,3)+POWER(@y,3)+POWER(@z,3) = @n
	begin
print @n
end
set @n += 1
end

4、

--身份证号提取出生日期，并计算年龄
select substring('xx1xxx20011115xxxx',7,8) as '出生日期'

select datediff(yy,cast(substring('xx1xxx20011115xxxx',7,8) as date) ,getdate()) as '年龄'

--隐藏手机号码4-7位
select stuff('12243171234',4,4,'****') as '手机号码'

--1-10随机整数
select ROUND(RAND()*9+1,0) as '随机数'