Oracle知识点总结

Oracle知识点总结

常见数据库

小型数据库

       Access,foxbase

中型数据库

       MySQL

       SQLServer

       informix

大型数据库

       db2

       Oracle

       Sybase

 

 

常见命令

连接用户命令

conn system/123;  或者 conn system 回车 密码

 

断开连接

disc

创建新用户

create user yue(用户名)identified by 123(密码);

给用户授予权限

grant connect,resource(权限)  to yue(用户名)

resource:允许该用户可以自行建表。

connect:允许该用户连接。

修改密码

passw

管理员删除用户

drop user yue1(用户名)

强制删除用户

drop user yue1(用户名) cascade

超级管理员修改其他用户的密码

password yue(用户名)

执行sql文件的所有sql语句

start e:\test.sql(路径名)                              小知识: (SQL)Structured Query Language

进入sql编辑路径

edit e:\test.sql(路径名)  

设置行宽

set linesize 150(默认值是80);

show linesize 查看行宽

设置页宽

Set pagesize 5;      

Show pagesize 查看页行

设置列宽

col name(字段名) for a12(宽度)

spool 该命令可以将sql*plus上面的内容保存到对应的文件里面

第一步:

       spoole:\gg.sql 在制定位置建立存储文件

第二步:

       输入你需要得到结果的sql语句

第三步:

       spooloff;

yueàtangàlei

当前用户(yue)授权给另一个用户操作自己的表

grant select(权限) on student(表名) to tang(另一个用户名);

连接tang用户

在tang用户下查询yue的student表:

select * from yue.student;

如果需要所有权限，则将权限改为all

让一个用户(tang)有权限，并且让这个用户(tang)拥有可以赋予其他用户(lei)权限的权利

grant select(权限) on student(表名) to lei(用户名) with grant option;

注意:如果是系统权限，则使用:with admin option;

收回权限

revoke select(权限) on student from tang(用户名);

 

提问:如果yue将tang的权限收回,那么lei还有这个权限吗?

权限页被收回，失去该权限

卸载Oracle

1.关闭oracle所有的服务。可以在windows的服务管理器中关闭；（控制面板-管理工具-服务）

打开注册表：在运行中regedit打开路径：

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\

删除该路径下的所有以oracle开始的服务名称，这个键是标识Oracle在windows下注册的各种服务！

3.打开注册表，找到路径：

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

删除该oracle目录，该目录下注册着Oracle数据库的软件安装信息。

4.删除注册的oracle事件日志，打开注册表

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application

删除注册表的以oracle开头的所有项目。

5.删除环境变量path中关于oracle的内容。

鼠标右键右单击“我的电脑属性高级环境变量PATH 变量。

删除Oracle在该值中的内容。注意:path中记录着一堆操作系统的目录，在windows中各个目录之间使用分号（; ）隔开的，删除时注意。

建议：删除PATH环境变量中关于Oracle的值时，将该值全部拷贝到文本编辑器中，找到对应的Oracle的值，删除后，再拷贝修改的串，粘贴到PATH环境变量中，这样相对而言比较安全。

6.重新启动操作系统。

以上1~5个步骤操作完毕后，重新启动操作系统。

管理员登录五次失败则锁定用户

profile:该配置文件管理用户的命令

failed_login_attempts:登录的次数

password_lock_time:锁定时间

第一步:创建配置文件

create profile lock_account(自定义用户) limit failed_login_attempts5(次数)

password_lock_time 2(天数)。

第二步:

alter user lei(用户名) profile lock_account(自定义);

 

给用户解锁解锁

alter user yue(用户名) account unlock;

删除配置文件

drop profile lock_account(配置文件名);

强制删除

drop profile lock_account(配置文件名) cascade;

解决关于密码输入错误也能进system,sys用户的问题

进入Oracle的安装文件àoracle\product\11.2.0\server\networkà在ADMIN文件夹下找到sqlnet.ora文件à将 SQLNET.AUTHENTICATION_SERVICES = (NTS) 括号中的NTS改为NONE就行了

 

 

 

表

表名的定义

1.      必须以字母开头

2.      长度一般不能超过30个字符

3.      不能使用Oracle保留字

4.      A-Z,a-z,0-9,$,_,#

数据类型

字符型

char 定长，最大2000个字节

varchar2(20) 变长(可变长度)最大4000字节

clob 字符型大对象,最大4G

数字型:

number 范围-10^38~~10^38(整数)

number(5,2) 表示一个五位数的小数，并且有两位小数  范围-999.99~~999.99

number(5) 范围:-99999~~99999

日期类型

date 包含年月日和时分秒

timestamp 9i对date数据类型扩展

图片类型

blod 二进制数据，用来存放照片，声音 4G

 

 

 

 

DDL(数据定义语言)语句:

增加字段

alter table student(表名) add(字段名 字段类型)

 

修改字段

alter table student(表名) modify (字段名 字段类型)

 

删除

alter table student(表名) drop column 字段名;

或

alter table student(表名) drop (字段名);

 

 

修改日期格式

alter session set nls_date_format=’yyyy-mm-dd’;

 

DML语句

查询字段为空的学生的信息

select * from student where stu_birthday(字段) is null;

 

修改字段的信息

update student(表名) setstu_idcard=430502199911128888 where stu_id=22;

修改多个字段的信息

update student set 字段1=’值1’,字段2=’值2’,字段3=’值3’ wherestu_name=’字段值’;

删除

delete from student;              全部删除数据

drop table student;         将表删除

truncate table student;           删除表中所有数据，表结构还在，但是他找不到日志，无法找回删除的记录，速度极快

 

建立保存点

savepoint aa(名字)

滚回保存点

rollback to aa(名字)

rollback不开分开

查询表中的行数

select count(*) from student;

 

修改字段名

Alter table 表名 rename column 原字段名 to 新字段名

 

修改表名

alter table 表名 rename to 新表名

 

 

一些基本操作

修改表格数据(会锁表，不建议使用)

select * from dept for update;

注释

单行注释

-- 注释内容

多行注释

/** 注释内容 **/

查询入职日期在2010/6/6以后的员工信息

select * from emp where emp_hiredate >  to_date('2016/6/6','yyyy-mm-dd');

查询员工工资在2000-2500之间的

select * from emp where emp_salary > 2000and emp_salary <2500;

select * from emp where emp_salary between2000 and 2500;

like模糊查询关键字

--  %可以表示0到多个任意字符

select * from emp where emp_name like ‘s%’;

 

--  _可以表示单个字符

-- 第三个字符为’a’的

select * from emp where emp_name like ‘__a%’;

 

查询奖金等于300和500

select * from emp where emp_bonus = 300 or emp_bonus =500;

select * from emp where emp_bonus in (300,500);

工资大于500或者岗位为’manager’的员工，他们的名字首字母为’j’;

select * from emp where (emp_salary >500or emp_position = ‘manager’)

and emp_namelike ‘j%’;

组合函数

升序

order by 字段名 asc;

降序

order by 字段名 desc;

在dept_id升序的情况下emp_salary降序

select * from emp order bydept_id,emp_salary desc;

当指定的数据为空时，可以指定一个默认输出的number类型数字

nvl()

select nvl(emp_parent_id,20) from emp whereemp_name =’king’;

查询并计算

select emp_name.emp_salary,emp_salary*12 “年薪” from emp;

select emp_name.emp_salary,emp_salary*12 “年薪” from emp order by emp_salary*12;

一些常见的函数

max:求最大值

min:求最小值

nvl:替换空值

count:统计行数

avg:求平均值

sum:求和

all: 查询所有值

any:查询任意值

查询员工的最高工资和最低工资

select max(emp_salary),min(emp_salary) fromemp;

查询最高工资的员工姓名和职位

Select emp_name,emp_position from emp where

emp_salary =(select max(emp_salary) fromemp)

查询大于平均工资员工的信息

select * from emp

where emp_salary >(selectavg(emp_salary) from emp);

 

分组查询

group by

按照部门id分组查询出部门中最高工资和最低工资的数据

selectmax(emp_salary),min(emp_salary),dept_id from emp

group by dept_id;

 

分dept_id,emp_position两个组查询

selectmax(emp_salary),min(emp_salary),dept_id

from emp group by dept_id,emp_position;

 

小技巧:

       点击右键àdescribe然后双击可以添加字段

       可以给表名设置一个别名,然后将字段给(.)点出来

 

Having条件

 

查询平均工资高于2000的部门编号和他的平均工资

先分组再通过having查询

select dept_id,avg(emp_salary) from empgroup by dept_id

having avg(emp_salary) > 2000;

 

多表查询

select * from emp;

select * from dept;

在多表查询时尽量给表名设置一个别名

产生笛卡尔积

错误:select * from emp e,dept d;

不产生笛卡尔积的技巧时在后面加个条件

select * from emp e,dept d where e.dept_id = d.id;

 

查询两个表

select e.emp_id "员工编号",e.emp_name"员工姓名",d.dept_id "部门编号",d.dept_name"部门姓名"

from emp e,dept d;

 

查询部门编号为10的员工信息 及 对应的部门信息

select e.emp_id "员工编号",e.emp_name "员工姓名",d.dept_id "部门编号",d.dept_name "部门姓名"

from emp e,dept d where e.dept_id =d.dept_id and e.dept_id = 10;

查询工资比部门30的所有员工的工资高的员工的姓名、工资和部门(大于最大的)

select e.emp_name,e.emp_salary,e.dept_idfrom emp e,dept d

where e.dept_id = d.dept_id ande.emp_salary > (select max(emp_salary) from empwhere dept_id = 30);

 

select e.emp_name,e.emp_salary,e.dept_idfrom emp e,dept d

where e.dept_id = d.dept_id ande.emp_salary > all(select emp_salary from empwhere dept_id = 30);

比部门为30的任意一个员工的工资都高的员工的姓名，工资和部门(大于最小的)

select e.emp_name,e.emp_salary,e.dept_idfrom emp e,dept d

where e.dept_id = d.dept_id and e.emp_salary> (select min(emp_salary) from emp where dept_id = 30);

 

select e.emp_name,e.emp_salary,e.dept_idfrom emp e,dept d

where e.dept_id = d.dept_id ande.emp_salary > any(select emp_salary from emp where dept_id = 30);

查询与smith的部门和岗位完全相同的员工

select * from emp

where (emp_position,dept_id) = (

select emp_position,dept_id from emp whereemp_name = 'smith');

分页查询数据4-10条(常见分页查询模板)

select * from (select a1.*,rownum rn(别名) from

(select * fromemp) a1

where rownum <= 10)

where rn(别名) > 4;

 

 

插入语句

对指定类型的数据插入,如:日期类型

Insert into emp values(8888,’laowang’,’clert’,’male’,’250.55’,

to_date(‘2015/11/11’,’yyyy-mm-dd’),1000,40,7839);

注意:要加上commit;才能插入成功

通过子查询插入数据

Insert into copy_demo(emp_id,emp_name,emp_position)

 

通过子查询插入数据

insert into copy_demo(emp_id,emp_name,emp_position)

select dept_id,dept_name,dept_location fromdept where dept_location= '长沙';

 

创建语句

复制表格(包括表的字段类型和数据一起复制)

可以保证原表不受影响进行测试

create table copy_demo (emp_id,emp_name,emp_position)

as select emp_id,emp_name ,emp_positionfrom emp;

修改语句

 

 

合并数据

 

合并

union ,union all ,intersect , minus

union合并数据(默认去除重复数据)

select emp_id,emp_name,emp_position fromemp

where emp_salary > 2000

union

select emp_id,emp_name,emp_position fromcopy_demo;

union 合并数据(不去除重复数据)

select emp_id,emp_name,emp_position fromemp

where emp_salary > 2000

union all

select emp_id,emp_name,emp_position fromcopy_demo;

intersect 取交集

minus 取差集

函数

max,min,avg,sum,count,

lower  --将字符串转换成小写

select lower(‘AABBCC’) from dual

upper  --将字符串转换成大写

select upper(emp_name) from emp;

initcap  --首字母大写，其余小写

select initcap(emp_name) from emp;

concat  --连接 通常使用 “||” 代替

select ‘姓名:’ || emp_name from dual;

length   --查询字符串长度

length(‘asdf’);

substr -- 截取字符串

select emp_name,substr(emp_name,1,3) fromemp;

replace  --替换

select ‘aabbcc’,replace(‘aabbcc’,’b’,’d’)from dual;

round(n,m) 

取四舍五入,如果去掉m，四舍五入到整数,不去则四舍五入到m位

Select (round(‘250.55’)+round(100))*13 fromdual;

trunc()

截取数字(整数)或者日期

mod()

取余数

floor()

返回大于或是等于较小的整数

ceil()

返回大于或是等于较大的整数

abs(n)

返回数字绝对值

acos(n)

反余弦值

asin(n)

反正弦值

Log(m,n)

以m为底n对数

Pow(m,n)

返回m的n次幂

Last_day(d)

返回指定日期所在月份的最后一天

 

找出每个月倒数第三天入职的员工

select emp_name,emp_hiredate from emp wherelast_day(emp_hiredate)- 2 = emp_hiredate;

Add_months(d,n)

查找已经入职8个月以上的员工

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

满了2年服务年限的员工信息

Select * from emp where sysdate >add_months(emp_hiredate,12*2);

显示每个员工加入公司的天数

select emp_name,trunc(sysdate -emp_hiredate) "入职天数" from emp;

 

 

小知识

查询当前时间(具体)

 

select to_char(sysdate,'yyyy-mm-ddhh24:mi:ss') from dual;

 

select to_char(sysdate,'yyyy-mm-ddhh24:mm:ss') from dual;(有bug)

星期几的前三个字母

dy

略

 

对货币的转换

select to_char(emp_salary,'L999,999,99')from emp;

 

select to_char(emp_salary,'L000,000,00')from emp;

 

9：显示的是数字,可以忽略值前面的0

0：显示的是数字,足的会补0

. :  小数点

, :  逗号

$: 美元符号

L:本地的货币币种

C:国际货币符号

G:指定位置显示分组符号

D：显示小数点符号

 

字符串转换为数字

to_number

 

select to_number(‘123423’) from dual;

 

系统函数:

sys_countext

terminal

language

db_name

nls_date_format

session_user

 

数据库建模

系统开发的周期

需求分析à系统设计(数据库设计，框架)à编码à测试à上线维护

 

数据开发:

是一个自顶向下的系统过程

将商业信息需求转化为一个可操作的数据库

由五个阶段组成:

1)     策略分析

2)     数据建模 (ER图)

3)     构建文档

4)     将ER图转化为表实例，列，对应的视图关系

5)     产品化，设计SQL语句，约束

6)     避免重复发明轮子

 

数据模型

ER

实体关系模型的概念

实体:生活中存在的一些事物

属性:描述实体的一些特征，品牌,型号

关系:两个实体质检的关联关系

 

数据建模

实体关系模型

根据业务规则和描述创建ER图

 

 

#：唯一(主键UID)

*：非空

O：可有可无，任意值

实线：mustbe 必须

虚线：maybe可有可无

竖杠(|)：代表要强制在(|)一方建立一个联合组件，将对方的UID拿过来做联合组件

实线加虚线：有订单必须要有客户，有客户不一定有订单

 

实体关联关系的类型

one-to-one：一对一关系如果要建立主外键关系，如果是实线或者是虚线都可以随便建

one-to-many：一对多关系外键建立在多的一方

many-to-many：多对多可以拆分两个一个或建立一张桥表

 

完整型约束

主键(primary key  PK)：一个表最多只能有一个主键，主键内容非空

如果多个列联合作为主键，则是联合唯一、单列非空

-代理主键：流程账号的数字、递增，没有任何逻辑含义，只是个数字，代号

-自然主键：(例如身份证号)具有一定的商业意义

外键(foreign key   FK)：由一列或多列组成取值为本表或其他表位置约束列或主键列，可以为null如果外键是联合组件的一部分那么外键的值不能为空

 

非空(not null)：一定要赋值

唯一(unique)：取值不能重复

check：自定义约束限定列的内容

 

数据库设计

实体属性映射成表和列

命名规则定义

其他需求设计

-设计索引

-定义视图

-设计物理存储空间

-重新定义完整性约束

 

数据库设计步骤

创建表实例图

-映射实体到表

-映射属性到列

-映射UID到主键

-映射关联关系到外键

 

数据字典

包含数据库的相关信息,包括:

数据库用户的名字

授权给用户的权限信息

数据库的对象信息

表的约束信息

 

 

根据权限分类

-user:用户创建的对象对应的数据字典表。例如：user_object,user_tables等

 

-user

select * from user_objects;

-all :所有用户能访问的对象(包括用户创建的对象)

select * from all_objects;

-dba:所有对象对应的数据字典表。例:dba_objects,dba_tables

select * from dba_objects;

-v$:描述系统同性能相关的数据字典表

-dictionary:一个特殊的数据字典表，用来描述数据字段表相关信息的

-table_privileges:数据表权限

使用人员范围

dba: v$version  DBA

开发人员

       user_*:只能看到本用户schema下面的表

       all_*:可以看到其他用户的表(条件是必须要授予权限)

数据字典常用表的定义

select * from dictionary;

dictionary:数据字典表相关信息的数据字典表;

user_objects:存放当前用户(schema)下所有的对象(表、视图、索引、约束等)

select * from user_objects;

user_constraints:存放的是当前用户下所有的约束信息;

user_cons_columns:查看数据字典中所有视图所对应的列

user_users:存放当前用的信息

select * from user_users;

all_users:存放数据库中所有用户的信息

user_indexes:索引

all_indexes:所有用户的索引信息

user_tables:表的信息

all_tables:所有用户表的信息

user_views:     视图

all_views:

user_synonyms:存放当前用户所有表的同义词

 

数据库常见对象

序列:sequence

select * from emp;

在Oracle数据库中是一种特殊的对象，能够产生现需的整数值，

可以为数据库只多个对象共同使用，主要作用于作为主键值

 

创建sequence

-可选择位置

cache  n：可以每次预产生多个存放到内存中

默认情况下    cache 20

-sysle 用于循环，到最大值后循环

 

创建序列

create sequence name(emp_t)(姓名)

 

[increment by n](自增长)

[start with n](从哪开始)

[{maxvalue n | nomaxvalue}]

[{minvalue n |nominvalue}]

[{cycle | cache n |nocache}]

 

查询序列

select * from user_sequences;

 

select emp_s.nextval from dual;

select emp_s.currval from dual;

修改序列

alter sequence emp_s;

删除序列

drop sequence emp_s;

 

创建视图

create [or replace][force | noforce]view name

as subquery(查询是的视图);

例如:

create view emp_v as

select emp_name,emp_salary from emp whereemp_salary < 2000;

 

[with check option]

[with read only]

 

or replace:如果有相同名字的视图对象则替换

force | noforce: 如果subquery中源表不存在，先创建视图对象，这时视图对象然后创建，但不能正常使用，默认值是noforce，即源表不存在，不能正常创建视图

with check option: 表示可以进行crud操作，但应该满足where子句

with read only: 只允许进行select操作

简单视图

数据来源于一张表，不包含函数及进行分组，可以进行DML操作

复杂视图

数据来源于多个表，包含函数及进行分组，不能进行DML操作

创建视图的优点

限制数据库访问

简化查询

数据的独立性

对同一数据有不同的表现

 

查询视图

select * from emp_dept_v(视图名);

 

修改视图

update name(视图名)

set sal = 900 where emp_name =’smith’;

作业

1)     对于简单视图进行crud操作会不会对源表数据有影响

2)     with check option ,然后在对视图进行crud操作。满足视图中子查询条件，和不满足子查询条件的数据

 

 

索引

作用：用于加快查询速度

创建索引:

create unique index onemp(emp_name,emp_salary);

确认索引:

select *

from user_ind_columns ic,user_indexes ix

where ix.INDEX_NAME =ic.INDEX_NAME

and ic.TABLE_NAME = 'emp';

 

索引结构:

1.      B*tree

2.       反向索引

3.       降序索引

4.       函数索引

5.       位图索引

 

删除索引

drop index name_salar_pk;

数据库结构

oraclet 提交结构

      逻辑结构:

              数据库(XE) à表空间 à段à区 à块

      物理存储结构:

              创建表空间

              create tablespace spacedemo datafile ‘d:\Oracle\spacedemo.dbf’size 10M;

              创建临时表空间

              create temporary tablespace spacedemo_tmp  datafile ‘d:\oracle\spacedemo_emp.dbf’

       size5M;

              表空间:是oracle数据库中最的逻辑结构，一个oracle数据在逻辑上有过个表空间组成，而一个表空间只属于一个数据库，oracle中有个system的表空间，这个是在按照数据库是自动创建的，主要是用于存储系统的数据字典，过程，函数，触发器等

       创建用于授权表空间

              create user spacedemo(用户名) identified by 123default tablespace spacedemo(表空间名字)

       授权

grant connect,resource to spacedemo

       查询表空间

              Select * from spacedemo（表空间名字）

修改数据文件spacedemo.dbf,扩展空间到1024M

              alter database datafile ‘d:\Oracle\spacedemo.dbf’ resize1024M;

       修改数据文件spacedemo.dbf，每次自动扩展50M,最大值2048M

alter database datafile ‘d:\Oracle\spacedemo.dbf’ autoextend on next 50M maxsize 2048M;

       删除表空间(包括数据和表):

              drop tablespacespacedemo including contents and datafile;

schema

       数据库对象、如：表、视图、序列、索引等

 

授权:

Grant select,resourceto yue(用户);

回收权限

revoke select ,inserton dept(表名) from yue(用户) [with grant option]

 

将用户的所有信息导出

exp yue/123 full=yfile=’d:\yue.dmp’;

导入用户信息

imp yue/123 full file=’d:\yue.dmp’;

 

Jdbc（javaDataBaseConnect）

1.       设置url= “jdbc:oracle:thin:@localhost:1521:XE”,密码(user)，用户名(password)

2.       在静态方法中注册驱动

Class.forName(“oracle.jdbc.driver.OracleDriver”);

3.       通过DriverManager获取连接对象

Connection conn = DriverManager.getConnection();

4.       创建sql语句

String sql = “select * from emp”;

4.  创建Statement/PreparedStatement对象