oracle study

工具安装  乐观锁

apply- -ok

1 oracle 数据库创建用户
create user xc identified by xc123;
 
2 然后分配表空间（存放oracle 数据的地方--没有表空间，就无法存放数据）
 create tablespace xc_tablespace  //表空间名
 datafile 'E:\app\oracleuser\product\12.1.0\xc.dbf'    // 表空间位置
size 50m;   //大小

3 alter user xc default tablespace xc_tablespace; //用户xc 默认使用 xc_tablespace
4 授权
grant create session,create table,create sequence,unlimited tablespace to xc;


二、创建角色

角色，即权限的集合，可以把一个角色授予给用户

1、创建角色

create role myrole;

2、赋权 ---  会话权限（没有该权限无法登录）

grant create session to myrole;--将创建session的权限授予给角色myrole

3、赋角色给用户

grant myrole to zhangsan;--授予zhangsan用户myrole的角色

4、删除角色

drop role myrole;

基础

 显示plsql中所有的数据

oracle 语言大小写不敏感   seleCt email FROM empLoyEES;
支持算符运算符  select salary * 12+100 from employees;
日期可以加减
空值是无效的，未指定的，未知的或不可预知的值空值不是空格或者0。

as 别名（as可省略）默认大写 ，可以用双引号效果  （大小写敏感）
|| 连接符     select last_name|| 'aa' from employees;

字符串使用单引号 ''
distinct 去重

Structural query language （sql） 结构化查询语言
sql*plus 一种环境

关键字
where 条件过滤
between and  在一个区间值内
in  或者的意思
使用 IS (NOT) NULL 判断空值
ORDER BY hire_date desc   按照雇佣日期排序  降序 默认是asc升序
to_char()  //将日期转换为char类型
select * from EMPLOYEES where  to_char(hire_date,'yyyy-MM-dd') >='1987-02-01' and 
to_char(hire_date,'yyyy-mm-dd') <='1989-9-21' ;

函数
 select * from EMPLOYEES where upper(last_name)='KING'; //大写
lower 小写   INITCAP  首字母大写

select CONCAT('Hello', 'World'), SUBSTR('HelloWorld',1,5),LENGTH('HelloWorld') from dual;
----------------------- ------------------------ --------------------
HelloWorld              Hello                                      10
---------------------------------------------------------------------
INSTR('HelloWorld', 'W')//某个字符在字符串中的首次出现的位置，没有为0
TRIM('H' FROM 'HelloWorld')//去掉字符串首位的h
select  REPLACE('bbaaabdfddbb','b','m') from dual;//去掉所有的b
-----------------------------------------------

select ROUND(532.25,1) from dual; //523.3
TRUNC //截断
mod 求余
------------------------------
日期函数
日期可以相减，得到相差的天数，加无意义。
select MONTHS_BETWEEN(sysdate,hire_date) from EMPLOYEES  两个日期相差的月数
ADD_MONTHS  //指定日期加上几个月
select NEXT_DAY(sysdate, '星期二') from dual; //指定下一个星期的星期二
select LAST_DAY(sysdate) from dual; //本月最后一天
------------------------------------------
转换函数
1999/8/10  oracle 默认日期（date）类型  HIRE_DATE  select * from employees where 
hire_date ='7-6月-94';
----------------------------------------------------------------------------
to_char()//date转换字符类型
select * from employees where to_char(hire_date,'yyyy-mm-dd') ='1994-06-07'
SELECT TO_CHAR(sysdate,'yyyy-MM-dd hh:mm:ss') FROM dual;
to_date()//字符转换日期类型
select * from employees where to_date('1994-06-07','yyyy-mm-dd') =hire_date;
-----------------------------------------------------------------------------
number转为char
select to_char(214544,'L999,999,990') from dual;
char转为number
select to_number('￥214,544','L999,999,990') from dual;

---------------------
通用函数
select nvl(commission_pct,0) from employees where hire_date ='7-6月-94'  //null值就用0代替

NVL2 (expr1, expr2, expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3。

多表 

表的别名
使用别名可以简化查询。
使用表名前缀可以提高执行效率。

分组

//组函数  任何数据都可以使用max和min的， avg 和sum 只能使用number类型的
SELECT max(email), MAX(salary),
              MIN(salary), SUM(salary)
FROM   employees;  

COUNT（计数）函数忽略空值 组函数忽略空值
COUNT(DISTINCT name)返回name非空且不重复的记录总数
NVL函数使分组函数无法忽略空值：
SELECT AVG(NVL(commission_pct, 0))
FROM   employees;

----------------------------------
select department_id ,round(avg(salary),2)
from employees group by department_id;//查询employees 中各个部门的平均工资--按照department_id
进行分组
----------------------------------
select department_id ,round(avg(salary),2),job_id
from employees group by department_id ,job_id; 按照department_id，job_id进行分组(多条件分组)

---------------having语句-------------
HAVING   MAX(salary)>10000 
--------------嵌套组函数----------显示各部门平均工资的最大值
SELECT MAX(AVG(salary)) FROM employees  GROUP BY department_id;

子查询--单行--多行


select e.last_name from EMPLOYEES e where e.salary>(select e.salary from  EMPLOYEES e where
 e.last_name='Abel');

（）中的值作为结果集返回 
内层返回一个值叫单行子查询 操作符 = , >= , <= <>（不等于）
返回多个值叫做多行子查询。 操作符 in ,any ,all

ddl 操作
对象	描述

表      基本的数据存储集合，由行和列组成。

视图	从表中抽出的逻辑上相关的数据集合。

序列 	提供有规律的数值。
索引	提高查询的效率
同义词 	给对象起别名

-- 创建表
create table gson(
id number(10), ---对应int 类型
salary number(10,2), --对应double 类型
birth date --对应日期类型

)
--创建表依托现有的表 create table gson_1 as select id, birth from gson;

剩余数据类型
LONG 	可变长字符数据，最大可达到2G
CLOB	字符数据，最大可达到4G
BLOB	二进制数据，最大可达到4G
BFILE	存储外部文件的二进制数据，最大可达到4G

alter 语句修改表结构
alter table gson_1 add email varchar2(20); //增加一列
alter table gson_1 add email varchar(16); //修改字段长度 default 增加默认值

alter table gson_1 drop column email;//删除列
alter table gson_1 rename column birth to birthday; //重命名一个列

drop table table_name;//删除表
rename dd to ds; //重命名表

--------------------------从其它表中拷贝数据，不用写values
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM   employees
WHERE  job_id LIKE '%REP%'; //判断条件

---------------------------修改表
update dept set dname='chaoshen' where deptno='20';
--------------------------删除表数据
DELETE FROM	  table

DML语句所涉及到的行被锁定， 其他用户不能操作。

约束

not null 列级约束 非空
unique 唯一  constraint cons_name unique(id)  表级约束

primary key  主键非空，唯一

create table goson2(//主键
id number(10),
name varchar(21) not null, --列级约束
constraint pk primary key(id) --表级约束
)

create table goson5(//外键
id number(10),
name varchar(21) not null,
department_id number(10),
constraint pka primary key(id),
constraint em_fk foreign key(department_id) references departments(department_id)
)

check() 检查约束
create table goson7(
id number(10),
name varchar(21) not null,
salary number(4,2) check(salary >30) -- 插入数据必须大于30
)


-------------
使用 ALTER TABLE 语句 添加或删除约束,但是不能修改约束 有效化或无效化约束

ALTER TABLE      employees DROP CONSTRAINT  emp_manager_fk; //删除约束

ALTER TABLE employees ADD CONSTRAINT  emp_manager_fk FOREIGN KEY(manager_id) 
  REFERENCES employees(employee_id); //增加外键约束

alter table goson7 add constraint pk_mr primary key(id);//增加主键约束
alter table goson7 modify(salary number(4,2) not null);//增加非空约束

ALTER TABLE		employees  DISABLE CONSTRAINT	emp_emp_id_pk; //无效化约束
ALTER TABLE		employees ENABLE CONSTRAINT	emp_emp_id_pk;//激活约束
当定义或激活UNIQUE 或 PRIMARY KEY 约束时系统会自动创建UNIQUE 或 PRIMARY KEY索引

视图 

视图是一种虚表。 
视图建立在已有表的基础上, 视图赖以建立的这些表称为基表。视图操作和表和一样，可以多表创建视图

-- 创建视图
create view empview as select employee_id ,last_name from employees where department_id =80;

--查询视图
select * from empview;

--修改视图
update empview set  last_name = 'chaoshen' where employee_id=145;
--删除视图数据
delete from empview where employee_id=145;

--删除视图
drop viwe empview;

CREATE OR REPLACE VIEW 子句修改视图 有就重新创建的意思

rownum 查询出多少行---查询出employees 中工资最高的十个人

select rownum mark, salary ,last_name from
(select salary, last_name from employees order by salary desc) where rownum <10;

序列

序列: 可供多个用户用来产生唯一数值的数据库对象
1 主要用于提供主键值
2 将序列值装入内存可以提高访问效率

创建序列
CREATE SEQUENCE sequence
       [INCREMENT BY n]  --每次增长的数值
       [START WITH n]    --从哪个值开始
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE n | NOMINVALUE}]
       [{CYCLE | NOCYCLE}]     --是否需要循环
       [{CACHE n | NOCACHE}];  --是否缓存登录

select sequence.nextval from dual;  //查询序列

alter sequence  sequence increment by 1 nocycle //修改序列

  create table emp01 as select last_name,email from employees  where 1=2 --创建空表

使用序列插入数据
insert into emp01 values(sequence.nextval,'a@123');

DROP SEQUENCE sequence;  ---删除序列

索引
索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引。用户不用
在查询语句中指定使用哪个索引。
在删除一个表时,所有基于该表的索引会自动被删除

创建索引
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引

手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询
在一个或多个列上创建索引 语句: CREATE INDEX index ON table (column[, column]...);
--创建索引
create index emp01_index  on emp01(last_name); //创建之后通过last_name来查询oracle会自动调用这
个索引来提高查询效率
--删除索引
drop index emp01;

什么时候创建索引?
列中数据值分布范围很广
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%

什么时候不要创建索引?
表很小
列不经常作为连接条件或出现在WHERE子句中
查询的数据大于2%到4%
表经常更新

同义词 synonym

•方便访问其它用户的对象

•缩短对象名字的长度

grant create SYNONYM to c##scott; 为用户进行创建同义词的授权  需切换到系统用户下

CREATE SYNONYM e FOR employees;  //为表employees 创建同义词 e
select * from e;  //通过查询同义词

------------------------经验----

create table a(     --创建a表
id number(11) primary key,
name varchar(20)

)

create table b(    --创建b表  有外键约束
id number(11) primary key,
name varchar(20),
aid number(11),
constraint pk_a foreign key reference key a(id)
)

效果

 

create table c(  --创建c表
id number(11) primary key,
name varchar(20)

)


create table d(  --创建d表
id number(11) primary key,
name varchar(20),
cid number(11)

)

效果

加入外键和不加外键的查询效果是一样的，使用外键有利于维持数据完整性和一致性，但是对于开发来说是非常不利的。