PHP + MySQL tutorial (3): create a database and table _ MySQL

Source: Internet
Author: User
PHP + MySQL tutorial (3): create a database and a table database with one or more tables.

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

Syntax
Create database database_name to allow PHP to execute the preceding statement, we must use the mysql_query () function. This function is used to send queries or commands to MySQL connections.

Example
In the following example, a database named "my_db" is created:

$ 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
Create table is used to CREATE a database TABLE in MySQL.

Syntax
Create table table_name
(
Column_name1 data_type,
Column_name2 data_type,
Column_name3 data_type,
.......
) To execute this command, I must add the CREATE TABLE statement to the mysql_query () function.

Example
The following example shows how to create a table named "person" with three columns. The column names are "FirstName", "LastName", and "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: you must select a database before creating a table. Use the mysql_select_db () function to select a database.

Note: When creating a varchar database field, you must specify the maximum length of the field, for example, varchar (15 ).

MySQL data type
The following MySQL data types are available:

Value type description
Int (size)
Smallint (size)
Tinyint (size)
Mediumint (size)
Bigint (size)
Only integers are supported. Specify the maximum value of a number in the size parameter.
Decimal (size, d)
Double (size, d)
Float (size, d)
Supports decimal numbers.

Specify the maximum value of a number in the size parameter. Specify the maximum number to the right of the decimal point in parameter d.

Text data type description
Char (size) supports fixed-length strings. (Can contain letters, numbers, and special characters ).

Specify a fixed length in the size parameter.

Varchar (size) supports variable-length strings. (Can contain letters, numbers, and special characters ).

Specify the maximum length in the size parameter.

Tinytext supports variable-length strings with a maximum length of 255 characters.
Text
Blob
A variable-length string with a maximum length of 65535 characters is supported.
Mediumtext
Mediumblob
A variable-length string with a maximum length of 16777215 characters is supported.
Longtext
Longblob
A variable-length string with a maximum length of 4294967295 characters is supported.
Date data type description
Date (yyyy-mm-dd)
Datetime (yyyy-mm-dd hh: mm: ss)
Timestamp (yyyymmddhhmmss)
Time (hh: mm: ss)
Supported date or time
Description of miscellaneous data types
Enum (value1, value2, ect) ENUM is the abbreviation of ENUMERATED list. A maximum of 65535 values can be stored in brackets.
Set is similar to ENUM. However, SET can have up to 64 list items and store more than one choice

Primary key and auto increment field
Each table should have a primary key field.

The primary key is used to uniquely identify the rows in the table. Each primary key value must be unique in the table. In addition, the primary key field cannot be blank because the database engine needs a value to locate the record.

Primary key fields will always be indexed. This rule is no exception. You must index the primary key field so that the database engine can quickly locate the row with this key value.

The following example sets the personID field as the primary key field. The primary key field is usually the ID and is usually set using AUTO_INCREMENT. AUTO_INCREMENT adds the field values one by one when a new record is added. To ensure that the primary key field is NOT empty, we must add the not null setting to this field.

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 );

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.