oracle基本使用

一、基本语法

1.1 基本查询

	//* 进行查询，运行效率偏低。可读性差。
	select * from 表名 
	select id,name,name,email... from 表名
	
	//对列的内容进行运算 (+-*/)
	select last_name,salary*12 from employees;
	
	//列起别名 alias，as关键字可以省略
	select 列名 [as] 别名，列名 [as] 别名 from employees;
	
	//多列内容的连接
	select first_name||last_name as name , salary from employees;

1.2 条件查询where/order by

	比较查询 >  <  =  !=  >=  <= 
	逻辑运算  and  or 
	特殊谓词  in 或者 not in
	between  ....  and 在一个范围 或者 区间中 （闭区间）
	not between 100 and 200 //（闭区间）
	is null/is not null
	like '%A%'
	
//单列排序/多列排序
select name,salary from employees 
	where  salary >600 
	order by salary desc,name asc;//desc降序 默认asc升序。

1.3 内置函数

//1 sysdate  获取当前系统时间
	select sysdate from employees 
	//oracle中的虚表，哑表 dual
	select sysdate from dual

//2 to_char(日期,’日期格式’) 把一个日期类型，转换成一个字符串 //yyyy/mm/dd/day/hh/mi/ss 日期格式
select to_char(sysdate,'mm') from dual;

//3 to_date(‘字符串’，‘日期格式’)把字符串转换成日期
to_date函数 主要应用在数据的插入过程。

1.4 组函数

• 针对于一组数据的操作。默认在不分组的情况下，会把一张表的数据划分成一个组。

//最大值 max()/min()/avg()/sum()/count(*)
select deepartment_id,count(*) from employees group by department_id;

//查询1997年每个月份入职的员工人数
	select to_char(hire_date,'mm'),count(*)
	 	from employees 
	 	where to_char(hire_date,'yyyy')='1997'
	  	group by to_char(hire_date,'mm');

小结：
   1. 只有出现在group by中的列名，才可以书写在select字句中。
   2. 如果列名出现在组函数中，也可以书写在select字句中。
   3. 普通的内置函数，如果应用在了group by子句中，可以书写在select字句中。

1.5 having 的使用

• 分组之后的条件判断

//平均工资大于5000 这些部门
select department_id,avg(salary)
 	from employees 
 	group by department_id
 	having avg(salary)>5000

// having的使用依据： 如果你的条件判断中，涉及到应用组函数的条件判断，则需要使
//用having,其他条件判断 需使用 where
//查询1997年个月中 大于2人 入职的这些月份及其员工数
select to_char(hire_date,'mm') asmouth,count(*)
 	from employees 
 	where to_char(hire_date,'yyyy')='1997'
	group by to_char(hire_date,'mm')
 	having count(*)>2;

1.6 关键字的使用顺序

	select * 
	from  
	where 
	group by
	having
	order by ...

二、子查询

• 在一个SQL语句中，嵌套另一个SQL语句。查询效率低，尽量避免使用.

2.1 where子查询

//查询Last_name 为Abel 这个员工的部门名称
select department_name 
	from departments 
	where department_id=(select department_id 
			from employees where last_name='Able')

//多值where子查询		
//查询last_name 叫做 ‘King’ 这个员工的部门
select department_name 
	from departments 
	where department_id in (select department_id 
			from employees where last_name='King')

2.2 from 子查询

//让sql语句的查询结果，充当表概念。
//from 表名 from (sql 语句)
select * from (select employees_id,first_name,last_name,from employees);

三、伪列

• 特殊列：不需要开发人员手工创建的列，oracle自动创建。
• 不主动发起查询，看不到伪列的数据内容

3.1 rowid

• 行数据 在数据库中的唯一标示，代表行数据所对应的物理存储空间

	select t.ID,t.NAME,t.PASSWORD,t.ROWID from T_USER t;
	注意：可以基于rowid进行查询，查询效率最快。

3.2 rownum 分页操作

• 对本次查询结果，符合要求的数据，进行标号.
• rownum 不能做 > >= = 操作

	select t.*,ROWnum from T_USER t where ROWnum<6;

• 如何应用rownum 做 >= > = 操作？

//前提 对ROWnum进行预处理
select * from (
select t.*,ROWnum r from T_USER t
) where r>3

四、多表操作

• 外键（foreign key）:多张表的关系的体现

4.1 内连接（innner join ）

//只会连接2张表中可以连接的数据，不能连接的数据不连接
select e.*,d.* 
	from employees e 
		inner join department d 
				on e.department_id=d.departmnet_id

4. 2 左外连接 （left outer join）

//左边的表全部查询出来，右边的能够连接的连接处来，不能连接的补null。
select e.*,d.* 
	from employees e 
		left outer join department d 
				on e.department_id=d.departmnet_id

4. 3 右外连接 （right outer join）

//右边的表全部查询出来，左边的能够连接的连接处来，不能连接的补null。
select e.*,d.* 
	from employees e 
		left outer join department d 
				on e.department_id=d.departmnet_id

4. 4 多表连接语法。

select a.*,b.*,c.* 
from A a 
     inner join
     B b on a._=b._ 
     inner join
     C c on b._=c._

4. 5 自连接

• 自己连接自己，特殊多表连接。

五、DDL创建表

• DDL (Data Define Language) 数据的定义语言 表的定义 修改 删除

5.1 建表

create table T_USER
(
  id       NUMBER(20) not null,
  name     VARCHAR2(30),
  password NUMBER(10),
  age      NUMBER(10)
);
alter table T_USER add primary key (ID)

5.2 删表

• drop table t_user

如果删除某一张表时，有别的表与其建立了外键关系，则应该先删除子表，在删除父表
可以进行级联删除 在删除表的同时，把约束（外键）同时删除
drop table t_user cascade constraints;

5.3 修改表

1.修改列的类型
	 alter table 表名 modify 列名 类型 
	前提条件 列 没有存储数据。
2.修改列的名字
	alter table 表名 rename column 老列名 to 新列名
3.追加新列
	alter table 表名 add 列名 类型;

六 DML 数据的修改

• DML(Data Modify Language) 表中数据的插入 修改 删除

i.插入数据
	1.insert into t_user (id,name,birthday) values (1,’suns’,sysdate);
	注意：DML 语句 必须执行 commit操作 才可以同步数据库
	      处理日期 to_date函数处理
	2.insert into t_user values(1,’suns’,sysdate);
ii.删除数据
	1.delete from t_user 全表所有的数据全部删除
	2.delete from t_user where ...条件删除
		delete from t_user where id = ?
		delete from t_user where age in (10,30)
		delete from t_user where name like ‘a%’
iii.修改数据
	1.update t_user set name=xxx,birthday=xxx,column_name=value 修改全表数据
		注意：主键 不能进行修改
		update t_user set name=xxx,birthday=xxxx where ....
		update t_user set name=xxx,birthday=xxx where id = ?
		update t_user set name=xxx,birthday=xxx where name = ? or id= ?
		udate t_user set name=xxx,birthday=xxx where name like ‘suns%’

七 事务控制语言

• TCL(Transaction Control Language)
• 事务：保证业务操作完整性的一种数据库机制。

	1、commit|rollback(撤销 回滚)
		提交DMIL操作，撤销DML操作
	   commit就是把回滚段中的数据，提交到数据库的文件系统中进行存储
	   rollback就是把回滚段中的数据，清空撤销。
	2. 可以控制一组sql语句，要不一起成功，要不一起失败，从而保证业务操作的完整性
	3. 事务的特性（ACID）
         1 A 原子性 一组sql语句 一起成功 一起失败 原子操作 完整整体
         2 C 一致性 操作前的数据 操作后的数据 保持一致。
         3 I 隔离性 保证多用户并发访问的数据安全。
         4 D 持久性  事务操作的数据 持久化到数据库中

八 Oracle数据库中的其他对象

8.1 序列 Sequence

作用：主键的自动生成。
i.创建sequence  create sequence suns_seq start with 100 increment by 2
ii.如何使用 suns_seq.nextval
insert into t_user values (suns_seq.nextval,’xiaohei’,sysdate);
iii.删除序列
drop sequence suns_seq

8.2 视图 View

把查询的sql语句，命名，存储在数据库中便于后续的重复使用。
	i.创建View create view emp_dept_view as (select语句)
	ii.如何使用视图
		select * from emp_dept_view where ....
		注意：1 视图存储的是什么？
		        视图存储的是sql语句 效率上 和原来书写程序 一样。
		     2 不建议应用视图进行增删改（DML）的操作 
	iii.drop view emp_dept_view; 

8.3 索引 Index

提高sql语句查询效率 （B+tree,BitMap,FullText）
i.创建索引 create index name_index on t_user(name);
ii.查询  select name from t_user;
	注意：1 索引的创建，是占用存储空间（硬盘），不能简单的就创建索引。
	      2 oracle自动的为pk ,唯一列 加入索引。
iii.drop index name_index;

九、数据库查看锁表与解锁。