mysql create keys_创建 MySQL

Create a Database

创建一个数据库

The CREATE DATABASE statement is used to create a database in MySQL.

“CREATE DATABASE”语句是用来创建MySQL数据库的。

Syntax

语法

CREATE DATABASE database_name

To get PHP to execute the statement above we must use the mysql_query() function. This function is used to send a query or command to a MySQL connection.

在PHP内创建数据库，我们需要在mysql_query()函数内使用上述语句。这个函数是用来发送MySQL数据库连接建立的请求和指令的。

Example

案例

In the following example we create a database called "my_db":

在下面的案例当中，我们建立了一个名为“my_db”的数据库：

$con = mysql_connect("localhost","peter","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}if (mysql_query("CREATE DATABASE my_db",$con))

{

echo "Database created";

}

else

{

echo "Error creating database: " . mysql_error();

}mysql_close($con);

?>

Create a Table

建立一张表

The CREATE TABLE statement is used to create a database table in MySQL.

“CREATE TABLE”语句使用于在MySQL中建表的指令。

Syntax

语法

CREATE TABLE table_name(

column_name1 data_type,column_name2 data_type,column_name3 data_type,.......

)

We must add the CREATE TABLE statement to the mysql_query() function to execute the command.

我们必须在mysql_query()函数中加入“CREATE TABLE”来执行这个指令。

Example

案例

The following example shows how you can create a table named "Person", with three columns. The column names will be "FirstName", "LastName" and "Age":

通过下面的案例，我们演示了如何创建一个名为“Person”的表；它包含三个列，每个列的名称分别为："FirstName", "LastName" 和 "Age"：

$con = mysql_connect("localhost","peter","abc123");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}// Create database

if (mysql_query("CREATE DATABASE my_db",$con))

{

echo "Database created";

}

else

{

echo "Error creating database: " . mysql_error();

}// Create table in my_db database

mysql_select_db("my_db", $con);

$sql = "CREATE TABLE Person

(

FirstName varchar(15),

LastName varchar(15),

Age int

)";

mysql_query($sql,$con);mysql_close($con);

?>

Important: A database must be selected before a table can be created. The database is selected with the mysql_select_db() function.

重点：必须先指定数据库以后，才能够在其中建立表单；数据库通过mysql_select_db()函数来指定。

Note: When you create a database field of type varchar, you must specify the maximum length of the field, e.g. varchar(15).

注意：如果在数据库中需要用到varchar类型的字段，那么你必须指定这个域的最大长度，如：varchar(15)。

MySQL Data Types

MySQL数据类型

Below is the different MySQL data types that can be used:

下面列举了不同的MySQL数据类型：

Numeric Data Types

数值型

Description

描述

int(size)

smallint(size)

tinyint(size)

mediumint(size)

bigint(size)

Hold integers only. The maximum number of digits can be specified in the size parameter

只能包含整数。数字的最大位数可以在“size”参数中指定。

decimal(size,d)

double(size,d)

float(size,d)

Hold numbers with fractions. The maximum number of digits can be specified in the size parameter. The maximum number of digits to the right of the decimal is specified in the d parameter

包含分数(或小数)。数字的最大位数可以在“size”参数中指定。在“size”右边的参数“d”是指定小数部分的最大位数的

Textual Data Types

文本型

Description

描述

char(size)

Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis

包含固定长度的字符串(如：字母、数字、指定的字符)；字符串的长度在圆括号内的“size”处指定。如：char(20)

varchar(size)

Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis

指定变量字符串长度(如：字母、数字、指定的字符)；字符串的长度在圆括号内的“size”处指定。如：varchar(20)

tinytext

Holds a variable string with a maximum length of 255 characters

指定一个最大长度为255(字节)的变量字符串

text

blob

Holds a variable string with a maximum length of 65535 characters

指定一个最大长度为65535(字节)的变量字符串

mediumtext

mediumblob

Holds a variable string with a maximum length of 16777215 characters

指定一个最大长度为16777215(字节)的变量字符串

longtext

longblob

Holds a variable string with a maximum length of 4294967295 characters

指定一个最大长度为4294967295(字节)的变量字符串

Date Data Types

日期型

Description

描述

date(yyyy-mm-dd)

datetime(yyyy-mm-dd hh:mm:ss)

timestamp(yyyymmddhhmmss)

time(hh:mm:ss)

Holds date and/or time

指定日期(和/或)时间

Misc. Data Types

混合数据类型

Description

描述

enum(value1,value2,ect)

ENUM is short for ENUMERATED list. Can store one of up to 65535 values listed within the ( ) brackets. If a value is inserted that is not in the list, a blank value will be inserted

“ENUM”是“ENUMERATED”列表的缩写形式。它可以在圆括号“()”内指定值的列表，最多可以指定含有65535个值的列表。如果加入的值并为存在于列表中，那么将默认为自动插入一个空值。

set

SET is similar to ENUM. However, SET can have up to 64 list items and can store more than one choice

“SET”和“ENUM”类似；然而，“SET”只能指定含有64个值的列表，并且可以包含多个选项

Primary Keys and Auto Increment Fields

关键字段[Primary Keys Field]和自动增加字段[Auto Increment Field]

Each table should have a primary key field.

每张表都包含一个关键字段。

A primary key is used to uniquely identify the rows in a table. Each primary key value must be unique within the table. Furthermore, the primary key field cannot be null because the database engine requires a value to locate the record.

关键字段是用来确认一张表中记录的唯一性的。一张表中的每个私钥值都必须是独一无二的。而且，私窑不可以是空值(null)，因为数据库引擎(database engine)需要一个值对记录进行定位。

The primary key field is always indexed. There is no exception to this rule! You must index the primary key field so the database engine can quickly locate rows based on the key's value.

关键字段必须被指明，无一例外。你必须指明关键字段使得服务器可以对基于关键字段的记录进行定位。

The following example sets the personID field as the primary key field. The primary key field is often an ID number, and is often used with the AUTO_INCREMENT setting. AUTO_INCREMENT automatically increases the value of the field by 1 each time a new record is added. To ensure that the primary key field cannot be null, we must add the NOT NULL setting to the field.

下面这个案例设置了“personID”作为关键字段。关键字段通常是一个ID，他经常与AUTO_INCREMENT属性一起使用。AUTO_INCREMENT属性会在一条记录被添加后自动将“值”加“1”。为了确保关键字段不是空值，我们必须再加上“NOT NULL”属性。

Example

案例

$sql = "CREATE TABLE Person

(

personID int NOT NULL AUTO_INCREMENT,

PRIMARY KEY(personID),

FirstName varchar(15),

LastName varchar(15),

Age int

)";mysql_query($sql,$con);