Summary of index, foreign key, and field syntax maintenance for mysql, SQL Server, and oracle databases, and SQL Server oracle

Summary of index, foreign key, and field syntax maintenance for mysql, SQL Server, and oracle databases, and SQL Server oracle

Summary of index, foreign key, and field syntax maintenance for mysql, SQL Server, and oracle databases

 

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)

Modify the table to add 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 an index

Drop index index_name ON talbe_name

Delete an index by modifying the table

Alter table table_name drop index index_name

Alter table table_name DROP PRIMARY KEY

 

3) create a foreign key

Modify the table to add a foreign key

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)

Note that the data types of the two fields are consistent;

 

4) delete a foreign key

Alter table sys_org drop foreign key fk_s_o_id;

 

5) column operation syntax

Add the COLUMN birthday with the COLUMN keyword

Alter table sys_useradd COLUMN birthday char (19) not null;

MODIFY the column birthday with the MODIFY keyword

Alter table sys_usermodify birthday char (10 );

Delete the COLUMN birthday with the drop column keyword

Alter table sys_userdrop column birthday;

Modify the column name and attributes, change birthday to CSRQ, and change the attribute to char (10) and not null.

Alter table sys_userchange birthday CSRQ char (10) not null;

2. SQL SERVER database 1) Create an index

Create unique index un_index_name on sys_user (user_name );

2) delete an 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 );

The data type of the two fields must be consistent with that of mysql;

4) delete a foreign key

Alter table sys_org DROP constraint fk_s_o_id;

Different from mysql syntax

 

5) column maintenance syntax

6) ADD the column birthday with the ADD keyword

Alter table sys_user add birthday char (19) not null;

7) modify the column birthday attribute with the alter column keyword

Alter table sys_user altercolumn birthday char (10 );

8) Delete the COLUMN birthday with the drop column keyword

Alter table sys_user dropcolumn birthday;

9) modify the column name and attributes, change the birthday to CSRQ, and change the attribute to char (10) and not allow null not null. Perform the modification in two steps;

Exec sp_rename 'sys _ user. [birthday] ', 'srq', 'column ';

Alter table sys_user altercolumn CSRQ char (10) not null;

3. ORACLE database 1) Create an index

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 an index

Drop index [schema.] indexname;

Drop index ind_s_u_sex;

3) create a foreign key

Modify the table to add a foreign key

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 foreign key (user_id)

REFERENCE sys_user (id)

Note that the data types of the two fields are consistent;

 

4) delete a foreign key

Alter table TABLE_NAME drop constraint FK_NAME;

Example:

Alter table sys_org drop constraint fk_s_o_id;

5) column operation syntax

Add the column birthday with the add keyword

Alter table sys_user add birthday char (19) not null;

MODIFY the column birthday with the MODIFY keyword

Alter table sys_usermodify birthday char (10 );

Delete the COLUMN birthday with the drop column keyword

Alter table sys_user DROPCOLUMN birthday;

Modify the column name and attributes, change birthday to CSRQ, and change the attribute to char (10) or not allow null not null. The modification should be done in two steps;

ALTERTABLE sys_user rename column birthday to CSRQ;

Alter table sys_user MODIFYCSRQ char (10 );