Oracle个人学习笔记

Oracle个人学习笔记

使用Oracle必须开的两个服务：
1、TNSListener:监听服务
2、OracleServiceAuscend：OracleService+SID
emp表：
empno：员工编号（主键【唯一且不为空】，唯一标识员工的数字号码）
ename：员工名字
job：从事的工作
mgr：员工的领导号（领导号码)
hiredate：员工受雇日期
sal：月薪
comm：提成（佣金）
deptno表：部门号（关联字段）数字

dept表：
deptno：部门号码（主键，唯一标识一个部门）
dname：部门名称
loc：部门所在地址

salgrade表：
grade：级别
losal：工资底线
hisal：工资上线

bonus：奖金表
ename：名字
job：工作
sal：月薪
comm：提成
Oracle中的常用数据类型：
number（1位，2位）
eg：number（2）：99
         number（5，2）：有5位数据，其中2位是小数   100.02
varchar2（1个参数）：动长字符串（长度）
eg：name varchar2(15)
char（1个参数）：定长字符串（长度）
date：日期

sql：structured query language(结构化查询语言)，数据库之间的通讯。
ANNI（美国国家标准协会）将sql作为关系型数据的标准语言。
DML:(data manipulation language),数据操作语言，select ,insert ,update,delete
DDL:(data definition language)数据定义语言，create,drop,alter,truncate
DCL:(data control language)数据控制语言，commit，rollback（事物操作）,grant,revoke

简单查询语句：
（oracle中的关键字是不敏感的，但是字段，表名这些是敏感的）
SELECT 字段 FROM 表名；
select 字段 from 表名；
select * from emp;(*匹配所有的字段）
select ename,sal from emp;(多个字段之间用逗号分隔，但是不能与*同时连用）；

select 字段1 别名1，字段2 别名2 from 表名；

select 字段1  as  别名1，字段2  as  别名2 from 表名；

select 字段1 "别名1"，字段2 "别名2" from 表名；(''表示字符串，“”表示别名)

“**于*日期参加*工作”
select 字段||‘字符串1’||字段2||'字符串2' from emp;(oracle 字符串的拼接用||)

select distinct 字段 from emp;(distinct排重)
select distinct ename,job from emp;(对两个字段联合起来的值进行排重)

select 字段|表达式 from emp;(表达式中支持+=*/运算)
select ename,sal*12 from emp;

dual：虚表；（语言严谨的原因）
select sysdate from dual;(自己可以得出值的语句，不需要从数据库中特定表中查询
数据的语句)

eg：各个部门的工资情况
          有哪些部门
          “select * from 表名”

限定查询（条件查询）：
select 字段1，字段2 from 表名 where 条件表达式（一个或者多个条件）；
eg：找出工资大于1500的员工信息（与数字直接进行比较> ,<,>=,<=
          找出工资在1500到3000范围之内的员工信息（between   。。。 and。。。）
          找出有提成的员工（与null值进行比较：is null，is not null）
          找出没有奖金的员工
          找出低工资》1500并且可以领取奖金的员工信息
          找出低工资》1500或者可以领取奖金的员工信息
          找出工资不大于1500，同时不可以领取奖金的员工信息
          在1981年雇佣的员工信息（1981-1-1  1981-12-31）
          找出叫做smith的所有信息
          找出员工编号为7369，7499，7521这些员工的信息
          找出员工编号不是7369，7499，7521这些员工的信息
          找出名字叫做SMITH,SCOTT,FORD信息
          找出名字中包含M的所有员工的信息（where 字段 like 规则【_ ：可以代替任意一个字符；
          %可以代替任意多个字符】）
          找出名字以M 开头的所有员工信息
          名字倒数第二个字符R员工信息
          名字第二个字符为_的所有员工信息
          在1981年雇佣的员工信息
          工资中包含6的所有员工信息
          找出编号不是7369的所有员工信息   （！=           <>)
排序
select 字段s from 表名 where  条件 order by 排序的条件（多个排序条件，第一个排序条件，第二条件）
{order by 字段 asc(升序，默认)|desc（降序）}
           找出员工信息并按照工资由低到高进行排序
             找出员工信息并按照职位进行排序，如果职位相同的按照工资降序进行排序
             找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)
             的详细资料并且按照部门进行排序，部门相同的按照工资降序排列.
              select                from emp where           order by deptno,sal desc;

单行函数：
字符函数：
upper（字符串|字段）
select 字段|* |函数   from 表名
lower(字符串|字段）
         找出叫做smith的所有信息
          select * from emp where lower(ename)='smith';
initcap(字符串|字段值）  首字母大写
concat(,)字符串连接
length（字符串|字段）算出字符串的长度
replace(字符串|字段值，要替换的字符，替换后的字符）
substr(字符串|字段，从哪个位置开始取，截取个数）
          找出每个员工姓名的最后三个字符

数值函数：
round（数值|字段）四舍五入rounud（，）一个参数
trunc（数值|字段） 截取trunc(,) 一个
mod（，）取模、


日期函数：
日期-数字=日期
日期-日期=数字（天数）
months_between(,)  两个日期相隔几个月
add_months(,)几个月之后的日期
next_day（）下一个今天是什么日期
last_day（）当月的最后一天的日期
trunc（日期|日期字段，截取到哪一位）


转换函数：
to_char（，）转换为字符串 to_char(sysdate,'fmdd')去掉数字前的0（fm去掉前导0），  day 星期
to_number（）转换为数字（少用）
to_date（，）转换为日期类型

一步一步来写，不用着急。
$ (美元)L（人民币）99,999,999.99（没有数据的不用填充） 00,000,000（没有数据用0填充）

通用函数：nvl   decode

nvl用来取代空值的，空值与任何数据进行运算得到的都是空值，nvl（comm，0）有两个参数，
nvl(字段，用什么类型来取代，数字，字符串，要和前面的字段类型保持一致）。

decode（字段，值1，结果，值2，结果） 选择语句 

表连接（解决笛卡尔积问题） ：笛卡尔积
select e.ename，d.ename from emp e，dept d where e.deptno=d.deptno；（关联查询中的等值连接，=表示等值连接） 

非等值连接
找出每个员工的工资
select ename，sal，grade from emp，salgrade where sal between lowersal and highsal；

自连接（自己和自己进行连接）:select e.ename employee,m.ename manger from emp e,emp m where e.mgr=m.empno(将一张表当成两张表看。)（找出每个员工的直接上司）

外连接：+用来填充null记录，然后匹配另外一张表，将对方的数据都找出来）
找出所有的领导
select e.ename employee,m.ename manger from emp e,emp m where e.mgr(+)=m.empno（+表示要打印出所有的领导）
左外连接：left outer join +写在右边，将左边的数据都打印出来
右外连接：right outer join +写在左边，将右边的数据都打印出来

c/emp.deptno/emp.deptno（+）
不要用同一张表
select e.ename,s.grade,e.sal,m.ename magr,m.sal,g.grade,d.deptno,d.dname from emp e,emp m,salgrade s,salgrade g,dept d where e.mgr=m.empno(+) and d.deptno=e.deptno and e.sal between s.losal and s.hisal and m.sal between g.losal and g.hisal
/
deptno in(10,20)与dpetno=any(10,20)等价,>any(800,1500)数据大于最小值就符合要求，大于反之。null对它有影响、

分组函数：
avg(),sum(),min(),max()(null不在内)count()受null影响
         
分组:select 分组相关字段（和group by 后面的字段一样）|分组函数 from 表 where group by 分组字段s having 条件 order by 排序字段s

子查询：
sql中又有sql语句
select 字段|表达式 from 表|子查询 where 条件表达式|子查询

-------
day3：
增删改查DML
insert into 表名|（若干字段） values（每个字段给定的值）（not null字段必须给定）有非空约束一定要写出来  eg：insert into emp（empno，ename） values（110，‘lisi’） ，表名后面不写东西是默认所有字段。也等于后面写上写了所有字段。要按顺序写字段，不按的话，值的顺序要一样。
create table temp as select * from emp（备份表）
批量插入数据不用values  insert into temp select * from emp;
事务操作：commit提交（从上次事务结束代表本次事物的开始，执行commit或者rollback表示本次食物结束）将数据写入数据库（持久化）
          rollback回滚（将本次DML操作中更改的数据还原到上次事务）清楚回滚段中的数据
insert into 表名 select * from 表名（将查询出来的数据插入到新表中）
顺序，个数，类型都要对上

update 表名 set 字段1=，字段2=，字段3= where 条件（没有where表示更新所有记录中的几个字段）


delet只删除数据没有删除表
delete from 表名 where 条件（没有where表示删除表中所有数据，但保留表）
“回滚段”

DCL（控制操作）（：commit rollback grant授予权限 revoke收回权限 set

create user zhang identified by zhang；创建zhang用户，密码为zhang
drop user zhang （cascade） 删除用户zhang

grant create session to zhang 给权限zhang这个用户会话的权限（能力）
grant create synonym to zhang 给创建同义词的权限
给予操作权限（增删改查）
grant select on scott.emp to zhang 给权限zhang查看scott中的emp表

revoke select on scott.emp from zhang （收回zhang查看scott的emp表的权限）

角色：把若干权限绑定成角色，grant connect,resource to zhang

select table_name from dictionary 数据字典


DDL:(data definition language)数据定义语言，create,drop,alter,truncate
create table 表名（字段1 类型（长度）（约束）default,.....）默认提交

数据类型：
number() 后面不跟长度，默认是38位，1位参数表示整数int，2个参数表示小数float
vachar()动长 vachar2（）动长 char（）定长 默认255
date
blob 大的二进制对象4G
clob 大的文本对象4G

数据设计的三大范式：（无精确定义）
1、每个字段必须定单一，具有单一意义。
2、每个表中要有固定的一个属一非空且唯一（id）。
3、非主属性必须依赖于主属性，不能依赖于其他属性。
person
身份证号码：
名字：
性别：
年龄：
工作部门号：
部门名称：
地址：

约束：针对表中的字段进行定义
not null 非空约束
unique 唯一约束（可以添加空值）uk
check 检查约束ck
primary key主键约束，唯一且不为空  联合主键（某几个字段联合起来唯一且不为空）pk
foreign key 外键约束，由外键约束修饰的字段值是从主表中主键值引用过来fk

行级约束和表级约束

行级约束 条件直接在后面加 非空约束只能是行级约束，其他的都可以是表级约束
表级约束
create table
constraint person_id_pk primary key（id）
修改错误：先定位到行 c /旧串/新串
user_constriants用户约束表

事务操作：原子性（多个操作，将多个操作绑定为一个原子，同时成功同时失败）
DML操作中需要手动操作事务操作，如果事务太大，资源占用比较大，如果事务太小的话，I/O流操作频率比较高.
多个用户对同一张表进行操作的话，会对这张表加上一个表级共享锁，如果对某张表中的记录进行dml操作（除select），会对记录加上行级排他锁（对本条记录锁定）。

drop tabel 表名  将数据以及表结构删除掉
truncate table 表名 截取数据，表结构还在

truncate是ddl操作，ddl自带操作，自动提交，不可以rollback，删除大量数据，在回滚段中无记录，dml要手动提交，可以rollback（delete），在回滚段中有记录。

alter：对表，对约束
对表：
alter table 表名 add（字段名 类型（长度）） 增加字段
alter table 表名 modify（字段名 类型（长度）） 修改字段 是否是同种数据类型，是否长度变小
alter table 表名 rename column 字段 to 改后字段
alter table 表名 drop column 字段名
rename 旧表名 to 新表名  改表名
对约束：
添加约束
alter table 表名 add constraint 提示名 约束类型
alter table t_person add constraint t_person_pk primary key(id)；
alter table t_person add(age number(3) check(age>0))
删除约束
alter table 表名 drop 约束  alter table t_person primary key； 只有一种约束，唯一约束
alter table 表名 drop constrain 约束名字   删除自定义的约束
alter table t_person drop constraint t_person_uk；
alter table t_person disable primary key。  enable  使某些约束失效或生效，这个只对primary key有用
alter table 表名 disable（enable） 约束名； 禁用约束可快速插入数据
alter table t_person disable t_person_uk；
cascade 解除级联 alter table 表名 主表drop primary key cascade（级联删除约束 ）
foreign key（father） references person（id）on delete set null
联合主键 primary key（sid，cid）表级约束 联合起来唯一

foreign key 从主表中主键字段取值作为子表的外键字段
create table 表名 as 查询语句 （where 1=2只复制表结构）数据可以复制，约束不可以
create table child(cid number primary key, name varchar(2),father number references person(id))


序列：用来产生主键值
create sequence 序列名字【increment by n 增长值
start with n 开始值  默认1
maxvalue n
                        minvalue n  默认1
                        cycle |no cycle 是否会环
                         cache n 缓存】 一次性去几个
se_per.nextval下一个序列  select se_per.currval from dual;查看当前序列
drop sequence 序列名 删除序列

索引：准确快速定位记录（经常在查询中作为条件用的字段，且重复几率小）（ 主键默认加索引，避免全盘扫描、rowid物理位置
select table_name from dictionary where
create child_name_index on child(name) 为表child中name字段建立一个索引
index对空记录不建立索引
alter index child_name_index rebuild 重建索引

视图：查询的缩略图
create or replace view 视图名 as 查询语句 with read only 只读，不能进行插入操作|with check option 只能插入符合check的记录

grant create view to scott 授予创建视图的权限
创建视图：
create or replace view dept_view as select emp.ename,dept.deptno,dept.dname,dept.loc
from emp,dept
where emp.deptno=dept.deptno
在视图中插入数据时在原表中也会相应的插入记录。
drop view 视图名  删除视图


分页：
rownum（伪列）序号
select rownum,empno,ename,sal from(select * from emp order by sal desc) where rownum<=3（可以找出<=数字和=1的记录）
select a.ru,a.empno,a.ename,a.sal from
(select rownum ru ,empno,ename,sal from(
select * from emp order by sal desc)) a
where a.ru>3
/


数据导出，导入（对少量数据）
exp imp
create synonym 同义词 for 表名
drop synonym 同义词


create table person(
id number(5) not null,
name varchar(15),
age number(3),
sex varchar2(2) dufault 'm',
constraint person_id_pk primary key(id),
constraint person_name_uk unique(name),
constraint person_age_ck check(age>0),
constraint person_sex_ck check(sex in ('f','m')))
/
---------------------------------------------------------------------------