尚学堂Oracle笔记整理

65 阅读 0 评论 43 点赞

我是靠谱客的博主精明云朵，最近开发中收集的这篇文章主要介绍尚学堂Oracle笔记整理，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1、解锁scott用户：
alter user scott account unlock;
I、在其他用户切换到sysdba用户
conn sys/root as sysdba
II、授权
grant create table,create view ,create session to scott
2、以DBA身份登陆数据库
sqlplus sys/root as sysdba
3、查看标的结构：
desc tablename
4、默认转义字符:/
select * from emp where ename like '%/%%'//查出ename中含有%的信息
修改转义字符：
select * from emp where ename like '%$%%'escape '$';
5、、数据格式的转换：
select to_char(sal,'L00000.00000') from emp;//其中L是代表本地货币符

select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual;
select to_char(sysdate,'YYYY-MM-DD HH:MI:SS') from dual;//日期转化格式
6、字符截取：
select substr(ename,1,3) from emp;//从第一位到到低三位
7、日期的转换：
SQL> select ename,hiredate from emp where hiredate>to_date('1981-2-20 11:00:00','YYYY-MM_DD HH:MI:SS');
select sal from emp where sal>to_number('$2,33.44477','$99,99.99999');
8】、找出职员年薪与津贴的综合，津贴为空值的用0代替
select ename,sal*12 + nvl(comm,0) from emp;
9】、精确度：
select round(sal,2) from emp;
10】、having、group by子句的使用：
SQL> select avg(sal) from emp group by deptno;

AVG(SAL)
----------
1566.66667
2175
2916.66667

SQL> select avg(sal) from emp group by deptno having avg(sal)>2000;

AVG(SAL)
----------
2175
2916.66667

11】、两张表的连接（旧语法--1992）
SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;

ENAME      DNAME
---------- --------------
CLARK      ACCOUNTING
KING       ACCOUNTING
MILLER     ACCOUNTING
JONES      RESEARCH
FORD       RESEARCH
ADAMS      RESEARCH
SMITH      RESEARCH
SCOTT      RESEARCH
WARD       SALES
TURNER     SALES
ALLEN      SALES

ENAME      DNAME
---------- --------------
JAMES      SALES
BLAKE      SALES
MARTIN     SALES

两张表的连接（新语法--1999）
SQL> select ename ,dname from emp join dept on(emp.deptno=dept.deptno);

ENAME      DNAME
---------- --------------
JAMES      SALES
BLAKE      SALES
MARTIN     SALES

已选择14行。
12】求部门中哪些人的薪水最高：
select ename ,sal from emp
join ( select max(sal) max_sal ,deptno from emp group by deptno ) t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);

13】求部门平均薪水等级
select deptno,avg_sal ,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);

14】雇员中哪些人是经理：
SQL> select ename from emp where empno in(select distinct mgr from emp);

15】、不准用组函数，求薪水的最高值（面试题）：
select distinct sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));

16】求薪水最高部门的部门编号：
select deptno, avg_sal from (select avg(sal) avg_sal ,deptno from
emp group by deptno) where avg_sal=(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno));

17】、求平均薪水最高部门的名称：
select dname from dept where deptno=
(select deptno f rom
(select avg(sal) avg_sal ,deptno from emp group by deptno ) where avg_sal=(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno)));
18】、创建视图：
create view v$_dept_sal_info as select .........
19】求比普通员工的平均薪水还要高的经理人名称
select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > (select max(sal) from emp where empno not in ( select distinct mgr from emp where mgr is not null))
/

1】、CreateNewUser
1.---backup scott
  exp
2----create user
  create user admin identified by admin default tablespace users quota 10M on users
  grant create session,create table,create view to admin
(给admin用户分配users表空间，且只有10 兆空间，以密码admin登陆）
3.----import the data
  imp
2]】、删除用户
drop user admin ;
22】、事务回滚：
rollback;
23】、备份；
create table as select * from emp;
23】、Oracle中不像Mysql中取数据分行，Oracle应该使用子查询才能达到其目的在使用rownum时，
     条件只可取"<="，"<"其他的的关系运算不行：
select ename,sal from (select rownum r,ename,sal from emp) where r>10
/
求薪水最高的第6列到第十列的雇员姓名和薪水（important）
Example：select ename,sal from(select ename,sal,rownum r from(slect ename,sal from emp order by sal desc))where r>6 and r<=10
/

<----------------------------------------------end------------------------------------------------------------------------>

1】、表的创建；
Example：(非空约束）
create table stu
(
id number(6),
name varchar(20) constraint stu_name_nn NOT NULL,
sex varchar(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50) unique
)
/

insert into stu values(080201,'唐龙','M',22,sysdate,1,1,'Elapsed2008@gmail.com')
insert into stu (name,email) values ('zhansan','ip_2008@sina.com')
Example2:（主键约束）
create table stu2
(
id number(6) primary key,
name varchar(20) NOT NULL,
sex varchar(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_name_ema_uni unique(name,email)
)
/

insert into stu2 values(080201,'唐龙','M',22,sysdate,1,1,'Elapsed2008@gmail.com')
Example3:（主键约束）
create table stu3
(
id number(6),
name varchar(20) NOT NULL,
sex varchar(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_name_ema_uni unique(name,email),
constraint stu_id_pk primary key(id)
)
/
Example4:(外键约束）
create table stu3
(
id number(6),
name varchar(20) NOT NULL,
sex varchar(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) references class(id),
email varchar(50),
constraint stu_name_ema_unique unique(name,email),
constraint stu_id_pk primary key(id)
)
/

create table class
(
id number(4) primary key,
name varchar2(20)not null
)
/
insert into class values(2006,'dianxin')//主义必须先插入该行才能插入下面的，否则出错
insert into stu3 (id,name,class,email) values (123,'lisi',1006,'dhdhdh@gmail.com')
/

<-------------------------------------------------------end----------------------------------------------------------------->
修改标表的结构：
1】、增加字段：
  alter table stu3 add(loc varchar(40))
/
2】、删除字段
  alter table stu3 drop(loc)
/
3】、修改表的精度（通常在没有数据插入到表中时，精度可以任意修改，但如果已经有了数据，只能把精度向大的改二不能向小的改
  alter table stu3 modify(loc varchar(150))
  alter table stu3 modify (loc varchar(20))
4】、去掉约束条件
Example：alter table stu3 drop constraint (stu_id_pk)
5、显示当前用户下的所有表
  desc user_tables
  select table_name from user_tables
/
  同理查看当前用户下的视图：
  select view_name from user_views
6】、查看当前用户下的表的约束关系
  desc user_constraints
/
  select constraint_name from user_constraints
/
  select constraint_name,table_name from user_constraints

7】、oracle数据字典表
  desc dictionary
  选定数据字典中以U开头的表名字
   select table_name from dictionary where table_name like 'U%'
/
8】、为某个字段建立索引（其功能是党访问该字段中的数据时，只有在频繁查询某个字段时效率更高）
Example：
  create index stu3_email on stu3(email)
/
9】、创建视图
  Example：
   create view v$_stu3_email as select email from stu3
/
10】、序列自动递增：
create sequence seq;
select seq.nextval from dual;
----其应用举例--------
create table article(
id number,
title varchar2(1024),
cont long)
/
  insert into article values(seq.nextval,'I love you!','Good good study,and day day up!')
  insert into article values(seq.nextval,'I do not love you!','Everything has end!')
  insert into article values(seq.nextval,'人应该积极的去活着','生生不息!');
  insert into article values(seq.nextval,'昂扬奋进的贵州师范大学','我爱我的母校');
/

<-------------------------------------------------------------end-------------------------------------------------------->
A、范式
I、第一范式：
定义：要有关键字，列不可分，即列成原子性，（冗余字段：包含想替你个信息的内容）
II、第二范式：
定义：当一张表中存在多个主键时，不是主键的字段不能部分依赖与主键，即要全部依赖于主键的组合，（不能存在部分依赖）
应该采用多个表然后建立之间的连接
III、第三范式：
定义：不能存在传递依赖

<----------------------------------------------------------------end--------------------------------------------------->

PL/SQL编程
I、应用举例：
Example：
  begin
  dbms_output.put_line('Hellow');
  end;
/
---------如果需要看到输出结果------
  set serveroutput on;
  begin
  dbms_output.put_line('Hellow');
  end;
/

Example2:
  declare
   v_num varchar(20);
  begin
   v_num :='唐龙你好';
   dbms_output.put_line(v_num);
  end;
/

Example3:
  declare
   v_num number :=0;
  begin
   v_num := 2/v_num;
   dbms_output.put_line(v_num);
  exception
   when others then
    dbms_output.put_line('erreor!');
    end;

变量的声明：、
  Example:
   declare
    v_temp number(1);
    v_count binary_integer :=0;
    v_sal number(7,2) :=4000.00
    v_date date :=sysdate;
    v_pi constant number(3,2) :=3; ---//constant表示常量
    v_valid boolean :=false;
    v_name varchar2(20) not null :='Myname';
    begin
    dbms_output.put_line('v_count value:' ||v_count);
    end;
--变量声明，使用%type属性：
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;

--table变量类型（相当于java中的数组）

Example:
declare
  type type_table_emp_empno is table of emp.empno%type index by binary_integer;
  v_empnos type_table_emp_empno;
  begin
  v_empnos(0) :=7369;
  v_empnos(1) :=5555;
  v_empnos(-1) :=1457;
  dbms_output.put_line(v_empnos(-1));
  end;

---Record变量的类型：
declare
type type_record_dept is record(
  deptno dept.deptno%type,
  dename dept.dname%type,
  loc dept.loc%type
  )
  v_temp type_record_dept;
begin
  v_temp.deptno :=50;
  v_temp.dname :='aaaaa';
  v_temp.loc :='bj';
  dbms_output.put_line(v_temp.dname);
  end;
  /

--使用%rowtype声明record所带来的好处（可以跟随表的结构动态变化）

declare

  v_temp dept%rowtype;
begin
  v_temp.deptno :=50;
  v_temp.dname :='aaaaa';
  v_temp.loc :='bj';
  dbms_output.put_line(v_temp.dname);
  end;
  /

----SQL语句的应用，在PL/SQL中使用select语句返回结果有且只有一条返回记录，并且有into关键字
Example：delete语句的应用
set serveroutput on;
declare
  v_ename emp.ename%type;
  v_sal emp.sal%type;

begin
  select ename,sal into v_ename,v_sal from emp where empno=7900;
  dbms_output.put_line(v_ename ||' '||v_sal);
  end;

Example：insert语句的应用
  declare
  v_deptno dept.deptno%type :=50;
  v_dname dept.dname%type :='kkkkkk';
  v_loc dept.loc%type :='gznu';
begin
  insert into dept values(v_deptno,v_dname,v_loc);
  commit;
  end;
  /
--SQl中对记录的影响的统计
declare
v_deptno emp2.deptno%type :=10;
v_count number;
begin
--update emp2 set sal=sal/2 where deptno=v_deptno;
--select deptno into v_deptno from emp2 where empno=7369;
select count(*) into v_count from emp2;
dbms_output.put_line(sql%rowcount||'条记录被影响');
commit;
end;

---SQL/Pl中的DDL语句应用
Example:
begin
execute immediate 'create table T(name varchar(20) default ''gznu'')';
end;

---PL/SQL中if语句的应用
--取出7369的薪水，如果小于1200，则输出‘low’，如果小于2000则输出‘middle’，否则输出‘high'
set serveroutput on;
declare
    v_sal emp.sal%type;
    begin
           select sal into v_sal from emp where empno=7369;
           if (v_sal<1200) then
           dbms_output.put_line('low');
           elsif(v_sal<2000) then
           --中间没有e
           dbms_output.put_line('middle');
           else
           dbms_output.put_line('high');
           end if;
           end;

--PL/SQL中的循环
declare
  i binary_integer :=1;
begin
  loop
   dbms_output.put_line(i);
   i :=i+1;
   exit when (i>=11);
  end loop;
  end;

-------------------------while循环--------------------
declare
     j binary_integer :=1;
     begin
  while j < 11 loop
      dbms_output.put_line(j);
      j :=j+1;
  end loop;
     end;

-----------------------------for循环-------------------------------
begin
  for k in 1..100 loop
   dbms_output.put_line(k);
  end loop;

  for k in 1..20 loop
   dbms_output.put_line(k);
  end loop;
end;

-----------------------exception--------------------------------------
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno=10;
exception
  when too_many_rows then
   dbms_output.put_line('记录太多uo了');
  when others then
   dbms_output.put_line('error');
end;

-----------------------------DBA处理PL/SQL异常，并记录到日志表格---------------------------
create table errorlog
(
id number primary key,
errcode number,
errmsg varchar2(1024),
errdate date
)

create sequence seq_errorlog_id start with 1 increment by 1;

declare
  v_deptno dept.deptno%type :=10;
  v_errcode number;
  v_errmsg varchar2(1024);
begin
  delete from dept where deptno = v_deptno;
   commit;
exception
  when others then
  rollback;
   v_errcode :=SQLCODE;
   v_errmsg :=SQLERRM;
   insert into errorlog values ( seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
   commit;
   end;

--游标（重要）--------
declare
  cursor c is
  --声明一个游标变量，并指向select语句返回的结果集的首地址
   select * from emp;
  v_emp c%rowtype;
  --v_emp类型是select语句返回的结果集
begin
  open c;
  --打开游标
  fetch c into v_emp;
  --读取结果集，游标并指向下一条
  dbms_output.put_line(v_emp.ename);
  close c;
end;
/

---循环游标-------
declare
  cursor c is
    select * from emp;
   v_emp c%rowtype;
  begin
   open c;
   loop
    fetch c into v_emp;
    exit when (c%notfound);
    --读取结果集，游标并指向下一条
    dbms_output.put_line(v_emp.ename);
    --以上三条语句(建议)顺序不能改变
    end loop;
    close c;
end;
/

-----do....while循环---------
declare
  cursor c is
    select * from emp;
   v_emp c%rowtype;
  begin
   open c;
    fetch c into v_emp;
    while(c%found) loop
     dbms_output.put_line(v_emp.ename);
     fetch c into v_emp;
    end loop;
    close c;
end;
/

-----for循环------------------------------------
---注意在for循环中无需fetch和打开、关闭游标，for循环本身会自动启动
declare
  cursor c is
   select * from emp;
  begin
  for v_emp in c loop
   dbms_output.put_line(v_emp.ename);
  end loop;
end;

----------------带参数的游标
  declare

   cursor c(v_deptno emp.deptno%type,v_job emp.job%type)
   is
    select ename,sal from emp where deptno = v_deptno and job=v_job;
    v_temp c%rowtype;
  begin
   for v_tempp in c(30,'CLERK') loop
    dbms_output.put_line(v_temp.ename);
   end loop;
  end;

--可更新的游标----
declare
  cursor c
  is
   select * from emp2 for update;
   v_temp c%rowtype;
  begin
   for v_temp in c loop
    if(v_temp.sal<2000) then
     update emp2 set sal=sal*2 where current of c;
    elsif(v_temp.sal=5000) then
     delete from emp2 where current of c;
    end if;
   end loop;
   commit;
  end;
/

-------------------------------------------end-----------------------------------------------------------
--创建存储过程--------
--(在存储过程创建过程中，不会报错，即使有错也只会报告：‘警告: 创建的过程带有编译错误。“
--要查看错误原因，show error
create or replace procedure p
is
  cursor c is
   select * from emp2;
begin
  for v_temp in c loop
   if(v_temp.deptno=10) then
    update emp2 set sal = sal+10 where current of c;
   elsif(v_temp.deptno=20) then
    update emp2 set sal = sal+20 where current of c;
   else
    update emp2 set sal =sal+2000 where current of c;
   end if;
  end loop;
  commit;
end;

----带参数的存储过程-----------------
create or replace procedure p
  (v_a in number,v_b number,v_ret out number,v_temp in out number)
  --其中v_a是接受参数，由调用环境给v_a赋值，v_b默认也是接收参数，v_ret是传出参数，其是向外传值的
  --v_temp是既能接收，也能传出，
  is
   begin
    if(v_a>v_b) then
     v_ret :=v_a;
    else
     v_ret :=v_b;
    end if;
    v_temp :=v_temp+1;
   end;
--(对带参数的存储过程的调用）
declare
  v_a number :=3;
  v_b number :=4;
  v_ret number;
  v_temp number :=5;
begin
  p(v_a,v_b,v_ret,v_temp);
  dbms_output.put_line(v_ret);
  dbms_output.put_line(v_temp);
end;
/

——————————-PL/SQL创建的函数，党函数创建后，就能直接调用，想系统函数调用一样
create or replace function sal_tax
  (v_sal number)
  return number
is
begin
  if(v_sal<2000) then
   return v_sal*0.2;
  elsif(v_sal<3000) then
   return v_sal*0.3;
  else
   return 0;
  end if;
end;

---调用自定义函数
select lower(ename),sal_tax(sal) from emp;

------------------------------------------------------------------end-------------------------------------------------------
--触发器
create table emp2_log
(
  uname varchar2(20),
  action varchar2(20),
  atime date
);

create or replace trigger trig
  after insert or delete or update on emp2 for each row
begin
  if inserting then
   insert into emp2_log values(USER,'insert',sysdate);
  elsif updating then
   insert into emp2_log values(USER,'update',sysdate);
  elsif deleting then
   insert into emp2_log values(USER,'delete',sysdate);
  end if;
end;

--测试触发器工作状态

SQL> update emp2 set sal=sal*10 where sal<3000;

已更新7行。

SQL> select * from emp2_log
2 /

  UNAME                ACTION               ATIME
  -------------------- -------------------- --------------
  SCOTT                update               20-10月-08
  SCOTT                update               20-10月-08
  SCOTT                update               20-10月-08
  SCOTT                update               20-10月-08
  SCOTT                update               20-10月-08
  SCOTT                update               20-10月-08
  SCOTT                update               20-10月-08

已选择7行。

SQL>

----当一个表与字段与另一个表有约束时，使用触发器可以修改表，并不违反约束条件
  create table emp2_log
   (
    uname varchar2(20),
    action varchar2(20),
    atime date
   );

  create or replace trigger trig
   after update on dept
   for each row
  begin
   update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
  end;

--树状结构的展现-------
  create table article
  (
  id int primary key,
  pid int,
  cont varchar2(2048),
  isleaf int ,--1-not leaf 0-leaf
  alevlel number(2) --等级
  );

  insert into article values (1, 0, '蚂蚁大战大象', 0,0);
  insert into article values ( 2, 1, '大象被打趴下了',0, 1);
  insert into article values ( 3, 2, '蚂蚁也不好过', 1,2);
  insert into article values ( 4, 2, '瞎说', 1, 1);
  insert into article values ( 5, 4, '没有瞎说', 0,2);
  insert into article values ( 6, 2, '怎么可能', 0,1);
  insert into article values ( 7, 6, '怎么没有可能', 1,2);
  insert into article values ( 8, 6, '可能性是很大的', 1,2);
  insert into article values ( 9, 2, '大象进医院了',0,2);
  insert into article values ( 10, 9, '护士是蚂蚁', 1,3);

--用存储过程展现该表
  create or replace procedure p(v_pid article.pid%type,v_level binary_integer)
  is
   cursor c is select * from article where pid = v_pid;
  v_preStr varchar2(2048) :='';

  begin
   for i in 1..v_level loop
    v_preStr :=v_preStr||'****';
   end loop;
   for v_article in c loop
    dbms_output.put_line(v_preStr||v_article.cont);
     if(v_article.isleaf=0) then
     p(v_article.id,v_level+1);
     end if;
   end loop;
  end;

  ----------------------IT试验室----------------------------------------
1、Using Literal Character String
  Example:select empno,'''s name is ',ename from emp;
2、通配符的使用
  Example：SQL> select * from emp where ename like '%S%';
3、where条件的过滤
  Example:select * from emp where empno>50;
4、排序
  Example:select * from emp order by empno,ename desc; #注意首先是以empno升序排序，党相同时以ename降序排序
5、函数的使用
  Example：SQL> select lower(ename),upper(job), initcap(hiredate) from emp; #更改字符的显示大小写

  SQL> c /ename/empno/ #对函数的修改，将上述查询的ename修改为empno

  CONCAT(AAAA,BBBB) #链接连个字段
  SUBSTR(ENAME,1,5) #从ENAME字段中从左边第一位连续取5个字符，党为负数时表示从右边开始去
  LENGTH（AAAA）  #取长度
  instr（ename，‘G’） #查找字符G
   Example：SQL> select instr(ename,'G') from emp;
  SQL> select lpad(ename,15,'@'), rpad(ename,15,'$') from emp; #分别从左边和右边填充指定的字符，知道达到字符串个数
  SQL> select trim('H'from ename) from emp; #指定字符替换

round(44557.216,2),trunc(44557.216,2) #前者为四舍五入，后者为截取，函数的第二个参数为小树位数，如果为小数则表示取整数位数

SQL> select months_between(sysdate,HIREDATE) from emp; #去间隔月份

SQL> select add_months(sysdate,6),last_day(sysdate) from dual; #月数加法，和本月最后一天

字符转换：
SQL> select to_char(sysdate,'YYYY-month-DD HH:mi:SS') from dual;

SQL> select to_char(sysdate,'year-month-DD HH:mi:SS') from dual;

  SQL> select to_char(7684445.123,'$99999999.9999') from dual;
  TO_CHAR(7684445
  ---------------
    $7684445.1230

  引入本地货币符号：
     1* select to_char(7684445.123,'l99999999.9999') from dual
    TO_CHAR(7684445.123,'L99
    ------------------------
       ￥7684445.1230

SQL> select to_char(112445.285544,'999,999.999999') from dual;

   TO_CHAR(112445.
   ---------------
    112,445.285544

SQL> select to_date('1987-7-12','YYYY-MM-DD HH:MI:SS') from dual;

TO_DATE('1987-
--------------
12-7月 -87

空值的填充：
NVL(xxxx,0) #如果字段xxxx为空则用0 填充
NVL（xxxx,'It is null') #如果为空用指定的字符填充

NVL2(xxxxx,'It is not null','It is null') #如果不为空则用第一个替换，否则用第二个替换

select coalesce(field,xxx,'2006') from dual; #如果field为空，则用

Example：
select coalesce(null,null,'2006') from dual;
COAL
----
2006

case表达式的运用：
select ename,sal ,decode(sal,5000,sal*1.2) from emp; #decode函数如果sal为5000则sal*1.2只影响显示，不影响数据库中数据

Oracle中Join：
表的别名：
select t1.deptno,t1.ename,t2.deptno,t2.dname from
emp t1,dept t2 where t1.deptno=t2.deptno

  create table students
  (
  stu_id int primary key not null,
   stu_name varchar2(20),
  t_id int ,
  score int)

  create table teachers
  (
  t_id int,
  t_name varchar2(20),
  stu_id int)

  insert into students values(001,'唐龙',101,89);
  insert into students values(002,'王寅武',102,70);
  insert into students values(003,'徐宏韬',102,null);
  insert into students values(004,'罗光辉',102,70);
  insert into students values(005,'杜爽',102,70);
  insert into students values(006,'刘则川',null,70);
  insert into students values(007,null,102,70);

  insert into teachers values(101,'何艳艳',001);
  insert into teachers values(101,'何艳艳',002);
  insert into teachers values(101,null,null);
  insert into teachers values(101,'何艳艳',004);
  insert into teachers values(null,'唐文丽',001);
  insert into teachers values(102,'唐文丽',002);
  insert into teachers values(null,'唐文丽',null);
  insert into teachers values(102,'唐文丽',null);

表的实际情况：
SQL> select * from students;

    STU_ID STU_NAME                   T_ID      SCORE
---------- -------------------- ---------- ----------
         1 唐龙                        101         89
         2 王寅武                      102         70
         3 徐宏韬                      102
         4 罗光辉                      102         70
         5 杜爽                        102         70
         6 刘则川                                  70
         7                             102         70

已选择7行。

SQL> select * from teachers;

      T_ID T_NAME                   STU_ID
---------- -------------------- ----------
       101 何艳艳                        1
       101 何艳艳                        2
       101
       101 何艳艳                        4
           唐文丽                        1
       102 唐文丽                        2
           唐文丽
       102 唐文丽

已选择8行。

右外链接：

右外连接：
SQL> select s.stu_id,s.stu_name,s.t_id,s.score,t.t_name
2 from students s,teachers t
3 where s.stu_id(+)=t.stu_id;

    STU_ID STU_NAME                   T_ID      SCORE T_NAME
---------- -------------------- ---------- ---------- ------------------
         1 唐龙                        101         89 唐文丽
         1 唐龙                        101         89 何艳艳
         2 王寅武                      102         70 唐文丽
         2 王寅武                      102         70 何艳艳
         4 罗光辉                      102         70 何艳艳
                                                      唐文丽
                                                      唐文丽

已选择8行。

左外连接：

SQL> select s.stu_id,s.stu_name,s.t_id,s.score,t.t_name
2 from students s,teachers t
3 where s.stu_id=t.stu_id(+);

    STU_ID STU_NAME                   T_ID      SCORE T_NAME
---------- -------------------- ---------- ---------- --------------------
         1 唐龙                        101         89 何艳艳
         2 王寅武                      102         70 何艳艳
         4 罗光辉                      102         70 何艳艳
         1 唐龙                        101         89 唐文丽
         2 王寅武                      102         70 唐文丽
         5 杜爽                        102         70
         3 徐宏韬                      102
         6 刘则川                                  70
         7                             102         70

已选择9行。

（注意二者之间的比较）

无条件链接：cross join
useage：
seelct * from students cross join teachers; #select * from students,teachers两者结果相同

left outer join:
SQL> select s.stu_name,s.stu_id,s.score
2 from students s
3 left outer join teachers t
4 on (s.stu_id=t.stu_id);

STU_NAME                 STU_ID      SCORE
-------------------- ---------- ----------
唐龙                          1         89
王寅武                        2         70
罗光辉                        4         70
唐龙                          1         89
王寅武                        2         70
杜爽                          5         70
徐宏韬                        3
刘则川                        6         70
                              7         70

已选择9行。

right outer join:
SQL> run
1 select s.stu_name,s.stu_id,s.score
2 from students s
3 right outer join teachers t
4* on (s.stu_id=t.stu_id)

STU_NAME                 STU_ID      SCORE
-------------------- ---------- ----------
唐龙                          1         89
唐龙                          1         89
王寅武                        2         70
王寅武                        2         70
罗光辉                        4         70

已选择8行。

full outer join:

SQL> run
1 select s.stu_name,s.stu_id,s.score
2 from students s
3 full outer join teachers t
4* on (s.stu_id=t.stu_id)

STU_NAME STU_ID SCORE
-------------------- ---------- ----------

已选择12行。

聚合函数：
0】、group by必须和聚合函数一起使用
1】在goup by中分组函数中select函数中的字段必须要符合group by中的分组，既能够分组
Example:
select stu_id,stu_name,t_id ，avg（score） from students group by t_id;
2】、group by语句可以跟几个字段
SQL> select deptno,sum(sal) from emp group by deptno,sal;

    DEPTNO   SUM(SAL)
---------- ----------
        10       5000
        20        800
        20       2975
        10       2450
        30       2500
        20       6000
        30        950
        20       1100
        10       1300
        30       1600
        30       2850

DEPTNO SUM(SAL)
---------- ----------
30 1500

已选择12行。
3】、where条件中不能出现聚合函数，如噶uoxuyao请使用having函数
Example：
SQL> select deptno,sum(sal) from emp where avg(sal)>2000 group by empno;
select deptno,sum(sal) from emp where avg(sal)>2000 group by empno
*
第 1 行出现错误:
ORA-00934: 此处不允许使用分组函数
Example：
SQL> select deptno,sum(sal) from emp group by deptno having avg(sal)>2000;

    DEPTNO   SUM(SAL)
---------- ----------
        20      10875
        10       8750

group by、having、where、order by同时使用的格式：
SQL> select deptno,sum(sal) from emp where sal is not null group by deptno having
avg(sal)>2000 order by deptno desc;

    DEPTNO   SUM(SAL)
---------- ----------
        20      10875
        10       8750

对于不确定条件的SQL语句：
SQL> select &first_field,&sec_field from &table_name where &condition;
输入 first_field 的值: deptno
输入 sec_field 的值: sal
输入 table_name 的值: emp
输入 condition 的值: deptno>10
原值 1: select &first_field,&sec_field from &table_name where &condition
新值 1: select deptno,sal from emp where deptno>10

    DEPTNO        SAL
---------- ----------
        20        800
        30       1600
        30       1250
        20       2975
        30       1250
        30       2850
        20       3000
        30       1500
        20       1100
        30        950
        20       3000

已选择11行。

也可以提前给变量赋值：

Example:
  SQL> define a=20;
  SQL> select * from emp where deptno=&a;
  原值    1: select * from emp where deptno=&a
  新值    1: select * from emp where deptno=20

如果要查看一个已经定义的变量的值：define a #在定义的时候不需要&符号，在引用时需要&符号
取消对一个变量的定义：undefine a

双&&符号，只需要定义一次
Example：
  SQL> select &&field ,deptno from emp where &field like '%S%';
输入 field 的值: ename
原值    1: select &&field ,deptno from emp where &field like '%S%'
新值    1: select ename ,deptno from emp where ename like '%S%'

ENAME          DEPTNO
---------- ----------
SMITH              20
JONES              20
SCOTT              20
ADAMS              20
JAMES              30

iSQLPlus的使用：
设置查询表的页眉和页脚：
Example：
  ttitle 'The Oracle 10g Heading'
  btitle 'The Ooracle 10g Footer'
设置表的显示格式：
Example：
  column sal justify left format '$99,999.999'  --设置sal字段左对齐并格式化为所指字符串格式
  select * from emp,dept

数据操纵语句：
表的备份或者表的复制：
  拷贝一个表的结构到另一个表：
  SQL> run
    1 create table test
    2 as
    3* select * from students where stu_id=37

表已创建。
拷贝一个表的数据到另一个表：

SQL> insert into test
2 select * from students
3 where stu_id=1;

已创建 1 行。

update语句：
SQL> run
1 update test
2 set score=80
3* where stu_id=37

已更新 1 行。

更改表的结构或者给表添加约束：

SQL> alter table test
2 add constraint chk_score check(score>60);

表已更改。
党插入不满足约束条件的数据就会报错：
SQL> insert into test
2 (stu_id,score)
3 values(23,35);
insert into test
*
第 1 行出现错误:
ORA-02290: 违反检查约束条件 (SCOTT.CHK_SCORE

增加约束条件：
create table father(
f_id integer,
c_id integer,
f_name varchar2(20),
f_age int)
/

create table child(
c_id integer,
f_id integer,
c_name varchar(20),
c_sex char(1) default 'M'
)
/

然后更改表的约束，增加主键：

SQL> alter table father
2 add constraint pk_id primary key(f_id);

表已更改。
增加外键约束：
SQL> run
1 alter table child
2* add constraint fk_id foreign key(f_id) references father(f_id)

表已更改。

insert into father values(100,1001,'张三',42);
insert into father values(200,2001,'李四',50);

insert into child values(2001,200,'小李','M');
insert into child values(1001,100,'小张','F');
insert into child values(1002,100,'小张','M');
insert into child values(3002,300,'xxx','M'); #违反外键约束条件

SQL> insert into child values(3002,300,'xxx','M');
insert into child values(3002,300,'xxx','M')
*
第 1 行出现错误:
ORA-02291: 违反完整约束条件 (SCOTT.FK_ID) - 未找到父项关键字

当一个表的记录中字段又是另一个表的外键时，如果存在记录则不能删除含有外键的那个数据

SQL> delete from father where f_id=100;
delete from father where f_id=100
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FK_ID) - 已找到子记录

插入时强制插入默认值
SQL> run
1 insert into child
2 (c_id,f_id,c_sex)
3* values(2002,200,default)

已创建 1 行。

创建表和表的权限：
1】、在Oracle中每个用户都有自己的空间限额，对于DBA要查看其他用户的表，必须需要scame.tablename
alter对表的修改：

SQL> create table tt1
2 (id int default 110,
3 name varchar2(20));

表已创建。

SQL> alter table tt1
2 add (address.varchar2(40),tel varchar(14));
add (address.varchar2(40),tel varchar(14))
*
第 2 行出现错误:
ORA-01748: 此处只允许简单的列名

SQL> drop table tt1;

表已删除。

SQL> create table tt1
2 (id int default 110,
3 name varchar2(20));

表已创建。

SQL>
SQL> alter table tt1
2 add (address varchar2(40),tel varchar2(14));

表已更改。

SQL> alter table tt1
2 modify address varchar(60);

表已更改。

SQL> alter table tt1
2 drop column tel;

表已更改。

数据表的更名：
SQL> rename tt1 to kk;

表已重命名。

一般一张表的所有列删除的同时也在向数据库中添加日志记录，影响了数据操纵的效率使用
TRUNCATE语句可以无需想数据库添加日志删除表中内容，但不可rollback（这与delete不同之处）

SQL> truncate table kk;

表被截断。

SQL> select * from kk;

未选定行

表的注释：
Example：
SQL> comment on table students
2 is 'This is 2006-2008 students score and shcedule';

注释已创建。

数据完整性约束：CONSTRAINT
-NOT NULL
-UNIQUE
-PRIMARY KEY
-FOREIGN KEY
-CHECK
举例说明：
（要姓名唯一，姓和名可以不唯一但组合唯一—）
SQL> CREATE TABLE TEST1
2 (ID INT NOT NULL,
3 LNAME VARCHAR(20),
4 FNAME VARCHAR(20),
5 CONSTRAINT UK_TEST1_NAME UNIQUE(LNAME,FNAME));

表已创建。

如果A表一个字段是B表的外键，那么B表中的数据的A有关联时不能删除A表的数据
SQL> delete from father where f_id =100;
delete from father where f_id =100
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FK_ID) - 已找到子记录

如果非要删除，则应连子集也应一并删除，
The syntax for creating a foreign key in an ALTER TABLE statement is:

ALTER TABLE table_name
add CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE;

For example:

ALTER TABLE products
add CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;

删除约束条件：
alter table tablename
drop consraint ck_XXX ;
or:
alter table tablename
drop constraint pk_YYY cascade #当删除该表的数据时，和其相关其他表的依附数据一并删除

暂时将某个约束条件disable掉：
alter table tablename
disable constraint pk_XXXX_YYYY;

恢复以前的约束条件：
alter table tablename
enable constraint pk_XXX_YYYY;

删除某这个字段以及其相关的约束条件：
SQL> alter table child
2 drop column f_id cascade constraint;

表已更改。

利用数据字典查找某个表的约束：
select constraint_name,constraint_type,search_condition from user_constraints
where table_name='child'

SQL> select constraint_name,column_name from user_cons_columns
2 where table_name='child';

视图：

创建视图：
SQL> create view v$_emp
2 as
3 select * from emp;

视图已创建。

对视图的操作都是直接操作的基表，同样对基表的操纵他也会影响视图的结果
（前提是不更改表的结构也就是说不得更改创建视图时的字段）
SQL> create table hh
2 (id int default 90,
3 loc varchar2(20),
4 tel varchar2(20));

表已创建。

SQL> insert into hh
2 values(99,'shaanxi','34556666');

已创建 1 行。

SQL> insert into hh values(null,'ankang','122');

已创建 1 行。

SQL> select * from hh;

        ID LOC                  TEL
---------- -------------------- --------------------
        99 shaanxi              34556666
           ankang               122

SQL> insert into hh (loc,tel) values('zhejiang','3838838');

已创建 1 行。

SQL> select * from hh;

        ID LOC                  TEL
---------- -------------------- --------------------
        99 shaanxi              34556666
           ankang               122
        90 zhejiang             3838838

SQL> create or replace view v$_temp
2 as
3 seelct * from hh;
seelct * from hh
*
第 3 行出现错误:
ORA-00928: 缺失 SELECT 关键字

SQL> l23
SP2-0226: 行号无效
SQL> l3
3* seelct * from hh
SQL> c /seelct/select/
3* select * from hh
SQL> run
1 create or replace view v$_temp
2 as
3* select * from hh

视图已创建。

SQL> update view v$_temp
2 set id=1 where id is null;
update view v$_temp
*
第 1 行出现错误:
ORA-00903: 表名无效

SQL> l1
1* update view v$_temp
SQL> /view//
SP2-0042: 未知命令 "/view//" - 其余行忽略。
SQL> l1
1* update view v$_temp
SQL> c /view/ /
1* update v$_temp
SQL> run
1 update v$_temp
2* set id=1 where id is null

已更新 1 行。

SQL> select * from v$_temp;

        ID LOC                  TEL
---------- -------------------- --------------------
        99 shaanxi              34556666
         1 ankang               122
        90 zhejiang             3838838

SQL> select * from hh;

        ID LOC                  TEL
---------- -------------------- --------------------
        99 shaanxi              34556666
         1 ankang               122
        90 zhejiang             3838838

SQL> update hh
2 set id=20 where id=1;

已更新 1 行。

SQL> select * from v$_temp;

        ID LOC                  TEL
---------- -------------------- --------------------
        99 shaanxi              34556666
        20 ankang               122
        90 zhejiang             3838838

创建一个视图并带有附加条件，对视图操作加以限制
SQL> create or replace view v$_temp
2 as
3 select * from hh
4 where id between 20 and 100
5 with check option;

视图已创建。
SQL> run
1 update   v$_temp
2* set id=2000 where id=99
update   v$_temp
         *
第 1 行出现错误:
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规

创建一个只读权限的视图
SQL> create or replace view v$_temp
2 as
3 select * from hh
4 where id between 20 and 100
5 with read only;

视图已创建。

对于一个表或者视图都存在隐含列rowid、rownum

------------------序列和索引--------------------------------------
创建一个序列的基本合适：
usuage:
CREATE SEQUENCE sequence
  INCREMENT BY n
  START WITH x
  MAXVALUE y |NOMAXVALUE
  MINVALUE Z |NOMINVALUE
  CYCLE | NOCYCLE
  CACHE m |NO CACHE;

Example:SQL> select sq.nextval from dual;

NEXTVAL
----------
1

获取当前序列的当前值： SQL> select sq.currval from dual

      CURRVAL
   ----------
     2

创建索引：  #默认情况一张表的主键系统都会自动创建索引
usuage:
create index index_name
on table_name(field);

同义词：当一个表的命名太长或者要经常访问其他用户的表时，采用同义词可以简化
usuage:
create public synonym t1
for scott.students;

SQL> conn sys/root as sysdba;
已连接。
SQL> create public synonym t1
2 for scott.students;

同义词已创建。

删除同义词：

SQL> drop public synonym t1; --public访问权限应与创建时一致

同义词已删除。

----------------------------------授予其他用户的权限和撤销权限-----------------------

SQL> create user acer
2 identified by acer;

用户已创建。

SQL> grant create session to acer;
授权成功。
SQL> conn acer/acer
已连接。

SQL> run
1* grant create table,create view,create sequence to acer with admin option

授权成功。

SQL>

创建Role，即把权限封装，然后在将Role授权给其他用户（只有拥有表权限的人
才可以把权限授予其他用户，即使DBA都不能把其他用户的权限授予其他用户）
SQL> create role guest;

角色已创建。
SQL> grant select on scott.students to guest --这种授权方式必须一次分布操作不能同时完成

授权成功。
SQL> grant delete on scott.father to guest with admin option;

授权成功。
SQL> grant guest to acer;

授权成功。

SQL> alter user acer --修改用户登陆密码
2 identified by root;

用户已更改。

with admin option表示该用户可以再授权给其他用户

SSQL> create user zhangsan identified by zhangsan;

用户已创建。

SQL> grant create session to zhangsan with admin option;

授权成功。

SQL> conn zhangsan/zhangsan
已连接。
SQL> grant create session to admin;

授权成功。

权限的撤销：revoke
SQL> revoke guest from acer;

撤销成功。

将某用户已售出的权限一并收回：
SQL> grant guest to acer with admin option;

授权成功。

SQL> revoke guest from acer cascade constraints;

撤销成功。

-------------------------集合操作--------------------------------------
--union
--union all
--intersect
--minus
union:
usuage:
对应的字段必须兼容，并且字段个数相同，具有相同的焦急部分只显示一部分，即去交集
SQL> select f_id,f_name from father
2 union
3 select c_id,c_name from child;

      F_ID F_NAME
---------- --------------------
       100 张三
       200 李四
      1001 小张
      1002 小张
      2001 小李
      2002

已选择6行。

union all:
usuage:显示所有记录，没有渠道重复记录，也没有排序

SQL> select c_id from father
2 union all
3 select c_id from child;

      C_ID
----------
      1001
      2001
      2001
      1001
      1002
      2002

已选择6行。

intersect:去交集
usuage：
SQL> select ename,deptno from emp where deptno=30
2 intersect
3 select ename,deptno from emp where ename like 'B%';

ENAME DEPTNO
---------- ----------
BLAKE 30

minus:去掉结果集中交集部分
usuage：
SQL> select ename,deptno from emp where deptno=30
2 minus
3 select ename,deptno from emp where ename like 'B%';

ENAME          DEPTNO
---------- ----------
ALLEN              30
JAMES              30
MARTIN             30
TURNER             30
WARD               30
两只之间比较：
SQL> select ename,deptno from emp where ename like 'B%' or deptno=30;

ENAME          DEPTNO
---------- ----------
ALLEN              30
WARD               30
MARTIN             30
BLAKE              30
TURNER             30
JAMES              30

已选择6行。

-------Datetime Functions----------------------------
美国东部时区与中国的时差：
SQL> run
1* select tz_offset('US/Eastern') from dual

TZ_OFFS
-------
-05:00

-------Advanced Subqueries--------------------------
1】、多栏位的比较子查询：
SQL> select * from emp
2 where(ename,deptno) in
3 (select ename,deptno from emp where deptno=30);

SQL> select t1.empno,t1.deptno,t2.dname
2 from dept t2,(select empno,deptno from emp where deptno between 20 and 60) t1;
2】、关联子查询：内查询条件需要调用外查询
SQL> select ename,deptno,sal
2 from emp outer
3 where sal>(
4 select avg(sal) from emp where outer.sal between 3000 and 5000);

ENAME          DEPTNO        SAL
---------- ---------- ----------
SCOTT              20       3000
KING               10       5000
FORD               20       3000

USING EXISTS Operateor
SQL> select * from emp outer
2 where exists(select ename,deptno from emp inner where outer.deptno=inner.deptno);

分层以及树形结构
SQL> run
1 select empno,ename,mgr from emp
2 start with empno>7900 --表示其实条件
3* connect by prior mgr=empno

     EMPNO ENAME             MGR
---------- ---------- ----------
      7902 FORD             7566
      7566 JONES            7839
      7839 KING
      7934 MILLER           7782
      7782 CLARK            7839
      7839 KING

已选择6行。

系统中存在伪劣level，表示等级
SQL> run
1 select level, empno,ename,mgr from emp
2 start with empno>7900
3* connect by prior mgr=empno

     LEVEL      EMPNO ENAME             MGR
---------- ---------- ---------- ----------
         1       7902 FORD             7566
         2       7566 JONES            7839
         3       7839 KING
         1       7934 MILLER           7782
         2       7782 CLARK            7839
         3       7839 KING

已选择6行。

多行插入到不同的表格：
SQL> create table emp_sal_mgr --为下面做准备，复制表结构
2 as
3 select empno,sal,mgr from
4 emp where empno=0;

表已创建。

SQL> create table emp_name --为下面做准备，复制表结构
2 as
3 select empno,ename
4 from emp where empno=0;

表已创建。

SQL> run
1 insert all
2 into emp_name(empno,ename) values(empno,ename)
3 into emp_sal_mgr(empno,sal,mgr) values(empno,sal,mgr)
4 select empno,ename,sal,mgr
5 from emp
6* where empno>7900

已创建4行。

条件插入
SQL> insert all
2 when ename like '%B%' then
3 into emp_name values(empno,ename)
4 when sal>500 then
5 into emp_sal_mgr values(empno,sal,mgr)
6 select empno,sal,ename,mgr from emp1
7 where empno is not null;

已创建15行。

多条件的插入语句：
SQL> insert first
2 when ename like'B%' then
3 into emp_sal_mgr values(empno,sal,mgr)
4 when sal<7000
5 then into emp_name values(empno,ename)
6 else
7 into emp_sal_mgr values(empno,sal,mgr)
8 select empno,ename,mgr,sal
9 from emp1;

已创建14行。

创建表的同时创建索引
SQL> create table test_index_table
2 (id int not null primary key using index
3 (create index test_index on test_index_table(id)),
4 lname varchar2(20),
5 fname varchar2(20));

表已创建。

------------------------------Oracle系统---------------------------------------------
1】、更改系统语言：

SQL> alter session set nls_language=american;

Session altered.
2】、设置系统的缓存区大小
SQL> alter system set db_cache_size=50M;
alter system set db_cache_size=50M
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache

3】、口令管理：
SQL> conn sys/root as sysdba;
已连接。
SQL> grant sysdba to admin;

授权成功。

SQL> revoke sysdba from admin;

撤销成功。

SQL> select * from v$pwfile_users; --查看系统有多少个具有的DBA权限的用户

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

4】、在cmd下指定sys管理员的密码
usuage:
C:/Users/acer>orapwd
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> nosys
dba=<y/n>

where
    file - name of password file (mand),
    password - password for SYS (mand),
    entries - maximum number of distinct DBA,
    force - whether to overwrite existing file (opt),
    nosysdba - whether to shut out the SYSDBA logon (opt for Database Vault only
).
There are no spaces around the equal-to (=) character.

C:/Users/acer>xxxx
说明xxx：orapwd file=ORACLE_HOME/dbs/orapwdU15
password=new_password entries=5 --5表示系统最多具有5个管理员

5】、关闭和启动一个数据库

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 289406976 bytes
Fixed Size                  1290184 bytes
Variable Size             234881080 bytes
Database Buffers           46137344 bytes
Redo Buffers                7098368 bytes
数据库装载完毕。
数据库已经打开。
SQL>