Create a Database
建立一个数据库
To create a database:
建立一个数据库;
CREATE DATABASE database_name |
Create a Table
建立一张数据表
To create a table in a database:
在一数据库中建立一张表:
CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... ) |
Example
举例
This example demonstrates how you can create a table named "Person", with four columns. The column names will be "LastName", "FirstName", "Address", and "Age":
这个举例演示了怎样建立一个含有四个栏目的数据表"Person",这些栏目分别为"LastName","FirstName","Address"和"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:
数据类型的指定关系到这个栏目所能存放的数据。下面这张表所放的一些类型是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". 保留小数。最到数字范围在括号中的size中指定,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.
利用建立索引可以快速高效地定位到现存表中的记录行上。我们可以在一张表上建立一个针对单个或多个的栏目索引,并且每个索引可以有它自己的名称。用户并不会看见这些索引,它们仅仅让查询速度变快而已。
注意:一旦表中含有索引那么它的数据更新速度就会减慢,这是因为里面的索引也需要同步更新。因此最好只给那些搜索时经常会用到的栏目加上索引。
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.
"column_name"指定的就是那个你想要让其成为索引的栏目名
A Simple Index
简易的索引
Creates a simple index on a table. When the UNIQUE keyword is omitted, duplicate values are allowed.
给一个表建立索引而没有加上UNIQUE关键字,那么它所包含的数据值是可以出现重复的。
CREATE INDEX index_name ON table_name (column_name) |
The "column_name" specifies the column you want indexed.
"column_name"指定的就是那个你想要让其成为索引的栏目名
Example
举例
This example creates a simple index, named "PersonIndex", on the LastName field of the Person table:
这个举例中将会建立一个简易的索引,命名为"PersonIndex",并针对Person表中的LastName栏目值进行建立:
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:
如果你想要让栏目中的数据降序排列,那么你可以在栏目名的后面加上一个保留字DESC:
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) |