Data definition of relational database standard language SQL

Insert picture description here

1. Schema (schema)

A mode is equivalent to a space, and defining a mode is equivalent to opening up a space in which tables, views, indexes, etc. can be defined.

1.1 Create

1. Grammar

create schema <模式名> authorization <用户名>
  • Mode name default == user name

2. Examples

Define a student-course model for user wang:

create schema 'S-T' authorization wang;

Create a table sc in the student-course model:

create table S_T.sc(
	Sno char(10),
    Cno char(10),
    Grade float,
    primary key(Sno,Cno)
)

1.2 Delete

1. Grammar

drop schema <模式名> <cascade|restrict>
  • cascade: cascade, delete this mode at the same time, delete all the databases in the mode
  • restrict: Restrict, if there are content (tables, views, etc.) in this mode, then refuse to delete

2. Examples

Delete ST mode in cascade:

drop schema S-T cascade

Try to delete ST mode under restricted conditions:

drop schema S-T restrict
Refuse to delete because the sc table exists

1.3 Modification

The SQL standard does not provide a statement to modify the mode. If you want to modify the mode, you can only rebuild it.

2. Basic table (table)

2.1 Create

1. Grammar

create table <表名>(
	<列名> <数据类型> [列级完整性约束条件],
	<列名> <数据类型> [列级完整性约束条件],
	……,
	[表级完整性约束条件]
)

2. Examples

Create a student table and require Sno as the master code:

create table student(
    Sno char(10) primary key,
    Sname varchar(20),
    Ssex char(2),
    Sage smallint,
    Sdept varchar(30)
)

Create a course table, requiring cno as the main code, Cpno as the external code of this table, the referenced table is the current table, and the referenced column is cno.

create table course(
    Cno char(10) primary key,
    Cname varchar(20),
    Cpno char(10),
    Ccredit smallint,
    foreign key(Cpno) references course(Cno)
)
The reference table and the referenced table can be the same table

Create a sc table, sno and cno are the main codes, refer to the student and course tables

create table sc(
    Sno char(10),
    Cno char(10),
    Grade float,
    primary key(Sno,Cno),
    foreign key(Sno) references student(Sno),
    foreign key (Cno) references course(Cno)
)

2.2 Delete

1. Grammar

drop table <表名> [cascade|restrict]
  • cascade: cascade delete. When the table is deleted, its related dependencies will be deleted.
  • restrict: (default) If the table has dependencies, refuse to delete it.

2. Examples

Delete the student table:

drop table student
消息 3726,级别 16,状态 1,第 56 行
Could not drop object 'student' because it is referenced by a FOREIGN KEY constraint.
(不能够删除表student,因为存在外键约束引用)

2.3 Modification

1. Grammar

-- 添加列
alter table <表名>
	add [column] <新列名> <数据类型> [列级完整性约束]
-- 添加表级完整性约束
alter table <表名>
	add <表级完整性约束>
-- 删除列
alter table <表名>
	drop [column] <列名> [cascade|restrict]
-- 删除完整性约束
alter table <表名>
	drop constraint <完整性约束> [cascade|restrict]
-- 更新列的数据类型
alter table <表名>
	alter column <列名> <新数据类型>

2. Examples

Add a Sentrance column to the student table, the data type is int type.

alter table student
	add Sentrance int

Modify the type of enrollment time for the student table to float

alter table student
	alter column Sentrance float

Delete the enrollment time column of the student table

alter table student
	drop column Sentrance

3. View

3.1 Create

create view <视图名>[列名,列名,……]
	as <子查询>
	[with check option]

3.2 Delete

drop view <视图名>

4. Index

4.1 Create

1. Grammar

create [unique][cluster] index <索引名> 
	on <表名>(<列名>[次序],<列名>[次序]……)

2. Examples

Create an index for the student table. The student table establishes a unique index in ascending order of student number.

create unique index stusno 
	on student(sno)

4.2 Delete

1. Grammar

drop index <索引名>

2. Examples

Delete the index of the student table

drop index stusno

4.3 Modify Index

1. Grammar

alter index <旧索引名>rename to <新索引名>

2. Examples

Modify the index of the student table named new_sno

alter index stusno rename as new_sno