SQL学习笔记

创建带约束的表属性
birthday date NOT NULL CHECK(birthday >=to_date('1970-01-01', 'yyyy-mm-dd'))
去掉重复的distinct
last_day()
首字母大写initcap(ename)
字符长度length(ename)
字符串中不含有R  ename not like '%R%'
字符串中含有R ename like '%R%'
字符串的前三个字符 substr(ename,1,3)
用a替换所有的A replace(ename,'A','a')
两个时间之间相差的月份 months_between(sysdate,早的时间)
从某个时间中取出年份to_char(hiredate,'yyyy')时间格式为(yyyy-mm-dd)转换取出的为字符串型
为某一数取整round(sal/30)
sysdate-hiredate 当前系统时间到受雇时间的天数
表1 join  表2 on 关联条件 where条件
把一个表看做两个表的思想
表1 left join  表2 on 关联条件 where条件 把表1中的内容全列出来，表2中没有对应的用null补齐
多表关联 ：
select e.empno,e.ename,d.dname from emp e 
join emp m on e.mgr=m.empno
join dept d on e.deptno=d.deptno
where e.hiredate < m.hiredate;
表1先与表2关联，再与表3关联
nvl(comm,0)函数如果comm为null就转换为0；
nvl2(comm,value1,value2)如果comm为null返回value1否则返回value2
count(rowid)。。。group by。。。计算出分组后该组的成员数量
伪例rownum的使用
Oracle中查找指定条记录select * from (select rownum num,t.* from emp t) where num between 5 and 14;


分析函数dense_rank() 相等时排位相等，排号是连续的。
select ename , sal,dense_rank() over (order by sal desc) rank from emp
分析函数rank() ，相等时排位相等，排号时跳跃的。
select ename , sal,rank() over (order by sal desc) rank from emp
分析函数row_number()不管是否具有相等的记录，排号是连续的
select ename , sal,row_number() over (order by sal desc) rank from emp


集合操作符
union：将多个查询结果合并到一个结果中，没有重复行（并集）
union all：将多个查询结果合并到一个结果中，有重复行
intersect：返回两个查询结果中共有的行 (交集)
minus：返回从第一个查询结果中减去第二个查询结果中相同的行之后剩余的行(差集)


conn / as sysdba系统管理员登陆
create user name identified by password; 创建用户
alter user s2 identified by sa;修改密码
alter user s2 account lock; 锁定账户
alter user s2 account unlock;解锁
drop user s2;用户删除
drop user s2 cascade;强制删除


权限集合：connect，resource
grant connect，resource to s2 授权
grant select on scott.emp TO test;
grant all on scott.emp to tjitcast
revoke connect，resource from s2 撤销权限


每个用户都可以通过user_sys_privs表查看所具有的系统权限
每个用户都可以通过user_tab_privs_made表查看自己向其他用户开放了哪些对象权限。
DBA：数据库管理员所有权限
EXP_FULL_DATABASE：可把数据库整个备份输出的权限
IMP_FULL_DATABASE ：可把数据库整个备份恢复输入的权限


exp scott/tiger [@ip8] file=d:\exp.dmp tables=(emp,dept,salgread) 表方式导出
exp scott/tiger [@ip8] tables=(emp,dept,salgrade) file=scott_back_tab 
exp scott/admin file=scott_back owner=scott 用户方式导出
exp system/aptech [@accp] tablespaces=(users) file=tbs_users表空间方式导出
exp system/aptech parfile='C:\parameters.txt'使用参数文件导出


imp accp/accp@accp file=item_back.dmp ignore=y full=y将整个文件导入数据库
imp system/aptech@accp file=scott_back fromuser=scott touser=martin tables=(emp,dept)将某个用户表的备份导入到另一个用户中
imp system/oracle parfile='C:\parameters.txt'使用参数文件导入










imp scott/admin file=scott full=y


创建语句：
CREATE TABLE [方案名.]表名(
        列名  列类型  [default 默认值] [约束类型],
         …
         [CONSTRAINT 表级约束]
     );
修改表结构：
添加列：ALTER TABLE 表名 ADD(列名 类型 [DEFAULT 默认值]…);
修改列：ALTER TABLE 表名 MODIFY(列名 类型 [DEFAULT 值]…);
删除列：ALTER TABLE 表名 DROP COLUMN 列名;


为表重命名：
RENAME 表名 TO 新表名;


删除表 DROP TABLE 表名;


显示回收站中的对象：SHOW RECYCLEBIN;
恢复回收站中的表：FLASHBACK TABLE 表名 TO BEFORE DROP;
删除回收站中的表：PURGE TABLE 表名;
彻底删除：DROP TABLE 表名 PURGE;


截断表删除表中的数据
 TRUNCATE TABLE 表名;


表的范围分区
PARTITION BY RANGE (column_name)
(
  PARTITION part1 VALUES LESS THAN(range1),
  PARTITION part2 VALUES LESS THAN(range2),
  ...
  [PARTITION partN VALUES LESS THAN(MAXVALUE)]
);


列表分区 
PARTITION BY LIST (column_name)
(
  PARTITION part1 VALUES (values_list1),
  PARTITION part2 VALUES (values_list2),
  ...
  PARTITION partN VALUES (DEFAULT)
);


添加分区，在分区的最后加上新的分区
SQL> ALTER TABLE SALES
     ADD PARTITION P4 VALUES LESS THAN (4000);
删除分区和分区中的数据
SQL> ALTER TABLE SALES DROP PARTITION P4


截断分区只删除分区中的数据
SQL> ALTER TABLE SALES TRUNCATE PARTITION P3;


合并分区 合并两个相连的分区
SQL> ALTER TABLE SALES 
MERGE PARTITIONS S1, S2 INTO PARTITION S2;
拆分分区 把一个大的分区分成两个小的分区
SQL> ALTER TABLE SALES SPLIT PARTITION P2 AT (1500)
INTO (PARTITION P21, PARTITION P22);


对表的修改用 alter table 表名 add/drop/modify....


添加主键
ALTER TABLE 表名
            ADD [CONSTRAINT 约束名]  约束类型(要约束的列名);


添加外键
ALTER TABLE 表名
            ADD [CONSTRAINT 约束名] 
            FOREIGN KEY (要添加外键的列名)
            REFERENCES 主表名(主表的某一列名);


alter table student add constraint fk_student_clszz_cid foreign key (cid) references clszz (cid)；


删除约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名;


创建有约束条件和关联的表
--创建班级表
create table clazz(
  cid number(5) not null,
  name varchar2(30) not null,
  state varchar2(6) default ('不可用'),
  constraint pk_clazz_cid primary key (cid),
  constraint ch_state check(state in ('可用','不可用'))
);
--创建学生表
create table student(
   sid number(10) not null,
   name varchar2(30) unique,
   age number(3) not null,
   gender varchar2(4)  default ('女'),
   cid number(5),
   constraint pk_student_sid primary key (sid),
   constraint ch_age check (age between 0 and 200),
   constraint ch_gender check (gender in ('男','女','保密'))
);
设置外键引用
alter table student add constraint fk_student_clazz_cid foreign key (cid) references clazz (cid);


创建序列
CREATE SEQUENCE 序列名   [INCREMENT BY 递增量值]
    [START WITH 开始值]
    [MAXVALUE 最大值]     [MINVALUE 最小值]
    [CYCLE | NOCYCLE] -- 达到最大值或最小值时是否循环。
    [CACHE 预生成数量 | NOCHE]; -- 默认缓存20个
使用序列
NEXTVAL：返回下一个序列值。可用做某个表的主键值
CURRVAL：返回当前序列值。仅用于查看
删除序列
DROP SEQUENCE 序列名;


CREATE TABLE use_seq(id number primary key);
CREATE SEQUENCE myseq;


SELECT myseq.nextval FROM dual;
SELECT myseq.currval FROM dual;


INSERT INTO use_sql VALUES(myseq.nextval);


创建“B-树”索引：
CREATE [UNIQUE] INDEX 索引名 ON 表名(列名[,列名]…);
创建“位图”索引：
CREATE BITMAP INDEX 索引名 ON 表名(列名[,列名]…);
删除索引：
DROP INDEX 索引名;


视图就是数据库中的虚拟表查询视图比查询表要快的多
创建：
CREATE [OR REPLACE] VIEW 视图名 AS 查询语句 [WITH CHECK OPTION] [WITH READ ONLY];
选项：
OR REPLACE：视图存在时就替换
WITH CHECK OPTION：视图的创建条件不能更改
WITH READ ONLY：视图中的内容不能更改
删除：
DROP VIEW 视图名;


当当前用户有了另一个用户中表的权限可以为该表创建同义词以方便对表的操作
CREATE SYNONYM emp FOR SCOTT.emp;
管理员可以创建共有的同义词
CREATE PUBLIC SYNONYM emp_syn FOR SCOTT.emp;
SQL> DROP [PUBLIC] SYNONYM emp_syn; 




PL/SQL编程：


PL/SQL(Procedural Language/SQL)：过程化编程语言
[DECLARE 
 --  程序的声明部分，例如，定义变量 
]
BEGIN 
 --  程序的执行体，具体的操作代码 
[EXCEPTION 
 --  异常的处理 
]
END ; 


表类型：类似于数组,以下标方式存放表中的某一列的某一值。
定义复合类型：
表类型
TYPE 类型名 IS TABLE OF 数据类型 INDEX BY BINARY_INTEGER;
声明复合类型的变量：
变量名 复合类型名;
DECLARE
  TYPE emp_empno_table_type IS TABLE OF number INDEX BY BINARY_INTEGER;
  v_empnos emp_empno_table_type;
BEGIN
  v_empnos(0) := 7788;
  v_empnos(2) := 7839;
  v_empnos(-1) := 7369;
  DBMS_OUTPUT.PUT_LINE(v_empnos(-1));
END;
行类型
记录类型：记录某一行记录的多列的值。
定义记录类型：
TYPE 类型名 IS RECORD (变量名 类型,…);
声明记录类型变量：
变量名 复合类型名;
DECLARE
  TYPE emp_record_type IS RECORD ( ename VARCHAR(10), sal NUMBER);
  v_emp_record emp_record_type;
BEGIN
  v_emp_record.ename:='xxx';
  v_emp_record.sal:=3000;
  DBMS_OUTPUT.PUT_LINE(v_emp_record.ename||','||v_emp_record.sal);
END;
特殊类型声明方式
可以通过%TYPE来定义变量的类型，表示使用与表中指定列相同的类型：
v_emp_hiredate emp.hiredate%TYPE;
%ROWTYPE定义一个由数据库表的字段集合构成的记录类型变量。
v_emp_record emp%ROWTYPE;
DECLARE
    v_currdate scott.emp.hiredate%TYPE;
    v_emp_record scott.emp%ROWTYPE;
BEGIN
    v_currdate := sysdate;
    v_emp_record.ename:='xxx';
    v_emp_record.sal:=3000;
    DBMS_OUTPUT.PUT_LINE(v_currdate||','||v_emp_record.ename
              ||','||v_emp_record.sal);
END;
if语句语法
IF 布尔表达式1 THEN
        语句块1
ELSIF 布尔表达式2 THEN
         语句块2
ELSE
          语句块3
END IF;






循环类型语句
LOOP
     语句块
     EXIT WHEN 退出条件;
     更改条件;
END LOOP;


WHILE 循环条件表达式 LOOP
      语句块
END LOOP;


FOR 循环变量 IN [REVERSE] 循环下限.. 循环上限 LOOP
         语句块
END LOOP;


PL/SQL中使用SQL语句
DECLARE
    v_emp_ename emp.ename%TYPE;
    v_emp_hiredate emp.hiredate%TYPE;
    v_emp_record scott.emp%ROWTYPE;
BEGIN
    SELECT ename,hiredate INTO v_emp_ename, v_emp_hiredate FROM emp where empno=7902;
    DBMS_OUTPUT.PUT_LINE('名'||v_emp_ename
        ||',入职日期'|| to_char(v_emp_hiredate, 'yyyy-mm-dd'));
    SELECT * INTO v_emp_record FROM emp where empno=7902;
    DBMS_OUTPUT.PUT_LINE('名'||v_emp_record.ename
        ||',入职日期'|| to_char(v_emp_record.hiredate, 'yyyy-mm-dd'));
execute immediate 'create table temp(tid number(4), gender char(3) default ''男'')';
END;


游标的使用
declare
   v_empno emp.empno%type;
   v_ename emp.ename%type;
   v_sal emp.sal%type;
   cursor v_cursor is select empno,ename,sal from emp;
begin
   open v_cursor;
   loop
      fetch v_cursor into v_empno,v_ename,v_sal;
      exit when v_cursor%notfound;
      dbms_output.put_line('编号'||v_empno||'姓名'||v_ename||'薪水'||v_sal);
  end loop;
  close v_cursor;
end;


带参数的显示游标
declare
desig varchar2(20);
v_deptno number(4);
v_ename varchar2(20);
cursor emp_cur(desigparam varchar2) is select deptno,ename from emp where job=desigparam;
begin
desig:='&desig';
open emp_cur(desig);
loop
fetch emp_cur into v_deptno,v_ename;
exit when emp_cur%notfound;
dbms_output.put_line(v_deptno||','||v_ename||','||desig);
end loop;
close emp_cur;
end;
显示游标更新行//删除
declare
new_sal number(6,2);
cursor cur_sal is select sal from emp where sal<3000 for update;
begin
open cur_sal;
loop
fetch cur_sal into new_sal;
exit when cur_sal%notfound;
update emp set sal=new_sal*1.5 where current of cur_sal;//delete from 表名 where  current of 游标
end loop;
close cur_sal;
commit;
end;


当使用FOR循环访问游标中的记录时，可以不显式地方打开和关闭游标，
FOR循环会自动执行这些操作。（隐式游标）
declare
type emp_record_type is record (empno number(4),ename varchar2(20),sal number(6,2));
v_emp emp_record_type;
cursor v_emp_cursor is select empno,ename,sal from emp;
begin
for v_emp in v_emp_cursor loop
dbms_output.put_line('编号'||v_emp.empno||'姓名'||v_emp.ename||'月薪'||v_emp.sal);
end loop;
end;


declare
type emp_record_type is record(empno number(4),ename varchar2(20),sal number(6,2));
v_emp emp_record_type;
cursor v_emp_cursor is select empno,ename,sal from itcast_emp for update;
begin
for v_emp in v_emp_cursor loop
if(v_emp.sal<2000) then
update itcast_emp set sal=1.5*sal where current of v_emp_cursor;
end if;
end loop;
commit;
end;
错误处理
用户定义异常
declare
invalidCategory exception;
category varchar2(20);
begin
category:='&category';
if category not in ('附件','备件') then
raise invalidCategory;
else
dbms_output.put_line('您输入的类别'||category);
end if;
exception
when invalidCategory then
dbms_output.put_line('无法识别类别');
end;


异常
系统预定义的异常：
ZERO_DIVIDE：除零
OTHERS：所有异常


DECLARE 
  i number := 100;
BEGIN 
  DBMS_OUTPUT.PUT_LINE(i/0);
EXCEPTION 
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('error');
END ; 


存储过程
CREATE [OR REPLACE] PROCEDURE 过程名
     [(参数名 [IN|OUT|IN OUT] 数据类型[, …])]
     {IS | AS}
           [变量声明]
     BEGIN
           过程的主体
     END [过程名];


create or replace procedure p
is
cursor v_emp_cursor is select * from emp for update ;
v_emp_rowtype emp%rowtype;
begin
 for v_emp_rowtype in v_emp_cursor loop
 if v_emp_rowtype.deptno=10 then
 update emp set sal=sal-100 where current of v_emp_cursor;
 end if;
 end loop;
 commit;
end;


带参数的存储过程
create or replace procedure pp
(v_a in number,v_b in number,v_c out number,v_d in out number)
is
begin
 if v_a>v_b then v_c:=v_a;
 else v_c:=v_b;
 end if;
 v_d:=v_d+v_c;
end;
调用存储过程
declare
v_a number :=3;
v_b number :=5;
v_c number;
v_d number :=7;
begin
 pp(v_a,v_b,v_c,v_d);
 dbms_output.put_line(v_c||','||v_d);
end;


函数
CREATE [OR REPLACE] FUNCTION 函数名
     [(参数名 [IN|OUT|IN OUT] 数据类型[, …])]
     RETURN 返回值类型
     {IS | AS}
     BEGIN
           函数的主体
     END [函数名];


 create or replace function tax(sal in number)
 return number
 is
 begin
 if sal<1500 then return 0;
 else return sal*0.05;
 end if;
 end;
触发器
创建语法：
CREATE [OR REPLACE] TRIGGER 触发器名
     [BEFORE | AFTER] 激活触发器的事件(insert,update,delete)
ON 表名
     [FOR EACH ROW]  -- 指定为行级触发器
     [WHEN 触发条件]
     BEGIN
          主体;
     END [触发器名];


注意：
多种激活触发器用or来连接：insert or update or delete
在触发器主体语句中可以用“inserting”、“updating”、“deleting”判断激活事件。
在行级触发器中，可以通过:old和:new别名访问列的原值和新值。
创建日志表
create table ti_emp_log(
 username varchar2(20),
 action varchar2(20),
 when date
)


创建触发器
create or replace trigger emp_log
after insert or update or delete
on emp
--for each row
begin
if inserting then
  insert into ti_emp_log values(user,'insert',sysdate);
elsif updating then
  insert into ti_emp_log values(user,'update',sysdate);
  dbms_output.put_line(:old.sal||','||:new.sal);
elsif deleting then
  insert into ti_emp_log values(user,'delete',sysdate);
end if;
end;


create or replace trigger emp_log2
after update 
on emp
for each row
begin 
insert into ti_emp_log values(user,'update:'||:old.sal||'->'||:new.sal,sysdate);
end;
禁用和启用触发器
ALTER TRIGGER 触发器名 {DISABLE | ENABLE};