《SQL必知必会》（16-22）

1.更新和删除数据

1.1 更新数据（update）

Ø  更新单列：

update customers set cust_email='[e-mail]'where cust_id='1000000005';

Ø  更新多列：update customersset cust_contact='Sam Roberts',cust_email='[e-mail]'where cust_id='1000000006';

Ø  设置列值为NULL，可删除该列：update customersset cust_email=NULLwhere cust_id='1000000005';

1.2 删除数据

Ø  删除某行：deletefrom customers where cust_id='1000000006';

Ø  删除表：deletefrom customersordrop tablecustomers cascade constraints

注：使用外键，可以实施数据库的引用完整性，防止删除某个关系要用到的行。

补充：delete、drop、truncate、cascade constraints

1.       drop table用于删除数据表，一旦删除，该数据表在数据库中将不再存在。而delete则用于删除数据表的记录，无论记录有无，数据表仍然存在；

2.       有些数据表的列被其他表引用，如外键引用，直接使用drop table将无法删除该表。此时需要使用cascade constraints选项，删除表时，同时删除所有相关约束。drop语句将表所占用的空间全部释放。

3.       truncate和delete完成相同的工作，只是速度更快，因为不记录数据的变动。想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。速度上，一般来说: drop> truncate > delete

4.       delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

2.创建和操纵表

2.1 创建表

创建表一般有两种方法：一是多数DBMS都具有交互式创建和管理数据库表的工具；二是表也可以直接用SQL语句操纵。

2.1.1    表创建基础

说明：不同的DBMS创建表的语法的有所不同，下面语句在MySQL中，varchar必须替换为text；对于DS2，必须从最后一列中去掉null。

createtable products1 (

                       prod  char(10)notnull,

                       vend  char(10)notnull,

                       pname char(254)notnull,

                      price decimal(8,2)notnull,

                       pdesc varchar(1000)null

                       );

2.1.2    使用null值

null值就是没有值或缺值。允许null值的列也允许在插入行时不给出该列的值。不允许null值的列在插入或更新行时，该列必须有值。创建表时，默认值为null。

createtable products1 (

                       prod  char(10)notnull,

                       vend  char(10)notnull,

                       pname char(254),

                      price decimal(8,2),

                       pdesc varchar(1000)

                       );

说明：某些DBMS要求指定关键字null，如果不指定将出错。再者，只有不允许null值的列可作为主键，允许null值的列不能作为唯一标识。null是没有值，不是空字符串，如果指定’’, 这在not null列中是允许的。空字符串是个有效值，它不是无值。null值用关键字null而不是空字符串指定。

2.1.3    指定默认值

SQL允许指定默认值，在插入行时如果不给出值，DBMS将自动采用默认值。默认值在create table语句的列定义中用关键字default指定。

createtable products2 (

                   prod  char(10)notnull,

                   vend  char(10)notnull,

                   quantity integerdefault1notnull,

                   price decimal(8,2)notnull,

                   pdesc varchar(1000)null

 );

2.2 更新表

更新表定义，可以使用alter table。虽然所有的DBMS都支持alter table，但它们所允许更新的内容差别很大。

Ø  理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动。

Ø  许多DBMS不允许删除或更改表中的列。

Ø  多数DBMS允许重新命名表中的列。

Ø  许多DBMS限制对已经填有数据的列进行更改，对未填有数据的列几乎没有限制。

Ø  数据表的更改不能撤销，小心使用altertable，最好做数据备份。

altertable vendorsadd v_phone char(20);

altertable vendorsdropcolumn v_phone ;

复杂的表结构更改一般需要手动删除过程。创建新表-复制数据-检验新表数据-重命名旧表-用旧表名字重命名新表-根据需要重建触发器等。

2.3 重命名表

每个DBMS对表重命名有所不同，对于这份操作不存在严格的标准，具体参阅相应文档。

3.视图

3.1 使用视图

视图是虚拟的表，与表不一样，视图只包含使用时动态检索数据的查询，不同DBMS对视图的支持不一样。 

3.1.1    为什么使用视图

Ø  重用SQL语句。

Ø  简化复杂的SQL操作。在编写查询后可以方便的重用它而不必知道其基本的查询细节。

Ø  使用表的一部分而不是整个表。

Ø  保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。

Ø  更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

Ø  创建视图后，可以用与表基本相同的方式使用它们。可以对视图执行select操作，过滤和排序数据，将视图联结到其他视图或表，甚至添加和更新数据。

Ø  因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果创建了复杂的视图或嵌套了视图，性能可能会下降的厉害。

3.1.2    视图的规则和限制

Ø  视图必须唯一命名，不能与别的视图或表名字相同。

Ø  对于可以创建的视图数目没有限制。

Ø  创建视图，必须具有足够的访问权限。一般由数据库管理员授予。

Ø  视图可以嵌套。

Ø  许多DBMS禁止在视图中查询中使用order by子句。

Ø  有些DBMS要求对返回的所有列进行命名，如果列是计算字段，则需使用别名。

Ø  视图不能索引，也不能有关联的触发器或默认值。

Ø  有些DBMS允许创建这样的视图，它不能进行导致行不再属于视图的插入或更新。

3.2 创建视图   

3.2.1    利用视图简化复杂的联结

1. grantcreateviewto study;（授予权限）

2. createview procutsas

select cust_name,cust_contact,prod_id

from customers c,orders o,orderitems s

where c.cust_id=o.cust_idand o.order_num=s.order_num;

3.  selectcust_name,cust_contactfrom procuts

where pro_id='RGAN01';

 

3.2.2    用视图重新格式化检索出的数据

1.  create view venlocas

select rtrim(vend_name) ||'(' || rtrim(vend_country) ||')'

as vend_title

from vendors orderby vend_name;

2.  select *from venloc;

3.2.3    用视图过滤不需要的数据

1.  createview cusemaias

selectcust_id,cust_name,cust_email

from customers

where cust_emailisnotnull;

2.  select *from cusemai;

 

18.2.4    使用视图与计算字段

1.  createview ordexpas

selectorder_num,prod_id,quantity,item_price,quantity*item_priceas expanded_price

from orderitems where order_num=20008;

2.  select *from ordexp where order_num=20008;

创建各种视图语句除了select子句，其他都一样。

4.使用存储过程

存储过程就是为以后使用而保存的一条或多条SQL语句。Access和SQLite不支持存储过程。这只提供Oracle的语法。使用存储过程的好处：

Ø  通过把处理封装在一个易用的单元中，可以简化复杂的操作。

Ø  由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。

Ø  简化对变动的管理。

Ø  因为存储过程通常以编译过的形式处理，所以DBMS处理命令所需的工作量少，提高了性能。

Ø  存在一些只能用在单个请求中的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

执行存储过程（execute）：

execute addnewproduct('JTS01',

                      'Stuffed EiffelTower',

                      6.39,

                      'Plush andblue');

这里执行一个名为addnewproduct的存储过程，将一个新产品添加到products表，并将传入的属性赋给相应的列。其实在products表中还需要一个列值prod_id，它是表的主键，这个值不作为属性传递给存储过程，因为要保证恰当地生成此ID，最好是使生成此ID的过程自动化。

对邮件发送清单中具有邮件地址的顾客进行计数，创建此存储过程：

create  procedure mailingiscount (

       listcount out integer

)

is

v_rows integer;

begin

  select count(*) into v_rows

  from customers

  where not cust_mail is null;

  listcount:=v_rows;

end;

调用示例：

var returnvalue number

exec mailingiscount(:returnvalue)

select returnvalue;

5.管理事务处理

5.1 事务管理

事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行。如果没有错误发生，整组语句提交给数据库表；如果发生错误，则进行回退，将数据库恢复到某个已知且安全的状态。

Ø  事务（transaction）指一组SQL语句；

Ø  回退（rollback）指撤销指定SQL语句的过程；

Ø  提交（commit）指将未存储的SQL  语句结果写入数据库表；

Ø  保留点（savepoint）指事务处理中设置的临时占位符，可以对它发布回退（与回退整个事务处理不同）。

5.2 控制事务处理

不同的DBMS用来实现事务处理语法稍有不同。在使用事务处理时需参阅相应的DBMS文档。管理事务的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

SQL语言分为五大类：
DDL(数据定义语言)- Create、Alter、Drop 这些语句自动提交，无需用Commit提交。
DQL(数据查询语言) -Select 查询语句不存在提交问题。
DML(数据操纵语言)- Insert、Update、Delete 这些语句需要Commit才能提交。
DTL(事务控制语言) -Commit、Rollback事务提交与回滚语句。
DCL(数据控制语言)- Grant、Revoke授予权限与回收权限语句。

有的DBMS要求明确标识事务处理块的开始和结束。如在SQL Server中，标识如下：

begin transaction

…

commit transaction

在这个示例中，begin transaction和committransaction语句之间的SQL必须完全执行或者完全不执行。

MariaDB和MySQL中等同的代码为：start transaction。

oracle的语法：set transaction。

5.2.1    使用rollback

rollback对已经commit的操作无法撤销。

delete from orders;

rollback;

5.2.2    使用commit

在SQL Server中，标识如下：

begin transaction

delete orderitems where order_num=12345

delete orders where order_num=12345

commit transaction

以此使用事务块来保证订单不被部分删除，最后的commit语句仅在不出错时写出更改。

oracle中：

set transaction

delete orderitems where order_num=12345;

delete orders where order_num=12345;

commit;

5.2.3    使用保留点

要支持回退部分事务，必须在事务处理模块中的合适位置放置占位符。这样，如果需要回退，可以回退到某个占位符。

在oracle、MariaDB和MySQL中创建占位符(savepoint)：

savepoint delete1;

在oracle、MariaDB和MySQL中回退：

rollback to delete1;

在SQL Server中创建：

save transaction delete1;

在SQL Server中回退：

rollback transaction delete1;

SQL Server完整示例：

begin transaction

insert into customers(cust_name,cuat_id)

values(’ToysEmporium’,’1000000010’);

save transaction startorder;

insert into orders(order_num,order_date,cust_id)

values(20100,’2001/12/1’,’1000000010’);

if @@error <> 0 rollback transaction startorder;

insert into…;

if @@error <> 0 rollback transaction startorder;

commit transaction

分析：在第一条insert语句后设置一个保留点，因此后面的任何一个insert操作失败，事务都能回退到这里。如果整个事务处理成功，发布commit以保留数据。

6.使用游标

6.1游标

游标（cursor）是一个存储在DBMS服务器上的数据库查询，它不是一条select语句，而是被该语句检索出来的结果集。有时需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标的选项和特性：

Ø  能够标记游标为只读，使数据能读取，但不能更新和删除。

Ø  能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置、相对位置等）。

Ø  能标记某些列为可编辑的，某些列为不可编辑的。

Ø  规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。

Ø  指示DBMS岁检索出的数据进行复制，使数据在游标打开和访问期间不变化。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或作出更改。

6.2 使用游标

6.2.1  创建游标（declare）

下面是创建此游标的DB2、MariaDB、MySQL和SQL Server版本。

declare custcursor cursorforselect * from customerswhere cust_email is null

oracle版本：

declare cursor custcursoris select *from customers where cust_email isnull;

6.2.2  使用游标

open cursor custcursor;

在处理open cursor语句时，执行查询，存储检索出的数据以供浏览和滚动。

下面用fetch语句访问游标数据。fetch指出要检索出哪些行，从何处检索以及将它们放于何处。

declare type custcursor is ref cursor                    return customers%rowtype;declare custrecord customers%rowtypebegin               open custcursor;               fetch custcursor into custrecord;               close custcursor;end;

关闭游标

close custcursor

7.高级SQL特性（约束、索引、触发器）

7.1 约束

约束（constraint）：管理如何插入或处理数据库数据的规则。大多数约束是在表定义中定义，用create table或alter table。      

7.1.1    主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。也就是，表中的一列（或多个列）的值唯一标识表中的每一行。

表中任意列只要满足以下条件，都可以用于主键。

Ø  任意两行的主键值不相同。

Ø  每行都具有一个主键值（即列中不允许null值）。

Ø  包含主键值的列从不修改或更新。

Ø  主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

实例1：

CREATE TABLE Customers

(

                  cust_id      char(10) NOT NULL primary key ,

                  cust_name    char(50) NOT NULL ,

                  cust_addresschar(50) NULL ,

                  cust_city    char(50) NULL

);

实例2：

ALTERT ABLE VendorsADD CONSTRAINT  PK_Vendors  PRIMARY KEY (vend_id);

7.1.2    外键

外键是表中的一列，其值必须列在另一表的主键中。

CREATE TABLE Orders2

(

                      order_num  int      NOTNULL ,

                      order_date date     NOTNULL ,

cust_id    char(10)NOT NULL references customers(cust_id)

);

相同的工作可以在alter中完成：

ALTER TABLE Orders

ADD CONSTRAINT FK_Orders_Customers

FOREIGN KEY (cust_id)

REFERENCES Customers (cust_id);

7.1.3    唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。类似于主键，但存在以下重要区别。

Ø  表可包含多个唯一约束，但每个表只允许一个主键。

Ø  唯一约束列可包含null值。

Ø  唯一约束列可修改或更新。

Ø  唯一约束列的值可重复使用。

Ø  唯一约束列不能用来定义外键。

唯一约束列既可用unique关键字（列之后）在表中定义，也可以用单独的constraint定义。

7.1.4    检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件，比如：

Ø  检查最小或最大值。

Ø  指定范围。

Ø  只允许特定的值。例如在性别字段中只允许M或F。

CREATE TABLE OrderItems

(

                   order_num  int         NOT NULL ,

                   order_item int         NOT NULL ,

                  prod_id    char(10)    NOT NULL ,

                  quantity   int           NOT NULL check (quantity>0),

                  item_price  decimal(8,2)  NOT NULL

);

7.2 索引

索引用来排序数据以加快搜索和排序操作的速度。在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表。创建索引;

create index prod_name_idon products(prod_name);

7.3 触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的insert、update和delete操作相关联。

与存储过程不同，触发器与单个表相关联。与order表上的insert操作相关联的触发器只在orders表中插入行时执行。

触发器内的代码具有以下数据的访问权：

Ø  insert操作中的所有新数据；

Ø  update操作中的所有新数据和旧数据；

Ø  delete操作中删除的数据。

示例：oracle数据库，创建一个触发器，它对所有insert和update操作，将customers表中的cust_state列转换为大写。

create trigger customers_state

after insert or update

on customers

for each row

         begin

         update customers

         set cust_state=upper(cust_state)

         where customers.cust_id=:old.cust_id

 end;

一般来说，约束比触发器块，所以尽量使用约束。

7.4 数据库安全

任何安全系统的基础都是用户授权和身份确认，所以安全使用SQL的grant和remoke语句来管理。一般来说，需要保护的操作有：

Ø  对数据库管理功能（创建表、更改或删除已存在的表等）的访问；

Ø  对特定数据库或表的访问；

Ø  访问的类型（只读、对特定列的访问等）；

Ø  仅通过视图或存储过程对表进行访问；

Ø  创建多层次的安全措施，从而允许多种基于登陆的访问和控制；

Ø  限制管理用户账号的能力。