1. Basic table definition, deletion, and modification (1) Definition basic table createtable 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 studentcreatetablestudent (Snointprimarykeyauto_in
1. Definition, deletion, and modification of Basic Tables (1) definition of the create 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 create table student (Sno int primary key auto_in
1. Define, delete, and modify basic tables
(1) define basic tables
Create table <表名> ( <列名> <数据类型> [Constraints on column-level integrity]
[, <列名> <数据类型> [Constraints on column-level integrity]
......
[, <表级完整性约束条件> ])
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 <表名>
[Add <新列名> <数据类型> [Integrity constraints]
[Drop <完整性约束名> ]
[Alter column <列名> <数据类型> ]
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 <表名> [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 <索引名>
On <表名> ( <列名> [ <次序> ] [, <列名> ] [ <次序> ]…)
An index can be created on one or more columns of a table. <列名> You can use <次序> Specify the order of index values. Options include 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 <索引名>
When you delete an index, the system also deletes the description of the index from the data dictionary.
3. Data Query
Select [all | distinct] <目标列表达式> [, <目标列表达式> ]… /* The target column expression can be either an attribute column in the table or an expression */
From <表名或视图名> [, <表名或视图名> ]…
[Where <条件表达式> ]
[Group <列名1> [Having <条件表达式> ] /* Press <列名1> To group the values of the attribute column. The tuples with the same values are one */
