`
isiqi
  • 浏览: 15949093 次
  • 性别: Icon_minigender_1
  • 来自: 济南
社区版块
存档分类
最新评论
阅读更多

u从一个用户转到令一个用户: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<=s.hisal and job<>'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 = '张三';


本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/furthersun/archive/2008/01/10/2033465.aspx

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics