Summary of MySQL, SQL Server, Oracle Three database maintenance indexes, foreign keys, field syntax

Summary of MySQL, SQL Server, Oracle Three database maintenance indexes, foreign keys, field syntax

1. mysql database 1) Create an index

CREATE INDEX index_name on table_name (column_list)

CREATE UNIQUE INDEX index_name on table_name (column_list)

To modify a table by adding an index

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

2) Delete Index

DROP INDEX index_name on Talbe_name

Modify the table to delete the index

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

3) Create a foreign key

To modify a table by adding foreign keys

ALTER TABLE table_name
ADD [CONSTRAINT foreign Key name]foreign key (Index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)

Example:

ALTER TABLE table_name ADD CONSTRAINT fk_name (user_id)

REFERENCE Sys_user (ID)

It is important to note that the data types of the two fields are consistent;

4) Delete foreign key

ALTER TABLE sys_org DROP FOREIGN KEY fk_s_o_id;

5) Column Manipulation syntax

? Add column birthday, with the columns keyword

ALTER TABLE Sys_useradd COLUMN birthday char (n) not null;

? Modify column birthday, with modify keyword

ALTER TABLE sys_usermodify Birthday char (10);

? Delete Column birthday, with drop-column keyword

ALTER TABLE Sys_userdrop column birthday;

? Modify the column name and properties, modify the birthday to CSRQ, and change the property to char (10) and not allow NULL for NULL

ALTER TABLE Sys_userchange birthday CSRQ char (ten) not null;

2. SQL SERVER database 1) index creation

Create UNIQUE INDEX un_index_name on Sys_user (user_name);

2) Delete Index

DROP INDEX Un_index_name on Sys_user

3) Create a foreign key

ALTER TABLE sys_org add CONSTRAINT fk_s_o_id FOREIGN key (Create_user)

REFERENCES Sys_user (ID);

Consistent with MySQL, it is important to note that the data types of the two fields are consistent;

4) Delete foreign key

ALTER TABLE sys_org DROP constraint fk_s_o_id;

Different from MySQL syntax

5) Column Maintenance syntax

6) Add column birthday, with columns keyword

ALTER TABLE Sys_user add birthday char (n) not null;

7) Modify column Birthday property with ALTER COLUMN keyword

ALTER TABLE Sys_user altercolumn birthday char (10);

8) Delete Column birthday, there is drop-column keyword

ALTER TABLE Sys_user Dropcolumn birthday;

9) Modify the column name and properties, modify the birthday to CSRQ, and change the attribute to char (10) and not NULL for nulls; two-step execution is required;

exec sp_rename ' Sys_user. [Birthday] ', ' csrq ', ' COLUMN ';

ALTER TABLE Sys_user Altercolumn CSRQ char (TEN) not null;

3. ORACLE database 1) index creation

CREATE INDEX index_name on table_name (column_list)

CREATE UNIQUE INDEX index_name on table_name (column_list)

Create INDEX ind_s_u_sex on sys_user (sex);

2) Delete Index

DROP INDEX [schema.] IndexName;

Drop index Ind_s_u_sex;

3) Create a foreign key

To modify a table by adding foreign keys

ALTER TABLE table_name
ADD [CONSTRAINT foreign Key name]foreign key (Index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)

Example:

ALTER TABLE table_name ADD CONSTRAINT fk_name (user_id)

REFERENCE Sys_user (ID)

It is important to note that the data types of the two fields are consistent;

4) Delete foreign key

ALTER TABLE table_name DROP CONSTRAINT fk_name;

Example:

ALTER TABLE sys_org drop constraint fk_s_o_id;

5) Column Manipulation syntax

? Add column birthday, with add keyword

ALTER TABLE Sys_useradd Birthday char (n) not null;

? Modify column birthday, with modify keyword

ALTER TABLE sys_usermodify Birthday char (10);

? Delete Column birthday, with drop-column keyword

ALTER TABLE sys_user dropcolumn birthday;

? Modify the column names and properties, modify the birthday to CSRQ, and change the property to char (10) and not NULL for nulls; two-step execution is required;

Altertable sys_user RENAME COLUMN birthday to CSRQ;

ALTER TABLE sys_user Modifycsrq char (10);

Summary of MySQL, SQL Server, Oracle Three database maintenance indexes, foreign keys, field syntax