oracle-速查

用户管理

--创建用户
create user 用户名 identified by 密码;
 
--用户授权
grant 权限 to 用户名;
 
--删除用户
drop user 用户名;
 
--账号解锁
alter user 用户名 account unlock;

表管理

--创建表
create table users(
    id number,
    name varchar2(32),
    password varchar2(32),
    birthday date
);
 
--添加一个字段
alter table users add(age number(2));
 
--修改字段
alter table users modify(age varchar2(2));
 
--删除字段
alter table users drop column age;
 
--修改表名
rename users to student;
 
--删除表
drop table users;

基本SQL

--添加数据
INSERT INTO student VALUES('A001', '张三', '男', '01-5月-05',10); --所有字段都插入数据
ALTER SESSIONSETNLS_DATE_FORMAT ='yyyy-mm-dd'; --修改日期的默认格式（临时修改）
INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); --插入部分字段
INSERT INTOstudent(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null); --插入空值
 
--修改数据
UPDATE student SET sex = '女' WHERE xh = 'A001'; --修改一个字段
UPDATE student SET sex = '男',birthday = '1984-04-01' WHERE xh = 'A001'; --修改多个字段
 
--删除数据
DELETE FROM student; --删除所有记录，表结构还在，写日志，可以恢复的，速度慢。
DROP TABLE student; --删除表的结构和数据；
delete fromstudent WHERExh = 'A001'; --删除一条记录；
truncate TABLE student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
 
DESC emp; --查看表结构
SELECT * FROM dept; --查询所有列
SELECT ename, sal, job, deptno FROM emp; --查询指定列
SELECT DISTINCT deptno, job FROM emp; --取消重复行
SELECT deptno,job,sal FROM emp WHERE ename= 'SMITH'; --条件查询
SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp; --使用算术表达式
SELECT ename "姓名", sal*12 AS "年收入" FROM emp; --使用列的别名
SELECT ename  || ' is a ' || job FROM emp; --连接字符串
SELECT ename,sal FROM emp WHERE ename like 'S%'; --使用like操作符 
SELECT * FROM emp WHERE empno in (7844,7839,123,456); --在where条件中使用in
SELECT *FROM emp WHERE mgr is null; --使用is null的操作符
SELECT * FROM emp WHERE (sal > 500 or job= 'MANAGER') and ename LIKE 'J%'; --使用逻辑操作符号 
SELECT * FROM emp ORDER by deptno, sal DESC; --使用order by字句
select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc; --使用列的别名排序
 
--分组函数
max() 求最大值
min() 求最小值
avg() 求平均值
sum() 求合计
count 求数量
 
SELECT min(sal), AVG(sal), deptno, job FROM emp GROUP by deptno, job; -- 使用 group by
SELECT AVG(sal), MAX(sal), deptno FROM empGROUP by deptno having AVG(sal) < 2000; --使用 having
 
--多列子查询
SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM empWHERE ename = 'SMITH');
 
--分页查询
select t2.* from
(selectt1.*,rownum rn from
   (select * from emp) t1
 where rownum<=6) t2 where rn>=4;
  
 --集合操作
 union(并集，取消重复行)、union all(并集、不会取消重复行)、intersect(交集)、minus(差集)
  
 --表连接
 SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename ='FORD'; --自连接
 select emp.ename,dept.dname fromemp,dept where emp.deptno=dept.deptno; --内连接
 selectstu.id,stu.name,exam.grade from stu left join exam on stu.id=exam.id; --外连接

常用函数

--字符函数
lower(char)：将字符串转化为小写的格式.
upper(char)：将字符串转化为大写的格式.
length(char)：返回字符串的长度。
substr(char,m,n)：取字符串的子串;n代表取n个的意思，不是代表取到第n个
replace(char1,search_string,replace_string)
instr(char1,char2,[,n[,m]])取子串在字符串的位置
trim() 删除给定字符串或者给定数字中的头部或者尾部的给定字符 
TRANSLATE(char, from, to) 返回将出现在from中的每个字符替换为to中的相应字符以后的字符串
decode() 它将输入数值与函数中的参数列表相比较，根据输入值返回一个对应值。
nvl() 空值判断
 
--数学函数
round(n,[m])该函数用于执行四舍五入，如果省掉m，则四舍五入到整数，如果m是正数，则四舍五入到小数点的m位后。如果m是负数，则四舍五入到小数点的m位前。
trunc(n,[m]) 该函数用于截取数字。若省掉m，就截去小数部分(等价于trunc(n,0))，如果m是正数就截取到小数点的m位后，若m是负数，则截取到小数点的前m位。
mod(m,n) 取模
floor(n) 返回小于或是等于n的最大整数
ceil(n) 返回大于或是等于n的最小整数
abs(n) 返回数字n的绝对值
exp(n) 返回e的n次幂 
log(m,n) 返回对数值 
power(m,n) 返回m的n次幂 
 
--日期函数
sysdate 该函数返回系统时间
add_months(d,n) 在日期d上增加n个月
last_day(d) 返回指定日期所在月份的最后一天
to_char(date,'format') 日期格式化
to_date(string,'format') 将字符串转换成date类型的数据
 
--类型转换
to_char(),to_date(),to_number()

序列

create sequencemyseq  --创建开始
start with 1           --从1开始
increment by 1         --每次增长1
minvalue 1             --最小值
maxvalue 30000         --最大值
cycle    //cycle表示当序列增加30000，重新从1开始，如果不希望，就nocycle
nocache; --不缓存，而 cache 10;表示一次产生10个号共你使用，缺点可能会跳号，但提供效率

视图

--创建视图
create view 视图名 as select 语句 [withread only];
--创建或修改视图
create  or replace view 视图名 asselect 语句 [with read only];
--删除视图
drop view 视图名;

索引

1 2	`create` `index` `索引名` `on` `表名(列名);`

存储过程

create procedure pro4(in_empno number)  
is 
--declare  这个就不要了 块才需要declare指定  
--定义变量的格式是：变量名称 变量的类型  
--和建表一样  
v_ename varchar2(10);  
begin 
--把指定雇员编号查询得到的姓名值放入v_ename变量中  
select ename into v_ename from emp where empno=in_empno;  
dbms_output.put_line('雇员名字是'||v_ename);  
end; 

函数

create or replace function fun1(in_v_ename varchar2)  
return number--这里不要分号  
is 
--定义一个变量来接受年薪  
v_annual_sal number;  
begin 
select sal+nvl(comm,0)*13 into v_annual_sal from emp  
where ename=in_v_ename;  
return v_annual_sal;  
end;  
sqlplus调用：select fun1('KING') from dual;  

包

--声明
create or replace package mypackage1  
is 
--声明一个过程  
procedure pro5(v_in_ename varchar2,v_in_newsal number);  
--声明一个函数  
function fun1(v_in_ename varchar2) return number;  
end;  
 
--实现
create or replace package body mypackage1  
is 
--实现一个过程  
procedure pro5(v_in_ename varchar2,v_in_newsal number)  
is 
v_empno varchar2(32);  
begin 
select empno into v_empno from emp where ename=v_in_ename;  
update emp set sal=v_in_newsal where ename=v_in_ename;  
exception  
when no_data_found then 
dbms_output.put_line('朋友,你输入的编号有误!');  
end;  
   
--实现一个函数  
function fun1(v_in_ename varchar2) return number  
is 
--定义一个变量来接受年薪  
v_annual_sal number;  
begin 
select sal+nvl(comm,0)*13 into v_annual_sal from emp  
where ename=v_in_ename;  
return v_annual_sal;  
end;  
   
end;

触发器

create or replace trigger tri4  
before update on scott.emp  
for each row--这个一定要 是行级  不是表级  
begin 
  if :new.sal<:old.sal then 
    dbms_output.put_line('工资不能低于原来工资');  
    raise_application_error(-2005,'工资不能低于原来工资');  
  else 
    dbms_output.put_line('原来工资是'||:old.sal||'现在工资'||:new.sal);  
  end if;  
end; 

PL/SQL数据类型

--简单类型
create or replace procedure pro2(v_in_empno in number) is 
--定义变量  
v_tax_rate number(3,2):=0.03;  
v_sal number;  
v_ename varchar2(32);  
v_tax number;  
begin 
select ename,sal into v_ename,v_sal from emp  
where empno=v_in_empno;  
--计算个人所得税  
v_tax:=v_sal*v_tax_rate;  
dbms_output.put_line(v_ename||'工资='||v_sal||'个人所得税:'||v_tax);  
end; 
 
--复合类型
create or replace procedure pro3(v_in_empno in number)is 
--定义一个记录数据类型  
type emp_record is record(  
v_ename emp.ename%type,  
v_sal emp.sal%type,  
v_job emp.job%type  
);  
--定义一个变量，其类型是emp_record  
v_emp_record emp_record;  
begin 
select ename,sal,job into v_emp_record from emp  
where empno=v_in_empno;  
dbms_output.put_line('名字'||v_emp_record.v_ename||'薪水'||v_emp_record.v_sal||'岗位'||v_emp_record.v_job);  
end;
 
--游标
create or replace procedure pro1(v_in_deptno number) is 
--先定义一个游标变量类型  一个变量类型可以定义多个变量  
type emp_cursor is ref cursor;  
--定义1个游标变量  
v_emp_cursor emp_cursor;  
--定义2个变量  
v_ename emp.ename%type;  
v_sal emp.sal%type;  
v_empno emp.empno%type;  
begin--两个空格  
  --执行语句  
  --打开游标  
  open v_emp_cursor for select ename,sal,empno from emp  
  where deptno=v_in_deptno;  
  --取出游标指向的每行数据，用循环语句  
  loop  
    fetch v_emp_cursor into v_ename,v_sal,v_empno;--这句 导致游标往下移  
    --判断当前游标是否达到最后  
    exit when v_emp_cursor%notfound;  
    --输出  输出在判断之后，先判断，否则重复输出(最后一行)  
    dbms_output.put_line('用户名'||v_ename||' 薪水'||v_sal);  
    ----------------------------更改地方-----------------------------  
    if v_sal<200 then 
      update emp set sal=sal+100 where empno=v_empno;  
    end if;  
  end loop;  
  --关闭游标[完后一定要关闭游标]  
  close v_emp_cursor;  
end; 

PL/SQL控制结构

--条件分支
create or replace procedure pro6(v_in_ename varchar2) is 
v_comm emp.comm%type;  
begin 
  --查询补助  
  select comm into v_comm from emp where ename=v_in_ename;  
  if v_comm<>0 then--这里把comm空值也加进去了  
    update emp set comm=comm+100 where ename=v_in_ename;  
  else 
    update emp set comm=200 where ename=v_in_ename;  
  end if;  
end;
 
--循环
declare 
i number:=1;  
begin
 
  --循环1
  for i in reverse 1..10 loop
  insert into users values(i,);
  end loop;
   
  --循环2  
  <<start_loop>>  
  loop  
    dbms_output.put_line('输出i='||i);  
    if i=12 then 
      goto end_loop;  
    end if;  
    i:=i+1;  
    if i=10 then 
      goto start_loop;  
    end if;  
  end loop;  
  <<end_loop>>   
dbms_output.put_line('循环结束');  
end;