理论依据:E.F.Codd提出的12条法则:
Rule 1: The Information Rule
All data should be presented to the user in table form. Last week’s newsletter already discussed the basics of this rule.
1,信息准则
关系数据库中的所有信息都应在逻辑层上用表中的值显式的表示。
Rule 2: Guaranteed Access Rule
All data should be accessible without ambiguity. This can be accomplished through a combination of the table name, primary key, and column name.
2,保证访问准则
依于表名,主键和列名,保证能以逻辑方式访问数据库中的每个数据项。
Rule 3: Systematic Treatment of Null Values
A field should be allowed to remain empty. This involves the support of a null value, which is distinct from an empty string or a number with a value of zero. Of course, this can’t apply to primary keys. In addition, most database implementations support the concept of a nun- null field constraint that prevents null values in a specific table column.
3,空值的系统化处理
RDBMS支持空值(不同于空的字符串或空白字符串,并且不为0)系统化的
表示缺少的信息,且与数据类型无关。
Rule 4: Dynamic On-Line Catalog Based on the Relational Model A relational database must provide access to its structure through the same tools that are used to access the data. This is usually accomplished by storing the structure definition within special system tables.
4,基于关系模型的联机目录
数据库的描述在逻辑上应该和一般数据采用同样的方式,使得授权用户可以
使用查询一般数据所用的关系语言来查询数据库的描述信息。
Rule 5: Comprehensive Data Sublanguage Rule
The database must support at least one clearly defined language that includes functionality for data definition, data manipulation, data integrity, and database transaction control. All commercial relational databases use forms of the standard SQL (Structured Query Language) as their supported comprehensive language.
5,合理广泛的子语言准则
一个关系系统可以具有几种语言和多种终端使用方式(表格填空方式,命令
方式等)。但是必须有一种语言,它的语句可以表示为具有严格语法规定的
字符串,并能全面的支持以下功能:数据定义,视图定义,数据操作,完整约束
,授权,事物控制。
Rule 6: View Updating Rule
Data can be presented to the user in different logical combinations, called views. Each view should support the same full range of data manipulation that direct-access to a table has available. In practice, providing update and delete access to logical views is difficult and is not fully supported by any current database.
6,视图更新准则
所有理论上可更新的视图也应该允许由系统更新。
Rule 7: High-level Insert, Update, and Delete
Data can be retrieved from a relational database in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
7,高阶的插入,更新和删除
把一个基本关系或导出关系作为一个操作对象进行数据的检索以及插入,更新和删除。
Rule 8: Physical Data Independence
The user is isolated from the physical method of storing and retrieving information from the database. Changes can be made to the underlying architecture ( hardware, disk storage methods ) without affecting how the user accesses it.
8,数据的物理独立性
无论数据库的数据在存储表示上或存取方法上做任何变化,应用程序和终端活动要都保持逻辑上的不变性。
Rule 9: Logical Data Independence
How a user views data should not change when the logical structure (tables structure) of the database changes. This rule is particularly difficult to satisfy. Most databases rely on strong ties between the user view of the data and the actual structure of the underlying tables.
9,数据的逻辑独立性
当基本表中进行理论上信息不受损害的任何变化时,应用程序和终端和终端活动都要保持逻辑上的不变性。
Rule 10: Integrity Independence
The database language (like SQL) should support constraints on user input that maintain database integrity. This rule is not fully implemented by most major vendors. At a minimum, all databases do preserve two constraints through SQL. No component of a primary key can have a null value. (see rule 3) If a foreign key is defined in one table, any value in it must exist as a primary key in another table.
10,数据完整的独立性
关系数据库的完整性约束必须是用数据子语言定义并存贮在目录中的,而不是在应用程序中加以定义的。至少要支持以下两种约束:实体完整性:主键中的属性不允许为NULL ; 参照完整性:对于关系数据库中每个不同的非空的外码值,必须存在一个取自同一个域匹配的主键值。
Rule 11: Distribution Independence
A user should be totally unaware of whether or not the database is distributed (whether parts of the database exist in multiple locations). A variety of reasons make this rule difficult to implement; I will spend time addressing these reasons when we discuss distributed databases.
11,分布的独立性
一个RDBMS应该具有分布独立性。用户不必了解数据库是否是分布式的。(无论数据库是否有部分处于复杂多重环境中)
Rule 12: Nonsubversion Rule
There should be no way to modify the database structure other than through the multiple row database language (like SQL).
12,无破坏准则
若RDBMS有某种低级语言,这一低级语言不能违背或绕过完整性准则以及高级关系语言表达的约束。
标准SQL不具备流程控制的能力,因此出现了很多在SQL基础上进行扩展的SQL,如:SQL Server—>T-SQL、Oracle—>PL-SQL.
SQL的组成:
1)DDL:CREATE语句
2)DML:UPDATE、DELETE、INSERT语句
3)DCL: SELECT语句
4)DQL:GRAMT语句
Tips:
COUNT(*) : 统计含NULL条数的总和
COUNT(列名):统计该列中非NULL条数的总和