二.《SQL必知必会》
(一)、初步了解
1、数据库:是一个以某种有组织的方式存储的数据集合;
保存有组织的数据的容器(通常是一个文件或一组文件)
2、数据库软件:称为数据库管理系统(DBMS)。
注意:数据库是通过DBMS创建和操纵的容器
3、表:是一种结构化的文件,可用来存储某种特定类型(是同一种类型的数据或清单)的数据(数据库里的文件称为表)
4、数据库中每个列都有相应的数据类型。数据类型(data type)定义了列可以存储哪些数据种类。(数据类型及名称是SQL不兼容的一个重要因素,因为可能存在着不同的DBMS下名称不同的情况,所以在创建表时要记住这些差异)
5、column列是表中的一个字段;row行是表中的一个记录
6、主键primary key:表中每一行都应该有一列(或几列)可以唯一标识自己,(总是定义主键,便于以后的数据管理和操作)
7、SQL:是结构化查询语言(Structured Query Language)的缩写。SQL是一种专门用来与数据库沟通的语言。
8、My SQL workbench操作:打开软件以后,在软件的左侧边栏有三个选项,分别是对应“连接数据库”、“设计数据库”、“迁移数据库”的功能。
9、My SQL创建数据库和表:打开MySQL;
创建新的数据库输入mysql>create database 库名;
使用该数据库并创建表:mysql>use 库名;
>create table 表名(column_name column_type)
填充数据:>INSERT INTO 表名(column_name1 column_name2 column_name3...)
VALUES('数值1', 数值2', 数值3',...);
设置主键:>ALTER TABLE 表名 ADD PRIMARY KEY (column_name);
显示表:>show 表名;
显示列:>select column_name from 表名;
10、限制结果输出(不同的DBMS语句不同)
LIMIT 5指示MySQL等DBMS返回不超过5行的数据:select prod_name from Products limit 5;
LIMIT 5 OFFSET 5指示MySQL等DBMS返回从第5行起的5行数据。第一个数字是指返回的行数,第二个数字是从哪开始
Note:MySQL简化版LIMIT 5 OFFSET 4语句为:limit4,5;
第一个被检索的行是第0行,而不是第1行。So,LIMIT 1 OFFSET 1会检索第2行,而不是第1行。
11、添加注释:
行内注释:使用-- (两个连字符)嵌在行内。-- 之后的文本就是注释
多行注释:释从/*开始,到*/结束,/*和*/之间的任何内容都是注释(可在任意位置停止或开始)
12、排序
子句(一个关键字加上所提供的数据构成)SELECT prod_name FROM Products ORDER BY prod_name;
多个列排序:SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;(两列位置顺序变动对结果又影响,先按prod_price升序排列,如遇到价格相同的则按 prod_name以字母顺序排列)
多列排序简化版SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;(先按价格排序,再按姓名)优点不用重新输入列名,缺点容易造成错用
Note:ORDER BY子句可取一个或多个列 要保证该子句是最后一个,否则会出错
即在同时有ORDER BY和where时,应该让ORDER BY在where之后
13、计算字段:加+减—乘*除/ 测试select now();返回现在日期和时间
14、拼接字段(concatenate):将值联结到一起(将一个值附加到另一个值)构成单个值。
即把两列合并拼接,
输入;SELECT concat(vend_name ,'(',vend_country,')') as vend_title FROM Vendors ORDER BY vend_name;
注释:MySQL用concat(str1,str2,...)函数。把vendors表里的name和country拼接成一个字段,并且country放在圆括号里显示(此方法不用再整理,直接为去掉空格后结果) as别名
15、My SQL函数:提取字符串substring()
数据类型转换convert()
取当前日期curdate()
16、文本处理函数:LEFT()(或使用子字符串函数) 返回字符串左边的字符
LENGTH()(也使用DATALENGTH()或LEN()) 返回字符串的长度
LOWER()(Access使用LCASE()) 将字符串转换为小写
UPPER()(Access使用UCASE()) 将字符串转换为大写
LTRIM() 去掉字符串左边的空格
RIGHT()(或使用子字符串函数) 返回字符串右边的字符
RTRIM() 去掉字符串右边的空格
SOUNDEX() 返回字符串的SOUNDEX值
Note:SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节,使得能对字符串进行发音比较而不是字母比较。(即匹配发音相似的)
17、数值处理函数:ABS() 返回一个数的绝对值
COS() 返回一个角度的余弦
EXP() 返回一个数的指数值
PI() 返回圆周率
SIN() 返回一个角度的正弦
SQRT() 返回一个数的平方根
TAN() 返回一个角度的正切
18、SQL聚集函数: AVG() 返回某列的平均值(只能返回单列的平均值,列名作为参数给出)
COUNT() 返回某列的行数
MAX() 返回某列的最大值(在用于文本数据时,返回该列最后一行数据)
MIN() 返回某列的最小值(在用于文本数据时,返回该列最前面一行数据)
SUM() 返回某列值之和(忽略列值为null的行)
Note:min()函数忽略列值为null的行
19、分组数据
创建分组数据:SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;
注释:GROUP BY子句指示DBMS按vend_id排序并分组,然后对每个组而不是整个数据集进行聚集
在使用GROUP BY子句前,需要知道一些重要的规定:
(1)GROUP BY子句可以包含任意数目的列,因而可以对分组进行嵌套,更细致地进行数据分组。
(2)如果在GROUP BY子句中嵌套了分组,数据将在最后指定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
(3)GROUP BY子句中列出的每一列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在SELECT中使用表达式,则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
(即不能为原数据库表中不存在的列名)
(4)大多数SQL实现不允许GROUP BY列带有长度可变的数据类型(如文本或备注型字段)。
(5)除聚集计算语句外,SELECT语句中的每一列都必须在GROUP BY子句中给出。
(6)如果分组列中包含具有NULL值的行,则NULL将作为一个分组返回。如果列中有多行NULL值,它们将分为一组。
(7)GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
20、过滤分组:WHERE过滤行,而HAVING过滤分组。(having支持所有where操作符)
输入SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4
GROUP BY vend_id HAVING COUNT(*) >= 2;
注释:先按where过滤价格大于4的行,并按vend_id分组,再按having过滤计数大于2 的分组,
Note:使用having是要结合group by子句,而where用于标准的行级过滤
21、分组与排序(order by与group by):
order by | group by |
对产生的输出排序 | 对行分组,但输出可能不是分组的顺序 |
任意列都可以使用(即使为非选择列) | 只可能使用选择列,且必须使用每个选择列表达式 |
不一定需要 | 如果与聚集函数一起使用列,则必须使用 |
Note:一般在使用group by时结合着order by,这样是保证数据正确排序的唯一方法,不要仅仅依赖于group by
输入SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;
注释:添加 ORDER BY子句进行排序(先分组 再计数 然后过滤 最后排序)
21、select子句顺序:
子 句 说 明 是否必须使用
SELECT 要返回的列或表达式 是
FROM 从中检索数据的表 仅在从表选择数据时使用
WHERE 行级过滤 否
GROUP BY 分组说明 仅在按组计算聚集时使用
HAVING 组级过滤 否
ORDER BY 输出排序顺序 否
22、使用子查询(subquery)
简单查询:指从单个数据库表中检索数据的单条语句
子查询:指嵌套在其他查询里的查询(即多个简单语句嵌套)总是从内向外处理
输入SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Order
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));
注释:最内层子句返回order_num(订单号),外面的子查询根据此订单号返回cust_id(顾客ID),最外面的子查询根据顾客返回cust_name, cust_contact(顾客信息)
可利用格式的缩进或者颜色的标注进行注释,方便阅读与调试
Note:子查询的select语句只能单列查询
23、带有计算字段的子查询
输入SELECT cust_name,cust_state,(SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
注释:子查询中的where子句使用了完全限定列名, 即Customers.cust_id(避免混淆)
24、联结表
SQL最强大的功能之一就是能在数据查询的执行中联结(join)表。联结是利用SQL的SELECT能执行的最重要的操作(将多个表中的数据用一条select语句来检索数据)
关系数据库设计的基础:相同的数据出现多次决不是一件好事。
关系表的设计:就是要把信息分解成多个表,一类数据一个表。各表通过某些共同的值互相关联(所以才叫关系数据库)。
25、分开储存数据的理由:既节省精力又节约空间
便于更新信息
避免重复信息录入存在错误
26、创建联结:指定要联结的所有表以及关联它们的方式,SQL本身不限制一条select语句可联结表的数目,但是具体的DBMS会有不同的限制
输入SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
注释:from子句列出要联结的两个表名字,where子句正确匹配两个表(使用完全限定列名)
Note:没有联结条件的表关系返回的结果成为笛卡儿积,且检索出的行数=第一个表的行数*第二个表的行数(说明第一个表的每一行与第二个表的每一行进行匹配,即包含原本没有的数据信息组合)
联结的表越多,越耗费资源,性能下降越厉害。
27、目前为止使用的联结称为等值联结(equijoin),它基于两个表之间的相等测试。这种联结也称为内联结(inner join)。
输入 SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
注释:输出与上一例子相同,其中on子句与where子句功能相同,inner join与on搭配而不用where
28、联结用到子查询中
输入SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';
注释:这里where有三个子句,前两个为联结表,最后一个为过滤条件
29、创建高级联结
(1)自然联结:排除多次出现重复列,(事实上,现使用的所有内联结都属于自然联结)
输入SELECT C.*, O.order_num, O.order_date,
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
(2)外联结:包含了那些在相关表中没有关联行的行
对比:内联结SELECT Customers.cust_id, Orders.order_num
FROM Orders INNER JOIN Customers
ON Orders.cust_id = Customers.cust_id; --表名的位置无影响,即保证二者的顺序一致即可
外联结SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
注释:因为外联结包含没关联行的行,所以在使用outer join时必须要结合left或者right来制定包括其所有行的表(right表示制定outer join右边的表,而left表示制定outer join左边的表)
(3)还有一种外联结,即全外联结(full outer join),它检索两个表中的所有行并关联那些可以关联的行。(与左外联结或右外联结包含一个表的不关联的行不同,全外联结包含两个表的不关联的行。)MySQL不支持该语法
30、带有聚集函数的联结
输入SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
注释:这条SELECT语句使用INNER JOIN将Customers和Orders表互相关联。GROUP BY子句按顾客分组数据,因此,函数调用COUNT(Orders.order_num)对每个顾客的订单计数,将它作为num_ord返回。
31、组合查询: (1)在一个查询中从不同的表返回结构数据;
(2)对一个表执行多个查询,按一个查询返回数据。
输入SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI') --in的用法???
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';
Note:
(1)在简单的例子中,使用UNION可能比使用WHERE子句更为复杂。但对于较复杂的过滤条件,或者从多个表(而不是一个表)中检索数据的情形,使用UNION可能会使处理更简单。
(2)自动去掉重复的行,若想要返回所有匹配的行,则用union all
要注意的规则:
(1)UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔(因此,如果组合四条SELECT语句,将要使用三个UNION关键字)。
(2)UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列不需要以相同的次序列出)。
(3)列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型(例如,不同的数值类型或不同的日期类型)。
32、对组合查询结果排序(只需在最后一条select语句加一个order by子句)
输入SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All'
ORDER BY cust_name, cust_contact;
33、插入数据(insert)方式有:
(1)插入完整的一行
输入INSERT INTO Customers VALUES('1000000006','Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);
注释:各列都必须给出一个值,且有次序要求(此语法简单,但不安全,尽量避免使用)
优化输入INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,
cust_state,cust_zip,cust_country,cust_contact,cust_email) VALUES('1000000006',
'Toy Land','123 Any Street','New York','NY','11111','USA',NULL,NULL);
注释:values以给定的次序进行插入数据,即使标的结构改变也能正常输出
(2)插入行的一部分(只列出需要插入数据的列名)省略的行要符合以下某个条件
A、该列允许为null值
B、在表的定义中给出默认值,这表示不给出值时,将使用默认值
(3)插入检索出的数据(使用insert select语句)
注释:可以用一条insert语句插入多行数据,即select返回多少行就插入多少
34、从一个复制到另一个表(select into)
与insert select的区别:前者是将行导入到一个新表;后者是从其他表中导入行
Note:如果只想复制一部分的列,只需给出明确的列名,而不用*即可
可利用联结从多个表插入数据,但是数据只能插入到一个表中
先进行复制,可在复制的数据上测试SQL代码,而不会影响实际的数据。
35、更新数据 (update)基本的UPDATE语句由三部分组成,分别是:
(1)要更新的表;update
(2)列名和它们的新值;set
(3)确定要更新哪些行的过滤条件(where)。
注释:(1)UPDATE语句中可以使用子查询,使得能用SELECT语句检索出的数据更新列数据
(2)在更新多个列时,只需要使用一条SET命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)
(3)不要省略where子句
36、删除数据(delete)
Note:
DELETE删除整行而不是删除列。要删除指定的列,请使用UPDATE语句。
若要删除表中的所有行,可使用truncate table语句(完成与select相同的工作,但速度更快)
37、下面是许多SQL程序员使用UPDATE或DELETE时所遵循的重要原则:
(1)除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
(2)保证每个表都有主键,尽可能像WHERE子句那样使用它(可以指定各主键、多个值或值的范围)。
(3)在UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WHERE子句不正确。
(4)使用强制实施引用完整性的数据库,这样DBMS将不允许删除其数据与其他表相关联的行。
(5)有的DBMS允许数据库管理员施加约束,防止执行不带WHERE子句的UPDATE或DELET语句。如果所采用的DBMS支持这个特性,应该使用它。
(6)若是SQL没有撤销(undo)按钮,应该非常小心地使用UPDATE和DELETE,否则你会发现自己更新或删除了错误的数据。
38、创建表的方法:
(1)多数DBMS具有交互式创建和管理数据库表的工具(实际使用的也是SQL)
(2)也可以直接使用SQL语句操纵
39、利用CREATE TABLE创建表,必须给出下列信息:
(1)新表的名字,在关键字CREATE TABLE之后给出;
(2)表列的名字和定义,用逗号分隔;(列的定义以列名开始,后跟数据类型)
(3)有的DBMS还要求指定表的位置。
Note:
(1)替换现有的表(即在创建新的表时),指定的表名必须不存在,否则会出错。防止意外覆盖已有的表,SQL要求首先手工删除该表,然后再重建它,而不是简单地用创建表语句覆盖它。(后面讲)
(2)在不指定not null时,多数DBMS认为默认null
(3)只有不允许null值的列可作为主键
(4)不要把NULL值与空字符串相混淆。NULL值是没有值,不是空字符串。如果指定''(两个单引号,其间没有字符),这在NOT NULL列中是允许的。空字符串是一个有效的值,它不是无值。NULL值用关键字NULL而不是空字符串指定。
40、更新表(ALTER TABLE)
以下是使用ALTER TABLE时需要考虑的事情:
(1)理想情况下,不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求,避免今后对表的结构做大改动。
(2)所有的DBMS都允许给现有的表增加列,不过对所增加列的数据类型(以及NULL和DEFAULT的使用)有所限制。
(3)许多DBMS不允许删除或更改表中的列。
(4)多数DBMS允许重新命名表中的列。
(5)许多DBMS限制对已经填有数据的列进行更改,对未填有数据的列几乎没有限制。
Note:
删除表DROP TABLE CustCopy;(无法撤销,执行该语句后讲永久删除表)
删除表列ALTER TABLE Vendors DROP COLUMN vend_phone;
注:使用ALTER TABLE要极为小心,应该在进行改动前做完整的备份(模式和数据的备份)。数据库表的更改不能撤销,
41、复杂的表结构更改一般需要手动删除过程,它涉及以下步骤:
1. 用新的列布局创建一个新表;
2. 使用INSERT SELECT语句(后面讲)从旧表复制数据到新表。有必要的话,可以使用转换函数和计算字段;
3. 检验包含所需数据的新表;
4. 重命名旧表(如果确定,可以删除它);
5. 用旧表原来的名字重命名新表;
6. 根据需要,重新创建触发器、存储过程、索引和外键
42、视图(不同的DBMS有不同的规则和限制)
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
(不包含任何列或数据,包含的是一个查询)
43、视图的一些常见应用。
(1)重用SQL语句。
(2)简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道其基本查询细节。
(3)使用表的一部分而不是整个表。(用视图过滤不想要的数据)
(4)保护数据。可以授予用户访问表的特定部分的权限,而不是整个表的访问权限。
(5)更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
Note:视图仅仅是用来查看存储在别处数据的一种设施。(视图本身不包含数据,因此返回的数据是从其他表中检索出来的)
创建可重用的视图:即不绑定特定数据,(扩展视图的范围不仅能被重用,还会更有用这样就不需要创建和维护多个视图
44、创建视图(create view)删除视图(drop view)(更新或者覆盖视图必须先删除在重新创建)
45、视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装SELECT语句的层次,可用来简化数据处理,重新格式化或保护基础数据。
46、事务处理是一种机制,用来管理必须成批执行的SQL操作,保证数据库不包含不完整的操作结
果(利用事物处理,可以保证一组操作不会中途停止,要么完全执行,要么不执行)
Note:如果没有错误发生,整组语句,提交给(写到)数据库表;如果发生错误,则进行回退(撤销),将数据库恢复到某个已知且安全的状态。
47、事务处理需要知道的几个术语:
事务(transaction)指一组SQL语句;
回退(rollback)指撤销指定SQL语句的过程;(用于撤销)
提交(commit)指将未存储的SQL语句结果写入数据库表;(用于保存更改)
保留点(savepoint)指事务处理中设置的临时占位符(placeholder),可以对它发布回退(与回退整个事务处理不同)。(MySQL使用savepoint delete1创建占位符)
Note:事务处理用来管理INSERT、UPDATE和DELETE语句,不能回退select、create或drop操作
保留点设置的越多越好,可以更灵活的进行回退
48、控制事务处理(关键在于将SQL语句分解为逻辑快,明确规定数据何时应该回退,何时不应该回退)(MySQL)使用start transaction...
49、完整的SQL server例子
输入
BEGIN TRANSACTION --MySQL使用start transaction
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
SAVE TRANSACTION StartOrder;--MySQL使用savepoint
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100,'2001/12/1','1000000010');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;--MySQL使用rollback to
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', 100, 5.49);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION
注释:这里的事务处理块中包含了4条INSERT语句。在第一条INSERT语句之后定义了一个保留点,因此,如果后面的任何一个INSERT操作失败,事务处理最近回退到这里。在SQL Server中,可检查一个名为@@ERROR的变量,看操作是否成功。(其他DBMS使用不同的函数或变量返回此信息。)如果@@ERROR返回一个非0的值,表示有错误发生,事务处理回退到保留点。如果整个事务处理成功,发布COMMIT以保留数据。
50、游标(cursor)是一个存储在DBMS服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
51、常见的选项与特性:
(1)能够标记游标为只读,使数据能读取,但不能更新和删除。
(2)能控制可以执行的定向操作(向前、向后、第一、最后、绝对位置、相对位置等)。
(3)能标记某些列为可编辑的,某些列为不可编辑的。
(4)规定范围,使游标对创建它的特定请求(如存储过程)或对所有请求可访问。
(5)指示DBMS对检索出的数据(而不是指出表中活动数据)进行复制,使数据在游标打开和访问期间不变化。
Note:主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或作出更改
52、使用游标的步骤:
(1)在使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句和游标选项。
(2)一旦声明,就必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
(3)对于填有数据的游标,根据需要取出(检索)各行。
(4)在结束游标使用时,必须关闭游标,可能的话,释放游标(有赖于具体的DBMS)。
声明:游标后,可根据需要频繁地打开和关闭游标。在游标打开时,可根据需要频繁地执行取操作。
53、约束:管理如何插入或处理数据库数据的规则
A、主键(一种特殊的约束用了保证一列或一组列中的值是唯一的,而且永不改动)
B、外键(表中的一列,其值必须列在另一表的主键中)
输入CREATE TABLE Orders
(order_num INTEGER NOT NULL PRIMARY KEY,--定义主键
order_date DATETIME NOT NULL,
cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id));--定义外键
Note:外键有助于防止意外删除(因为DBMS不允许删除在另一个表中具有关联行的行)
54、唯一约束(用来保证一列或一组列中的数据是唯一的)
与主键的重要区别:
(1)表可包含多个唯一约束,但每个表只允许一个主键。
(2)唯一约束列可包含NULL值。
(3)唯一约束列可修改或更新。
(4)唯一约束列的值可重复使用。
(5)与主键不一样,唯一约束不能用来定义外键。
D、检查约束(用来保证一列或一组列中的数据满足一组指定的条件)
常见用途:
(1)检查最小或最大值。例如,防止0个物品的订单(即使0是合法的数)。
(2)指定范围。例如,保证发货日期大于等于今天的日期,但不超过今天起一年后的日期。
(3)只允许特定的值。例如,在性别字段中只允许M或F。
55、索引(用来排序数据以加快搜索和排序操作的速度)
主键数据总是排序的(这是DBMS的工作)
DBMS搜索排过序的索引,找出匹配的位置,然后检索这些行。
在开始创建索引前,应该记住以下内容:
A、索引改善检索操作的性能,但降低了数据插入、修改和删除的性能。在执行这些操作时,DBMS必须动态地更新索引。
B、索引数据可能要占用大量的存储空间。
C、并非所有数据都适合做索引。取值不多的数据(如州)不如具有更多可能值的数据(如姓或名),能通过索引得到那么多的好处。
D、索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据,则该数据可能适合做索引。
E、可以在索引中定义多个列(例如,州加上城市)。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序,则这种索引没有用处。
输入CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);
注释:索引必须唯一命名。这里的索引名prod_name_ind在关键字CREATE INDEX之后定义。ON用来指定被索引的表,而索引中包含的列(此例中仅有一列)在表名后的圆括号中给出。
Note:索引的效率随表数据的增加或改变而变化,最好定期检查索引,并根据需要对索引进行调整
56、触发器(特殊的存储过程,在特定的数据库活动发生时自动执行)与单个的表相关联(只在特定的表上出现操作时执行)
57、触发器内的代码具有以下数据的访问权:
INSERT操作中的所有新数据;
UPDATE操作中的所有新数据和旧数据;
DELETE操作中删除的数据
58、触发器的一些常见用途:
(1)保证数据一致。例如,在INSERT或UPDATE操作中将所有州名转换为大写。
(2)基于某个表的变动在其他表上执行活动。例如,每当更新或删除一行时将审计跟踪记录写入某个日志表。
(3)进行额外的验证并根据需要回退数据。例如,保证某个顾客的可用资金不超限定,如果已经超出,则阻塞插入。
(4)计算计算列的值或更新时间戳。
59、数据库安全
一般说来,需要保护的操作有:
(1)对数据库管理功能(创建表、更改或删除已存在的表等)的访问;
(2)对特定数据库或表的访问;
(3)访问的类型(只读、对特定列的访问等);
(4)仅通过视图或存储过程对表进行访问;
(5)创建多层次的安全措施,从而允许多种基于登录的访问和控制;
(6)限制管理用户账号的能力。
Note:安全性使用SQL的GRANT和REVOKE语句来管理,不过,大多数DBMS提供了交互式的管理实用程序,这些实用程序在内部使用GRANT和REVOKE语句。
补充:
Delete从表中删除一行或多行
Drop永久地删除数据库对象(表、视图、索引等)
Insert为表添加一行
Update更新表中的一行或多行
数值数据类型 说 明
BIT 单个二进制位值,或者为0或者为1,主要用于开/关标志
DECIMAL(或NUMERIC) 定点或精度可变的浮点值
FLOAT(或NUMBER) 浮点值
INT(或INTEGER) 4字节整数值,支持-2147483648~2147483647的数
REAL 4字节浮点值
SMALLINT 2字节整数值,支持-32768~32767的数
TINYINT 1字节整数值,支持0~255的数