Oracle查询的时候条件要用单引号包裹，不能用双引号;Oracle的in子查询里面的值最多有1000个。。。。。。。。
连接orcl数据库
C:\Windows\system32>sqlplus sys/123456@orcl as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 2 14:32:34 2018 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user
USER 为 "SYS"
SQL> conn sbgl/sbgl@orcl
已连接。
SQL> show user
USER 为 "SBGL"
SQL> show parameter instance_name; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string orcl
登录用户切换用户:
C:\Windows\system32>sqlplus /@orcl as sysdba SQL*Plus: Release 11.2.0.1.0 Production on 星期五 2月 2 17:31:52 2018 Copyright (c) 1982, 2010, Oracle. All rights reserved. 连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show user
USER 为 "SYS"
SQL> connect sbgl/sbgl@orcl as sysdba
已连接。
SQL> show user
USER 为 "SYS"
SQL> connect sbgl/sbgl@orcl
已连接。
SQL> show user
USER 为 "SBGL"
SQL>
注意:以sysdba登录实际上登录的sys用户，因此登录用户的时候不要以sysdba登录。
/**1创建表空间**/
create tablespace sbgl datafile 'F:\Oracle\sbgl.dbf'size 200M
autoextend on next 50M force logging;
/**2.创建用户***/
create user sbgl identified by sbgl default tablespace sbgl;
/**3.授予sbgl操作权限**/
grant connect,resource,dba to sbgl;
/***4.创建导出目录(之后手动创建目录)**/
create or replace directory pump_dir as 'F:\expdp';
/********5.向新创的用户授权******/
grant read,write on directory pump_dir to sbgl;
/*******6.查看管理理员目录****/
select * from dba_directories;
导入导出:
*导入数据*
1、将SBGL20180129.EXPDP文件拷贝到d:\expdp；
2、在CMD窗口下执行以下命名
impdp sbgl/sbgl@orcl dumpfile=pump_dir:SBGL20180129.EXPDP table_exists_action=replace nologfile=y *导出数据：*
expdp sbgl/sbgl@orcl dumpfile=pump_dir:SBGL20180129.expdp schemas=sbgl nologfile=y
/****查看用户的角色***/
select * from USER_SYS_PRIVS where USERNAME='sbgl';
select * from dba_sys_privs;
/**查看当前登录用户信息(用户名、创建时间、表空间)*****/
select * from user_users;
/**查看所有用户信息****/
select * from dba_users; /*查看数据库里面所有用户,前提是有dba权限的帐号,如sys,system*/
select username from dba_users; //查看所有有dba权限用户名称
select * from all_users; /*查看能管理的所有用户*/
/****查看用户与表空间的关系***/
select default_tablespace, temporary_tablespace, d.username from dba_users d where username='SBGL';
/***查看所有的表空间**/
select tablespace_name from dba_tablespaces;
/*******删除表空间*********/
drop tablespace 表空间名称 including contents and datafiles;
/*****查看所有的表空间与其对应的数据文件的位置*********/
select * from dba_data_files;
/*查看所有用户**/
select * from dba_users;
/*查看一个表空间下面的所有表**/
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='SBGL';
/***查看一个用户有哪些表***/
select * from all_tables where owner='SBGL';
/***********查看当前用户有哪些表****/
select table_name from user_tables;
/**********查看当前用户有哪些试图**********/
select * from user_views;
select VIEW_NAME from user_views;
/*****查看dba_tablespaces表结构*****/
desc dba_tablespaces;
/***查看当前用户***/
show user;
select username from user_users;
/*****查看当前用户所有的权限***/
select * from session_privs;
/***查看当前连接数据库***/
show parameter instance_name;
/****查看建表语句******/
select dbms_metadata.get_ddl('TABLE','a') from dual 只需要将表名的小写改为大写即可；; //其中a为表名； 如果有PLSQL的话可以直接在可视化界面查看表的建表语句；
/*****oracle查看版本**/
select * from v$version;
/*查看数据库名*/
select name from v$database;
/*查看sid*/
select instance_name from v$instance;
/**根据约束名称查询对应的表名(有一列是tablename)**/
select * from dba_constraints where constraint_name='xxx外键名' and constraint_type = 'R';
/********删除用户************/
drop user username;
drop user username cascade;//级联删除用户的数据
/************添加一列 *******/
alter table A add( columnname varchar2(20));
/*********修改列名称 ******/
alter table A rename column coluName to newName;
/************修改列的类型：*******/
alter table A modify coluName varchar(200);
/****删除一列 ***********/
alter table A drop column coluName
/******字符串拼接 (||)***/
select 1||'23' from dual;
结果:
补充:oracle中多个用户共用一个表空间也是不同的表，表空间、表、用户的关系如下:
用户=商家
表=商品
表空间=仓库
1. 1个商家能有很多商品，1个商品只能属于一个商家
2. 1个商品可以放到仓库A，也可以放到仓库B，但不能同时放入A和B
3. 仓库不属于任何商家
4. 商家都有一个默认的仓库，如果不指定具体仓库，商品则放到默认的仓库中
oracle中用户的所有数据都是存放在表空间中的，很多个用户可以共用一个表空间，也可以指定一个用户只用某一个表空间。
表空间：创建表空间会在物理磁盘上建立一个数据文件，作为数据库对象（用户、表、存储过程等等）的物理存储空间；
用户：创建用户必须为其指定表空间，如果没有显性指定默认表空间，则指定为users表空间；创建用户后，可以在用户上，创建表、存储过程等等其他数据库对象；
表：是数据记录的集合；
创建过程： 表空间--->用户--->表;
所属关系： 表空间 包含 用户 包含 表；
分页查询相关:(对原始的查询进行两层封装)
/*****普通的查询(30数据)****/
select * from ENVIRONMENT_APPLY_AUDIT; /*******查询带行号的数据**/
select ROWNUM ru,AAA.* from ( select * from ENVIRONMENT_APPLY_AUDIT ) AAA; /*******查询第一页 每页5条数据***/
select * from (select AAA.*,ROWNUM ru from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<6);
select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<6) where ru>0; /**********查询第二页数据********************/
select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<11) where ru>5; /**********查询第三页数据********************/
select * from (select ROWNUM ru,AAA.* from (select * from ENVIRONMENT_APPLY_AUDIT ) AAA where ROWNUM<16) where ru>10;
/********************oracle表分区相关*************/
1.概念
表空间：是一个或多个数据文件的集合，所有的数据对象都存在指定的表空间中。
分区表：逻辑上分区表仍然是一个完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，可以提高查询效率，不至于每次都扫描整张表。
查询oracle版本是否支持分区
select * from v$option where parameter = 'Partitioning';
结果为true 的时候表示当前oracle版本支持分区。如果不支持需要更换oracle版本为支持分区的版本。XE不支持，ORCL支持。
对现有表table_8进行分区有两种方式,第一种是创建与现有表一样结构的分区表table_9，然后将原来table_8表的数据插入到table_9，然后删掉表table_8，将table_9命名为table_8；第二种是使用oracle自带的线上重定义功能进行分区，重定义会将基础表table_8和table_9进行交换，包括索引、触发器、外键等也会交换,重定义之后table_8变为分区表，table_9变为普通表，table_9会保留table_8的原有的数据。(两种方式都需要确保基础表table_8和中间表table_9的表结构一致，包括索引、触发器等)
2.练习
====================从无到有创建表分区(散列分区)========================
在列值上使用散列分区。当列的值没有指定条件时，建议使用散列分区。散列分区为同通过指定分区编号来均匀分布数据的一种分区类型。 1.创建两个表空间
create tablespace part1 datafile 'D:\oraclexe\tablespace\part1.dbf'size 1024M autoextend on next 500m force logging;
create tablespace part2 datafile 'D:\oraclexe\tablespace\part2.dbf'size 1024M autoextend on next 500m force logging; 2.创建用户
create user sa identified by 111222 default tablespace part1; 3.授权
grant connect,resource,dba to sa; 4. 查看是否支持分区
select * from v$option where parameter = 'Partitioning'; 5.创建一个测试表
create table table_1(ID NUMBER PRIMARY KEY, name varchar2(200)); 插入数据
insert into table_1 values(1, 'name1');
insert into table_1 values(2, 'name1'); 创建分区表(哈希分区)
CREATE TABLE table_2
(
ID NUMBER PRIMARY KEY,
name VARCHAR2(200)
)
partition BY hash
(
ID
)
(
partition part1 TABLESPACE part1,
partition part2 TABLESPACE part2
); 查看表是否已经分区：
SELECT PARTITIONED FROM USER_TABLES WHERE TABLE_NAME = 'TABLE_2'; 查看表table_2的分区以及分区所在的表空间：
select * from user_tab_partitions where table_name = 'TABLE_2' 测试将表1的数据插入表2：
insert into table_2 select * from table_1 查看分区1的数据：
select * from table_2 partition(part1)
结果：
2 name1
5 name1
6 name1
8 name1 查看分区2的数据：
select * from table_2 partition(part2)
1 name1
3 name1
4 name1
7 name1
9 name1
10 name1 ==============在线重定义将普通表变为分区表=========================
测试能否对表进行在线重定义：
EXEC SYS.dbms_redefinition.can_redef_table('SA', 'table_1'); // 参数是用户, 表名 创建中间分区表table_3:
CREATE TABLE table_3
(
ID NUMBER PRIMARY KEY,
name VARCHAR2(200)
)
partition BY hash
(
ID
)
(
partition part1 TABLESPACE part1,
partition part2 TABLESPACE part2
); 开始重建：
BEGIN
SYS.dbms_redefinition.start_redef_table('SA', 'TABLE_1', 'TABLE_3');
END; 把中间表的内容和数据源表进行同步
EXECUTE SYS.dbms_redefinition.sync_interim_table('sa', 'TABLE_1', 'TABLE_3') 开始重定义之后，完成重定义之前，需要在中间表上创建与源表对应的索引、外键、触发器等，而中间表与源表对应的主键，如果需要按照主键重定义，需要在开始redefine之前创建。 结束在线重定义
EXEC SYS.dbms_redefinition.finish_redef_table('sa', 'TABLE_1', 'TABLE_3') 如果执行在线重定义的过程中出错：
可以在start_redef_table之后，finish_redef_table之前执行：EXEC SYS.dbms_redefinition.abort_redef_table('sa', 'TABLE_1', 'TABLE_3') 测试表table_1是否已经分区
SELECT * FROM user_tab_partitions WHERE table_name = 'TABLE_1' ==================================分区的其他操作=================================================== 分区创建：
1.再开始建表的时候就指定分区
2.有数据的表table_1变为分区表的思路：
(1)建一个分区中间表table_2,表结构与表table_1一样，然后将表table_1的数据插入table_2,删除表table_1之后将table_2重命名为table_1
(2)在线重定义，也是用中间表进行分区 ===============================创建其他方式的分区================================
======范围分区====
1.基于ID进行划分
(1)小于10的一个分区，小于20的一个分区
CREATE TABLE table_4
(
ID NUMBER PRIMARY KEY,
name VARCHAR2(200)
)
partition BY range
(
ID
)
(
partition part41 VALUES less than (10) TABLESPACE part1,
partition part42 VALUES less than (20) TABLESPACE part2
);
插入20的时候会报错，未映射到分区：SQL 错误: ORA-14400: 插入的分区关键字未映射到任何分区。
解决办法：第二个分区建立的时候使用Maxvalue
CREATE TABLE table_4
(
ID NUMBER PRIMARY KEY,
name VARCHAR2(200)
)
partition BY range
(
ID
)
(
partition part41 VALUES less than (10) TABLESPACE part1,
partition part42 VALUES less than (maxvalue) TABLESPACE part2
); 按日期进行分区也是上面的语句:只不过值改为日期比较-> partition part42 VALUES less than (to_date('01-apr-1998','dd-mon-yyyy')) TABLESPACE part2 ======列表分区(适用于一个列的值只有几个值)====
1.创建列表分区
CREATE TABLE table_5
(
ID NUMBER PRIMARY KEY,
name VARCHAR2(200)
)
partition BY list
(
name
)
(
partition part51 VALUES
(
'zs'
)
TABLESPACE part1,
partition part52 VALUES
(
'lisi',
'ww',
'zl',
'tq'
)
TABLESPACE part2
);
如果插入的值不在上面分区允许的范围之内也是报错：SQL 错误: ORA-14400: 插入的分区关键字未映射到任何分区。 ======组合范围散列分区====
基于范围分区和散列分区，表首先按照某列进行范围分区，然后按照某列进行列表分区，分区之中的分区称为子分区
CREATE TABLE table_6
(
ID NUMBER PRIMARY KEY,
name VARCHAR2(200)
)
partition BY range
(
ID
)
subpartition BY list
(
name
)
(
partition part61 VALUES less than (10) TABLESPACE part1 ( subpartition part61sub1 VALUES
(
'zs'
)
TABLESPACE part1, subpartition part61sub2 VALUES
(
'lisi'
)
TABLESPACE part2 ) ,
partition part62 VALUES less than (maxvalue) TABLESPACE part2 ( subpartition part62sub1 VALUES
(
'zs'
)
TABLESPACE part1, subpartition part62sub2 VALUES
(
'lisi'
)
TABLESPACE part2 )
); ==================================分区查询===========================
1.查看当前用户可以访问的所有分区表的信息
select * from all_part_TABLES; 2.显示当前用户所有分区表的信息：
select * from user_part_tables; 3.查看所有的组合分区表的子分区信息：
select * from user_tab_subpartitions ============================带索引的表的重建(在线重定义分区)==================
1.创建带索引的表8
CREATE TABLE table_8
(
ID NUMBER PRIMARY KEY,
name VARCHAR2(200)
); 创建索引：
create index t8_name_index on table_8(name); 插入数据：
insert into table_8 select * from table_1; 2.查看是否支持在线重定义
EXEC SYS.dbms_redefinition.can_redef_table('SA', 'table_8'); 3.创建与表8结构一样的分区表9
CREATE TABLE table_9
(
ID NUMBER PRIMARY KEY,
name VARCHAR2(200)
)
partition BY hash
(
ID
)
(
partition part1 TABLESPACE part1,
partition part2 TABLESPACE part2
); 4.开始重定义
BEGIN
SYS.dbms_redefinition.start_redef_table('SA', 'TABLE_8', 'TABLE_9');
END; 5.进行数据同步
EXECUTE SYS.dbms_redefinition.sync_interim_table('sa', 'TABLE_8', 'TABLE_9') 6.结束在线重定义
EXEC SYS.dbms_redefinition.finish_redef_table('sa', 'TABLE_8', 'TABLE_9') 7.查看表8是否已经分区
SELECT * FROM user_tab_partitions WHERE table_name = 'TABLE_8' 8.查看表8中每个分区的数据：
select * from table_8 partition(part1)
select * from table_8 partition(part2)
/**************Oracle 事务相关********************/
DDL(数据定义语言) - Create、Alter、Drop 这些语句自动提交，无需用Commit提交。
DQL（数据查询语言）- Select查询语句不存在提交问题。
DML(数据操纵语言) - Insert、Update、Delete 这些语句需要Commit才能提交。
DTL(事务控制语言) - Commit、Rollback 事务提交与回滚语句。
DCL(数据控制语言) - Grant、Revoke 授予权限与回收权限语句。
执行完DML语句，若没有commit再执行DDL语句，也会自动commit未被commit的数据。
如果打开自动提交，DML操作后也不需要手动提交：SET AUTOCOMMIT ON;