SQL Server 数据库实验课第三周——INDEX、INSERT、SELECT语句

本节课内容 ：学习INDEX、INSERT、SELECT语句的使用

在数据库stu中，建立三张表Student ,Course,SC

DROP TABLE IF EXISTS SC
DROP TABLE IF EXISTS Student
DROP TABLE IF EXISTS Course

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,
 Cname CHAR(40),            
 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),  /* 表级完整性约束条件，Sno是外码，被参照表是Student */
 FOREIGN KEY (Cno)REFERENCES Course(Cno)     /* 表级完整性约束条件， Cno是外码，被参照表是Course*/
 ); 


INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
INSERT  INTO  Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);

SELECT * FROM Student

INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('1','数据库',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('2','数学',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('3','信息系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('4','操作系统',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('5','数据结构',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('6','数据处理',NULL,4);
INSERT  INTO Course(Cno,Cname,Cpno,Ccredit)	VALUES ('7','Pascal语言',NULL,4);

UPDATE Course SET Cpno = '5' WHERE Cno = '1' 
UPDATE Course SET Cpno = '1' WHERE Cno = '3' 
UPDATE Course SET Cpno = '6' WHERE Cno = '4' 
UPDATE Course SET Cpno = '7' WHERE Cno = '5' 
UPDATE Course SET Cpno = '6' WHERE Cno = '7' 

SELECT * FROM Course

INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT  INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);

SELECT * FROM SC

一、INDEX索引的建立与删除

索引：是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，常见索引包括顺序文件上的索引、B+树索引，散列索引，位图索引等。

1.建立索引

语句格式

CREATE [UNIQUE] [CLUSTER] INDEX <索引名> 
ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);

<表名>： 要建索引的基本表的名字
索引： 可以建立在该表的一列或多列上，各列名之间用逗号分隔
<次序>： 指定索引值的排列次序，升序：ASC，降序：DESC。默认值为ASC
UNIQUE： 此索引的每一个索引值只对应唯一的数据记录
CLUSTER： 表示要建立的索引是聚簇索引。物理上有序，检索速度快。
例题3.13：为学生-课程数据库中三个表建立索引。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);
--按学号升序，若学号相同按课程号降序

2.修改索引

可用ALTER INDEX语句对索引重新命名
语句格式

ALTER INDEX <旧索引名> RENAME TO <新索引名>;

例题3.14 ：将SC表的SCno索引名改为SCSno

ALTER INDEX SCno RENAME TO SCSno

但是SQL Server上报错，因alter不能改变索引名，如若改变索引名就需要调用存储过程。
故若代码应写成

EXEC sp_rename'SC.SCno','SCSno','index';

参考博客
【alter更改索引名】‘rename’ 附近有语法错误。

3.删除索引

语句格式

DROP INDEX <索引名>;

删除索引时，系统会从数据字典中删去有关该索引的描述
例题3.15
格式

DROP INDEX Stusname;

SQL Server报错

加上表名即可，代码改为

DROP INDEX Student.Stusno

或

 DROP INDEX Stusno on Student;

二、INSERT插入数据

1.插入元组

语句格式

INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );

INTO子句：
（1）指定要插入数据表名及属性列
（2）属性列的顺序可以与表定义中顺序不一致
（3）没有指定属性列：插入为一条完整元组，且属性列与表定义顺序一致
（4）指定部分属性列：插入元组再其余属性列上取空值
VALUES子句：
提供的值的个数、类型必须与INTO子句匹配
例题3.69 将一个新学生元组（学号：201215128，姓名：陈冬，性别：男，所在系：IS，年龄：18岁）插入到Student表中

INSERT
INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES('201215128','王辉','男','IS',18)

也可改变属性列的顺序，不过结果是一样的
插入的时候要注意相关数据的约束如：主键约束，外键约束，非空约束等和相关完整性原则
例题3.70没有指定属性列的插入，插入为一条完整元组

INSERT 
INTO Student
VALUES('201215126','张成民','男',17,'CS');

尝试插入一条选课记录

INSERT
INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES ('1','数据库','5',4);

报错

因为参照完整性约束，Cpno要么为null要么等于Course中某个元组主码值，此语句为创建第一个元组，主码值只有1，所以可将Cpno先为NULL，都录完以后再修改

INSERT
INTO Course(Cno,Cname,Cpno,Ccredit)
VALUES ('1','数据库',NULL,4);

例题3.71插入一条选课记录

INSERT
INTO SC(Sno,Cno)
VALUES('201215128','1');

注意因为参照完整性，插入的学号Sno和课程号Cno都是在Student和Course里面已有的

三、SELECT查询

1.选择表中的若干列

例题3.16查询全体学生的学号与姓名

SELECT Sno,Sname
FROM Student;

例题3.18查询全部列

SELECT * FROM Student;

等价于

SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student;

（3）查询经过计算的值
查询学生姓名及其出生年份

SELECT Sname,2014-Sage
FROM Student;

2.选择表中的若干元组

（1）DISTINCT消除取值重复的行
查询选修课名称
如果没有指定DISTINCT关键词，默认为ALL保留结果中取重复值的行
代码

SELECT Cname FROM Course

或者

SELECT ALL Cname FROM Course

指定DISTINCT，去掉表中重复的行

SELECT DISTINCT Cname FROM Course

（2）查询满足条件的元组
查询满足指定条件的元组可以通过WHERE子句实现。
查询选课名称为数据库1的选课信息

SELECT *
FROM Course
WHERE Cname='数据库1'

逻辑运算符AND和OR可以用来连接多个查询条件

SELECT *
FROM Course
WHERE Cname='数据库1'AND Cno='2';