Database Learning (5) -- create and update data tables 2

Database Learning (5) -- create and update a data table 2 use indexes in practical applications. Appropriate indexes are created for the data table to accelerate query efficiency. An index is a pointer to data in a data table. By creating an appropriate index for a data table, you can increase the access speed of SQL statements to the data table. Index classification indexes can be divided into unique indexes, primary indexes, single-column indexes, composite indexes, and clustered indexes. The following describes these indexes respectively. UNIQUE index: You can use the UNIQUE keyword in a data table to define a UNIQUE index for a data column. Each index value in a unique index only corresponds to one record in the data table. It ensures the uniqueness of the record in the data column. If a record is inserted into a data table, the database checks the record to be inserted. If the value of this record appears in the column that defines a unique index, the database management system will not insert this record into the data table. Generally, after creating a unique constraint for a column, the database creates a unique index for the column. The index name is the same as the constraint name. PRIMARY Index: You can use the primary key keyword in a data table to define a PRIMARY index for a data column. The primary index is the index created in the defined primary key column. The primary index also ensures the uniqueness of records in the data column. Generally, after a primary key constraint is created for a column, the database creates a primary index for the column. The index name is the same as the constraint name. Single Column index: The index defined in a data column in a data table is a single column index. In data query, if a column in a data table is often used as a query condition in the WHERE clause, you can create a single column index for the column to improve the query efficiency. Composite Index: an index can be defined on multiple data columns of a data table. An index like this is called composite index. Generally, when querying data, if the WHERE clause often uses several columns in the data table as the query condition, you can create a composite index for these columns to improve the query efficiency. Clustered index: To improve the query efficiency of SQL statements on data tables, you can create a clustered index for data tables. The order of index items in the clustered index is consistent with the physical order of data records in the data table. You can create only one clustered index in each data table. The clustering index order is the physical storage order of the data. The non-clustering index interpretation is that the index order is irrelevant to the physical sorting order of the data. Because of this, a table can have at most one clustered index. Create and delete indexes in a data table. Generally, creating and deleting indexes is done by the database administrator or creator of the data table. To CREATE an INDEX, use the create index statement. Syntax for creating an INDEX: CREATE [UNIQUE] | [CLUSTER] INDEX name ON table name (column name [sorting method]…) The sorting method specifies whether the index is sorted in ascending or descending order. The keyword ASC indicates ascending, And the keyword DESC indicates descending order. The default value is ascending. The syntax for deleting an INDEX is as follows: drop index name ON table name modify the table in the database. After creating a data table, you need to modify the structure of the data table, for example, adding a column to a data table, deleting a column, adding constraints or indexes to a column in the data table, and modifying the Data Type of a column in the data table, in this case, you need to use the alter table statement. Add a column to the table in actual application. In some cases, you need to add a specified column to the data table to improve the data table information. The syntax format of adding a column to a data TABLE is as follows: alter table table_name ADD (column_name datatype [constraint_condition]) Where alter table is the keyword for modifying the TABLE structure; the name of the table_name TABLE followed by the keyword alter table; ADD is the keyword used to specify the columns to be added to the data TABLE; you need to specify the new column name, the Data Type and integrity constraints corresponding to the column in the brackets after the keyword ADD. Column_name indicates the new column name. ype is used to specify the Data Type of the column. constraint_condition is used to indicate the integrity constraints of the column. It is optional. Add a constraint. If no constraint is defined for the specified column when a data table is created, after the data table is created, you can use the ADD clause in the alter table statement to ADD a constraint to a column. The syntax format is as follows: alter table table_name ADD constraint_type (column_name) Where alter table is the keyword for modifying the TABLE structure, the name of the table_name TABLE followed by the keyword alter table, and ADD is the keyword, used to specify the columns to be added to the data table. Constraints and newly added column names must be specified in brackets after the keyword ADD. Constraint_type indicates the specified constraint, and column_name1 indicates the new column name. If an index is not defined for a specified column when a data TABLE is created, you can ADD an index to the column by using the ADD clause in the alter table statement after the TABLE is created. Syntax format: alter table table_name add index (column_name1 [, column_name2]...) Here, alter table is the keyword used to modify the TABLE structure; the name of the table_name TABLE followed by the keyword alter table; ADD is the keyword used to specify the columns to be added to the data TABLE; the INDEX keyword indicates that you want to add an INDEX to the data table. Column_name1 indicates the column specified for adding an index. The specified column can be one or multiple columns. MODIFY a column in a TABLE in practical use. Sometimes you need to MODIFY the data type and default value of a column in the data TABLE. In this case, you need to use the MODIFY clause in alter table. The syntax format is as follows: alter table table_name MODIFY column_name datatype where alter table is the keyword for modifying the TABLE structure; the name of the table_name TABLE followed by the keyword alter table; MODIFY is the keyword, it is used to MODIFY a column in a data table. After the keyword MODIFY, you must specify the name of the column to be modified and the corresponding data type of the column. Column_name indicates the column name, And ype indicates the column data type. Delete a column in a TABLE. If you do not need to use a column in the data TABLE, you can use the DROP clause in the create table statement to delete the column. The syntax format for deletion is as follows: alter table table_name DROP column_name where alter table is the keyword for modifying the TABLE structure; the name of the table_name TABLE followed by the keyword alter table; DROP is the keyword, used to delete a column in a data table. You must specify the name of the column to be deleted after the keyword DROP. Delete a constraint. If you delete the constraint of a column in a data TABLE, you can use the DROP clause in the create table statement. The syntax format for deleting constraints is as follows: alter table table_name DROP constraint_type, where alter table is the keyword for modifying the TABLE structure, the name of the table_name TABLE followed by the keyword alter table, and DROP is the keyword, it is used to delete the constraints of columns in the data table. You must specify the name of the constraint to delete after the keyword DROP. constraint_type indicates the constraint to delete. To delete a TABLE in a database, you can use the drop table statement to delete a TABLE that is no longer needed. The syntax format is as follows: drop table table_name [cascade constraints] When you use drop table to delete a data TABLE, the records in the TABLE and the corresponding views created from the TABLE will be deleted at the same time. If there is a foreign key constraint between data tables, you must use cascade constraints cascade to delete the foreign key constraint from the table When deleting the primary table. It is optional. To delete a data table, you must have the permission to delete the data table.