day73_oracle03_笔记

==========笔记=============

---------------oracle第三天-------------一、创建表空间+用户+赋权限1.1创建表空间,同时创建dbf文件--scott用户名没权限，PL/SqlDeveloper-->Session-->logo on 可以切换用户create tablespace heima_68_space   --表空间的名字datafile 'c:\\heima_68.dbf'        --数据文件----同时创建文件size 10M                           --指定数据文件的大小autoextend on                      --数据文件自动增长next 1M;                           --增长的大小select * from session_privs  --查看当前登录人的权限1.2创建用户，指定操作的表空间create user heima_68                 --用户名identified by heima_68               --密码default  tablespace heima_68_space   --### 指定操作的表空间-----现在 登录不了，缺少权限。1.3给用户赋权限---简化赋予权限给用户：每种角色有多种默认权限。角色 dba---管理员 system用户 ---都有161种权限（最多）resourceconnectgrant dba  to heima_68;--###-给用户赋予了 DBA管理员的权限，现在登录成功。select * from session_privs  --查看当前登录人的权限：---全部161种权限--------------------------------ok二、Oracle常见的数据类型2.1字符型char      255    固定长度     name char(10)   'TOM'varchar2  3999   不固定长度   name varchar2(10)   'TOM'---【推荐使用】2.2数值型number  id  number(3)  999     ---总长度  aa  number(3,2)  999.99 错！！ -9.99    -- number(3,2) 第一个参数：总长度(不包括小数点和负号),第二个参数:小数位数。 2.3日期型date   相当于mysql 的datetime：--年月日+时分秒timestamp 时间戳  精度支持秒小数点后9位2.4大数据类型long 相当于mysql longtext  支持2Gclob 支持4Gblob 支持4G--------------------------------ok三、创建表--create  ------ drop3.1约束：        主键  非空  唯一 检查 外键     drop table person;---test1:create table person( pid number(10) ,   --###### 主键 设置方式2：pid number(10) primary key , pname varchar2(30) not null,--非空 telephone varchar2(11), --### 唯一 设置方式2：telephone varchar2(11) unique, gender char(1),     --  0女  1男   --###### 检查 设置方式2：gender char(1) check(gender in(0,1)),  ---约束设置方式二：（可以自定义 约束名称，报错检查比较简单） constraint person_pk_pid primary key(pid),  --###### 主键 设置方式1 constraint person_unique_telephone unique(telephone),--### 唯一 设置方式1 constraint person_check_gender check(gender in(0,1))--###### 检查 设置方式1)insert into person  values(1,'TOM','13898765432','1');---省略列名，字段顺序要和建表顺序一致insert into person  values(2,'JERREY','13898765431','1');insert into person  values(3,'JERREY','13898765433','1');insert into person  values(4,'hello','13898765434','0');select * from person---test2:create table orders( ooid number(10) primary key, totalPrice number(10,2));create table orderdetail( odid number(10) primary key, goodsName varchar2(30), ooid number(10), constraint orderdetail_fk_ooid foreign key(ooid) references orders(ooid));insert into orders values(1,1000);insert into orders values(2,5000);select * from orders;select * from orderdetail;insert into orderdetail values(1,'鼠标',1);insert into orderdetail values(2,'键盘',2);insert into orderdetail values(3,'键盘',3);---fail。订单ooid=3不存在delete from orderdetail where ooid=1;delete from orders where ooid=1;3.2 修改表（了解）---严重了解---图形界面搞定。alter table person add (address varchar2(10))--添加列alter table person modify(address varchar2(100)) --修改列alter table person drop column address  --删除列3.3 操作数据drop table person;create table person( pid number(10) , pname varchar2(30) not null, telephone varchar2(11), gender char(1),     --  0女  1男 birthday date,      ---添加日期类型。 constraint person_pk_pid primary key(pid), constraint person_unique_telephone unique(telephone), constraint person_check_gender check(gender in(0,1)))---######--- 【插入日期类型 字段：必须转换类型。否则插入失败】insert into person values(111,'TOM','12345678908','1','2008-08-08');---错误：iteral does not match format Stringinsert into person values(1,'TOM','12345678909','1',to_date('2008-08-08','yyyy-mm-dd'))--###-根据已存在的表，复制数据。---as----######-【create 新表名 as（select 已存在的表名）： 快速建表语句】--【通用。非oracle特有】。---oracle 表也是对象，通过用户调用。---mysql 直接：create 新表名 as(select 表名)。create table myemp as (select * from scott.emp);--###-根据已存在的表，复制数据。ascreate table mydept as (select * from scott.dept)给纽约NEW YORK地区的员工涨100元工资select * from myempselect * from mydept--######---子查询时单列结果集时，可以作为 in的参数。select * from myemp where deptno in (select deptno from mydept where loc='NEW YORK');--###--可以作为in的结果集update myemp set sal=sal+100 where deptno in (select deptno from mydept where loc='NEW YORK')--------------------------------ok四、事务保存点  savepointselect * from  person;update person set pname='大郎2' where pid=1;savepoint a;--执行完某一句。执行一句：--语法：【savepoint 自定义保存点名称;】--这句话之前的操作都可以保存和恢复。--作用：【实现恢复保存到SQL操作的任意位置。】insert into person values(3,'小潘','12345432123','0',null);savepoint b;delete from person where pid=2;rollback to a;--恢复到某一保存点：【rollback to 某一保存点名称】--事务提交前才有效。rollback to b;rollback;--###-恢复到本次session最初的状态。---回滚全部操作。事务保存点 使用场景用户注册 1、insert  user 2、发送短信: insert 短信记录表  insert  user;  savepoint a;  insert log表;--------------------------------ok  五、数据库的其他对象：视图、序列、索引、同义词 --5.1视图  view----【很常用】 概念：是个虚表,【可以把视图当做表来操作】 作用|好处--【三个好处(优点)】  --1、可以封装复杂的sql  -- 格式：create view 视图名 as 要执行的SQL   ---根据SQL语句,创建视图,省略括号。  create view view_hireCount as   select sum(t.counts) "Total" ,       sum(decode(years,'1980',counts)) "1980" ,       sum(decode(years,'1981',counts)) "1981" ,       sum(decode(years,'1982',counts)) "1982" ,       sum(decode(years,'1987',counts)) "1987"  from       (select to_char(hiredate,'yyyy') years, count(*) counts from myemp          group by to_char(hiredate,'yyyy') ) t;select * from view_hirecount---简化了复杂SQL的执行。    --2、可以隐藏敏感列：工资。--###-【公司内部严禁谈论工资】  create view view_emp as select empno,ename,job,mgr,hiredate,deptno from myemp;  ---根据SQL语句,创建视图,省略括号。    select * from view_emp;--查出来的结果,没有敏感列:工资。    update view_emp set ename ='aaaa' where empno=7369;--###-修改视图也可以（会导致）修改实际表数据。  --- 因为：【视图只是对SQL的引用(封装复杂SQL)，操作视图实际就是在执行SQL语句】  --- 所以就有了两种 途径修改表。为了防止通过视图 修改表。就需要定义【只读视图】   select * from myemp;      --###-只读视图  with read  only  --格式：create view 视图名 as SQL语句  with read only   create view view_emp2 as    select empno,ename,job,mgr,hiredate,deptno from myemp   with read only    select * from view_emp2  -- 只读视图不可修改  update view_emp2 set ename='bbbb' where empno=7369;---更新失败。  --5.2序列  sequence 概念：---独立于表之外的对象 作用|好处：作用：--######【主要用来做主键自增】 怎么用 序列创建的简单语法 create sequence seq_person; 使用序列 select seq_person.nextval from dual;--每次执行 动态改变。--自增1 select seq_person.currval from dual; select * from person; --######-实例：【主键自增使用序列nextval赋值。】 insert into person values(seq_person.nextval,'小潘','1234540000','0',null);  序列的复杂语法：  5 7 9 11 13 15    3 5 7 9 11 13 ---=====V1：teacher 开始值比最小值 大。 create  sequence seq_test2 increment by 2 --递增值 默认是1 minvalue 3     --最小值 默认是1   --######-【开始值是5,最小值是3,第二轮循环时会从最小值开始】 maxvalue 15    --最大值 默认19个9  9999999999999999999 start with 5   --起始值 默认1      cycle          --循环  默认nocycle---【表示：### 循环从缓存中取数据】 cache 6;--ok          --缓存---【序列存在硬盘(缓存：一次从磁盘取几个数据)】  ---=====V2：开始值比最小值 小。==######=报错；【开始值不能 比最小值 小。】大于等于 create  sequence seq_test3 increment by 2 --递增值 默认是1 minvalue 3     --最小值 默认是1   --###-【开始值是5,最小值是3,第二轮循环时会从最小值开始】 maxvalue 15    --最大值 默认19个9  9999999999999999999 start with 1   --起始值 默认1      cycle          --循环  默认nocycle cache 6;          --缓存  ---=====V3：cache ---- create  sequence seq_testXxxx3 increment by 2 --递增值 默认是1 minvalue 3     --最小值 默认是1   --###-【开始值是5,最小值是3,第二轮循环时会从最小值开始】 maxvalue 15    --最大值 默认19个9  9999999999999999999 start with 5   --起始值 默认1      cycle          --循环  默认nocycle --cache 6;--ok          --缓存 cache 7; --报错：######【number to cache must be less than one cycle】 -----【一次循环：(第一次循环最短(起始值大于等于最小值)：起始值-->最大值 数据个数)】  select seq_testXxxx3.nextval from dual;   select seq_test.nextval from dual; ----【一般每张表各自使用一个序列（生成id）; ---- 多个表共用一个序列,每个表的id值就不连续了（id断层）】  --5.3索引   index 概念：相当于一本书的目录 作用|好处:--【为了提高查询效率】 ----【主键和唯一约束】默认就会创建索引。----查看Objects --> Indexs文件夹可见。 ----【什么时候不能创建索引：1.数据量少；2.数据频繁被改动时】 怎么用：--### 语法：create index 索引名 on 表名(字段名); create index index_emp_ename on myemp(ename);  select * from myemp where ename='SCOTT';  1、创建一个表  create table t_test(---t_test  tid number(10),  tname varchar2(20)  ) 2、插入500W条数据 begin   for i in 1..5000000     loop       insert into t_test values (i,'测试数据'||i);---t_test     end loop;     commit; end;  3、查询记录时间 select * from t_test where tname='测试数据4898765' --1.844---t_test【索引创建前】  4、创建索引  create index index_test on t_test(tname);---【创建索引:###--对某一作为查询条件的字段】  5、查询 记录时间  select * from t_test where tname='测试数据4567865' --0.062---【索引创建后】   5.4同义词  synonym 概念：帅 酷  作用|好处  ---###-缩短访问对象的名称     ---类似SQL的列和表的别名 怎么用？ --【这里是给表（对象）添加同义词】 --添加和删除 create synonym e for scott.emp;---默认是private，在synonyms文件夹下可见。 drop synonym e; create public  synonym e for scott.emp;---public修饰，在synonyms文件夹下不可见（全局所有用户的）。 drop  public synonym e;   select * from e;---【等效于 select * from scott.emp】 六、数据的导入导出1、全部导出---基本不用。看讲义2、按照用户导出--【重点】3、导出某表--- 表上右键，export data。---导出失败。export按钮灰色。--【exp和imp使用命令的前提是当前电脑上安装了Oracle】--只能在装了oracle的虚拟机运行。---直接在cmd下运行即可。按照用户导出把scott用户下的所有对象导入到heima_68用户导出 --exp scott/tiger@192.168.66.10:1521/orcl  file=c:\\scott20171018.dmp owner = scott exp scott/tiger@192.168.65.222:1521/orcl  file=c:\\scott20171020.dmp owner = scott导入 --imp heima_68/heima_68@192.168.66.10:1521/orcl  file=c:\\scott20171018.dmp full=y imp heima_68/heima_68@192.168.65.222:1521/orcl  file=c:\\scott20171020.dmp full=y  ------------小结：----------------1、创建表空间用户（保存语句）2、创建表   数据类型（重点）    ---常用就几个：   ---number、varchar、varchar2(不用varchar:定长；varchar2可变长度,(按实际内容决定长度的)省内存)、date   创建表（重点）   修改表结构（了解）3、操作数据（重点）--基本知识   insert   delete   update   4、事务保存点（了解） 5、视图（重点）--简化复杂SQL执行（类似别名）6、序列 （重点）--###- Oracle独有（通常用于自动生成主键）7、索引  （重点）--######--【给作为查询条件的字段添加索引】---- 为了提高查询效率（当数据量足够大时，使用索引可以明显缩短查询时间）8、同义词 （了解） 9、导入导出 ---操作命令。10、要求：--- 常用的SQL和命令能够找到就行。（保存语句）  

===========================