【HNU数据库】实验1 数据库定义实验
一、实验 1.1 数据库定义实验
软件：sqlserver
（一）实验目的
理解和掌握数据库 DDL 语言，能够熟练地使用 SQL DDL 语句创建、修改和
删除数据库、模式和基本表。
（二）实验内容和要求
理解和掌握 SQL DDL 语句的语法，特别是各种参数的具体含义和使用方法；
使用 SQL 语句创建、修改和删除数据库、模式和基本表。掌握 SQL 语句常见语
法错误的调试方法。
（三）实验过程
实验数据库：SQLServer
1、创建数据库
创建一个数据库：create database 数据库名；
删除一个数据库：drop database 数据库名；
----创建数据库
CREATE DATABASE student
go
USE student
go
2、定义模式
创建模式： create schema 模式名
删除模式： drop schema 模式名;
use student--选择要操作的数据库
go --批处理命令
create schema ST
go
3、在ST模式下创建基本表
进入数据库：use 数据库名;
创建数据库表：create table 表名(列声明);
use student--选择要操作的数据库
go --批处理命令
create schema ST
create table Student(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
)
create table Course
(
Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4),
Ccredit smallint ,
foreign key(cpno) references Course(Cno)
)
create table SC
(
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)
go
4、向表中插入数据
插入数据：
INSERT
INTO <表名> [(<属性列1>[，<属性列2 >…)]
VALUES (<常量1> [，<常量2>] … )
由于此处的Course表中的Cno和Cpno会产生外键冲突，所以在插入数据时先将所有课程的Cpno设为自身，再使用UPDATE语句进行更改。
use student
go
insert into ST.Student(Sno,Sname,Ssex,Sage,Sdept)
values('201215121','李勇','男','20','CS'),
('201215122','刘晨','女','19','CS'),
('201215123','王敏','女','18','MA'),
('201215125','张立','男','19','IS')
go
INSERT INTO ST.Course (Cno, Cname, Cpno, Ccredit) VALUES ('1', '数据库', '1', '4')
INSERT INTO ST.Course (Cno, Cname, Cpno, Ccredit) VALUES ('2', '数学', '2', '2')
INSERT INTO ST.Course (Cno, Cname, Cpno, Ccredit) VALUES ('3', '信息系统', '3', '4')
INSERT INTO ST.Course (Cno, Cname, Cpno, Ccredit) VALUES ('4', '操作系统', '4', '3')
INSERT INTO ST.Course (Cno, Cname, Cpno, Ccredit) VALUES ('5', '数据结构', '5', '4')
INSERT INTO ST.Course (Cno, Cname, Cpno, Ccredit) VALUES ('6', '数据处理', '6', '2')
INSERT INTO ST.Course (Cno, Cname, Cpno, Ccredit) VALUES ('7', 'PASCAL语言', '7', '4')
go
INSERT INTO ST.SC(Sno,Cno,Grade)
VALUES('201215121','1','92'),
('201215121','2','85'),
('201215121','3','88'),
('201215122','2','90'),
('201215122','3','80')
update ST.Course
set Cpno='5' where Cno='1';
update ST.Course
set Cpno='1' where Cno='3';
update ST.Course
set Cpno='6' where Cno='4';
update ST.Course
set Cpno=null where Cno='6';
update ST.Course
set Cpno=null where Cno='2';
update ST.Course
set Cpno='7' where Cno='5';
update ST.Course
set Cpno='6' where Cno='7';
go
5、查看表内容
use student
go
--单表查询
Select * FROM ST.student
Select * FROM ST.Course
Select * FROM ST.SC
go
查询结果：
二、实验 1.2 数据基本查询实验
（一）实验目的
掌握 SQL 程序设计基本规范，熟练运用 SQL 语言实现数据基本查询，包括单表查询、分组统计查询和连接查询。
（二）实验内容和要求
针对某个数据库设计各种单表查询 SQL 语句、分组统计查询语句；设计单个表针对自身的连接查询，设计多个表的连接查询。理解和掌握 SQL 查询语句各个子句的特点和作用，按照 SQL 程序设计规范写出具体的 SQL 查询语句，并调试通过。说明：简单地说，SQL 程序设计规范包含 SQL 关键字大写、表名、属性名、存储过程名等标识符大小写混合、SQL 程序书写缩进排列等编程规范。
（三）实验过程
单表查询
全表查询
use student
go
Select * FROM ST.Student
Select * FROM ST.Course
Select * FROM ST.SC
go
部分列查询
select Sno,Sname
from ST.Student;
实现选择操作
select Sname
from ST.Student
where Sdept='CS' and Sage<20;
ORDER BY子句
查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。
SELECT Sno,Grade
FROM ST.SC
WHERE Cno= '3'
ORDER BY Grade DESC;
聚集函数
查询学生201215121选修课程的总学分数。
SELECT SUM(Ccredit)
FROM ST.SC, ST.Course
WHERE Sno='201215121' AND SC.Cno=Course.Cno;
分组查询
使用group by语句
select Sdept,avg(sage)
from ST.Student
group by Sdept;
连接查询
自身连接：查询必修课的先修课
select first.Cno,second.CPno
from ST.Course first,ST.Course second
where first.Cpno=second.Cno;
两表连接(外连接)
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
from ST.Student left outer join ST.SC
on (Student.Sno=SC.Sno)
多表连接：查询每个学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno,Sname,Cname,Grade
FROM ST.Student,ST.SC,ST.Course /*多表连接*/
WHERE Student.Sno = SC.Sno
and SC.Cno = Course.Cno;
三、实验 1.3 数据高级查询实验
（一）实验目的
掌握 SQL 嵌套查询和集合查询等各种高级查询的设计方法等。
（二）实验内容和要求
针对自定义数据库，正确分析用户查询要求，设计各种嵌套查询和集合查
询。
（三）实验过程
非相关子查询：
内层独立查询，不涉及与外层相关的子查询。
select Sname
from ST.Student
where Sno in (select Sno from ST.SC where Cno='1');
相关子查询
内层查询需要依靠外层的某些参数为限定条件：找出每个学生超过他自己选修课程平均分的课程号
select Sno,Cno
from ST.SC x
where Grade>=
(select avg(Grade)
from ST.SC y
where y.Sno=x.Sno);
嵌套查询
查找选择了课程2的同学的名字
SELECT Sname /*外层查询/父查询*/
FROM ST.Student
WHERE Sno IN
(SELECT Sno /*内层查询/子查询*/
FROM ST.SC
WHERE Cno= '2')
查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno,Sname
FROM ST.Student
WHERE Sno IN
(SELECT Sno
FROM ST.SC
WHERE Cno IN
(SELECT Cno
FROM ST.Course
WHERE Cname='信息系统'
)
)
exist嵌套查询：查询所有选修了1号课程的学生姓名
SELECT Sname
FROM ST.Student
WHERE EXISTS
(SELECT *
FROM ST.SC
WHERE Sno=Student.Sno AND Cno= '1')
not exist嵌套查询：查询所有没有选修了1号课程的学生姓名
SELECT Sname
FROM ST.Student
WHERE NOT EXISTS
(SELECT *
FROM ST.SC
WHERE Sno = Student.Sno AND Cno='1'
)
集合查询
交集
SELECT *
FROM ST.Student
WHERE Sdept='CS'
INTERSECT
SELECT *
FROM ST.Student
WHERE Sage<=19
并集
SELECT Sno
FROM ST.SC
WHERE Cno='1' AND Sno IN
(SELECT Sno
FROM ST.SC
WHERE Cno='2')
差集
SELECT *
FROM ST.Student
WHERE Sdept='CS'
EXCEPT
SELECT *
FROM ST.Student
WHERE Sage <=19;
四、实验 1.4 数据更新实验
（一）实验目的
熟悉数据库的数据更新操作，能够使用 SQL 语句对数据库进行数据的插入、修改、删除操作。
（二）实验内容和要求
针对自定义数据库设计单元组插入、批量数据插入、修改数据和删除数据等 SQL 语句。理解和掌握 INSERT、UPDATE 和 DELETE 语法结构的各个组成成分， 结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的语句， 并调试成功。
（三）实验过程
INSERT基本语句
将学生张成民的信息插入到Student表中。
INSERT
INTO ST.Student
VALUES ('200215126','张成民','男',18,'CS')
插入一条选课记录( ‘201215125’，'1 ')。
INSERT
INTO ST.SC(Sno,Cno)
VALUES ('201215125','1')
插入子查询结果
CREATE TABLE Dept_age
(Sdept CHAR(15) , /* 系名*/
Avg_age SMALLINT) /*学生平均年龄*/
INSERT
INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage)
FROM ST.Student
GROUP BY Sdept
Select * FROM Dept_age
UPDATE语句
带子查询，修改多个元组的值：将计算机科学系全体学生的成绩置零。
UPDATE ST.SC
SET Grade=0
WHERE 'CS'=
(SELECT Sdept
FROM ST.Student
WHERE Student.Sno = SC.Sno)
DELETE语句
删除基本表
drop table ST.Student
drop table ST.Course
drop table ST.SC
删除模式
drop schema ST
带子查询的删除语句： 删除计算机科学系所有学生的选课记录。
DELETE
FROM ST.SC
WHERE 'CS'=
(SELECT Sdept
FROM ST.Student
WHERE Student.Sno=SC.Sno)
五、实验 1.5 视图
（一）实验目的
熟悉SQL语言有关视图的操作，能够熟练使用 SQL语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。
（二）实验内容和要求
针对给定的数据库模式，以及相应的应用需求，创建视图和带 WITH CHECK OPTION 的视图，并验证视图 WITH CHECK OPTION 选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。
（三）实验过程
创建视图
CREATE VIEW CS_Student
AS
SELECT Sno,Sname,Sage
FROM ST.Student
WHERE Sdept= 'CS';
SELECT *FROM CS_Student;
查询视图
在信息系学生的视图中找出年龄小于20岁的学生。
SELECT Sno,Sage
FROM CS_Student
WHERE Sage<20;
查询选修了1号课程的信息系学生
SELECT CS_Student.Sno,Sname
FROM CS_Student,ST.SC
WHERE CS_Student.Sno =SC.Sno AND SC.Cno= '1';
with check option验证
--建立视图
create view seniorstu(Sno,Sage)
as select Sno,Sage
from ST.Student
where Sage<='19' with check option;
--查看视图
SELECT * FROM seniorstu
--删除视图
drop view seniorstu;
--插入有效数据
insert into seniorstu values('201215129',17);
--删除数据，否则再插入时会显示冲突
DELETE
FROM ST.Student
WHERE Sno= '201215129';
--插入无效数据
insert into seniorstu values('201215129',21);
第一条能插入，因为sage=18<=19，而第二条不能插入，因为sage=21>19。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZqkRzkbR-1650081153457)(C:/Users/Lenovo/AppData/Roaming/Typora/typora-user-images/image-20220331102857335.png)]
更新视图
将信息系学生视图CS_Student中学号200215122的学生姓名改为“刘辰”。
UPDATE CS_Student
SET Sname= '刘辰'
WHERE Sno= '201215122';
向信息系学生视图CS_Student中插入一个新的学生记录：200215129，赵新，20岁
INSERT
INTO CS_Student
VALUES('95029','赵新',20 )
删除视图
删除信息系学生视图CS_Student中学号为200215126的记录
DELETE
FROM CS_Student
WHERE Sno= '200215126';
DROP VIEW CS_Student
六、问题和解决方案
1、SQL-server2012使用T-SQL语句对数据库、模式、表、视图、约束、触发器、变量、函数的相关操作
https://blog.csdn.net/qq_43127132/article/details/90177902
2、针对Sql Server中进行查询操作时提示“对象名无效”
https://blog.csdn.net/weixin_42153410/article/details/81254933
3、Sql Server视频教程
https://www.bilibili.com/video/BV1Hg411u78M?p=12&spm_id_from=333.880.my_history.page.click
删除视图
删除信息系学生视图CS_Student中学号为200215126的记录
DELETE
FROM CS_Student
WHERE Sno= '200215126';
DROP VIEW CS_Student
六、问题和解决方案
1、SQL-server2012使用T-SQL语句对数据库、模式、表、视图、约束、触发器、变量、函数的相关操作
https://blog.csdn.net/qq_43127132/article/details/90177902
2、针对Sql Server中进行查询操作时提示“对象名无效”
https://blog.csdn.net/weixin_42153410/article/details/81254933
3、Sql Server视频教程
https://www.bilibili.com/video/BV1Hg411u78M?p=12&spm_id_from=333.880.my_history.page.click
