SQL语句模板及例题

仅作个人学习使用，资料来源B站

一、创建表

1. 模板

CREATE TABLE 表名(

        列名1 数据类型 列级完整性约束条件,

        列名2 数据类型 列级完整性约束条件,

        表级完整性约束条件1，

        表级完整性约束条件2

);

1.1 数据类型

CHAR(n) 长度为n的字符型

VERCHAR(n) 最大长度为n的变长字符串

NUMBER(n) 长度为n的数字型

INT 长整型（4B）

SMALLINT 短整型（2B）

BIGINT 大整型（8B）

FLOAT(n) 精度至少为n位数字的浮点数

DATE 日期，格式为：YYYY-MM-DD

TIME 时间，格式为：HH:MM:SS

1.2 列级完整性约束

主码：PRIMAY KEY

非空：NOT NULL

唯一：UNIQUE

条件：CHECK

1.3 表级完整性约束

多个主码：PRIMAY KEY（列名1，列名2）

参照完整性：POREIGN KEY（列名1） REFERENCE 被参照表（列名1）

2 例题

2.1 定义院系表

其中编号为主关键字，名称应该唯一、并且不允许为空值

编号

名称

负责人

办公地点

CREATE TABLE Department(
    编号 CAHR(8) PRIMARY KEY,
    名称 CHAR(40) UNIQUE NOT NULL,
    负责人 CHAR(20)，
    办公地点 CHAR(20)
);

2.2 定义学生表

其中规定

• 学号列是主关键字；
• 院系列为计算列（取学号列的第3和第4个字符），并且院系值参照院系表的编号值（院系表是被参照表，主关键字是编号；参照表是学生表，外部关键字是院系），此约束说明一名学生一定属于某个院系； 
• 姓名列不允许为空值； 
• 性别必须取值“男”或“女”； 
• 学生的学籍状态为正常、留级、休学或退学。 

  CREATE TABLE Student(
      学号 CHAR(10) PRIMARY KEY,
      院系 as convert(char(8),substring(学号,3,2))
          persisted not null
          FOREIGN KEY(院系) REFERENCES Department(编号),
      姓名 char(20) NOT NULL,
      性别 char(2) constrain c1 check (性别 in ('男'，'女')),
      状态 char(4) constrain c2 check (状态 in ('正常'，'留级','退学','休学'))
  );
      

  2.3 定义教师表

• 其中规定：

• 教师编号是主关键字； 
• 院系列为计算列（取教师编号的第1和第2个字符），并且院系值参照院系表的编号值，此约束说明一名教师一定属于某个院系； 
• 姓名列不允许为空值； 
• 性别必须取值“男”或“女”； 

• 职称的取值为教授、副教授、讲师或助教
   

  CREATE TABLE Teacher(
      教师编号 CHAR(10) PRIMARY KEY,
      院系 as convert(char(8),substring(教师编号,1,2))
          persisted not null
          FOREIGN KEY(院系) REFERENCES Department(编号),
      姓名 char(20) NOT NULL,
      性别 char(2) constrain c3 check (性别 in ('男'，'女')),
      状态 char(6) constrain c4 check (状态 in ('教授'，'副教授','讲师','助教'))
  );
      

  2.4 定义课程表

• 其中规定：

• 课程编号是主关键字； 
• 课程名称不允许为空值； 
• 责任教师参照教师表的教师编号值，此约束说明责任教师一定是已经在编的教师； 
• 学时不允许为空值； 
• 课程性质分为公共基础、专业基础、专业选修和任意选修。 

  CREATE TABLE Course(
      课程编号 CHAR(10) PRIMARY KEY,
      课程名称 CHAR(8) NOT NULL,
      责任教师 CHAR(10) constrain Duty_teacher foreign key(责任教师) references Teacher(教师编号),
      学时 SMALLINT NOT NULL,
      课程性质 constrain c5 check (课程性质 in ('公共基础','专业基础','专业选修','任意选修'))
      
  );

  2.5 定义选课表

• 学号和课程编号两列构成主关键字（需要表级完整性约束）； 
• 学号参照学生表的学号（约束选课的学生必须存在）； 
• 课程编号参照课程表的课程编号（约束被选的课程必须存在）； 
• 考试成绩取值在0~100之间，并且默认为空值。 

  CREATE TABLE SC(
      学号 CAHR(10),
      课程编号 CAHR(10),
      考试成绩 INT constrain c6 check(考试成绩 between 0 and 100) default null,
      primary key(学号,课程编号),
      foreign key(学号) references Student(学号),
      foreign key(课程编号) references Course(课程编号)
  );

  二、修改表

1 模板

1.1 增加新的属性列

ALTER TABLE 表名 ADD 新列名 数据类型 完整性约束条件；

例1 向SC表中增加时间列，数据类型为日期型

ALTER TABLE SC ADD COLUMN Time DATE;

1.2 增加列级完整性约束条件

ALTER TABLE 表名 ADD 列级完整性约束条件；

例2 向SC表中增加Cname列必须取唯一值的约束条件

ALTER TABLE SC ADD UNIQUE(Cname);

1.3 增加表级完整性约束条件

ALTER TABLE 表名 ADD 表级完整性约束条件；

例3 向SC表中增加Cno为外码，参照表是Student表

ALTER TABLE SC ADD FOREIGN KEY(Cno) REFERENCES Student(Cno);

1.4 删除列

级联：引用了该列的其他对象（例如视图）一起删除

ALTER TABLE 表名 DROP 列名 CASCADE;

限制：若该列被其他对象引用，则拒绝删除

ALTER TABLE 表名 DROP 列名 RESTRICT;

1.5 删除指定的完整性约束条件

ALTER TABLE 表名 DROP CONSTRAINT 完整性约束名 CASCADE;

ALTER TABLE 表名 DROP CONSTRAINT 完整性约束名 RESTRICT;

1.6 修改列

ALTER TABLE 表名 ALTER COLUMN 列名 数据类型;

例4 将SC表中原有的Sage（假设是字符型）修改为整型

ALTER TABLE SC ALTER COLUMN Sage INT;

1.7 删除表

DROP TABLE 表名 CASCADE;

DROP TABLE 表名 RESTRICT;

2 例题

2.1 规定学生表的性别字段不允许为空值

ALTER TABLE Student ADD NOT NULL(性别);

2.2 删除课程表中的责任教师列

alter table Course drop constraint Duty_Teacher
alter table Course drop column 责任教师;

2.3 为课程表增加责任教师列，列描述如下：

• 字符类型，长度为6；
• 允许空值；

  alter table Course add 责任教师 char(6) null
  constraint newdt foreign key references Teacher(教师编号);

三、INSERT/UPDATE/DELETE语句

1 插入数据

1.1 插入元组

INSERT

INTO 表名

VALUES(常量1，常量2);

1.2 插入子查询结果

INSERT

INTO 表名

子查询;

3.2 更新数据

3.3 删除数据