1. Definition, deletion and modification of basic tables
(1) Defining a basic table
CREATE table < table name > (< column name >< data type >[column-level integrity constraints)
[,< Column name >< data type >[column-level integrity constraints]]
......
[,< Table-level integrity constraints;])
Such as: Create a "student" table student
CREATE TABLE Student
(Sno int primary KEY auto_increment,/* column-level integrity constraints, Sno is the main code, integer, Auto-increment */
Sname varchar (a) unique,/*sname take a unique value */
Ssex varchar (5)
)
(2) Modify the basic table
ALTER TABLE < table name >
[Add < new column name >< data type >[integrity constraint]]
[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 base table
When a base table is no longer needed, you can use the DROP TABLE statement to delete it
drop table < table name >[restrict| CASCADE]
If you select restrict: the deletion of the table is restricted, the underlying table to be deleted cannot be referenced by other table constraints (such as constraints such as Check,foreign key), cannot have views, cannot have triggers, and cannot have stored procedures or functions. This table cannot be deleted if there are objects of that table since those tables.
If you choose Cascade: Then the deletion of the table is restricted, and when the base table is deleted, objects, such as views, will be deleted together.
2, the establishment and deletion of the index
The index of resumes is an effective means to speed up the query. Based on the needs of the application environment, the user can resume one or more indexes on the basic table to provide multiple access paths and speed up the search.
In general, the creation and deletion of indexes have the DBA of the database administrator or the owner of the table, that is, the person who created the table, is responsible for completion.
(1) Building an index
Create [Unique][cluster] Index < index name >
On < table name > (< column name >[< order >][,< column name >][< order;)
An index can be built on one or more columns of a table, each < column name > can be followed by < order > make an index worthy of order, selectable ASC (Ascending) and Desc (descending), and the default value ASC
Unique indicates that each index value for this index only corresponds to a unique data record.
Cluster indicates that the index to be established is a clustered index. The so-called clustered index refers to an index organization in which the order of the index items is consistent with the physical order of the records in the table.
You can create up to one clustered index on a base 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 a table or an expression */
From < table name or view name >[,< table name or view name;] ...
[where < conditional expressions;]
[GROUP by < column name 1> [having < conditional expression;]/* Group the query results by the value of the < column name 1>, which is a tuple of equal values for the attribute column */
Database base table Operations-Create tables, indexes, and queries
