Comprehensive exposure to SQL syntax (6)

SQL and database maintenance
Form establishment

After introducing the basic syntax in SQL, most of them are biased towards querying and filtering database data, but in fact, we There are many things that can be done through SQL commands. Next, I will introduce how to use SQL syntax commands to create a table in a database.

CREATE TABLE statement

We can use this command to create a new table, but the premise is: the database must already exist.

CREATE TABLE table(field1 type[(size)][index1][,field2 type[(size)][index2][,...]][,nultifieldindex[,...]])
table
The name of the new table to be created.
field1,field2
The new field name in the new form, at least one field.
type
The data type of the field.
size
The size of the field.
index1,index2
Use the CONSTRAINT conditional clause to define the index name of a single field.
multifieldindex
Use the CONSTRAINT conditional clause to define the index name of a multifield.


　For example:

　Create a table with employee name and department fields.

CREATE TABLE staff table (name TEST, department TEST, staff number INTEGER CONSTRAINT staff field index PRIMARY KEY)
In this example, we created a table named "staff table", And the primary key value of the table is defined to limit the data from being entered repeatedly.

Establishment of table index

CREATE INDEX statement

This command is mainly used to create an index on an existing table. Its usage is as follows:

CREATE[ UNIQUE]INDEX index ON table(field[ASC|DESC][,field[ASC|DESC],...])
[WITH {PRIMARY|DISALLOWNULL|IGNORENULL}]
index



The name of the index to be created.

table

The name of the table to be indexed.

　field

　The field name of the index to be created. And the DESC reserved word can be used to determine the order of the index.

For example:

Create an index in the employee table.
CREATE INDEX New index name
ON employee table (name department);

Field update of the table
CONSTRAINT conditional clause
CONSTRAINT function is similar to index (INDEX), Although CONSTRAINT can also establish associations between tables.
Single field index:
CONSTRAINT name{PRIMARY KEY|UNIQUE|REFERENCES foreigntable[(foreignfield1,foreignfield2)]}
Multiple field index:
CONSTRAINT name
{PRIMARY KEY(primary1[,primary2 [,...]])
|UNIQUE(unique1[,unique2[,...]])
|FOREIGN KEY (ref1[,ref2[,...]])
|REFERENCES foreigntable[(foreignfield1[,foreignfield2[,...]])]}
name
CONSTRAINT name to be created.
primary1, primary2
is used to design the field name of the primary key value (can be more than one).
unique1, unique2
is used to design the field name as a unique key value (can be more than one).
foreign key
field name, or field name that refers to fields in other tables.
foreigntable
As mentioned above, the table to be referenced.
foreignfield1, foreignfield2
The fields specified by the ref1 and ref2 fields in the referenced table. You can also omit this conditional clause if the field being referenced is a primary key value in the reference table.
For example:
When we want to create a new employee data table, the table contains three fields: name, department name and birthday, and a unique index is created from these three fields, you can use the following SQL statement.
CREATE TABLE employee data table
(name TEST, department name TEST, birthday DATETIME, CONSTRAINT employee data table limit UNIQUE (name, department name, birthday));
The above is created with the database table in SQL Related commands, you can use these commands to completely create database tables through SQL statements. The following chapters will introduce the SQL statements used for maintenance, additions and deletions after the database is established.

The above is the content of comprehensive contact with SQL syntax (6). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!