Full access to SQL syntax (6)

SQL syntax SQL and database maintenance
The establishment of the form

After introducing the basic syntax in SQL, but mostly in favor of querying and filtering the database data, in fact, there are many things we can do with SQL commands, and the next step is to use SQL syntax commands to build a table in a database.

CREATE Table Statement

We can use this command to create a completely new form, but the premise is that the database must already exist.

CREATE table Table (field1 type[(size)][index1][,field2 type[(size)][index2][,...]] [, nultifieldindex[,...]])
Table
The new form name to create.
Field1,field2
The new field name in the new table, to less than one field above.
Type
The data type of the field.
Size
The size of the field.
Index1,index2
Use the constraint condition clause to define the index name of a single field.
Multifieldindex
Use the constraint condition clause to define the index name of a multiple field.


For example:

Create a table with Employee name and Department fields.

CREATE table Employee form (name Test, Department test, employee number INTEGER constraint Employee field index primary KEY)
In this example, we created a table with the table name "Employee table" and defined the primary key value of the table to restrict the data from being entered repeatedly.

Establishment of Table index

CREATE Index statement

This command is primarily an index of an existing table, and its use 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 established.

Table

The name of the table in which to build the index.

Field

The field name of the index to be built. The order of the indexes can be determined by desc reserved words.

For example:

Create an index in the employee table.
CREATE Index New index name
On staff form (name Department);
　
field updates for a table
Constraint conditional clause
CONSTRAINT functions are similar to indexes (index), although CONSTRAINT can also establish the correlation between tables.
Single Field index:
CONSTRAINT name{primary key| unique| REFERENCES foreigntable[(foreignfield1,foreignfield2)]}
Multi-field Index:
CONSTRAINT Name
{PRIMARY KEY (primary1[,primary2[,...]])
| UNIQUE (unique1[,unique2[,...]])
| FOREIGN KEY (ref1[,ref2[,...]])
| REFERENCES foreigntable[(foreignfield1[,foreignfield2[,...])]}
Name
The name of the constraint to be established.
Primary1,primary2
The name of the field that is designed to be the primary key value (more than one).
Unique1,unique2
The name of the field that is designed to be a unique key value (more than one).
FOREIGN key
The field name, or the field name that references the field in the other table.
ForeignTable
As mentioned above, the table to be referred to.
Foreignfield1,foreignfield2
In the table referenced to, the field specified by the Ref1,ref2 field. If the referenced field is the primary key value in the reference table, you can also omit the conditional clause.
For example:
The following SQL statement can be used when we want to create a new staff data table that contains the name, department name, and birthday three fields, and when a unique index is established by the three fields.
CREATE Table Employee Data table
(name test, department name test, birthday datetime,constraint staff data form limit UNIQUE (name, department name, birthday));
The above is in SQL, with the database table to establish the relevant commands, you can use these commands, through SQL statements, the database table is fully established, the next section will be for the database after the establishment of maintenance and additions and deletions to use the SQL statements to be introduced.