实验一

前言

  • 注：TIT的校友只需要运行二、1.2创建表格，三、1.建立索引，四、1.2插入表格信息。其他内容为做实验过程中扩充的知识点，可作为SQL学习使用

  • 参考教材《数据库系统概论第五版》 ==一、二、三为P82—P93，四、表格信息为P118内容==

  • 第六点为遗留问题，但不影响代码运行。解决后会回来修改

  • 本篇主要介绍SQL语句，在SQL Server中也可以使用键鼠操作，我参考了这篇，很不错！《数据库实验》实验一：建立数据库和基本表结构_三桥君-CSDN博客

  • 实验内容和要求

    1．创建数据库和查看数据库属性。

    2．创建表、确定表的主码。

    3．查看和修改表结构。

    4．具体内容：

    （1）使用SQL语句按教材中的内容建立学生数据库。

    （2）查看学生数据库的属性，并进行修改，使之符合要求。

    （3）使用SQL语句，在建好的学生数据库中建立学生、课程、选课和系部4个表，其结构为；

    • 学生（学号，姓名，年龄，性别，所在系）

    • 课程（课程号，课程名，先行课，学分）

    • 选课（学号，课程号，成绩）

    • 系部（系号，系名称，系主任）

    要求:

    （1）建库、建表和建立表间联系。(表间联系通过外键实现，在属性关系中建)

    （2）选择合适的数据类型。

    （3）定义必要的主键和索引。

    （4）使用SQL语句在上述4个表中尽可能的多输入些数据（每个表不能少于10条记录）。要求记录不仅满足数据约束要求，还要有表间关联的记录。

一、创建数据库和查看数据库属性

1.创建数据库

右击数据库，点击“新建数据库”，命名“学籍管理”

SQL语句：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>

2.查看数据库属性

右击学籍管理，点击“属性”

SQL语句：

sp_helpdb 学籍管理  --使用数据库信息报告的系统过程
select *from sys.databases where name='学籍管理'

第一种信息更为全面

二、基本表

1.创建表、确定表的主码

1.1右击学籍管理新建

  • 展开新建的数据库，右击“新建表”

    image-20211129234857087

    右击列名可以修改其属性

    image-20211129234938005

1.2使用SQL语句创建

创建前先展开”学籍管理“数据库，或在CREATE TABLE前加一句”USE 学籍管理“表示新建表在学籍管理数据库中或下图（常用按键）

image-20211129112301629

点击新建查询，写入代码，执行代码，右击刷新即可看到新建表格

  • 学生（学号，姓名，年龄，性别，所在系）
CREATE TABLE Student(
    Sno CHAR(9) PRIMARY KEY, /*列级完整性约束条件，Sno是主码*/
    Sname CHAR(20) UNIQUE,/*Sname取唯一值*/
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);
  • 课程（课程号，课程名，先行课，学分）
CREATE TABLE Course(
    Cno CHAR(4) PRIMARY KEY,/*列级完整性约束条件，Cno是主码*/
    Cname CHAR(40) NOT NULL,/*列级完整性约束条件，Cname不能取空值*/
    Cpno CHAR(4),
    Ccredit SMALLINT,
    --FOREIGN KEY(Cpno) REFERENCES Course(Cno)/*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);
  • 选课（学号，课程号，成绩）
CREATE TABLE SC(
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY(Sno,Cno),/*主码由两个属性构成，必须作为表级完整性进行定义*/
    FOREIGN KEY(Sno) REFERENCES Student(Sno),/*表级完整性约束条件，Sno是外码，被参照表是Student*/
    FOREIGN KEY(Cno) REFERENCES Course(Cno)/*表级完整性约束条件，Cno是外码，被参照表是Course*/
);
  • 系部（系号，系名称，系主任)
CREATE TABLE Dept(
    Dno CHAR(10) PRIMARY KEY,
    Dname CHAR(40),
    Ddictor CHAR(20)
);

扩充：==主键/主码 ：primary key==，一个表可能有多个，往往选中一个作为主键. 外键/外码 ：foreign key，假设表A的某个属性attr是另一表B中的主码，且A和B有某种联系，则称attr是外码

2.查看基本表结构

查看Course表的结构

sp_help table_name

sp_help Course

sp_columns Student

sp_columns Course;

区别: 第一种信息更全面

3.修改基本表

3.1增加列

向Student中增加入学时间，数据类型为日期型

ALTER TABLE Student ADD S_entrance DATE;

3.2修改数据类型

修改年龄字符型为整数型

ALTER TABLE Student ALTER COLUMN Sage INT;

3.3修改约束条件

增加课程名称必须取唯一值的约束条件

ALTER TABLE Course ADD UNIQUE(Cname);

3.4删除约束条件

删除FOREIGN KEY(Cno) REFERENCES Course(Cno)，FK__SC__Cno__2C3393D0在键中查看

ALTER TABLE SC DROP CONSTRAINT FK__SC__Cno__2C3393D0

4.删除基本表

删除基本表时会删除索引与触发器等对象，例如删除Student，则与Student索引的SC中Sno也会删除

CASCADE：删除表和视图

DROP TABLE Student CASCADE;

RESTRICT: 若有视图，系统返回错误信息，此表不能删除

DROP TABLE Student RESTRICT;

视图(将多张表组合为一张表）这里只以一张表做列子，实验一入门SQL，不做赘述

CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept='IS';

三、索引

1.建立索引

能够加速数据库查询，但需要占用一定的储存空间，当基本表更新时，索引需要维护，增加数据库负担，所以根据实际选择地创建

格式:

UNIQUE: 索引的每一个索引值只对应唯一的数据记录

CLUSTER: 建立的索引是聚簇索引

CREATE [UNIQUE/CLUSTER] INDEX <索引名> ON <表名>(<列名> <次序:ASC升序，DESC降序>);

建立Student 、Course、SC三个表的索引

CREATE UNIQUE INDEX Stusno ON Student(Sno);
CREATE UNIQUE INDEX Coucno ON Course(Cno);
CREATE UNIQUE INDEX SCno ON SC(Sno ASC,Cno DESC);

查看结果：建立好后展开Student，展开索引查看对应属性

SQL语句：

use 学籍管理
exec sp_helpindex Student

2.修改索引

将SC中的SCno索引修改为SCSno

ALTER INDEX SCno RENAME TO SCSno;

3.删除索引

删除Student中的Stusname索引

DROP INDEX Stusname;

四、表格信息

可以右击表格“编辑前200行”，进行编辑或查看，实现表格信息中增删改

SQL语句：

1.插入表格信息(增)

1.1只指出表名，无属性，则必须按CREATE TABLE中的属性赋值

INSERT INTO Student
VALUES('192054401','张帆','男','20','计算机工程系');

1.2属性顺序可以与CREATE TABLE不同，但VALUES与INTO必须对应

INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES('192054401','张帆','男','20','计算机工程系'),('192054402','万润南','女','21','计算机工程系'),('192054403','白峰','男','20','计算机工程系'),('192054404','汤明学','男','19','计算机工程系')
,('192054405','王政杰','男','20','计算机工程系'),('192054406','张力','男','22','计算机工程系')
,('192062101','刘俊卓','男','18','数学系'),('192062102','李云龙','男','21','数学系')
,('192062103','刘宇豪','男','20','数学系'),('192062104','江鑫艺','女','21','数学系')
,('192062105','王鼎','男','21','数学系'),('192062106','申宇宙','男','20','数学系'),('192062107','樊倩','男','20','数学系'),('192062108','武文琴','男','20','数学系');

INSERT INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES('001','数据库','5','4'),('002','数学','','2'),('003','信息系统','1','4'),('004','操作系统','6','3'),('005','数据结构','7','4'),('006','数据处理','','2'),('007','PASCAL语言','6','4'),('008','C++','2','4'),('009','C++面向对象','3','4'),('010','JAVA','4','4');

INSERT INTO Dept(Dno,Dname,Ddictor)
VALUES('1','王宇藩','机械工程系'),('2','殷爱博','电子工程系'),('3','秦立伟','自动化系'),('4','武文琴','化学与化工系'),('5','樊倩','计算机系'),('6','董佳杰','环境与安全工程系'),('7','郭文裕','新能源系'),('8','刘尧','理学系'),('9','曲婷婷','经管系'),('10','张伟豪','外语系');

在这里注意，选课中的Sno被Student中的Sno所约束，不能一次运行完成代码。所以要先插入上面代码，之后再插入SC信息

INSERT INTO SC(Sno,Cno,Grade)
VALUES
('192054401','001','88'),('192054401','002','85'),('192054401','003','70'),('192054401','004','80'),('192054401','005','71'),('192054401','006','73'),('192054401','007','72'),('192054401','008','75'),
('192054401','009','80'),('192054401','010','70'),('192054402','002','85'),('192054403','002','84'),('192054404','002','80'),('192054405','002','88'),('192054406','002','70'),('192062101','001','88'),('192062102','001','85'),('192062103','001','84'),('192062104','001','87'),('192062105','001','83'),('192062106','001','84'),('192062107','001','80'),('192062108','001','86'),('192054406','001','83'),('192054404','001','');

2.查询表格信息(查)

2.1.自定义列显示顺序，也可以单独看某几列

SELECT Sno,Sname,Ssex,Sage,Sdept/*默认为ALL,也可定义为DISTINCT*/
FROM Student;

2.2当表中有重复信息

SELECT DISTINCT Sno/*表示查看Sno列有两个192062116时只显示其中一行*/
FROM Student;

2.3查看全部

SELECT *
FROM Student;

3.删除表格信息(删)

3.1删除一个元组的值

删除192054401的学生记录

DELETE
FROM Student
WHERE Sno='192054401'

3.2删除多个元组的值

删除所有的选课记录

DELETE
FROM SC;

3.3.带子查询的删除语句

删除计算机系所有学生的选课记录

DELETE
FROM SC
WHERE Sno IN(
    SELECT Sno
    FROM Student
    WHERE Sdept='计算机系'
);

4.修改表格信息(改)

4.1修改一个元组的值

修改192054401的学生年龄为21

UPDATE Student
SET Sage=21
WHERE Sno='192054401';

4.2修改多个值

将所有的学生年龄加一

UPDATE Student
SET Sage=Sage+1;

五、思考题

为什么要建立索引？

当表的数据量比较大时，查询操作会比较耗时，建立索引是加快查询速度的有效手段，用户可以根据应用环境的需要在基本表上建立一个或多个索引，以提供多种存取路径，加快查找

学生库选课表中的学号、课程号采用数值型、还是采用字符型的？采用哪种数据类型更好？

字符型。数值型只能代表数字，当学号前缀有字符时，不能使用。

六、遗留问题

二、1.2中建立Course，第六行，Cpno被Cno约束后，无法修改Cpno的值，参考下面这两篇仅理解外键被约束后，主键必须先有值再填写外键。但问题没有解决。解决后回来重写！

[INSERT 语句与 FOREIGN KEY 约束”XXX”冲突。该冲突发生于数据库”XXX”，表”XXX”, column ‘XXX。_Mr.zhou-CSDN博客](https://blog.csdn.net/love20165104027/article/details/78343748?ops_request_misc=&request_id=&biz_id=102&utm_term=INSERT 语句与 FOREIGN KEY 约束”FK__&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduweb~default-3-78343748.first_rank_v2_pc_rank_v29&spm=1018.2226.3001.4187)

[INSERT语句与FOREIGN KET约束‘FK__SC__Cno__2C3393D0‘冲突，该冲突发生于数据库‘Test20210322‘，表‘dbo.Course‘，column‘Cno‘_+51的博客-CSDN博客](https://blog.csdn.net/qq_51776413/article/details/115131064?ops_request_misc=&request_id=&biz_id=102&utm_term=INSERT 语句与 FOREIGN KEY 约束”FK__&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduweb~default-1-115131064.first_rank_v2_pc_rank_v29&spm=1018.2226.3001.4187)