oracle基础总结

矫情吗；* 2022-05-15 01:22 192阅读 0赞

从一个用户转到令一个用户：conn 请输入用户名：scott/tiger  
给scott用户解锁：alter user scott account unlock;  
sqlplus sys/bjsxt as sysdba 系统管理员的密码是：bjsxt  
一、oracle需要记住的：分页用：rownum  
修改表结构和添加约束条件用：alter table  
索引：是为了加快对数据得搜索速度。但索引是占空间的，如果太多  
会影响插入数据，修改数据，删除数据的速度。（create index idx\_stu\_email on stu(email)）  
视图：实际上就是给复杂的子查询起了一个别名。缺点：如果表结构变了  
跟这个表有关的视图都得改，所以视图适量最好！（create view v$\_temp as ..）  
序列：用来解决主键递增的问题，和mysql的Auto差不多。（create sequence seq\_article\_id start with 1 increment by 1）  
主键：string类型的作主键，没有int类型的作主键检索速度快。一、单条的select语句  
1、having 对分组进行限制  
eg:把平均薪水大于1000的组，从emp这张表中取出来。  
select avg(sal) from emp group by deptno having avg(sal)>1000;  
2、where语句是对单条记录进行过滤。  
执行的前后顺序：where group by having order by  
这个顺序不能颠倒，只能这么限制。  
3、 select \* from emp  
2 where sal > 1000 对取出来的每条数据进行过滤  
3 group by deptno 对过滤后的数据进行分组  
4 having 对分组之后产生的结果进行限制  
5 order by 对取出来的最后的数据进行排序  
4、薪水大于1200的雇员，按照部门编号进行分组，这些人分组之后的平均薪水必须大于1500，  
查询分组之内的平均工职，按照平均薪水的倒顺进行排列。  
select avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;二、组函数：定义：多行的输入产生一个输出。  
1、avg 求平均值  
max 求最大值  
min 求最小值  
sum 求总和  
count 求记录的数量  
distinct 唯一（去掉重复的）  
2、eg：求薪水的总和、最大值、最小值、平均值。  
select sum(sal),max(sal),min(sal),avg(sal) from emp;  
eg：求emp表的记录数量  
select count(\*) from emp;  
eg：把emp表里的部门编号取出来，去掉重复的。  
select count(distinct deptno) from emp;三、多表连接  
1、eg：取出这个人的名字，和这个人所在部门的名字。（从两张表里取数据）  
select ename,dname from emp,dept where emp.deptno=dept.deptno; 笛卡尔乘积（不写连接条件时）：eg（两张表）：一张表的每一条记录和另一张的每一条记录都组合一次，作为一个结果被取出来。  
解释：select count(\*) from emp; 14  
select count(\*) from dept; 4  
select ename dname from emp,dept; 56 (前两者的乘积，一张表的每一条记录和另一张的每一条记录都组合一次。)  
2、如果想取两张表里重复的字段，得明确是从哪张表里往外取。方法：表名.字段（见例子）  
select ename,dname,deptno from emp,dept where emp.deptno=dept.deptno;  
报的错误：未明确定义列。  
select ename,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno;  
注：deptno在两张表里都有，但在不同的表中取的效率不见的相同。（有一个效率的问题）  
从dept这张表中，取deptno速度应该快些。  
3、从emp表和salgrade表中，取出ename、sal、grade  
select ename,sal,grade from emp,salgrade where sal between losal and hisal;  
注：这个就是看grade这个记录怎么取出来，和这两张表连接条件怎么写，是用between and 写的  
是用一个区间来表示这两张表的连接条件的。  
4、从emp表、dname表、salgrade三张表中，求出ename、deptno、grade三个字段，和job不是PRESIDENT的。  
select ename,dname,grade from emp e,dept d,salgrade s where e.deptno=d.deptno and  
e.sal>=s.losal and e.sal’PRESIDENT’;  
连接条件：e.deptno=d.deptno and e.sal>=s.losal and e.sal<=s.hisal  
数据过滤条件：job=’PRESIDENT’  
表起别名：emp e  
字段起别名：ename e  
不是的写法： 和 ！＝ 是一样的。四、子查询（关键：把子查询的结果当成一张表！！！）  
1、eg：谁赚的钱最多  
select ename,max(sal) from emp; 报错：不是单组分组函数。  
select ename,sal from emp where sal = (select max(sal) from emp);  
注：子句是对主句的限制，主句是把ename，sal从emp表里取出来，子句对主句做了限制，把sal＝最大值  
的ename，sal取出来。  
我的错误：where子句放在from emp 前面了，低级错误。  
错误句子：select ename,sal where sal = (select max(sal) from emp) from emp;  
2、这个子查询可以出现在where语句里面，也可以出现在from语句里面。  
3、eg：求出哪些人的工职位于所有人的平均工职之上。  
select ename,sal from emp where sal > (select avg(sal) from emp);  
eg：按照部门进行分组之后，每个部门赚钱最多的那个人。  
select ename,sal from emp where sal = (select max(sal) from emp group by deptno);  
错误：sal＝一个值，所以说子句select max(sal) from emp group by deptno 取出来的应该是一个值，而不是多个值。 select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);（结果如下：）  
ENAME SAL DEPTNO  
BLAKE 2850 30  
FORD 3000 20  
SCOTT 3000 20  
KING 5000 10 解释：子句是对主句的限制，select max(sal) from emp group by deptno 它从emp取出来的是3000、2850、5000  
而in的意思是：是这三个值的其中的一个就行，所以说10部门的3000这条记录就能被取出来。需要注意的是：  
子句取出的结果只是对主句的限制，而不是主句从子句的结果中取数据，主句还是从emp这张表中取数据，只要  
取出的结果符合子句的限制就可以。在这里也就是说：只要主句从emp这张表里，取出的sal是3000、2850、5000  
中的一个即可，所以说取的结果很有可能不准，很有可能把不是每个部门最高的值，取出来。  
从另一方面说：就是子句对主句的限制，不够严密！  
  
子查询的整个语句需要拆开来理解，不能放到一起理解！  
  
注：in是在什么里，也就是说我们所取出来的sal是在这些值之中的一个。  
子句的意思是，把个部门里薪水最高的取出来。  
错误：如果10部门里，还有一个薪水是3000的，也会被取出来，见下面（自己插入一条做下试验）：  
也就是说当数据多时，上面这条sql就不准了！！  
(不理解这里面内部怎么执行的！为什么把3000这条记录也取出来了，它内部是怎么取的??)  
ENAME SAL DEPTNO  
BLAKE 2850 30  
FORD 3000 20  
SCOTT 3000 20  
DAFEI 3000 10  
KING 5000 10 还是利用子查询解决这个问题，这个子查询放到from里。  
select max(sal),deptno from emp group by deptno;  
下面这个是，取出来的结果，看起来这个结果，很象一张表，有字段名，有数据，我们可以把它  
当成一张表，从中往外取数据！  
MAX(SAL) DEPTNO  
2850 30  
3000 20  
5000 10  
1 select ename,sal from emp  
2 join (select max(sal) max\_sal,deptno from emp group by deptno) t  
3 on (emp.sal = t.max\_sal and emp.deptno = t.deptno);  
这回10部门，3000，这条记录就取不出来了！！也就是说：这会子句对主句的限制比较严密。  
  
注：max(sal) max\_sal 把max(sal)看成一个字段，并重命名了一下。  
t 把子句取出来的结果，看成一张表，并重命名为t。4、求每个部门的平均薪水，并求出平均薪水的等级是多少?即每个部门的平均薪水的等级。（自己做！）  
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);  
注：以后自己写都用sql1999的语法，不用1992的看着不舒服，有点乱！！  
select deptno,avg\_sal,grade,dname from  
(select deptno,avg\_sal, grade from  
(select deptno,avg\_sal,grade from  
(select deptno,avg(sal) avg\_sal from emp group by deptno)t,salgrade s where t.avg\_sal between s.losal and s.hisal))m join dept using (deptno);  
  
为什么这么写不对?错误：未明确定义列?  
select deptno,avg\_sal,grade,dname from (select deptno,avg\_sal, grade from  
(select deptno,avg\_sal,grade from  
(select deptno,avg(sal) avg\_sal from emp group by deptno)t,salgrade s where t.avg\_sal between s.losal and s.hisal))m join dept on (m.deptno = dept.deptno);  
eg：求每个部门中哪些人的薪水最高。  
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) ;  
注：是求每个部门中薪水最高的人，不是求所有部门薪水最高的人，前者是多个值，后者只有一个值。  
以后用sql1999的语法，where语句里只能写数据过滤条件，不能写表连接条件，养成好的习惯。  
5、把这个人的名字，及他的经理人的名字。（自连接：一张表起两个别名，当成两张表用，进行多表连接！）select e1.ename,e2.ename from emp e1 ,emp e2 where e1.mgr =e2.empno;  
五、sql1999为了把连接条件和过滤条件分开来，看的清楚。  
eg：select ename,dname,grade from emp e,dept d,salgrade s  
where e.deptno = d.deptno and grade between s.losal and s.hisal （连接条件）  
and job ‘CLERK’; （过滤条件） sql1992 的写法 eg：select ename,dname,from emp,dept; sql1992的写法 取的是56行，笛卡尔乘积  
  
select ename,dname,from emp cross join dept; cross join：交叉连接 sql1999的写法（以后用新的写法）  
  
eg：select ename,dname from emp,dept where emp.deptno = dept.deptno; （sql1992 连接条件写在where语句里） select ename,dname from emp join dept on (emp.deptno = dept.deptno); （sql1999 新的语法：连接条件不写在where语句里，where语句里只写数据过滤条件！！！，on里面写连接条件）  
select ename,dname from emp join dept using (deptno); （sql1999 等值连接简单的写法，不等值连接就得象上面那样写，但不推荐使用，原因：using是假设emp表和dept表都有deptno这个字段，并且字段的类型 必须相同，如果把deptno这个字段删了，就会报些奇怪的错误，不好找！）  
eg：select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal); （sql1999 的不等值连接，连接条件数据过滤条件区分的很明显）  
eg：select ename,dname,grade from  
emp e join dept d on (e.deptno = d.deptno) （emp表和dept表连接）  
join salgrade s on (e.sal between s.losal and s.hisal) （emp表和salgrade表连接）  
where ename not like ‘\_A%’; （where语句里写数据过滤条件）  
注：此语句是三张表的连接（可以写很多表的连接，按照这样的写法），和哪张表连接，看的非常清楚，并且数据过滤条件也看的非常清楚。 eg：select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno); （用sql1999写自连接,这个时候KINK这个字段是拿不出来的，原因：KINK是e1的一个字段，它和e2的字段不能进行连接，所以拿不出来。）  
eg：select ename,dname from emp e left join dept d on (e.deptno = d.deptno); （左外连接：会把左边这张表的多于的数据，也就是不能和另外一张表产生连接的数据取出来，这个时候KINK就能拿出来。）  
eg：select ename,dname from emp e right join dept d on (e.deptno = d.deptno); （右外连接：同上：会把右边这张表的多于的数据，也就是不能和另外一张表产生连接的数据取出来，这个时候就会把OPEATIONS拿出来，不写右外连接这 个字段是拿不出来的。）  
eg：select ename,dname from emp e full join dept d on (e.deptno = d.deptno); （全外连接：会把左右两边不能产生连接的数据都拿出来。sql1992的语法，不支持这个。）  
六、求：部门平均薪水的等级：（这个是先求平均薪水，之后再求平均薪水的等级）  
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);  
求：部门平均的薪水等级：（这个是先求薪水等级，再平均）  
  
select deptno,avg(grade) from (select ename,deptno,grade from emp join salgrade s on  
(emp.sal between s.losal and s.hisal))t group by deptno;  
注：当group by deptno时，deptno这个字段必须是表中存在的字段，也就是t表中必须有deptno这个字段，不存在就会报错： “DEPTNO”: 标识符无效。  
eg：select \* from emp group by deptno; 注：group by 时，emp这张表里必须有deptno这个字段。  
七、oracle练习题：  
1、雇员中有哪些人是经理人 （解释：就是在雇员编号里出现了经理编号的那个雇员就是经理人）  
select ename from emp where empno in (select distinct mgr from emp);  
2、不准用组函数，求薪水的最高值（面试题）  
select sal from emp where sal not in  
  
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal)); 理解（思想）：做自连接e1.sal < e2.sal，把e1里的sal能和e2里的sal做连接的取出来，而那些不能和e2做自连接的，就是sal的最大值，再想办法把最大值取出来，就是不在这些能做自连接之内的贝。  
3、求平均薪水最高的部门的部门编号  
select avg\_sal ,deptno 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));  
理解：先求平均薪水，再求平均薪水最大的值，重点：把一个子查询看成一张表，另一个子查询看成一个值。  
4、求平均薪水最高的部门的部门名称  
select avg\_sal,dname from （我的写法）  
(select avg\_sal ,deptno 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)))t join dept on (t.deptno = dept.deptno);  
老马的写法：就是把已经取出来的deptno当做一个值，也就是数据过滤条件，去dept表里取dname。  
select dname from dept where deptno =  
(  
select deptno 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)  
)  
)  
5、求平均薪水最高的部门的部门编号  
  
select deptno,avg\_sal from  
(select avg(sal) avg\_sal,deptno from emp group by deptno)  
where avg\_sal =  
(select max(avg(sal)) from emp group by deptno) 注释：这里用到了一个组函数的嵌套max(avg(sal))，一个组函数输出的是一组值，嵌套两个组函数只能  
输出一个值，所以组汉数只能嵌套两层。 6、求平均薪水的等级最低的部门名称  
  
select grade,dname,t1.deptno,avg\_sal from  
(  
select avg\_sal,deptno,grade from  
(select avg(sal) avg\_sal,deptno from emp group by deptno)t  
join salgrade s on (t.avg\_sal between s.losal and s.hisal)t1  
join dept on (t1.deptno = dept.deptno)  
where t1.grade =  
(  
select min(grade) from  
(  
select avg\_sal,deptno,grade from  
(select avg(sal) avg\_sal,deptno from emp group by deptno)t  
join salgrade s on (t.avg\_sal between s.losal and s.hisal)  
)  
) 注释：当出现未明确定义列时，可能就是要取的字段，oracle无法判断应该从哪张表里往外取，所以就  
t1.deptno，明确是从哪张表里取出来的。（我总犯这个错误，下回要记住，要明确要从那张表取数 据）。t1.grade这里也是明确那张表里的grade。 select avg\_sal,deptno,grade from  
(select avg(sal) avg\_sal,deptno from emp group by deptno)t  
join salgrade s on (t.avg\_sal between s.losal and s.hisal)  
这个例子中，这张表可以看成一个视图！！  
  
7、视图：  
  
创建视图时：权限不足，原因：就是以scott/tiger身份登陆进来，scott这个用户没有创建视图的权限。  
conn sys/bjsxt as sysdba 以dba身份登陆。 grant create table, create view to scott; 授权scott用户，创建表和视图的权限。  
  
创建一个视图，记住视图的创建格式。  
create view v$\_dept\_avg\_sal\_info as  
select avg\_sal,deptno,grade from  
(select avg(sal) avg\_sal,deptno from emp group by deptno)t  
join salgrade s on (t.avg\_sal between s.losal and s.hisal);  
  
select dname,grade,avg\_sal,t1.deptno from v$\_dept\_avg\_sal\_info t1  
join dept on (t1.deptno = dept.deptno)  
where t1.grade =  
(  
select min(grade) from v$\_dept\_avg\_sal\_info  
); 8、比普通员工的最高薪水还要高的经理人名称  
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)); 9、面试题：比较效率  
select \* from emp where deptno = 10 ename like ‘%A%’;  
select \* from emp where ename like ‘%A%’ and deptno = 10;  
上面的速度快：原因：int类型的检索速度比string类型的检索速度快，理论上是这样。  
但实际中不见得是这样，因为很有可能oracle对这些东西做了优化。 10、给数据库建立不同得用户，把相关的表给他到过去，让他在单独的不同的空间里做试验。  
  
oracle的逻辑结构：一个大数据库，又分为不同的表空间，这个表空间就是放表的地方，  
eg：emp、dept、salgrade这三张表就放在，users这个表空间里，在这个表空间里放在scoot这个用户所有的表，也就是scott用户所有拥有的表。当你以scott用  
户登陆以后，其实你访问的是users这个表空间里的表。当两个人都以scott用  
户登陆，都对users这个表空间里的表进行操作，这时就会存在数据不一致的问  
题，怎么解决这个问题：  
  
oracle怎么解决多用户的问题： 就是我在服务器上，新建个用户liuchao，然后在users这个表空间里，liuchao这个用户分配一块表空间，然后再把他要用的表，导到给他分配  
的表空间里，这样就不会存在数据不一致的问题了，以后liuchao登陆时，访问  
的就是，给他分配的表空间里的表。 一、oracle创建新用户，导出、到入scott用户的所有东西。（这个就是从oracle中导出、导入数据的步骤！！）  
  
1、用超级管理员登陆，只有超级管理员才能创建用户。  
conn sys/bjsxt as sysdba;  
2、删除用户  
drop user liuchao cascade；  
3、backup scott 备份scott这个用户相关的表和其他东西。  
exp 导出scott用户的所有东西，这里可能也包括其他用户导出的数据。在下面导入的时候，要求  
输入一个用户名，这个用户名添scott，意思是：把scott这个用户的东西导入进去，不导入  
其他用户的东西。  
缓冲区：内存里的一个小区域，填满了之后再统一的拿到硬盘上来。  
4、create user liuchao identified by liuchao default tablespace users quota 10M on users;  
创建一个用户，他的用户名和密码都是liuchao，他登陆时默认的表空间是users，并且  
给这个用户分配10M的表空间，也就是说这个用户只能往users这个表空间里，放10M的数据。  
5、给新建的用户分配权限。  
grant create session,create table,create view to liuchao  
给这个用户登陆的权限、创建表的权限、创建视图的权限。  
6、把导出的scott用户的所有东西，导入到新创建的用户的表空间里去。  
import the data  
imp  
11、insert语句：可以按着顺序所有字段都进行插入，也可以插入特定的字段的值，只要把这个字段的值写出来就可以了。  
insert into dept valus(50,’game’,'bj’);  
注：为什么game要加”，因为dname是string类型的，也就是varchar2在数据库里的显示，所以说当字符串入库时，必  
须加上”，而int类型的就不用加”。 insert into dept (deptno,dname) values(’60′,’game2′);  
这个就是只插了两个字段，并不是所有字段都出入数据。  
  
利用子查询插入数据，但注意的是dept数据的类型和dept2的数据的类型必须一样。  
insert into dept2 select \* from dept;  
  
12、恢复数据  
rollback  
对某张表进行备份：create table emp2 as select \* from emp;  
  
13、数据库设计的三范式  
  
范式：数据库设计的规则。  
三范式追求的原则：不存在冗余数据，意思是：同样的数据不存第二遍。 第一范式：  
1、设计任何表都要又主键。  
2、列不可分，但也不能重复。意思是：列分到不能再分为止，也就是说不能把学号、姓名、年龄放到一个字段里，要分成三个字段。一个字段怎么放：0123\_张三\_23 取出这个字段之后，再做字符串分割。 第二范式：多对多关系表设计的时候，你所应该遵循的一种东西。  
当一张表里有多个字段作为主键的时候，非主键的字段不能够依赖于部分主键，也就是不能  
存在部分依赖。也就是说：在学生3这张表中，学生姓名这个字段依赖于学生编号这个字段，但学生编号在这张表里不是主键，也就是学生姓名依赖于部分主键学生编号，在这张表里，  
学生和教师的编号，才是主键，这种存在部分依赖就是存在着冗余字段。 多对多的关系：拆分成三张表。  
eg：求教张三这人老师的名字。三张表关联。  
select te\_name from te join su\_te on (te.id = su\_te.teId) join su on (su\_te.suId = su.id) where su\_name = ‘张三’; 分成三张表的好处：不用张老师的姓名每出现一次，就存一次，只在教师表里存一次就够了不存在冗余字段，如果向上面设计成一张表，张老师的姓名每出现一次，就要存一次。  
第三范式：存在传递依赖：意思是：其他字段必须直接依赖于主键，而不能依赖于其他字段。  
eg:班级编号依赖于主键学号，而班级名字并不是依赖于学号，它依赖的是班级编号，所以说，班级名字和班级信息就是冗余字段，在su这张表里，只留班级编号这个字段就可以，其他字段放到另一张表里，班级表里。 eg：张三这人他所在班级的名称：  
select class\_name from class join su on (su.classId = class.id) where su\_name = ‘张三’;