「笔记」Oracle Database 期中考笔记

DDL

建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE staff (
PRIMARY KEY (staff_num),
staff_num INTEGER DEFAULT 1 NOT NULL,
first_name VARCHAR2(100) NOT NULL,
leader_num INTEGER NOT NULL,
CONSTRAINT staff_first_name_uq UNIQUE (first_name),

CONSTRAINT staff_leader_num_ck
CHECK(leader_num >= 1 AND leader_num < 100),

CONSTRAINT staff_leader_fk
FOREIGN KEY(first_name)
REFERENCES staff2(first_name)
);

只复制表结构

1
CREATE TABLE staff_temp like staff;

复制表结构及数据

1
CREATE TABLE staff_temp as select * from staff;

删表

1
DROP TABLE staff;

修改表

添加列

1
2
ALTER TABLE staff
ADD new_column_name INTEGER;

删除列

1
2
ALTER TABLE staff
DROP COLUMN drop_column_name;

修改列

1
2
ALTER TABLE staff
MODIFY modify_column_name INTEGER;

添加约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 添加 CHECK 约束
ALTER TABLE staff
ADD CONSTRAINT staff_first_name_ck CHECK (first_name in ('a','b','c'));

-- 添加 UNIQUE 约束
ALTER TABLE staff
ADD CONSTRAINT staff_first_name_uq UNIQUE (first_name);

-- 添加 FOREIGN KEY 约束
ALTER TABLE staff
ADD CONSTRAINT staff_leader_fk
FOREIGN KEY(first_name)
REFERENCES staff2(first_name);

-- 添加 NOT NULL 约束 (使用 MODIFY !!!)
ALTER TABLE staff
MODIFY first_name CONSTRAINT staff_first_name_nn NOT NULL;

ALTER TABLE staff
MODIFY first_name NOT NULL;

删除、禁用、启用约束

1
2
3
4
5
6
7
8
9
10
11
-- 删除
ALTER TABLE staff
DROP CONSTRAINT staff_first_name_nn;

-- 禁用
ALTER TABLE staff
DISABLE CONSTRAINT staff_first_name_nn;

-- 启用
ALTER TABLE staff
ENABLE CONSTRAINT staff_first_name_nn;

清空表

DELETE

1
2
DELETE staff;
COMMIT;

TRUNCATE

1
TRUNCATE TABLE staff;
TRUNCATE DELETE
清空表格 T F
语句类型 DDL DML
自动提交 T F
可回滚 F T
释放储存空间 T F
推荐使用 T F

注释

1
2
3
4
5
COMMENT ON TABLE table_name is '表注释';
COMMENT ON TABLE staff is '员工表';

COMMENT ON COLUMN column_name is '列注释';
COMMENT ON COLUMN staff_num is '员工号';

重命名表

1
RENAME staff TO staff2;

DML

查询

基本顺序

1
2
3
4
5
6
7
SELECT avg(group_column) FROM
table_a INNER JOIN table_b
USING(join_column)
WHERE join_column > 1
GROUP BY group_column
HAVING avg(group_column) > 10
ORDER BY oreder_column DESC;

WITH

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
WITH t AS (
SELECT equipment_name,damage*speed*(1+durability/100) val
FROM equipments
WHERE durability IS NOT NULL AND
attack_id IS NOT null

UNION ALL

SELECT equipment_name,durability*dr_ratio*2
FROM equipments
WHERE durability IS NOT null AND
attack_id IS NULL)

SELECT equipment_name,val
FROM t
WHERE val > (
SELECT AVG(val) FROM t)
ORDER BY val DESC;

IN TABLE

1
2
3
4
5
6
7
SELECT npc_name,attack_type,npc_hp
FROM npcs inner join attack_types USING(attack_id)
WHERE (attack_id,npc_hp) in (
SELECT attack_id,max(npc_hp)
FROM npcs
GROUP BY attack_id
);

连接

1
2
3
4
5
6
7
8
9
10
11
-- 内连接
FROM products INNER JOIN product_types
USING (product_type_id);

-- 外连接
FROM products (LEFT | RIGHT | FULL) OUTER JOIN product_types
USING (product_type_id);

-- 自连接
FROM employees w INNER JOIN employees m
ON w.manager_id = m.employee_id;

杂项

1
2
3
4
5
where equipment_name like '%铁_'

group by cid,cname

nvl(gname,'无')

函数

插入数据

1
2
INSERT INTO staff VALUES(1,'fn',10);
INSERT INTO staff(staff_num,first_name,leader_num) VALUES(1,'fn',10);

删除数据

1
2
DELETE FROM staff
WHERE staff_num = 1;

修改数据

1
2
UPDATE staff SET staff_num = 3, first_name = 'xxx'
WHERE staff_num = 1;

PL/SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
set serveroutput on

declare

cursor c_countries IS
select cname,cid from countries;

v_cid countries.cid%type;
v_city_cnt integer;
v_gener_cnt integer;

begin
for v_c IN c_countries LOOP
v_cid := v_c.cid;

select count(*) into v_city_cnt from cities where cid = v_cid;
select count(*) into v_gener_cnt from generals where cid = v_cid and cityid is not null;
IF gener_cnt > 2 THEN
DBMS_OUTPUT.PUT_LINE(v_c.cname ||'有'||v_city_cnt||'座城市,'||v_gener_cnt||'名现役武将');
END IF;
END LOOP;
end;
/
Donate comment here