Basic Database Table operations-create tables, indexes and queries, and database Indexes

Basic Database Table operations-create tables, indexes and queries, and database Indexes
1. Define, delete, and modify basic tables
(1) define basic tables
Create table <table Name> (<column Name> <data type> [column-level integrity constraints]
[, <Column Name> <data type> [column-level integrity constraints]
......
[, <Table-level integrity constraints>])
For example, create a student table
Create table student
(Sno int primary key auto_increment,/* column-level integrity constraints. Sno is the primary code, integer, and automatically added */
Sname varchar (20) unique,/* obtain the unique value of Sname */
Ssex varchar (5)
)
(2) modify the basic table
Alter table <table Name>
[Add <new column Name> <data type> [integrity constraints]
[Drop <Integrity Constraint Name>]
[Alter column <column Name> <data type>]
For example, add the "admission time" column to the student table.
Alter table student add Sentrane date;
(3) Delete A basic table
When a basic table is no longer needed, you can use the drop table statement to delete it.
Drop table <table Name> [RESTRICT | CASCADE]
If restrict is selected, the table has restrictions when it is deleted. The basic table to be deleted cannot be referenced by other table constraints (such as check and foreign key constraints ), there cannot be a view, a trigger, or a stored procedure or function. If the table objects exist, the table cannot be deleted.
If cascade is selected, the table has a limit on the number of meters to be deleted. When deleting a basic table, all related objects, such as views, will be deleted together.
2. index creation and Deletion
Resume indexing is an effective way to speed up queries. Based on the needs of the application environment, you can resume one or more indexes on the basic table to provide multiple access paths and speed up searching.
Generally, the database administrator DBA or table owner is responsible for creating and deleting indexes.
(1) Create an index
Create [unique] [cluster] index <index Name>
On <Table Name> (<column Name> [<sequence>] [, <column Name>] [<sequence>]…)
An index can be created on one or more columns of a table. Each <column Name> column can be followed by <order> to sort the indexes in ascending order. Optional. ASC (ascending) and DESC (descending). The default value is ASC.
Unique indicates that each index value of this index only corresponds to a unique data record.
Cluster indicates that the index to be created is a clustered index. The so-called clustered index refers to the index organization in which the order of index items is consistent with the physical order recorded in the table.
You can create up to one clustered index on a basic table.
(2) Deleting an index
Drop index <index Name>
When you delete an index, the system also deletes the description of the index from the data dictionary.
3. Data Query
Select [all | distinct] <target column expression> [, <target column expression>]…/* The target column expression can be either an attribute column in the table or an expression */
From <Table name or view Name> [, <Table name or view Name>]…
[Where <conditional expression>]
[Group by <column name 1> [having <conditional expression>]/* Query results are grouped by the value of <column name 1>. The tuples with the same attribute column values are a single tuple */