oracle基础

SQL语句在执行时会默认全部转成大写

Oracle

（一）表空间

表空间就是数据真正存储在硬盘文件的映射，一个表空间下可以有多个用户

create tablespace aspace
datafile 'D:\a.dbf'
size 100m	//默人文件为一百兆，数据到了100m后每次扩容10兆
autoextend on
next 10m

（二）用户

oracle中的一个用户 类似MySQL的一个数据库，

create user aaa	//用户名
identified by 123123	//密码
default tablespace aspace	//指定表空间，一个表空间可对应多个用户

（三）授权

grant dba to aaa	//将aaa用户授权成dba

一、表的操作

常见数据类型：

1、字符型：

（1）CHAR：固定长度类型，不够用空格补齐，最多存储2000个字节

（2）VARCHAR2：可变长度类型，最多4000字节

（3）LONG：大文本类型，最多两个G（与java的long区分开，这里是字符型，类似mysql的text）

2、数值型

NUMBER：数值类型 （只有这一种，包括了整数和浮点数）

例如：NUMBER(5) ：最多可存到99999

NUMBER(5,2) ：最多可存到999.99

3、日期型

（1）DATE：日期，精确到秒

（2）TIMESTAMP：精确到小数点后9位

4、二进制类型

（1）CLOB：存储字符，最大可以存4个G

（2）BLOB：存储图像、声音、视频等二进制数据，最多可以存4个G

（一）创建表

create table xxx(
	ID NUMBER PRIMARY KEY,
	NAME VARCHAR2(30),
	ADDERSSID NUMBER,
	MYDATE DATE,
)

（二）修改表

1、添加字段

ALTER TABLE 表名称 ADD(列名1 类型[DEFAULT 默认值],列2...)

2、修改字段

ALTER TABLE 表名称 MODIFY (列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值])

3、修改字段名

ALTER TABLE 表名称 RENAME COLUMN 原名 TO 新名

4、删除字段名

ALTER TABLE 表名称 DROP COLUMN 列名1,列名2

（三）删除表

DROP TABLE XXX

二、数据操作

（一）DML

注：oracle不会自动提交事务，需要手动提交

1、插入数据

INSERT INTO TABLE_NAME(ID,NAME) VALUES(1,"张三");
COMMIT;

INSERT INTO TABLE_NAME(ID,NAME，DATETIME) VALUES(1,"张三",sysdate);	//sysdate获取当前日期

2、修改语句

UPDATE TABLE_NAME SET MYDATE = MYDATE+3 WHERE ID=1
//当前天数加三，默认加的是天数
COMMIT;

3、删除

DELETE FROM TABLE_NAME WHERE ID=1;
COMMIT;

TRUNCATE TABLE TABLE_NAME;		//清空数据。摧毁表，重建表

注：

① DELETE可回滚；DELETE并不释放空间，产生碎片

② TRUNCATE 摧毁后重建

三、导入导出

注：在cmd窗口下执行，如果不指定导入的文件名，会默认去找EXPDAT.DMP文件来执行

1、EXP 用户名/密码 full=y		
例：EXP system/123 full=y
//导出所有
//包括系统自带的表、索引、视图...，导出的默认文件名为EXPDAT.DMP，DMP文件是二进制文件

2、导入：IMP system/123 full=y

2、指定导入导出的文件名

1、导出：EXP system/123 full=y file=a.dmp
2、导入：IMP system/123 full=y file=a.dmp

3、指定用户导入导出

1、导入：EXP 带dba身份的用户名/密码  owner=要导出的表所在的用户名  file=a.dmp
例：EXP system/123 owner=qhj file=qhj.dmp

2、导出：IMP 带dba身份的用户名/密码 file=a.dmp fromuser=上面写的用户名
例：IMP system/123 fromuser=qhj file=a.dmp

4、指定表

//这里的用户名和密码可以是当前所要导出表的所属用户的用户名和密码
EXP 用户名/密码 file=a.dmp tables=t_table1,t_table2		//多个表之间用逗号隔开

IMP 用户名/密码 file=a.dmp tables=t_table1		//可只导入部分表

四、单表查询

（一）简单查询

1、单表查询

2、模糊查询

3、and、or

注：and的优先级高于or

需求：查询name包含5或者name包含6，并且age为100的记录

1、错误写法：select * from user where name like '%5%' or name like '%6%' and age=100;
因为这里的条件会分为两个部分：(name like '%5%') 、 (name like '%6%' and age=100)
将会查询出（name包含5），或者（name包含6且age=100）的数据

2、正确写法：select * from user where (name like '%5%' or name like '%6%') and age=100;
//优先级低所以应该加括号

4、范围查询：大于等于、小于等于、between…and…

5、空值查询：is null、is not null（not放中间）

（二）去重：DISTINCT

SELECT DISTINCT name,age FROM user：会对所有记录去重复
去重前：
    name age
    张三	 1
    李四	 2
    张三	 1
去重后：
	name age
    张三	 1
    李四	 2

（三）排序查询

ORDER BY

1、升序：默认排序查询就是升序，可以不用加ASC

2、降序：需要加DESC

（四）伪列

在oracle表的使用过程中，实际表中还存在一些附加列，称为伪列。伪列只能进行查询操作。

1、ROWID

表中每一行数据文件都有一个物理地址，ROWID伪列返回的就是改行的物理地址。

ROWID可以唯一标识一行。

ROWID也可以显示行是如何存储的。

select ROWID,t.* form table1 t		//设置别名进行查询

2、ROWNUM

每一行都自带一个序号，ROWNUM只能进行小于、小于等于进行查询，不能使用其他符号

select ROWNUM,t.* from table1 t

select * from user ROWNUM<10
select * from user ROWNUM>10		//无结果

（五）聚合函数

五大聚合函数：求和、平均值、最大最小、总数

1、sum()

2、avg()

3、max()

4、min()

5、count()

（六）分组查询

1、group by

select name,count(age) where user group by name
注：*查询结果只能是group by后的字段或者是聚合函数。

2、having

having是对分组后的结果进行处理（后执行），而where是对分组前的数据进行处理（先执行）。

需求：查询年龄小于45的员工，并根据工作地址分组，员工数量大于3的工作地址
select workaddress,count(*) from emp where age <45 group by workaddress having count(*) >=3

五、连接查询

（一）内连接

只显示符合条件的数据，不符合条件的不显示

① 隐式内连接：select * from table1,table2 where table1.id = table2.id

② 显示内连接：select * from 表1 inner join 表2 on 连接条件…; //inner可省略

（二）外连接

2.1 左外连接

左表所有数据都会显示。

outer关键字可以省略。

需求：查询emp的所有数据，对应dept的信息
方式一 通用：
	select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id

方式二 Oracle专有：
	select e.*,d.name from emp e,dept d on e.dept_id = d.id(+)

左连接可以右表的条件处加一个"（+）"，即可表示左连接，右连接相反。

2.2 右外连接

例：查询dept的所有数据，对应emp的信息
方式一：
	select e.*,d.name from emp e right outer join dept d on e.dept_id = d.id
方式二 Oracle专有：
	select e.*,d.name from emp e,dept d on e.dept_id(+) = d.id

右连接在左表的条件处加一个“（+）“

六、子查询

（一）where中的子查询

1、单行子查询：子查询结果只中只有一条记录

操作符：大于等于、小于等于…

例：select * from user where dept_id = (select id from dept where dept_name=“人资”);

2、多行子查询：子查询结果只中有多条记录

操作符：IN、ANY、ALL

例：select * from user where dept_id IN (select id from dept limit 3);

（二）from中的子查询

from后可以是表或查询，这里是查询

select * from (select rownum r,t.* from table1) where r=10

（三）select中的子查询

需求：使用子查询将emp表中的dept_id换为dept的dept_name

例：select id,name,(select dept_name from dept where id=dept_id) 部门 from user;

析：子查询中的条件dept_id是user表中的dept_id

七、分页查询

（一）简单分页

select * from (select rownum r,t.* from table1) where r<=20 and r>10 //子查询时已经查询出结果了

（二）基于排序的分页

select * from (select rownum r,t.* from (select * from table1 t order by id desc) t ) where r<=20 and r>10