数据库 (二)

关系数据库

1.关系数据结构以及形式化定义

1.1.关系

  • 域:一组具有相同数据类型的值的集合
  • 笛卡尔积:域的一种集合运算(相乘)
  • 候选码:某一属性组的值能唯一的标识一个元组(其子集不同)
  • 主码:选一个候选码
  • 主属性:候选码中的每一个属性
  • 全码:关系模式的所有属性是这个关系模式的候选码(无非主属性)

1.2.关系模式

  • R(U,D,DOM,F)
  • R:关系名,U:组成该关系的属性名集合,DOM:属性向域的影响集合,F:函数依赖

2.关系操作

  • 基本操作:选择、投影、并、差、笛卡尔积

3.关系的完整性

3.1.实体完整性

  • 以主码为唯一标识符
  • 主码属性不为空

3.2.参照完整性

  • 外码与主码间的引用规则

3.3.用户定义的完整性

4.关系代数

  • 选择 投影Π 连接 除运算
  • 自然连接:会去掉重复的属性列,特殊的等值连接

关系数据库标准语言SQL

1.SQL特点

  • 综合统一
  • 高度非过程化
  • 面向集合的操作方式
  • 一种语法结构提供多种使用法师
  • 支持数据库三级模式结构(通过视图模式实现)

2.数据定义

操作对象 创建 删除 修改
模式 create schema drop schema
create table drop table alter table
视图 create view drop view
索引 create index drop index alter index

2.1.模式定义与删除

create schema <模式名>authorization<用户名>[<表定义子句>|<试图定义子句>|<授权定义子句>]
  • 不指定模式名的话,隐含为用户名
drop schema <模式名><cascade|restrict>
  • cascade:级联 restict:限制

2.2.表的各种操作

2.2.1.创建基本表

create table<表名>(<列名><数据类型>[列级完整性约束条件]
                        ······
                [,<表级完整性约束条件>])

2.2.2.修改基本表

Alter Table <表名>                                    //列级操作
[add [column]<新列名><数据类型>[完整性约束]]            //增
[add <表级完整性约束>]                                 //增
[drop [column]<列名>[cascade|restrict] ]              //删
[drop constraint<完整性约束名>[cascade|restrict]]      //删
[alter column<列名><数据类型>]                         //改

2.2.3.删除基本表

drop table <表名>

2.3.索引操作

2.3.1.建立索引

create [unique][cluster] index<索引名>
on<表名>(<列名>[<次序>],······)

2.3.2.修改 删除索引

alter index<旧索引表> rename to <新索引明>
drop index <索引名>

3.数据类型(Mysql)

3.1.Text 类型:

CHAR(size) 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size) 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。 注释:如果值的长度大于 255,则被转换为 TEXT 类型。
TINYTEXT 存放最大长度为 255 个字符的字符串。
TEXT 存放最大长度为 65,535 个字符的字符串。
BLOB 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT 存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.) 允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值,则插入空值。 注释:这些值是按照你输入的顺序存储的。 可以按照此格式输入可能的值:ENUM(‘X’,’Y’,’Z’)
SET 与 ENUM 类似,SET 最多只能包含 64 个列表项,不过 SET 可存储一个以上的值。

3.2.Number 类型:

数据类型 描述
TINYINT(size) -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size) -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size) -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d) 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型,允许固定的小数点。

* 这些整数类型拥有额外的选项 UNSIGNED。通常,整数可以是负数或正数。如果添加 UNSIGNED 属性,那么范围将从 0 开始,而不是某个负数。

3.3.Date 类型:

数据类型 描述
DATE() 日期。格式:YYYY-MM-DD 注释:支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME() *日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIMESTAMP() *时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式:YYYY-MM-DD HH:MM:SS 注释:支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME() 时间。格式:HH:MM:SS 注释:支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR() 2 位或 4 位格式的年。 注释:4 位格式所允许的值:1901 到 2155。2 位格式所允许的值:70 到 69,表示从 1970 到 2069。

4.数据查询

select [all | distinct] <目标列表达式>···
from <表名或视图>,··· as <别名>
where <条件表达式>
group by <列名1>[having<表达式> ]
order by <列明2>[desc|asc]
where条件 谓词
确定范围 between and,not between and
确定集合 in,not in
字符匹配 like,not like %aa%注意百分号的使用 ‘_’表示数量
空值 is null,is not null

4.1.聚集函数

  • 只能作用于select子句和group by 中的having子句
  • 聚集函数遇到空值时,除了count(*)外,都跳过nulls
count(*)                        //统计行数
count([distinct|all] <列名>)    
sum([distinct|all] <列名>)       //值求和
avg([distinct|all] <列名>)       //值求平均
max([distinct|all] <列名>)       //最大
min([distinct|all] <列名>)       //最小

4.2.连接查询

  • where连接

4.2.1.自身连接

  • 要为自身连接的表取两个别名
select a.cno,b.cno
from course a,course b
where a.cpno=b.cno
  • 外连接
    • 左外保留左边的所有,右置null
select *
from a left outer join b on(a.a=b.a)
  • 多表连接

4.3.嵌套查询

  • 查询块:select-from-where
  • 将查询块作为一个查询中的where中,为嵌套查询
  • 注意 IN(多值) 和 = 的使用
  • 子查询中不可用 order by,必须放在最终查询中
select * 
from a 
where a IN (
    select a
    from b
)
  • 有些嵌套查询可以用连接运算代替(不相关子查询)
  • 相关子查询/不相关子查询,取决于子查询是否受父查询影响
  • 子查询结果为单值时,可用 > any <any !=any IN ;

4.3.1.带有EXISTS谓词的子查询

  • exists 的子查询不返回数据,返回true,false
  • 存在 not exists的用法
select *
from a 
where EXISTS (
    select *
    from b
)

5.数据更新

5.1.插入数据

5.1.1.插入元组

insert into <tableName> [<属性列1>,<属性列2>,···]
values (<常量1>,<常量2>,···)
  • 如果只给出表名,不给出属性列,意味着新元组要在所有属性列上赋值,并且次序一一对应

5.2.2.插入子查询结果

insert into <tableName> [<属性列1>,<属性列2>,···]
子查询
  • 属性列和子查询中 【select 属性列1,属性列2】要一一对应

5.2.修改数据

update <tableName>
set <列名> = <表达式>,···
[where <条件>]
  • 如果不指定 where 则表示要修改表中的所有元组

5.3.删除数据

delete from <tablename>
[where <条件>]
  • 省略where则表示删除表中所有元素,但保留表
  • delete 是对表内部的操作,不对表级单位操作

5.4.空值的判断

  • is null 和is not null 来判断
  • =null 来赋值空值

6.视图

  • 视图是从一个表或多个表切分出来的表,是一张虚表
  • 数据库中只存放视图的定义,不存放视图的数据,数据还是在原表中。
  • 可以理解为 观察角度 的不同

6.1.定义视图

create view <视图名>[<列名>,···]
as <子查询>
[with check option]  

create view stu1
as
select sno,sname
from stu
where sedpt='is'
with check option    //加入此句,表示在插入修改删除时,会自动加上sdept='is'的条件
  • 视图名后面的列名,要么全部写出,要么全部忽略,默认select中的属性。不存在列出部分列名情况
  • 行列子集视图:如果一个视图是从一个基本表中导出的,并去掉了某些行列,但保留了主码。
  • 分组视图:带有聚集函数和group by子句的子句的查询来定义视图

6.2.删除视图

Drop view <视图名> [cascade]
  • cascade表示删除由该视图衍生出的子视图
  • 删除基本表后,由该产生的视图无法使用,但需要调用该语句,才能删除视图定义

6.3.更新/查询视图

  • 视图消解:从数据字典取出视图定义,处理视图,转换成对基本表的查询,再执行修正了的查询
  • 因为视图是不存储数据的虚表,所以对视图的各种操作,都要转为对基本表的操作,即视图消解

6.4.视图的作用

  • 简化用户操作
  • 使用户多角度看待同一数据
  • 为重构数据库提供了一定程度的逻辑独立性
  • 可以提供安全保护
  • 可以更清楚的表达查询

Leave a Comment