建表约束

第7章 建表约束

1. 创建表 emp1，字段如下：

CREATE TABLE e1 (
    eno CHAR(3),
    ename CHAR(6),
    sex CHAR(3),
    age NUMBER(2),
    deptno INT
);

使用 SQL 语句插入如下数据，要求编号使用 SEQUENCE 实现：

INSERT INTO e1 VALUES (1, 'TOM', '男', 21, 10);
INSERT INTO e1 VALUES (2, 'JERRY', '男', 21, 20);
INSERT INTO e1 VALUES (3, 'KATE', '女', 21, 30);
INSERT INTO e1 VALUES (4, 'MARY', '女', 21, 40);
INSERT INTO e1 VALUES (5, 'JACK', '男', 21, 50);

(1) 在 eno 字段上创建主键约束。

ALTER TABLE e1 ADD PRIMARY KEY (eno);

(2) 在 ename 字段上创建非空约束。

ALTER TABLE e1 MODIFY (ename CHAR(6) NOT NULL);

(3) 创建检查约束，判断 age 在 18 到 60 岁之间的男性，或者 age 在 18 到 55 岁之间的女性。

ALTER TABLE e1 ADD CHECK (
    (age BETWEEN 18 AND 60 AND sex = '男') OR
    (age BETWEEN 18 AND 55 AND sex = '女')
);

(4) 在 deptno 字段上创建唯一性约束。

ALTER TABLE e1 MODIFY (deptno INT UNIQUE);

(5) 创建和 emp1 表字段相同的 emp_bak 表，将 emp_bak 表的 deptno 字段与 dept 表的 deptno 字段创建外键约束。

-- 创建 emp_bak 表
CREATE TABLE e1_bak AS
SELECT * FROM e1 WHERE 1 != 1;

-- 添加主键约束
ALTER TABLE e1_bak ADD PRIMARY KEY (deptno);

-- 确保 dept 表有主键
ALTER TABLE dept ADD PRIMARY KEY (deptno);

-- 添加外键约束
ALTER TABLE e1_bak ADD FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

-- 插入测试数据
INSERT INTO e1_bak VALUES (1, 'xiao', '男', 17, 10);

(6) 删除 JACK 员工。

DELETE FROM e1 WHERE ename = 'JACK';

(7) 把 MARY 的年龄修改为 23。

UPDATE e1 SET age = 23 WHERE ename = 'MARY';

2. 请创建一个表，表名为 phone，表结构如下：

CREATE TABLE phone (
    phonenum VARCHAR2(8),
    pay NUMBER(8, 2),
    numlevel VARCHAR2(4),
    paydate VARCHAR2(12)
);

(1) 插入以下两条数据：

INSERT INTO phone VALUES ('123456', 600, 'pt04', '20051220');
INSERT INTO phone VALUES ('888888', 900, 'pt05', '20051019');

(2) 创建一个备份表结构名为 phone_bak，将 phone 中的数据插入 phone_bak 中。

CREATE TABLE phone_bak AS
SELECT * FROM phone;

(3) 将电话号码为 123456 的电话费改为 1000，执行回滚操作。

-- 更新数据
UPDATE phone SET pay = 1000 WHERE phonenum = '123456';

-- 回滚操作
ROLLBACK;

(4) 查询 phone 表中所有字段和所有数据，如果 PHONENUM 字段中的数据为 ‘123456’，则返回 ‘Y’，否则返回 ‘N’。

SELECT phonenum, pay, numlevel, paydate,
       DECODE(phonenum, '123456', 'Y', 'N') AS flag
FROM phone;

(5) 查询 phone 表中所有字段和所有数据，显示费用日期 (PAYDATE) 字段的日期转变成 YYYY/MM/DD 格式显示。

SELECT phonenum, pay, numlevel,
       TO_CHAR(TO_DATE(paydate, 'YYYYMMDD'), 'YYYY/MM/DD') AS formatted_paydate
FROM phone;

(6) 将电话号码为 888888 的号码等级改为 pt04 并提交。

UPDATE phone SET numlevel = 'pt04' WHERE phonenum = '888888';
COMMIT;