oracle数据库学习笔记

查看表结构 ： desc emp;

虚表 dual 如 select sysdate from dual;

关于给字段取别名：

select 字段 别名 from 表;

select 字段 as 别名 from 表;

如果别名需要用到空格：select 字段 "含空格别名" from 表;

含有空值的数学表达式结果都是空值。

字符串连接符： ||  如果连接的字符串中含有单引号，使用 2个单引号即可，如   select ename||'abc''def' from emp;

distinct

消除重复的值 select distinc 字段 from 表;

where

筛选: 

基本筛选：> < = <>is (not) nullin(...)/not in(...)...and/or...日期按照格式...where hiredate>'03-12月-81'红色部分必须格式一致

模糊查询：like

...like 'ALL';

...like '%ALL';取出所有以 ALL 结尾的。

...like 'ALL%';取出所有以 ALL 开头的。

...like '%ALL%';取出所有含有ALL的。

...like '_A%';取出所有第二个是字符A的，一个_代表一个字符。

如果检索包含 % 的字符串，使用转义符 \ ：...like '%\%%';转义符可以指定：...like '%$%%' escape '$';

order by

排序

select 字段 from 表 order by 字段 desc/asc; desc：降序 asc：升序，不写表示升序。where 在order by 前面：select 字段 from 表 where ... order by ...

可以按照多个字段排序：...order by 字段1 desc/asc,字段2 desc/asc...;表示的意思是，当字段1相同时，数据按照字段2指定顺序排列

函数：可以用在检索的字段，可以用在where条件中

如：

转换成小写 lower()：select lower(ename) from emp;select ename from emp where lower(ename) like...

字符串截取substr(字段名,初始位置,截取个数):select substr(ename,2,1) from emp;其中初始位置从1开始，不是从0开始

字符chr(数字) ：select chr(65) from dual;结果为 A；相反，ascii('A'),结果为65

四舍五入round():round(23.653)结果为24，round(23.653,2)结果为23.65，round(23.653,1)结果为23.7，round(23.653,-1)结果为20

函数有很多，可以不记得具体使用，但是需要知道有哪些函数

其中，需要牢记的函数：

to_char()

to_date()

to_number()

nvl()

空值处理nvl(字段,0):如果字段为空，用0代替

max() 

min()

avg()

组合函数：

sum()

count()可以和distinct一起用：count(distinct 字段)

group by

当需要用到组合函数時，同时又需要分类计算，需要用到group by：例如班级平均成绩，进行科目分类。

having

对分组进行限制，在goup by之后

总结一下sql语句的顺序：

select * from 表 

where ...

group by...

having...

order by...

子查询：将一个查询结果用括号括起来当作一个表来使用。

连接查询：

92年标准： select ... from 表1,表2...where ...

存在一个不易读的问题，where后面连接的条件和逻辑过滤条件混合在一起。

99年建立新的标准：使用 join

各种复杂的查询，可以通过一步一步从内而外来拆解成简单的查询

create - insert - update - delete

创建用户：

exp 导出数据

drop user hcz;

create user hcz identified by hcz default tablespace user quota 10m on users;

grant create session,create table,create view to hcz;

imp 导入数据

关于rownum：select * from emp where  rownum </<= 5;只能跟<   or  <=

解决办法：用子查询 select * from (select rownum r,e.* from emp e) where r<10

insert

insert into 表 values (...);

insert into 表(....) values (...);

insert into 表 select ....; 检索的整个结果插入

update

update 表 set 字段1 = ...,字段2 = ... where ...

delete

delete from 表 where... 不加where条件删除整个表的数据，删除表结构：drop table 表

事物transaction：

起始于一个DML语句，结束于commit、rollback、DDL/DCL语句自动commit、正常断开自动commit、异常断开自动rollback

建表

create table class(       id number(4) primary key,       name varchar2(20) not null)create table stu(       id number(6),       name varchar2(20) constraint stu_name_nn not null, --非空约束       sex number(1),       age number(3),       sdate date,       grade number(2) default 1,       class number(4),       email varchar2(50),       constraint stu_class_fk foreign key (class) references class(id), --外键       constraint stu_id_pk primary key (id),-- 主键       constraint stu_name_email_uni unique(email,name), -- 复合唯一约束）

其中5中约束：not null、unique、primary key、foreign key、check
修改表结构：

alter table stu add(addr varchar2(100));alter table stu modify(addr varchar2(150));

修改约束：一般是先删掉，再重新写约束

alter table stu drop constraint stu_class_fk;alter table stu add constraint stu_class_fk foreign key (class) references class(id)

数据字典表：查看当前用户下有哪些表，都有什么视图等等之类的信息。
user_tables
user_views
user_constraints
.....
这些表的名字存在于 dictionary 表中。dictionary 中有table_name 和 comments 2个字段。


索引 index
只是为了访问字段更加快捷，只是访问快。
create index 索引名 on 表（字段）
drop index 索引名
查找索引:
select * from user_indexes
什么时候建立索引？
不要轻易建立索引：影响其他操作的效率，并且会占用空间。


视图view
视图就是一个子查询。
create view 视图名 as select .....
查找视图
select * from user_views
视图可以简化查询，但是不便于维护。如果表结构发生变化，视图也需要修改。
视图里面的数据是可以修改的，但是不建议在视图里面修改数据。


序列sequence
oracle 特有

create sequence seq;select seq.nextval from dual;--1select seq.nextval from dual;--2select seq.nextval from dual;--3

是线程安全的
语法：

create sequence emp_sequenceINCREMENT BY 1 -- 每次加几个START WITH 1 -- 从1开始计数NOMAXVALUE -- 不设置最大值NOCYCLE -- 一直累加，不循环CACHE 10;

一旦定义了emp_sequence，你就可以用CURRVAL，NEXTVAL
CURRVAL=返回sequence的当前值
NEXTVAL=增加sequence的值，然后返回sequence值


2道经典面试题:
1.有三个表s，c，sc
S（sno，sname）代表（学号，姓名）
C（cno，cname，cteacher）代表（课号，课名，教师）
SC（sno，cno，scgrade）代表（学号，课号，成绩）
问题：
1.找出没有选过“黎明”老师的所有学生姓名（简单）
2.列出2门以上（含2门）不及格学生姓名及平均成绩（group by...having..）
3.即学过1号课程，也学过2号课程的所有学生姓名(学过1的学生 in （学过2的学生）)


2.一个简单的表table ，包含100条以上的信息，其中包括：
产品 颜色 数量
产品1 红色 123
产品1 蓝色 126
产品2 蓝色 103
产品2 红色 null
产品2 红色 89
产品1 红色 203
..........
请用sql完成以下问题：
1.按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量
2.按产品分类，将数据按照下列方式进行统计显示
产品 红色蓝色
select 产品,
sum(case when 颜色 = '红色' then 数量 end) as 红色,
sum(case when 颜色 = '蓝色' then 数量 end) as蓝色
from table group by 产品


数据库设计三范式：
三范式虽然很有用，但是还是具体问题具体对待。
1. 要有主键，列不可分
2.当一个表含有复合主键時，非主键的数据不能依赖部分主键（必须全部依赖），即不能存在部分依赖
3.属性不依赖于其他非主属性


PL/SQL  oracle特有
也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）
PL/SQL块由四个基本部分组成：声明、执行体开始、异常处理、执行体结束

set serveroutput on;begin  dbms_output.put_line('hello world'); --输出hello worldend;declare  v_name varchar2(20);begin  v_name := 'myname';  dbms_output.put_line(v_name); -- 输出 mynameend;

异常处理

declare  v_num number := 0;begin  v_num := 2/v_num;  dbms_output.put_line(v_num);exception  when others then -- 没有写原来的情况，那么其他情况（others）就包括所有情况    dbms_output.put_line('error');end;

--table变量类型 table表示的是一个数组，指定数组的类型必须重新定义一种数组类型

declare  type type_table_emp_empno is table of emp.empno%type index by binary_integer;--type表示定义一种类型，名为type_table_emp_empno,类型为emp.empno，下标为binary_integer类型    v_empnos type_table_emp_empno;-- 定义一个变量v_emonos，类型为type_table_emp_empnobegin  v_empnos(0) := 7369;  v_empnos(2) := 7839;  v_empnos(-1) := 9999; --oracle中下标可以是负数  dbms_output.put_line(v_empnos(-1));end;

record 变量类型：类似于java中的类

declare  type type_record_dept is record  -- 声明类型    (      deptno dept.deptno%type,      dname dept.dname%type,      loc dept.loc%type    );    v_temp type_record_dept; -- 声明该类型的变量begin    v_temp.deptno := 50; -- 成员变量赋值    v_temp.dname := 'aaa';    v_temp.loc := 'bj';    dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname); --取值end;

使用%rowtype声明record变量

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

SQL语句的运用

declare  v_ename emp.ename%type;  v_sal emp.sal%type;begin  select ename,sal into v_ename,v_sal from emp where empno = 7369;--不用游标的时候，select语句里面必须有into，select需有且仅有一条数据  dbms_output.put_line(v_ename || ' ' || v_sal);end;

-- 表变量

declare  v_emp emp%rowtype;begin  select * into v_emp from emp where empno = 7369;  dbms_output.put_line(v_emp.ename);end;insertdeclare  v_deptno dept.deptno%type := 50;  v_dname dept.dname%type := 'aaa';  v_loc dept.loc%type := 'bj';begin  insert into dept values(v_deptno,v_dname,v_loc);  commit;end;updatedeclare  v_deptno emp.deptno%type := 50;  v_count number;begin  update emp set sal = sal/2 where deptno = v_deptno;  dbms_output.put_line(sql%rowcount || '条数据被影响');--sql表示刚刚执行的sql语句，rowcount表示被影响的语句条数  commit;end;

在输出中文的时候，可能会出现乱码，这是工具的原因，在命令行中则不会出现这个问题，解决的办法：
1.查看注册表：开始-运行-输入regedit-回车进入注册表，依次单击HKEY_LOCAL_MACHINE--->SOFTWARE ---> ORACLE--->KEY_OraDb11g_home1（不同版本的Oracle显示的都不太一样，但都会包含home这个单词），找到“NLS_LANG”，查看数值数据是否为：“SIMPLIFIED CHINESE_CHINA.ZHS16GBK”，如果不是就将它设置为“SIMPLIFIED CHINESE_CHINA.ZHS16GBK。”我的就是这个。没有修改
2.设置完注册表后，接下来设置我们的环境变量，计算机（右键） --->属性--->高级系统设置--->高级--->环境变量--->新建，个人建议新建用户变量，变量名输入：“NLS_LANG”，变量值输入：“SIMPLIFIEDCHINESE_CHINA.ZHS16GBK”。重启pl/sql developer，登录時可能会报错：ora-12705:cannot access NLS data ...什么的，需要把之前的乱码那条数据删掉就好了。

createbegin       execute immediate 'create table T(name varchar2(20) default ''abc'')';end;

分支循环语句
if

--if 取出7369的薪水，如果小于1200，则输出low，如果小于2000则输出middle，否则highdeclare     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 -- 注意：elsif 写法，不是 else if          dbms_output.put_line('middle');     else-- else 后面没有then          dbms_output.put_line('high');      end if;-- 有分号 注意end;

循环

declare      i binary_integer := 1;begin      loop        dbms_output.put_line(i);        i:=i+1;        exit when (i >=11);      end loop;end; 

相当于java中的do-while循环，结果是 1 2 3 4...10

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

相当于java中的while循环，结果是1 2 3 4...11
for循环

begin       for k in 1..10 loop     -- 1-10循环输出           dbms_output.put_line(k);       end loop;              for k in reverse 1..10 loop   -- 10-1循环输出           dbms_output.put_line(k);       end loop;end;

游标（重点）
oracle里面遍历数据，类似于java中的迭代器Iterator

declare       cursor c is              select * from emp;       v_emp c%rowtype;begin       open c;       fetch c into v_emp; --fetch 取得 的意思       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;

需要说明一下，游标属性：ISOPEN  FOUND/NOTFOUND ROWCOUNT

declare       cursor c is               select * from emp;       v_emp emp%Rowtype;begin       open c;       fetch c into v_emp; --先fetch，否则c%found一直是false       while(c%found) loop         dbms_output.put_line(c%rowcount || v_emp.ename); --输出结果为 1XXX 2XXX 3XXX 4XXX...14XXX         fetch c into v_emp;        end loop;       close c;end; 

最方便的遍历方法for循环:不用定义变量了，不用手动close了。这里的for循环类似于java的foreach

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;begin       for v_temp in c(30,'CLERK') loop           dbms_output.put_line(v_temp.ename);       end loop;end;

大多数的游标是只读的，但是也是可以修改的

declare        cursor c is              select * from emp for update; -- for updatebegin       for v_temp in c loop           if(v_temp.sal<2000) then             update emp set sal = sal*2 where current of c; --where条件注意 当前的           elsif(v_temp.sal=5000) then-- =用于判断，既不是‘：=’，也不是‘==’             delete from emp where current of c;           end if;       end loop;       commit; -- 注意提交end;

存储过程procedure
由于存在需要直接调用某个程序的需求，所以产生了存储过程。

create or replace procedure p is       cursor c is              select * from emp for update;begin       for v_emp in c loop           if(v_emp.deptno = 10) then              update emp set sal = sal+10 where current of c;            elsif(v_emp.deptno = 20) then              update emp set sal = sal+20 where current of c;            else              update emp set sal = sal+30 where current of c;            end if;        end loop;        commit;end;

调用procedure的方式

exec p;

或者

begin     p;end;

帯参数的存储过程

create or replace procedure       p(v_a in number,v_b number,v_ret out number,v_temp in out number)  -- in输入参数，out输出参数，不写默认输入   isbegin   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;

往往在创建存储过程之中，代码会有错误，但是oracle只是提示：警告：创建的过程带有编译错误，此时可以查看错误：

show error;

删除存储过程

drop procedure p;

functioncreate or replace function sal_tax(v_sal number) return numberisbegin       if(v_sal<2000) then                      return 0.10;       elsif(v_sal<2750) then                      return 0.15;       else                      return 0.20;       end if;end;select ename,sal_tax(sal) from emp;

触发器

create or replace trigger trig       after insert or delete or update on emp for each row  --after表示操作之后，before表示之前；for each row表示每一行，可以不写就只会出发一次begin       if inserting then          insert into emp_log values(USER,'insert',sysdate);       elsif updating then           insert into emp_log values(USER,'update',sysdate);       elsif deleting then           insert into emp_log values(USER,'delete',sysdate);       end if;end;

有个小问题：

update dept set deptno=99 where dept=10;--这个操作是不能执行的，但是可以写一个触发器，这条语句就可以用了：create or replace trigger trig after update on dept for each rowbegin       update emp set deptno = :NEW.deptno where deptno = :OLD.deptno; -- :NEW 新状态 :OLD 旧状态end;update dept set deptno = 99 where deptno = 10;--可以执行

但是这种做法不常用，除非被逼无奈使用


思考：
树状结构的存储：

create table article(       id number primary key, --主键       content varchar2(4000), -- 内容       pid number, --父节点主键       isleaf number(1), --冗余字段 便于开发 ：是否是叶子节点 0非叶子节点 1叶子节点       alevel number(2) --冗余字段 便于开发  ：节点等级 ，根节点0，根节点的子节点1...以此类推);

字段的说明只是个例子

                                             
        0        0           
	
				
	
					   
oracle数据库学习笔记
	  	   
Oracle数据库学习笔记
	  	   
Oracle数据库学习笔记
	  	   
Oracle数据库学习笔记
	  	   
Oracle数据库学习笔记
	  	   
oracle数据库学习笔记
	  	   
oracle数据库学习笔记
	  	   
Oracle数据库学习笔记
	  	   
oracle数据库 学习笔记
	  	   
Oracle数据库学习笔记
	  	   
oracle数据库学习笔记(一)
	  	   
Oracle数据库开发学习笔记
	  	   
oracle数据库基础知识学习笔记
	  	   
Oracle数据库程序设计学习笔记
	  	   
oracle数据库命令学习笔记
	  	   
ORACLE数据库-学习笔记1
	  	   
Oracle数据库学习笔记一
	  	   
Oracle数据库学习笔记二
	     		  
	  	   
爬虫奇遇记——如何按照标签的文本内容来抓取
	  	   
SSH框架总结
	  	   
JZOJ4983. 网络
	  	   
Linux简单笔记
	  	   
《五朵金花》电影影评
	  	   
oracle数据库学习笔记
	  	   
3572: [Hnoi2014]世界树
	  	   
centos7 vscode c++ 扩展不生效处理
	  	   
城市交通
	  	   
3631: [JLOI2014]松鼠的新家
	  	   
MAC上SSDB使用指南
	  	   
GG   数字三角形顺推。。。
	  	   
锁定 Linux 用户虚拟控制台的好命令–volck
	  	   
WebApi接口