[数据库]数据库系统概论·课本验证

注:MySQL运行环境。持续更新。by echo

-- DROP DATABASE echo;
-- 新建数据库
CREATE DATABASE echo;


USE echo;


CREATE TABLE Student (
Sno CHAR (9) PRIMARY KEY,
Sname CHAR (20) UNIQUE,
Ssex CHAR (2),
Sage SMALLINT,
Sdept CHAR (20)
);


CREATE TABLE Course (
Cno CHAR (4) PRIMARY KEY,
Cname CHAR (40) NOT NULL,
Cpno CHAR (4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course (Cno)
);


CREATE TABLE SC (
Sno CHAR (9),
Cno CHAR (4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student (Sno),
FOREIGN KEY (Cno) REFERENCES Course (Cno)
);


/* 注意！
sql server中为ALTER TABLE ... ALTER COLUMN 
orcale中为ALTER TABLE ... MODIFY 
*/
-- 修改基本表 
-- RESTRICT 不能有限制（默认） CASCADE 强制删除，相关也删除（级联） 
ALTER TABLE Student ADD COLUMN aa SMALLINT;


ALTER TABLE Student MODIFY COLUMN aa INT;


-- 更改类型
ALTER TABLE Student CHANGE COLUMN aa cc SMALLINT;


-- 更改列名和类型
ALTER TABLE Student DROP COLUMN cc CASCADE;


-- 新增数据
INSERT INTO Student (Sno, Sname, Ssex, Sage, Sdept)
VALUE
(
201215121,
'李勇',
'男',
20,
'CS'
),
(
201215122,
'刘晨',
'女',
19,
'CS'
),
(
201215123,
'王敏',
'女',
18,
'MA'
),
(
201215125,
'张立',
'男',
19,
'IS'
);


INSERT INTO Course (Cno, Cname, Cpno, Ccredit)
VALUE
(1, '数据库', 5, 4),
(2, '数学', NULL, 2),
(3, '信息系统', 1, 4),
(4, '操作系统', 6, 3),
(5, '数据结构', 7, 4),
(6, '数据处理', NULL, 2),
(7, 'PASCAL语言', 6, 4);


INSERT INTO SC (Sno, Cno, Grade)
VALUE
(201215121, 1, 92),
(2015121, 2, 85),
(2015121, 3, 88),
(2015122, 2, 90),
(2015122, 3, 80);


-- 全表查询
SELECT
*
FROM
echo.Student;


SELECT
*
FROM
echo.Course;


SELECT
*
FROM
SC;