学习、改良、极致
随笔- 31 文章- 0 评论- 50
https://www.cnblogs.com/hjwublog/category/792609.html
随笔分类 - oracle：sql基础
sql/oracle
oracle（sql）基础篇系列（五）——PLSQL、游标、存储过程、触发器
摘要: PL/SQL PL/SQL 简介 每一种数据库都有这样的一种语言，PL/SQL 是在Oracle里面的一种编程语言，在Oracle内部使用的编程语言。我们知道SQL语言是没有分支和循环的，而PL语言是为了补充SQL语言的，是带有了分支和循环的语言。 PL/SQL 语法 基本数据类型声明 declar阅读全文
posted @ 2016-10-16 22:56 ITPSC 阅读(1653) | 评论 (0) 编辑
oracle（sql）基础篇系列（四）——数字字典、索引、序列、三范式
摘要: 数字字典表 --查看当前用户下面有哪些张表 select * from user_tables; select table_name from user_tables; --查看当前用户下面有哪些视图 select * from user_views; select view_name from user_views; --查看当前用户下面有哪些约束 select...阅读全文
posted @ 2016-10-15 12:32 ITPSC 阅读(400) | 评论 (0) 编辑
oracle（sql）基础篇系列（三）——数据维护语句、数据定义语句、伪列
摘要: DML语句 insert 向表中插入新的记录 --三种插入方式 --（1）不写字段的名字，直接按照字段的顺序把值逐个往里插 insert into dept2 values(50,'DANAME','BEIJING'); --（2）指定某些字段往里插，其他不插的字段默认都是空值 insert int阅读全文
posted @ 2016-10-14 12:05 ITPSC 阅读(296) | 评论 (0) 编辑
oracle（sql）基础篇系列（二）——多表连接查询、子查询、视图
摘要: 多表连接查询 内连接（inner join） 目的：将多张表中能通过链接谓词或者链接运算符连接起来的数据查询出来。 等值连接（join...on(...=...)） --选出雇员的名字和雇员所在的部门名字 --（1）必须明确的指出重复字段属于哪个表 select ename,dname dept.d阅读全文
posted @ 2016-10-12 12:31 ITPSC 阅读(4479) | 评论 (0) 编辑
oracle（sql）基础篇系列（一）——基础select语句、常用sql函数、组函数、分组函数
摘要: 花点时间整理下sql基础，温故而知新。文章的demo来自oracle自带的dept,emp,salgrade三张表。解锁scott用户，使用scott用户登录就可以看到自带的表。 #使用oracle用户登录linux [oracle@localhost ~]$ sqlplus / as sysdba阅读全文
posted @ 2016-10-11 18:10 ITPSC 阅读(605) | 评论 (0) 编辑
oracle（sql）基础篇系列（五）——PLSQL、游标、存储过程、触发器
文章主目录
PL/SQL
PL/SQL 简介
每一种数据库都有这样的一种语言，PL/SQL 是在Oracle里面的一种编程语言，在Oracle内部使用的编程语言。我们知道SQL语言是没有分支和循环的，而PL语言是为了补充SQL语言的，是带有了分支和循环的语言。
PL/SQL 语法
基本数据类型声明
declare
v_name varchar2(20);
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.14;
v_valid boolean := false;
v_name varchar2(20) not null := 'myname';
declare开头声明变量，v_name表示变量名字，通常以v_xxx这种格式命名变量，varchar2(20)表示变量类型， :=为赋值操作符。
在PL/SQL里面boolean类型变量在定义的时候一定要给初始值，Oracle 里面的put_line()不能打印boolean类型的值。
%type属性声明
还有一种声明变量的方法：使用%type属性。
declare
v_empno number(4);
v_empno2 emp.empno%type;
v_empno3 v_empno2%type;
begin
dbms_output.put_line('Test');
end;
使用emp表中empno字段的类型，这种方式的好处是PL/SQL声明的变量类型随着表字段类型的变化而变化。dbms_output.put_line('Test');表示输出Test。
table类型声明
table类型类似java中的数组类型。
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(2) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
--type开头
--类型名type_table_emp_empno
--is table of 表示table数据类型，相当于java里面的数组Array[]
--emp.empno%type表示该类型数据装的是emp表中empno类型的数据
--index by binary_integer 表示下表
--v_empnos type_table_emp_empno；使用type_table_emp_empno类型声明变量v_empnos
record数据类型声明
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 := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
--type type_record_dept is record声明type_record_dept是record类型。
--该类型中有三个属性deptno ，dname ，loc ，类型分别为dept表中deptno ，dname ，loc 三个字段的类型。record类型类似java中的类，record类型可以存储一条记录。
-- v_temp type_record_dept;使用type_record_dept类型声明变量v_temp。
%rowtype属性声明
另一种声明record变量的方法：
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'aaaa';
v_temp.loc := 'bj';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
使用%rowtype声明record类型的变量，v_temp 的属性和dept表的字段保持一致，这种方式的好处是PL/SQL声明的变量类型随着表字段类型的变化而变化。
异常处理
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then//固定写法
dbms_output.put_line('error');
end;
exception定义异常处理，紧跟“when others then”为固定写法。
SQL> declare
2 v_num number := 0;
3 begin
4 v_num := 2/v_num;
5 dbms_output.put_line(v_num);
6 exception
7 when others then
8 dbms_output.put_line('error');
9 end;
10 /
error
PL/SQL 过程已成功完成。
SQL>
其他类型的异常
--返回记录太多异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.put_line('太多记录了');
when others then
dbms_output.put_line('error');
end;
--没有记录异常
declare
v_temp number(4);
begin
select empno into v_temp from emp where empno = 2222;
exception
when no_data_found then
dbms_output.put_line('没数据');
end;
PL/SQL的DML语句
select语句
PL/SQL里面的selec t语句必须和into语句一块用并且有且只有一条记录。
--将编号为7369的员工的员工编号和薪水查询出来并存储到v_empno，v_sal并输出
declare
v_empno emp.empno%type;
v_sal emp.sal%type;
begin
select empno,sal into v_empno,v_sal from emp where empno=7369;
dbms_output.put_line(v_empno || '-' || v_sal);
end;
--将编号为7369的员工的记录查询出来并存储到v_emp，并输出员工编号和薪水
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output.put_line(v_emp.empno || '-' || v_emp.sal);
end;
insert语句
--向dept表中插入一条数据
declare
v_deptno dept2.deptno%type := 50;
v_dname dept2.dname%type := 'dname';
v_loc dept2.loc%type := ‘ckg’;
begin
insert into dept2 values (v_deptno, v_dname, v_loc);
commit;
end;
和sql语句唯一不同的是采用了pl/sql变量。update与delete语句和sql语句一样。
PL/SQL的DDL语句
create语句
begin
execute immediate 'create table stu2(id number(10),name varchar2(20) default ''zhangsan'')';
end;
PL/SQL编写ddl语句和SQL语句不同的是需要加execute immediate ，单引号中的sql语句使用双单引号指定缺省值，如''zhangsan''。alter语句，drop语句同理。
PL/SQL的分支循环语句
判断语句
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
dbms_output.put_line('middle');
else
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 循环。
declare
j binary_integer := 1;
begin
while j < 11 loop
dbms_output.put_line(j);
j := j + 1;
end loop;
end;
注意红色语法部分即可。上面的循环相当于java里的while 循环。
begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
--逆序
for k in reverse 1..10 loop
dbms_output.put_line(k);
end loop;
end;
注意红色语法部分即可。上面的循环相当于java里的增强 for 循环。
游标
我们知道，select语句的结果集是一张表，如果我们想对结果集逐条记录遍历该如何实现，就像java中的迭代器一样？PL/SQL提供了解决遍历结果集的的功能：游标。游标是指在结果集上的指针，通过游标可以对select语句的结果集逐条记录遍历。
显示游标与隐式游标
oracle中的游标分为显示游标和隐式游标。显示游标是用cursor...is..命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理。显式游标的操作：打开游标、操作游标、关闭游标。
隐式游标由Oracle数据库自动创建，名称是sql ,主要用途是可以返回一个操作是否成功或失败，只能用于DML语句。PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它。
游标属性
访问游标对象的属性方法：游标对象%游标属性。游标具有的属性如下：
%notfound 没有结果集
%found存在结果集
%rowcount 返回受影响的行数
%isopen询问游标是否已经打开
sql%rowcount可以统计刚执行的sql语句影响了多少条记录。
declare
v_deptno dept2.deptno%type := 50;
v_dname dept2.dname%type := 'dname';
v_loc dept2.loc%type := ‘can’;
begin
update dept2 set loc = 'sha' where deptno = 10;
dbms_output.put_line (sql%rowcount || '条记录被影响');
commit;
end;
--输出
1条记录被影响
循环游标
declare
cursor c is --声明游标指向select的结果集
select * from emp;
v_emp c%rowtype;
begin
open c; --打开游标
loop
fetch c into v_emp; --取出游标当前执向的值存入v_emp，每fetch一次，游标指向下一条记录
exit when (c%notfound); --找不到就退出
dbms_output.put_line(v_emp.ename);
end loop;
close c; --关闭游标
end;
--输出
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
可见，使用PL/SQL的游标和循环的结合，实现了对select结果集的遍历。
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;
for循环中使用游标，不需要声明 v_emp变量，for开始的时候自动声明v_emp；不需要打开关闭游标；不需要每次fetch。因此，通常采用此写法。
带参数的游标
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;
注意红色语法部分即可。从这里我们也可以看出，实际上，真正fetch的时候，PL/SQL才回去数据库查询数据。
可更新的游标
declare
cursor c is
select * from emp2 for update;
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;
注意红色语法部分即可。for update声明游标是更新用的，current of c 更新或者删除时指明是当前游标指向的记录。
存储过程
存储过程的创建
declare
cursor c is
select * from emp2 for update;
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 > 2000) then
update emp2 set sal = sal / 2 where current of c;
end if;
end loop;
end;
对于上面这段PL/SQL代码，如果我们需要经常执行，可以将这段代码创建成存储过程，如下：
create or replace procedure p is
cursor c is
select * from emp2 for update ;
begin
for e in c loop
if(e.sal < 2000) then
update emp2 set sal = sal * 2 where current of c;
elsif(e.sal > 2000) then
update emp2 set sal = sal / 2 where current of c;
end if;
end loop;
end;
创建存储过程和普通的PL/SQL代码不同的是将“declare”改为“create or replace procedure p is ”，其他保持不变。
调用存储过程
--命令方式
Procedure created
SQL> exec p;
PL/SQL procedure successfully completed
SQL>
--另一种方式
begin
p;
end;
带参数的存储过程
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)
is
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + v_a;
end;
--in 叫做传入参数，调用者负责给v_a赋值
--out 叫做传出参数，存储过程是没有返回值的,它就借助于传出参数
-- v_b 中间什么都没写，默认是in，是接收参数用的
-- v_temp 既可以接收，又可以传出
调用过程
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;
需要注意的是，执行存储过程并不会直接显示错误，可以使用show error命令显示编译错误。
删除存储过程
drop procedure p;
使用存储过程求emp表的树状结构
求出每个员工的上级（经理）并输出
create or replace procedure p_emp(v_empno emp.empno%type,v_level binary_integer) is
cursor c is
select * from emp where mgr = v_empno;
v_str varchar2(256) := '';
begin
for i in 1..v_level loop
v_str := v_str || ' ';
end loop;
for emp in c loop
dbms_output.put_line(v_str || emp.ename);
p_emp(emp.empno,v_level+1);--递归调用存储过程
end loop;
end;
--求出没有经理的员工
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where mgr is null;
dbms_output.put_line(v_emp.ename);
p_emp(v_emp.empno, 1);
end;
--或者我们已经知道7839是没有上级的
begin
p_emp(7839, 1);
end;
--输出
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
触发器
当对某一张表进行增删改查操作的时候，触发其他操作。
触发器的创建
--创建一张记录操作表
create table emp2_log-- emp2这张表的操作记录
(
uname varchar2(20),--用户
action varchar2(10),--操作
atime date--操作时间
);
--创建一个队emp2表操作的触发器
create or replace trigger trig
after insert or delete or update on emp2 for each row --表示每更新一条记录都会生成一条操作记录
--after可以改为before
begin
if inserting then
insert into emp2_log values(USER, 'insert', sysdate);--USER关键字,代表当前用户是谁
elsif updating then
insert into emp2_log values(USER, 'update', sysdate);
elsif deleting then
insert into emp2_log values(USER, 'delete', sysdate);--记录到log文件中
end if;
end;
触发器的执行
触发器不能直接执行，必须指明在哪张表上面执行哪些操作的时候才能触发触发器。现在对emp2表进行插入操作并查看emp2_log表
SQL> insert into emp2(ename,deptno) values('lisi',20);
SQL> select * from emp2_log;
UNAME ACTION ATIME
-------------------- ---------- -----------
SCOTT insert 2016/10/10
SCOTT insert 2016/10/10
SQL>
可见对emp2表的插入操作触发了日志记录操作。
触发器的删除
drop trigger trig;
基础篇到此。
作者：ITPSC出处：http://www.cnblogs.com/hjwublog/温馨提示：当您看到这篇文章时，我可能在很久之前就已经准备了，如果您觉得阅读本文能让你有所收获，请点一下“推荐”按钮或者“关注我”按钮，您的肯定将是我写作的动力！欢迎转载，转载请注明出处！分类: oracle：sql基础
+加关注
2
0
关注ITPSC | 快速评论
« 上一篇：oracle（sql）基础篇系列（四）——数字字典、索引、序列、三范式
» 下一篇：基于dubbo构建分布式项目与服务模块
posted @ 2016-10-16 22:56 ITPSC 阅读(1649) 评论(0) 编辑 收藏
https://www.cnblogs.com/hjwublog/p/5968097.html?utm_source=itdadao&utm_medium=referral
oracle（sql）基础篇系列（四）——数字字典、索引、序列、三范式
文章主目录
数字字典表
--查看当前用户下面有哪些张表
select * from user_tables;
select table_name from user_tables;
--查看当前用户下面有哪些视图
select * from user_views;
select view_name from user_views;
--查看当前用户下面有哪些约束
select * from user_constraints;
select constraint_name from user_constraints;
--查看当前用户下面的约束在哪些表上面
select constraint_name, table_name from user_constraints;
--select * from dictionary;
像上面这样的表被称作数据字典表，存在dictionary中。
索引
索引的概念
索引是一个数据库对象，索引就相当于我们字典里那个索引；当为某个字段建立索引，查询这个字段的时候效率就会更高。
需要注意的是，索引的建立只是提供了查询效率，修改却更慢了，因为需要附加修改索引表。
什么时候建索引
查询某个字段，访问量特别大的时候，而且效率比较低的时候，这个时候可以考虑建立索引。但不要轻易建立索引，因为建立所以也会增加而外的维护开销。
创建索引
--给stu表的email字段创建索引
create index stu_email_index on stu(email);
删除索引
--删除stu表的email字段的索引stu_email_index
drop index stu_email_index;
序列
序列的概念
create table stu
(
id number(10) ,
...
constraint stu_id_pk primary key(id),
...
);
在创建stu表的时候，我们制定了id作为主键，主键是唯一的，这就要求每次插入一条记录，都要插入一个新的id，但是在多线程的环境下同时插入大量记录，如何保证每次插入的id是不同的，而且这个id应该是有序递增的？一般数据库都会提供这样的机制：专门产生一个独一无二的数，然后每次自己往上递增一个1或者指定增量。Oracle里，这种机制称为序列sequence。sequence是一个对象，可以使用里面的属性nextval作为主键id的值，这就能保证主键id是唯一的。
创建序列与查看
--序列提供了两个属性，即NEXTVAL 和CURRVAL,用来访问序列中的序号。
--其中NEXTVAL代表下一个可用的序号， CURRYAL代表当前的序号。
drop sequence seq;
create sequence seq;
select seq.nextval ,seq.currval from dual;
SELECT * FROM USER_SEQUENCES;
SELECT * FROM ALL_SEQUENCES;
--sys用户查看
SELECT * FROM DBA_SEQUENCES;
--INCREMENT BY选项指定了序列中序号递增的幅度
drop sequence seq2;
create sequence seq2 increment by 2;
select seq2.nextval ,seq2.currval from dual;
--START WITH选项指定序列中的序号从哪个数字开始
drop sequence seq3;
create sequence seq3 start with 20;
select seq3.nextval ,seq3.currval from dual;
--MAXVALUE用来指定序列中序号的最大值
drop sequence seq4;
create sequence seq4 maxvalue 3;
select seq4.nextval ,seq4.currval from dual;
--MINVALUE用来指定序列中序号的最小值
drop sequence seq5;
create sequence seq5 minvalue 5;
select seq5.nextval ,seq5.currval from dual;
--选项CYCLE使得序列中的序号可以循环使用
drop sequence seq6;
--必须指定nocache 或者cache,否则报ORA-04013: number to CACHE must be less than one cycle
create sequence seq6 minvalue 1 maxvalue 5 cycle nocache;
select seq6.nextval ,seq6.currval from dual;
--如果把序列中的序号放在内存中进行缓冲，那么获得序号的速度将大大加快。
--将序列中接下来的n个序号在内存中进行缓冲，最小值为2
drop sequence seq7;
create sequence seq7 minvalue 1 maxvalue 30 cache 3;
select seq7.nextval ,seq7.currval from dual;
序列的修改
--序列在创建之后，可以对其进行修改。
--比如修改它的最大值、最小值、增幅等，但不能修改初始值。
drop sequence seq8;
create sequence seq8 increment by 2;
select seq8.nextval ,seq8.currval from dual;
alter sequence seq8 increment by 3;
删除序列
drop sequence seq;
序列作为主键使用
insert into stu(id,name,email) values(seq.nextval,'lisi','321@qq.com');
insert into stu(id,name,email) values(seq.nextval,'wangwu','988@qq.com');
insert into stu(id,name,email) values(seq.nextval,'chenliu','567@qq.com');
三范式
范式的由来
数据库设计时的一些规则，而这些规则是由一个姓范的人规定的，所以叫范式。
范式的目标
不存在冗余数据(同样的数据不存第二遍)。
第一范式
第一范式的要求：1.要有主键(设计任何表都要有主键) 2.列不可分
第二范式
第二范式的要求：当一张表里面有多个字段作为主键的时候，非主键的这些字段，不能依赖于部分主键。简单的说：不能存在部分依赖。
第三范式
第三范式的要求：不能存在传递依赖(除了主键之外的其他字段必须直接依赖于主键)。
作者：ITPSC
出处：http://www.cnblogs.com/hjwublog/
温馨提示：当您看到这篇文章时，我可能在很久之前就已经准备了，如果您觉得阅读本文能让你有所收获，请点一下“推荐”按钮或者“关注我”按钮，您的肯定将是我写作的动力！欢迎转载，转载请注明出处！
posted @ 2016-10-15 12:32 ITPSC 阅读(400) 评论(0) 编辑 收藏
https://www.cnblogs.com/hjwublog/p/5963143.html
oracle（sql）基础篇系列（三）——数据维护语句、数据定义语句、伪列
文章主目录
DML语句
insert
向表中插入新的记录
--三种插入方式
--（1）不写字段的名字，直接按照字段的顺序把值逐个往里插
insert into dept2 values(50,'DANAME','BEIJING');
--（2）指定某些字段往里插，其他不插的字段默认都是空值
insert into dept2(deptno,dname) values(60,'DNAME2');
--（3）将子查询的结果插入到表中
insert into dept2 select * from dept;
update
修改记录。
--对部门号为10的员工的工资加2 倍
update emp2 set sal = sal*2 , ename='2'||ename where deptno =10;
--将编号为7369的员工的经理改为编号为7698 的经理
update emp2 set mgr = 7698 where empno = 7369;
delete
删除员工名为2MILLER的员工
delete from emp2 where ename = ‘2MILLER’;
DDL语句
oracle常用变量类型
binary_integer，主要用来计数而不是用来表示数据类型
number，数字类型
char，定长字符串
varchar2，变长字符串
date，日期类型
long，长字符串，最长2GB
boolean，布尔型，可以取值true,false,null
create
创建新的表，创建表时可以指定字段的约束条件。约束条件通常有：默认约束，非空约束，唯一约束，主键约束，外键约束，check 。约束有可以分为列级约束、表级约束。
无约束创建表
create table stu
(
id number(10),
name varchar2(20),
sdate date,
class number(10),
grade number(2) ,
email varchar2(50)
);
非空约束&check约束&默认约束&唯一约束
可以对某些字段进行非空约束和唯一约束。
--name非空，emali唯一
create table stu
(
id number(10) check(id>1000),
name varchar2(20) not null,
sdate date,
class number(10),
grade number(2) default 1,
email varchar2(50) unique
);
--使用constraint给约束条件取名字，如果不给约束条件起名字，系统会默认的给约束条件起一个名字
create table stu
(
id number(10),
name varchar2(20) constraint stu_name_nn not null,
sdate date,
class number(10),
grade number(2) default 1,
email varchar2(50) constraint stu_email_un unique
);
主键约束&外键约束
主键的一些特性：
（1）可以唯一标识整条记录
（2）非空且唯一
（3）用类型的数值的字段做主键比字符类型的字段更合适
（4）可以用多个字段作为联合主键
外键的一些特性：
（1）建立于一张表的两个字段，或者两张表的两个字段，一个字段去参考另一个字段的值
（2）如果被参考字段没有这个值，不能把参考字段的值设置成为其他的值（如，被参考字段为空，而将参考字段设置为一个非空值是不能的）
（3）被参考的字段必须是主键
（4）被参考的字段不能作为删除条件
--name非空，emali唯一
create table stu
(
id number(10) primary key,
name varchar2(20) constraint stu_name_nn not null,
sdate date,
class number(10),
grade number(2) default 1,
email varchar2(50) constraint stu_email_un unique
);
--建立class表
create table class
(
id number(10) primary key,
name varchar2(20)
);
--stu表的class字段参考class表的id字段，即stu表的class字段是class表的外键
create table stu
(
id number(10) primary key,
name varchar2(20) constraint stu_name_nn not null,
sdate date,
class number(10) references class(id),
grade number(2) default 1,
email varchar2(50) constraint stu_email_un unique
);
列级约束&表级约束
上面sql的约束条件都是写在字段的后面，称为列级约束；还有另一种写法，就是在所有字段的最后用constraint声明，称为表级约束。需要注意的是，非空约束与默认约束只能用于列级约束。
create table stu
(
id number(10) ,
name varchar2(20),
sdate date,
class number(10),
grade number(2) default 1,
email varchar2(50),
check(id>1000),
constraint stu_id_pk primary key(id),
constraint stu_class_fk foreign key(class) references class(id),
constraint stu_email_un unique(name,email)
);
alter
修改现有表的结构。
增加列
alter table stu add(address varchar2(100));
删除列
alter table stu drop(address);
修改列
alter table stu modify(address varchar2(200));
删除约束条件
alter table stu drop constraint stu_class_fk;
添加约束条件
alter table stu add constraint stu_class_fk foreign key(class) references class(id);
修改约束条件
通常修改约束条件的方法时把原来的删掉，然后再添加个新的。
drop
删除表。
drop table stu;
伪列
Oracle表中的数据的默认显示顺序是先插入的先显示，Oracle表中rownum和rowid都是伪列，这两个伪列都可以对记录进行编号排序，很有用处。
rownum
rownum是根据sql查询出的结果给每行分配一个逻辑编号，对查询结果按照1，2，3...进行排列的，但这个字段不会显示出来。每条记录的rownum根据sql查询结果的不同而不同。
--新插入的数据
insert into emp2(empno,ename,deptno) values(8888,'test',80);
select * from emp2;
--选出最早插入的3条记录
select * from emp2 where rownum <=3;
可见，rownum 是对查询结果按照先插入的先显示的顺序进行编号。但是，rownum只能和< 或者 <= 一起用，不能与 > 和 = 一起使用。
（1）求10行以后的后4行的员工编号和姓名
--错误写法，查询结果为空
select empno, ename from emp where rownum > 10;
--正确写法，先对rownum 进行别名显示，别名显示后就可以采用>，>=
select empno, ename from (
select rownum r, empno,ename from emp
) where r > 10;
（2）求薪水最高的前5个人
--错误写法，先取出rownum <= 5的记录，再对记录排序
select ename,sal from emp where rownum <= 5 order by sal desc;
--正确写法，先排好序，再取出rownum <= 5的记录
select ename,sal from (
select ename,sal from emp order by sal desc ) where rownum <=5;
（3）求薪水最高的第6个人到第10个人
select ename,sal from (
select rownum r ,ename,sal from (
select ename,sal from emp order by sal desc )) where r >=6 and r <=10;
分析：先排序，再对rownum 进行别名化，别名显示后就可以采用>，>=。
rowid
rowid是物理结构上的记录，在每条记录插入到数据库中时，都会有一个唯一的物理记录 （不会变）。
select t.*,t.rowid from emp t;
rowid的一个应用
对emp表根据薪水降序分页查询。
--rowid分页，第一步获取数据物理地址rowid
select rowid rid,sal from emp order by sal desc;
--rowid分页，第二步根据rownum取得最大页数
select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10;
--rowid分页，第三步根据rownum别名化rn取得最小页数
select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5;
--rowid分页，第四步根据物理地址，查询出具体数据
select * from emp where rowid in(select rid from(select rownum rn,rid from(select rowid rid,sal from emp order by sal desc) where rownum<10) where rn>5);
分析：
分页的关键是根据逻辑rownum取得最小最大页数，然后再根据物理rowid查询出物理记录。第一步先获取数据物理地址，第二步取得最大页数，第三步取得最小页数，第四步因为取得的页数都是物理地址，再根据物理地址，查询出具体数据。
作者：ITPSC
出处：http://www.cnblogs.com/hjwublog/
温馨提示：当您看到这篇文章时，我可能在很久之前就已经准备了，如果您觉得阅读本文能让你有所收获，请点一下“推荐”按钮或者“关注我”按钮，您的肯定将是我写作的动力！欢迎转载，转载请注明出处！
分类: oracle：sql基础
好文要顶 关注我 收藏该文 
+加关注
0
0
快速评论
« 上一篇：oracle（sql）基础篇系列（二）——多表连接查询、子查询、视图
» 下一篇：oracle（sql）基础篇系列（四）——数字字典、索引、序列、三范式
posted @ 2016-10-14 12:05 ITPSC 阅读(297) 评论(0) 编辑 收藏
https://www.cnblogs.com/hjwublog/p/5959886.html
oracle（sql）基础篇系列（二）——多表连接查询、子查询、视图
文章主目录
多表连接查询
内连接（inner join）
目的：将多张表中能通过链接谓词或者链接运算符连接起来的数据查询出来。
等值连接（join...on(...=...)）
--选出雇员的名字和雇员所在的部门名字
--（1）必须明确的指出重复字段属于哪个表
select ename,dname dept.deptno from emp,dept where emp.deptno = dept.deptno;
--（2）新语法：join...on(...=...)
select ename, dname from emp join dept on ( emp.deptno = dept.deptno);
非等值连接（join...on(...>...)）
--求每个人的薪水值，名字和他的薪水处于第几个级别
--（1）不使用连接查询
select ename, sal, grade from emp, salgrade where sal between losal and hisal;
--或者
select ename, sal, grade from emp, salgrade where sal >= losal and sal <= hisal;
--（2）新语法：join...on(...between...and...)
select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
--（3）三表连接：取出雇员名字，他的部门名称，和薪水等级其中名字第二个字母包含A的不要取出
select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';
从上面的例子也可以看出，比较的对象不仅可以是具体的数据，字段名也可以作为比较的对象；一个join...on(...xxx...)只能连接两张表，要连接两张表以上，写多个join...on(...xxx...)即可，之间用空格隔开。
自然链接（natural join...）
自然连接是在两张表中寻找那些数据类型和列名都相同的字段，然后自动地将他们连接起来，并返回所有符合条件按的结果。
--自动将emp表中的deptno和dept表中的deptno做了连接
select emp.ename,dept.dname from emp natural join dept;
交叉链接（cross join...）
交叉连接，交叉连接即返回这两个表的笛卡尔积。
select * from emp cross join dept;
外连接(outer join)
目的：将两张表中不能产生连接的数据也查询出来。注意和内连接的区别：内连接只查询符合连接条件的记录，外连接则把不能产生连接的数据也查询出来。
右外连接（right join... on(...=...)）
概念：right join关键字右边的表有一条（多条）不能产生连接的数据没有在连接查询中显示出来，要想把他显示出来，就要用到右连接。
--求每个雇员的名字，他所在部门的名称，全部选出来，并且把多余的部门也选出来(有一个部门没有员工)
select ename, dname from emp e right join dept d on (e.deptno = d.deptno);
左外连接（left join...on(...=...)...）
概念：left join关键字左边的表有一条（多条）不能产生连接的数据没有在连接查询中显示出来，要想把他显示出来，就要用到左连接。
--求这个人的名字，他经理人的名字，并且把没有经理的员工也选出来
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
全外连接（full join...on(...=...)...）
概念：把full join关键字左边右边没有连接的记录都查询出来。
select ename, dname from emp e full join dept d on (e.deptno = d.deptno);
自连接
概念：为同一张表起不同的别名，然后当成两张表来用。
--求这个人的名字，他经理人的名字(经理也是员工，经理编号就是员工编号)
select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno)
小结
join后跟要连接的表，on后跟连接条件，将连接条件和过滤条件分开。
--SQL1992旧写法
select ename,dname from emp e,dept d where e.deptno = d.deptno and e.ename not like '%A%';
--SQL1999新写法
select ename,dname from emp e join dept d on(e.deptno = d.deptno) where e.ename not like '%A%';
子查询
子查询是一个 SELECT 语句，它嵌套在一个 SELECT语句、DELETE 语句、UPDATE 语句或嵌套在另一子查询中。
求谁挣的钱最多
select ename, sal from emp where sal = (select max(sal) from emp);
求按照部门进行分组之后挣钱最多的那个人的名字，部门编号
--（1）只求挣钱最多的一个人的名字和部门编号
select ename,deptno from emp where sal = ((select max(m_max) from (select deptno,max(sal) m_max
from emp group by deptno )));
--（2）求出每个部门挣钱最多的人的名字和部门编号
select e.deptno,e.ename from emp e join (select deptno,max(sal) max_sal from emp group by deptno) t on (t.deptno = e.deptno and t.max_sal = e.sal);
思路：
1.先求出每个部门的最大工资和部门号，根据部门号分组
2.把第一步的结果当成一张表t，表t有max_sal，deptno两个字段
3.连接查询表t和表emp,求出挣钱最多的那个人的名字，部门编号
求每个部门的平均薪水等级是多少
select deptno ,grade from salgrade s join
(select deptno, avg(sal) s_avg from emp group by deptno) t
on (t.s_avg between s.losal and s.hisal );
分析：
1.求出每个部门的平均薪水
2.把第一步的结果当成一张表t，表t有avg_sal，deptno两个字段
3.连接查询表t和表salgrade表,求出grade,dept
小结
子查询的关键是将子查询的结果当成一张表。
视图
授权
默认情况下scott用户没有创建视图的权限，需要授权。
#使用oracle用户登录linux
[oracle@localhost ~]$ sqlplus / as sysdba;
......
SQL> grant create table ,create view to scott;
视图的概念
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。
简单的理解，视图就是一个子查询或者就是一张表,视图中的表叫虚表，实际数据依然在实际当中的表里面。
创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
--求平均薪水的等级最低的部门名称
--分析：（1）先求出每个部门的平均薪水等级，结果当成表t1
---------（2）从t1中求出平均薪水等级最低的部门编号
---------（3）根据（2）中查询出的部门编号连接查询dept表得出部门名称
select dname from dept where deptno = (
select deptno from (
select t1.deptno, s.grade from salgrade s join
(select avg(sal) s_avg,deptno from emp group by deptno) t1 on (t1.s_avg between s.losal and
s.hisal)
) where grade = (
select min(grade) from (
select t1.deptno, s.grade from salgrade s join
(select avg(sal) s_avg,deptno from emp group by deptno) t1 on (t1.s_avg between s.losal and
s.hisal)
)
)
)
在上面的例子中，有两部分子查询是同样的sql语句，我们可以将这个子查询创建为视图，从而简化sql语句。
create view v$_dept_sal_grade as (
select t1.deptno, s.grade from salgrade s join
(select avg(sal) s_avg,deptno from emp group by deptno) t1 on (t1.s_avg between s.losal and
s.hisal)
)
上面的sql语句就可以简化为：
select dname from dept where deptno = (
select deptno from v$_dept_sal_grade where grade = (
select min(grade) from v$_dept_sal_grade
)
)
可见，通过创建视图，大大简化了sql语句。
作者：ITPSC
出处：http://www.cnblogs.com/hjwublog/
温馨提示：当您看到这篇文章时，我可能在很久之前就已经准备了，如果您觉得阅读本文能让你有所收获，请点一下“推荐”按钮或者“关注我”按钮，您的肯定将是我写作的动力！欢迎转载，转载请注明出处！
好文要顶 关注我 收藏该文
+加关注
0
0
快速评论
« 上一篇：oracle（sql）基础篇系列（一）——基础select语句、常用sql函数、组函数、分组函数
» 下一篇：oracle（sql）基础篇系列（三）——数据维护语句、数据定义语句、伪列
posted @ 2016-10-12 12:31 ITPSC 阅读(4483) 评论(0) 编辑 收藏
https://www.cnblogs.com/hjwublog/p/5952296.html
oracle（sql）基础篇系列（一）——基础select语句、常用sql函数、组函数、分组函数
文章主目录
花点时间整理下sql基础，温故而知新。文章的demo来自oracle自带的dept,emp,salgrade三张表。解锁scott用户，使用scott用户登录就可以看到自带的表。
#使用oracle用户登录linux
[oracle@localhost ~]$ sqlplus / as sysdba;
......
SQL> alter user scott account unlock；
四大语句
DQL语句——select
DML语句——insert,upate,delete等（关键字后带表名）
DDL语句——create,alter,drop等（关键字后带table关键字再跟表名）
事务控制语句——commit,rollback
基础select语句
简单select语句
select * from emp;
包含算术表达式的语句
--查询出来的sal乘以5
select ename , sal*5 from emp;
--表中取出2*3的结果
select 2*3 from emp;在emp;
--当我们需要显示一个结果的时候就用系统提供的dual虚表
select 2*3 form dual;
获取系统当前的时间
--sysdate在Oracle中表示当前系统时间
select sysdate from dual;
含有别名的语句
--显示出来的是salary字段名而不是sal
select ename, sal*12 salary from emp;
含有空值
空值表达式
--含有任何null值的数学表达式最后的结果都为null
select ename, sal*5+comm from emp;
空字符串（字符串、字符串连接符）
--含有任何null值的字符串表达式中，null被当作空字符串处理
select empno, ename || 'abc' || mgr from emp;
--字符串连接符：||。select语句中用单引号表示字符串
distinct消除重复
--用distinct消除结果集中deptno相同的记录
select distinct deptno from emp;
--消除结果集中job和deptno都相同的记录
select distinct job, deptno from emp;
where 条件过滤
等值判断
select * from emp where empno = 7369;
非等值判断
（1）>,<,>=, <= ,<>
--取出所有部门号不是10的雇员的名字和薪水
select ename, sal from emp where deptno <> 10;
（2）between..and..
--包含最大值和最小值
select ename, sal from emp where sal between 800 and 1500;
（3）字符串比较
比较字符串ASCII码值的比较，先比较第一字母，依次比较每个字母
select ename, sal from emp where ename > ‘DBA’;
空值处理
--为空
select ename from emp where comm is null;
--不为空
select ename from emp where comm is not null;
注意，select ename from emp where comm = null;这种写法表示comm字段的值等于null，is null 或者is not null返回的boolean值，而=null返回的是null。null是一种特有的数据类型，其等价于没有任何值、是未知数。因此通常会获取不到结果却不提示语法错误。
in语句
--在选定范围
select ename, sal from emp where sal in (800, 1250, 1500, 2000);
--不在选定范围
select ename, sal from emp where sal not in (800, 1500, 2000);
日期处理
--Oracle默认的日期格式为：DD-MON-RR
select ename, hiredate from emp where hiredate > ’20-2月-1981’;
--to_char()，把日期类型字段转为指定格式的字符串
--输出2008-04-04
select to_char(sysdate, ‘YYYY-MM-DD-HH24:MI:SS’) from dual;
--to_date()，将日期字符串转为指定格式的日期
select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);
and,or,not语句
--and
select deptno, ename, sal from emp where deptno = 10 and sal > 1000;
--or
select deptno, ename, sal from emp where deptno = 10 or sal > 1000;
--not
select ename, sal from emp where sal not in (800, 1500, 2000);
like模糊查询（通配符匹配、单字符匹配、转义字符）
（1）通配符%，代表0个或者多个字符
select ename from emp where ename like ‘%ALL%’;
（2）单个字符-，代表一个字符
select ename from emp where ename like ‘_A%’;
（3）转义字符\，统默认的转义字符是 ‘\’
--中间的%不表示通配符，而是表示姓名包含%
select ename from emp where ename like ‘%\%%’；
order by排序
asc升序
select ename, sal from emp order by ename asc;
desc降序
select ename, deptno from emp order by ename desc;
常用sql函数
lower()——将字符串全部转成小写
select lower(ename),sal from emp where lower(ename) like '%a%';
upper()——将字符串全部转成大写
select upper(ename),sal from emp where upper(ename) like '%a%';
substr()——截取字符串
--从第2个字符开始截取ename
select substr(ename, 2) from emp;
--substr('hello',1,3)，输出hel，第三个参数可以省略
select substr('hello', 1,3) from dual;
chr()——求一个与某个ASCII码值对应的字符
select chr(65) from dual;
ascii()——求一个字符的ASCII码值
select ascii(‘A’) from dual;
round()——四舍五入
对参数值进行四舍五入，可以指定四舍五入到小数点后几位，可以用负数指定小数点前面几位
--输出24
select round(23.652) from dual;
--输出23.65
select round(23.652，2) from dual;
--输出20，-1表示对个位数进行四舍五入
select round(23.652，-1) from dual;
--输出0，-2表示对十位数进行四舍五入
select round(23.652，-2) from dual;
to_char()——日期类型转字符串
把日期类型字段转为指定格式的字符串，可以精确到时分秒
--输出2008-04-04
select to_char(sysdate, ‘YYYY-MM-DD’) from dual;
--2008-04-04 01:10:21
select to_char(sysdate,'YYYY-MM-DD hh:mm:ss') from dual;
to_date()——字符串转为日期类型
将日期字符串转为指定格式的日期
select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);
to_number()——字符串转数字
将指定的字符串转换成数字格式,第一个参数为要转为数字的字符串（一定要确保所转换字段是可转换为数字的，否则报“invalid number”），第二个参数为指定的格式
select * from emp where sal > to_number(‘$1,200.00’, ‘$9,999.99’);
nvl()——空值处理
这个函数有两个参数：
第一参数：字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值。
select ename, sal*12+nvl(comm, 0) from emp;
组函数
min()——求最小值
max()——求最大值
avg()——求平均值
sum()——求总和
select sum(sal), avg(sal), max(sal), min(sal) from emp;
count()——求数量
--求记录数量
select count(*) from emp;
--求comm字段中所有非空记录的数量
select count(comm) from emp;
--和distinct一起使用，得到所有唯一值记录的数量
select count(distinct deptno) from emp;
分组函数
对组函数的查询结果进行分组
group by
使用group by 的规律：出现在select列表中的字段，如果没有出现在组函数中，则必须出现在group by子句中。
select deptno, avg(sal) from emp group by deptno;
--典型错误，group by缺少ename
select ename, deptno, max(sal) from emp group by deptno;
--求出每个部门中最赚钱的那个人的名字
select ename, deptno, sal from emp where sal in (
select sal from (
select deptno, max(sal) sal from emp group by deptno
))
having
having语句的作用是对分组之后的数据进行过滤数据，where 语句的作用是分组之前过滤数据。
--注意不能使用where
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;
综合练习
从emp这张表里把平均工资和部门编号列出来，并且过滤掉大写是A的名字 把剩下的数据按照部门编号进行分组，分组之后的平均薪水必须大于2000，按照部门编号的倒序排列。
分析：分组，过滤，排序；过滤又包含分组前过滤(where)和分组后过滤(having)。编写顺序：分组前过滤(where) > 分组（group by） > 分组后过滤(having) > 排序（order by）
select avg(sal),deptno,ename from emp where ename not like '%A%' group by deptno,ename having avg(sal) > 2000 order by deptno desc;
本文就到此，下一篇我们继续整理多表连接查询、子查询、视图相关内容。
作者：ITPSC
出处：http://www.cnblogs.com/hjwublog/
温馨提示：当您看到这篇文章时，我可能在很久之前就已经准备了，如果您觉得阅读本文能让你有所收获，请点一下“推荐”按钮或者“关注我”按钮，您的肯定将是我写作的动力！欢迎转载，转载请注明出处！
分类: oracle：sql基础
好文要顶 关注我 收藏该文
+加关注
1
0
快速评论
« 上一篇：分布式缓存技术redis系列（五）——redis实战（redis与spring整合，分布式锁实现）
» 下一篇：oracle（sql）基础篇系列（二）——多表连接查询、子查询、视图
posted @ 2016-10-11 18:10 ITPSC 阅读(605) 评论(...) 编辑 收藏
https://www.cnblogs.com/hjwublog/p/5950301.html
扫一扫
673
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
