《SQL必知必会》学习笔记

二.SQL必知必会》

(一)、初步了解

1、数据库:是一个以某种有组织的方式存储的数据集合;

保存有组织的数据的容器(通常是一个文件或一组文件)

2、数据库软件:称为数据库管理系统(DBMS)。

注意:数据库是通过DBMS创建和操纵的容器

3、表:是一种结构化的文件,可用来存储某种特定类型(是同一种类型的数据或清单)的数据(数据库里的文件称为表)

4、数据库中每个列都有相应的数据类型。数据类型(data type)定义了列可以存储哪些数据种类。(数据类型及名称是SQL不兼容的一个重要因素因为可能存在着不同的DBMS下名称不同的情况,所以在创建表时要记住这些差异

5column列是表中的一个字段;row行是表中的一个记录

6、主键primary key:表中每一行都应该有一列(或几列)可以唯一标识自己,(总是定义主键,便于以后的数据管理和操作)

7SQL:是结构化查询语言(Structured Query Language)的缩写。SQL是一种专门用来与数据库沟通的语言。

8My 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行数据。第一个数字是指返回的行数,第二个数字是从哪开始

NoteMySQL简化版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子句指示DBMSvend_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 bygroup 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的数

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值