ORACLE总结
数据库口令：tiger
用户名密码：
scott/tiger
sys/tiger
system/tiger
登陆命令:
sqlplus scott/tiger
解锁账户(管理员身份：sqlplus sys/tiger as sysdba)
alter user hr identified by tiger account unlock
解锁并提供一个密码tiger
查询方式:select * from dept;
第一种方式：
开始---->运行(输入CMD)----> sqlplus 用户名/密码
第二种方式:sqlplusw
开始---->程序---->Oracle - OraDb10g_home1---->Application
Development---->SQL*Plus---->输入用户名密码连接标识符
第三种方式: isqlplus(浏览器的方式)
打开IE---->输入URL http://localhost:5560/isqlplus-->
用户名密码连接标识符(不指定的情况下，可不写)---->
成功登陆进入工作区进行操作
查看数据库的表格数量:select * form all_tables;
显示用户表结构:desc user_tables;找出table_name字段，在根据table_name查找用户表
显示用户：select table_name from user_tables;
DML数据操纵语言
DDL数据定义语言
DCL数据控制语言
select * from dept;//*查询所有记录
select deptno,dname from dept;
select deptno,dname,loc from dept;
更换用户：之前sqlplus scott/tiger 转换:conn sys/tiger as sysdba.
奖金(comm)
第一种奖金显
示：select ename as name,sal as salary,comm co from emp;(别名便于显示)
第二种奖金显示：select ename "name",sal "salary",comm "employee comm" from emp;
连接符的应用：select ename,job,ename || ' is a ' || job as "employee detail" from emp;
select deptno from emp;
select distinct deptno from emp;//删除重复的
DOM 下用spool(启动)spool off(来结束)来保存之后的内容。
日期select ename,hiredate from emp;
获得编号:select empno,ename,job,deptno from emp
where deptno = 20;where deptno>10;
值在两者之间的(between...and...):select ename,job,sal from emp
where sal between 2500 and 3000;
IN(运算符):select ename,sal,deptno from emp
where deptno in(10,20);
LIKE:select ename,sal,job from emp
where ename like'_A%';
and(可多个条件连用):select ename,sal,deptno from emp
where sal>2000
and deptno = 20
and ename = 'FORD';
or(可多个条件连用):select ename,sal,deptno from emp
where sal>2000
or deptno = 20
or ename = 'FORD';
not:select deptno from emp
where deptno not in(10,20);
升序排列：select ename,deptno,sal,hiredate from emp
order by hiredate;
降序排列:select ename,sal,hiredate,job from emp
order by sal desc ;
(名字排序按照字母先后)
单行函数：function_name
字符改变大小：
全部变小：select lower('SAJLJSDF csljOJFJOSFDJ') from dual;
全部变大: select upper('LKDJSLFlkjlsdkjflkjsdflkj') from dual;
首字母变大：select initcap('SFJLSlklsdkfjlskjdfl sdlfjlas') from dual;
字符连接：select concat('sdfjsldjkfsdf') from dual；
取字符串长度:select length('dfdofjdf') from dual;
判断第一次遇见字符的位置:select instr('djaljdlfkjsdf','l') from dual;答案为:4
只能去掉两边的字符:select trim('h' from 'ehlloworldh') from dual;未指定是为空格（去掉两边的空格）
不足的位在左边补*:select lpad(sal,10,'*') from emp;
不足的位在右边补*:select rpad(sal,10,'*') from emp;
获取字符段:select substr('HelloWorld',1,5) from emp;
取中间字符所在位置:select instr('HelloWorld','W') from emp;
数组函数：
四舍五入函数：select round(45.926,2) from dual;//保留小数点后两位，进行四舍五入进位
截断：select trunc(45.926,2) from dual;//从小数点后两位开始截断
select trunc(45.926,-2) from dual;//从小数点前两位开始截断答案为0
select trunc(45.926) from dual;//保留整数
取模函数:select mod(6565,2) from dual;
查询系统时间:select sysdate from dual;
select sysdate+3 from dual;
select sysdate+10 from dual;//加的天数超过本月月份自动加
select sysdate-3 from dual;
求入职天数：select ename,sysdate-hiredate hiredate from emp;//当前时间减去入职时间
求入职年数: select ename,hiredate,round(sysdate-hiredate)/365 hiredate from emp;
select to_char(sysdate+2/24,'yyyy-dd-mm hh24:mi:ss') from emp;//sysdate+2/24在日期上加12小时。
select next_day(sysdate,'星期五') from dual;
select last_day(sysdate) from dual;
trunc(sysdate,'D'); 截取到本周的第一天(0:00)
trunc(sysdate,'MM'); 截取到本月的第一天(0:00)
trunc(sysdate,'DD'); 截取到本日的0:00
trunc(sysdate,'yyyy'); 得到今年的第一天的0:00
select to_char(sysdate,'year month dd day dy') from emp;
select to_char(123456.78,'L999G999D99') from emp;
通用函数：
单行函数：
使用函数对数据进行计算
使用函数修改数据
使用函数控制一组数据的输出格式
使用函数改变日期的显示格式
使用函数改变数据类型
使用NVL 函数
使用IF-THEN-ELSE 逻辑
分组函数：
select avg(sal),sum(sal),max(sal),min(sal) from emp;
select max(hiredate),min(hiredate) from emp;
select count(*) from emp;
select count(comm) from emp;
select sum(comm) from emp;
select count(*) from emp
where deptno = 10;
select deptno,count(*) a from emp 2 group by deptno;
select deptno,min(sal) as a from emp 2 group by detpno;
select avg(nvl(comm,100)) from emp;
select avg(comm) from emp;
select ename,comm from emp where comm is not null;
select count(distinct deptno) from emp;
select deptno,job,sum(sal) 2 from emp 3 group by deptno,job;
select avg(sal) from emp
select avg(sal) from emp 2 group by deptno;
select ename,comm from emp where comm is not null;
select deptno,avg(sal) from emp 2 group by deptno 3 having avg(sal) >2000; 每个部门的平均工资：select max(avg(sal)) from emp 2 group by deptno; select ename,d.deptno from emp,dept d;
select ename,dname from emp,dept where emp.deptno = dept.deptno;
创建表：create table departments
did number primary key,
dname varchar2(20),
lid number);
插入数据:insert into departments values(10,'accounting',1);
select * from locations;
select * from departments;
查询多张表中的相同字段：select e.ename,d.dname,l.city from
emp e,departments d,locations l
where e.deptno = d.did and d.lid = l.locid;
连接N个表至少需要N-1个条件
select tabel_name from user_tables;
等级：select * from salgrade;
薪水在最低和最高之间:select e.ename,e.sal,s.grade from
emp e,salgrade s
where e.sal between s.losal and s.hisal;
加号行为匹配令一行为全部:select e.ename,d.dname from emp e,
dept d
第一种情况: where e.deptno = d.deptno(+);
select e.ename,m.ename from
emp e,emp m
where e.mgr = m.empno;
select empno,ename from emp;
select e.ename,d.dname from emp e natural join dept d;
select e.ename,d.dname from emp e
join dept d
using(deptno);
select e.ename,d.dname from emp e
,dept d where e.deptno = d.deptno;
join...on..实现外连接e.deptno = d.deptno : select e.ename,d.dname from emp e
2 join dept d on e.deptno =
d.deptno;
按等值的连接:select e.ename,d.dname,l.city from
emp e,
departments d,
locations l
where e.deptno = d.did and d.did = l.locid;//d.did和l.locid无关连
和上面等值关系的join...on...方法:select e.ename,d.dname,l.city from
emp e
join departments d on(e.deptno = d.did)
join locations l on(d.did = l.locid);
和上面结果相同，两条件没有关联性。
select e.ename,d.dname from emp e,
dept d where e.deptno(+) = d.deptno;
左(右)外连接:select e.ename,d.dname from dept d
left outer join emp e/right outer join dept d
on(e.deptno = d.deptno);
全外连接(full outer join):select e.ename,d.dname from dept d
full outer join dept d
on(e.deptno = d.deptno);
部门编号为空: select * from emp where deptno is null;
子查询在主查询之前一次执行完成:
指定一个人来和其他人比较:select ename,sal from emp
where sal >/=/<>
(select sal from emp where ename = 'SCOTT');
select ename,job,sal from emp where sal =(select min(sal) from emp);
分组，过滤，子查询：select deptno,min(sal) from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);
IN(在什么中):select empno,ename,sal from emp
where sal in
(select min(sal) from emp group by deptno);
ANY() : select empno,ename,job,sal from emp//empno(员工编号)
where sal > any
(select sal from emp where job = 'SALESMAN');
ALL():......
小于any是小于最大值大于any是大于最小值
小于all是小于最小值大于all是大于最大值
select * from emp where deptno = &no;输入no的值:10
select ename,deptno,sal*12 from emp
where job = '&job';输入job的值:SALESMAN
select * from &table; 输入table的值:emp
insert into dept(deptno,dname,loc) 2 values(50,'market','beijin');
insert into dept values(60,'account','shanghai');
insert into dept(deptno,dname) values(70,'test');
insert into dept(deptno,dnam,loc) values(80,'PK','chongqing');
insert into dept values(90,'test1',null);
desc emp
insert into emp(empnom,ename,hiredate) 2 values(123,'tyler','sysdate');
insert into emp(empno,ename,sal) 2 values(&empno,'&ename',&sal);
输入empno的值：1234 输入ename的值:枫输入sal的值:6000
select empno,ename,sal,hiredate from emp;
拷贝数据:
创建表格:create table emp_bak(
empno number primary key,
ename varchar(20),
sal number(7,2));
创建行:insert into emp_back 2 (select empno,ename,sal
3 from emp where deptno =10);
更新数据:update emp_bak set sal = 3000 2 where empno =7782;
update emp_bak set sal = 3000, 2 ename = 'Pk' 3 where empno = 7934;
update emp set sal = 1200, 2 job = 'SALESMAN',
3 hiredate = to_date('2010-6-25','yyyy-mm-dd')
4 where ename = 'SMITH';
select ename,sal,job,hiredate from emp;
update emp set sal =
(select sal from emp where ename = 'KING'),
hiredate=
(select hiredate from emp where ename = 'KING')
where deptno = '10000';违反完整约束条件(SCOTT.FK_DEPTNO) - 已找到子记录
更新数据错误:insert into emp(empno,deptno,ename) values (0123,55,'HELLO') rollback(取消之前操作)
删除数据:delete from emp where empno = 1234;
删除所有数据:delete from emp_bak;
删除表:drop table emp_bak;
删除中的完整性错误:delect from dept where deptno = 20;
违反完整约束条件(SCOTT.FK_DEPTNO) - 已找到子记录
事物:
进入两个CMD中: 第一个:create table myemp(id number);//创建表格第二个:insert into myemp values(1);//创建一行
select * from myemp;//不能查询到数据select * from myemp;//可以查询到数据
savepoint a;//创建保存点
rollback to a;//返回保存点
select * from myemp;//还是返回第一次创建的那个值
insert into myemp values(2);//创建第二行
rollback to a;//返回保存点
select * from myemp;还是只显示第一次创建的值
insert into myemp values(2);//从新创建第二行
commit;//提交
select * from myemp;//现在可以查询到创建的值rollback to a;//报错从未创建过报错点(以为以提交)
如果有多个保存点，只能有后面的保存点返回前面的保存点而不能从前面的保存点到后面的保存点。
查看用户定义的表:select table_name from user_tables;
如何查询用户对象:
显示表结构:desc user_objects
查看用户定义的...:select * from user_catalog;
:select object_name,object_type from
2 user_objects where object_type = 'TABLE';
怎样去掉头部:set heading off;
打开:set heading on;
添加一个列:alter table emp1 2 drop column job;
删除一个列:alter table emp1 2 add(job varchar2(30) default 'clerk');
更改表名:alter table emp1 rename column sal to salary;
rename emp1 to emp01;
设置为不可用(不显示):alter table emp1 set unused columns sal;
创建注释:comment on table emp1 is 'emp1 information';
查询注释:select * from user_tab_comments where comments is not null;
删除不可用:alter table emp1 drop unused columns;
定义约束:
创建约束表:create table employees
(id number unique,
name varchar2(20) not null,
hiredate date,
email varchar2(30),
constraint employee_email_uk unique(email));//用户加的约束名
select constraint_name from user_constraints where table_name = 'EMPLOYEES';
创建行:insert into employees values
(1324,'do',sysdate,'1@https://www.doczj.com/doc/9f12032164.html,');//创建成功
insert into employees values
(1234,'doo',sysdate,'1@https://www.doczj.com/doc/9f12032164.html,');//违反唯一约束条件(SCOTT.SYS_C005038)
insert into employees values
(1234,'doo',sysdate,'1@https://www.doczj.com/doc/9f12032164.html,');//创建成功
表及约束:create table employee3
(id number,
name varchar2(20),
constraint employee3_id_pk primary key(id));
select constraint_name from user_constriaints where table_name = 'EMPLOYEE3';
外件约束
CHECK约束: create table employee5(
id number,
name varchar2(20),
sal number(7,2),
constraint emp_sal_ch check(sal > 800));
insert into employee5 values(1,'1name',100);
alter table employee5
add constraint emp_id_pk primary key(id);//表已更改
alter table employee5
modify(name varchar2(20) not null);//表已更改
删除约束: ALTER TABLE employee5
DROP CONSTRAINT emp_id_pk;
alter table departments
drop primary key cascade;
无效化约束:alter table employee5
disable constraint emp_id_pk cascade;
激活无效化约束: alter table employee5
enable constraint emp_id_pk;
创建视图:create view v_emp20
as
select empno,ename,sal,deptno from
emp where deptno = 20;
授权:grant create view to scott
撤销授权:revoke view from scott;
select empno,ename,sal,deptno from
emp where deptno = 20;
select * from v_emp20;
创建复杂视图: create or replace view v_emp_sal
(deptno,maxsal,minsal,avgsal,countnum)
as
select deptno,max(sal),min(sal),avg(sal),count(*)
from emp group by deptno;
select view_name from user_views;
select view_name,text from user_views;//text查询上下问
如何在视图中插入数据:insert into v_emp20 values(3344,'feng',3900,20);
创建一行:insert into v_emp20 values(3344,'feng',3900,20);
删除行:delete from v_emp20 where empno = 3344;
删除视图:drop view v_emp21;
创建:update v_emp20 set sal = 3800 where empno = 7788;
select object_name from user_objects
where object_type ='VIEW';
尾例:select rownum,deptno,dname,loc from dept;
工资最高的前三行：select rownum num,empno,ename,sal,job from
(select * from emp where sal is not null order by sal desc)
where rownum<4;
最早入职的5位员工：select rownum num,empno,ename,sal,job,hiredate from
(select * from emp order by hiredate)
where rownum <6;
序列创建:1.create sequence myseq;
2.create sequence myseq1 start with 100 increment by 10;
3. create sequence myseq2 start with 1000 increment by 100
maxvalue 999999 nocache nocycle;
查询序列: desc user_sequences
select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY from user_sequences;
select
SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,last_number from user_sequences;
查询不经值:
获得下一个序列和当前序列的方法:select myseq1.nextval from dual;
修改步进：alter sequence myseq
increment by 2
maxvalue 999999;
插入:insert into dept values(myseq.nextval,'sales','chongqing');
删除序列:drop sequence myseq3;//序列不存在:select myseq3.currval from dual;
创建所以:create index emp_ename_idx on emp(ename);
查询:desc user_indexes;
select object_name from user_objects where object_type = 'INDEX';
查看索引信息:select index_name,table_name,column_name from user_ind_columns;
创建同义词:create synonym e for emp; select * from e;
转换到授权帐户: grant create synonym to scott;
撤销授权:revoke synonym from scott;
删除同义词:drop synonym ve;
创建所有用户都能访问的同义词:conn sys/tiger as sysdba
create public synonym sy_e for scott.emp;
select * from scott.emp;
删除:drop public synonym sy_e;;
集合：create table test_a( create table test_b(
a_id number, b_id number,
a_name varchar2(20)); a_name varchar2(20));
创建行: insert into test_a values(1,'a0'); select * from test_c
insert into test_a values(2,'a1'); union
insert into test_b values(1,'a0'); select * from test_d; //去掉从复的
insert into test_b values(3,'a3'); select * from test_c
insert into test_b values(1,'a2'); union all
select * from test_d;//不去掉从复的
交集: select * from test_c 补集:select * from test_c
intersect intersect
select * from test_d; select * from test_d;
exists(子查询存在为TRUE,不存在为FALSE):select * from emp e
where exists(select deptno from dept d where e.deptno = d.deptno);
select * from emp e
where exists(select deptno from dept where deptno = 100000);
select empno,ename,deptno from emp e where deptno in
(select deptno from dept d where d.deptno = e.deptno);
select empno,ename,deptno from emp e where deptno in
(select deptno from dept d where d.deptno = e.deptno and d.deptno = 10);
结果相同的两种表达式:select empno,ename,deptno from emp e
where not exists(select * from dept d where e.deptno = d.deptno and deptno = 10);
select empno,ename,deptno from emp e
where deptno not in(select * from dept d where e.deptno = d.deptno and deptno