Database principles-Data definition

Definition, deletion and modification of basic tables for data definition

Defined

Format: CREATE table < table name > (< column name >< data type >[column-level integrity constraints],< column name >< data type >[column-level integrity constraints]...,[table-level integrity constraints]);

Example 1:

CREATE TABLE Student (
ID INT (one) PRIMARY KEY,
NAME VARCHAR (one) UNIQUE
);

Example 2:

CREATE TABLE Student (
ID INT (11),
NAME VARCHAR (11),
PRIMARY KEY (ID),
Poreign KEY (name) REFERENCES NAMES (name)
);

Modify

Format: ALTER TABLE

[Add [Column] < new column name >< data type >[column-level integrity constraint]]

[Add [table-level integrity constraint]]

[Drop[column] < column name >[cascade|restrict]]

[Drop constraint < integrity constraint name >[cascade|restrict]]

[Alter COLUMN < NEW column name >< data type;]

Example 1 adding a column

ALTER TABLE Students ADD sentrancedate DATETIME;

Example 2 Modifying a column

Alter TABLE Students ALTER COLUMN Sage SMALLINT;

Example 3 removing a unique constraint

ALTER TABLE Students DROP CONSTRAINT un_sname;

Delete

Format: Drop table < table name >[cascade|restrict]]

Default delete is restrict

Instance:

drop table students;

Definition, deletion, and modification of indexes

Format: Create [Unique][cluster] Index < index name > on < table name > (< listing >[< Order >],< listing >[< Order;]); Order is ASC ascending , desc Descending

Example: Create unique index indexname on student (id ASC);

Modify

Format: Alter INDEX < old index name > Rename to < new index name >;

Example: Alter index stusname rename to newname;

Delete

Format: Drop INDEX < index name >;

Example: Drop index stusname;

Database principles-Data definition