oracle常用知识,oracle的基本知识

一、 基本

(一)三种进入客户端的方式

1.Dos命令行下：sqlplus用户名：scott密码：tiger,sqlplus scott/tiger

2.开始－运行－sqlplus图形版的SQLPLUS

3.http://localhost:5560/isqlplus

(二)解锁

1.sqlplus sys/*** as sysdba//***为密码

2.alter user scott account unlock; //用户scott解锁

(三)描述表

1.desc emp //描述emp表

2.dual //只有一条记录，用来计算各种表达式，显示特殊数据

EX：select sysdate from dual;//显示当前时间

(四)基本设置

1.set linesize 200;//用于设定每行显示的宽度

2.set pagesize 30;//设置显示的页数

(五)创建新用户

1.backup scott

exp//导出***用户至当前目录

2.超级管理员登陆

sqlplus sys/*** as sysdba

已在SQL中：conn sys/密码as sysdba;

删除用户:drop user *** cascade;

3.create user

create user fxw identified by ffxxww default tablespace users quota 10M on users;

4.grant

grant create session, create table, create view to fxw;

5.import the data

imp//由当前目录导入***用户

(六)简单备份方法

create table dept 2 as select * from dept;

(七)回退

rollback;

(八)注释

1.多行：/* */

2.单行--

二、select语句

(一)取数据

1.select * from salgrade;//把salgrade表中的所有数据取出

2.select ename,deptno from emp;//把emp中所有数据的ename和deptno取出

(二)取别名

1.select ename,sal*12 annual_sal from emp;//给sal*12取别名annual_sal

2.select ename,sal*12 "annual sal" from emp;//双引号使其保持原来的格式

(三)空值的处理

1.任何含有空值的表达式，结果都是空值

2.select ename, sal, comm from emp where comm is null;

3.select ename, sal, comm from emp where comm is not null;

(四)字符串

1.字符串的表示：''

2.字符串连接符：||

3.两个单引号''表示一个单引号'

EX:select ename || 'asfsdf' from emp;

(五) distinct

select distinct deptno from emp;//去掉重复的deptno

(六) where

1.select * from emp where deptno = 10;//从emp中取出deptno=10的数据

2.select * from emp where empno < > 10;//<>表示不等于

3.select ename, sal from emp where sal between 800 and 1500;

等价于select ename, sal from emp where sal >= 800 and sal <= 1500;

4.select sal from emp where sal in (800, 1500, 2000);

//取出sal等于800或1500或2000的值

5.select ename from emp where ename like '%ALL';

//模糊查询，通配符(正则表达式)

1)_表示一个字符，%表示一个或多个字符

2)如果本身就有%，使用转义字符

3)修改转义字符符号

EX:select ename from emp where ename like '%$%ALL' escape '$';

(七) order by

1.select * from dept order by deptno desc;

//排序，默认升序(asc)，降序加desc

2.select * fromemp order by deptno asc, ename desc;//组合

(八) sql_function (单行函数)

1.lower

select lower(ename) from emp;//转换成小写

2.upper

select upper(ename) from emp;//转换成大写

3.substr

select substr(ename, 1, 3) from emp;

//从第一个字符开始截，一共截3个字

4.chr

select chr(65) from dual;//ASCII码转换成字符

5.ascii

select ascii('A') from dual; //字符转换成ASCII码

6.round

1)select round(23.6552) from dual; //四舍五入

2)select round(23.6552, 2) from dual;//四舍五入到小数点后2位

7.to_char

1)select to_char(sal, '$99,999.9999') from emp;

//格式化输出数字(9代表一位数字，没有就不显示，但对于小数点后的强制显示)

2)select to_char(sal, '$00,000.0000') from emp;

//0代表一位数字，没有显示为0

3)select to_char(sal, 'L99,999.9999') from emp;//L代表本地货币

4)select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;

//格式化输出日期

5)select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

//格式化输出当前时间,24小时格式输出小时数

8.to_date

select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'YYYY-MM-DD HH24:MI:SS');

//输出1981年2月20日后入职的数据

9.to_number

select sal from emp where sal > to_number('$1,250.00', '$9,999.99');

//输出薪水大于$1,250.00的数据

10.nvl

select ename, sal*12 + nvl(comm, 0) from emp;

//如果comm的值是空值用0替代，否则直接输出

(九) group function(组函数)

1.区别：

1)单行函数：一条输入对应一条输出

2)组函数:多条输入对应一条输出

2.max

select max(sal) from emp;//求最高薪水值

3.min

select min(sal) from emp;//求最低薪水值

4.avg

1)select to_char(avg(sal), '999999.99') from emp;

//求平均薪水值，并保留到小数点后两位

2)select round(avg(sal), 2) from emp;

//求平均薪水值，并四舍五入到小数点后两位

5.sum

select sum(sal) from emp;//求和

6.count

1)select count(*) from emp;//求出emp表中有多少条记录

2)select count(comm) from emp; //count不计算空值

3)select count(*) from emp where deptno = 10;

//10这个部门一共有多少人

(十) group by

1.select deptno, avg(sal) from emp group by deptno;//分组求出平均薪水

2.select deptno, avg(sal) from emp group by deptno, job;//组合分组

3.错误：select ename, max(sal) from emp;

1)因为max只能有一个值，但是等于max的值可能有好几个，不能匹配。

2)应用子查询：

select ename from emp where sal = (select max(sal) from emp);

4.规则:出现在select的字段。如果没有出现在组函数里必须出现在group by里

Having where是对单条语句进行过滤，对分组后的使用having

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

(十一)单条select执行顺序：

select ... from... where ... group by... having ... order by...；

(十二)子查询

在select语句中套select语句)：

select ename, sal from emp where sal > (select avg(sal) from emp);

技巧：把子查询的结果当成一张表

(十三)table connection

1.自连接

select e1.ename, e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

2.连接-SQL1992: select ename, dname from emp, dept;//笛卡尔乘积

1)select ename, dname from emp, dept where emp.deptno = dept.deptno;//等值连接

2)select ename, grade from emp e，salgrade s where e.sal between s.losal and s.hisal; //非等连接

3.连接-SQL1999：

1)select ename, dname from emp cross join dept;//笛卡尔乘积

2)select ename, dname from emp join dept on (emp.deptno = dept.deptno);

//等值连接

#:另一种写法：

select ename, dname from emp join dept using(deptno);

//但不推荐，要求条件过多，如类型一致，少了不宜查错

3)select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);//非等值连接

4.新语法把过滤条件和连接条件分开，读起来更明确。

select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno);

5.外连接

1)select e1.ename, e2.ename from emp e1 left outer join emp e2 on(e1.mgr = e2.empno);

//左外连接会把左边那张表的多余(不能和另一张表连接)的数据拿出来,outer可省略

2)select ename, dname from emp e right outer join dept d on (e.deptno = d.deptno); //右外连接

3)select ename, dname from emp e full outer join dept d on (e.deptno = d.deptno);//全连接

(十四)rownum

1.默认显示：先导入的先显示

2.伪字段：

rownum是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列(强调：先要有结果集)。简单的说rownum是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1，而有其他大于1的值。

EX：select ename from emp where rownum <= 5;

3.限制：不能接>号和>=号

4.oracle特有，其他数据库不兼容

5.所有数据库里取第几行到第几行最麻烦：

select ename from (select rownum r, ename from emp) where r > 10;

(十五)多表连接子查询：

1.求部门中哪些人薪水最高：

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);

2.求部门平均薪水的等级：

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);

3.求部门平均的薪水等级：

select deptno, avg(grade) from (select deptno, ename, grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t;

4.求哪些人是经理人：

select ename from emp where empno in (select distinct mgr from emp);

5.不准用组函数，求薪水的最高值：

select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));

6.求平均薪水最高的部门的部门编号：

select deptno, avg_sal from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno));

7.求平均薪水最高的部门的部门名称：

select dname from dept where deptno = (select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno)));

#：组函数嵌套(最多只能嵌套两层)：

select dname from dept where deptno = (select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) where avg_sal = (select max(avg(sal)) from emp group by deptno));

8.求平均薪水的等级最低的部门的部门名称：

select dname, t1.deptno, grade, avg_sal from (

select deptno, grade, avg_sal 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) )t1

join dept on (t1.deptno = dept.deptno)

where t1.grade = (

select min(grade) from (

select deptno, grade, avg_sal 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))

);

#：简化办法-创建视图：

1)create view v$_dept_avg_sal_info as select deptno, grade, avg_sal 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);

2)select dname, t1.deptno, grade, avg_sal 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 t2);

3)若权限不足，则：

conn sys/密码as sysdba;

grant create table, creat view to scott;

9.求比普通员工的最高薪水还要高的经理人名称：

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)

);

10.球薪水最高的前5名雇员

select ename, sal from (select ename, sal from emp order by sal desc) where rownum <= 5;

11.求薪水最高的第六到第十名雇员

select ename, sal from (select ename, sal, rownum r from (select ename, sal from emp order by sal desc)) where r >=6 and r <= 10;

(十六)面试题：

1.比较效率

select * from emp where deptno = 10 and ename like ‘%A%’;

select * from emp where ename like ‘%A%’ and deptno = 10;

第一个效率高，对数字的比较效率较高

2.有三个表格S,C,SC

S(SNO, SNAME)代表(学号，姓名)

C(CNO, CNAM, CTEACHER)代表(课号，课名，教师)

SC(SNO, CNO, SCGRADE)代表(学号，课号，课号成绩)

问题：

1， 找出没选过“liming”老师的所有学生姓名

select sname from s join sc on (s.sno = sc.sno) join c(c.cno = sc.cno) where c.cteacher <> ‘liming’;

2， 列出两门以上(含两门)不及格学生姓名及平均成绩

select sname where sno in (select sno from sc where scgrade < 60

group by sno having count(*) >= 2);

3， 即学过1号课程又学过2号课程所有学生的姓名

select sname from s where sno in (select sno from sc where cno = 1 and cno in (select sno from sc where cno = 2);

三、DML语句

(一) select

(二) insert

1.insert into dept2 values (50, ‘bill’, ‘dl’);

2.insert into dept2 (deptno, dname) values (60, ‘setve’);

3.insert into dept2 select * from dept;

#:要求，子查询拿出来的东西必须与插入对象的类型一致

(三) update

update emp2 set sal = sal * 2, ename = ename || ‘-‘ where deptno =10;

(四) delete

delete from dept where deptno = 10;

(五)事物(transaction)控制语句

1.开始

一个transaction起始于一条DML语句

#：一个rollback全回退

2.结束：

1)rollback;

2)commit；

#:之后再rollback;无效

3)执行DDL或DCL语句时自动commit

4)正常断开连接，自动commit，非正常断开自动rollback

四、DDL语句及数据库常用对象

(一) create

1.创建一张表

create table stu (id number(8) primary key, name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50)),

constraint stu_name_email_uni unique(email, name)

constraint stu_class_fk foreign key (class) re references class(id)

);

create table class ( id number(4) primary key, name warchar2(20) not null);

2.constraint

1)constraint约束取名

2)字段约束：写在字段名后 限制：无法写组合约束

3)表级约束：可组合约束

4)五种约束

a)NOT NULL (非空)--防止NULL值进入指定的列,在单列基础上定义,默认情况下,ORACLE允许在任何列中有NULL值

b)CHECK (检查)--检查在约束中指定的条件是否得到了满足

c)UNIQUE (唯一)--保证在指定的列中没有重复值.在该表中每一个值或者每一组值都将是唯一的

#：两个null不认为是重复

d)PRIMARY KEY (主键)--用来唯一的标识出表的每一行,并且防止出现NULL值,一个表只能有一个主键约束(语法上等于唯一+非空，逻辑上还有约束)

e)POREIGN KEY (外键)--通过使用公共列在表之间建立一种父子(parent-child)关系,在表上定义的外键可以指向主键或者其他表的唯一键

#:外键约束被参考的字段必须是主键

#:有子记录则不能删除

(二) alter

1.alter table add(addr varchar2(100));

2.alter table stu drop (addr);

3.alter table stu modify (addr varchar2(150));

4.alter table stu drop constraintstu_class_fk;

5.alter table stu add constraint stu_class_fk foreignkey (class) references class(id);

(三) drop

drop table stu;

(四)数据库常用对象

1.table

2.数据字典表

1)dictionary

a)存放数据字典表的表

b)desc dictionary;

c)select table_name from dictionary where table_name like ‘USER%’;

2)user_tables

a)desc user_tables;

b)select table_name from user_tables;

3)user_views

select view_name from user_views;

4)user_constraints

select constraint_name from user_constraints;

desc user_constraint;

3.index

1)create index idx_stu_email on stu(email);

2)drop index idx_stu_email;

3)select index_name from user_indexes;

4)当加主键约束或者唯一约束，则自动建立索引

5)一般在当访问字段量特别大，效率特别低时，建立索引，但不要轻易建立索引

6)优点：读的时候效率更高

7)缺点：插入时效率更低，占用空间

4.view

1)为子查询取别名

2)视图可以用来更新数据，但是很少用

3)优点：简化查询，保护私有数据

4)缺点：增加维护支出(表结构修改，视图也得改)

5.sequence

1)oracle特有

2)用来产生一个唯一的不间断的数字序列，一般用来做主键

3)create table article (

id number,

title varchar2(1024),

cont long);

4)create sequence seq;

5)create sequence seq start with 10 increment by 2;

6)drop sequence seq;

7)select seq.nextval from dual;

8)insert into article values(seg.nextval, ‘a’, 1234);

五、 数据库设计的三范式

(一)范式

数据库设计的规则

(二)原则

不存在冗余数据

(三)第一范式

要有主键，列不可分

(四)第二范式

不能存在部分依赖：

当一张表里有多个字段作为主键时，非主键的字段不能依赖部分主键

(五)第三范式

不能存在传递依赖：

(六)注意

实际情况具体分析，可能会违反三范式

六、PL_SQL

(一) oracle内部使用的编程语言

(二) EX

1)begin

dbms_output.put_line(‘HelloWorld!’);

end;

2)set serveroutput on;//显示输出

3)declare

v_name varchar(20);//默认v_开头

begin

v_name := ‘myname’;

dbms_output.put_line(v_name);

ends;

4)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(‘error’);

end;

5)declare

v_sal emp.sal%type;

begin

select sal into v_sal from emp

where empno = 7369;