目录
数据库原理与技术
一.关系代数
关系代数可以分为以下三种情况:
(1)集合运算(并,交,差;广义笛卡尔积)
(2)关系运算(投影,选择,连接和除运算)
(3)扩充的关系运算(广义投影,外连接,半连接,聚集等)
1.集合运算
集合运算包括并,交差,广义笛卡尔积四种运算,除了广义笛卡尔积外,其余运算中参与运算的两个关系必须是相同的同类关系,也就是说,它们必须有相同的列数,且相应的属性值取自同一个域(属性名可以不相同)
2.关系运算
1️⃣选择（Selection）
选择运算是在关系行上进行的元组挑选，结果产生同类关系。记做：
Бf（R）={t|（t∈R）∧F（t）=True}
含义：表示从关系R中选出满足条件表达式F的那些元组所构成的关系。其中，F由属性名，比较符，逻辑运算符构成。
2️⃣投影（Projection）
投影运算是在关系列上进行的选择，结果产生不同类关系，需要注意的是：投影后不仅取消了原关系中的某些列，而且还可能取消了某些元组，记做：
∏A（R）={t[A]|(t∈R)}
含义：R中取属性名表A中指定的列，消除重复元组。
以下为举例说明：
关系T：
| A1 | A2 | A3 |
|---|---|---|
| a | 3 | f |
| b | 2 | d |
| c | 2 | d |
| e | 6 | f |
| g | 8 | f |
选择关系运算结果（БA2>5∨A3≠'f'(T)）：
| A1 | A2 | A3 |
|---|---|---|
| b | 2 | d |
| c | 2 | d |
| e | 6 | f |
| g | 6 | f |
投影关系运算结果（∏A（R）={t[A]|（t∈R））**：**
| A2 | A3 |
|---|---|
| f | 3 |
| d | 2 |
| f | 6 |
3️⃣连接（Join）
连接运算也称θ连接，他是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。
在连接运算的表达式中，A和B分别为R和S上列数相等且可比的属性组。
含义：从R×S中选取R关系在A属性组上的值与S关系在B属性组上值满足θ关系的元组，构成一个新关系。
常用的连接运算有：
（1）等值连接：即θ为“=”的连接。
（2）自然连接：特殊的等值连接，要求两个关系中进行比较的分量必须是相同的属性组，并且要在结果中把重复的元素去掉
❗自然连接要求R,S有同名属性，其连接结果为满足同名属性值也对应相同，并且去掉重复属性后的连接元组
4️⃣除（Division)
二.SQL数据类型
在SQL中规定了三种数据类型:
(1)预定义数据类型
(2)构造数据类型
(3)用户定义数据类型 SQL的数据类型说明及其分类如下:
1.预定义数据类型
(1)数值型:
INT //整数类型(也可以写成INTEGER)
SMALLINT //短整数类型
REAL //浮点数类型
DOUBLE PRECISION //双精度浮点数类型
FLOAT(n) //浮点数类型,精度至少为n位数字
NUMERIC(p,d) //定点数类型,共有p位数字(不包括符号,小数点),小数点后面有d位
数字(2)字符串类型:
CHAR(n) //长度为n的定长字符串类型
VARCHAR(n) //具有最大长度为n的变长字符串类型(该类还包含其余的类型,例如:VARCHAR2,NCHAR,NVARCHAR2,NVARCHAR)
(3)位串型
BIT(n) //长度为n的二进制位串类型
BIT VARYING(n) //最大长度为n的变长二进制位串类型(4)时间型
DATE //日期类型：年-月-日（形如YYYY-MM-DD）
TIME //时间类型：时：分：秒（形如HH:MM:SS)
TIMESTAMP //时间戳类型（DATE加TIME)(5)布尔型
BOOLEAN //值为TRUE,FALSE,UNKNOWN(6)大对象
CLOB与BLOB //字符型大对象和二进制大对象数据类型值为大型文件是,视频,音频
等多媒体数据2.构造数据类型
由特定的保留字和预定义数据类型构造而成,如用"ARRAY"定于的聚合类型,用"ROW"定义的行类型,用"REF"定义的引用类型等
3.自定义数据类型
是一个对象类型,是由用户按照一定的规则用预定义数据类型组合定义的自己专用的数据类型
三.SQL的数据定义
表的建立与删改
(1)表的建立:
CREATE TABLE[模式名.]<表名>(<列名><数据类型>[列级完整性约束])
[,<列名><数据类型>[列级完整性约束]]....[,<表级完整性约束>])
例如:
CREATE TABLE student (...... ......);
(2)表完整性约束的子句
1️⃣主键子句:
PRIMARY KEY(<列名>)
例如:
VARCHAR(8) NOT NULL;
被定义为主键的列强制满足非空和唯一性条件.凡带有NOT NULL的列,表示不允许出现空值;反之,可出现空值.当首次用CREATE TABLE定义一个新表后,只是建立了一个无值的表结构.
2️⃣外键子句:
FOREIGN KEY(<列名>)REFERENCES[<表名>][<约束选项>]
3️⃣检验子句:
CHECK(<约束条件>)
CHECK子句规定了一个规则:一个表的指定列只能取这个规则定义的集合之内的值.默认值用默认子句表达:DEFAULT<默认值>.
(3)表的删除
DROP TABLE<表名>[CASCADE|RESTRICT]
当选用任选项CASCADE时,删除表时,表中的数据,表本身以及在该表上所建的索引和视图将全部消失;当选用任选项RESTRICT时,只有在先清除了表中的全部记录行数据以及在该表上所建的索引和视图后,才能删除一个空表,否则拒绝删表.
例如:
DROP TABLE student CASCADE;
(4)表的扩充和修改
!--相关的操作有:增加新列,修改原有的列定义或增加新的,删除已有的完整性约束条件等--
1️⃣表中增加新列:
ALTER TABLE<表名>
ADD(<列名><数据类型>,....);
例如:
ALTER TABLE S ADD SID CHAR(18);
2️⃣删除列:
ALTER TABLE<表名>
DROP <列名>[CASCADE|RESTRICT];
例如:
ALTER TABLE student DROP subject;
3️⃣修改列类型:
ALTER TABLE<表名>
MODIFY<列名><类型>;
注意:新增加的列一律为空值;修改原有的列定义可能会破坏已有数据
例如:
ALTER TABLE student MODTFY subject VARCHAR(8);
(5)模式与域类型的定义
模式是由模式名或者模式拥有者的用户名来确定,并且包含模式中的每个元素的定义.定义一个模式,就是定义一个存储空间.
语句如下:
CREATE SCHEMA<模式名>AUTHORIZATION<用户名>
[<CREATE DOMAIN子句>|<CREATE TABLE 子句>|<CAEATE VIEW>|... ...]
❗❗创建模式需要拥有DAB权限或获得DAB授予建模式的权限;默认的方括号内是在该模式中要创建的域,表和视图等子句,模式中的表,视图等也可以在以后随时创建;若不指定<模式名>,则隐含为<用户名>
SQL可以自定义属性值的范围,为其定义域名,并在不同的表定义中使用该域名,这就是用户自定义的域类型.
域类型是用于用户自定义属性的一个特定数据类型,它由带有约束的数据类型和默认值一起构成.
定义格式如下:
CREATE DOMAIN<域名>[AS]<数据类型>
[DEFAULT<默认值>][<域约束>]
(6)索引的建立与删除
1️⃣建立索引的目的旨在提供多种存取路径,加快查找速度.在创建表的时候直接定义主键,一般系统会自动在主键上建立索引.
格式如下:
CREATE[UNIQUE][CLUSTER] INDEX<索引名>
ON<表名>(<列名1>[ASC|CESC],<列名2>[ASC|DESC],....)
ASC表示升序(系统一般默认升序),DESC表示降序
一个基本表最多只能建立一个聚簇索引
❗❗UNIQUE表示唯一索引,即此索引的每一个索引值只对应唯一的数据记录;CLUSTEER表示聚簇索引,即索引项的顺序与表中记录的物理顺序一致的有序索引;
例如:
CREATE UNIQUE INDEX sindex ON student(sno); CREATE UNIQUE INDEX cindex ON course(sno ASC,cno DESC)
2️⃣删除索引:
格式如下:
DROP INDEX [ON<表名>]<索引名>
若要删除按照学号所建立的索引,则使用语句:
DROP IENDEX sindex;
删除索引的同时,系统会从数据字典中删去有关该索引的描述
三.SQL的数据查询
1.单表查询
(1)基本结构如下:
SELECT<列名表A>
FROM<表或视图名集合R>
WHERE<元组满足的条件F>
一般格式如下,该格式查询能力更强大:
SELECT[ALL|DLSTINCT]<目标列表达式>[,<目标列表达式>]...
FROM<表名或视图名>[,<表名或视图名>]...
[WHERE<条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY<列名2>[ASC|DASC]];
2.查询中的语句解释
(1)通过SELECT子句的<目标列表达式>中指定要查询的列来实现对表中的一部分属性列进行查询的效果
(2)通过带有WHERE子句的SELECT语句来实现查询表中的行
1️⃣比较和逻辑运算:WHERE之后的查询条件中允许出现比较运算符=,>,>=,<,<=,<>(不等于)和逻辑运算符AND,NOT,OR等
2️⃣谓词BERWEEN:用于判断某值是否属于一个指定的区域
3️⃣谓词IN:用来查找列值属于指定集合的元组,其否定是NOT IN
4️⃣字符匹配(LIKE):用来进行字符串的匹配,其一般格式如下:
[NOT]LIKE'<匹配串>'[ESCAPE'<换码字符>',]
其含义是查找指定的属性列值与<匹配串>相匹配的元组,匹配串中可以是完整的字符串,也可以含有通配符%和_.
5️⃣通配符:通配符一般分为两种,分别是%和_,二者的区别在于,%代表任意长度(长度为可以为0)的字符串,_代表了任意的单个字符.
注意:一个汉字占两个字符串,所以表示一个汉字需要用到两个下划线_
6️⃣ORDER BY子句:该子句后可以跟多个排序的变量名,第一个变量为主序,下面依次类推.每个排序列名后面可用限定词ASC或DESC声明排序的方式.
3.函数与表达式
(1)聚集函数:
COUNT([DISTINCT|ALL]*) //统计元组个数 COUNT([DISTINCT|ALL]<列名,) //统计一列中值的个数 SUM([DISTINCT|ALL]<列名>) //计算一数值型列值的总和 AVG([DISTINCT|ALL]<列名>) //计算一数值型列值的平均值 MAX([DISTINCT|ALL]<列名>) //求一列值中的最大值 MIN([DISTINCT|ALL]<列名>) //求一列值中的最小值
(2)算术表达式:
查询目标列中允许使用算术表达式.算术表达式由算术运算符+,-,*,/与列名或数值常量及函数组成.
常见的算术函数由:
INTEGER(取整),SQRT(求平方根),三角函数(SIN,COS),字符串函数SUBSRING(取子串),UPPER(大写字符),日期型函数MONTHS_BETWEEN(月份差)等
(3)分组与组筛选:
目的旨在细化聚集函数的作用对象
一般格式:
<SELECT 查询块>
GROUP BY <列名>
HAVING<条件>
❗❗注意:GROUPE BY子句总是跟在WHERE子句之后,若没有WHERE子句,则跟在FROM子句之后;HAVINGE短语常用于对查询结果进行控制,对于满足条件的小组进行输出,即进行筛选
4.多表查询
(1)嵌套查询
嵌套查询效率低,连接查询效率高
嵌套查询的一般求解方法是先求解子查询,其结果用于建立父查询的查找条件.
例子:
SELECT sname
FROM student
WHERE age>
(SELECT AVG(age)
FROM student);
嵌套查询中,谓词ALL,SOME的使用格式如下:
<标量表达式><比较运算符>ALL|SOME(<表子查询>)
其中:X>SOME,表示X大于子查询结果中的某个值;X<ALL,表示X小于子查询结果中的全部值.=SOME等价于IN,<>ALL等价于NOT IN
(2)条件连接查询
通过连接使查询的数据从多个表中取得.查询中用来连接两个表的条件称为连接条件,一般格式如下:
[<表名1>.]<列名1><比较运算符>[<表名2>.]<列名2>
表的连接除=外,还可以用比较运算符以及BETWEEN,LIKE,IN等谓词.当连接运算符是=时,可以称为等值连接.
两者之间的区别和联系如下: 1、自然连接一定是等值连接，但等值连接不一定是自然连接。 2、等值连接要求相等的分量，不一定是公共属性；而自然连接要求相等的分量必须是公共属性。 3、等值连接不把重复的属性除去；而自然连接要把重复的属性除去。
[等值连接与自然连接区别] https://blog.csdn.net/jiakw_1981/article/details/3050917
例子:
SELECT sno,grade FROM s_c,course WHERE s_c.cno = couse.sno AND cname = '数据库原理';
当一个表与其子句进行连接的时候,这种连接称为自身连接,涉及到的查询称为自身连接查询.
(3)相关子查询
当子查询的判断条件涉及外层父查询的属性时,称为相关子查询.相关子查询要用到存在谓词EXISTS和NOT EXISTS,或者ALL,SOME等.
例子:
SELECT sname,sno
FROM srudent
WHERE EXISTS
(SELECT *
FROM s_c
WHERE s_c.sno =student.sno AND cno = '005');
由EXISTS引出的子查询,其目标列表达式通常都有"*",因为带EXISTS的子查询只返回真值或假值,给出列名无实际意义.
❗❗所有带IN的谓词,比较运算符,SOME和ALL谓词的子查询都能用带EXISTS谓词的子查询等价交换
❗❗任何一个带全称量词的谓语可以转换为等价的带存在量词的谓语
(4)集合运算
SQL提供了集合运算谓词:UNION,INTERSECT,EXCEPT
例子:
(SELECT * FROM st1) UNION (SELECT * FROM st2);
UNION表示全部,EXCEPT表示除某一部分外.
5.连接查询
(1)自然连接
SQL的自然连接与关系代数中的自然连接功能是一样的,使用JOIN完成操作
例子:
SELECT * FROM student NATURAL JOIN s_c WHERE dept = '计算机学院'AND grade>=90;
四.SQL的数据更新
1.插入数据
(1)用子句向表中插入数据:
格式如下:
INSERT INTO <表名>[(列名1)[,<列名2>]... ...]
VALUES(<常量1>[,<常量2>]... ...)
例子:
INSERT INTO S
VALUES('SID','ZHANG','IS',18);
(2)用子查询向表中插入数据
格式如下:
INSERT INTO <表名>[<列名1>[,<列名2>]... ...]
<查询语句>;
其功能是以批量插入的方式,一次将查询的结果全部插入到指定表中
例子:
INSERT INTO st1_grade(gno,name,avg) SELECT student.sno,sanme,AVG(grade)avg_good FROM student,s_c WHERE student.sno=s_c.sno GROUP BY sno HAVING avg_good>=90;
2.更改数据
(1)用子句修改表中数据
格式如下:
UPDATE<表名>
SET<列名>=<表达式>[,<列名>=<表达式>]
[WHERE<条件表达式>]
其功能是修改指定表中满足WHERE条件的元组.SET子句用来指定修改值,即用表达式的值来取代相应的属性列值.如果忽略WHERE子句则表示修改表中所有元组.
例子:
UPDATE student SET age=sge+1 WHERE dept='计算机学院';
(2)用子查询修改表中数据
例子:
UPDATE student
SET grade=0
WHERE cno='001'AND sno=
(SELECT sno
FROM student
WHERE sname='左键');
3.删除数据
格式如下:
DELETE
FROM <表名>
[WHERE<条件表达式>]
如果忽略WHERE子句，就会删除表中的全部元组.
例子:
DELETE FROM student WHERE sno='1234556';
五.连接查询
连接查询主要分为三个类:内连接,外连接与自然连接.
[内连接,外连接与自然连接的区别] https://blog.csdn.net/weter_drop/article/details/84729822
1.自然连接(Natural Join)
SQL的自然连接与关系代数中的自然连接功能是一样的.使用NATURAL JOIN进行自然连接
自然连接是一种特殊的等值连接,他要求两个关系表中进行连接的必须是相同的属性列(即名字相同),无需添加连接条件,并且在结果中消除重复的属性列.
格式如下:
Select * from table1 natural join table2
例子:
SELECT * FROM student NATURAL JOINN s_c WHERE dept='计算机学院'AND grade>=90;
2.内连接(Inner Join,inner可省略)
内连接与自然连接相同,不同之处在于自然连接的是同名属性的连接,而内连接不要求两属性列同名,可以用using或on来指定某两列字段相同的连接条件.
3.外连接(Outer Join)
外连接的作用是在做连接操作时避免丢失信息.
左外:内连接+join左边不符合连接条件的数据
右外:内连接+join右边不符合连接条件的数据
外连接有以下三类:
(1)左外连接(LEFT [OUTER] JOIN),其结果表中保留左关系的所有元组,
格式如下:
SELECT * from table1 LEFT OUTER JOIN table2 on table1.c=table2.c
(2)右外连接(RIGHT [OUTER] JOIN),其结果表中保留右关系的所有元组.
格式如下:
SELECT * from table1 RIGHT OUTER JOIN table2 on table1.c=table2.c
(3)全外连接(FULL [OUTER] JOIN),其结果表中保留左右关系的所有元组.
格式如下:
SELECT *from table1 FULL OUTER JOIN table2 on table1.c=table2.c
例子:
SELECT * FROM teacher FULL OUTER JOIN post//全外连接 on teacher.name=post.name;
7112
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
