Create a database with SQL
Let's talk about how to create a database with a SQL statement. The statement that creates a database is as follows: 1. CREATE TABLE (Create a new table) 2. CREATE INDEX 3. DROP INDEX 4. Constraint 4. Constraint 5. ALTER TABLE 6. DROP TABLE (Delete Table)
CREATE TABLE statement:
Generate a new table in the database, the type of field in the table can be: Integer, Long (long integer), Single (single-precision floating point), DoubleTime (Date type, also Can be written to Date), Bit (Bur), Text (string type, maximum 255 bytes), MEMO (string type, maximum can be 1.2G byte), Counter (automatic growth, can determine record Uniqueness), currency (monetary, accurate to the left side of the decimal point, 4 digits on the right), binary (byte, maximum 255), longbinary (for OLE object), GUID (globally unique identifier).
Generate table newtable, which has text fields Field1 and integer fields Field2, table names, and field names can be casually taking, not case sensitive, but some reserved words cannot be used as a table name, such as Number
Create Table NewTable (Field1 Text (30), Field2 Integer;
CREATE INDEX statement:
Index is to speed up the speed of finding records, or set to add field constraints.
Creating an index statement performs a record in the previous table, but the existence record must meet the constraint relationship of the index statement, otherwise the statement cannot be executed, and it should be noted that in the same database (not just in the same table) The index name cannot be the same, otherwise the statement will fail.
Generate the index field of field field1 NewIndex, two statements
After the generation, the Field1 field can have the same value, you can have a null value (NULL)
Create Index NewIndex on NewTable (Field1);
Create Index NewIndex On NewTable (Field1) with ignore null;
Generate the index field of field field1 NewIndex, note that there can only be a primary index (Primary) per table. After the generation, the field1 field does not have the same value, can not have a null value (of course, if it is the TEXT type, there can be an empty string, but the empty string is not a null value)
Create Index NewIndex on NewTable (Field1) with primary;
Field Field1 does not have the same value, but can have a null value (two null values are not the same value)
CREATE UNIQUE INDEX NEWINDEX ON NEWTABLE (Field1);
Field Field1 can have the same value, but no null value
Create INDEX NEWINDEX ON NEWTABLE (FIELD2) WITH DISALLL can add ASC (ascending) or DESC (descending) to control record arrangement, if not use sequential word, SQL uses ASC order by default
Create INDEX NEWINDEX ON NewTable (Field1 ASC, Field2 DESC);
DROP INDEX statement:
Delete the index in the table newTable NewIndex, the index newIndex must exist before the statement
Drop Index NewIndex on NewTable;
CONSTRAINT statement:
The Constraint clause is used to create an index of database integrity. Like the INDEX statement, some places can be replaced with each other, it can use Primary Key, Unique (unique) and Foreign Key (external keyword), and Compared to Ignor Null and Disallow Null compared to Ignor Null, more Foreign Key (this is also its most powerful place). In addition, the consTRAINT statement must be used with the Create Table or ALTER TABLE statement.
Generate a table newtable, the main key field is field1, the main index is newpk
Create Table NewTable (Field1 Long Constraint Newpk Primary Key, Field2 Memo, Field3 DateTime);
Generating an index for newuk table newtable, Field1 does not have the same value, you can have a null value
CREATE TABLE NEWTABLE (Field1 Integer Constraint Newuk Unique);
Generate multi-column main index, the two recorded field1 and field2 cannot be all the same, nor can no null value
Create Table NewTable (Field1 INTEGER, FIELD2 CURRENCY, CONSTRAINT Newpk Primary Key (Field1, Field2);
Generate multiple unique indexes, two recorded field1 and field2 cannot be all the same, if two records are the same, the other field is null, that is also different from the two fields.
Create Table NewTable (Field1 Integer, Field2 Currency, Constraint Newuk Unique (Field1, Field2);
To create a connection between several different tables, use the Foreign Key References clause, which can qualify the field content of a table must exist in another table.
First example:
First, generate the main key field for the table for Field1 newTable11
Create Table NewTable11 (FIELD1 INTEGER CONSTRAINT Newpk Primary Key);
Then, regenerate into an external index, the field1 of the two tables must be the same, and the field1 of the first table is the primary key field or the UNIQUE field. After generating an external index, the table newTable2 is to increase the record, and its Field1 field value must already exist in the field1 field of the table newtable1.
The following two statements have the same effect, because Field1 is the main key field of NewTable1, you can omit
Create Table NewTable2 (Field1 Integer ConsTable1); Create Table NewTable2 (Field1 Integer Construction Newfk References NewTable1 (Field1));
Second example:
First, build the main key field for the table newtable1 for field1 and field2
Create Table NewTable11 (Field1 Integer, Field2 Text (20), Constraint Newpk Primary Key (Field1, Field2);
Then, generate multiple column external indexes
Create Table NewTable2 (Field1 Integer, Field2 Text (20), ConsTRAINT Newfk Foreign Key (Field1, Field2) References NewTable1 (Field1, Field2));
ALTER TABLE statement:
After the table generation, if you want to modify the structure of the table, use this statement, it can increase or delete fields and constraints.
Give the table newtable Add the date field field3, statement execution Previous table NEWTALBE must have no field Field3
Alter Table NewTable Add Column Field3 Date;
Delete the field field3 in the table NewTable, the statement execution field field field must exist in the table newTable
Alter Table NewTable Drop Column Field3;
Add newUK constraints to table newtable
Alter Table NewTable Add Constraint Newuk Unique (Field1, Field2);
Delete the table NewTable Newuk constraint
Alter Table NewTable Drop Constraint Newuk;
DROP TABLE statement:
Delete the table newtable, the statement execution before the NewTable must exist
Drop Table newTable;
