数据库作业5：SQL练习2 - INDEX / INSERT / SELECT

一、索引-----INDEX
1、建立索引语句格式：CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…);
<次序>：指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC
UNIQUE：此索引的每一个索引值只对应唯一的数据记录
CLUSTER：表示要建立的索引是聚簇索引
2、修改索引语句格式：ALTER INDEX <旧索引名> RENAME TO <新索引名>（实验过程中肯会出现语法错误）
3、删除索引语句格式：DROP INDEX <索引名>;
删除索引时，系统会从数据字典中删去有关该索引的描述。
建立索引
【例3.13】为学生-课程数据库中的Student，Course，SC三个表建立索引。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);

默认的顺序是升序。
在第三个索引中，Sno升序排列与Cno降序排列，这二者之间没有冲突，这是因为优先以Sno升序为主，当学号Sno有重复时，按照Cno降序排列。（例如有学生选修了多门课程，在按照学号升序排列的基础上，再按照课程号Cno降序排列，使得排列结果唯一。）
修改索引
【例3.14】将SC表的SCno索引名改为SCSno。

ALTER INDEX SCno RENAME TO SCSnO;

在运行过程中会显示在rename有语法错误，经过查资料得到解决方案，出现这样的错误是由于：alter不能改变索引名，如若改变索引名就需要调用存储过程。
修改之后的代码为：

EXEC sp_rename 'SC.SCno','SCSno','index';
/*格式为：EXEC sp_rename '表名.旧索引名','新索引名','index';*/

或者下面这个也可以运行成功：

EXEC sp_rename @objname='SC.SCno',@newname='SCSno',@objtype='index';

虽然这样命令执行成功了，但是会提示注意： 更改对象名的任一部分都可能会破坏脚本和存储过程。
删除索引
【例3.15】删除Student表的Stusno索引。

DROP INDEX Stusno;

在运行过程中出现错误“必须为 DROP INDEX 语句指定表名和索引名。”，经过查询资料得到：

DROP INDEX Stusno ON Student;
/*DROP INDEX 索引名 ON 表名*/

二、插入数据------INSERT
插入元组语句格式：INSERT
INTO <表名> [(<属性列1>[,<属性列2 >…)]
VALUES (<常量1> [,<常量2>]… );(或者其他的形式，在下面的例子中给出)
（1）INTO子句

• 指定要插入数据的表名及属性列
• 属性列的顺序可与表定义中的顺序不一致
• 没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致
• 指定部分属性列：插入的元组在其余属性列上取空值
  （2）VALUES子句
  提供的值必须与INTO子句匹配，值的个数，值的类型

插入元组
【例3.69】将一个新学生元组（学号：201215128;姓名：陈冬;性别：男;所在系：IS;年龄：18岁）插入到Student表中。

INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES('201215128','陈冬','男','IS',18);

要注意：
（1）这里插入的学号和姓名信息不能与之前的重复，如果重复会出现错误。
（2）属性名的顺序和相应的值要一一对应，但是他们的顺序可以变化或者不写（Sno,Sname,Ssex,Sdept,Sage），但是后面的值要全部都给定。

INSERT INTO Student VALUES('201215128','陈冬','男',18,'IS');

在选择需要运行的语句时，可以将不用的语句注释掉，也可以直接选中需要运行的语句。
在需要插入外码信息时，可以先将外码的值置为NULL，在最后在插入值，否则可能会出现外码不存在的情况。
例如：可以将Course表的Cpno置为空

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

【例3.70】插入一条选课记录（ ‘201215128’,'1 '）。
(1)

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

关系数据库管理系统将在新插入记录的Grade列上自动地赋空值。
(2)或者全部写出属性名，未给出属性值的设为NULL

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

(3)或者不再写出属性名，只明确的给出属性值（可以设为NULL）

 INSERT INTO SC VALUES (' 201215128 ',' 1 ',NULL);

注意：这里插入的学生学号和课程号必须是Student表和Course表已经存在的，Sno和Cno是SC表的外码，而Sno和Cno分别是Student和Course的主码，Student关系和Course关系均为被参照关系，SC关系为参照关系 。
外码要么为空，要么源自于被参照关系的主码。

【例3.71】将学生张成民的信息插入到Student表中。（INTO 子句不写属性名的情况）

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

三、查询-----SELECT
1、查询语句格式：SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[,<表名或视图名> ]…|(SELECT 语句)
[AS]<别名>
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
SELECT子句：指定要显示的属性列
FROM子句：指定查询对象（基本表或视图）
WHERE子句：指定查询条件
GROUP BY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语：只有满足指定条件的组才予以输出
ORDER BY子句：对查询结果表按指定列值的升序或降序排序
2、
单表查询–1.选择表中的若干列 ，查询指定列（关系代数中的投影）
【例3.16】查询全体学生的学号与姓名。

SELECT Sno,Sname
From Student;

【例3.17】查询全体学生的姓名、学号、所在系。

SELECT Sno,Sname,Sdept
FROM Student;

单表查询–1.查询全部列，选出所有属性列：（1）在SELECT关键字后面列出所有列名 （2）将<目标列表达式>指定为*
【例3.18】查询全体学生的详细记录.
(1)

SELECT *
FROM Student;

(2)

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

查询经过计算的值 ：SELECT子句的<目标列表达式>不仅可以为表中的属性列，也可以是表达式
【例3.19】查全体学生的姓名及其出生年份。

SELECT Sname,2019-Sage/*假设为2019年，当前年份-年龄=出生年份，*/
FROM Student;

注意：在属性列或表达式写完了之后不要再加逗号，容易因为习惯性而出现错误

【例3.20】查询全体学生的姓名、出生年份和所在的院系，要求用小写字母表示系名。

SELECT Sname,'Year oof Birth:',2019-Sage,LOWER(Sdept)
FROM Student;

LOWER：将英文的大写字母转换成小写字母；
Year oof Birth：：给出相应的提示信息;
【拓展1】
LOWER可以将大写转换成小写，UPPER函数可以将小写转换成大写

SELECT Sname,'Year oof Birth:',2019-Sage,Sdept
FROM Student;

SELECT Sname,'Year oof Birth:',2019-Sage,UPPER(Sdept)
FROM Student;

【拓展2】使用列别名改变查询结果的列标题:

SELECT Sname NAME,'Year of Birth:'BIRTH,2014-Sage BRTHDAY,LOWER(Sdept) DEPARTMENT
FROM Student; 

可以更改查询出来的列名，使得可队形更强。

单表查询----2.选择表中的若干元组
消除取值重复的行
如果没有指定DISTINCT关键词，则缺省为ALL
【例3.21】 查询选修了课程的学生学号

SELECT Sno FROM SC;
/*等价于：SELECT ALL Sno FROM SC;*/

运行结果如下：

这样会因为有学生选修了多门课程而有重复学号出现。
指定DISTINCT关键词，去掉表中重复的行 。
DISTINCT代表唯一的。

SELECT DISTINCT Sno
FROM SC;

查询满足条件的元组-----①比较大小WHERE后面加条件

【例3.22】查询计算机科学系全体学生的名单。

SELECT Sname
FROM Student
WHERE Sdept='CS';

【例3.23】查询所有年龄在20岁以下的学生姓名及其年龄。

SELECT Sname,Sage
FROM Student
WHERE Sage<20;

【例3.24】查询考试成绩有不及格的学生的学号。

SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;

只要有一门不合格就要记录，如果一个人有多门课程不及格只需记录一次就行，所以需要加DISTINCT

查询满足条件的元组-----②确定范围
谓词: BETWEEN … AND …
NOT BETWEEN … AND …
【例3.25】查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄.

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 23;

【例3.26】查询年龄不在20~23岁之间的学生姓名、系别和年龄

SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 23;

查询满足条件的元组-----③ 确定集合
谓词：IN <值表>, NOT IN <值表>
IN代表着“或”,满足值表中的一个条件即可，NOT IN代表着“既不也不，且否定”，需要满足值表中的所有条件。
【例3.27】查询计算机科学系（CS）、数学系（MA）和信息系（IS）学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN('CS','MA','IS');

查询是CS或MA或IS 的学生，只要是其中一个。

【例3.28】查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别.

SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN('CS','MA','IS');