一、基本概念
记录 -- 行
字段 -- 列
主键： 唯一
外键
完整性
- 数据完整性
- 实体完整性
表中每一记录是唯一实体 - 域完整性
- 表中列须满足某种特定数据类型的约束
- 取值范围
- 精度
- 表中列须满足某种特定数据类型的约束
- 参照完整性
- 两表的主键和外键数据应一致
- 用户定义的完整性
- 实体完整性
- 完整性约束类型
- 与表有关的约束
- 列约束
- not null
- 表约束
- PRIMARY KEY
- UNIQUE
- foreign key
- CHECK约束
- 特定域（数据类型）的约束
- 列约束
- 断言
- 与表有关的约束
数据库类型
- 层次...
- 网状...
- 关系...
二、关系运算
集合运算
- 并
- 定义
抽取两张表中所有行的运算须去重
- 符号：U
- 定义
- 交
- 定义
抽取既在表1，又在表2中的行
- 定义
- 差
- 定义
抽取其中一张表中独有行的运算
- 符号： -
- 定义
- 笛卡尔积
- 定义
将两张表的所有行进行排列组合
- 符号：X
- 定义
关系运算
- 投影
- 定义
抽取列的运算
- 定义
- 选择
- 定义
抽取行的运算
- 定义
- 连接
- 定义
如表中某字段是其他表中的主键通过连接运算，可以将两张表连起来
- 定义
- 除
- 定义
从“被除表格”中调取“除表格”中包含的所有行去掉“除表格”中所有列的运算
- 总结
笛卡尔积的逆运算
-
实例
- 定义
三、E-R模型 & 规范化
E-R模型关系
表格规范化
- 类别
- 非范式
没有除去数据重复的表 - 第一范式
- 定义
- 表的每一列均是不可分割的基本数据项
- 同一列中不能有多个值
- 反例
- 表里的电话字段，既有手机号，又有座机号
- 总结
- 原子性约束
- 字段不能再分
- 定义
- 第二范式
- 定义
- 满足第一范式
- 表中每一行可以被唯一的区分
- 其他列完全依赖于主键
- 总结
- 唯一性约束
- 每一行记录都可以通过主键被唯一标识
- 定义
- 第三范式
- 定义
- 满足第二范式
- 不能存在其他数据表中的非主键字段
- 主键以外的字段依赖主键，且不能依赖表中其他字段
- 每一列数据和主键直接相关，而非间接相关
- 总结
- 冗余性约束
- 直接依赖，而非传递依赖
- 定义
- BCNF
- 第四范式
- 非范式
- 设计目标
- 规范化数据库：将冗余降到最低
- 需执行开销很大的连接操作
- 非规范化数据库：优化读取时间
- 通常用于构建高可扩展性系统
- 规范化数据库：将冗余降到最低
四、SQL基本操作
select
- like模糊搜索
- %： 表示任意字符串
- _： 表示一个字符
- 检索条件
- where
- between ... and ...
- is null
- select * from product where price is null
- order by
- 实例
- select * from table
- select * from product where name like '%果' or name like '% 莓' order by price
- 计算函数
- count()
- count(*)
求行数 - count(列名)
求非空值行数 - count(distinct 列名)
求排除空值及重复行的行数 - select count as ... from ...
- count(*)
- avg()
- sum()
- select sum(field) as ... from ...
- min()
- max()
- count()
- 分组：group by
- select place, avg(price) from product group by 地域 having avg（price） >= 200
- 子查询检索
- select * from where ... in（select * from ... where ...）
- 连接
-
内部连接
- 选择数值相同的行进行连接的连接
- 与一般的where语句等价
- 类别
- 相等连接
- 自然连接
- 两表中相同列只出现一次
- 交叉连接
- 即笛卡尔积连接
-
外部连接
- 定义
- 保留其中一表格的所有行
- 将另一方中没有的行设置为空值
- 类别
- 左外连接
- 左表为主表，右边表为副表
- 右外连接
- 右表为主表，左表为副表
- 全连接
- 左右外连接的并集
- 左外连接
- 定义
实例
-
相关参考
- 连接--》维基
-
create
- 实例
create table product
（
id number(3, 0)，
name char(20),
price number(10, 0),
primary key(id)
）
create database DBName
insert
- 实例
insert into product (id, name, price) values(101, '香瓜'， 800)
update
- 实例
update product set name = "甜瓜" where name = “香瓜”
delete
- 实例
delete from product where name = "苹果"
drop
- 实例
drop database DBName
drop table TableName
高级检索
- 复制表
- 既复制表结构，也复制内容
- select * into B from A
- 只复制表结构
- select * into B from A where 1 = 2
- select top 0 * into B from A
- 只复制内容
- insert into B select * from A
- 既复制表结构，也复制内容
- 分页查询
- 倒序top
select top 3 userid from ( select top 7 userid from ... order by userid) order by userid desc - 排除top
- 倒序top
select top 5 * from UserInfo where UserId not in
(select top (n-1)*5 UserID from UserInfo order by UserID asc) order by UserID asc
SELECT TOP 页大小 * FROM table WHERE 主键 NOT IN
(
SELECT TOP (页码-1)*页大小 主键 FROM table WHERE 查询条件 ORDER BY 排序条件
)
- ORDER BY 排序条件
- not in
- 优点：通用性强。
- 缺点：当数据量较大时向后翻页，NOT IN中的数据过大会影响性能。
- 适用：数据量不大
- 直接限制返回区间
- SELECT * FROM table WHERE 查询条件 ORDER BY 排序条件 LIMIT ((页码-1)*页大小),页大小;
- 优劣
- 优点：写法简单。
- 缺点：当页码和页大小过大时，性能明显下降。
- 适用：数据量不大
- MAX
- not in
SELECT TOP 页大小 * FROM table WHERE 查询条件 AND id >
(
SELECT ISNULL(MAX(id),0) FROM
(
SELECT TOP ((页码-1)*页大小) id FROM table WHERE 查询条件 ORDER BY id
) AS tempTable
)
- ORDER BY id
-
优劣
- 优点：速度快，特别是当id为主键时。
- 缺点：适用面窄，要求排序条件单一且可比较。
- 适用：简单排序（特殊情况也可尝试转换成类似可比较值处理）
ROW_NUMBER()
-
SELECT TOP 页大小 * FROM
(
SELECT TOP (页码*页大小) ROW_NUMBER() OVER (ORDER BY 排序条件) AS RowNum, * FROM table WHERE 查询条件
) AS tempTable
WHERE RowNum BETWEEN (页码-1)*页大小+1 AND 页码*页大小
-
ORDER BY RowNum
- 优劣
- 优点：在数据量较大时相比NOT IN有优势。
- 缺点：小数据量时不如NOT IN。
- 适用：大部分分页查询需求。
- 优劣
-
查询表中某列最小数
- select top 1 col from A order by col
- select * from A where col = ( select min(col) from A )
建立临时表
create table #Tmp(
...
)
- 操作表中重复记录
查找表中重复记录
- 单个字段
select * from people where peopleId in
(
select peopleId from people
group by peopleId having count(peopleId) > 1
)
- 多个字段
select * from vitae a where (a.peopleId,a.seq) in
(
select peopleId,seq from vitae
group by peopleId,seq having count(*) > 1
)
- 完全重复
- select distinct * from tableName
删除表中重复记录
- 单个字段
delete from people where peopleId in
(
select peopleId from people
group by peopleId having count(peopleId) > 1
)
delete from people where peopleId in
(
select peopleId from people
group by peopleId having count(peopleId) > 1
) and rowid not in
(
select min(rowid) from people group by peopleId having count(peopleId )>1
)
- 多个字段
delete from vitae a where (a.peopleId,a.seq) in
(
select peopleId,seq from vitae
group by peopleId,seq having count(*) > 1
)
delete from vitae a where (a.peopleId,a.seq) in
(
select peopleId,seq from vitae
group by peopleId,seq having count(*) > 1
)and rowid not in
(
select min(rowid) from vitae group by peopleId,seq having count(*)>1
)
- 重复记录保留1条
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
五、数据库的应用
事务
- 定义
- 作为单个逻辑工作单元执行的一系列操作
- A
- 原子性
- 事务中的所有操作，要么全部完成，要么全部不完成
- 要么提交，要么回滚
- 没有中间状态
- 原子性
- C
- 一致性
- 事务开始前与结束后，数据库的完整性约束没有被破坏
- 数据库机制层面
- 唯一约束
- 外键约束
- check约束
- 触发器
- 业务层面
- 保持业务的一致性
- 一致性
- I
- 隔离性
- 事务执行互不干扰
- 利用锁和阻塞来保证事务之间的隔离性
- 并发事务中
- 不同事务同时操作相同数据
- 隔离性
- D
- 持久性
- 事务一旦完成，所做修改便持久地保存在数据库中
- 持久性
锁
- 定义
- 进程间由于争夺资源而处于无限期的等待状态
- 类别
- 共享锁
- 又称读锁
- 某事务对数据加共享锁
- 其他事务也只能加共享锁
- 其他事务不能加互斥锁
- 有事务读数据时，其他事务
- 共享锁
- 不能执行除读以外的操作
- 独占锁
- 用于数据修改操作
- 某事物为数据加互斥锁，则其他事务不能再加任何锁
- 独占锁
六、概念总结
存储过程 vs 函数
- 存
- 用户定义的一系列sql语句的集合
- 涉及特定表或对象的任务
- 函
- 数据库中已定义的方法
- 不涉及特定表
游标
- 作用
- 定位结果集的行
- 从结果集中每次提取一条记录的机制
- 判断游标是否已到了最后
- 判断全局变量@@FETCH_STATUS
- 不为0表示到了最后或出错
- 尽量用存储过程执行查询的原因
- 封装
- 尽可能使各层的功能、职责隔离，不相互影响
- 安全
- 有效防防sql注入攻击
- 优化
- sql语句执行需先编译，再执行
- 存储过程可高效优化
- 封装
sql注入攻击
- 定义
- 将sql命令插入到...中
- web表单输入域
- 页面请求的查询字符串
- 欺骗服务器执行恶意的sql命令
- 将sql命令插入到...中
- 防范
- 替换单引号
- 限制权限
- 删除用户输入内容中的所有连字符
- 尽可能使用存储过程
- 检查数据的合法性
存储过程格式与调用
