数据库操作-1

1、SQL模式的定义与删除:

1
CREATE SCHEMA Teaching_DB;
1
DROP SCHEMA Teaching_DB;

2、表的建立

1
2
3
4
5
6
7
CREATE TABLE Student(
	sno char(5) not null UNIQUE,
	sname char(8) not null,
	sex char(2),
	age int,
	dept char(20)
)
1
2
3
4
5
6
7
8
9
CREATE TABLE Student_2(
	sno char(5),
	sname char(8) not null,
	sex char(2),
	age SMALLINT,
	dept char(20),
	PRIMARY KEY(sno),
	CHECK (sex IN ('男','女'))
);
1
2
3
4
5
6
7
CREATE TABLE Student_3(
	sno char(5) PRIMARY KEY,
	sname char(8) not null,
	sex char(2) CHECK(sex in ('男','女')),
	age SMALLINT,
	dept char(20)
);
1
2
3
4
5
6
7
CREATE TABLE Course(
	cno char(4) PRIMARY KEY,
	cname char(10) not null,
	pcno char(4),
	credit SMALLINT,
	FOREIGN KEY(pcno) REFERENCES Course(cno)
)
1
2
3
4
5
6
7
8
9
CREATE TABLE SC(
	sno char(5), 
	cno char(4),
  grade int,
	PRIMARY KEY(sno, cno),
	FOREIGN KEY(sno) REFERENCES Student(sno),
	FOREIGN KEY(cno) REFERENCES Course(cno),
	CHECK ((grade is null) or (grade BETWEEN 0 and 100))
)

3、表的删除、修改

1
2
3
-- 删除表的某列
ALTER TABLE Student
DROP age
1
2
3
-- 修改某列的类型
ALTER TABLE Student
MODIFY sname char(100)
1
2
ALTER TABLE Student
DROP PREPARE KEY
1
2
ALTER TABLE Student
ADD PREPARE KEY (sno)
1
ALTER TABLE Student RENAME TO stu;
1
2
3
4
5
6
7
8
9
10
ALTER TABLE stu
CHANGE COLUMN sex ssex int

-- 不管用
ALTER TABLE stu
RENAME COLUMN ssex to ss


-- alter table stu drop constraint pk_student;
alter table stu add constraint pk_student primary key(sno);

4、插入数据

1
2
INSERT INTO Student_3(sno,sname)
VALUES ('1','1')
数据库操作-1
https://zhangfuli.github.io/2020/10/24/数据库操作-1/
作者
张富利
发布于
2020年10月24日
许可协议