SQL 指南-创建数据库、表、索引
创建数据库
CREATE DATABASE database_name |
-------------------------------------------
创建表
在数据库中创建表:
CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... ) |
示例
This example demonstrates how you can create a table named "Person", with four columns. The column names will be "LastName", "FirstName", "Address", and "Age":
CREATE TABLE Person ( LastName varchar, FirstName varchar, Address varchar, Age int ) |
This example demonstrates how you can specify a maximum length for some columns:
CREATE TABLE Person ( LastName varchar(30), FirstName varchar, Address varchar, Age int(3) ) |
The data type specifies what type of data the column can hold. The table below contains the most common data types in SQL:
Data Type | Description |
---|---|
integer(size) int(size) smallint(size) tinyint(size) |
Hold integers only. The maximum number of digits are specified in parenthesis. |
decimal(size,d) numeric(size,d) |
Hold numbers with fractions. The maximum number of digits are specified in "size". The maximum number of digits to the right of the decimal is specified in "d". |
char(size) | Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. |
varchar(size) | Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. |
date(yyyymmdd) | Holds a date |
-------------------------------------------
Create Index
Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed up queries.
Note: Updating a table containing indexes takes more time than updating a table without, this is because the indexes also need an update. So, it is a good idea to create indexes only on columns that are often used for a search.A Unique Index
Creates a unique index on a table. A unique index means that two rows cannot have the same index value.
CREATE UNIQUE INDEX index_name ON table_name (column_name) |
The "column_name" specifies the column you want indexed.
A Simple Index
Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.
CREATE INDEX index_name ON table_name (column_name) |
The "column_name" specifies the column you want indexed.
Example
This example creates a simple index, named "PersonIndex", on the LastName field of the Person table:
CREATE INDEX PersonIndex ON Person (LastName) |
If you want to index the values in a column in descending order, you can add the reserved word DESC after the column name:
CREATE INDEX PersonIndex ON Person (LastName DESC) |
If you want to index more than one column you can list the column names within the parentheses, separated by commas:
CREATE INDEX PersonIndex ON Person (LastName, FirstName) |