第四部分 约束 视图 事务 安全

一、约束

        CHECK（检查）约束限定允许插入某列的值。她和WHERE子句使用相同的条件表达式。如果插入的值无法通过CHECK条件，那么会出现错误信息。（MySQL中无法使用check，可以用触发机制(trigger，满足特定条件才执行)来实现）。

CREATE TABLE piggy_bank
(
    id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    coin CHAR(1) CHECK (coin IN('P','N','D','Q')),
    coin_year CHAR(4)
)

CREATE TABLE mystery
(
    column1 INT(4) CHECK (column1>200),
    column2 CHAR(1) CHECK (column2 NOT IN('x','y','z')),
    column3 VARCHAR(3) CHECK ('A'=SUBSTRING(column3,1,1)),
    column4 VARCHAR(3) CHECK ('A'=SUBSTRING(column4,1,1) AND 'A'=SUBSTRING(column4,2,1))
)

ALTER TABLE my_contacts
ADD CONSTRAINT CHECK gender IN('M','F');

 

 二、视图

CRETE VIEW  web_designers AS
SELECT mc.name,mc.phone,mc.email
FROM my_contacts mc 
NATURAL JOIN job_desired jd   #此处也可以改为INNER JOIN job_desired jd ON mc.id=jd.id
WHERE jd.title='Web Designer';

 

SELECT * FROM web_designers;

          这个视图与查询等价于 

SELECT * FROM 
CRETE VIEW  web_designers AS
SELECT mc.name,mc.phone,mc.email
FROM my_contacts mc 
NATURAL JOIN job_desired jd 
WHERE jd.title='Web Designer';  

         视图是一个查询中使用VIEW是才存在的表，它被视为虚拟表。视图把查询简化成一条命令。即使一直改变数据库结构也不会破坏依赖表的应用程序。创建视图可以隐藏读者需看到的信息。

CRETE VIEW  job_raises AS
SELECT mc.name,jc.id,jc.salary,jd.salary_low,jd.salary_low-jc.salary AS raise
FROM my_contacts mc 
INNER JOIN job_desired jd 
INNER JOIN job_current jc
WHERE mc.contact_id=jd.contact_id AND jd.contact_id=jc.contact_id;

        这个查询是庞大的，且经常使用时，可以将其创建为一个视图，这样就只需要输入SELECT * FROM job_raises;就可以查到需要的信息。

        视图不仅仅能用于SELECT，还能用于UPDATE、INSERT、DELETE。当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化，同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

CRETE VIEW  pb_dimes AS
SELECT * FROM piggy_bank 
WHERE coin='D' WITH CHECK OPTION;

         这个视图带有CHECK OPTION，这意味着，如果对视图进行update或者insert时，会检查修改或插入是否符合WHERE后面的内容，例如 UPDATE pb_dimes SET coin='X';不符合WHERE后面的字句。

        一般，不使用INSERT、UPDATE、DELETE来改变视图。

        用SHOW TABLES能看到表和视图，用DESC来查看结构，用DROP VIEW pd_dimes;来删除视图。

 

三、事务

         在事务过程中，如果所有步骤无法不收干扰完成，则不该完成任何单一步骤。一个SQL步骤是否是事务可以用ACID来判断：一、原子性(autoumicity),不能只发生一半就停下来；二、一致性(consistency),步骤完成后应该维持数据库的一致性，例如，取钱后，余额与所取的钱的和应该与原始余额相等；三、隔离性(iolation),每次事务应该看到相同的数据库，两个人使用同一张卡同时同时取钱，一个人正在完成操作，另外一个正在查询余额，这时后一个人根本就不应该看到余额，或者后一个人看到“正在交易”之类的信息；四、持久性(burability),事务完成后，通常把数据放在主数据库以外的地方，这样就可以防止断电或者其他威胁。

        SQL事务工具有：START TRANSACTION持续追踪后续所有SQL语句，直到COMMIT或者ROLLBACK，而COMMIT是提交代码进行执行，ROLLBACK是撤销到START TRANSACTION状态。

        要想使用事务，必须要采用正确的存储引擎。有些存储引擎不允许事务，可以用SHOW CREATE TABLE my_contacts;来查看存储引擎(反括号后面ENGINE=...)，支持事务的存储引擎有BDB和InnoDB。改变存储引擎的语句是

ALTER TABLE my_contacts TYPE=InnoDB;

 

START TRANSACTION;
SELECT * FROM piggy_bank;
UPDATE piggy_bank SET coin='Q' WHERE coin='P';
SELECT * FROM piggy_bank;
ROLLBACK;    #放弃改变数据，回到原始状态
SELECT * FROM piggy_bank;

 

START TRANSACTION;
SELECT * FROM piggy_bank;
UPDATE piggy_bank SET coin='Q' WHERE coin='P';
SELECT * FROM piggy_bank;
COMMIT;    #确认改变数据
SELECT * FROM piggy_bank;

 

四、安全

    数据库中默认第一位用户为根用户root，它具有一切权限。给它设置密码如下：

SET PASSWORD FOR 'root'@'localhost' =PASSWORD('123456');

        localhost表示运行查询的计算机，即安装当前SQL RDBMS。如果你的SQL软件在O'Reilly的网络中的一台叫kumquats的机器上，那么主机名可能是[e-mail]。 

        创建用户：

CREATE USER elsie IDENTIFIED BY '123456';创建用户elsie密码123456

         可以使用GRANT语句来控制用户的权限：

GRANT INSERT ON animals TO doc;  #给doc INSERT 表 animals权限
GRANT INSERT、SELECT ON animals TO doc;
GRANT ALL ON animals TO doc;    #doc拥有SELECT、INSERT、UPDATE、DELETE表animals的权限
GRANT DELETE ON animals TO doc,happy WITH GRANT OPTION ;
             #给doc、happy DELETE 表 annimals的权限，并且他们有权把同样的权限给别人
GRANT SELECT(animal_name) ON animals TO doc; #给doc查询表animal中的列animal_name的权限
GRANT SELECT ON cottage.* ON doc;  #doc查询数据库cottage中所有的表

         用REVOKE撤销权限：

REVOKE INSERT ON animals FROM doc;  #收回INSERT权限
REVOKE GRANT OPTION ON
    DELETE ON animals TO doc,happy; #收回给予别人权限的权限，但仍然有DELETE权限
REVOKE DELETE ON animals FROM happy CASCADE;  #收回happy的DELETE权限，同时收回happy赋予别人的权限，默认为CASCADE
REVOKE DELETE ON animals FROM happy RESTRICT;  #如果happy给了第三方权限，那么“收回”失败

 

GRANT SELECT ON animals TO doc IDENTIFIED BY '123456';#创建用户并授权

        
        如果要给一群人赋予相同的权限，可以先创建一个角色，在给他们权限。

CREATE ROLE data_entry；    #创建角色
GRANT SELECT ,INSERT ON animanls TO  data_entry;  #给角色授权
GRANT data_entry TO doc;  #用户doc和角色data_entry有一样的权限
DROP ROLE data_entry;     #删除角色，用户doc不再有权限

 

GRANT data_entry TO doc WITH ADMIN OPTION;  #用户doc获得权限并且可以把data_entry赋予别人
REVOKE data_entry FROM doc CASCADE;  #收回doc的权限，doc给别人的权限也被收回
REVOKE data_entry FROM doc RESTRICT; #如果doc给别人权限，那么收回失败