很久前Oracle的一些笔记_1-3

MySql可以用show databases;而Oracle需要使用
select table_name from all_tables;show tables.
-------------------------------------1----------------------------------------

1.select sysdate from dual; 查询日期   用户名 system  密码 orcl  数据库ORCL  2. create table tb_1 (id number,name varchar2(18)) 注意oracle 不用int  用 number3. 忘记管理员密码      命令行下输入 sqlplus /nolog 进入SQL*PLUS      输入 conn / as sysdba        修改密码 alter user system identified by pwd            然后可以使用system用户 和修改的pwd 登录了  密码第一个字符不能是数字4.  主机字符串  是一个写在配置文件(内有IP地址 端口号)的一个标示  一般是orcl           网络配置文件  tnsnames.ora 是Oracle主机配置文件 服务名向导配置文件         监听器配置文件  listener.ora 重要的两个配置文件               Oracle默认端口 15215.测试数据库 是否联通 tnsping 主机字符串   ①②③④⑤⑥⑦⑧⑨⑩6.Oracle 所使用的操作系统物理文件  分为①数据文件  DBF文件②控制文件  CTL控制文件③日志文件  LOG日志文件  7.ORACLE的默认用户 ①SYS 系统  系统最高权限管理员  orcl②SYSTEM 默认的系统管理员 orcl③SCOTT 示范账户 测试账号 默认锁住 alter user scott account unlock 解锁  默认密码tiger8.数据字典主要有三种静态视图组成 每种应用在不同范围  DBA 所有方案的对象   DBA_XXX ALL 用户所能访问的对象  ALL_XXX USER 在用户方案的对象      USER_XXX9.用系统管理员登陆  查询  系统表空间  select * from DBA_TABLESPACES;查看所有表看空间 健在数据库的所有信息select * from DBA_DATA_FILES; 查看所有数据文件select * from DBA_TABLES; 查看所有在数据库的表select distinct tablespace_name from dba_free_space   查看的表空间 即 dbf文件10 创建表空间即DBF文件   CREATE TABLESPACE ZUKGIT DATAFILE 'E:\ZUKGIT\zukgit.dbf'   SIZE 20M    AUTOEXTEND on;11 删除表空间  同时删除表空间所在的物理文件DROP TABLESPACE ZUKGIT INCLUDE CONTENT AND DATAFILE;12 要连接数据库 必须要一个用户账户 （ORACLE 通过用户管理数据） 每个用户都有自己默认的  表空间  和一个临时表空间Create User 用于用户   //用户有对应的权限 CREATE USER zukgit     //刚创建的用户没有任何权限IDENTIFIED BY zukgit      //它的账号密码登陆不了ORACLE 也DEFAULT TABLESPACE tablespace  //做不了任何操作  所以需要给用户授权TEMPORARY TABLESPACE  tablespace(TEMP这个表空间专门为临时表空间)13 select * from all_users;//查看所有用户select * from dba_users;  //查看所有用户14权限有两种   系统权限和对象权限System系统权限允许用户执行数据库操作 如 创建表Object对象权限允许用户对对象（表,视图）进行操作角色是一组相关权限的集合 为了方便管理 我们可以权限赋予角色 角色赋予用户，以达到简化的目的 Oracle有100多种系统权限通过Grant 给用户赋予权限或者角色 给12创的用户赋予登陆角色  grant connect to zukgit with admin option;   with admin option 表示zukgit又可以给别的用户赋予权限其实   with admin option 可以不写。  grant connect to zukgit with admin option;    连接登陆的角色 connectrevoke connect from zukgit;收回登陆的角色grant resource to zukgit with admin option;    创建表的角色 resourcegrant connect,resource to zukgit;一次赋予多个角色grant dba to zukgit; 赋予管理员权限drop user zukgit;删除用户

-------------------------------------2----------------------------------------
1 数据类型、  ORACLE 没有双引号  select id ,case name when 'sun280' then '??' end as DD from host;  ①②③④⑤⑥⑦⑧⑨⑩    ⑴⑵⑶⑷⑸⑹⑺⑻⑼⑽  ①数值型  number(<p>,<s>)  Integer/int number(38)  在定义整型时候  一般情况都是number很少用到int 定义浮点型 就要用到number(12,1)   -float  浮点型② 日期型      date  默认格式 DD-MON-YY   日 月 年    timestamp date的扩展③LOB类型 大对象 Large Object     BLOB 二进制大对象  包括图片 MP3 电影     CLOB  字符大对象  包括文章④ 字符型 char(<size>) 最多2000字节 占用空间固定就是size个字节 没占用的char就填充空字符                            char的效率比varchar2高   varchar(<size>) 最多4000 字节 占用空间变化                         size只是指定最大的你不能大过的值 它实际存储空间看他存储的值   varchar2(<size>) 最多4000字节 占用空间变化         目前 varchar 和varchar2 是同义词          工业标准的varchar类型可以存储空字符串 但Oracle不这样做，不允许这样做~（varchar不能存储空字符串）   保留了这样做的权利。Oracle自己开发了一个数据类型varchar2，它将工业标准的varchar可以存储空字符串的特性 改为存储NULL值 ~ORACLE建议使用VARCHAR2 而不是VARCHAR  何时用Char 何时用VARCHAR2 ？    Char和VARCHAR2 是矛盾的统一体~VARCHAR2比Char节省空间 但效率上比Char会弱 牺牲空间 获得效率 Char                    如果VARCHAR2烈经常被修改 每次修改的数据不一样 那么会造成行偏移现象                所以经常修改的话那么数据类型选择CHAR会好一点⑤ ROWID 表示在行中的唯一地址2,.SQL分类   DDL 数据定义语言 （基本上与数据无关与结构有关）     包含： Create , Alter, Drop, Truncate,Rename 等  DML 数据操纵语言（与数据有决定关系）      包含：Select,delete,update,insert 最重要  DCL 数据控制语言（就是赋予权限 收回权限的语言）     包含：grant  ~~to,revoke~~ from  TCL 事物控制语言(表示的是数据提交控制语言~要么一起完成~要么都不完成)     包含 Commit,ROLLBACK,SAVEPOINT3.DDL 对表结构进行操作 建表   create table tab_1(id number,sex char(5),age number,address varchar2(25),iphone varchar2(15));create table tab_2 as select id from tb_1;另一种方式 建表~~从其他的表结构创建数据 create table tab_2 as select id from tb_1;增加字段  alter table tab_1 add email varchar2(20);修改字段(把Address增长)  alter table tab_1 modify address varchar(50);删除iphone字段   alter table tab_1 drop column iphone;重命名字段名  alter table tab_1 rename column iphone to iphone4s;修改表的名字  rename tab_1 to tab_zukgit;为表的字段加注释 便于理解  即注释Comment on table tb_1 is '祝正杰学Oracle';Comment on column tb_1.iphone4s is '苹果';查看 表有注释的(数据字典)select * from user_tab_comments where comments is not null;查看 字段有注释的(数据字典)select * from user_col_comments where comments is not null;删除一张表 drop table tab_1; 在执行drop table 语句时候 Oracle会把删除表放在数据库回收站里10g以后可以恢复把表从垃圾站恢复回来FlashBack table tab_1 to before Drop;不能恢复的删除彻底删除一张表  drop table tab_1 purge[cascade constraints];4.DML  插入数据     insert into tab_1(id,sex,age)  values(1,'m','22');  不提交事物的话一个Session更改了数据 能在该session得到结果但另一个session得不到这个结果 只有session提交事务commit;才能让数据写到数据库 让所有session访问到   对Oracle来说DML语言需要手动提交我们执行一条DML语句 ,sql会先放入缓存(回滚段)  需要我们手动提交commit;commit;  提交rollback; 把上条语句撤销掉； 插入数据     insert into tab_1(id,sex,age)  values(1,'m','22');插入数据 另一种方式     insert into tab_2(id,sex,age) select id,sex,age from tab_1;修改数据   update tab_1 set age=23 where id=1;update tab_1 set age=23 , id=1 where name='zukgit'; 修改两个数据  update tab_1 set age=23;如果不加where语句 那么就对所有字段修改删除数据  delete  不能用*delete from tab_1 where id=3;delete from tab_1; 删除所有数据(表存在  可以回滚)truncate table tab_1;DDL语句删除表数据（表存在 不可以回滚）  不可以回滚那么效率就快drop table tab_1 连表都删除了5.数据完整性   存储在数据库的数据都是正确状态，否则数据库就丧失数据完整性 数据库采用多种办法 保证数据的完整性  外键 约束 规则 触发器约束:是在表中强制执行的数据，校验规则 保护数据完整性 大部分数据库支持五类数据完整性 not null 非空约束 unique  唯一约束 foreign key 外键约束 primary key  主键约束 check 检查约束6 约束  列级约束列级约束 直接跟在列后面定义，不指定列名，与下一个字段定义逗号隔开create table student(int number ,name varchar2(15) not null,//非空约束sex char(4) not null check(sex='男' or sex='女') , //检查约束age number not null check(age>18 and age<60),  //检查约束address varchar2(25) default 'gz'  //假如 不插入这个数据 默认为广州)唯一性字段可以是空 但不能相同主键和唯一键 Oracle自动创建索引外键与主键                REFERENCE 就是个坑    REFERENCES才对 create table class(id number primary key,code varchar(16) not null)create table student(int number ,name varchar2(15) not null,//非空约束sex char(4) not null check(sex='男' or sex='女') ,age number not null check(age>18 and age<60)，classId number  not null references class(id) // 行级外键约束 非空约束  列级约束)7.表级约束 在所有字段定义好之后添加约束  逗号隔开create class(id number, code varchar2(15),constraints class_pk primary key (id))//要定义约束名？                 删除这个约束 就要操作这个约束名create table student(int number ,    表级约束  是不能添加非空约束的name varchar2(15) not null,//非空约束sex char(4) not null,age number not null，classId number not null， constraints student_pk primary key (id),// 表级外键约束 非空约束  列级约束constraints student_unique  unique(id),constraints student_check_sex check (sex='男' or sex='女'),constraints student_check_age check (age>18 and age<60), constraints student_fk foreign key (classId) REFERENCES  class(id)// 表级外键约束)8.约束的维护 create table class(id number,code varchar2(10) not null);alter table class add constraints class_pk primary key (id);增加主键约束create table student(int number ,name varchar2(15) not null,//非空约束sex char(4) not null  , //检查约束age number not null ,  //检查约束address varchar2(25) default 'gz' , //假如 不插入这个数据 默认为广州classId number)alter table student add constraints student_pk primary key (id);//增加主键约束alter table student add constraints student_fk foreign key （classId）REFERENCES class(id);//增加外键alter table student add constraints student_unique_email  unique(Email)//增加唯一约束alter table student add constraints student_check_sex check(sex='男' or sex='女)//增加检查约束alter table student add constraints student_check_age check(age>18 and age<60)//增加检查约束//删除外键约束约束   约束如果不自定义那么系统会给定义alter table student  drop constrains student_fk;  通过约束名删除约束  删除外键约束//删除唯一约束约束alter table student  drop constrains student_unique_email;//禁止约束 或激活约束alter table student disable constrains student_fk;禁止约束alter table student enable constrains student_fk; 激活约束9 复合约束   联合主键create table person(lastname varchar2(20),firstname varchar2(20),age number,constrains person_pk primary key(lastname,firstname));对于外国人来说联合主键  两个和在一起不重复10 查询语句select * from tab_1;查询的时候可以使用 +-*/ select empno,sal,sal*12 from emp; 查询员工编号, 月薪,  年薪连接操作  select dname||'_'||loc from emp；查询的数据连在一起---where 条件select * from emp where empno=111;select * from emp where job='CLERK';select * from emp where job='CLERK' and empno=111;select * from emp where job='CLERK' or empno=111;--查询NULL值 select * from emp where job is null;  不能 用 =null select * from emp where job is not null; select * from emp where job is null; 不等于 select * from emp where job="";--所有与NULL 进行运算 结果都为空select empno,(sal+comm)*12 from emp;  因为有的comm奖金是空 所以返回结果好多是空            只有comm不为空的才不返回null 返回数值--定义别名 select empno,sal*12 年薪 from emp; --查询重不复记录 distinct select distanct deptno from emp; select distanct deptno,job from emp;//以 deptno,job组合不重复来判断输出    输出更多的行了、--限制      <>不等于  查询 部门号是20 ,job是 clerk  薪水大于 1000的select * from emp where deptno=20 and job ='CLERK' and sal >1000; ---between and 查询薪水大于1000 并且 小于 3000的select * from emp where sal >1000 and sal<3000;（不包含）select * from emp where sal between 1000 and 3000; 相当于 大于等于1000 小于等于3000 （包含）---in（list）  in执行的时候会拆分为一堆的or--查询部门在20,30的 select * from emp where deptno in(20,30);select * from emp where deptno not in(20,30);select * from emp where deptno=20 or deptno=30;---like  模糊查询   %表示任意多个(包括0个)的字符  _下划线代表一个字符select * from emp like ename like '%A%'; 所有名字包含A的员工--- is not null select * from emp where comm is nou null---优先级  select * from emp where job="CLERK" or job='MANAGER' AND sal>2000;---排序 order 默认升序order by ~~desc  order~~by asc ---单行函数 进来多少行  出去多少行字符函数:   一：大小写转换函数 lower upper initcap(首字母大写 每一个一个单词)   二：字符串处理函数 concat(连接两字符)                      substr(获取子字符串)                        Length(返回长度)                        Instr(返回字符串位置)                      LPAD|RPAD (空格补齐宽度)                        TRIM|LTRIM|RTRIM(去掉空格)                         REPLACE(替换字符串)()()   select * from emp where lower(ename)='smith';   select upper('hello') from dual;-->HELLO   select upper('HELLO') from dual;-->hello   select INITCAP('sql course') from dual; -->Sql Course   select concat('HELLO','world') from dual;;-->HELLOworld   select substr('HelloWorld',1,5) from dual;-->Hello  索引从1开始到5包含   select substr('HelloWorld',4) from dual; -->oWorld直接给一个参数就是从4开始取到末尾   select length('HelloWorld') from dual;-->  10   select INSTR('HelloWorld','H') from dual;-->1   RPAD在列的右边黏贴字符串   select RPAD(sal,8,'*') from temp;--> 显示8位 不足用*填充 12345*** 123*****    select LPAD(sal,8,'*') from temp;*****123    *1234567  select trim('   HelloWorld  ') from dual;-->删除首尾空字符 HelloWorld select length(trim('   HelloWorld  ')) from dual;;-->10  嵌套函数select trim(‘H’from 'HelloH WorldH') from dual;-->elloH World 删除首尾的Hselect trim(‘H’from 'HelloH WorldH ') from dual;-->elloH WorldH 删除首尾的H 因为最后的是""空字符select trim(leading‘H’from 'HelloH WorldH ') from dual;-->elloH WorldH 只删除前面的Hselect trim(trailing‘H’from 'HelloH WorldH') from dual;-->HelloH World 只删除后面的Hselect replace('helloworld','ll','FF') from dual;-->heFFoworld数值函数:select Round(45.926,2) from dual;--> 45.93   取两位 四舍五入select TRUNC(45.926,2) from dual;--> 45.92    取两位 截断select MOD(1600,300) from dual;--> 100 取余日期函数: 日期上加上或减去一个数字仍未日期  两日期相减返回之间隔得天数  Oracle内部使用数字存储日期 ： 世纪,年,月,日,小时,分,秒,          Oracle默认日期格式  DD-MON-RR  YYY-MM-DDselect sysdate from dual;  ;-->当前时间select trunc(sysdate+1/24) from dual; -->得到下一小时0分0秒 2014/3/29 16:00:00select trunc(sysdate+1) from dual;得到下一天0时0分0秒select months_between(d1,d2) from dual;  -->返回d1 d2 之间的月数 d1d2可以是字符select add_months(d,I) from dual;  -->返回d 加上I月后的日期select last_day(d) from dual;  -->返回d 月最后一天的日期select last_day(sysdate) from dual;  2014/3/31 16:05:11 最后一天下个月的第一天select trunc(last_day(sysdate)+1) from dual;  2014/3/31 00:00:00 最后一天凌晨得到下一年的 1月 1号 0时 0分 0秒select months_add(trunc(sysdate,'yyyy'),12) select round(sysdate) from dual 超过中午12点 就进入下一天select round(sysdate,'mm') from dual 超过15号 就进入下一月--截断日期   select  trunc(sysdate,'mm') from dual   select  trunc(sysdate,'yyyy') from dual转换函数:TO_CHAR(date,'fmt') 函数对日期的转换select to_char(123) from dual 变为字符‘123’select to_char(sysdate) from dual --> 13-6月-12   12年6月13号select to_char(sysdate，‘yyyy-mm-dd hh:mi:ss’) from dual --> 2013-06-13 14:13:22 TO_DATEselect to_date('2012-06-12 13:42:21','yyyy-mm-dd hh:mi:ss') from dual;2012-06-12 13:42:21 日期TO_NUMBERselect to_number('123') from dual; 字符变数字123select to_number('aa') from dual; 报错通用函数: nvl nvl2 case decode 许多数据库提供空 从数据库拿到的数据是空 有时候需要把null转为0；case实现逻辑if else select ename,sal,nvl(comm,0) from emp;//把奖金为空的用0代替select ename,sal,nvl2(comm,comm,0) from emp;//三目运算 如果comm为空则返回第三个 如果comm不为空则返回它自己case语句select ename,job,sal ,case job     when 'SALESMAN' THEN sal*0.9when 'ALERK' THEN sal*0.8                                when 'SALESMAN' THEN sal+100else sal endas t_sal from emp;     根据部门发不同的钱    ename,job,sal ,t_saldecode语句            同样实现if else语句select ename,job,sal ,decode( job      'SALESMAN' , sal*0.9 'ALERK' , sal*0.8                                 'SALESMAN' ,sal+100)as t_sal from emp;    根据部门发不同的钱    ename,job,sal ,t_sal ---多行函数 组函数 进来多行 出去一行mysql> select name,sum(case course when 'hibernate' then grade end) as hibernate ,sum(case course when 'spring' then grade end) as spring ,sum(case course when'JDBC' then grade end) as JDBC from tb_course group by name;+-------+-----------+--------+------+| name  | hibernate | spring | JDBC |+-------+-----------+--------+------+| marry |        60 |     70 |   50 || tom   |        50 |     80 |   20 |+-------+-----------+--------+------+select * from tb_course;mysql> select * from tb_course+-----------+-------+-------+| course    | grade | name  |+-----------+-------+-------+| JDBC      |    20 | tom   || Hibernate |    50 | tom   || Spring    |    80 | tom   || Spring    |    70 | marry || Hibernate |    60 | marry || JDBC      |    50 | marry |+-----------+-------+-------+mysql> select name,case course when 'hibernate' then grade end as hibernate ,case course when 'spring' then grade end as spring ,case course when 'JDBC' then grade end as JDBC from tb_course ;+-------+-----------+--------+------+| name  | hibernate | spring | JDBC |+-------+-----------+--------+------+| tom   |      NULL |   NULL |   20 || tom   |        50 |   NULL | NULL || tom   |      NULL |     80 | NULL || marry |      NULL |     70 | NULL || marry |        60 |   NULL | NULL || marry |      NULL |   NULL |   50 |+-------+-----------+--------+------+


-------------------------------------3----------------------------------------
我有外键 箭头就指向我 1______>n  的关系  foreign 是一对多的信息我就是多的一方            foreign 加 unique 是一对一的信息                                        中间表 |||--->|||<---|||  中间表（有两个外键 并且这两个外键联合主键） 那么就是多对多关系1 . 把关系型数据库中的表中删除冗余数据的过程  称为规范化 重点在冗余  并避免非规范的数据更新 数据删除异常   第一范式： ER  E为实体   R为关系      在一张表中 必须要有主键(无论多少个主键必须要有主键)        并且表的每个字段是不可再分的字段    （不要象电话这样 还可以分为手机电话 家庭电话 既不能当主键 也不能是字段 需要细分电话）                   create table person(phone number primary key,name varchar2(10),card varchar2(18))          create table person(phone number ,name varchar2(10),card varchar2(18) primary key )          不满足第一范式的例子（电话这样 还可以分为手机电话 家庭电话 需要细分电话 为两个字段）   第二范式：(主要针对联合主键)   （没有部分依赖的问题(例如单个关键字) 只要满足第一方式 就是第二范式？）          如果表中的非主属性都完全依赖于  主属性(1个主键，2个联合主键，3个联合主键都可能) 则是第二范式           例如: create table sci(学号 number ,学生姓名 varchar2(10),课程号 number,成绩 number,学分 number,constarins cci_pf primary key(学号,课程号)); 这个表有联合主键  (学号,课程号)          不满足第二范式的例子(部分依赖了 第二范式需要完全依赖) 但满足第一范式的例子会出现问题：一：数据冗余                 假如同一门课由40个学生选修，学分(每门课程学分都一样)那就重复40次            二：更新异常                   若调整了某课程的学分，表中的相关元组（一行）都要更新，当没有完全更改                 完时，有可能出现同一门课程学分不同            三:插入异常               如果计划开新课,由于没人选择这个课程，没有学号关键字，所以不能插入               只能等有人选择才能把课程和学分插入（其实是先有课程才让学生选择）            四:删除异常               如果在表中的学生李四 结业 需要删除他的数据，从当前数据库删除                李四选修记录  假设：李四选修课程只有他这么一条记录               （即数据库显示只有李四一人选修该课程（如Oracle）） 那么删除李四的信息时，                那么Oracle这个课程也会被删除 在数据库就再也找不到Oracle课程  产生这些异常的原因： 非关键字属性   课程名 学分 仅依赖于课程号 ，                        也就是 课程名 学分 部分依赖关键字(学生号码,课程号码)                         而不是完全依赖于关键字(学生号码,课程号码)   解决模式：分成两个关系模式           create table 学生(学号 number primary key，课程号 number references 课程(课程号) ，姓名 varchar2(10)，分数 number);         create table 课程(课程号 number,课程名 number,学分 number);   第三范式：      表中的所有非主属性字段都依赖于一个主键（第二范式是允许完全依赖联合主键的但第三范式不行）        非主属性字段只能依赖主键  非主属性字段之间也不有函数联系    依赖指的是 我可以根据这个字段 推出那个字段  例子： Create table 学生(学号 number primary key，姓名 varchar2(10),学院编号 number,学院名 varchar2(10), 学院地址 varchar2(10) );     不满足第三范式的例子( 第三范式需要完全依赖依赖于主键 而学员名不依赖于主键) 但满足第一和第二范式的例子     分析:关键字 学号决定各个属性，由于是单属性，没有部分依赖问题 所以肯定是第二范式。       但这个关系肯定有大量冗余 学院名   学院地址           数据冗余  更新异常  插入异常  删除异常 都会出现   解决： Create table 学生(学号 number primary key，姓名 varchar2(10),学院编号 number foreign key 学院(学院编号)); Create table 学院(学院编号number primary key，学院名 varchar2(10),地址 varchar2(10));2. 表的关系  一对一  一对多    多对多   课程和学生 多对多  中间表   学生(多)和班级（一） 多对一  多对一 外键  多的一方有外键   人和身份证  一对一create table student(int number ,name varchar2(15) not null,//非空约束sex char(4) not null check(sex='男' or sex='女') , //检查约束age number not null check(age>18 and age<60),  //检查约束address varchar2(25) default 'gz' , //假如 不插入这个数据 默认为广州classid number references class(id))create table course(id number primary key,name varchar2(8),credit number)课程和学生 多对多  中间表--学生课程中间表--  1 建立主键  学生表外键 课程表外键                     2学生id 课程id 联合主键 同时还要是外键create  table student_course(student_id number foreign key student(id),course_id number foreign key course(id),grade number,constrains pk primary key(student_id,course_id))--一对一   人 和  身份证  1 唯一外键关联  2主键关联 1 唯一外键关联create table person(id number primary key,name varchar2(10),card_id number unique,constrains person_fk foreign key (card_id) references card(code))create table card(id number primary key,code varchar2(18)   ---身份证) 2主键关联create table person1(id number primary key,name varchar2(10),constrains person_fk foreign key (id) references card1(id))create table card1(id number primary key,code varchar2(18)   ---身份证)一对多  就是foreign key  有外键自己就是多