数据库之SQL

使用视图

视图

• 视图是虚拟的表,是动态查询 的一种表现
• 重用SQL语句
• 简化复杂的SQL操作.在编写查询后,可以方便的重用它而不必知道其他查询细节
• 使用表的一部分而不是整个表
• 保护数据.可以授予用户访问表的特定部分的权限,而不是这个表的访问权限
• 更改数据格式和表示.视图可返回与表层表的表示和格式不同的数据
• 视图的名字和表的名字都是唯一的
• 可创建视图的数量没有限制
• 视图可以嵌套,可以利用其它视图数据中检索出来的数据来构建视图
• 在许多DBMS中不允许用ORDER BY 来构建视图
• 有些DBMS要求返回的所有列必须有列名,如果是计算列,则需要别名
• 视图不能索引,也不能有关联的触发器,和默认值
• 有些DBMS只有查询阅读,没有写
• 有些DBMS在显示的列中有数据被删除或更新就不在这个视图了.

创建视图

• 视图的创建用 CREATE VIEW语句来创建

  • CREATE VIEW ProductCustomers AS SELECT cust_name, cust_contact, prod_id FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num

• 创建视图可以一次运行SQL语句,创建动态的查询,简化SQL语句的查询

• 利用视图可以重新格式化检索出来的数据

存储过程

为什么?

• 为了核对数据保证满足一些条件
• 对一些数据做预操作
• 数据到了某个界限,触发一些操作
• 相当于封装了一组SQL语句,调用时完成某些功能到一个简易的单元中,可以简化复杂的操作
• 由于建立的代码是同一,所以调用这个操作的数据也是一样的,防止出错的可能,
• 对管理的简化,对变动的管理只需要对存储过程做出改变,其他人员可以不知道这个变化.保证了安全性
• 因为存储过程是编译过的存储结构,所以DBMS执行处理命令时会有更少的工作量,提高了性能
• 使用存储过程可以更灵活更强的功能的处理数据
• 简单,安全,性能高

缺点

• 可移植性不高,因为DBMS的有一些差异,可能会需要更改一些结构,字段等
• 因为编写存储过程的SQL语句很复杂,所以需要有专业的知识,需要提高性能需要更为丰富的经验

执行存储过程

• EXECUTE 存储过程的名和需要传递的任何参数

  • EXECUTE AddNewProduct( ‘JTS01’, ‘Stuffed Eiffel Tower’, 6.49, ‘Plush stuffed toy with the text La ➥Tour Eiffel in red white and blue’ );
  • 验证传递的数据,保证参数的完整性
    参数可选,如果不传可以设置默认值
  • 如果不按次序,传递参数需要用’参数=值’的来传递参数
  • 输出参数,允许存储过程在执行的时候,可以更新使用的参数
  • 用SELECT语句检索数据
  • 子主题允许存储过程返回一个值给执行的应用程序 6

创建存储过程

• CREATE PROCEDURE 等关键字

  • CREATE PROCEDURE MailingListCount ( ListCount OUT INTEGER )
    IS v_rows INTEGER;
    BEGIN SELECT COUNT(*) INTO v_rows FROM Customers WHERE NOT cust_email IS NULL; ListCount := v_rows; END;
  • ListCount 是该函数的参数
  • OUT 是一种行为,表示是返回一个值
  • IN 表示传入的值
  • INOUT 表示既传递又传回
  • 以上只有一部分支持,如果要编写存储过程还需要,去查看DBMS的文档

事务处理

使用事务为了保证SQL要么执行,要么不执行,保证数据库的完整性

数据库故障:超出磁盘空间,安全限定,表锁

术语

• 事务:表示执行一组SQL语句
• 退回:表示撤销指定SQL语句的过程
• 提交:是将为存储的SQL结果存入数据库中
• 保留点:指事务处理中设置的临时占位符,对它分布回退
• 事务处理用来管理INSERT,UPDATE和DELETE语句,不能回退SELECT,CREATE或DROP语句,

STR TRANSACTION

• 控制事务处理

COMMIT

• 一般DBMS都有隐式提交,就是提交会自动进行
• 但是开启事务后就不会隐式提交,需要在确认不会出错是提交

ROLLBACK

• DELETE FROM Orders; ROLLBACK;
• 利用SQL的ROLLBACK命令撤销SQL语句

SAVEPOINT 名字

• 创建占位符

• 表留点如果表退回某个状态,

  • ROLLBACK TRANSACTION delete1;
  • ROLLBACK TO delete1;

游标

SQL操作返回的一组称为结果集的行.简单的使用SELECT没办法得到特定的行

有的时候需要在检索的结果集中前进和后退一行或多行,这就是游标的用途

特性

• 可以设置游标为只读,就只能浏览,不能进行删除和更新
• 能够操控可执行的定向操作(向前,后,第一个,最后一个,绝对位置,相对位置
• 标记某些列可编辑和不可编辑的
• 规定范围,划定不同权限的可浏览的范围

使用

• 在使用游标之前一定要定义它,在个过程中没有检索数据,这是在定义要是用的SELECT语句,和游标的选项
• 一旦声明,就必须打开游标以供使用,调用前面定义的SELECT语句检索出来
• 对于有数据的游标,根据需要的行提取数据
• 在结束使用时,要关闭游标,如果可能的话,释放游标
• 使用OPEN CURSOR 游标名 CHAR(number)语句打开游标
• 关闭使用 CLOSE 游标名
• 子主题 7

创建游标

• DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL
• 用 DECLARE 游标名 SELECT来创建游标

高级SQL特性

约束

• 管理如何插入或处理数据库数据的规则

• 大多是在创建表和修改表的时候就已经定好了

• 主键

  • 主键是一种特殊的约束,要保证这一列(一组列)是唯一的,而且永远不变动额的.

  • 任意两个主键是唯一的

  • 每行都有一个自己的主键不能weiNULL

  • 主键是不修改,不更新

  • 主键是不能重复利用的,删除了其他主键也不能用

  • 创建和修改时用PRIMARY KE 定某列就可以设置值为主键,前提要符合主键的条件

    • ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);

• 外键

  • 外键的值要保证在另一个表的主键中
  • 要保证引用的完整性

• 唯一约束

  • 保证一列中的数据是唯一的
  • 每个表可以有多个唯一约束,但是主键只有一个
  • 唯一约束可有NULL值
  • 可以修改或更新
  • 可以重复利用
  • 不能用来做外键

• 检查约束

  • 保证一列的数据满足一定的条件

索引

• 用来排序数据,用来加速搜索和排序的速度
• 索引增加了检索的操作,却降低的插入,修改删除的性能,DMNS需要动态的更新索引
• 索引数据可能要占据大量的存储空间
• 并非所有的数据都适合建立索引,可能性多的数据建立索引好处更大
• 索引用于数据过滤和数据排序,如果经常用某种特定的顺序排序,这个数据更适合做索引
• 可以在索引中定义多个列

触发器

• 触发器是一种特殊的存储过程.它在特定的数据库活动发生是自动执行

• 对数据访问权限

  • INSERT 的新数据
  • UPDATE操作中的新老数据
  • DELETE 操作中删除的数据

• 用途

  • 保证数据的一致性
  • 基于一个表的更改时,在另一个表中执行的活动
  • 根据额外的验证回退数据
  • 计算计算列的值或更新时间戳
  • 约束比触发器更快,一般选择约束

数据库安全

对数据库管理功能(创建,删除,更改,已存在的表)的访问

对特定数据库和表的访问

访问类型(只读,写入,对特定列的访问)

通过视图或存储过程对表的访问

创建多层次的安全措施,来构建访问权限

限制管理员的能力