xingzhe

数据库
关系的完整性
1.实体完整性，就是主属性的取值不能为空。
2.参照完整性，若属性F是基本关系R的外码，它与基本关系S的主码K相对应，则R中每个元组在F上的值必须为：空；等于S中的主码。
3.用户定义的完整性。
SQL语言动词
数据查询：SELECT
数据定义：CREATE, DROP, ALTER
数据操纵：INSERT, UPDATE, DELETE
数据控制：GRANT, REVOKE
SQL语言支持关系数据库的三级模式:外模式（视图），模式（基本表）和内模式（存储文件），基本对象视图，表和索引。

一、表

1.定义表
CREATE TABLE Student(Sno CHAR(5) NOT NULL UNIQUE,
Sname CHAR(10) UNIQUE,
Ssex CHAR(1),
Sage INT,
Sdept CHAR(15));

2.修改表
增加字段
ALTER TABLE Student ADD Scome DATE;

修改字段属性
ALTER TABLE Student MODIFY Sage SMALLINT;

删除字段
ALTER TABLE Student DROP UNIQUE Sname;

3.删除表
DROP TABLE Student;

二、索引
聚族索引：索引项的顺序与表中的物理顺序一致的索引组织。
CREATE CLUSTER INDEX Stusname ON Student(Sname0);
ASC:升序
DESC:降序

CREATE UNIQUE INDEX Stusno ON Student(Sno);

DROP INDEX Stusno;

三、查询

SELECT [ALL|DISTICT]<目标表达式>[,<目标表达式>]...
FROM <表或视图>[,<表或视图>]...
[WHERE<条件表达式>]
[GROUP BY <列名>[HAVING<条件表达式>]]
[ORDER BY <列名>[ASC|DESC]];
根据where子句的条件从from指定的表或者视图中找出满足条件的元组，再按照select的目标表达式选出元组中的属性值形成结果表。
如果有group子句，则将结果按照列名分组，该属性列值相等的元组为一个组。
如果有having子句，则只有满足指定条件的组才能输出。
如果有order子句，则结果按升序或者降序排序。

Student(Sno, Sname, Ssex, Sage, Sdepe);
Course(Cno, Cname, Cpno, Ccredit);
SC(Sno, Cno, Grade);

3.3.1 单表查询

1.查询指定列
SELECT Sno, Sname
FROM Student;

2.查询全部列
SELECT *
FROM Student;

3.经过计算的查询
SELECT Sno, 1996-Sage
FROM Student;

SELECT Sname, 'Year of Birth', 1996-Sage, ISLOWER(Sdept)
FROM Student;
利用别名查询
SELECT Sname NAME, 'Year of Birth' BIRTH, 1996-Sage BIRTHDAY, ISLOWER(Sdept) DEPARTMENT
FROM Student;

4.消除表中的重复行
SELECT DISTINCT Sno
FROM Student;

5.查询满足条件的元组
比较： =、 <、>、 >=、 <=、 !=、 <>、 !>、 !<、 NOT+。
查询计算机系全体学生的名单
SELECT Sname
FROM Student
WHERE Sdepe='CS';

SELECT Sname, Sage
FROM Student
WHERE Sage<20;

限定范围：BETWEEN AND, NOT BETWEEN AND.
SELECT Sname
FROM Student
WHERE Sage NOT BETWEEN 20 AND 24;

限定集合：IN, NOT IN.
SELECT Sname, Ssex
FROM Student
WHERE Sdepe IN ('IS','MA');

字符匹配：LIKE, NOT LIKE.
%:任意个字符
-:单个字符
SELECT *
FROM Student
WHERE LIKE '9800';

SELECT *
FROM Student
WHERE Sno = '9800';

查询所有不姓刘的学生姓名
SELECT Sname
FROM Student
WHERE Sname NOT LIKE'刘%';

空值： IS NULL, IS NOTNUUL.
SELECT Sno, Sname
FROM Student
WHERE Grade IS NULL;
有成绩的所有学生
SELECT Sname
FROM Student
WHERE Grade IS NOT NULL;

多重条件：AND, OR.
SELECT Sname
FROM Student
WHERE Sdept='CS' AND Sage<20;

三、对结果进行排序
SELECT Sno, Grade
FROM SC
WHERE Sdept = '3'
ORDER BY Grade DESC;

查询学生的所有情况，按系升序排列，同一个系的按学生降序排列
SELECT *
FROM Student
ORDER BY Sdepe ASC, Sage DESC;

四、使用函数集
COUNT([DISTINCT|ALL]*)
COUNT([DISTINCT|ALL]<列名>)
SUM([DISTINCT|ALL]<列名>)
AVG([DISTINCT|ALL]<列名>)
MAX([DISTINCT|ALL]<列名>)
MIN([DISTINCT|ALL]<列名>)

SELECT COUNT(*)
FROM Student;

SELECT COUNT(DISTINCT Sno)
FROM SC;

SELECT AVG(Grade)
FROM SC
WHERE Sno = '1';

Student(Sno, Sname, Ssex, Sage, Sdept);
Course(Cno, Cname, Cpno, Ccredit);
SC(Sno, Cno, Grade);

五、对查询结果分组
求各个课程号及其相应的人数
SELECT Cno, COUNT(Sno)
FROM SC
GROUP BY Cno;

查询选修了3门以上课程的学生学号
SELECT Sno
FROM SC
GROUP BY Sno//按照学号分组
HAVING COUNT(*)>3;//每组的元素大于3的
HAVING 作用于组，从中选择满足条件的组。

3.3.2连接查询
等值连接 自然连接 非等值连接 自身连接 外连接 符合连接
一、等值与非等值连接
= < > <= >= !=
BETWEEN AND
每个学生及其选修课程的情况
SELECT Sno.*, SC.*
FROM Studen, SC
WHERE Student.Sno=SC.Sno
二、自身连接
三、外连接
四、符合条件连接
查询选修2号课程且成绩在90分以上的所有学生
SELECT Student.Sno, Sname
FROM Student, SC
WHERE Student.Sno=SC.Sno AND
SC.Grade>90 AND
SC.Cno='2';

3.3.3嵌套查询
一、带IN的子查询
与孙在同一个系的学生
SELECT Student.Sname
FROM Student
WHERE Sdept IN
( SELECT Sdept
FROM Student
WHERE Sname='孙'
);

二、带比较运算符的子查询
SELECT Student.Sname
FROM Student
WHERE Sdept =
(SELECT Sdepe
FROM Student
WHERE Sname='孙');

三、带有ANY或者ALL的子查询
四、带有EXISTS的子查询

Student(Sno, Sname, Ssex, Sage, Sdepe);
Course(Cno, Cname, Cpno, Ccredit);
SC(Sno, Cno, Grade);
查询所有选修了1号课程的学生
SELECT Sname
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC
WHERE Cno='1');

SELECT Sname
FROM Student
WHERE EXISTS
(SELECT*
FROM SC
WHERE Sno=Student.Sno AND Cno='1');

3.3.4集合查询
UNION INTERSECT MINUS
选修了1号或者2号课程的学生
SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno='2';

3.4数据更新
3.4.1插入数据
INSERT
INTO Student
VALUES('001','SUNHAIMIN','男','IS','19');

对每个系求学生的平均年龄，并把结果存入数据库
CREATE TABLE Deptage
(Sdepe CHAR(15),
Avgage SMALLINT);
)

INSERT
INTO Deptage(Sdepe, Avgage)
SELECT Sdept, AVG(Sage)
FROM Student
GROUP BY Sdept;

3.4.2修改数据
UPDATE Student
SET Sage=32
WHERE Sno='001';

UPDATE Student
SET Sage=Sage+1;

UPDAE SC
SET Grage=0
WHERE 'CS'=
(SELECT Sdept
FROM Student
WHERE Student.Sno=SC.Sno);

3.4.3删除数据
DELETE
FROM Student
WHERE Sno='11';

删除所有数据使之成为为空表
DELETE
FROM SC;