实验一：

要求在本地磁盘D创建一个学生-课程数据库（名称为student），只有一个数据文件和日志文件，文件名称分别为stu和stu_log，物理名称为stu_data.mdf 和stu_log.ldf，初始大小都为3MB，增长方式分别为10%和1MB，数据文件最大为500MB，日志文件大小不受限制。

CREATE DATABASE student
ON (
name=stu,	--数据文件名称
filename='D:\student.mdf',--存放位置
size=3MB,	--初始大小
filegrowth=10%)		--增长方式
LOG ON (
name=stu_log,	--日志文件名称
filename='D:\student.ldf',
size=3MB,
maxsize=500MB,	--数据文件最大
filegrowth=1MB		--增长方式
)

结果展示

创建一个Company数据库，该数据库的主数据文件逻辑名称为Company_data，物理文件为Company.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为Company_log，物理文件名为Company.ldf，初始大小为1MB，最大尺寸为50MB，增长速度为1MB。

CREATE DATABASE Company
ON (
name=Company_data,
filename='D:\Company.mdf',
size=10mb,
maxsize=unlimited,		--最大尺寸无限制
filegrowth=10%)
LOG ON (
name=Company_log,filename='D:\Company.ldf',
size=1mb,
maxsize=50mb,
filegrowth=1mb
)

结果展示

创建数据库DB，具有2个数据文件，文件逻辑名分别为DB_data1和DB_data2，文件初始大小均为5MB，最大为100MB，按10%增长；只有一个日志文件，初始大小为3MB，按10%增长；所有文件都存储在D盘文件夹ceshi中。

create database DB
on
primary
(							--创建两个数据文件
name=DB_data1,
filename='D:\ceshi\DB_data1.mdf',		--文件存储在D盘文件夹ceshi中
size=5mb,
filegrowth=10%,
maxsize=100mb
),
(name=DB_data2,
filename='D:\ceshi\DB_data2.ndf',
size=5mb,
filegrowth=10%,
maxsize=100mb
)
log on
(							--只有一个数据文件
name=DB_log,
filename='D:\ceshi\DB.ldf',
size=3mb,
maxsize=unlimited,
filegrowth=10%
)

注：先在D盘新建一个 ceshi 文件夹

结果展示

在数据库student中增加数据文件db2，初始大小为10MB，最大大小为50 MB，按10%增长；

ALTER DATABASE student
ADD FILE
(
NAME=db2,	--增加数据文件db2
filename='D:\db2.ndf',
size=10mb,
maxsize=50mb,
filegrowth=10%
)

结果展示

在数据库student中添加日志文件，保存在D盘中，初始大小为1MB，最大无限制，增长方式按照1MB增长；

ALTER DATABASE student
ADD LOG FILE
(
NAME=stu_log2,		--增加日志文件db2
FILENAME='D:\stu_log2.ldf',
SIZE=1MB,
MAXSIZE=unlimited,
FILEGROWTH=1MB
)

结果展示

修改数据库student主数据文件的大小，将主数据文件的初始大小修改为10Mb，增长方式为20%；

ALTER DATABASE student
MODIFY FILE
(
NAME=stu,
SIZE=10MB,		--修改数据文件初始大小
FILEGROWTH=20%	--修改数据文件增长方式
)

修改数据库student辅助数据文件初始大小为3MB，最大为100MB，按照10%增长，名称为db2;

ALTER DATABASE student
MODIFY FILE
(NAME=db2,
 SIZE=3MB,
 MAXSIZE=100MB,
 FILEGROWTH=10%
 )

报错原因：不能被收缩

删除数据库student辅助数据文件和第二个日志文件；

ALTER DATABASE student
REMOVE FILE db2
--分割线--
--------必须一个一个输入--------
ALTER DATABASE student
REMOVE FILE stu_log2

删除数据库company和DB。

1
2
3

DROP DATABASE company
go	--可以连续操作
DROP DATABASE DB

实验二

请分别使用Management Stuio及T-SQL完成以下内容：

请在指定数据库内完成以下内容：

1）依据数据表的结构创建相对应的数据表，表结构如下所示；

学生信息表（student）

字段名称	字段类型及长度	说明	备注
Sno	Char（9）	学生学号	主关键字
Sname	Char（6）	学生姓名	非空
Ssex	Char（2）	学生性别	可为空
Sage	Int	学生年龄	可为空
Sdept	VarChar（8）	学生所在院系	可为空

课程信息表（course）

字段名称	字段类型及长度	说明	备注
Cno	Char（4）	课程编号	主关键字
Cname	VarChar（20）	课程名称	非空
Cpno	Char（4）	先行课	可为空
Ccredit	Int	学分	可为空

选课信息表（sc）

字段名称	字段类型及长度	说明	备注
Sno	Char（9）	学生学号	主关键字
Cno	Char（4）	课程编号	主关键字
Grade	Int	成绩	可为空

create table Student (
	Sno   char(9)  primary key,		--主码
	Sname char(6)  not null,		--非空
	Ssex  char(2)  ,
	Sage  Int      ,
	Sdept VarChar(8))
go	
create table course (
	Cno   char(4)  primary key,
	Cname VarChar(20)  not null,
	Cpno  char(4)  ,
	Ccredit  Int    )
go
create table sc (
	Sno   char(9)  not null,
	Cno   char(4)  not null,
	Grade  Int     
	primary key (Sno,Cno)		--双主码
	)

结果展示

2）在表student中增加新字段 “班级名称（sclass）”；

1	Alter table student add sclass VarChar(20)

结果展示

3）在表student中删除字段“班级名称（sclass）”；

1	Alter table student drop column sclass

4）修改表student中字段名为“sname”的字段长度由原来的6改为8；

1	Alter table student alter column Sname char(8)

结果展示

5）修改表student中字段“sdept”名称为“dept”，长度为20；

1 2	Alter table student drop column Sdept Alter table student add dept VarChar(20)

6）修改表student中sage字段名称为sbirth，类型为smalldatetime；

1 2	Alter table student drop column Sage Alter table student add sbirth smalldatetime

7）修改表student新名称为stu_info；

1	exec sp_rename'student','stu_info'

结果展示

8）删除数据表student；

1	drop table student

注：改名了还如何删除？

错误展示

1	drop table stu_info

真实删除

2、创建教师授课管理数据库JSSK，并完成以下内容；

1）在数据库JSSK中创建下列三张表；

表名：teachers

列名	数据类型	说明	描述
Tno	字符型，长度7	主键	教师号
Tname	字符型，长度10	非空	姓名
Tsex	字符型，长度2	默认取值为“男”	性别
Birthday	小日期时间型	允许空	出生日期
Dept	字符型，长度20	允许空	所在部门
Sid	字符型，长度18		身份证号

表名： lessons

列名	数据类型	说明	描述
Cno	字符型，长度10	主键	课程号
Cname	字符型，长度20	非空	课程名
Credit	短整型		学分
property	字符型，长度为10		课程性质

表名： shouke

列名	数据类型	说明	描述
Tno	字符型，长度7	主键	教师号
Cno	字符型，长度10	主键	课程名
Hours	整数		课时

create database JSSK

go

create table teachers (
	Tno   char(7)  primary key,
	Tname char(10)  not null,
	Tsex  char(2)	default'男'	,	--默认取值为“男”
	Birthday  smalldatetime     ,
	Dept  char(20)				,
	Sid   char(18))
go	
create table  lessons (
	Cno   char(10)  primary key,
	Cname char(20)  not null,
	Credit	smallint	,
	property char(10))
go	
create table  shouke (
	Tno   char(7)  not null,
	Cno   char(10) not null,
	Hours int
	primary key (Tno,Cno))

结果展示

2）在shouke表里添加一个授课类别字段，列名为Type，类型为Char，长度为4；

1	Alter table shouke add Type char(4)

结果展示

3）将shouke表的Hours的类型改为smallint；

1	Alter table shouke alter column Hours smallint

结果展示

4）删除lessons表中的property列；

1	Alter table lessons drop column property

实验三

以课本指定的数据库为例，并依据数据表的结构创建相对应的数据表（student、course、sc），请分别使用Management Stuio界面方式及T-SQL 语句实现进行以下操作：

create table Student (
	Sno   char(9)  primary key,		--主码
	Sname char(6)  not null,		--非空
	Ssex  char(2)  ,
	Sage  Int      ,
	Sdept VarChar(8))
go	
create table course (
	Cno   char(4)  primary key,
	Cname VarChar(20)  not null,
	Cpno  char(4)  ,
	Ccredit  Int    )
go
create table sc (
	Sno   char(9)  not null,
	Cno   char(4)  not null,
	Grade  Int     
	primary key (Sno,Cno)		--双主码
	foreign key (Sno) references Student (Sno),		--外码
	foreign key (Cno) references course (Cno)
	)

预备：预建表格

向各个数据表中插入如下记录：

*学生信息表（student）

Sno	Sname	Ssex	Sage	Sdept
200515001	赵菁菁	女	23	CS
200515002	李勇	男	20	CS
200515003	张力	男	19	CS
200515004	张衡	男	18	IS
200515005	张向东	男	20	IS
200515006	张向丽	女	20	IS
200515007	王芳	女	20	CS
200515008	王民生	男	25	MA
200515009	王小民	女	18	MA
200515010	李晨	女	22	MA
200515011	张毅	男	20	WM
200515012	杨磊	女	20	EN
200515013	李晨	女	19	MA
200515014	张丰毅	男	22	CS
200515015	李蕾	女	21	EN
200515016	刘社	男	21	CM
200515017	刘星耀	男	18	CM
200515018	李贵	男	19	EN
200515019	林自许	男	20	WM
200515020	马翔	男	21
200515021	刘峰	男	25	CS
200515022	牛站强	男	22
200515023	李婷婷	女	18
200515024	严丽	女	20
200515025	朱小鸥	女	30	WM

课程信息表（course）

Cno	Cname	Cpno	Ccredit
1	数据库	5	4
2	数学		2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理		2
7	PASCAL语言	6	4
8	大学英语		4
9	计算机网络		4
10	人工智能		2

选课信息表（sc）

Sno	Cno	Grade
200515001	1	75
200515002	1	85
200515002	3	53
200515003	1	86
200515004	1	74
200515005	1	58
200515006	1	84
200515004	2	46
200515005	2	89
200515006	2	65
200515008	2	72
200515009	2	76
200515010	2	96
200515010	8	86
200515011	8	62
200515015	8	0
200515018	8	58
200515001	4	62
200515002	4	85
200515021	9	54
200515001	5	58
200515021	6	58
200515001	7	70
200515005	10	65
200515016	8	Null
200515017	8	Null

insert into student values('200515001','赵菁菁' ,'女',23,'CS')
insert into student values('200515002','李勇' ,'男',20,'CS')
insert into student values('200515003','张力' ,'男',19,'CS')
insert into student values('200515004','张衡' ,'男',18,'IS')
insert into student values('200515005','张向东' ,'男',20,'IS')
insert into student values('200515006','张向丽' ,'女',20,'IS')
insert into student values('200515007','王芳' ,'女',20,'CS')
insert into student values('200515008','王民生' ,'男',25,'MA')
insert into student values('200515009','王小民' ,'女',18,'MA')
insert into student values('200515010','李晨' ,'女',22,'MA')
insert into student values('200515011','张毅' ,'男',20,'WM')
insert into student values('200515012','杨磊' ,'女',20,'EN')
insert into student values('200515013','李晨' ,'女',19,'MA')
insert into student values('200515014','张丰毅' ,'男',22,'CS')
insert into student values('200515015','李蕾' ,'女',21,'EN')
insert into student values('200515016','刘社' ,'男',21,'CM')
insert into student values('200515017','刘星耀' ,'男',18,'CM')
insert into student values('200515018','李贵' ,'男',19,'EN')
insert into student values('200515019','林自许' ,'男',20,'WM')
insert into student values('200515020','马翔' ,'男',26,NULL)		--NULL为空
insert into student values('200515021','刘峰' ,'男',25,'CS')
insert into student values('200515022','牛站强' ,'男',22,NULL)
insert into student values('200515023','李婷婷' ,'女',18,NULL)
insert into student values('200515024','严丽' ,'女',20,NULL)
insert into student values('200515025','朱小鸥' ,'女',30,'WM')
 
 
insert into course values('1','数据库','5',4)
insert into course values('2','数学',NULL,2)
insert into course values('3','信息系统','1',4)
insert into course values('4','操作系统','6',3)
insert into course values('5','数据结构','7',4)
insert into course values('6','数据处理',NULL,2)
insert into course values('7','PASCAL语言','6',4)
insert into course values('8','大学英语',NULL,4)
insert into course values('9','计算机网络',NULL,4)
insert into course values('10','人工智能',NULL,2)
 
 
insert into sc values('200515001','1',75)
insert into sc values('200515002','1',85)
insert into sc values('200515002','3',53)
insert into sc values('200515003','1',86)
insert into sc values('200515004','1',74)
insert into sc values('200515005','1',58)
insert into sc values('200515006','1',84)
insert into sc values('200515004','2',46)
insert into sc values('200515005','2',89)
insert into sc values('200515006','2',65)
insert into sc values('200515008','2',72)
insert into sc values('200515009','2',76)
insert into sc values('200515010','2',96)
insert into sc values('200515010','8',86)
insert into sc values('200515011','8',62)
insert into sc values('200515015','8',0)
insert into sc values('200515018','8',58)
insert into sc values('200515001','4',62)
insert into sc values('200515002','4',85)
insert into sc values('200515021','9',54)
insert into sc values('200515001','5',58)
insert into sc values('200515021','6',58)
insert into sc values('200515001','7',70)
insert into sc values('200515005','10',65)
insert into sc values('200515016','8',NULL)
insert into sc values('200515017','8',NULL)

结果展示

修改CS系姓名为“李勇”的学生姓名为“李咏”；

1
2
3

update student 
set Sname='李咏'
where Sname = '李勇' and Sdept = 'CS';

结果展示

修改课程“数据处理”的学分为3学分；

1 2	update course set Ccredit=3 where Cname = '数据处理'

结果展示

将选修课程“1”的同学成绩加5分；

1 2	update sc set Grade=Grade+5 where Cno = '1'

结果展示

将选修课程“大学英语”的同学成绩加5分；

update sc set Grade = Grade+5
where Cno in 
(
select Cno from course where Cname= '大学英语'
);

结果展示

将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；

1 2	update student set Sname = '王丹丹',Ssex='女',Sage=20,Sdept='MA' where Sno = 200515010

结果展示

向student表中增加记录：（200515026，王婧婧、女、21，CS）；

1	insert into student values(200515026,'王婧婧','女',21,'CS')

结果展示

删除数据表student中无系别的学生记录；

1 2	delete from student where sdept is NULL --只能用 is 语句

结果展示

删除数据表student中计算机系年龄大于25的男同学的记录；

1 2	delete from student where Sage > 25 and Sdept = 'CS' and Ssex='男'

结果展示

原因：数据表student中无计算机系年龄大于25的男同学

（惊了，计算机专业居然没有大蜀黍）

删除数据表course中学分低于1学分的课程信息；

1 2	delete from course where Ccredit < 1

结果展示

原因：数据表course中无学分低于1学分的课程

实验四

1、在学生选课库（如下3个表）中，用Transact-SQL语句实现下列简单数据查询操作。

create table Student (
	Snum	char(9)  primary key,
	Sname	char(6)  not null,
	Sex	char(2),
	Sage	Int,
	Sphone	char(16),
	Dnum	char(6)
	)
go	
create table Course (
	Cnum   char(4)  primary key,
	Cname VarChar(20)  not null,
	Cfreq  Int
	)
go
create table SC (
	Snum   char(9)  not null,
	Cnum   char(4)  not null,
	Score  Int     
	primary key (Snum,Cnum)
	foreign key (Snum) references Student (Snum),
	foreign key (Cnum) references Course (Cnum)
	)

预备：预建表格

Student表

学号Snum	姓名Sname	性别Sex	年龄Sage	电话Sphone	系编号Dnum
S001	王明	男	19	86824571	D2
S002	李勇	男	23	89454321	D3
S003	刘燕	女	21		D1
S004	王萍	女	23		D1
S005	王佳	男	24	13098765892	D3
S006	赵婷	女	20		D1

SC表

学号Snum	课程号Cnum	成绩Score
S001	C1	83
S001	C2	89
S001	C3	65
S001	C4	85
S001	C5	69
S002	C3	78
S002	C4	75
S005	C1	95
S004	C1	85
S005	C1	92
S005	C3	76

Course表

课程号Cnum	课程名称Cname	学分Cfreq
C1	数据库系统原理	4
C2	C程序设计	4
C3	计算机体系结构	3
C4	自动控制原理	2
C5	数据结构	4

insert into Student(Snum,Sname,Sex,Sage,Sphone,Dnum)
values
('S001','王明','男',19,'86824571','D2'),
('S002','李勇','男',23,'89454321','D3'),
('S003','刘燕','女',21,NULL,'D1'),
('S004','王萍','女',23,NULL,'D1'),
('S005','王佳','男',24,'13098765892','D3'),
('S006','赵婷','女',20,NULL,'D1')

go

insert into Course (Cnum,Cname,Cfreq)
values
('C1','数据库系统管理',4),
('C2','C 程序设计',4),
('C3','计算机体系结构',3),
('C4','自动控制原理',2),
('C5','数据结构',4)

go

insert into SC(Snum,Cnum,Score)
values
('S001','C1',83),
('S001','C2',89),
('S001','C3',65),
('S001','C4',85),
('S001','C5',69),
('S002','C3',78),
('S002','C4',75),
('S005','C1',95),
('S004','C1',85),
--('S005','C1',92),		重复键
('S005','C3',76)

插入信息

注：有一行重复键无法输入plus：这是老师您的良苦用心嘛：）

plus+:不能用excel表格导入的原因

辣鸡玩意儿

微软自家的SQL连自家的Excel2019都导入不了（是不是废了？：）

怪我版本号高喽~

查询系编号为‘D2’学生的基本信息（学号、姓名、性别、年龄）。

1 2	select *from Student --查询全部列 where Dnum='D2';

结果展示

查询学号为S006的学生的姓名。

1 2	select Sname from Student where Snum='S006';

结果展示

查询成绩在60-85之间的学生的学号。

select Snum from SC
where Score between 60 and 85 ;

select DISTINCT Snum from SC	--取消取值相同的行
where Score between 60 and 85 ;

结果展示

（当然，后面的题目告诉我这里不必要用DISTINCT：）

查询所有姓王，并且姓名为两个字的学生的信息。

1 2	SELECT *FROM Student WHERE Sname LIKE '王_'; --只能使用 LIKE 不能用 =

结果展示

查询选修课程号为‘C1’且成绩非空的学生学号和成绩，成绩按150分制输出（每个成绩乘以系数1.5）。

1 2	SELECT Snum,Score*1.5 FROM SC WHERE Cnum = 'C1' and Score is not NULL ;

结果展示

查询有选课记录的所有学生的学号，用DISTINCT限制结果中学号不重复。

1	select DISTINCT Snum from SC

结果展示

查询选修课程‘C1’的学生学号和成绩，结果按成绩的升序排列，如果成绩相同则按学号的降序排列。

1
2
3

select Snum,Score from SC
where Cnum='C1'
order by Score asc,Snum desc

结果展示

2、以数据库原理实验三数据库中数据为基础，请使用T-SQL 语句实现以下操作：

列出所有不姓刘的所有学生；

1 2	SELECT Sname FROM Student WHERE Sname NOT LIKE '刘%' ;

结果展示

列出姓“沈”且全名为3个汉字的学生；

1 2	SELECT Sname FROM Student WHERE Sname LIKE '沈__'

结果展示

空

显示在1985年以后出生的学生的基本信息；

1 2	SELECT *FROM student WHERE Year(getDate())-Sage>1985 --Year(getDate()) 输出当前年份

结果展示

按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男生，性别为女显示为女生，其他显示为“条件不明”；

SELECT
	case
		when(Ssex='男') then '男生' 
		when(Ssex='女') then '女生' 
		else '条件不明' 
	end
 Ssex,Sno,Sname,Sage,Sdept FROM Student

结果展示

查询出课程名含有“数据”字串的所有课程基本信息；

1 2	SELECT *FROM course WHERE Cname LIKE ('%数据%')

结果展示

显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；

1 2	SELECT Sno,Sname,Ssex,Year(GETDATE())-Sage FROM student --Year(GETDATE()) 现在的年份 WHERE Sno LIKE '_______[1,2,3,4,9]%' or Sno LIKE '________[1,2,3,4,9]%'

结果展示

列出选修了‘1’课程的学生，按成绩的降序排列；

1
2
3

SELECT *FROM sc
WHERE cno=1
ORDER BY Grade DESC

结果展示

列出同时选修“1”号课程和“2”号课程的所有学生的学号；

1
2
3

SELECT Sno FROM sc
WHERE cno = 1 or cno = 2
GROUP BY sno HAVING count(*) = 2;	--统计选了两门课的学号

结果展示

列出课程表中全部信息，按先修课的升序排列；

1 2	SELECT *FROM course ORDER BY Cpno ASC;

结果展示

列出年龄超过平均值的所有学生名单，按年龄的降序显示；

1
2
3

SELECT Sname,Sage FROM student
WHERE Sage > (SELECT avg(Sage) as '平均年龄' FROM student)	--求平均年龄
ORDER BY Sage Desc

结果展示

按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；

1 2	SELECT Sno as '学号',Sname as '姓名',Ssex,Year(getDate())-Sage as '出生年份',Sdept as '院系' FROM student ORDER BY 出生年份 ASC

结果展示

显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”，若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；

select Sdept,院系规模=(
CASE
when count(Sno)>=5  then '规模很大' 
when count(Sno)>=4 and count(Sno)<5  then '规模一般'
when count(Sno)>=2 and count(Sno)<4  then '规模稍小'
ELSE '规模很小' end)
FROM student
GROUP BY Sdept HAVING Sdept is not null

结果展示

按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；

1
2
3

SELECT *FROM sc
WHERE Grade BETWEEN  70 and 80
ORDER BY Cno,Grade DESC

结果展示

显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；

1	SELECT count(Sno) as '学生总人数',avg(Sage) as '平均年龄' FROM student

结果展示

显示选修的课程数大于3的各个学生的选修课程数；

1 2	SELECT Sno,count(Cno) as '选修课程数' FROM sc GROUP BY Sno HAVING count(cno)>3

结果展示

按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；

1
2
3

SELECT count(Sno)as '课程总人数',max(Grade) as '最高成绩',min(Grade) as '最低成绩',avg(Grade) '平均成绩' from sc
GROUP BY Cno
ORDER BY Cno DESC

结果展示

选做题：

显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；

1 2	SELECT Sno,avg(Grade) as 平均成绩 FROM sc GROUP BY Sno HAVING avg(Grade)>(SELECT avg(Grade) FROM sc WHERE Sno='20051001')

结果展示

显示选修各个课程的及格的人数;

SELECT Cno,
sum(
CASE
when Grade>60 then 1
else 0
END
) as '及格人数'
FROM sc
GROUP BY Cno

结果展示

显示选修最多的课程数和最少的课程数；

SELECT Sno '选修课程数最多的学号' FROM sc
GROUP BY Sno
HAVING COUNT(Sno)>= ALL (SELECT COUNT(Sno) FROM sc GROUP BY Sno)
SELECT Sno '选修课程数最少的学号' FROM sc
GROUP BY Sno
HAVING COUNT(Sno)<= ALL (SELECT COUNT(Sno) FROM sc GROUP BY Sno)

结果展示

显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

SELECT Sdept,'男生人数'=
COUNT(
CASE
WHEN Ssex='男' then 1 end),
'女生人数'=
COUNT(
CASE
WHEN Ssex='女' then 1 end)
FROM Student
GROUP BY(Sdept)

结果展示

列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

SELECT Sno'学生的学号',AVG(Grade)'平均成绩'
FROM sc
WHERE Grade <60
GROUP BY Sno
Having COUNT(Sno)>=2

结果展示

实验五

1、以数据库原理实验四——学生选课库中，用Transact-SQL语句实现下列简单数据查询操作。

查询选修了计算机体系结构的学生的基本信息。

select * from student
where Snum in (
select Snum from course
where Cnum in (
select Cnum from sc
where Cname='计算机体系结构'
)
);

结果展示

查询年龄比李勇小的学生的学号和成绩。

select student.Snum,Score from student,sc
where student.Snum=sc.Snum and Sage<(
select Sage from student where Sname='李勇'
)

结果展示

查询其他系中比系编号为‘D1’的学生中年龄最小者要大的学生的信息。

select * from Student
where Dnum<>'D1' and Sage>(
select min(Sage) from Student
where Dnum='D1'
)

结果展示

查询其他系中比系编号为‘D3’的学生年龄都大的学生的姓名。

select Sname from Student
where Dnum<>'D3' and Sage>(
select max(Sage) from Student
where Dnum='D3'
)

结果展示

查询‘C1’课程的成绩高于70的学生姓名。

1 2	select Sname from Student,SC where Student.Snum=SC.Snum and Cnum='C1' and Score <=70

结果展示

查询‘C1’课程的成绩不高于70的学生姓名。

select Sname from Student
where Snum in(
select Snum from SC
where Cnum ='C1'and Score<=70)

结果展示

查询没有选修的学生姓名。

select Sname from Student
where not exists (
select Snum from SC
where SC.Snum=Student.Snum);

结果展示

查询学校开设的课程总数。

1	select count(*) from SC

结果展示

查询选修两门及两门以上课程的学生姓名。

select Sname from Student,SC
where Student.Snum=SC.Snum
group by Student.Snum,Sname
having count(Cnum)>=2

结果展示

查询开设的课程和选修该课程的学生的总成绩、平均成绩、最高成绩和最低成绩。

1 2	select Cnum,sum(Score) '总成绩',avg(Score) '平均成绩',max(Score) '最高成绩',min(Score) '最低成绩' from sc group by Cnum;

结果展示

2、以数据库原理实验四数据为基础，请使用T-SQL 语句实现进行以下操作：

查询以‘DB_’开头,且倒数第3个字符为‘s’的课程的详细情况；

1 2	select * from course where Cname like 'DB\_%s_'escape '\'

结果展示

查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；

1 2	select Sname,student.Sno,course.Cno,Cname from student,course,SC where student.Sno=SC.Sno and course.Cno=sc.Cno and Sname LIKE '_ _阳%';

结果展示

列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

select student.Sno,sname,Sdept,Cno,Grade from student,sc
where student.Sno=sc.Sno and student.Sno in (
select Sno from sc
where Cno in (
select Cno from course
where Cname=' 大学英语'or Cname='数学'
)
);

结果展示

查询缺少成绩的所有学生的详细情况；

1 2	select student.* from student,SC where student.Sno=SC.Sno and Grade IS NULL

结果展示

查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

select * from student
where Sage<>(
select Sage from student
where Sname = '张力'
)

结果展示

查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；

select student.Sno,Sname,平均成绩=AVG(Grade) from student,sc
where student.Sno=sc.Sno
group by student.Sno,Sname
having AVG(Grade)>(
	select AVG(Grade) from student,sc
	where student.Sname='张力' and student.sno=sc.sno
	group by student.Sno,Sname
)

结果展示

按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

1
2
3

select student.Sno,Sname,Sdept,'已修学分'=SUM(Ccredit) from student,course,sc
where Grade>=60 and student.Sno=sc.Sno and sc.Cno=course.Cno
group by student.Sno,Sname,Sdept

结果展示

列出只选修一门课程的学生的学号、姓名、院系及成绩；

select student.Sno,Sname,Sdept,Grade from sc,student
where student.Sno=sc.Sno and student.Sno in(
select student.Sno from sc,student
where student.Sno=sc.Sno
group by student.Sno having count(sc.Cno)=1)

结果展示

查询选修“数据库”或“数据结构”课程的学生的基本信息；

select student.sno,sname,Ssex,Sage,Sdept
from student,sc,course
where student.sno=sc.sno and
sc.cno=course.cno and
sc.sno in(select sc.sno from sc,course
where (cname='数据库'or cname='数据结构')and 
sc.cno=course.cno
group by sc.sno
having COUNT(*)=2)
group by student.sno,sname,Ssex,Sage,Sdept
having COUNT(*)=2

结果展示

选修了“数据库”或“数据结构”课程，选择了其他课程不可以入选

列出所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩；

1
2
3

select course.Cno,Cname,student.Sno,Sname,Grade from student,sc,course
where student.Sno=sc.Sno and sc.Cno=course.Cno
order by course.Cno

结果展示

查询只被一名学生选修的课程的课程号、课程名；

1
2
3

select sc.Cno,Cname from sc,course
where sc.Cno=course.Cno
group by sc.Cno,Cname having COUNT(sc.Sno)=1

结果展示

检索所学课程包含学生‘张向东’所学课程的学生学号、姓名；

select distinct student.Sno,student.Sname from student,sc
where student.Sno=sc.Sno and sc.Cno in (
select sc.Cno from student,sc
where student.Sno=sc.Sno and student.Sname='张向东')

结果展示

检索所学课程包含学生‘张向东’所学全部课程的学生学号、姓名；

select distinct course.sno,sname from sc course,student
where student.sno=course.sno and student.sname<>'张向东' and not exists(
select * from sc scy,student
where scy.sno=student.sno and student.sname='张向东' and not exists(
select * from sc scz
where scz.sno=course.sno and scz.cno=scy.cno
)
)

结果展示

使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；

select Sno,Sname from student
where Sno in (
select Sno from sc
where Cno in(
select Cno from course where Cname='数据结构'
)
)

结果展示

使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

select 姓名=Sname,年龄=Sage,院系=Sdept from student
where Sage<any(
select Sage from student
where sdept='CS')
and Sdept!='CS'

结果展示

使用ANY、ALL 查询，列出其他院系中比CS系所有学生年龄小的学生；

select Sname,Sage from Student
where Sage<ANY (select min(Sage) from Student
where Sdept='CS')and Sdept<>'CS'

select Sname,Sage from Student
where Sage< ALL(
select Sage from Student
where Sdept='CS')and Sdept<>'CS'

结果展示

分别使用连接查询和嵌套查询，列出与‘张力’在一个院系的学生的信息；

select first.* from student first,student second
where first.sdept=second.sdept and second.sname='张力';

select * from student
where sdept=(
select sdept from student
where sname='张力');

结果展示

使用集合查询列出CS系的学生以及性别为女的学生名单；

1
2
3

select Sname from student
where Sdept='CS' union select Sname from student
where Ssex=' 女'

结果展示

使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集；

select *from student
where Sdept='CS'intersect select *from student
where Sage<=19;

select *from student
where Sdept='CS' except select *from student
where Sage<=19

结果展示

使用集合查询列出选修课程1的学生集合与选修课程2的学生集合的交集；

1
2
3

select sno from sc
where Cno=1 intersect select sno from sc
where Cno=2

结果展示

实验六

以系统管理员身份登录到SQL Server服务器，在SQL Server2005界面中实现以下操作；

在当前计算机中增加一个用户zhang和cheng，密码为secret。使此用户通过winows nt模式下登录SQL Server服务器，登录名分别为zhang和cheng；

| 步骤展示 | |

步骤展示

| 步骤展示 | |

新建以混合模式登录SQL Server服务器的用户登录名为stu1、stu2和stu3，登录密码为secret，默认登录数据库为stu；

步骤展示

将帐号zhang添加为数据库stu的用户，用户名为zhang；

1	create user zhang for login [LAPTOP-0LH877B2\zhang]

结果展示

在数据库stu中创建用户stu1、stu2和stu3，登录帐号分别为stu1、stu2和stu3；

1
2
3

create user stu1 for login stu1
create user stu2 for login stu2
create user stu3 for login stu3

结果展示

给数据库用户zhang赋予创建数据库的权限；

1	grant create database to zhang

或者

步骤展示

给数据库用户stu1赋予对sc表进行插入、修改、删除操作权限；

1	grant insert,alter,delete on sc to stu1

给数据库用户stu2和stu3赋予对student表、course表所有操作权限及查询sc的操作权限，并允许再授权给其他用户；

grant all PRIVILEGES on student to stu2,stu3
WITH GRANT OPTION
grant all PRIVILEGES on course to stu2,stu3
WITH GRANT OPTION

收回数据库用户stu2对student表和course表的删除操作的权限；

1 2	revoke delete on student to stu2 cascade revoke delete on course to stu2 cascade

若一个小组共5个成员，他们对数据库stu具有相同的操作权限，具体权限如下：

1	create role small_group

1）对于student、course表只能进行数据查询；

1 2	grant select on student to small_group grant select on course to small_group

2）只能对student表中sname进行更改；

1	grant update on student(sname) to small_group

3）对于sc表只能进行修改、删除或插入；

grant update,delete,insert on sc to small_group
create user cheng for login [LAPTOP-0LH877B2\cheng]
exec sp_addrolemember 'xiaozu', 'stu1'
exec sp_addrolemember 'xiaozu', 'stu2'
exec sp_addrolemember 'xiaozu', 'stu3'
exec sp_addrolemember 'xiaozu', 'zhang'
exec sp_addrolemember 'xiaozu', 'cheng'

实验七

没有图片是因为我也是从网上获取

以系统管理员身份登录到SQL Server服务器，并使用T-SQL语句实现以下操作；

请用至少2种方法定义stu数据库中student表的主键sno；

create table student(
Sno char(9) primary key,
Sname char(6) not null,
Ssex char(2),
Sage int,
Sdept varchar(8),
)

create table student(
Sno char(9),
Sname char(6) not null,
Ssex char(2),
Sage int,
Sdept varchar(8),
primary key(Sno)
)

将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;

1 2	alter table course add constraint cno_pk primary key(cno)

为表course中的字段cname添加唯一值约束；

1 2	alter table course add constraint C2 unique(cname)

将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;

1 2	alter table sc add constraint sc_pk primary key(sno,cno

对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：

1）删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；

alter table sc
add constraint sc1 foreign key(sno) references student(sno)
on delete cascade
on update no action
alter table sc
add constraint sc2 foreign key(cno) references course(cno)
on delete cascade
on update cascad

2）修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；



3）修改course表cno字段值时，该字段在sc表中的对应值也应修改；



4）删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；



5）向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；

定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；

1 2	alter table student add constraint Ccheck check(sno like '[1-9]00[0-9][0-9][0-9][0-9][0-9][0-9]')

定义stu数据库中student表中学生年龄值在16-25范围内；

1 2	alter table student add constraint check7 check (sage between 16 and 25)

定义stu数据库中student表中学生姓名长度在2-8之间；

1 2	alter table student add constraint check8 check (len(Sname) between 2 and

定义stu数据库中student表中学生性别列中只能输入“男”或“女”；

1 2	alter table student add constraint check9 check(ssex in ('男','女'))

定义stu数据库student表中学生年龄值默认值为20；

1 2	alter table student add constraint check10 default 20 for Sag

修改student表学生的年龄值约束可以为15-30范围内；

alter table student
drop constraint check7
alter table student
add constraint check7 check (sage between 15 and 30)

删除上述唯一值约束、外键约束及check约束；

alter table student
drop constraint check7,check8,check9,check10
alter table course
drop constraint cno_pk,C2,sc_pk
alter table sc
drop