oracle表空间创建 表创建 清空数据 表操作 表查询 表数据导入导出

一. 表空间创建

-- 查询所有表
select * from tabs;
-- 服务器编码
select USERENV('LANGUAGE') "Language" FROM DUAL;

-- 创建表空间 创建一个初始化大小为100m 每次扩容10m
create tablespace waterboss
datafile 'c:\waterboss.dbf'
size 100m
AUTOEXTEND on
next 10m;

-- 查询表空间
select * from dba_tablespaces;

-- 创建用户
create user wateruser
identified by charles			-- 密码
DEFAULT tablespace waterboss;  -- 归属表空间

-- 给wateruser授权 dba权限
grant dba to wateruser;

--查看用户
select * from dba_users
	
-- 解锁用户 以system用户或dba身份登录,执行下面的语句对scott用户解锁
alter user scott account unlock;
		
-- 设置密码
alter user scott identified by tiger;

二. 表创建

1.创建表
	a)语法:
		CREATE TABLE 表名称( 
		  字段名 类型(长度) primary key, 
		  字段名 类型(长度), 
		  ....... 
		  );


	b)数据类型:
		1.字符类型
			（1）CHAR      :固定长度的字符类型，最多存储 2000 个字节 
			（2）VARCHAR2  :可变长度的字符类型，最多存储 4000 个字节   (和varchar是同义词)
			（3）LONG      :大文本类型。最大可以存储 2 个 G

		2.数值型	
			(1)NUMBER : 数值类型   不写默认是18,最大也是18;
			    例如：NUMBER(5)   最大可以存的数为 99999     
					  NUMBER(5,2)  最大可以存的数为 999.99 
		3.日期型 
			（1）DATE：日期时间型，精确到秒   
			（2）TIMESTAMP：精确到秒的小数点后 9 位

		4.二进制型（大数据类型） 
			（1）CLOB : 存储字符,最大可以存 4 个 G 
			（2）BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G 
		 
		 eg:create table t_owners 
			  ( id number primary key, 
			  name varchar2(30), 
			  addressid number,
			  housenumber varchar2(30),
			  watermeter varchar2(30), 
			  adddate date, 
			  ownertypeid number 
			  );
2.修改表
	a) 增加字段语法: 
		ALTER TABLE 表名称 ADD(列名 1 类型 [DEFAULT 默认值]，列名 2 类型 [DEFAULT 默认值]...) 
	
	b) 修改字段语法： 
		ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名 1 类型 [DEFAULT 默认值]...)		
	
	c) 修改字段名语法： 
		ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名 

	d) 删除字段名 
		-删除 一个 字段
			ALTER TABLE 表名称 DROP COLUMN 列名 
			
		-删除 多个 字段
			ALTER TABLE 表名称 DROP (列名 1,列名 2...) 

3.删除表
	语法： 
		DROP TABLE 表名称 	

4.清空表
	delete table 表名称
	truncate table 表名称 
	
	delete:会产生rollback,如果删除大数据量的表速度会很慢,同时会占用很多的rollback segments; 
	truncate:是DDL操作， 不产生rollback,速度快。

三. 表数据操作

1.插入数据 
	语法： 
		INSERT  INTO 表名[(列名1，列名 2，...)]VALUES(值 1，值 2，...) 
		**执行 INSERT 后一定要再执行 commit 提交事务 
		
		insert into T_OWNERS VALUES (1,' 张三丰',1,'2-2','5678',sysdate,1); 
			语句中的 sysdate 是系统变量用于获取当前日期
2.修改数据 
	语法： 
		UPDATE 表名 SET 列名1=值 1，列名 2=值 2，....WHERE 修改条件； 
		执行 UPDATE 后一定要再执行 commit 提交事务 
	 
	eg:需求：将 ID 为 1 的业主的登记日期更改为三天前的日期 
		update T_OWNERS set adddate=adddate-3 where id=1; commit; 

3.删除数据 
	语法 1： 
		DELETE FROM 表名 WHERE 删除条件; 
		执行 DELETE 后一定要再执行 commit 提交事务 

	语法 2： 
		TRUNCATE  TABLE  表名称 
		
		比较 truncat 与 delete 实现数据删除？ 
			1. delete删除的数据可以 rollback 
			2. delete删除可能产生碎片，并且不释放空间 
			3. truncate是先摧毁表结构，再重构表结构 
			
		delete效率比truncate效率低
			delete并不是真正的删除,而是放在了表空间中的回滚段中,commit之后就不能再回滚了

四. 数据的导入导出

a)整库导出命令 
	exp system/itcast full=y 添加参数 full=y 就是整库导出
	执行命令后会在当前目录下生成一个叫 EXPDAT.DMP，此文件为备份文件。 
	
	如果想指定备份文件的名称，则添加 file 参数即可，命令如下 
	exp system/itcast file=文件名 full=y 
 
b)整库导入命令 
	imp system/itcast full=y 此命令如果不指定 file参数，则默认用备份文件 EXPDAT.DMP 进行导入 
	如果指定 file 参数，则按照 file 指定的备份文件进行恢复 
	 
	imp system/itcast full=y file=water.dmp 

c)按用户导出与导入 
	(1)按用户导出 
		exp system/itcast owner=wateruser file=wateruser.dmp 
	 
	(2)按用户导入 
		imp system/itcast  file=wateruser.dmp fromuser=wateruser 

d)按表导出与导入 
	(1)按表导出 
		exp wateruser/itcast file=a.dmp tables=t_account,a_area 用 tables 参数指定需要导出的表，如果有多个表用逗号分割即可 
	(2)按表导入 
		imp wateruser/itcast file=a.dmp tables=t_account,a_area 

五. 单表查询

(一)简单条件查询
	a)精确查询
		select * from T_OWNERS where watermeter='30408'
	b)模糊查询
		查询业主名称包含“刘”的业主记录
		select * from t_owners where name like '%刘%'
	c)and运算符
		查询业主名称包含“刘”的并且门牌号包含5的业主记录
		select * from t_owners where name like '%刘%' and housenumber like '%5%' 
	d)or运算符
		查询业主名称包含“刘”的或者门牌号包含5的业主记录
		select * from t_owners where name like '%刘%' or housenumber like '%5%'
	e) and 与or运算符混合使用
		查询业主名称包含“刘”的或者门牌号包含5的业主记录，并且地址编号为3的记录。
		select * from t_owners where (name like '%刘%' or housenumber like '%5%') and addressid=3 
		*因为and的优先级比or大，所以我们需要用 ( ) 来改变优先级。
	f) 范围查询
		查询台账记录中用水字数大于等于10000，并且小于等于20000的记录
			(1)我们可以用>= 和<=来实现，语句
			select * from T_ACCOUNT where usenum>=10000 and usenum<=20000
			
			(2)我们也可以用between .. and ..来实现
			select * from T_ACCOUNT where usenum between 10000 and 20000

	g) 空值查询
		(1)查询T_PRICETABLE表中MAXNUM 为空的记录
		select * from T_PRICETABLE t where maxnum is null

		(2)查询T_PRICETABLE表中MAXNUM 不为空的记录
		select * from T_PRICETABLE t where maxnum is not null

(二)去掉重复记录
	查询业主表中的地址ID,不重复显示
	
	select distinct addressid from T_OWNERS
	
	如果显示两条以上记录时,横向的一行为一组,与其他行对比是否重复	
(三)排序查询
	a)升序排序(默认升序)asc
		对T_ACCOUNT表按使用量进行升序排序
		select * from T_ACCOUNT order by usenum
	b)降序排序
		对T_ACCOUNT表按使用量进行降序排序
		select * from T_ACCOUNT order by usenum desc 

(四)基于伪列的查询
			在Oracle 的表的使用过程中，实际表中还有一些附加的列，称为伪列。伪列就像表中的列一样，
		但是在表中并不存储.伪列只能查询，不能进行增删改操作。
	
	(a)ROWID
			表中的每一行在数据文件中都有一个物理地址，ROWID 伪列返回的就是该行的物理地址。
		使用ROWID 可以快速的定位表中的某一行。ROWID 值可以唯一的标识表中的一行。
		由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。
		
		查询语句：
			select rowID,t.* from T_AREA t;
			
		我们可以通过指定ROWID来查询记录 
			select rowID,t.* from T_AREA t where ROWID='AAAM1uAAGAAAAD8AAC'; 	
				
	(b)ROWNUM
			在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。
		通过ROWNUM伪列可以限制查询结果集中返回的行数。
		
		查询语句：
		select rownum,t.* from T_OWNERTYPE t 
		
(五)聚合统计:
	ORACLE的聚合统计是通过分组函数来实现的，与MYSQL一致。
	注意:
		    select areaid,sum(money) from t_account group by areaid
		1.显示的列必须在聚合函数中,或者在group by后面有分组,否则会出错(非聚合函数的列都要参与group by的分组)
		2.where 在返回结果前起作用,不能和聚合函数一起使用
		3.having在返回结果后过滤数据,和聚合函数搭配使用
	(1)聚合函数
		a)求和 sum
			统计2012年所有用户的用水量总和 
			select sum(usenum) from t_account where year='2012'

		b)求平均 avg
			统计2012年所有用水量（字数）的平均值
			select avg(usenum) from T_ACCOUNT where year='2012'
		c)求最大值 max
			统计2012年最高用水量（字数） 
			select max(usenum) from T_ACCOUNT where year='2012'
		d)求最小值min
			统计2012年最低用水量（字数） 
			select min(usenum) from T_ACCOUNT where year='2012'
		e)统计记录个数 count
			统计业主类型ID为1的业主数量 
			select count(*) from T_OWNERS t where ownertypeid=1
	
	(2)分组聚合Group by
		按区域分组统计水费合计数
		select areaid,sum(money) from t_account group by areaid
	(3) 分组后条件查询 having
		查询水费合计大于16900的区域及水费合计
		select areaid,sum(money) from t_account group by areaid having sum(money)>169000 

六. 连接查询

(一)多表内连接查询
	select * from 表1,表2  这样查询的结果是笛卡尔积,(列相加,行相乘)
	
	sql书写顺序:
		第一步:确定需要那些表
		第二步:确定链接条件
		第三步:确定字段
		第四步:整合前三步
		eg:需求：查询显示业主编号、业主名称、地址、所属区域、收费员、业主分类		  	  
			第一步:确定需要那些表
				from t_ownertype ot,t_owners os,t_address ad,t_area ar,t_operator op
			第二步:确定链接条件
				where ot.id=os.ownertypeid and ad.id=os.addressid and ar.id=ad.areaid and ad.operatorid=op.id;

			第三步:确定字段
				select os.id 业主编号,os.name 业主名称,ad.name 地址,ar.name 所属区域,op.name 收费员,ot.name 业主分类
			第四步:整合前三步	
				select os.id 业主编号,os.name 业主名称,ad.name 地址,ar.name 所属区域,op.name 收费员,ot.name 业主分类
				from t_ownertype ot,t_owners os,t_address ad,t_area ar,t_operator op
				where ot.id=os.ownertypeid and ad.id=os.addressid and ar.id=ad.areaid and ad.operatorid=op.id;

(二)左外连接查询
	注意:
		(+)补空(null)	
		oracle单引号表示字符串
		没加引号或者双引号代表列名
		一般先执行子查询,除非子查询中需要外面的字段或结果
	
	eg:查询业主的账务记录，显示业主编号、名称、年、月、金额。如果此业主没有账务记录也要列出姓名。
		1.按照SQL1999标准的语法，查询语句如下： 
			SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM T_OWNERS ow left join T_ACCOUNT ac on ow.id=ac.owneruuid
		2.按照ORACLE提供的语法，就很简单了： 
			SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROM T_OWNERS ow,T_ACCOUNT ac WHERE ow.id=ac.owneruuid(+)

		如果是左外连接，就在右表所在的条件一端填上（+）
(三)右外连接
	eg:查询业主的账务记录，显示业主编号、名称、年、月、金额。如果账务记录没有对应的业主信息，也要列出记录
		1.SQL1999标准的语句 
		select ow.id,ow.name,ac.year,ac.month,ac.money from T_OWNERS ow right join T_ACCOUNT ac on ow.id=ac.owneruuid
		2.ORACLE的语法 
		select ow.id,ow.name,ac.year,ac.month,ac.money from T_OWNERS ow , T_ACCOUNT ac where ow.id(+) =ac.owneruuid

七. 子查询

1.where子句中的子查询
	a)单行子查询
	   只返回一条记录
	   单行操作符
	
		操作符     含义
		=			Equal to
		> 			Greater than
		>=			Greater than or equal
		<			Less than
		<=			Less than or equal to
		<>			Not equal to
		
		eg:查询2012年1月用水量大于平均值的台账记录
			select * from T_ACCOUNT where year='2012' and month='01' and usenum> ( select avg(usenum)
			from T_ACCOUNT where year='2012' and month='01' )
	b)多行子查询
		返回了多条记录
		多行操作符
	
		操作符	含义
		IN 		等于列表中的任何一个
		ANY		和子查询返回的任意一个值比较
		ALL		和子查询返回的所有的值比较
		
		in 运算符(其中之一)
			eg1:查询地址编号为1 、3、4 的业主记录
				select * from T_OWNERS where addressid in ( 1,3,4 ) 

			eg2:查询地址含有“花园”的业主的信息
				select * from T_OWNERS where addressid in ( select id from t_address where name like '%花园%' )
			
			eg3:查询地址不含有“花园”的业主的信息
				select * from T_OWNERS where addressid not in ( select id from t_address where name like '%花园%' )
				
		any运算符(和其中的一个值进行比较,判断是否满足条件)
			select * from t_account where usenum > any(select usenum from t_account where money>10000);
			select * from t_account where usenum > any(10000,8130,20099);
		
2.from子句中的子查询
	from子句的子查询为多行子查询
	
	eg:查询显示业主编号，业主名称，业主类型名称，条件为业主类型为”居民”，
		select * from (select o.id 业主编号,o.name 业主名称,ot.name 业主类型 
		from T_OWNERS o,T_OWNERTYPE ot where o.ownertypeid=ot.id) where 业主类型='居民'	
3.select子句中的子查询
	select 子句的子查询必须为单行子查询*
	
	eg1:列出业主信息，包括ID，名称，所属地址。
		select id,name, (select name from t_address where id=addressid) addressname from t_owners
		
	eg2:列出业主信息，包括ID，名称，所属地址，所属区域。
		select id,name, ( select name from t_address where id=addressid ) addressname, 
		( select (select name from t_area where id=areaid )
		from t_address where id=addressid ) adrename from t_owners; 

八. 分页查询

1.简单的分页(需要用到伪列ROWNUM和嵌套查询)
	eg:分页查询台账表T_ACCOUNT，每页10条记录
		select rownum,t.* from T_ACCOUNT t where rownum<=10 

		rownum是在查询语句扫描每条记录时产生的，所以不能使用“大于”
	符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。
	eg:显示第11条到第20条的记录
		select * from (select rownum r,t.* from T_ACCOUNT t
		 where rownum<=20) where r>10
		
2.基于排序的分页
	需求：分页查询台账表T_ACCOUNT，每页10条记录，按使用字数降序排序。
	查询第2页数据，使用排序
		eg:select * from (select rownum r,t.* from T_ACCOUNT t
		 where rownum<=20 order by usenum desc) where r>10 		
		结果:rownum是乱序,因为ROWNUM 伪列的产生是在表记录扫描是产生的，
			而排序是后进行的，排序时R已经产生了，所以排序后R是乱的。

	让结果先排序，然后对排序后的结果再产生R，这样就不会乱了。
		select * from (select rownum r,t.* from (select * from T_ACCOUNT 
		order by usenum desc) t where rownum<=20 ) where r>10