Oracle总结1

1. 当前主流数据库：

|-微软：SQL Server、access

|-瑞典:Mysql

|-IBM: DB2

|-Sybase: Sybase

|-Oracle:Oracle

A. 小型数据库：access、foxbase。负载量小，100人内，成本千元内，安全性要求不高。例如留言板等。

B. 中型数据库：Mysql、SQL Server、Informix。日访问量5000-15000，成本万元内。例如电子商务网站等。

C. 大型数据库：Sybase<Oracle<DB2。海量负载。

2. 选择数据库，要看项目的规模：

a) 负载量多大（用户多大）

b) 成本

c) 安全

4. Oracle与其他数据库不同的是，每次登陆，登陆的是Oracle数据库的实例。每个用户，例如sys、system、scott，每个用户名登陆的数据库看到的数据对象（表、存储过程等）不同。

5. Oracle默认生成三个用户：

1. sys用户：超级管理员，权限最高，角色dba

2. system用户：系统管理员，角色dbaoper

3. scott用户：普通用户，密码tiger    该账号需解锁

 4.解锁账号：alter user 账号名 account unlock

5.sys有create database的权限，而system没有。

6. Oracle管理工具：

1. SQLPLUS

2. Oracle Enterprise Manager Console

3. pl/sql developer

7. SQL * PLUS命令

1.登录命令：sqlplus 用名名/密码

2. 切换账号：conn 用户名/密码;

2. 断开连接：disc

3. 修改密码：alter user 用户名 identified by 新密码;需要使用sys、system用户登录。

4. 显示当前用户：show user;

5. 退出：exit

10. Oracle用户管理：

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

3. 删除用户：drop user [cascade];如果待删除的用户已经创建了表，就需要带上cascade参数。

4. 指定用户的权限：grant 权限名 to user；connect是权限。Oracle大约有140多种系统权限，25种对象权限。

5. 回收权限使用命令：revoke；revoke select on scott.emp from user;

6. 一个用户查询另一个用户的表（使用对象权限）：grant select on scott.emp to user;允许user查询scott账号下emp表。user查询如下：select * from scott.emp;

11. 权限：

|-系统权限：用户对数据库的相关权限。

|-对象权限：用户对其他用户的数据对象操作的权限。对象权限分为：select、insert、update、delete、all等。

13. 角色：权限批量授权给角色。方便权限的授予。角色分为：自定义角色和预定义角色。重要的角色：DBA(系统管理)，resource（允许在表空间建表）。grant resource to user；允许user创建表。

14. profile是口令限制，资源限制的命令集合。

1. 账户锁定：create profile lock_account limit failed_login_attempts 3 password_lock_time 2;alter user xiaoming profile lock_account每个用户只能尝试登陆3此，锁定时间为2天。创建的profile名字为lock_account。

2. 给账户解锁：alter user xiaoming account unlock;

3. 终止口令，为了让用户定期修改密码。Create profile myprofile limit password_life_time 10 password_grace_time 2;alter user xiaoming profile myprofile;每隔10天修改自家的登陆密码，宽限期为2天。

4. 禁止新旧密码相同：create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10; alter user xiaoming profile password_history;

5. 删除profile：drop profile password_history;

15. Oracle表的管理。

1. 表名、列名的命名规则：字母开头，不超过30字符，不能使用oracle保留字。

2. 支持的数据类型：

a) 字符型：

i. char（定长）：char(10) ‘Conan’。5个存放字符，5个空着，但是查询效率高。最大2000字符。

ii. varchar2（变长）：自动增加、减少，节省空间。最大4000字符。

iii. clob（character large object）：字符型大对象，最大4G。

b) 数字型：

number(4):4位。number(5,2)：共5位，2位小数。

c) 日期类型：

i. Date：包括年月日时分秒

ii. Timestamp：时间戳

d) 二进制数据：

blob。可存放图片、声音

3. 建表：

create table student(xh number(4), xm varchar2(20),sex char(2),birthday date, sal number(7,2));

4. 修改表：

a).添加字段

alter table student add (classid number(2));

b).修改字段

alter table student modify (classid number(2));

c).删除字段

alter table student drop column sal;

d).修改表名

rename tablename to stu；

e).删除表名

drop table tablename；

5. 查看表：desc tablename；

6. 添加数据：Oracle默认日期格式’DD-MON-YY’

//01-12月-13中“月”必须写上！

insert into student values(‘a001’,’conan’,’male’,’01-12月-13’);

//插入部分字段

insert into student (xh) values(‘a001’);

//添加空值

Insert into student(xh,xm,sex,birthday) values (‘a004’,’MARTIN’,’男’,null);

通过：alter session set nls_date_format = ‘yyyy-mm-dd’;修改日期默认格式。这样就不用写“月”了。

7. 修改字段数据：

//修改一个字段

update student set sex=’female’ where xh=”a004”;

//修改多个字段

update student set sex=’female’，birthday=’xx’  where xh=”a004”;

8. 删除数据

//删除所有记录，表结构还在，因为写入日志，可以恢复数据

delete from student;

//删除表的结构和数据

drop table student;

//删除一条记录

delete from student where xh=’a001’;

//删除表中所有记录，表结构还在，不写日志，无法找回

truncate table student;

9. 设置保存点：savepoint aa;

10. 恢复到保存点：rollback to aa;

16. 查询语句select。

EMP表结构：EMPNO/ENAME/JOB/MGR（上级的编号）/HIREDATE/SAL/COMN（奖金）/DEPTN（所在部门）。

DEPT表：DEPTNO/DNAME/LOC

1. 查看表结构:desc dept;

2. 查询所有列：select * from emp;

3. 查询指定列：select ename, sal, job from emp;

4. 取消重复行：select distinct deptno, job from emp;

问题：

1. 查询SMITH的薪水、工作，所在部门：(oracle大小写不区分，但是单引号中的区分大小写)

Select deptno,job,sal from emp where ename=’SMITH’;

2. 使用算数表达式显示每个员工的年工资

Select ename, sal*12+nvl(comm.,0)*12 from emp；nvl表示如果comm是null，则替代为0；不为空，就用comm本身。否则，oracle规定，同含有null的进行运算，结果整体为null。

Select ename, sal*12 “年工资” from emp;(年工资是sal*12的别名)

3. 如何显示工资高于3000的员工

Select ename,sal from emp where sal>3000 and sal<10000;

4. like操作符。%表示0到多个字符；_:表示任意单个字符。显示首字母为S的员工姓名和工资。

Select ename,sal from emp where ename like ‘S%’;

5. where中使用in。显示empno为123,345,800的雇员情况

select * form emp where ename in (123,345,566);

6. is null.显示没有上级的雇员

select * from emp where MGR is null.

7. 逻辑运算符。and or

Select * from emp where (sal > 50 or job =’MANAGER’)注意括号

8. order by.按照工资从低到高的顺序显示雇员信息。

Select * from emp order by sal [asc];//从低到高

Select * from emp order by sal desc;//从高到底

按照部门号升序而雇员工资降序排列：

Select * from emp order by deptno, sal desc;

对年薪排序：

Select ename,sal*12 “年薪” from emp order by “年薪”;

9. 分页查询：select t.* from(select g.*,rownum rn from 表名 g) t where t.rn>=开始索引 and t.rn<=结束索引

17. Oracle复杂查询

1. 数据分组：max,min,avg,sum,cout;

显示所有员工中最高工资和最低工资：

select max(sal),min(sal) from emp;

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

2. group by（查询结果分组统计）和having子句（限制分组显示结果）

显示每个部门的平均工资和最高工资：

Select avg(sal),max(sal),deptno from emp group by deptno;

显示每个部门的每种岗位的平均工资和最低工资

Select avg(sal),min(sal),deptno,job from emp group by deptno,job;

显示平均工资低于2000的部门号和他的平均工资：

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

总结：

1. 分组函数只能出现在选择列表、having、order by子句中；

2. 如果在select语句中同时包含group by，having，order by，那么他的顺序是group by，having，order by。

3. 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现子group by 子句中，否则就会出错。

18. 多表查询：

1.显示雇员名，雇员工资及所在部门的名字：

Select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;不加where条件的话，是用第一个表中的每一条跟第二个表的全部构成，一共为第一表长度乘以第二个表长度。

2.自连接：在同一张表的连接查询。显示某个员工的上级领导的姓名。

select worker.ename,boss.ename from emp worker,emp boss where boss.empno=worker.mgr;

3.子查询：嵌入在其他sql语句中的select查询。

单行子查询：返回一行数据的子查询语句。显示与SMITH同一部门的所有员工。

Select * form emp where deptno=(select deptno from emp where ename=’SMITH’);从右往左执行。

多行子查询：返回多行数据的子查询。查询和部门10的工作相同的雇员的名字、岗位、工资和部门号。

Select * from emp where job in (select distinct job from emp where deptno=10);

多行查询中使用all操作符。显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。

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

在多行查询中使用any操作符。显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部门号。
select ename,sal,dept from emp where sal>any(select sal from emp where deptno=30);

多列子查询。查询与smith的部门和岗位完全相同的所有雇员。

Select deptno,job from emp where ename=’smith’;

Select * from emp where (deptno,job)=(select deptno,job from emp where ename =’smith’);

显示高于自己部门平均工资的员工的信息：

(1.查询各部门的平均工资)Select deptno,avg(sal) mysal from emp group by deptno;

(2.将上面的查询看做是一张子表)select a2.ename,a2.sal,a2.deptno from emp a2, (Select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;

在from中使用子查询的时候，该子查询会被当做成一个视图对待，成为内前视图。在from中使用子查询时必须制定别名。

Oracle分页查询一共有三种：

A.rownum分页：select a1.*, rownum from (select * from emp) a1;

select a1.*, rownum from (select * from emp) a1 where rownum <=10;不能再rownum>=6，oracle规定rn只能用一次。

Select * from (select a1.*, rownum from (select * from emp) a1 where rownum <=10) where rownum>=6;修改查询只需要在里层改动。

B.ROWID:效率很高

5.用查询结果创建新表：

Create table mytable(id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;

6.合并查询。使用集合操作符union，union，all，intersect，minus。

A.union:取集合的并集；将多个查询的结果合并到一个结果中，没有重复的行

Select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job=’manager’;

B.union all:将多个查询的结果合并到一个结果中 不取消重复行，不排序

C.intersect:取两个集合的交集。  返回两个查询结果中共有的行

D.minus:取两个集合的差集。  返回从第一个查询的结果中减去第二个查询结果相同的行后剩余的行

1.使用to_date函数：to_date(‘1988-04-16’,’yyyy-mm-dd’);21. Oracle中操作数据

2.使用子查询插入数据：导入emp表中10号部门到新表kkk中。

Insert into kkk (MyID,MyName) select empo,ename,detpno from emp where deptno=10;

3.使用子查询更新数据：更新员工scott的岗位、工资和smith员工一样

Update emp set (job,sal)=(select job,sal from emp where ename=’SMITH’) where ename=’SCOTT’;

22. Oracle中的事务：

事务用于保证数据的一致性，它由一组相关的dml（数据操纵语言）语句组成，该组的dml语句要么全部成功，要么全部失败。Dml语言是说操纵语言，包括增删改操作。当执行事务操作的时候，oracle会在被作用的表进行加锁，防止其他用户对该表进行操作。

使用commit提交事务。当执行commit后，会确认事务的变化，结束事务、删除保存点、释放锁，当使用commit语句结束事务后，其他绘画可以查看事务变化后的新数据。

保存点（savepoint）：是事务中的一点，用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点。当执行回退事务rallback是，通过指定保存点可以回退到指定的点。这里需要注意的是这些保存点要想能够回退，是以没有commit为前提的。使用命令commit提交，使用exit退出的时候也会自动commit。

事务的几个重要操作：

1.提交事务：commit

2. 取消全部事务：rollback;

设置只读事务：set transaction read only只读事务：只允许执行查询的操作，不允许执行其他dml操作的事务。只读事务可以确保用户只能够取得某时间点的数据。 

23. SQL函数的使用

1.字符函数：

lower(char):将字符串转化为小写

upper(char):将字符串转换为大写

length(char):返回字符串长度

substr(char,m,n):取字符串的字串

replace(char1,serarch_string,replace_string):替换函数

instr(char1,char2,[,n[,m[]):取字串位置函数

eg：将所有员工的名字按照小写显示：select lower(name) from emp;

2.数学函数：

cos,cosh,exp,ln,log,sin,sinh,sqrt,tan…

round(n,[m]):执行四舍五入，保留m位小数

trunk(n,[m]);截取数字，截取n小数点后的m位

mod(m,n):去模

floor(n):返回小于或者等于n的最大整数

ceil(n):返回大于或是等于n的最小整数

abs(n):绝对值

3.日期函数：默认格式为dd-mon-yy，例如12-7月-15,显示为2015-7-12。

Sysdate：当前系统时间。Select sysdate from emp;

Add_months(d,n):加一个月份。Eg：查找入职8个月入职的员工。

Select from emp where sysdate>add_months(hiredate,8);

Last_day(d):返回指定日期的最后一天

4.转换函数：用于将数据类型的转换，oracle提供自动转换。

to_char():转换字符函数

eg1：日期显示时分秒：select ename ,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’);

eg:显示日民币：select ename ,to_char(hiredate,’yyyy-mm-dd hh24:mi:ss’) to_char(sal,’L9999.99’);L表示当地语言，9999.99代表显示数字格式。

5.系统函数

Sys_context：查询系统参数

24. 数据库管理：

Sys：董事长

System：总经理

主要区别：

1. 存储的数据的重要性不同

Sys：所有oracle的数据字典的基表和视图都是放在sys用户中的，sys拥有dba，sysdba，sysoperar的角色，是用户权限最高的用户。

System：存放系统次一级的内部数据，拥有dba和sysdbajuese或者系统权限。

2. 权限的不同

Sys必须以as sysdba或者as stsoper形式登录，不能以normal形式登录

Systm如果正常登录，其实就是一个普通的dba用户，如果以as sysdba登录，结果实际上就是作为sys用户登录。

Sysoper较之sysdba不不能改变字符集、创建、删除数据库，只能万群恢复数据库等。

sysdba>sysoper>dba（大致如此）。其中dba不能启动关闭数据库。

显示初始化参数：show parameter

27. 约束

数据的完整性用于确保数据库的数据遵从一定的商业和逻辑规则。在

中，数据的完整性可以使用约束、触发器，应用程序（过程、函数）三种方式完成。

约束用于确保数据库数据满足特定的商业规则。Oracle中约束包括：not null、unique，primary key，foreign key和check五中。

Not null：非空，插入数据时必须提供数据

Unique：该值不能重复，但是可以为null

Primary key：唯一的标示表行的数据，该列不能重复且不能为null，一张表只能有一个主键，但可以有多个unique。

Foreign key：用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或者unique约束当定义外键约束后，要求外键列数据必须在主表的主键列或者是null。

Check：强制行数据满足条件。

Create table goods(goodsID char(8) primary key,--主键

goodsName varchar2(30),

nitprice number(10,2) check (unitprice>0),

category varchar2(8),

provider varchar2(30));

Create table customer(customerID char(8) privary key,

name vchar2(30) not null,

address varchar2(50),

email varchar2(50) unique,

sex char(2) default ‘男’ check(sex in ‘男’,’女’)

cardId char(18));

Create table purchase (customerId char(8) references customer(customerId),

goodsId char(8) references goods(goodId),

nums number(5) check (nums between 1 and 30));

删除约束：alter table 表名 drop constraint 约束名称。在删除主键时，有必要加入cascade关键字，以防主键有关联关系。

SQL语言分类：

1.DDL（数据定义语言）：  create（创建）    alter（修改）  drop（删除）------------主要是对表操作。

 

2.DML（数据操作语言）：  insert(插入)    delete(删除)  update(更新)  select(查询) 、select......for update(查询更新)----表中数据进行操作

 

3.DQL数据查询语言：基本语句、Order by 子句、Group by 分组语句

 

4.TCL（事务控制语言）：Commit(提交) 、Savepoint(保存) 、rollback(回滚)、

 

5.DCL（数据控制语言）：Grant(授权)、revoke(撤销)命令。