数据库总结Oracle篇

Oracle篇

作者：fashion

QQ交流群: 658398544

个人博客网站:http://blog.csdn.net/p6620582

备注：相信自己，每天不断学习，一定会成为大神，一定会在IT行业中有所作为。

oracel的安装和卸载

1. 对于oracle的安装，网上都有安装步骤，注意一下安装时候，管理口令那里把用户scott用户解锁就是了，不过没有解锁也没事，后面有解锁的步骤。

2.完全卸载oracle11g步骤

1、 开始－＞设置－＞控制面板－＞管理工具－＞服务 停止所有Oracle服务。

2、 开始－＞程序－＞Oracle - OraHome81－＞Oracle Installation Products－＞ Universal Installer，单击“卸载产品”-“全部展开”，选中除“OraDb11g_home1”外的全部目录，删除。

5、 运行regedit，选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，按del键删除这个入口。

6、 运行regedit，选择HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services，滚动这个列表，删除所有Oracle入口(以oracle或OraWeb开头的键)。

7、 运行refedit，HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application，删除所有Oracle入口。

8、 删除HKEY_CLASSES_ROOT目录下所有以Ora、Oracle、Orcl或EnumOra为前缀的键。

9、 删除HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\MenuOrder\Start Menu\Programs中所有以oracle开头的键。

10、删除HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI中除Microsoft ODBC for Oracle注册表键以外的所有含有Oracle的键。

11、我的电脑-->属性-->高级-->环境变量,删除环境变量CLASSPATH和PATH中有关Oracle的设定。

12、从桌面上、STARTUP（启动）组、程序菜单中，删除所有有关Oracle的组和图标。

13、删除所有与Oracle相关的目录(如果删不掉，重启计算机后再删就可以了)包括：

1.C:\Program file\Oracle目录。

2.ORACLE_BASE目录(oracle的安装目录)。

3.C:\WINDOWS\system32\config\systemprofile\Oracle目录。

4.C:\Users\Administrator\Oracle或C:\Documents and Settings\Administrator\Oracle目录。

5.C:\WINDOWS下删除以下文件ORACLE.INI、oradim73.INI、oradim80.INI、oraodbc.ini等等。

6.C:\WINDOWS下的WIN.INI文件中若有[ORACLE]的标记段，删除该段。

14、如有必要，删除所有Oracle相关的ODBC的DSN

15、到事件查看器中，删除Oracle相关的日志 说明： 如果有个别DLL文件无法删除的情况，则不用理会，重新启动，开始新的安装，安装时，选择一个新的目录，则，安装完毕并重新启动后，老的目录及文件就可以删除掉了。

*************************菜鸟的分割线***************************

对于plsql客户端，用户注册，这里有通用的用户注册系列号、产品编号、密码

1、Serial Number:115139568

2、Product Number:keqtkf6mb5z5kww7wkl4u3m84emzk6644mgj4gt2

3、Password:xs374ca

*************************菜鸟的分割线***************************

数据库连接

连接数据库命令： conn 用户名/密码@网络服务名 [as sysdba/sysoper]

举例：sqlplus scott/tiger@localhost:1521/orcl

切换数据库用户:conn 用户名/密码

断开数据库连接：disc

退出数据库：exit/quit

修改密码：passw/password

行宽：每一行默认80字符 set linesize 90

页行：每页显示的行数 set pagesize 10

*************************菜鸟的分割线***************************

Scott用户的解锁：

第一种方法：

1. conn /as sysdba进入管理员用户下
2. alter user scott account unlock;用户scott解锁;
3. alter user scott identified by tiger;修改用户scott密码为tiger;

第二种方法：如果plsql客户端输入scott用户登录是出现用户被锁现象，就有第二种方法来解决。

1. 打开plsql用户，输入如下视图：

Username:system

Password:sysdba

Database:ORCL

Connect as:SYSDBA

2. 添加之后，点击ok，在这里找到Users，如下图所示：

3. 点开Users，找到scott，鼠标右键-edit（编辑），如下图所示：

4. 如上图所示：记住把Account locked取消，之后在Password填写scott用户的密码，点击Apply，点击Colse即可。
5. 下次登录时就可以成功登录scott用户了，如下图所示登录信息：

*************************菜鸟的分割线***************************

用户操作：

创建：create user 用户名 identified by 密码

详细：create user xiaom identified by m123

default tablespace users

temporary tablespace temp

quota 3m on users;

修改密码：自己修改密码：passw

修改其他用户密码：alter user 用户名 identified by 新密码

给定权限/角色：grant 权限/角色 to 用户名

给定角色：grant 角色名称 to username;

给定权限：grant 权限名 on 对象名 to 用户名 with grant option把自己得到的方案下的对象的权限分配给其他用户

撤销权限： revoke 权限名 on 对象名 from 用户名

删除用户：drop user 用户名 [cascade] 删除用户，cascade表示用户有对象，照样删除

*************************菜鸟的分割线**************************远程连接数据库

配置监听：

\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

listener.ora sqlnet.ora tnsnames.orc

修改host为计算机名/IP名->重新启动监听、Oracle服务

登录：sqlplus 用户名/密码@服务名|IP：端口号/实例名

切换：conn 用户名/密码@服务名|IP：端口号/实例名

1、查看所有用户

　　select * from dba_users;

　　select * from all_users;

　　select * from user_users;

　　2、查看用户系统权限

　　select * from dba_sys_privs;

　　select * from all_sys_privs;

　　select * from user_sys_privs;

　　3、查看用户对象权限

　　select * from dba_tab_privs;

　　select * from all_tab_privs;

　　select * from user_tab_privs;

　　4、查看所有角色

　　select * from dba_roles;

　　5、查看用户所拥有的角色

　　select * from dba_role_privs;

　　select * from user_role_privs;

　　6、查看当前用户的缺省表空间

　　select username,default_tablespace from user_users;

　　7、查看某个角色的具体权限

　　如grant connect,resource,create session,create view to TEST;

　　8、查看RESOURCE具有那些权限

用SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE';

*************************菜鸟的分割线**************************

表操作：

创建：create table 表名(字段名 字段类型,

字段名字段类型,

字段名字段类型,

字段名字段类型,

字段名字段类型,

字段名 字段类型);

create table stu(id number,name varchar2(10),sex char(2),age number(3));

insert into stu values(1,'王东','男',999);

查看表结构：desc 表名;

desc stu;

列操作：

增加列：ALTER TABLE tablename

ADD (columnname datatype);

alter table stu add phonenumber number(12);

修改列：ALTER TABLE table

MODIFY (columnname datatype);

alter table stu modify phonenumber number(14);

删除列： ALTER TABLE table

DROP (column);

alter table stu drop column phonenumber;

查询：desc stu;

日期的查询：select to_char(hiredate,'yyyy-mm-dd') from emp;

日期的插入：insert into emp(hiredate) values(to_date('1990/02/2','yyyy/mm/dd'));

insert into emp(hiredate) values(to_date('1990/02/2','yyyy/mm/dd hh/MM/ss ms'));

insert into emp(empno,hiredate) values(1110,to_date('1990/02/2 12:12:12','yyyy/mm/dd hh:mi:ss'));

insert into emp(empno,hiredate) values(1111,to_timestamp('1990/02/2 12:12:12:666666','yyyy/mm/dd hh:mi:ss:ff'));

取消重复行

select distinct deptno ,job from emp;

别名：一般加引号

列：

select distinct deptno 部门编号 ,job 职位 from emp;

select distinct deptno "部门编号" , job "职位" from emp;

select distinct deptno as "部门编号", job as "职位" from emp;

select distinct deptno as 部门编号, job as 职位 from emp;

#如果comm为null，那么取值为0

select sal*13+nvl(comm,0)年薪 from emp;

select ename || ' is a ' || job from emp;

select ename || ' 工资： ' || sal from emp;

表：

#别名

select e.deptno,e.ename from empe;

select e.deptno,e.ename from emp e where e.sal>4000;

#直接用表名

select emp.deptno,emp.ename from emp;

日期

select * from emp where hiredate>to_date('1982-1-1','yyyy-mm-dd');

#如何显示工资在2000到2500的员工情况

select * from emp e where e.sal>2000 and e.sal<=2450;

#between number1 and number2

#[number1,number2]

select * from emp e where e.sal between 800 and 2450;

like:

#字符区分大小写

select * from emp e where e.ename like 'M%';

select * from emp e where e.ename like '%N';

select * from emp e where e.ename like '%T%';

select * from emp e where e.ename like '%T%';

select * from emp e where e.ename like '___T_%';

in:

select * from emp e where e.empno in(800,1111,7934);

is null:

select * from emp e where e.mgr is null;

select * from emp e where e.mgr is not null;

逻辑操作符：or and

select * from emp e where (e.sal>500 or e.job='MANAGER' ) and e.ename like 'J%';

order by:

#默认自然顺序

select * from emp e order by e.sal ;

#升序、降序

select *　from emp e order by e.sal asc;

select *　from emp e order by e.sal desc;

#按别名排序

select ename,sal*12 "年薪" from emp order by "年薪" asc;

#order by columnname columnname-只要是表中存在的字段就好

select ename from emp order by sal desc;

#五个聚集函数max,min,avg,sum,count

select max(sal) from emp;//最大值

select min(comm) from emp;//最小值

select sum(sal)+sum(comm) from emp;//求和，数量

select avg(sal) from emp;//平均值

select count(comm) from emp;//计算

select max(sal) 最大工资,min(sal) 最小工资 from emp;

group by 和having// group by是聚合，分组。having分组条件

完整的sql语句

select[distinct] columnname from tablename [where 条件] [group by columname [having 条件]] [order by columnname [desc|asc]]

select avg(sal),min(sal) from emp e group by e.job;

#显示平均工资低于2000的部门号和它的平均工资- having是根据 分组后的数据 进行过滤 ：过滤可以使用聚集函数

select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;

select deptno,avg(sal) from emp group by deptno having avg(sal)<200000 order by avg(sal) desc;

#测试group by、 having 、order by顺序

select deptno,avg(sal) from emp order by avg(sal) desc having avg(sal)<200000 group by deptno;

#测试group by

#在select中凡是出现的列名，必须出现在group by 中

#聚集函数、表达式可以不出现

#没有select的可以出现

#如果字段与聚集函数一起select，那么需要group by ;并且，如果此时需要排序，那么需要用select的内容排序

select avg(sal) from emp group by deptno;

select deptno,comm,avg(sal)*10,count(*) from emp group by deptno,comm;

{

测试：#如果字段与聚集函数一起select，那么需要group by ;并且，如果此时需要排序，那么需要用select的内容排序

select avg(comm) from emp order by sal;

select ename,avg(comm) from emp order by sal;#错误

select ename,avg(comm) from emp;#需要分组

select ename,avg(comm) from emp group by ename;

select ename,avg(comm) from emp group by ename,avg(comm);#聚集函数不能再group by之后，可以在having后面

select ename,avg(comm) from emp group by ename having avg(comm)>3;

select ename,avg(comm) from emp group by ename having avg(comm)>3 order by sal;#不能排序：select中没有，不能排序。因为有select中有 字段 与 聚集函数

select ename,avg(comm) from emp group by ename having avg(comm)>3 order by ename;

}

子查询：

■ 什么是子查询

子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询

■ 单行子查询

单行子查询是指只返回一行数据的子查询语句

select * from emp where deptno=(select deptno from dept where deptno=30);

请思考：如何显示与SMITH同一部门的所有员工?

■ 多行子查询

多行子查询指返回多行数据的子查询

select * from emp where deptno in (10,20,30,40);

->select * from emp where deptnoin ( select deptno from emp );

select * from emp where deptno in(30);

->select * from emp where deptno in( select deptno from dept where deptno=30)

->select * from emp where deptno in( select deptno from dept where dname='SALES')

#多列子查询：返回多个字段的多行数据

select * from emp where (deptno,ename) in (select deptno,ename from emp where sal>800);

多表查询：

#多表查询是指基于两个和两个以上的表或是视图的查询.在实际应用中,查询单个表可能不能满足你的需求

1、如显示sales部门位置和其员工的姓名),这种情况下需要使用到(dept表和emp表)

思路：找到sales部门的编号deptno，根据编号找到对应的员工，工作地址

select e.ename ,d.loc from emp e,dept d where (e.deptno,d.deptno) in (select dd.deptno,dd.deptno from dept dd where dd.dname='SALES');

长的，笨的

select e.ename,d.loc from emp e,dept d where e.deptno =(select deptno from dept where dname='SALES') and d.deptno =(select deptno from dept where dname='SALES');

自连接：

自连接是指在同一张表的连接查询。

显示员工的上级领导的姓名

比如显示’FORD’的上级.

#从ee表中找到ford的上级编号，再从e中找到相同的编号，查询名称

select e.ename from emp e,emp ee where ee.ename='FORD' and ee.mgr=e.empno;

->select e.ename from emp e where e.empno = ( select ee.mgr from emp ee where ee.ename='FORD');

子查询-

1、显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

#部门编号为30的，工资都没我高

select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);

->

select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);

2、如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号

#比其中一个员工的工资高

select ename,sal,deptno from emp where sal>any( select sal from emp where deptno = 30) order by sal;

->

select ename, sal , deptno from emp where sal>(select min(sal) from emp where deptno=30) order by sal;

rownum: 1、虚拟的，查询的时候才虚拟出来的。表中实际不存在。

2、一般不能直接写>,只能直接写<

3、通过子查询，可以使用>

select rownum,empno,ename from emp where rownum <5 order by empno;

select b ,empno ,ename from (select rownum b,emp.* from emp) a where a.b>5 and a.b<10;

创建新表：

1、只创建表结构

create table rsz as select * from emp where 1=2;

2、表结构与数据一起创建-相当于复制

create table rsz as select * from emp;

insert into rsz (id,name,sal,job,deptno) select empno,ename,sal,job,deptno from rsz;

union：并集，去重

select * from (select * from emp where sal<1000 union select * from emp where sal <=2000)a order by empno;

union all:并集不去重

select * from (select * from emp where sal<1000 union all select * from emp where sal <=2000)a order by empno;

intersect：交集

select * from (select * from emp where sal<1000 intersect select * from emp where sal <=2000)a order by empno;

minus :差集

select * from (select * from emp where sal<2000 minus select * from emp where sal <=1000)a order by empno;

表内连接和外连接

select * from emp a,emp b where a.empno=b.mgr order by a.empno;

联系：查询king手下是谁

内连接：

select emp.empno,emp.ename,dept.dname from emp inner join dept on emp.deptno=dept.deptno;

select emp.empno,emp.ename,dept.dname from emp,dept emp.deptno=dept.deptno;

左外连接：左边表完全显示，右边表不完全显示

select * from dept left join emp on emp.deptno=dept.deptno;

select * from dept , emp where emp.deptno(+)=dept.deptno;

右外连接：右边表完全显示，左边表不完全显示

select * from dept right join emp on emp.deptno=dept.deptno;

select * from dept,emp where emp.deptno=dept.deptno(+);