数据库回顾 二

定义表

建立学生表Student

create table Student (Sno text primary key, Sname text unique, Ssex text, Sage integer, Sdept text);

CREATE TABLE IF NOT EXISTS Student (
Sno text PRIMARY KEY,
Sname text UNIQUE,
Ssex text,
Sage integer,
Sdept text
);

建立课程表Course

create table Course (Cno text primary key, Cname text, Cpno text, Ccredit integer, foreign key (Cpno) references Course(Cno));

CREATE TABLE IF NOT EXISTS Course (
Cno text PRIMARY KEY,
Cname text,
Cpno text,
Ccredit integer,
FOREIGN KEY (Cpno) REFERENCES Course (Cno)
);

建立学生选课表SC

create table SC (Sno text, Cno text, Grade integer, primary key (Sno, Cno), foreign key (Sno) references Student(Sno), foreign key(Cno) references Course(Cno));

CREATE TABLE IF NOT EXISTS SC (
Sno text,
Cno text,
Grade integer,
FOREIGN KEY (Sno) REFERENCES Student (Sno),
FOREIGN KEY (Cno) REFERENCES Course (Cno),
PRIMARY KEY(Sno, Cno)
);

修改表

增加一个字段

alter table student add Sentrance date

改字段数据类型

alter table student alter column Sage integer

增加字段约束条件

alter table course add unique(Cname)

删除表

drop table student restrict;
drop table student cascade;
说明
restrict 有限制条件删除 比如说有约束索引
cascade 无限制条件删除 同时会删除相关依赖