文章中的指令可直接执行以查看结果,基础表数据链接在文章末尾可以自行下载,文中大多数指令多适用于MYSQL,如部分无法使用请参考相关DBMS文档。


指令一栏

作用 指令 备注
检索 SELECT * FROM * DISTINCT去重
排序 ORDER BY DESC降序
过滤 WHERE AND OR IN NOT操作符
通配符 % _ [ ] LIKE子句
拼接 + ||
别名 AS
分组 GROUP BY HAVING子句过滤分组
联结 * JOIN * ON * 内联INNER 外联(LEFT/RIGHT)OUTER
组合查询 UNION ALL不去重
插入数据 INSERT INTO 如果直接复制表使用INSERT SELECT
更新/删除数据 UPDATE/DELETE 应使用WHERE和外键限定范围
创建/更新/删除表 CREATE/ALTER/DROP TABLE
创建视图 CREATE VIEW
创建储存过程 CREATE PROCEDURE 各DBMS中语法不同
使用储存过程 EXECUTE * 各DBMS中语法不同
创建管理事务 START TRANSACTION 各DBMS中语法不同 撤销ROLLBACK 确认COMMIT 保留点SAVEPOINT
创建游标 DECLARE * CURSOR
使用/关闭游标 OPEN/CLOSE CURSOR *

基本概念

  1. 数据库(database):保存有组织的数据的容器(通常是一个文件或者一组文件)。
  2. 表(table):某种特定类型数据的结构化清单。
  3. 模式(schema):表具有一些特性来定义如何储存数据。
  4. 列(column)、行(row):列为字段,用来限定种类;行是表中的一个记录。
  5. 数据类型(datatype):定义列可以储存数据的种类。
  6. 主键(primary key):一列或几列,其值用于唯一表示表内每一行,不得更新、修改和重用。
  7. 子句(clause):有些为必须有些为可选,例如From。
  8. 操作符(operator):链接WHERE子句的关键字,也称操作符(logic operator)。
  9. 通配符(wildcard):用于匹配值的一部分特殊字符。
  10. 搜索模式(search pattern):由字面值、通配符或组合构成的搜索条件。
  11. 谓词(predicate):操作符又是可做谓词,例如LIKE。
  12. 字段(field):基本与列意思相同,但在数据库中称列,在计算字段中称呼字段。
  13. 拼接(concatenate):将值连接(附加)到一起构成的新值。
  14. 可移植(portable):所编写的代码可以在多个系统上运行。
  15. 聚集函数(aggregate function):对数据计算并返还值。
  16. 查询(query):任何SQL语句都是查询。但一般指SELECT语句。
  17. 可伸缩(scale):能适应不断增加的工作量而不失败。
  18. 笛卡尔积(cartesian product):没有联结条件的表返回的结果。检索出的行的数目是第一个表行和第二个表行的乘积。也称为叉联结(croess join)
  19. 事务(transaction):一组SQL语句。
  20. 回退(rollback):撤销指定SQL语句。
  21. 提交(commit):将未储存的SQL语句结果写入数据库。
  22. 保留点(savepoint):事务处理中的临时占位符(placeholder),可以对他发布回退。
  23. 结果集(result set):SQL查询所检索出的结果。
  24. 游标(cursor):是一个储存在DBMS服务器上的数据库查询。
  25. 约束(constraint):管理如何插入或处理数据库数据的规则。

开始使用

SQL语句必须以分号;分隔且不区分大小写,在处理语句过程中所有空格均会被忽略,所以在书写时尽量保持美观。

SQL语句的实现并非都是一致的,基本语句往往容易移植,但复杂的语句就不一定了。

数据检索

  • 检索单列:例如SELECT prod_name FROM Products;,在返回的数据中没有进行过滤(将得到结果集的一个子集),也没有排序。
  • 检索多列:例如SELECT prod_id,prod_name,prod_price FROM Products;,区别就在于列名用逗号分隔。
  • 检索所有列:例如SELECT * FROM Products,使用通配符*则返回表中的所有列,尽量不要使用以提高性能。
  • 去重:用DISTINCT关键字,例如SELECT DISTINCT ven_id FROM Products;将其加在列名之前,并且会作用于所有的列。
  • 限制字段数:使用LIMIT关键字,例如SELECT prod_name FROM Products LIMIT 5 ;来指定返回前5行数据(在使用MYSQL情况下),如果加上OFFSET 1则表示从第二行开始,因为检索的首行是从第0行开始的。
  • 注释:使用--之后的文本就是注释,或者使用#注释,也可以使用/* XXX */来将中间部分注释掉。

数据检索排序

  • 排序单列:使用ORDER BY子句来对检索排序,但必须是最后的子句否则会报错。
  • 排序多列:区别就在于列名用逗号分隔且有先后顺序,并且可以使用数字代替列。

    排序的顺序默认为升序ASC,如果要使用降序则在对应的列后加上DESC即可。

数据过滤

数据也可以在应用层过滤,但会影响应用的性能并且浪费带宽。

  • 使用WHERE子句:例如SELECT prod_name,prod_price FROM Products WHERE prod_price = 3.49;
  • WHERE操作符
    操作符 说明 操作符 说明
    = 等于 > 大于
    <> 不等于 >= 大于等于
    != 不等于 !> 不大于
    < 小于 BETWEEN 指定两值间(a AND b)
    <= 小于等于 IS NULL 为NULL值(与0和空格不同)
    !< 不小于

高级数据过滤

  • AND操作符:用于WHERE子句关键字,逻辑与。
  • OR操作符:与AND用法相反,逻辑或。

    如果当AND和OR同时存在时,优先处理AND语句,可以且应该使用括号进行分组。

  • IN操作符:例如SELECT prod_name,prod_price FROM Products WHERE vend_id IN('DLL01','BRS01') ORDER BY prod_name;,推荐使用IN语句来代替OR语句,这样能更直观且提高性能,最大的优点是可以包含其他SELECET语句
  • NOT操作符:否定其后所跟的任何条件,不单独使用且能放在过滤的列前。

通配符

通配符本身实际上是SQL的WHERE子句中有特殊含义的字符,在使用通配符时必须使用LIKE操作符,有点类似于正则表达式。但尽可能少的使用通配符,且尽量放在最后,以提高运行的效率。

  • 百分号(%)通配符:%表示任何字符出现任意次数(包括0次),例如SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE 'Fish%' ;表示Fish起头的任何字符。如果使用LIKE 'F%Y'时会出现一个问题,文本Y之后跟有空格将不会被检索出来,解决办法时去掉空格或者使用’F%Y%’。
  • 下划线( _ )通配符:_匹配的是单个字符。
  • 方括号([ ])通配符:用于指定一个字符集,匹配其中的一个字符,可以用前缀字符^(拖字节)来取反。例如LIKE '[^JM]%'来否定整个字符集。

创建计算字段

储存在数据库中的数据一般杂乱无序,但需要在数据中找到一些有用的信息就需要计算字段。计算字段并不实际存在于数据库中,是运行时在SELECT语句内创建。一般来讲,数据库上完成操作比在客户端中更快。

  • **拼接(+或者||)**:例如SELECT vend_name + '(' + vend_country + ')' FROM Vendors ORDER BY vend_name;直接完成计算。
    但是拼接过程中,取自不同表的数据宽度并不一致,并且有些会自动填充至列宽,这样返回的数据就会被填充很多不用的空格,为了去除空格可以使用TRIM()函数来完成。
  • **使用别名(alias)**:在拼接完的字段生成后,是一个没有名字的值,所以客户端无法引用它,所以使用AS赋予其别名。例如SELECT vend_name + '(' + vend_country + ')' AS vend_title FROM Vendors ORDER BY vend_name;对比之前。
  • 执行计算:例如SELECT prod_id,quantity,item_price,quantity * item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;直接增加一列expanded_price计算总价,并且客户端可以直接引用。

函数使用

几乎所有DBMS都支持SQL语句,但对函数及其使用而言都各不相同,所以SQL函数是不可移植的,所以使用函数时尽量做好注释或者少使用函数。

一般来讲SQL均支持处理字符串、算数、处理时间日期、返回DBMS信息的函数。

  • 常用文本处理函数
    函数 说明
    LEFT( )/RIGHT( ) 返回字符串左边/右边的字符
    LEN( ) 返回字符串长度
    UPPER( )/LOWER( ) 将字符串转换为大/小写
    (L/R)TRIM( ) 去(左/右)空格
    SOUNDEX( ) 返回字符串的SOUNDEX值

    SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法,它考虑了类似的发音字符和音节,使得能对字符串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念,但多数DBMS都支持。

  • 日期和时间处理函数:每种DBMS的时间函数均不一致,可移植性很差。例如Oracle没有Access的DATEPART()函数,所以使用SELECT order_num FROM Orders WHERE to_number(to_char(order_date,'YYYY')) = 2012; 使用to_char()函数提取日期,to_number()转换为数值。而MySQL和MariaDB有各种日期处理函数例如YEAR()提取年份,所以使用时参阅文档。
  • 数值处理函数:一般用于代数、三角函数或者几何运算,但在主要的DBMS中,数值函数是最一致、统一的函数
    函数 说明
    ABS( ) 取绝对值
    SIN( )、COS( )、TAN( ) 三角函数
    EXP( ) 取期望
    PI( ) 圆周率
    SQRT( ) 平方根

数据汇总

有些时候我们需要汇总数据但是不用实际检索出来,所以SQL提供了聚集函数用于分析和报表生成:确定行列数求和行内值最大(小)/平均值

函数 说明 备注
AVG( ) 取某列平均值
COUNT( ) 返回某列行数 COUNT(*)计数全部包含NULL COUNT(COLUMN)忽略NULL
MAX/MIN( ) 取某列最大/小值 文本数据时返回排序后的最后(前)一行,且忽略NULL
SUM( ) 返回某列之和
  • 关于聚集函数
  1. 对于上述函数,如果指定ALL参数或者不指定参数,均对所有执行计算。
  2. 如果只包含不同值,在参数内加入DISTINCT参数。
  3. DISTINCT不能用于COUNT(*),所以DISTINCT必须使用列名。

分组数据

从上一节知道可以通过聚集函数可以汇总数据,但是如果数目过多每次只能返回一组数据,那么分组数据显得更加方便。

  • 创建分组GROUP BY():例如SELECT vend_id,COUT(*) AS num_prods FROM Products GROUP BY vend_id;需要注意的是:
  1. GROUP BY 可以包含任意数目的列,所以可以进行分组嵌套。
  2. 如果嵌套了分组,将在最后指定的分组上进行汇总。
  3. 子句中必须是有效的表达式(不能是聚集函数)且不能使用别名。
  4. 如果在SELECT中使用表达式,则在GROUP BY 中也应指定相同的表达式。
  5. 大多数SQL不允许GROUP BY 列带有长度可变的类型。
  6. 如果包含NULL值的行,则NULL将作为一个分组返回,且多行NULL将被分为一组。
  7. GROUP BY 子句必须在WHERE子句之后,ORDER BY 之前。
  • 过滤分组HAVING:之前在过滤时使用WHERE语句,但是他只能过滤行但是不能作用于分组,所以使用HAVING子句来代替WHERE。最大的区别在于WHERE先过滤,而HAVING则是先分组后过滤,顺序可能会改变计算值。例如SELECT vend_id,COUNT(*) AS num_prods FROM Products WHERE prod_price >=4 GROUP BY vend_id HAVING COUNT(*)>=2;这是先过滤出价格大于4的供应商再进行分组过滤。

    HAVING和WHERE非常类似,如果不指定GROUP BY,大多数DBMS会同等对待他们,所以一定要配合同步使用。

  • 关于分组与排序:ORDER BY 和 GROUP BY 两者常常完成同样工作,但是非常不同。但GROUP BY 确实时按分组顺序输出,但不一定是所需要的顺序,所以应提供明确的ORDER BY 子句。
    ORDER BY GROUP BY
    对产生的输出排序 对行分组,但输出可能不是分组顺序
    任意列都可以使用 只能对选择列或者表达式列,且必须使用每个选择列表达式
    不一定需要 如果与聚集函数一起使用列(或表达式),则一定要使用
  • SELECT 子句顺序及说明
    子句 说明 是否必须使用
    SELECT 要返回的列或表达式
    FROM 从中检索数据的表 仅在选择表数据时使用
    WHERE 行级过滤
    GROUP BY 分组说明 仅在按组计算聚集时使用
    HAVING 组级过滤
    ORDER BY 输出排序顺序

使用子查询

一般来讲数据库中的表都是关系表,如果要进行多层的查询便是查询的嵌套,即子查询。但包含子查询的语句会变得难以阅读和调试,所以应当进行适当的缩进。例如:

SELECT cust_name , cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
        FROM Orders
        WHERE order_num IN (SELECT order_num
                   FROM OrderItems
                   WHERE prod_id = ‘RGAN01’));

最里边的子查询返回订单号列表,用于其外面的子查询WHERE子句。外面的子查询返回顾客ID列表,用于最外层的WHERE子句。最外层查询返回所需要的数据。但子查询并不是最有效的方法,在后面章节将给出更多的论述。

子查询的SELECT只能用于查询单个列,如果查询多个将返回错误!

同样可以以计算字段作为子查询例如:

SELECT cust_name,
    cust_state,
    (SELECT COUNT(*)
    FROM Orders
    HERE Orders.cust_id = Customers.cust_id) AS orders

FROM Customers
ORDER BY cust_name;

子查询中的WHERE子句与前面使用的WHERE子句稍有不同,因为他使用了完全限定列名(cust_id)。他指定表名和列名(Orders.cust_id和Customers.cust_id)。子查询中用一个句点分隔表名和列名,在有可能混淆列名时必须使用这种语法。但它并不是最有效的方法,在后面学习JOIN时,同样有更多的论述。

联结表

联结表是SQL最强大的功能之一,是利用SQL的SELECT能执行的最重要的操作。将多个表分开储存并且相互联系起来,这样可以节省空间也便于维护。联结是一种机制,在一条SELECT语句中关联包。

  • 创建联结:例如SELECT vend_name,prod_name,prod_price FROM Vendors,Products WHERE Vendors.vend_id = Products.vend_id;和以前的SELECT语句不同,这里指定的两列(prod_name和prod_price)在一个表,而第三列(vend_name)在另一个表。而在FROM子句中,则是只列出了2个表:Vendors和Products,而将两个表正确联结的是WHERE子句,将Vendors中的vend_id和Products中的vend_id匹配起来。如果缺省了WHERE语句过滤,则会将第一个表的每一行和第二个表每一行匹配无论逻辑是否正确,返回的表的结果为笛卡尔积。
  • 内联结:目前使用的联结称为等值联结(equijoin),这种联结也成为内联结(inner join)。也可以对联结使用不同的语法来指定类型,例如:SELECT vend_name,prod_name,prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;这个与之前SELECT语法相同,但FROM不同,这里量表的关系是以INNER JOIN指定的部分FROM子句。在使用时,联结条件用特定的ON子句而不是WHERE子句给出,传递给ON的实际条件和传递给WHERE的相同。

    在联结处理时可能十分耗费资源,联结越多性能下降越厉害,虽然SQL本身不限制联结约束中表的数目,但实际上许多DBMS会有限制,具体参考其对应文档。

    再回顾一下使用子查询的例子:之前说到子查询不是最有效的方法,那就可以使用联结的相同查询:

    SELECT cust_name , cust_contact
    FROM Customers , Orders , OrderItems
    WHERE Customers.cust_id = Orders.cust_id
     AND OrderItems.Order_num = Order.order_num
     AND prod_id = ‘RGAN01’;

    这里用联结表代替了嵌套的子查询,虽然方法各不相同,但性能会受操作类型的影响。

高级联结

之前使用的都是简单的内联结或等值联结,现在介绍其他三种联结:自联结(self-join)、自然联结(natural join)和外联结(outer join)。

  • 自联结:例如想要给Jim Jones同一个公司的同事发邮件,如果使用子查询方式如下:

    SELECT cust_id , cust_name , cust_contact
    FROM Customers
    WHERE cust_name = (SELECT cust_name
               FROM Customers
               WHERE cust_contact = ‘Jim Jones’);

    该方法时先在表中通过Jim Jones工作的公司cust_name,然后用这个值继续在这张表中找到该公司工作的员工信息。
  • 但是如果使用自联结的话:

    SELECT c1.cust_id,c1.cust_name,c1.cust_contact
    FROM Customers AS c1,Customers AS c2
    WHERE c1.cust_name = c2.cust_name
     AND c2.cust_contact = ‘Jim Jones’;

    在两次查询中都是使用的相同的表,所以Customers表在FROM中出现了2次,但是对Customers的引用具有歧义性,因为DBMS不知道你使用的哪一张。所以我们将使用别名作为区分,如果不这样,DBMS将返回错误,因为cust_id,cust_name,cust_contact的列各有两个且DBMS不知道是哪一列(即使是同一列)。WHERE首先联结了2张表,然后再第二个表中过滤出数据再返回。
  • 自然联结:在联结时,必定有至少一列出现在两个不同的表中。标准联结返回所有的数据,相同的列甚至多次出现。自然联结排除多次出现,使每一列只返回一次。通过一个表使用通配符,而对其他表的列,使用明确子集来完成,例如:

    SELECT C.* , O.order_num , O.order_date ,
        OI.prod_id , OI.prod_quantity , OI.item_price
    FROM Customers AS C , Order AS O , OrderItems AS OI
    WHERE C.cust_id = O.cust_id
     AND OI.order_num = O.order_num
     AND OI.prod_id = ‘RGAN01’;

    在这个例子中,只有第一个表使用了通配符,而通过其他列的筛选使得没有重复的列呗检索出来,实际上迄今我们使用的每个内联结都是自然联结。
  • 外联结:许多联结会将一个表中的行和另外一个表的行相关联,但有时候需要包含没有关联的行,这就要使用外联结。对比之前的内联结——

    SELECT Customers.cust_id , Orders.order_num
    FROM Customers INNER JOIN Orders
      ON Customers.cust_id = Orders.cust_id;

    外联结语法类似——

    SELECT Customers.cust_id , Orders.order_num
    FROM Customers LEFT OUTER JOIN Orders
      ON Customers.cust_id = Orders.cust_id;

    内联结与外联结不同的是,外联结还包含了没有关联的行,在使用OUTTER JOIN时,就必须使用RIGHT或者LEFT关键字来指定其包含所有行的表。还有一种外联结叫做全外联结,其检索两个表中所有行并关联那些可以关联的行,关键字使用FULL即可,但很多DBMS均不支持这种语法。
  • 使用带聚集函数的联结:聚集函数与联结可以同时使用,例如——

    SELECT Customers.cust_id,
        COUNT(Orders.order_num) AS num_ord
    FROM Customers LEFT OUTER JOIN Orders
      ON Customers.cust_id = Orders.cust_id
    GROUP BY Customers.cust_id;

    所以在使用联结时一定要注意联结类型,且关于联结语法应参考对应的DBMS文档。使用联结一定要保证条件正确,防止出现笛卡尔积。应当降低联结的表数,以方便测试和维护。

组合查询

多数SQL语句查询只返回单挑的数据,同时SQL也支持多个查询且返回集合的形式。组合查询通常被称为并(union)或者复合查询(compound query)
主要有两种情况需要使用组合查询:

  1. 在一个查询中从不同表返回结构数据。
  2. 对一个表执行多个查询,按一个查询结果返回。

多数情况下,组合查询与多个WHERE子句所完成的工作相同 — — 任何具有多个WHERE的SELECT语句均可作为一个组合查询。

  • 创建组合查询:直接将语句1语句2UNION连接起来即可,将会返回两个语句所形成的集合,但应该查看DBMS文档了解最大语句数目限制。关于性能问题:WHERE子句和UNION理论上没有实际的差别,但在优化程序时应当测试差异。

  • UNION规则

    1. 必须由多条SELECT语句组成且用UNION连接。
    2. 每个查询语句必须包含相同的列、表达式、聚集函数(不需要相同次序)
    3. 列数据类型应保持兼容。
  • UNION性质

    1. 因为返回的数据类型为集合,所以当不同的SELECT语句返回相同数据时将会被去重,如果想保留原始重复数据应使用UNION ALL语句。
    2. SELECT语句输出时我们会使用ORDER BY进行排序,而在使用UNION组合查询时,只能用一次ORDER BY函数且必须位于最后一条SELECT语句之后,因为要保证排序的唯一性。看似ORDER BY只对最后一个SELECT语句进行排序,但实际上是先组合查询后再执行ORDER BY语句排序的。

插入数据

插入数据也是十分常用的语句,用于插入完整行、行的一部分或者某些查询数据。使用INSERT插入语句时需要在客户端/服务器DBMS中获得相对应的安全权限。

  • 插入完整行:使用INSERT INTO语句给对应的表插入新值,例如INSERT INTO Customers VALUES ('XXX','XXX','XXX',NULL);。需要注意的是,必须根据表的结构每一列提供一个值,或者使用NULL填充。因为这种语句高度依赖于表中列的定义次序,还依赖于其容易获得的次序信息,虽然语法简单但应该尽量避免使用。
    如果想使用更安全的方法也可以,指定对应的列名来控制其匹配的次序。对比之前——INSERT INTO Customers(第一列,第二列,第三列,第四列) VALUES ('XXX','XXX','XXX',NULL);即使表结构改变也可能正常工作。如果想只给部分列赋值,拿就在表的列名和值中缺省对应的项即可。

    虽然INSERT中可以省略某些列,但必须保证该列可设置为NULL值或者给定默认值。如果表中不允许有NULL或者默认值却省略列,则会禅城错误,相应行不能成功插入。

  • 插入检索出的数据:同时使用INSERT INTO语句和SELECT语句组合将筛选出来的表插入到新表中,且不需要保证列名相同,只需要保证位置正确即可。
  • 从一个表复制到另一个:可以不使用INSERT语句转而使用SELECT INTO语句。与INSERT SELECT不同的是,INSERT INTO是将数据复制到一个新表内(且有的DBMS可以覆盖之前的表),而前者是插入数据,后者相当于是导出数据,所以后者经常用于复制副本以测试。

更新和删除数据

更新和删除数据可以选择特定行也可以选择所有行,同样和插入数据一样需有足够的安全权限。

  • 更新数据:使用UPDATE语句由三部分构成:要更新的表、列名和新值、过滤条件——UPDATE 表 SET 列名 = 'XXX' WHERE 过滤条件,如果想要更新多列用逗号(,)隔开将条件间隔即可,也可以将值设置为NULL(前提是允许)来删除某列的值。
  • 删除数据:使用DELETE语句类似于SELECT语句,用子句筛选要删除的列或整表。DELETE不需要列名或者通配符,其删除的是整行而不是删除列,要删除指定列的时候应使用UPDATE语句。

    关于外键的使用:
    在之前提到的简单联结只需要两个表的共用字段,也可以让DBMS通过外键来严格实施关系。存在外键时,DBMS使用它实施引用完整性。例如要在Product表中插入一个新产品,而vend_id是以外键连接到Vendors表的。这样我使用DELETE语句在Product表删除产品时(而这个产品也用在OrderItem订单中),就可以防止出现错误。

  • 使用原则
    1. 除非更新和删除每一行,一定要使用带WHERE的DELETE或者UPDATE语句。
    2. 保证每个表都拥有主键且尽可能的像WHERE语句一样使用。
    3. 在UPDATE或DELETE使用前,尽量使用SELECT语句先进行测试。
    4. 使用强制实施引用完整性的数据库(防止允许删除数据与其关联的行)。
    5. 谨慎使用更新和删除,并查询是否存在撤销(undo)功能。

创建和操纵表

在之前章节所讲到的是操纵表内的数据,SQL还能直接用于表的创建和处理。

  • 创建表:创建表可以通过交互式创建和管理数据库表的工具,也可以直接使用SQL语句进行操作。用程序创建表可以使用CREATE TABLE语句,具体的DBMS应参考对应文档。实际上使用交互式工具就是使用SQL语句,这些语句不是用户编写的,工具会自动生成并执行相应语句。
    创建新表时,新表名字跟在CREATE TABLE语句之后,名字和定义用逗号(,)隔开,并且有些DBMS还要求指定表的位置。例如CREATE TABLE Products(列名1 数据类型 是否允许NULL,列名2 数据类型 是否允许NULL);

    在之前提到SQL语句会忽略其中的空格,所以在上述创建表的过程中使用空格仅仅是为了易于阅读与编辑。

  • 是否使用NULL值:如果需要允许使用NULL时,在数据类型后使用NULL语句或者缺省,如果不允许则使用NOT NULL,但并不是所有DBMS都时默认使用NULL的。但是NULL值与空字符串’’不同,空字符串是一个有效的值并非无值。
  • 指定默认值:在插入行时不给出值,DBMS将自动填充默认值,使用DEFAULT语句来指定字段,且常用于日期或者时间戳列。
  • 更新表:可以使用ALTER TABLE语句来更新表定义,虽然所有DBMS都能使用但差别很大。多数DBMS可以使用RENAME语句来重命名。尽量不要在表中包含数据时对其进行更新,并且对列的删除与重命名限制各不相同,应先参考对应的文档。在语句ALTER TABLE后应该给出要更改的表名,以及要做出的更改,例如:ALTER TABLE Vendors ADD vend_phone CHAR(20),但在使用其语句时应极为小心,数据库表的更改不能撤销,如果增加了不需要的行可能无法删除,删除了不该删除的列可能会丢失全部数据。
  • 删除表:删除整个表包括其结构使用DROP TABLE语句,删除表没有确认也不能撤销。

使用视图

视图与表内的数据不同,是虚拟的表,他只包含使用动态检索数据的查询。比如我要将三个表使用WHERE通过一个字段联结,WHERE语句会变得很长。而如果把整个查询包装成一个虚拟的表,这样就可以直接SELECT FROM轻松检索,而这个表就是视图的作用,所以视图包含的是一个查询。
视图相当于是将SQL语句封装和重用而并非用来储存数据,虽然可以简化操作和保护数据,但是当创建了很复杂的视图或者嵌套,性能将下降的非常厉害,所以在使用前应当测试。

  • 创建/删除视图:和创建表类似,使用CREATE/DROP VIEW来创建/删除视图,覆盖和更新视图必须先删除它再重新创建。
  • 使用视图简化联结:例如CREATE VIEW ProductCustomers AS SELECT XXX FROM XXX WHERE XXX AND XXX这样就创建了一个名为ProductCustomers的视图,就封装了这个联结且之后可以直接用FROM语句调用。

视图因为类似于条件语句的封装,所以还能包含筛选、计算字段、格式调整,可以用来简化、格式化或者保护基础数据。

使用储存过程

对于视图而言是对条件子句的封装,而储存过程则是相当于对SQL语句的封装,为以后使用而保存的一条或者多条SQL语句。所有储存过程都支持–(2个连字符)来进行注释

  • 执行储存过程:储存过程的执行比创建编写频繁得多,但是用起来很简单。使用EXECUTE接受储存过程名和需要传递的参数,例如:EXECUTE AddNewProduct('XXX1','XXX2','XXX3');这里执行了一个名为AddNewProduct的储存过程,共有3个参数。
  • 创建储存过程:相对而言创建储存过程更加重要,现在是以Oracle版本为例

    CREATE PROCEDURE MailingListCount(ListCount OUT INTEGER) IS v_row INTEGER;
    BEGIN
      SELECT COUNT(*) INTO v_rows FROM Customers WHERE NOT cust_email IS NULL;
      ListCount :=v_rows;
    END;

    这个储存过程有一个名为ListCount的参数。这个参数从储存过程返回一个值而不是传递一个值给储存过程。Oracle支持IN(传递给储存过程)、OUT(从储存过程返回值)INOUT(即储存又返回)类型的参数。储存过程的代码括在BEGIN和END语句中,这里是检索出有邮件和地址的客户,然后用检索出的行数设置ListCount。
    调用时:

    var ReturningValue NUMBER
    EXEC MailingListCount(:ReturnValue);
    SELECT ReturningValue;

    这段代码声明了一个变量来保存储存过程的返回值,然后执行储存过程,再用SELECT筛选返回。

    储存过程是相对复杂的主题,无法完全涉及且各种DBMS实现各不相同,在使用其功能前应参阅对应文档。

管理事务处理

事务处理(transaction processing)是通过确保成批的SQL操作要么完全执行,要么完全不执行,来维护数据库完整性的。例如在MYSQL中代码为:START TRANSACTION ···,可以发现一般没有明确标识事务处理在何时结束,事务一直存在直到被中断。各种DBMS实现各不相同,在使用前应参阅文档。

  • 使用ROLLBACK:例如DELETE FROM Orders;ROLLBACK;,先执行DELETE再用ROLLBACK撤销。
  • 使用COMMIT:一般SQL语句是隐式语句(implicit commit),是针对数据库表直接执行编写的,提交自动进行。但在事务处理中一般不会隐式进行(但有些DBMS也会)。在创建事务处理之后使用COMMIT;即可。
  • 使用保留点:使用简单的ROLLBACK和COMMIT即可写入和撤销事务。但复杂的事务则需要进行回退。在许多DBMS中可以使用SAVEPOINT XXX创建占位符,在需要回退时使用ROLLBACK TO XXX即可。

使用游标

SQL检索返回的结果集会有很多行,没办法定位到某行或某部分。游标是一个储存在DBMS服务器上的数据库查询,但不是一条SELECT语句,而是被该语句检索出来的结果集。储存了游标之后,可以根据需要滚动或者浏览其中的数据。
关于其特性:

  • 可控制游标为只读,防止数据更新和修改。
  • 可控制进行定向操作(相对位置、绝对位置)。
  • 能标记和规定范围。
  • 指示DBMS对检索出的数据进行复制,且使数据在游标打开和访问期间不变化。

在使用游标前,需要提前声明它,一旦声明之后就必须打开游标以供使用,这个过程用定义的SELECT语句把数据检索出来。在游标使用结束后,必须关闭游标,可能的话应释放游标。文章只介绍游标的基本使用,其他详细功能应参考对应的文档说明。

  • 创建游标:使用DECLARE语句创建游标,在不同DBMS中语法不同。该语句命名游标并定义相对的SELECT语句,根据需要使用WHERE和其他子句。例如MYSQL中:DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL定义名为CustCursor的游标来标记没有电子邮箱的客户。
  • 使用/关闭游标:使用OPEN/CLOSE CURSOR语句打开/关闭游标,且在大多数DBMS中语法相同,部分DBMS要求在关闭游标之后释放。一旦游标关闭,如果需要重新使用无需再声明,只要OPEN打开即可。

高级SQL特性

此书中介绍SQL所设计的几个高级特性:约束、索引、触发器,使用前应参阅对应文档。

约束

关联与引用完整性在之前的章节有提到,关系数据库存储分解为多个表的数据,每个表储存相应的数据。利用键来建立从一个表到另一个表的引用,所以产生了术语引用完整性(referential integrity)。正确的进行关系数据库设计,需要保证在表中插入合法数据,虽然可以在新插入前使用SELECT进行检查,保证所有值存在且合法但不应这样做:

  1. 如果在客户端层面上实施数据库完整性规则,则所有客户端均被迫实施,且会有部分客户端不实施。
  2. 在执行UPDATE和DELETE时也必须实施这些规则。
  3. 执行客户端检查非常耗时,而DBMS执行起来会相对高效。

DBMS通过在数据库表上施加约束来实施引用完整性,大多数约束是在表中定义的。

  • 主键:是一种特殊的约束,值是唯一的且永不改动和重用。这个唯一的值标识表中的每一行,以方便交互表中的行。创建时,在定义列的末尾添加PRIMARY KEY即可,如果要给表的列添加关键字使其成为主键,使用ALTER TABLE更新表且使用语句ADD CONSTRAINT PRIMARY KEY(xxx);来添加主键值。
  • 外键:外键是表中的一列,其值必须列在另一个表的主键中。比如Orders表和Customers表,订单表内每个订单号作为主键唯一,顾客表内顾客ID作为主键唯一。为了限定订单信息中顾客一定要来自于顾客表内,这样就需要在订单表中定义一个顾客ID的外键,保证引用完整性。
    使用外键和添加外键的语法和主键类似:在列定义末尾添加REFERENCES字段即可,额外添加使用ALTER更新和子句ADD CONSTRAINT FOREIGN KEY(列XX) REFERENCES 表XXX (列XX)。使用外键的另一个重要作用即是方便维护,如果想删除一个顾客,那么需要先删掉顾客相关的订单,则可以使用外键来删除以防止意外。有的DBMS支持级联删除(cascading delete)特性,启用的话将会关联删除。
  • 唯一约束:唯一约束用来保证一列或一组列中的数据唯一,类似于主键但有许多区别。约束不同于主键的唯一性可更新、重用、可包含NULL、可设置多个约束,但不能用来定义外键。唯一约束既可以用UNIQUE关键字定义,也可以用单独的CONSTRAINT定义。
  • 检查约束:用于保证一列数据满足一组指定的条件。常用于最值、范围值、特定值,使用CHECK()子句来限定条件,参考LIKE子句与通配符搭配使用。

    索引

    索引用于加快搜索和排序操作的速度。主键数据总是排序的,因此按主键检索特定行总是一种快速有效的操作。但搜索其他列中的值时效率不搞,因为表数据未经过排序。但在创建索引前应留意:
  • 索引改善检索的性能,但降低了数据插入、修改和删除的性能。因为在执行时,DBMS必须动态更新索引。
  • 索引数据可能占用大量空间,所以并非所有数据都适合索引,应选择用于过滤和排序的数据。
  • 可以在索引中定义多个列,但不一定有用。

创建方法:使用CREATE INDEX * ON *表(*列)来创建索引,索引必须唯一命名且用ON来指定被索引的表。使用的效率随表数据的变化而变化,最好做定期的检查并根据需要进行调整。

触发器

是特殊的储存过程,在特定数据库活动发生时自动执行。与储存过程不一样(储存过程知识存储SQL语句),触发器与单个的表相关联。触发器内的代码具有的访问权限:

  • INSERT操作中的所有新数据。
  • UPDATE操作中的所有新数据和旧数据。
  • DELETE操作中删除的数据。

根据所使用的DBMS不同,触发器可在特定操作执行之前和之后触发。一般触发器可以用于保证数据一致(转换大小写)、基于表变动而变动(例如记录日志)、数据验证、更新和计算列(例如时间戳)。
不同的触发器语法差异很大,例如Oracle的版本:

CREATE TRIGGER customer_state
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id
END;

这个则是使用触发器将改动的表内数据转换为大写,一般来说,约束器的处理速度比触发器要快,在可能的适合尽量使用约束。

数据库安全

大多数DBMS都提供了管理机制,任何安全系统的基础都是用户授权和身份确认。一般来说,需要保护的操作有:

  • 对数据库管理功能(创建、更新、删除)的访问
  • 对特定数据库的访问
  • 访问类型(只读、特定列访问)
  • 仅通过试图或者储存过程对表访问
  • 创建多层次安全措施,从而允许多种基于登录的访问和控制
  • 限制管理用户账号的权力

安全性使用SQL的GRANT和REVOKE语句来管理,但是大多数DBMS提供了交互式管理实用程序。

资源相关

本书中样表下载地址:http://www.forta.com/books/0672336073/