用户管理

创建用户

create user 用户名 identified by 密码;

修改用户密码

alter user 用户名 identified by 密码;

删除用户

drop user 用户名;

-- 关键字cascade 
-- 可删除用户所有的对象，然后在删除用户
drop user 用户名 cascade;

用户权限

-- 1、连接权限
-- connect 临时用户，赋给不需要建表的用户
-- 2、资源权限
-- resource 提供给用户另外的权限以创建他们自己的表、序列、过程、触发器、索引和簇
-- 3、数据库管理员权限
-- dba 无限制的空间限额和给其他用户授予各种权限的能力

授权

grant 权限 to 用户名;

撤权

revoke 权限 from 用户名;

角色

创建角色

create role 角色名;

授权角色

grant 增删改查权限 on 表名 to 角色名;

删除角色

drop role 角色名;

表管理

创建表

CREATE TABLE 表名(
   字段名 字段类型 字段约束
)
-- 例子，创建一个用户表，并给用户表字段添加属性
CREATE TABLE users(
   userid NUMBER(8) PRIMARY KEY,
   username VARCHAR2(24) NOT NULL,
   usersex VARCHART2(4)
)
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.userid IS '用户编号';
COMMENT ON COLUMN users.username IS '用户姓名';
COMMENT ON COLUMN users.usersex IS '用户性别';

表字段操作

-- 例子，基于上面的例子的用户表
-- 给表增加一个字段
ALTER TABLE users ADD(userphones VARCHAR2(24));
-- 给表字段修改名字
ALTER TABLE users RENAME COLUMN userphones TO userphone;
-- 给表字段修改类别
ALTER TABLE users MODIFY userphone VARCHAR2(48);
-- 删除一列表字段
ALTER TABLE users DROP COLUMN userphone;

表数据操作

-- 插入一条数据
INSERT INTO users VALUES(1,'王五','男');
-- 更新一条数据
UPDATE users SET username='李四' WHERE userid = 1;
-- 查询一条数据
SELECT * FROM users;
-- 删除一条数据
DELETE FROM users WHERE userid=1;

视图

CREATE OR REPLACE VIEW 视图名
AS
一条select语句

序列

CREATE SEQUENCE 序列名
 minvalue 1  --最小值
 nomaxvalue  --不设置最大值(由机器决定)，或 根据表字段的值范围设置 maxvalue
 maxvalue 999  -- 最大值
 start with 1   --从1开始计数，数值可变
 increment by 1  --每次加1，数值可变
 nocycle  --一直累加，不循环；cycle：达到最大值后，将从头开始累加
 nocache；  --不建缓冲区。 如果建立cache那么系统将自动读取cache值个seq，这样会加快运行速度；如果在单机中使用cache，或者oracle死了，那么下次读取的seq值将不连贯，所以不建议使用cache。
 
-- 例子
drop table book;   
--创建表 
create table book(       
   bookId varchar2(4) primary key,   
   name varchar2(20)         
);   
--创建序列 
create sequence book_seq start with 1 increment by 1;    
  
--创建触发器 
create or replace trigger book_trigger       
before insert on book       
for each row       
begin       
select book_seq.nextval into :new.bookId from dual;      
end ;   
--添加数据 
insert into book(name)  values ('cc');    
insert into book(name)  values ('dd');   
  
commit;  

函数

-- 创建一个函数
CREATE OR REPLACE FUNCTION 函数名
(参数名 参数类型，参数名 参数类型)
RETURN 返回参数类型
IS
参数名 参数类型;
BEGIN
	PL/SQL程序块
	RETURN 返回参数名;
EXCEPTION
	WHEN no_data_found THEN
		dbms_output.put_line('data not found');
END;		

-- 调用函数
SELECT 函数名(参数) FROM dual;

-- 例子
CREATE OR REPLACE FUNCTION queryUsers(
  uid NUMBER
)
RETURN NUMBER
IS
v_sum NUMBER;
BEGIN
  SELECT count(*) INTO v_sum from users WHERE userid = uid;
  return v_sum;
EXCEPTION
  when no_data_found then
    dbms_output.put_line('data not found');
end;

SELECT queryUsers(1) from dual;

存储过程

-- 创建存储过程
CREATE OR REPLACE PROCEDURE 过程名(
	参数名 IN 参数类型,  -- 输入参数
    参数名 OUT 参数类型  -- 输出参数
)
IS 
 cot number:=0; --变量声明
BEGIN
	PL/SQL语句;
	num:=cot; --赋值语句
END;

-- 调用存储过程
-- 方法一
BEGIN
	过程名(参数,...);
END;
-- 方法二
var 变量名 number;
EXEC 过程名(参数,...);
PRINT 变量名;

-- 例子
CREATE OR REPLACE PROCEDURE queryUserBySex(
sex IN VARCHAR2,
num OUT NUMBER
)
IS
  cot number:=0;
BEGIN
  SELECT COUNT(*) INTO cot FROM users WHERE usersex = 'nan';
  num:=cot;
END;

DECLARE
retureCount NUMBER;
BEGIN
  queryUserBySex('nan',retureCount);
  DBMS_OUTPUT.PUT_LINE('性别nan的学生人数为'||retureCount);
END;

触发器

CREATE OR REPLACE TRIGGER 触发器名
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名
FOR EACH ROW
-- DECLARE 设计要对表进行增删改时，需要加上
	-- PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	PL/SQL语句;
	-- COMMIT;
END;

-- 例子：
CREATE OR REPLACE TRIGGER in_del_trigger
AFTER DELETE
ON users
FOR EACH ROW
declare
  pragma autonomous_transaction;
BEGIN
  dbms_output.put_line(:old.userid);
  INSERT INTO copyuser VALUES(:old.userid,'li','nan');
  COMMIT;
END;

索引

创建索引

CREATE OR REPLACE 索引名 ON 表名(字段名)

单一索引

-- 创建索引
CREATE INDEX uname ON USERS(username);
-- 使用索引
select * from users where username='hong';

复合索引

CREATE INDEX uname ON USERS(userid,username);
select * from users order by userid,username;

基于函数的索引

CREATE INDEX uname ON USERS(UPPER(username));
select * from users where upper(username) = 'HONG';

如果经常再索引列上执行DML操作，需要定期重建索引

ALTER INDEX 索引名 REBUILD

游标

DECLARE 
  CURSOR cur_user IS SELECT * FROM USERS;  -- 声明游标
  v_user cur_user%ROWTYPE;
BEGIN
  OPEN cur_user; -- 打开游标
  
  LOOP
    FETCH cur_user INTO v_user;  -- 提取数据
    EXIT WHEN cur_user%NOTFOUND;
    
    IF cur_user%FOUND THEN
      dbms_output.put_line(v_user.userid || ' ' || v_user.username);
    ELSE
      dbms_output.put_line('none');
    END IF;
  END LOOP;
  
  CLOSE cur_user; -- 关闭游标

EXCEPTION
  WHEN OTHERS THEN
    IF cur_user%ISOPEN THEN
      CLOSE cur_user;
    END IF;
  
END;