Oracle 相关

概念

docker 安装oracle

Oracle命令

sqlplus  # 连接oracle数据库

表操作

新建

唯一约束 unique 不可重复
非空约束 not null 不能为空必须制定值
主键约束 primary key 通常是给id使用的，同时具备了unique和not null约束
外键约束 foreign key

CREATE TABLE hero(
id number primary key,
name varchar2(30),
hp number,
mp number,
damage number,
armor number,
speed number);

修改表

增加字段

ALTER TABLE hero ADD(test number);

删除字段

ALTER TABLE hero DROP COLUMN test;

修改字段

ALTER TABLE hero MODIFY(test VARCHER2(200));

删除

DROP TABLE hero;

记录操作

插入记录

插入记录需要COMMIT提交

INSERT INTO hero (id, name, hp, mp, damage, armor, speed) VALUES (hero_seq.nextval, 'link', 233, 904, 232, 23, 233);
COMMIT;

自增id

创建序列（SEQUENCE）并指定初始值，在插入记录时传递此序列值
INCREMENT（步长）

CREATE SEQUENCE hero_seq
INCREMENT by 1
START with 1
MAXVALUE 9999999
-------
INSERT INTO hero (id, name, hp, mp, damage, armor, speed) VALUES (hero_seq.nextval, 'link', 233, 904, 232, 23, 233);
COMMIT;

删除记录

DELETE删除记录可以回滚数据
ROLLBACK回滚DELETE的数据
TRUNCATE删除记录不可回滚

DELETE FROM hero WHERE id = 5;
--COMMIT;   -- 提交后不可回滚
SELECT * FROM hero;
ROLLBACK;

TRUNCATE TABLE hero;  -- 不可回滚

更新记录

UPDATE hero SET id = null WHERE id = 99;

字符串连接

字符串连接：||
hr.employees是Oracle自带表

SELECT e.last_name || ' ' || e.first_name as NAME_  FROM hr.employees e

去重复

DISTINCT

SELECT DISTINCT e.department_id FROM hr.employees e

回滚

ROLLBACK

 DELETE FROM hero;
 -- 回滚删除的数据
 ROLLBACK;

设置回滚点

SAVEPOINT ：设置保存点
ROLLBACK TO

 UPDATE hero SET name = 'TEST A' WHERE id = 13;
 
 SAVEPOINT A
  
 UPDATE hero SET name = 'TEST B' WHERE id = 13;
 
 SAVEPOINT B
 
 DELETE FROM hero WHERE id = 13
 
 SAVEPOINT C
 -- 回滚到保存点A之前的数据
 ROLLBACK TO A

条件查询

like

%匹配多个或者没有,_匹配有且只有一个

-- job_id 中以N或者K结尾且必须包含L的字段
SELECT * FROM hr.employees e WHERE (e.job_id like '%N' OR e.job_id like '%K') AND e.job_id like '%L%';

BETWEEN

SELECT * FROM hr.employees e WHERE e.salary BETWEEN 2000 AND 3000

IN

SELECT * FROM hr.employees e WHERE e.Employee_Id IN(116, 130);

分组查询

-- 对员工表按部门分组，且计算部门平均工资
SELECT e, AVG(e.salary) FROM hr.employees e GROUP BY e.department_id

分组条件

HAVING

-- 对员工表按部门分组，且计算部门平均工资,筛选大于5000的部门
SELECT e.department_id, AVG(e.salary) FROM hr.employees e GROUP BY e.department_id HAVING AVG(e.salary) > 5000

分页查询

rownum：当前行号

SELECT * FROM(
      SELECT e1.* ,rownum r FROM (
            SELECT * FROM hr.employees e ORDER BY e.salary DESC ) e1
      ) e2 WHERE r > 5 AND r <= 10;

连接查询

-- 查询员工名称（e.first_name）和部门名称（d.department_name）
SELECT e.first_name, d.department_name FROM hr.employees e LEFT JOIN hr.departments d ON e.department_id = d.department_id

存储过程

创建

 CREATE OR REPLACE PROCEDURE test_pro(cnt OUT number)
 AS
 BEGIN
      SELECT count(*) INTO cnt FROM hero;
 END;

调用

CALL test_pro()