目录
使用数据库
数据模型
数据模型是指描述数据,数据间的关系,对数据的约束的有关概念。数据模型包括数据结构,数据完整性约束,数据运算三个部分。其目的是提供一个框架,实现数据操作简单容易,做到数据正确、完整、安全。
关系(Relation)数据模型。(关系(Relation)是来自数学的概念,通俗的说法,就是表)
- 数据结构:二维表
- 数据完整性约束:实体,引用,域,业务规则(行业情况)
- 数据运算:关系代数
关系是一组域的笛卡尔积子集
关系特性
- 在一个数据库中,表的名字要唯一,不能出现同名的表
- 在一个表中,列的名字要唯一,不能出现同名的列
- 每行数据表达一个实例,一个实例在表中只有一行数据
- 表中的列具有对等性,没有等级区别
- 表中的行具有对等性,没有先后概念
注意:
- 一个数据在数据库中只存一份
- 不同的列可来自同一域,每列必须有不同的属性名
- 行列的顺序无关紧要
各种键
超键
超键(Super Key)关系中的一个属性组(含有1个或多个列的组),其值能唯一标识一个元组(行)。这样的属性组称作该关系的超键。
候选键
候选键(Candidate Key)满足特殊条件的超键:如果一个超键删除任何一个属性后都不再是超键,则该键为该关系的候选键。即子集都不是超键的超键为候选键。
(非)主属性
任何一个候选键中的属性称作主属性(Prime Attribute),不属于任何一个候选键中的属性称作非主属性。
主键
主键(Primary Key)进行数据库设计时,从一个关系中的多个候选键中选定一个作为主键,除主键外的其它候选键称为该关系的唯一键(Unique Key)或替代键(Alternate Key)。
外键
外键(Foreign Key)关系中的一组属性,这组属性在其它表中是主键(即明确指定的某个候选键)。
数据完整性约束
- 实体完整性约束:每个表有主键,并且作为主键的一个或多个列对应的取值唯一(不重复),同时主键中任一列对应的取值不能是NULL; 关系模型的实体完整性约束是通过主键Primary key来定义; 现实意义中,主键是标识实体的,不能为空是基本要求。
- 参照完整性约束:任何外键的值,要么是NULL,要么是存在于另一张表中主键的值。确保不会引用一个不存在的值。关系模型的参照完整性约束是通过外键Foreign key来定义。
- 域完整性约束:每一列要有明确的数据类型、数据格式和取值范围、以及是否可以为NULL。
- 用户自定义完整性约束:针对某一具体应用的数据必须满足的语义要求。
关系代数
由操作数(operands)和操作符(operators)组成
输入:一个或多个关系(输入为一个关系是一元代数,输入为多个关系是多元代数)
输出:一个关系
关系的并
关系R和关系S的所有元组合并,再删除重复的元组,组成的新关系记为,称为R和S的并
关系的差
关系R和关系S的差是属于R不属于S的元组组成的集合,记为
关系的交
关系R和关系S的交是由既属于R又属于S的元组组成的集合,记为,也可表达为
笛卡尔积
一组域D1 , D2 ,…, Dn的笛卡尔积为:
笛卡尔积的每个元素称作一个n元组
例:
A | B |
---|---|
1 | 2 |
3 | 4 |
5 | 6 |
A | B |
---|---|
1 | 2 |
7 | 8 |
9 | 10 |
A | B |
---|---|
1 | 2 |
3 | 4 |
5 | 6 |
7 | 8 |
9 | 10 |
A | B |
---|---|
1 | 2 |
A | B |
---|---|
3 | 4 |
5 | 6 |
R.A | R.B | S.A | S.B |
---|---|---|---|
1 | 2 | 1 | 2 |
1 | 2 | 7 | 8 |
1 | 2 | 9 | 10 |
3 | 4 | 1 | 2 |
3 | 4 | 7 | 8 |
3 | 4 | 9 | 10 |
5 | 6 | 1 | 2 |
5 | 6 | 7 | 8 |
5 | 6 | 9 | 10 |
选择
从关系中找出满足给定条件的所有元组称为选择,以逻辑表达式给出条件,为真的元组被选取,运算符记为,R为一个关系,F为布尔函数
投影
从关系中挑选若干属性组成新的关系称为投影,运算符记为,R为一个关系,为一组属性名或属性序号组,如果新关系中包含重复元素,则要删除重复元组
连接
将两个关系的属性名拼接成一个更宽的关系,生成的新关系中包含满足条件的元组
自然连接
除去重复属性的等值连接,记为,R、S为两个关系,并且具有一个或多个同名属性。计算过程如下:
- 计算
- 挑选中对应公共属性的值相同的元组
- 去掉上述元组中或的部分
关系的除
设有关系与关系,其中为属性集合,若和具有相同的属性个数,且对应的属性来自相同域。除以即为在上投影的一个子集,该子集和的笛卡儿积必须包含在中,记为
例:
A | B | C | D |
---|---|---|---|
a | b | c | d |
a | b | e | f |
a | c | c | d |
C | D |
---|---|
c | d |
e | f |
A | B | C | D |
---|---|---|---|
a | b | c | d |
a | b | e | f |
A | C |
---|---|
a | c |
a | e |
A | B | C | D |
---|---|---|---|
a | b | c | d |
a | b | e | f |
a | c | c | d |
A | B |
---|---|
a | b |
SQL语言
Structured Query Language(SQL) 是访问关系型数据库的国际标准语言。
SQL分为以下四类:
- 数据定义语言Data Definition Language (DDL),建立数据库对象
- 数据操作语言Data Manipulation Language (DML),实现添加、修改和删除操作
- 数据查询语言Data Query Language (DQL),实现查询操作
- 数据控制语言Data Control Language (DCL),数据库对象的权限管理和事务管理
创建表:
CREATE TABLE 表名
( 列名 数据类型,
…
完整性约束,
…
)
基本数据类型
- INT:整数
- FLOAT(n):浮点数,精度至少为 n 位数字
- CHAR(n):长度为 n 的定长字符串
- DATETIME:日期时间型
SQL日期有关的函数
getdate( ):返回系统的当前日期
datepart( datepart, date ):返回date中的datepart部分
例:datepart( dd, '6/1/2024') = 1 datepart( yy, getdate( )) = 2024
day( date ),month( date ),year( date ):分别返回date中的“日”,“月”,“年”日期部分
datediff( datepart, startdate, endate ):按照datepart返回两个日期startdate和endate之差
例:datediff (mm, ‘6/1/2024’, getdate ())返回2024年6月1日和当前日期之间相隔的月数
dateadd(datepart, number, date):将number加到date的datepart部分上
例:dateadd(dd, 20, '6/1/2024') = '6/21/2024'
完整性约束
- 主键约束
- 外键约束
- 域约束
- 业务规则约束
约束类型
- primary key
- foreign key
- unique
- check
- default
primary key 与 unique 通过唯一性索引支持
foreign key 作为主键的关系称为基本关系,作为外键的关系称为依赖关系。
删除基本关系元组
-
RESTRICT方式,只有当依赖关系中没有一个外码值与要删除的基本关系的主码值相对应时,才可以删除该元组,否则系统拒绝此删除操作
-
CASCADE方式,将依赖关系中所有外码值与基本关系中要删除的主码值所对应的元组一起删除
-
SET NULL方式,删除基本关系中元组时,将依赖关系中与基本关系中被删主码值相对应的外码值置为空值
check全局约束,涉及多个属性间的或多个关系间的联系
插入操作
#插入元组值
INSERT INTO 表名(列名序列) VALUES (元组值)
#插入查询值
INSERT INTO 表名(列名序列) SELECT 查询语句
修改操作
#修改表
ALTER TABLE 表名
ADD 列名 数据类型 #增加一个列
DROP COLUMN 列名 #删除一个列
MODIFY 子句 #修改列定义
#修改语句
UPDATE 表名
SET 列名 = 值表达式 …
WHERE 条件表达式
删除操作
#删除表
DROP TABLE 表名
#删除语句
DELETE FROM 表名 子句
查询操作
SELECT 列名
FROM 表名
WHERE 行条件表达式
GROUP BY 列名
HAVING 组条件表达式
ORDER BY 列名 ASC | DESC
如果要去掉重复的行,可在列名前加上"DISTINCT"
如果要改变查询结果显示的列名,可在列名后加上"AS 新列名"
函数名 | 功能 |
---|---|
AVG | 计算一个数值型表达式的平均值 |
COUNT | 计算表达式中选择的项数 |
MIN | 计算表达式中的最小值 |
MAX | 计算表达式中的最大值 |
SUM | 计算表达式中的数值和 |
子查询
IN
表达式 [NOT] IN [子查询]
判断表达式的值是否在子查询结果中。
EXISTS
一般用在WHERE子句中,其后是一个SELECT子查询,EXISTS代表存在量词,返回为逻辑值。子查询结果不为空是返回真,否则为假。
关系的连接
连接类型 | 连接条件 |
---|---|
INNER JOIN | ON |
LEFT OUTER JOIN | ON |
RIGHT OUTER JOIN | ON |
FULL OUTER JOIN | ON |
索引
创建索引
CREATE [索引类型] INDEX 索引名 ON {表名 | 视图名} {列名 [ASC | DESC]}
删除索引
DROP INDEX 表名.索引名
作用
- 减少无效运输,提高查询速度
- 防止数据的分散性和读取的低有用率
触发器
格式
CREATE TRIGGER 触发器名
BEFORE INSERT ON 表名
REFERENCING NEW ROW AS new
FOR EACH ROW
BEGIN
…
raise_application_error(…);
END;
触发器实现业务规则约束
- BEFORE触发器在语句开始做任何增删改之前被触发
- AFTER触发器则在语句做完所有增删改之后被触发,触发的语句晚于监视的增删改,无法影响前面的增删改动作
- INSTEAD OF触发器只能被定义在视图上,因为视图本身不能被直接修改,而是需要使用"Instead of"触发器来将修改转换为修改底层表
视图
创建和删除视图
#创建视图
CREATE VIEW 视图名 (列名) AS SELECT 查询语句
#删除视图
DROP VIEW 视图名
作用
- 不同用户可从不同角度观察同一数据
- 简化用户操作
- 限制用户数据的访问范围
- 作为基本表与外模式之间的映象提供了逻辑独立性
存储过程
CREATE PROCEDURE 存储过程名(@变量名1 IN 数据类型, @变量名2 IN 数据类型, @变量名3 IN 数据类型, …)AS
BEGIN
…
END;
#用户调用语句
CALL 存储过程名(变量1,变量2,变量3,…)
应用程序
JDBC
ODBC
安全威胁
- SQL注入(参数化SQL语句/函数)
- HTML注入(去除标签函数)
- 抵赖(加密)
- 非法数据访问(审计)