仅以此篇博客纪念让我羞愧的一次笔试，作为对数据库基础的恶补。
一、SQL的基本概念：
SQL是集数据定义语言DDL，数据操纵语言DML，数据控制语言DCL的功能于一体，可以独立完成数据库生命周期的全部活动。
包括：定义关系模式，插入数据，建立数据库；
对数据库中的数据进行查询和更新；
数据库重构和维护；
数据库安全性、完整性控制；等一系列操作要求。
二、关系数据库系统的三级模式结构：
①外模式：对应于视图和部分基本表；
②模式：对应于基本表；基本表是本身独立存在的表，在SQL中一个关系就对应一个基本表，一个（或多个）基本表对应一个存储文件。
③内模式：对应于存储文件。存储文件的逻辑结构是任意的，对用户透明。
三、数据定义
以下用一个例子来讲解SQL的数据定义、数据操纵，数据查询和数据控制语句的具体应用。
典例：学生—课程数据库
包含以下3个表：
- 学生表：Student(Sno, Sname, Ssex, Sage, Sdept)
- 课程表：Course(Cno, Cname, Cpno, Ccredit)
- 学生选课表：SC(Sno, Cno, Grade)
3.1 基本表的定义
1、建立一个学生表Student：
create table Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
系统执行上面的create table语句后，就在数据库中建立一个新的空的“学生表”Student，并将有关“学生”表的定义及有关约束条件存放在数据字典中。
2、建立一个“课程”表Course：
create table Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4), /*选修课*/
Ccredit SMALLINT,
FOREIGN KEY Cpno REFERENCES Course(Cno) /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);
3、建立学生选课表SC：
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*/
);
3.2 修改基本表
SQL语言用ALTER TABLE语句修改基本表，其一般格式为：
ALTER TABLE <表名>
[ADD <新列名><数据类型>[完整性约束]] /*增加新列*/
[DROP <完整性约束名>] /*删除*/
[ALTER COLUMN<列名><数据类型>]； /*修改原有的列定义，包括修改列名和数据类型*/
【例1】向Student表增加“入学时间”列，其数据类型为日期型。
ALTER TABLE Student ADD S_entrance DATE;
【例2】将年龄的数据类型由字符型改为整数。
ALTER TABLE Student ALTER COLUMN Sage INT;
3.3 删除基本表
DROP TABLE <表名>[RESTRICT | CASCADE];
若选择RESTRICT：则该表的删除有限制条件，欲删除的基本表不能被其他表的约束所引用(如CHECK, FOREIGN KEY等约束)；
若选择CASCADE：删除没有限制条件。但相关的依赖对象也会被一起删除。
3.4 建立索引
CREATE [UNIQUE | CLUSTER] INDEX <索引名> ON<表名>(<列名>[次序]...);
- 次序可选ASC(升序)或DESC(降序)，缺省值为ASC。
- CLUSTER：聚簇索引，是指索引项的顺序与表中记录的物理顺序一致的索引组织。
【例】CREATE CLUSTER INDEX Stusname ON Student(Sname);
将会在Student表的Sname列上建立一个聚簇索引，而且Student表中的记录将按照Sname值升序存放。
3.5 删除索引
DROP INDEX <索引名>
四、SQL语句
4.1 常见SQL语句
- 选择：select * from table1 where 范围
- 插入：insert into table1(field1,field2) values(value1,value2)
- 删除：delete from table1 where 范围
- 更新：update table1 set field1=value1 where 范围
- 查找：select * from table1 where field1 like ’%value1%’
- 排序：select * from table1 order by field1,field2 [desc]
- 总数：select count as totalcount from table1
- 求和：select sum(field1) as sumvalue from table1
- 平均：select avg(field1) as avgvalue from table1
- 最大：select max(field1) as maxvalue from table1
- 最小：select min(field1) as minvalue from table1
扩展：字符匹配
- %：代表任意长度（长度可以为0）的字符串。例如：a%b表示以a开头，以b结尾的字符串。
- _ ：代表任意单个字符。
- PS：如果用户要查询的字符串本身就含有通配符%或_ ,要使用ESCAPE '<换码字符>'短语，对通配符进行转义。
【例1】查询所有姓刘的学生的姓名，学号和性别。
SELECT Sname, Sno, Ssex
FROM Student
WHERE Sname LIKE '刘%';
4.2 GROUP BY子句
GROUP B子句将查询的结果按某一列或多列的值分组，值相等的为一组。
【例1】求各个课程号及相应的选课人数。 SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno; 该语句对查询结果按Cno的值分组，所有具有相同Cno值得元组为一组，然后对每一组作用聚集函数COUNT计算，以求得该组的学生人数。
【例2】查询选修了3门以上课程的学生学号。
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*) >3;
这里先用GROUP BY子句按Sno进行分组，再用COUNT对每一组进行计数，HAVING短语给出了选择组的条件（元组个数>3）。
PS：WHERE子句和HAVING短语的区别：
作用对象不同，WHERE子句作用于基本表或者视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。
五、连接查询
5.1 等值与非等值连接查询
比较运算符有：=，>,<,>=,<=,!=等。
【例】(等值连接) 查询每个学生及其选修课程的情况。
SELECT Student.*, SC.*
FROM Student, SC
WHERE Student.Sno = SC.Sno;
5.2 外连接
外联接可以是左向外联接、右向外联接或完整外部联接。在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
【例】SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student LEFT JOIN SC ON(Student.Sno = SC.Sno);
2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
posted on
