一、基本概念

---

记录 -- 行

字段 -- 列

主键： 唯一

外键

完整性

• 数据完整性
  • 实体完整性
    表中每一记录是唯一实体
  • 域完整性
    • 表中列须满足某种特定数据类型的约束
      • 取值范围
      • 精度
  • 参照完整性
    • 两表的主键和外键数据应一致
  • 用户定义的完整性
• 完整性约束类型
  • 与表有关的约束
    • 列约束
      • 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 ...
  • avg()
  • sum()
    • select sum(field) as ... from ...
  • min()
  • max()
• 分组：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

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

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命令
• 防范
  • 替换单引号
  • 限制权限
  • 删除用户输入内容中的所有连字符
  • 尽可能使用存储过程
  • 检查数据的合法性

存储过程格式与调用