数据库基础知识

来源：互联网发布：中国网络电视台手机版编辑：程序博客网时间：2024/05/01 01:19

数据定义：
定义基本表：CREATE TABLE <表名>(<列名><数据类型> [列级完整性约束条件] [，<列名> <数据类型> [列级完整性约束条件]...] [,<表级完整性约束条件>];

后面用到的表：
(1) “学生”表 Student 由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）5个属性组成，可记为： Student(Sno,Sname,Ssex,Sage,Sdept)
(2) “课程”表 Course 由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）4个属性组成，可记为： Course(Cno,Cname,Cpno,Ccredit)
(3) “学生选课”表 SC 由学号（Sno）、课程号（Cno）、成绩（Grade）3个属性组成，可记为： SC(Sno,Cno,Grade) (SNO, CNO, Grade)

修改基本表：ALTER TABLE <表名>[ADD <新列名><数据类型>[完整性约束]][DROP<完整性约束名><完整性约束名>] [MODIFY<列名> <数据类型><数据类型>];

删除基本表：DROP TABLE <表名>;

建立索引

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

删除索引：DROP INDEX<索引名>;

数据查询：

SELECT [ALL|DISTINCT]<目标列表达式>[,<目标列表达式>]...

FROM <表名或视图名>[,<表名或视图名>] ...

[WHERE <条件表达式>] [GROUP BY <列名1>[HAVING <条件表达式>]] [ORDER BY <列名2> [ASC|DESC]];

整个SELECT语句的含义是，根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表。如果有GROUP子句，则将结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每组中作用集函数。如果GROUP子句带HAVING短语，则只有满足指定条件的组才予输出。如果有ORDER子句，则结果表还要按<列名2>的值的升序或降序排序。

如果想去掉结果表中的重复行，必须在列名前指定 DISTINCT 短语。

常用的查询条件：
         查询条件                谓词
            比较                 =, >, <, >=, <=, !=, <>, !>, !<
                                         NOT+ 上述比较运算符
            确定范围             BETWEEN AND, NOT BETWEEN AND
            确定集合             IN, NOT IN
            字符匹配             LIKE, NOT LIKE
            空值                 IS NULL, IS NOT NULL
            多重条件             AND, OR

字符匹配基本语法：[NOT] LIKE '<匹配串>' [ESCAPE '<换码字符>']
查以“DB_”开头，且倒数第三个字符为i的课程的详细情况：
SELECT * FROM Course WHERE Cname LIKE ’DB/_%i _ _’ ESCAPE ’/’;

SQL 提供了许多集函数：
COUNT([DISTINCT|ALL] *) 统计元组个数
COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数
SUM([DISTINCT|ALL] <列名>) 计算一列值的总和（此列必须是数值型）
AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值（此列必须是数值型）
MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值
MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值

连接查询
用来连接两个表的条件称为连接条件或连接谓词。其一般格式为：

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
还有：[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>
其中比较运算符主要有：=、>、<、>=、<=、!=

自然连接：如果是按照两个表中的相同属性进行等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接

外连接的运算符通常为*。有的关系数据库中也用+。
外连接符*出现在连接运算符的右边，所以也称其为右外连接。相应地，如果外连接符出现在连接运算符的左边，则称为左外连接。
SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade
FROM Student, SC
WHERE Student.Sno=SC.Sno (*);

用集函数实现子查询通常比直接用ANY或ALL查询效率要高。

使用存在量词EXISTS后，若内层查询结果非空，则外层的WHERE子句返回真值，否则返回假值。由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名亦无实际意义。

查询选修了全部课程的学生姓名
等价于：查询这样的学生姓名，没有一门课程是他不选的。
该查询涉及三个关系，存放学生姓名的Student表，存放所有课程信息的Course表，存放学生选课信息的SC表。其SQL语句为：

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

查询至少选修了学生95002选修的全部课程的学生号码
查询这样的学生，凡是95002选修的课，他都选修了。换句话说，若有一个学号为x的学生，对所有的课程y，只要学号为95002的学生选修了课程y，则x也选修了y；那么就将他的学号选出来。它所表达的语义为：不存在这样的课程y，学生95002选修了y，而学生x没有选。用SQL语言可表示如下：

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
      (SELECT *
       FROM SC SCY
       WHERE SCY.Sno='95002' AND NOT EXISTS
             (SELECT *
              FROM SC SCZ
              WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno);

当连接运算符为=时，称为等值连接。使用其它运算符称为非等值连接

广义笛卡儿积：是不带连接谓词的连接。两个表的广义笛卡儿积即是两表中元组的交叉乘积，也即其中一表中的每一元组都要与另一表中的每一元组作拼接，因此结果表往往很大。

集合查询
集合操作主要包括并操作 UNION、交操作 INTERSECT 和差操作 MINUS。

数据更新
INSERT INTO <表名> [(<属性列1>[,<属性列2>...)] VALUES (<常量1> [,<常量2>]...)

插入子查询结果的INSERT语句的格式为：
INSERT INTO <表名> [(<属性列1> [,<属性列2>...)]

UPDATE <表名> SET <列名>=<表达式>[,<列名>=<表达式>] [WHERE <条件>];

DELETE FROM <表名> [WHERE <条件>];

数据控制
赋予权限：
GRANT <权限>[,<权限>] [ON <对象类型> <对象名>] TO <用户>[,<用户>] [WITH GRANT OPTION];

不同对象类型允许的操作权限:
对象     对象类型       操作权限
属性列   TABLE          SELECT, INSERT, UPDATE, DELETE ALL PRIVIEGES
视图       TABLE          SELECT, INSERT, UPDATE, DELETE ALL PRIVIEGES
基本表   TABLE          SELECT, INSERT, UPDATE, ALTER, INDEX, DELETE ALL PRIVIEGES
数据库   DATABASE       CREATETAB

收回权限：
REVOKE <权限>[,<权限>] [ON <对象类型> <对象名>] FROM <用户>[,<用户>];