A Relational Database Overview关系数据库概述

A database is a means of storing information in such a way that information can be retrieved from it.数据库是一种存储信息的方式，可以从中检索信息。In simplest terms, a relational database is one that presents information in tables with rows and columns.用最简单的术语来说，关系数据库是在具有行和列的表中显示信息的数据库。A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows).表被称为关系，因为它是相同类型（行）的对象的集合。Data in a table can be related according to common keys or concepts, and the ability to retrieve related data from a table is the basis for the term relational database.表中的数据可以根据公共键或概念进行关联，从表中检索相关数据的能力是术语关系数据库的基础。A Database Management System (DBMS) handles the way data is stored, maintained, and retrieved.数据库管理系统（DBMS）处理数据的存储、维护和检索方式。In the case of a relational database, a Relational Database Management System (RDBMS) performs these tasks.对于关系数据库，关系数据库管理系统（RDBMS）执行这些任务。DBMS as used in this book is a general term that includes RDBMS.本书中使用的DBMS是一个通用术语，包括RDBMS。

Integrity Rules完整性规则

Relational tables follow certain integrity rules to ensure that the data they contain stay accurate and are always accessible. First, the rows in a relational table should all be distinct.关系表遵循某些完整性规则，以确保它们所包含的数据保持准确且始终可访问。首先，关系表中的行都应该是不同的。If there are duplicate rows, there can be problems resolving which of two possible selections is the correct one.如果存在重复行，则在解决两个可能的选择中哪一个是正确的时可能会出现问题。For most DBMSs, the user can specify that duplicate rows are not allowed, and if that is done, the DBMS will prevent the addition of any rows that duplicate an existing row.对于大多数DBMS，用户可以指定不允许重复行，如果这样做，DBMS将阻止添加与现有行重复的任何行。

A second integrity rule of the traditional relational model is that column values must not be repeating groups or arrays.传统关系模型的第二个完整性规则是列值不能是重复的组或数组。A third aspect of data integrity involves the concept of a null value.数据完整性的第三个方面涉及空值的概念。A database takes care of situations where data may not be available by using a null value to indicate that a value is missing.数据库通过使用空值指示缺少值来处理数据可能不可用的情况。It does not equate to a blank or zero. A blank is considered equal to another blank, a zero is equal to another zero, but two null values are not considered equal.它不等于空白或零。一个空白被视为等于另一个空白，一个零等于另一个零，但两个空值不被视为相等。

When each row in a table is different, it is possible to use one or more columns to identify a particular row.当表中的每一行不同时，可以使用一个或多个列来标识特定行。This unique column or group of columns is called a primary key.此唯一列或列组称为主键。Any column that is part of a primary key cannot be null; if it were, the primary key containing it would no longer be a complete identifier.属于主键的任何列都不能为空；如果是，则包含它的主键将不再是完整的标识符。This rule is referred to as entity integrity.此规则称为实体完整性。

The Employees table illustrates some of these relational database concepts.Employees表说明了其中一些关系数据库概念。It has five columns and six rows, with each row representing a different employee.它有五列六行，每行代表不同的员工。

The primary key for this table would generally be the employee number because each one is guaranteed to be different.此表的主键通常是员工编号，因为每个编号都保证是不同的。(A number is also more efficient than a string for making comparisons.)（在进行比较时，数字也比字符串更有效。）It would also be possible to use First_Name and Last_Name because the combination of the two also identifies just one row in our sample database.还可以使用First_Name和Last_Name，因为这两个名称的组合也只标识示例数据库中的一行。Using the last name alone would not work because there are two employees with the last name of "Washington."仅使用姓是行不通的，因为有两名员工姓“华盛顿”In this particular case the first names are all different, so one could conceivably use that column as a primary key, but it is best to avoid using a column where duplicates could occur.在这个特定的例子中，名字都是不同的，因此可以设想使用该列作为主键，但最好避免使用可能出现重复的列。If Elizabeth Yamaguchi gets a job at this company and the primary key is First_Name, the RDBMS will not allow her name to be added (if it has been specified that no duplicates are permitted).如果Elizabeth Yamaguchi在这家公司找到了一份工作，并且主键是First_Name，RDBMS将不允许添加她的姓名（如果已指定不允许重复）。Because there is already an Elizabeth in the table, adding a second one would make the primary key useless as a way of identifying just one row. Note that although using First_Name and Last_Name is a unique composite key for this example, it might not be unique in a larger database.因为表中已经有一个Elizabeth，添加第二个将使主键作为仅标识一行的方式变得无用。请注意，尽管在本例中使用First_Name和Last_Name是唯一的组合键，但在较大的数据库中可能不是唯一的。Note also that the Employees table assumes that there can be only one car per employee.还要注意，Employees表假定每个员工只能有一辆车。

SELECT Statements语句

SQL is a language designed to be used with relational databases.SQL是一种设计用于关系数据库的语言。There is a set of basic SQL commands that is considered standard and is used by all RDBMSs.有一组基本的SQL命令被认为是标准的，并被所有RDBMS使用。For example, all RDBMSs use the SELECT statement.例如，所有RDBMS都使用SELECT语句。

A SELECT statement, also called a query, is used to get information from a table.SELECT语句，又称为“查询”，用来从表中获得信息。It specifies one or more column headings, one or more tables from which to select, and some criteria for selection.它指定一个或多个列标题、要从中选择的一个或多个表以及一些选择条件。The RDBMS returns rows of the column entries that satisfy the stated requirements.RDBMS返回满足所述要求的列条目行。A SELECT statement such as the following will fetch the first and last names of employees who have company cars:如下所示的SELECT将抓取拥有公司汽车的员工的名和姓：

SELECT First_Name, Last_Name
FROM Employees
WHERE Car_Number IS NOT NULL

The result set (the set of rows that satisfy the requirement of not having null in the Car_Number column) follows.结果集（满足Car_Number列中不包含null的要求的行集）如下所示。The first name and last name are printed for each row that satisfies the requirement because the SELECT statement (the first line) specifies the columns First_Name and Last_Name.因为SELECT语句（第一行）指定了First_Name和Last_Name列，所以会为满足要求的每一行打印名和姓。The FROM clause (the second line) gives the table from which the columns will be selected.FROM子句（第二行）给出了将从中选择列的表。

The following code produces a result set that includes the whole table because it asks for all of the columns in the table Employees with no restrictions (no WHERE clause).下面的代码生成一个包含整个表的结果集，因为它要求表Employees中的所有列都没有限制（没有WHERE子句）。Note that SELECT * means "SELECT all columns."请注意，SELECT *表示“选择所有列”

SELECT *
FROM Employees

WHERE Clauses子句

The WHERE clause in a SELECT statement provides the criteria for selecting values.SELECT语句中的WHERE子句提供了选择值的标准。For example, in the following code fragment, values will be selected only if they occur in a row in which the column Last_Name begins with the string 'Washington'.例如，在下面的代码片段中，仅当值出现在列Last_Name以字符串“Washington”开头的行中时，才会选择这些值。

SELECT First_Name, Last_Name
FROM Employees
WHERE Last_Name LIKE 'Washington%'

The keyword LIKE is used to compare strings, and it offers the feature that patterns containing wildcards can be used.关键字LIKE用于比较字符串，它提供了可以使用包含通配符的模式的功能。For example, in the code fragment above, there is a percent sign (%) at the end of 'Washington', which signifies that any value containing the string 'Washington' plus zero or more additional characters will satisfy this selection criterion.例如，在上面的代码片段中，“Washington”的末尾有一个百分号（%），表示任何包含字符串“Washington”加上零个或更多附加字符的值都将满足此选择标准。So 'Washington' or 'Washingtonian' would be matches, but 'Washing' would not be.所以“Washington”或“Washingtonian”是匹配的，但“Washing”则不是。The other wildcard used in LIKE clauses is an underscore (_), which stands for any one character.LIKE子句中使用的另一个通配符是下划线（_），它表示任意一个字符。For example,例如：

WHERE Last_Name LIKE 'Ba_man'

would match 'Barman', 'Badman', 'Balman', 'Bagman', 'Bamman', and so on.将匹配“Barman”、“Badman”、“Balman”、“Bagman”、“Bamman”等等。

The code fragment below has a WHERE clause that uses the equal sign (=) to compare numbers.下面的代码片段有一个WHERE子句，它使用等号（=）来比较数字。It selects the first and last name of the employee who is assigned car 12.它选择分配给car 12的员工的名字和姓氏。

SELECT First_Name, Last_Name
FROM Employees
WHERE Car_Number = 12

The next code fragment selects the first and last names of employees whose employee number is greater than 10005:下一个代码片段选择员工编号大于10005的员工的姓名：

SELECT First_Name, Last_Name
FROM Employees
WHERE Employee_Number > 10005

WHERE clauses can get rather elaborate, with multiple conditions and, in some DBMSs, nested conditions.WHERE子句可以变得相当复杂，有多种条件，在某些DBMS中，还有嵌套条件。This overview will not cover complicated WHERE clauses, but the following code fragment has a WHERE clause with two conditions; this query selects the first and last names of employees whose employee number is less than 10100 and who do not have a company car.这个概述将不包括复杂的WHERE子句，但是下面的代码片段有一个WHERE子句，带有两个条件；此查询选择员工编号小于10100且没有公司汽车的员工的姓名。

SELECT First_Name, Last_Name
FROM Employees
WHERE Employee_Number < 10100 and Car_Number IS NULL

A special type of WHERE clause involves a join, which is explained in the next section.WHERE子句的一种特殊类型涉及联接，将在下一节中进行解释。

Joins联接

A distinguishing feature of relational databases is that it is possible to get data from more than one table in what is called a join.关系数据库的一个显著特点是，可以从多个表（称为联接）中获取数据。Suppose that after retrieving the names of employees who have company cars, one wanted to find out who has which car, including the make, model, and year of car.假设在检索了拥有公司汽车的员工姓名后，人们想找出谁拥有哪辆汽车，包括汽车的品牌、型号和年份。This information is stored in another table, Cars:此信息存储在另一个表Cars：

There must be one column that appears in both tables in order to relate them to each other.两个表中都必须出现一列，才能使它们相互关联。This column, which must be the primary key in one table, is called the foreign key in the other table.此列必须是一个表中的主键，在另一个表中称为外键。In this case, the column that appears in two tables is Car_Number, which is the primary key for the table Cars and the foreign key in the table Employees.在这种情况下，两个表中出现的列是Car_Number，它是表Cars的主键，也是表Employees的外键。If the 1996 Honda Civic were wrecked and deleted from the Cars table, then Car_Number 5 would also have to be removed from the Employees table in order to maintain what is called referential integrity.如果1996年的本田思域（Honda Civic）被毁坏并从Cars表中删除，那么Car_Number5也必须从Employees表中删除，以保持所谓的参考完整性。Otherwise, the foreign key column (Car_Number) in the Employees table would contain an entry that did not refer to anything in the Cars table.否则，Employees表中的外键列（Car_Number）将包含一个未引用Cars表中任何内容的条目。A foreign key must either be null or equal to an existing primary key value of the table to which it refers.外键必须为null或等于其引用的表的现有主键值。This is different from a primary key, which may not be null.这与主键不同，主键不能为null。There are several null values in the Car_Number column in the table Employees because it is possible for an employee not to have a company car.表Employees中的Car_Number列中有几个空值，因为某些员工可能没有公司汽车。

The following code asks for the first and last names of employees who have company cars and for the make, model, and year of those cars.以下代码要求提供拥有公司汽车的员工的姓名，以及这些汽车的品牌、型号和年份。Note that the FROM clause lists both the Employees and Cars tables because the requested data is contained in both tables.请注意，FROM子句同时列出Employees表和Cars表，因为请求的数据包含在这两个表中。Using the table name and a dot (.) before the column name indicates which table contains the column.使用表名和列名前的点（.）指示哪个表包含该列。

SELECT Employees.First_Name, Employees.Last_Name,
    Cars.Make, Cars.Model, Cars.Year
FROM Employees, Cars
WHERE Employees.Car_Number = Cars.Car_Number

This returns a result set that will look similar to the following:这将返回一个类似于以下内容的结果集：

Common SQL Commands通用SQL命令

SQL commands are divided into categories, the two main ones being Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands.SQL命令分为两大类，主要是数据操作语言（DML）命令和数据定义语言（DDL）命令。DML commands deal with data, either retrieving it or modifying it to keep it up-to-date.DML命令处理数据，要么检索数据，要么修改数据以使其保持最新。DDL commands create or change tables and other database objects such as views and indexes.DDL命令创建或更改表和其他数据库对象，如视图和索引。

A list of the more common DML commands follows:下面列出了更常见的DML命令：

• SELECT —  used to query and display data from a database.用于查询和显示数据库中的数据。The SELECT statement specifies which columns to include in the result set.SELECT语句指定了要包含在结果集中的列。The vast majority of the SQL commands used in applications are SELECT statements.应用程序中使用的绝大多数SQL命令都是SELECT语句。

• INSERT —  adds new rows to a table.向表中添加新行。INSERT is used to populate a newly created table or to add a new row (or rows) to an already-existing table.用于填充新创建的表或向现有表中添加新行。

• DELETE —  removes a specified row or set of rows from a table从表中删除指定的行或行集

• UPDATE —  changes an existing value in a column or group of columns in a table更改表中一列或一组列中的现有值

The more common DDL commands follow:下面是更常见的DDL命令：

• CREATE TABLE —  creates a table with the column names the user provides.使用用户提供的列名创建表。The user also needs to specify a type for the data in each column.用户还需要为每一列中的数据指定一个类型。Data types vary from one RDBMS to another, so a user might need to use metadata to establish the data types used by a particular database.数据类型因RDBMS而异，因此用户可能需要使用元数据来建立特定数据库使用的数据类型。CREATE TABLE is normally used less often than the data manipulation commands because a table is created only once, whereas adding or deleting rows or changing individual values generally occurs more frequently.CREATE TABLE的使用频率通常低于数据操作命令，因为一个表只创建一次，而添加或删除行或更改单个值的频率通常更高。

• DROP TABLE —  deletes all rows and removes the table definition from the database.删除所有行并从数据库中删除表定义。A JDBC API implementation is required to support the DROP TABLE command as specified by SQL92, Transitional Level.需要JDBCAPI实现来支持SQL92过渡级指定的DROP TABLE命令。However, support for the CASCADE and RESTRICT options of DROP TABLE is optional.但是，对DROP TABLE的CASCADE和RESTRICT选项的支持是可选的。In addition, the behavior of DROP TABLE is implementation-defined when there are views or integrity constraints defined that reference the table being dropped.此外，当定义了引用要删除的表的视图或完整性约束时，DROP TABLE的行为由实现定义。

• ALTER TABLE —  adds or removes a column from a table.从表中添加或删除列。It also adds or drops table constraints and alters column attributes它还添加或删除表约束并更改列属性

Result Sets and Cursors结果集和游标

The rows that satisfy the conditions of a query are called the result set.满足查询条件的行称为结果集。The number of rows returned in a result set can be zero, one, or many.结果集中返回的行数可以是零、一或多。A user can access the data in a result set one row at a time, and a cursor provides the means to do that.用户可以一次访问一行结果集中的数据，游标提供了实现这一点的方法。A cursor can be thought of as a pointer into a file that contains the rows of the result set, and that pointer has the ability to keep track of which row is currently being accessed.游标可以看作是指向包含结果集行的文件的指针，该指针能够跟踪当前正在访问的行。A cursor allows a user to process each row of a result set from top to bottom and consequently may be used for iterative processing.游标允许用户从上到下处理结果集的每一行，因此可用于迭代处理。Most DBMSs create a cursor automatically when a result set is generated.大多数DBMS在生成结果集时自动创建游标。

Earlier JDBC API versions added new capabilities for a result set's cursor, allowing it to move both forward and backward and also allowing it to move to a specified row or to a row whose position is relative to another row.早期的JDBCAPI版本为结果集的游标添加了新功能，允许它向前和向后移动，还允许它移动到指定的行或位置相对于另一行的行。

See Retrieving and Modifying Values from Result Sets for more information.有关详细信息，请参阅从结果集中检索和修改值。

Transactions事务

When one user is accessing data in a database, another user may be accessing the same data at the same time.当一个用户访问数据库中的数据时，另一个用户可能同时访问相同的数据。If, for instance, the first user is updating some columns in a table at the same time the second user is selecting columns from that same table, it is possible for the second user to get partly old data and partly updated data.例如，如果第一个用户正在更新表中的某些列，而第二个用户正在从同一个表中选择列，则第二个用户可能会获得部分旧数据和部分更新数据。For this reason, DBMSs use transactions to maintain data in a consistent state (data consistency) while allowing more than one user to access a database at the same time (data concurrency).因此，DBMS使用事务将数据保持在一致状态（数据一致性），同时允许多个用户同时访问数据库（数据并发性）。

A transaction is a set of one or more SQL statements that make up a logical unit of work.事务是一组由一个或多个SQL语句组成的逻辑工作单元。A transaction ends with either a commit or a rollback, depending on whether there are any problems with data consistency or data concurrency.事务以提交或回滚结束，具体取决于数据一致性或数据并发性是否存在任何问题。The commit statement makes permanent the changes resulting from the SQL statements in the transaction, and the rollback statement undoes all changes resulting from the SQL statements in the transaction.commit语句使事务中SQL语句产生的更改永久化，rollback语句撤消事务中SQL语句产生的所有更改。

A lock is a mechanism that prohibits two transactions from manipulating the same data at the same time.锁是一种禁止两个事务同时处理相同数据的机制。For example, a table lock prevents a table from being dropped if there is an uncommitted transaction on that table.例如，如果某个表上有未提交的事务，则表锁可防止该表被删除。In some DBMSs, a table lock also locks all of the rows in a table.在某些DBMS中，表锁还锁定表中的所有行。A row lock prevents two transactions from modifying the same row, or it prevents one transaction from selecting a row while another transaction is still modifying it.行锁防止两个事务修改同一行，或者防止一个事务在另一个事务仍在修改行时选择行。

See Using Transactions for more information.有关更多信息，请参阅使用事务。

Stored Procedures存储过程

A stored procedure is a group of SQL statements that can be called by name.存储过程是一组可以按名称调用的SQL语句。In other words, it is executable code, a mini-program, that performs a particular task that can be invoked the same way one can call a function or method.换句话说，它是可执行代码，一个小型程序，执行一个特定的任务，可以用调用函数或方法的相同方式调用该任务。Traditionally, stored procedures have been written in a DBMS-specific programming language.传统上，存储过程是用特定于DBMS的编程语言编写的。The latest generation of database products allows stored procedures to be written using the Java programming language and the JDBC API.最新一代的数据库产品允许使用Java编程语言和JDBCAPI编写存储过程。Stored procedures written in the Java programming language are bytecode portable between DBMSs.用Java编程语言编写的存储过程可以在DBMS之间移植字节码。Once a stored procedure is written, it can be used and reused because a DBMS that supports stored procedures will, as its name implies, store it in the database.一旦编写了存储过程，就可以使用和重用它，因为支持存储过程的DBMS（顾名思义）会将其存储在数据库中。See Using Stored Procedures for information about writing stored procedures.有关编写存储过程的信息，请参阅使用存储过程。

Metadata

Databases store user data, and they also store information about the database itself.数据库存储用户数据，还存储有关数据库本身的信息。Most DBMSs have a set of system tables, which list tables in the database, column names in each table, primary keys, foreign keys, stored procedures, and so forth.大多数DBMS都有一组系统表，其中列出了数据库中的表、每个表中的列名、主键、外键、存储过程等。Each DBMS has its own functions for getting information about table layouts and database features.每个DBMS都有自己的函数，用于获取有关表布局和数据库功能的信息。JDBC provides the interface DatabaseMetaData, which a driver writer must implement so that its methods return information about the driver and/or DBMS for which the driver is written.JDBC提供接口DatabaseMetaData，驱动程序编写器必须实现该元数据，以便其方法返回有关为其编写驱动程序的驱动程序和/或DBMS的信息。For example, a large number of methods return whether or not the driver supports a particular functionality.例如，大量方法返回驱动程序是否支持特定功能。This interface gives users and tools a standardized way to get metadata.该接口为用户和工具提供了获取元数据的标准化方法。In general, developers writing tools and drivers are the ones most likely to be concerned with metadata.一般来说，编写工具和驱动程序的开发人员最可能关心元数据。