1、创建数据库
CREATE DATABASE db_sc;
2、创建表
CREATE TABLE tb_student(
sno VARCHAR(9) PRIMARY KEY,
sname VARCHAR(20) UNIQUE,
gender VARCHAR(2),
age INT,
dept VARCHAR(20)
);
CREATE TABLE tb_course (
cno VARCHAR (4) PRIMARY KEY,
cname VARCHAR (40) NOT NULL,
cpno VARCHAR (4),
credit INT,
FOREIGN KEY (cpno) REFERENCES tb_course(cno)
);
创建表时添加主键和外键约束
CREATE TABLE tb_sc(
sno VARCHAR(9),
cno VARCHAR(4),
grade INT,
PRIMARY KEY(sno, cno),
FOREIGN KEY (sno) REFERENCES tb_student(sno),
FOREIGN KEY (cno) REFERENCES tb_course(cno)
);
3、插入数据
# 插入方式1(不指定列,默认按属性列的创建顺序插入全部属性)
INSERT INTO tb_student VALUES('stu001', '张三','男',18,'信科院');
# 插入方式2 (指定插入某些列)
INSERT INTO tb_student(sno, sname, gender, age) VALUES('stu001', '张三','男',18);
# 插入方式3 (同时插入多条数据)
INSERT INTO tb_courseVALUES('c001','数据结构',NULL, 4),('c002','数据库','c001', 3);
4、修改表
4.1 给指定表新增一列
ALTER TABLE tb_student ADD birthday date;
4.2 删除指定表的某一列
ALTER TABLE tb_student DROP birthday;
4.3 给某个表的某一列添加约束
ALTER TABLE tb_student ADD CONSTRAINT uk_sno UNIQUE(sno);
4.4 删除某个约束
5 创建视图
# 创建索引,查询信科院的学生
CREATE VIEW vw_XKY_STUDENT
AS
SELECT sno, sname, gender
FROM tb_student
WHERE dept = '信科院'
SELECT * FROM vw_XKY_STUDENT
6 创建索引
CREATE INDEX idx_sno_sname ON tb_student(sno, sname);