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
---------- --------------
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
已选择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))
/
<!--------------------------------select----end------------------------------>
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
-------------------- ---------- ----------
唐龙 1 89
王寅武 2 70
罗光辉 4 70
唐龙 1 89
王寅武 2 70
杜爽 5 70
徐宏韬 3
刘则川 6 70
7 70
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>