Basic Table Creation
To create a table using CREATE TABLE, you must specify the following information:
_The name of the new table specified after the keywords CREATE TABLE.
_The name and definition of the table columns separated by commas.
_Some DBMSs require that you also specify the table location.
The following SQL statement creates the Products table used throughout this book:
Input
CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);
Analysis
As you can see in the above statement, the table name is specified immediately following the CREATE TABLE keywords. The actual table definition (all the columns) is enclosed within parentheses. The columns themselves are separated by commas. This particular table is made up of five columns. Each column definition starts with the column name (which must be unique within the table), followed by the column's datatype. (Refer to Lesson 1, "Understanding SQL," for an explanation of datatypes. In addition, Appendix D, "Using SQL Datatypes," lists commonly used datatypes and their compatibility.) The entire statement is terminated with a semicolon after the closing parenthesis.
I mentioned earlier that CREATE TABLE syntax varies greatly from one DBMS to another, and the simple script just seen demonstrates this. While the statement will work as is on Oracle, PostgreSQL, SQL Server, and Sybase, for MySQL the varchar must be replaced with text, and for DB2 the NULL must be removed from the final column. This is why I had to create a different SQL table creation script for each DBMS (as explained in Appendix A).
Statement Formatting As you will recall, whitespace is ignored in SQL statements. Statements can be typed on one long line or broken up over many lines. It makes no difference at all. This enables you to format your SQL as best suits you. The preceding CREATE TABLE statement is a good example of SQL statement formatting—the code is specified over multiple lines, with the column definitions indented for easier reading and editing. Formatting your SQL in this way is entirely optional, but highly recommended.
Replacing Existing Tables When you create a new table, the table name specified must not exist or you'll generate an error. To prevent accidental overwriting, SQL requires that you first manually remove a table (see later sections for details) and then recreate it, rather than just overwriting it.
To create a table using CREATE TABLE, you must specify the following information:
_The name of the new table specified after the keywords CREATE TABLE.
_The name and definition of the table columns separated by commas.
_Some DBMSs require that you also specify the table location.
The following SQL statement creates the Products table used throughout this book:
Input
CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);
Analysis
As you can see in the above statement, the table name is specified immediately following the CREATE TABLE keywords. The actual table definition (all the columns) is enclosed within parentheses. The columns themselves are separated by commas. This particular table is made up of five columns. Each column definition starts with the column name (which must be unique within the table), followed by the column's datatype. (Refer to Lesson 1, "Understanding SQL," for an explanation of datatypes. In addition, Appendix D, "Using SQL Datatypes," lists commonly used datatypes and their compatibility.) The entire statement is terminated with a semicolon after the closing parenthesis.
I mentioned earlier that CREATE TABLE syntax varies greatly from one DBMS to another, and the simple script just seen demonstrates this. While the statement will work as is on Oracle, PostgreSQL, SQL Server, and Sybase, for MySQL the varchar must be replaced with text, and for DB2 the NULL must be removed from the final column. This is why I had to create a different SQL table creation script for each DBMS (as explained in Appendix A).
Statement Formatting As you will recall, whitespace is ignored in SQL statements. Statements can be typed on one long line or broken up over many lines. It makes no difference at all. This enables you to format your SQL as best suits you. The preceding CREATE TABLE statement is a good example of SQL statement formatting—the code is specified over multiple lines, with the column definitions indented for easier reading and editing. Formatting your SQL in this way is entirely optional, but highly recommended.
Replacing Existing Tables When you create a new table, the table name specified must not exist or you'll generate an error. To prevent accidental overwriting, SQL requires that you first manually remove a table (see later sections for details) and then recreate it, rather than just overwriting it.
To create a table using CREATE TABLE, you must specify the following information:
要使用CREATE TABLE创建表，必须指定如下信息：_The name of the new table specified after the keywords CREATE TABLE.
在CREATE TABLE后必须指定表的名字。_The name and definition of the table columns separated by commas.
指定表中列的名字和定义，列之间用逗号分隔。_Some DBMSs require that you also specify the table location.
有些DBMS要求必须指定表的位置。The following SQL statement creates the Products table used throughout this book:
下面的SQL语句创建了Products表：Input
输入CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_name CHAR(254) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL
);Analysis
分析As you can see in the above statement, the table name is specified immediately following the CREATE TABLE keywords.
在上面的说明中，可以看到，表名字紧跟在CREATE TABLE之后。The actual table definition (all the columns) is enclosed within parentheses.
表定义(所有列)是在括号中的。The columns themselves are separated by commas.
列之间用逗号分隔。This particular table is made up of five columns.
上面创建的这个表有5列。Each column definition starts with the column name (which must be unique within the table), followed by the column‘s datatype.
每个列定义以列名开头(在同一个表中，列名必须是唯一的)，紧跟着是列的数据类型。(Refer to Lesson 1, "Understanding SQL," for an explanation of datatypes.
(参考第1节，“理解SQL”，关于数据类型的说明。In addition, Appendix D, "Using SQL Datatypes," lists commonly used datatypes and their compatibility.)
还有，附录D，使用SQL数据类型，给出了常用的数据类型和适用范围。The entire statement is terminated with a semicolon after the closing parenthesis.
语句以末尾括号后的分号结束。I mentioned earlier that CREATE TABLE syntax varies greatly from one DBMS to another, and the simple script just seen demonstrates this.
前面提到CREATE TABLE的语法在不同的DBMS中可能相差很大，上面这个简单的SQL脚本仅是为了说明这一点。While the statement will work as is on Oracle, PostgreSQL, SQL Server, and Sybase, for MySQL the varchar must be replaced with text,
and for DB2 the NULL must be removed from the final column.
如果SQL语句要运行在Oracle, PostgreSQL, SQL Server, and Sybase上，如：在MySQL中varchar必须写为text，而在DB2中最后一列不能定义为NULL。
This is why I had to create a different SQL table creation script for each DBMS (as explained in Appendix A).
这就是在不同的DBMS上创建不同的SQL脚本定义的原因(详情查看附录A)。Statement Formatting As you will recall, whitespace is ignored in SQL statements.
正如前面看到的语句格式，在SQL语句中，空格是被忽略的。Statements can be typed on one long line or broken up over many lines.
一个SQL语句可以写成一整行，也可以写成很多行。It makes no difference at all. This enables you to format your SQL as best suits you.
这根本没有区别。这能使你更方便的调整SQL语句的格式。The preceding CREATE TABLE statement is a good example of SQL statement formatting—the code is specified over multiple lines,
with the column definitions indented for easier reading and editing.
前面讲到的CREATE TABLE语句是一个格式很好的例子，代码分开在多行中，列定义更便于维护。Formatting your SQL in this way is entirely optional, but highly recommended.
以这种方式书写SQL不是必须，但是强烈建议这样做。Replacing Existing Tables When you create a new table, the table name specified must not exist or you‘ll generate an error.
当要创建一个新表，来替代已经存在的表时，表名必须原来不存在，否则会出错。To prevent accidental overwriting, SQL requires that you first manually remove a table (see later sections for details) and
then recreate it, rather than just overwriting it.
为了防止无意的覆盖操作，SQL要求必须先手工删除表(详情查看后面的章节)，再重新创建，而不是直接覆盖。