Data definition of the database

The data definition features in SQL include schema definition, table definition, view definition, and index definition. Here is a summary of the above operations one by one:

1, the definition and deletion of the mode

"Mode" is a database namespace, a framework, for example: in Java, creating a schema is equivalent to creating a package, and creating a table is equivalent to creating a class, a class is managed under a package. In MySQL, the creation schema is the same as creating a database, that is, the CREATE schema < schema name > is equivalent to create databases < database name >. So the definition of the pattern and the deletion statement are as follows:

Create Schema < schema name > authorization < user name; for example, creating a student-course pattern S-T

The CREATE SCHEMA "S-T" Authorization wang;//creates a schema for user Wang S-t

The Delete mode statement is as follows:

Drop Schema < schema name > <cascade|restrict>

One of the cascade and restrict must be selected.

Cascade (Cascade), which indicates that all database objects in the schema (such as tables, etc.) are also deleted while deleting the schema.

Restrict, which indicates that if a subordinate's database object (such as a table, view, and so on) is already defined in the schema, the execution of the DELETE statement is denied.

2, the basic table definition, deletion and modification

The basic format of the table definition is as follows:

Create table< table name > (

< column names > < data types > [column-Level integrity constraints],

< column names > < data types > [column-Level integrity constraints],

< column names > < data types > [column-Level integrity constraints]

）;

For example, create a student's choice of timetable:

CREATE TABLE SC (

Sno Char (9),

Cno char (4),

Sname char (unique,//) to take a unique value

Primary KEY (SNO,CNO),//main code consists of two attributes and must be defined as table-level integrity

Foreign KEY (Sno) references Student (Sno),//table-level integrity constraint, Sno is outer code, referenced table is Student

Foreign KEY (Cno) references Course (Cno)//table-level integrity constraint, Cno is outer code, referenced table is Coruse

);

The definition of deleting a base table is as follows:

Drop table< Table name >[restrict|cascade];

If you select restrict, the deletion of the table is restricted. The base table to be deleted cannot be referenced by other constraints (such as constraints such as Check,foreign key), cannot have views, cannot have triggers (trigger), cannot have stored procedures or functions, and so on.

If it is cascade, the deletion of the table has no restrictions. When you delete a table, related dependent objects, such as views, are also deleted.

The general format for modifying the base table is as follows:

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 "admission time" to the student table:

ALTER TABLE Student add S_entrance Date;

Change the age data type from character type to integer type

ALTER TABLE Student ALTER column Sage int;

To increase the constraint that a course name must take a unique value

ALTER TABLE Course add unique (Cname);

3, the establishment and deletion of the index

Indexing is done to speed up queries, and you can set up one or more indexes on the base table as needed to provide multiple access paths for faster lookups.

The indexed statement is defined as follows:

Create [Unique][cluster] Index < index name > on < table name > (< listing >[< Order >][,< listing >[< Order;]);

The order is the sort order for the specified index value, either ASC (Ascending) or desc (descending) and the default is 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. A clustered index is an indexed organization in which the order of the index items is consistent with the physical order of the records in the table. For example:

Create cluster index stuname on Student (Sname);

The statement will establish a clustered index on the sname column in the student table, and the user can establish a clustered index on the most frequently queried column to improve query efficiency. Only one clustered index can be created on a table. If you update the data on a clustered index column, it often causes the physical order of the records in the table to change and is expensive, so it is not appropriate to establish a clustered index for the frequently updated columns.

For example, an index is created on the SC table, which is unique and descending by number ascending and course number, as follows:

Create unique index scno on SC (Sno ASC, Cno DESC);

The maintenance of the index is done by the system without user intervention. The index is to reduce the time of the query operation, but if the data deletion and modification of the operation is more frequent, the system will spend a lot of time to maintain it, thereby reducing query efficiency, you can delete some unnecessary indexes, delete the following statements:

Drop INDEX < index name > on < table name > or ALTER TABLE < table name > DROP INDEX < index name >

How to use the specific index, you can refer to the site: http://www.jb51.net/Special/621.htm

In RDBMS, indexes are generally implemented by B + trees and hash indexes. B + trees have the advantage of dynamic balance. Hash has the advantage of finding fast. The implementation of the index is based on B + tree or hash index, which is determined by the specific RDBMS.

Data definition of the database