ORACLE笔记
**********************************************************************************************************************************
▲ORACLE是什么？
数据库产品
▲流行的数据库
Oracle Oracle
IBM db2
sybase ASE
Informxs
▲oracle 提供什么？
rdbms（关系型数据库管理系统）
中间件 Application Server
DT开发工具
ERP解决方案
▲关于oracle 版本
企业版 标准版 个人版
version 8-->8i-->9i-->10g
tarena目前使用的是oralce 9i 9201 版本（企业版）
i---internet g---gred
▲关系DB操作
投影（projection）
过滤（filter）
关联（join）
约束（constraint）
索引（index)
▲oracle 环境
what sqlplus
sqlplus是ORACLE提供的工具，是连接oracle数据库一种方式。
连接oracle：
wuhan%sqlplus 用户名/密码
wuhan%sqlplus sd0710/sd0710
SQL> --表示你已经与数据库建立连接
☆常见的问题
⑴使用自己的用户名登陆服务器无法运行sqlplus
wuhan%sqlplus -->command not fount
⑵不能使用VI编辑sql语句
造成的原因通常是由于你自己的环境变量设置问题，设置以后就可解决
$PATH-没有设置$ORACLE_HOME/bin
$EDITOR-没有指定
最简单的解决的方式是：
SQL> host
wuhan%cd
wuhan%cp ~openlab/.cshrc .
生效
wuhan%exit
SQL>
▲怎么知道跟哪个数据库连接?（一台机器可以有多个数据库）
通过环境变量ORACLE_SID指定的实例名
▲oracle中表
⑴user table
⑵system table (data dictinary)
》user table的DDL操作会触发system table的DML操作
▲你的第一条select 语句
》select table_name from user_tables;(当前用户下用那些表）
运行select的标志是在语句后加分号
重复运行最后一条语句
键入/回车
SQL> /--运行上一次执行的sql
desc user_tables;(查看当前用户下系统表的结构）
sqlplus命令:connect describe list column change 等等
⑴oracle 数据库特有的命令其他数据库没有这样的命令
⑵可以缩写取前四个字母 describe =>desc
⑶不以';'作为命令的结束
可以执行的命令：
sqlplus命令:
操作系统命令:（实际是一个子进程去执行使用！或host)
是sqlplus特有的命令
如果你在执行语句时报错怎么解决
select table_name from user_tables;
desc myTable
掌握了以上两条命令你将解决以下问题：
表名不存在
字段名不存在
利用错误号来查错误
！oerr ora 出错代码
例如：SQL>!oerr ora 942 (装完系统后会装一个oerr工具，用于通过错误号来查看错误的具体信息)
!--实现sqlplus 与shell的切换（实际上！是sqlplus的子进程）
host命令 同！使用
SQL>! 回车
shell>
shell>exit
回到sqlplus
掌握 s_emp，s_dep表结构
》show user 显示当前登陆的身份.
》set pause on
》set pause 'please input enter key'
》set pause off 分页显示.
》clear screen 清屏
oracle中默认日期和字符是左对齐，数字是右对齐
table or view does not exist ; 表或示图不存在
edit 命令用于自动打开vi修改刚修执行过的sql的命令。
修改方法二：
l 3 先定位到行
c /旧串/新串
保存刚才的sql语句： save 命令 第二次保存时要替换之前的角本 save 文件名 replace
把刚才保的sql重新放入 buffer中
spool 文件名
此命令会把所有的操作存在某个文件中去
spool off
减少硬分析规范sql语句的写法
练习1：查看s_emp表中员工的年工资
select first_name , salary*12 salary from s_emp;
select first_name , salary*12 "annual salary" from s_emp;
▲给列起别名的命令：
方式1字段后空格跟别名
方式2利用关键字 as
注：使用双引号将大小写敏感保持引号内容原样输出，如果不加双引号时，默认为大写
为 年薪起 'annual salary' 别名(带有空格或大小写敏感) ->"annual salary"
▲拼接操作：
select first_name||','||last_name from s_emp ;
select first_name||last_name "employees" from s_emp ;
oracle中表达字符串用单引号来表达：
select first_name||' '||last_name from s_emp;(在两个字段之间拼接一个空格)
查看当前用户所有的表：
练习2：在当前用户下所有表在表前有select * from字符串表名后加';'
例如显示：SELECT * FROM s_emp ;
SELECT * FROM s_dept ;
SELECT * FROM s_customer ;
》select 'select * from' || table_name ||';'
from user_tables;
动态的拼sql语句写入文件
spool test.sql
select 'select * from' || table_name ||';'
from user_tables;
spool off
注：spool命令用储存终端
动态生成脚本例子
shell>edit crtsql.sql
set head off--去除表头
set feed off -- 去除最后一行
set echo off -- 去除执行SQL的显示
spool test.sql
select 'select * from' || table_name ||';'
from user_tables;
spool off;
存盘
注:
运行脚本
方式1：SQL>@crtsql.sql
方式2：shell> sqlplus 用户名/密码 @srtsql.sql
练习3：(查出s_emp表中所有员工的一年的总收入)
select first_name , salary*12*( 1+nvl(commission_pct/100 , 0 ) ) " year salary " from s_emp;
不是所有的人都有提成，没有提成的员工commission_pct字段为空，对于null的理解，它即不为0也不为空是一个不确定的值，注意空值对order by 、单列函数的影响
nvl函数 将空值转为实际意义的值专用于处理空值的影响.
nvl函数实现重载支持date、char、number
练习4：列出公司所有部门（排重）
select distinc name
from s_dept;
练习5：列出不同的部门不同的职位（复合唯一）
select distinct dept_id,title
from s_emp;
▲column 定义格式化输出
SQL>column last_name
SQL>column last_name Heading 'Employee|Name' format a15;
SQL>column last_name clear;
SQL>column salary justify left format $99,999.00 ( 定义工资的显示形式 )
salary显示格式受本地环境变量影响
》setenv NLS_LANG AMERICAN_AMERICA.US7ASCII//English
》setenv NLS_LANG 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'//chinese
▲ORDER BY 排序 升序和降序 ASC 升序(默认) DESC 降序
select last_name AS employee from s_emp order by employee desc -跟别名
select id，last_name from s_emp order by 2 desc--跟字段号
order by 跟字段有空值按无穷大排序
先部门号升序，后工资降序
select * from s_emp order by dept_id , salary desc
关键字distinct也会触发排序操作。
▲过滤操作： where 子句
查看部门号为42的所有员工
select * from s_emp where dept_id=42;
查看工资高于1000的所有员工
select * from s_emp where salary>1000
找出名字为Ben员工的工资
select salary from s_emp where first_name='Ben'
在create table 存入数据字典的表名是大写的，查某个具体表名时，表名的字符串必须要为大写
select table_name from user_tables where table_name='S_EMP'; （若写s_emp查不出结果）
工资在1500－2500之间的员工
select last_name,salary from s_emp where salary between 1500 and 2000;
日期的默认的格式 DD-MON-RR(缺省使用单引号）
BETWEEN AND 在什么之间 NOT BETWEEN AND 注意区间：[ ]是一个闭区间
IN ( LIST) 另一种形式 =any
NOT IN (list) 空值会有影响 (等于list其中任何一个就行，为提高效率常把比例高的放在前面)<=>
另一种形式<>ALL
not in (1,3,null) --不会找到任何记录，not in 要与所有值比较然后得到结果，不能与null比较
LIKE 模糊配置 NOT LIKE 通配比较
IS NULL 是空
AND
OR
NOT
练习4:找出表名以S_开头的所有表
你可能这样写:
select table_name from user_tables where table_name like 's_%' --小写
SQL> no rows selected --Oracle在执行见表后自动将表明转换成大写
select table_name from user_tables where table_name like 'S_%'
一个表名为SS的表也被查找出来，显然不是我们要查找的记录，因为'_'在oracle中表示任意字符
对于一些特殊字符，要用到escape转义
select table_name from user_tables where table_name like 'S\_%' escape '\';
哪些人没有提成
select last_name ,commission_pct from s_emp where commission_pct is not null;
42部门工资大于1000员工
select first_name ,salary from s_emp
where dept_id=42 and salary >1000
▲单行函数：
☆dual 哑表dual--系统提供一张用于计算的表，一行一列
字符函数：
lower 转小写 select lower('HUGO') from dual;
upper 转大写 select upper('hugo') from dual;
initcap 首字符大写 select initcap('tarena') from dual;
concat 连接字符串 select concat(first_name , last_name) from s_emp;
substr 求子串 select substr('whsdasd0710' ,1,6) from dual; (取前六个字符) select substr('whsdasd0710',-2) from dual; (取后两个字符)
length 求字符长度 select length('tarena') from dual;
nvl 空值函数
select first_name , substr(first_name , -2 ) from s_emp; (查出s_emp表中所有用户名字的最后两个字符)
练习5： 找出不确定大小写名字为ben的员工
select first_name , salary from s_emp where lower(first_name)='ben';-不能使用index
优化提示：
如果数据在表中存的很整齐不要使用转换函数，where first_name='Ben' 效率高，能用到index
在DML语句中使用转换函数使数据格式整齐，便于提高查询效率
显示员工first_name后两个字母
select first_name ,substr(first_name,-2) from s_emp;
select first_name ,substr(length(first_name)-1,2) from s_emp;
▲数值函数：
round 函数 select round(45.935, 2) from dual; 不带参数时默认为0位小数(四舍五入）
trunc 函数 select trunc(45.995, 1) from dual; （截取）
日期函数：DD-MON-RR （日期格式敏感）
日期信息:世纪(yyyy隐含世纪信息）、年、月、日、时、分、秒
sysdate 返回当前系统时间 select sysdate from dual;
更改当前会话的设置格式：
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
显示昨天、今天、明天
select sysdate-1, sysdate+1, sysdate , sysdate+1 from dual; 注意单位是以天为单位
十分钟后时间
select sysdate+10/24/60 from dual;
MONTHS_BETWEEN (DATE1 , DATE2 ) 求两个日期之前相差的月数
add_months(date , 4 ) 在 date上再添加4个月
select next_day(sysdate,'FRIDAY') from dual ; 求这个日期的下一个FRIDAY
last_day 求月的最后一天
round 函数: select round(sysdate, 'MONTH') from dual; 参数可以为： MONTH YEAR(看上半年还是下半年)
日期的运算：
从现在起第一个周五的日期
select next_day(sysdate,'FRIDAY') from dual;
当前日期当月最后一天
select trunc(last_day(sysdate)+1) from dual;
round()不支持隐式数据类型转换
round(10-OCT-06)--不支持
sysdate='10-OCT-06'
round(sysdate)
下个月1号的日期
select add_months(trunc(sysdate, 'MONTH'), 1 ) from dual ;
select trunc(last_day(sysdate)+1,'month') from dual;
关于日期的两种形式：
DD-MON-RR(default)
转换函数：
to_char
从数字转化为char
从日期转化为char to_char(date, 'fmt' )
select to_char(sysdate, 'yyyy mm dd hh24:mi:ss') from dual;
select to_char(sysdate ,'YEAR MONTH ' from dual;
to_date
字符转日期 select to_date('2000 11 20', 'yyyy mm dd ') from dual;
select round(to_date('10-OCT-06' ,'dd-mon-RR') ) from dual;
to_number
字符转数字
select to_number('10') from dual
▲读日期
显示42部门员工入职的时间 日期的格式 yyyy mm dd hh24:mi:ss
select first_name ,to_char(start_date,'yyyy mm dd hh24:mi:ss') from s_emp where dept_id=42;
五月份入职的员工
select first_name ,start_date from s_emp where to_char(start_date,'mm')='05';
select first_name ,start_date from s_emp where to_char(start_date,'fmmm')='5';--fm压缩前导0
▲写日期
怎么插一个日期到表里
方式1：to_date()
方式2：缺省日期格式
create table test_hugo(c1 date);--建表
insert into values('10-OCT-06');--输入入日期值（默认日期格式）
--默认的日期格式系统会补世纪信息（根据但前时间和公式算出来）和时分秒（00：00：00午夜）
insert into test_hugo values(to_date('2007 08 02 01:01:01','yyyy mm dd hh24:mi:ss')) ;(指定日期格式）
*****************************************************************************************************************
*********************************
** oracle 学习笔记第二天 **
** hugo **
***********************************
触发排序的操作：distinct 、order by、group by
为什么有关联？
任何一张表都不能满足需求，所以要用多张表
关联类型：
内联接：inner join严格匹配
外连接：outter join一个都不能少，显示不能匹配的记录,模拟空记录
▲等值连接（内联接）：
首先了解s_emp ,s_dept结构
》查看员工的姓名和员工部门名称:(要考虑到表中实际数据中空值的影响）
select first_name , name from s_emp, s_dept 此语句会产生300条记录，笛卡儿积
s_emp (25) * s_dept(12) = 300 两表没有任何关联时会产生笛卡儿积
只有部门号相等的记录才有意义
select first_name , name from s_emp, s_dept where s_emp.dept_id=s_dept.id;
作表关联时经常为表起别名，可以省去输入很长的表名
select first_name , name from s_emp e, s_dept d where s_emp e.dept_id=d.id;
》每个员工所在的部门和部门所在的地区
select first_name , name from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;
Ben在什么部门上班
select e.first_name , d.name ,r.name
from s_emp e, s_dept d, s_region r
where e.dept_id=d.id
and d.region_id=r.id
and e.first_name='Ben';
Asia 地区有哪些部门
非等值连接（内联接）：
》找出员工名称和员工的工资级别
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
》king的工资是几级
select a.ename , a.sal, b.grade from emp a , salgrade b
where a.sal between b.losal and b.hisal
and a.ename='KING';
▲自连接：（一张表字段有关系必然导致表记录间有关系，给一张表起不同的别名将表的字段关系转换成
两个表记录的关系）
select first_name , manager_id from s_emp;
查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select e.first_name "Employee" , m.first_name "Manager"
from s_emp e , s_emp m
where e.manager_id=m.id;
▲谁是总经理(没有manager_id)
select first_name from s_emp
where id in (select id from s_emp
where manager_id is null);
-------------规范的写法(外连接＋is null)-----------------
select e.first_name "Employee", m.first_name "Manager" from s_emp e , s_emp m where e.manager_id=m.id(+)
and e.manager_id is null;
外连接：(（+）的一方会模拟空记录配置另一方，表示表达式另一端全部显示)这就称为外连接,一个记录都不能少
select e.first_name "Employee" , m.first_name "Manager"
from s_emp e , s_emp m where e.manager_id=m.id(+);
显示e.manager_id这张表
》统计公司的部门：
select distinct deptno from emp ;
select deptno from dept ;
★解决XX中没有XX的标准方式：外连接加非空条件
》找出没有员工的部门：
select e.deptno,d.deptno ,d.dname
from emp e,dept d
where d.deptno=e.deptno(+) and e.deptno is null;
detail信息
summary信息
组函数（group function)：
group by 分组子句 对分组后的子句进行过滤还可以用having 条件 对分组后的条件进行过滤 where 是对记录进行过滤
avg(distinct | all )求平均值
count(distinct | all )统计
max(distinct | all ） 求最大值
min(distinct | all )求最小值
sum(distinct | all ) 求和
(★所有组函数会忽略空值 , avg sum只能作用于数字类型）
》求有提成员工的提成的平均值；
select avg(commission_pct) from s_emp;//所有有提成人的平均值（忽略空值）
select avg(nvl(commission_pct ,0 ) ) from s_emp;
select sum(commission_pct)/count(*) from s_emp;
》有多少人有提成：
select count( commission_pct ) from s_emp ;
count(*) --用于统计记录数：
员工分部在多少个不同的部门：count 默认为作all的动作
select count(dept_id) from s_emp;//处理所有非空值
select count(distinct dept_id) from s_emp;//处理排重的非空值
求各个部门的平均工资：group by 子句也会触发排序
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;
select dept_id , avg(salary) aa from s_emp group by dept_id ;
注意：group by 子句后面跟有条件只能是查询的结果中的字段,所以我们会人为在结果要加入一些group by 要用的字段,sql语句中出现group by select后面只能跟group by出现的字段
select region_id , count(*) from s_dept 此句会有错
select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确）
》求各个部门的平均工资
select dept_id,avg(salary) from s_emp group by dept_id;
》求各个部门不同工种的平均工资（列出部门号）：
select dept_id , title, avg(salary) from s_emp group by dept_id , title ;
☆group by跟的字段越多粒度越细，效率越慢
》哪些部门的平均工资比2000高:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;
》42部门的平均工资:
select dept_id,avg（salary) from s_emp where dept_id=42 group by dept_id;
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id=42;
》除了42部门以外的部门的平均工资:
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;(此种sql效率要高，先过滤再计算）
▲where 与 having区别
where 过滤的是记录，跟单行函数。
having 过滤的是分组以后的结果，跟组函数不能是单行函数。
》求各个部门的平均工资:(显示部门名称）
select d.name "dept",r.name "area",avg(salary) "avgSalary"
from s_emp e,s_dept d ,s_region r
where e.dept_id=d.id
and d.region_id=r.id
group by d.name,r.name
//为了过 oracle sql 语法关而写max(d.name) ***
//dept_id 定下来后，s_dept.name ,s_region.name 只有一个
select max(d.name) , max(r.name) , avg(e.salary)
from s_emp e, s_dept d , s_region r
where e.dept_id = d.id
and d.region_id=r.id
group by e.dept_id ;
▲关于子查询: Subqueries
找出所有员工中，工资最低的那个员工：( 利用子查询 ）
select first_name, salary from s_emp where salary = ( select min(salary) from s_emp);//where不能使用组函数将组函数作为结果返回
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;
子查询运行的顺序： 先运行子查询再运行主查询 子查询一般出现在运算符的右边
单值运算符：运算后面只能跟一个值
多值运算符：可以对两个以上的值进行操作
》谁跟Smith干一样的活：
select last_name , title from s_emp where title =( select title from s_emp where last_name='Smith' )
and last_name <> 'Smith' ;
//上面的写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题，当有两个人叫Smith并且做相同的工作，返回多条记录用等号，会漏掉记录。
★在执行模拟的数据不报错不一定sql正确，客户的数据更真实
//这种写法才考虑的比较全面
select last_name , title from s_emp where title in ( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
使用子查询时应注意： 单行子查询返回多个结果时会有错误 single-row subquery returns more than one value
select * from s_emp where salary < ( select avg(salary) from s_emp) ;
哪些部门的平均工资比32部门的平均工资要低：
select dept_id ,avg(salary)
from s_emp
group by dept_id
having avg(salary)> (select avg(salary)
from s_emp
where dept_id=32)
查出哪些员工的工资比平均工资低：
第一步先查出各个部门的平均工资：
select min(avg(salary ) ) from s_emp group by dept_id;
第二步再查出哪个部门的工资是最低的:
select dept_id, avg(salary) from s_emp group by dept_id having avg(salary) = (select min(avg(salary) ) from s_emp group by dept_id ) ;
★结论：
⑴能用in实现都可以用内联接实现
⑵能用not in实现的可以用外连接
性能提示：使用not in效率差
》哪些人是领导：
select first_name ,id from s_emp where id in (select manager_id from s_emp);
》哪些人是普通员工：
----子查询----
select * from s_emp where id not in ( select manager_id from s_emp
where manager_id is not null);
not in 有空值找不到，加where manager_id is not null
》哪个部门里没有员工：
select deptno from dept where deptno not in ( select deptno from emp where deptno is not null);
》哪个部门平均工资最低
select dept_id ,avg(salary)
from s_emp
group by dept_id
having avg(salary)=(select min(avg(salary))
from s_emp
group by dept_id);
★》谁的工资比本部门的平均工资高(from 跟子查询）
-------规范写法------
select first_name ,salary
from s_emp e,
(select dept_id ,avg(salary) avgsal
from s_emp
group by dept_id) a
where e.dept_id = a.dept_id
and e.salary > a.avgsal;
E--R图 实体关系图entity relation
开发流程先进行需求分析，进行系统设计，建表，再进行开发编码,测试最终产品上线试运行。
把软件设计模型转化为数据中的表，设计时要考虑性能的设计
第一范式：一个属性描述一件事情，每个column 描述事情唯一，因为没有对每个column的值作限制，所以有重复记录
第二范式：至少有一个column取值唯一(UID),记录可区分,部分记录存在冗余，存在非主属性传递依赖。
第三范式：打破非主属性的依赖关系，非主属性严格依赖主属性，降低数据存贮压力，将数据分成多张表。
约束：
数据库提供完整性约束，防止葬数据保证数据的一致性
实体完整性--primary key (pk) 主键约束 不允许有重复和空值(唯一且非空）
引用完整性--foregin key (fk) 外键约束 两张表parent table child table
unique key (uk) 唯一可以为空
not null 非空
先插主表后插子表
先删子表在删父表
数据库设计时的注意：
索引： 为了提高效率而设计的一种与业务无关的
考虑表点用的物理空间：
考虑表之间的关系：
一对多关系： 利用FK+PK实现，多的一方引用外键
一对一关系： 可以利用FK+UK实现，
多对多关系： 通过中间增加一个附加表来实现，附加表利用联合主键来实现，联合起来的主键唯一。
DDL语句：数据库定义语句：
table (表）
view(示图）
sequence(序列号）
index(索引）
创建表语句：
create table [schema].表名 ( 字段名， 字段类型 约束条件); schema 默认就是当前用户,严格来访问表名完整的写法是schema.tablename
数据类型：
表名的命令规则： 首字母为字母，不得超过30个字符
char(size) 定长 不管是否达到最大宽度，都会点最大的宽度。2000
varchar2(size) 可变长 按实际的字节占用空间4000
number 所有的数字类型都称为number
number(n, m ) n------n位宽度 m-----小数点后的宽度
number(2,4)小数点后4 位，有效位2位 values(0.0099) 这样可以 values(0.01)这样出错
LONG 2GB 大文本一个表最我只允许定义一个LONG类型(不建议使用）
CLOB 大对象形式存放(在表里只存一个指针)
BLOB 存二进制大对象(声音，图像之类）
使用缺省值：
default 作用演示：
create table test(c1 number default 10, c2 number);
约束的演示：
主键约束的定义：
create table test(c number primary key ); 列级约束
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
列级约束定义不了联合主健
外键约束的定义：(先定义父表，再定义子表）引用完整性
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
on delete cascade (及联删除，删除父表时子表也跟着删除）
on delete set null (及联删除父表时子表中引用的字段为null）
uk与pk区别：uk允许为空
不给约束起名字时，系统给约束起名时的规律为：数据库用户名_数字(约束名也不能重名）
定义一个约束的两种形式：
列级约束 表级约束
非空约束：
not null (利用desc可能看到）primary key 自动具有非空约束的特点
primary key约束：
主键约束的定义：
第一种定义形式：
create table test(c number primary key ); 列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
foregin key (fk) 外键约束:
(先定义父表，再定义子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
check 约束：
create table test(c1 number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .
怎么创建一个脚本文件： xxx.sql结尾
执行角本的方法：
在sqlplus环境中执行：@filename.sql
在shell环境中执行： sqlplus sd0706/sd0706 @filename.sql
▲伪列
oracle提供给结果自动编号的机制
select rownum,fisrt_name from s_emp where rownum=1;
select rownum,fisrt_name from s_emp where rownum<=3;
rownum不能等于n，rownum between m and n不可以（rownum>=m不能实现，仅满足rownum = 1或者rownum<=n ）
select rownum,fisrt_name from s_emp where rownum=2;--报错
要查询 rownum between m and n条件，先写子查询将rownum变成列，然后rownum between m and n
查询s_emp 第5条至第10条记录
select first_name from (select rownum r , first_name from s_emp ) a where a.r between 5 and 10;
select id,first_name from (select rownum r , a.* from (select * from s_emp) a ) where r between 5 and 10;
》工资最高的前5个人
select rownum,first_name,salary from
(select first_name ,salary from s_emp order by salary desc ) a
where rownum <=5;
*****************************************************************************************************************
*************************************
** oracle 学习笔记第三天 **
** hugo **
*************************************
E--R图 实体关系图entity relation
开发流程先进行需求分析，进行系统设计，建表，再进行开发编码,测试最终产品上线试运行。
把软件设计模型转化为数据中的表，设计时要考虑性能的设计
第一范式：一个属性描述一件事情，每个column 描述事情唯一，因为没有对每个column的值作限制，所以有重复记录
第二范式：至少有一个column取值唯一(UID),记录可区分,部分记录存在冗余，存在非主属性传递依赖。
第三范式：打破非主属性的依赖关系，非主属性严格依赖主属性，降低数据存贮压力，将数据分成多张表。
约束：
数据库提供完整性约束，防止葬数据保证数据的一致性
实体完整性--primary key (pk) 主键约束 不允许有重复和空值(唯一且非空）
引用完整性--foregin key (fk) 外键约束 两张表parent table child table
unique key (uk) 唯一可以为空
not null 非空
先插主表后插子表
先删子表在删父表
数据库设计时的注意：
索引： 为了提高效率而设计的一种与业务无关的
考虑表点用的物理空间：
考虑表之间的关系：
一对多关系： 利用FK+PK实现，多的一方引用外键
一对一关系： 可以利用FK+UK实现，
多对多关系： 通过中间增加一个附加表来实现，附加表利用联合主键来实现，联合起来的主键唯一。
DDL语句：数据库定义语句：
table (表）
view(示图）
sequence(序列号）
index(索引）
创建表语句：
create table [schema].表名 ( 字段名， 字段类型 约束条件); schema 默认就是当前用户,严格来访问表名完整的写法是schema.tablename
数据类型：
表名的命令规则： 首字母为字母，不得超过30个字符
char(size) 定长 不管是否达到最大宽度，都会点最大的宽度。2000
varchar2(size) 可变长 按实际的字节占用空间4000
number 所有的数字类型都称为number
number(n, m ) n------n位宽度 m-----小数点后的宽度
number(2,4)小数点后4 位，有效位2位 values(0.0099) 这样可以 values(0.01)这样出错
LONG 2GB 大文本一个表最我只允许定义一个LONG类型(不建议使用）
CLOB 大对象形式存放(在表里只存一个指针)
BLOB 存二进制大对象(声音，图像之类）
使用缺省值：
default 作用演示：
create table test(c1 number default 10, c2 number);
约束的演示：
主键约束的定义：
create table test(c number primary key ); 列级约束
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
列级约束定义不了联合主健
外键约束的定义：(先定义父表，再定义子表）引用完整性
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
on delete cascade (及联删除，删除父表时子表也跟着删除）
on delete set null (及联删除父表时子表中引用的字段为null）
uk与pk区别：uk允许为空
不给约束起名字时，系统给约束起名时的规律为：数据库用户名_数字(约束名也不能重名）
定义一个约束的两种形式：
列级约束 表级约束
非空约束：
not null (利用desc可能看到）primary key 自动具有非空约束的特点
primary key约束：
主键约束的定义：
第一种定义形式：
create table test(c number primary key ); 列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
foreign key (fk) 外键约束:
(先定义父表，再定义子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
check 约束：
create table test(c1 number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .
怎么创建一个脚本文件： xxx.sql结尾
执行角本的方法：
在sqlplus环境中执行：@filename.sql
在shell环境中执行： sqlplus sd0704/sd0704 @filename.sql
=========================
创建表的语法：
create table 表名 ( 字段名 字段类型 约束类型（可选））；
利用已知表建一张新表：注会把非空约束带过来，其它约束要自己添加
create table s_emp_42 as select * from s_emp where dept_id = 42;
只取要表结构，不想要表中数据的建表方式：
create table s_emp_copy as select * from s_emp where 1=2;
(这是一个小技巧,在JDBC的学习中会用到 where 1=1 的形式,注意体会）
查看一张表的约束：( 查数据字典示图)
desc user_constraints;(这个数据字典中会查到相应的信息）
select constraint_name, constraint_type from user_constraints where table_name='S_EMP';
P pk
R fk
C check
U UK
V 这种只定义在示图中(with check option 相当于组示图加了一个约束）
O 也是出现在示图中
非空约束和CHECK都是用C来表示
查看字段约束的方法：
desc user_cons_columns;
select column_name, position from user_cons_columns where constraint_name='S_EMP_ID_PK' ;
position 的含义：联合主键，约束名一样。
user_constraints user_cons_columns 两张表的约束名相等，表名相等，两张表一关联就可以查出所需的信息。
select constraint_name , r_constraint_name from user_constraints where constraint_type='R' and table_name='S_EMP' ;
数据库建立时，数据字典就会建好。
user_constraints; 自己拥有的
all_constraints; 你自己拥有的加上你可以访问的
dba_constraints 所有的
查看当前数据库数据字典的字典（这个视图很重要）
desc dict;
select table_name from dict where table_name like '%cons%';
示图：
user_objects; user_tables;
select distinct object_type from user_objects;
注意 insert into 后面可以跟子查询
insert into s_emp_42 select * from s_emp where dept_id =42;
UPDATE 修改字段值：
update s_emp set dept_id =10 where id =2 ;
update s_emp set commission_pct =10 ; 没有where条件时说明是改表中所有的值.
注意：如有外键引用时常会出现外键引用值没有找到等错误？
delete 删除记录命令语法：
delete 操作可恢复
delete 操作后空间一直被占用
delete 删除大表效率低
delete from s_emp where dept_id=42;
delete form s_emp ; 没有where条件时说明删除表中所有的值
注意：如有外键引用时，删除一张表时常会出现不能删除的情况，
原因一 是因为此时正在有人操作表中记录
原因二 此表有其他的表引用，没能设及联删除：
delete 删除一张大表时空间不释放，非常慢是因为占用大量的系统资源，支持回退操作，空间还被这张表占用着。
truncate table 表名 (删除表中记录时释放表空间)
介绍事务的概念：一起成功一起失败
commit 提交，此时说明前面所有语句都成功执行，DDLorDCL命令隐含自动提交
rollback 回退操作，此时会恢复至上一次提交时的状态。
savepoint 设置保存点
演示并发操作：－－提交很重要
开三个session
session1：表test插2调记录，改变一条记录没有提交称为active tranction
session2：看不到session1更改的记录，此时update表test会进入等待
session3：drop test 报错 resource busy an acquire with NOWAIT specified
有DML正在操作这张表
DML 操作时oracle会给表加：（锁的粒度行级－并发度高）sybase以前是页级锁在同一配置下加锁，
锁的时间：事务有多长是就会锁都长时间
×××写程序时一定要commit不然锁不释放
表级共享锁： 对于操作一张表中的不同记录时，互不影响（你加我也能加）
行级排它锁：对于一行记录，oracle 会只允许只有一个用户对它在同一时间进行修改操作
wait() 等到行级锁被释放，才进行数据操作
drop一张表时也会对表加锁，DDL排它锁,所以在删除一张表时如果当前还有用户操作表时不能删除表
alter table 命令用于修改表的结构(这些命令不会经常用)：
增加约束：
alter table 表名 add constraint 约束名 primary key (字段）;
解除约束：(删除约束)
alter table 表名 drop primary key（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
alter table father drop primary key cascade ; (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
alter table 表名 drop constraint 约束名;
(怎样取一个约束名：1、人为的违反约束规定根据错误信息获取!
2、查询示图获取约束名!)
alter table 表名 disable from primary key ; (相当于把一个表的主键禁用)
alter table 表名 enable primary key ;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）
增加字段：
alter table 表名 add(字段字，字段类型)
删除字段：
alter table 表名 drop(字段)
alter table 表名 drop column 字段 ; (8i 以后才支持）
给列改名:920才支持
alter table 表名 rename column 旧字段名 to 新字段名;
修改字段
(此时应注意的问题，更改时要看具体值情况之间的转达换， 改为字符类型时，必须要为空)
alter table 表名 modify( 字段，类型)
更改表中的字段：
update 表名 set 字段 ＝ 值 where 条件
更改表名
rename 旧表名 to 新表名 ；
删除表：
trucate table 表名:(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表
关于oralce中产生序列(sequence):利用sequence生成id会产生gap（不连续），可能有rollback，down机，并发使用
create sequence 序列名alter system flush shared_pool;
(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值）
sequence 的参数：
increment by n 起始值, start with n 递增量, maxvalue n 最大值, minvalue n 最小值，cycle | no cycle 轮回， cache n 绶存(第一次取时会一次取多少个id存起来）
查看 sequence 示图：
desc user_sequences ;
select sequence_name , cache_size , last_number from user_sequences where sequence_name like 's_';
select 序列名.currval from dual 查看当前的序列数
select 序列名.nextval from dual 查看下一个序列数，它会自动给当前的序列加１
为列：nextval currval
(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)
清空当前会话的内存：
定义SEQUENCE定义cache或nocache的影响
cache从内存中取，减少对系统表的使用，提高并发度
nocache要通过nextval看下一个值
alter system flush shared_pool;（执行此命令要有DBA权限，一般用户执行出错）
flush shared_pool定义cache的sequence会将cache中值清空
修改序列：(此命令不常用，只需了解就行不必深究）
alter sequence 序列名 修改项；
删除序列sequence
drop sequence 序列名;
创建示图： creating views（属于了解知识）
desc user_views;
select text from user_views where view_name='TEST1_V1' ;
示图就相当于一条select 语句,定义了一个示图就是定义了一个sql语句,示图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle 8i 以后的新示图）MV 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题， 主要应用在数据仓库中用要用于聚合表）
使用示图的好处：限制数据访问权限.
如何创建一个示图：
建议建立view时使用create or replace如果使用create，view存在会先删除后创建
create or replace views test_vi as select * from test1 where c1=1;
此时往表test1（base table 基表）中插入数据时：表中没能变化，示图中的数据发生改变
从示图中插数据时相对应的表会发生改变:
往示图中插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创建时的select语句。
简单示图：能进行DML操作。
复杂示图：来源于多张表，不能执行DML操作。
物化视图：用于数据仓库，给领导看，mv查询速度快，但基表数据无法更新，存在刷新问题
关于rownum：内嵌视图
rownum 有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。rownum常用于分页显示。
练习：查询出第5条数据和第10条数据之间：将前10条选出来在where5－10
select first_name , rnum from ( select rownum rnum , first_name from s_emp where rownum <=10 ) where rnum between 5 and 10;
练习：哪些员工的工资比本部门的平均工资高？
select first_name , salary , avgsal from s_emp e , ( select dept_id , avg (salary ) avgsal from s_emp group by dept_id ) a where e.dept_id =a.dept_id and e.salary > a.avgsal;
另一种写法效率低有多少员工就计算多少此平均值
select first_name,salary
from s_emp o
where salary > (select avg(salary) from s_emp e
where e.dept_id = o.dept_id)
在示图上加一个 with check option 就相当于给示图加上了约束
create view test_v as select * from test where c =1 with check option ;
同义词:相当于别名的作用(***只需了解***）系统自建的同义词： user_tables
create synonym asd_s_emp for asd_0607.s_emp ;
目的就是为了给asd_0607_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;
create public synonym p_s_emp fro asd_0607.s_emp; 创建公共的同义词，但是要权限.
删除同义词：
drop synonym 同义词名称
创建索引： Creating indexes(概念很重要对系统的性能影响非常大）
建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。truncate 表时索引结构在，但是数据不存在。
full table scan 全表扫描 --找多的记录
用索引就是为了快速定位数据：(理解时就以字典的目录为例）--对于数据量大的表，找少的记录适合用索引
查看表的rowid：告诉我们表的物理位置
select rowid , first_name from s_emp;
rowid 定义的信息有： object block table
每条记录都有自己的rowid ，定位记录最快的方式使用rowid，index使用rowid定位
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,对于表大情况才能现出优势这就是我们建索引的最终目的，
创建一个索引：
create index 索引名 on 表名 ( 字段名）;
create index testindex on test(c1, c2);
哪些字段应该建索引：
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率最高.
什么时候建联合索引
先解决什么时候建索引付出空间、维护的代价。
1，这个字段经常出现在where里的字段
2，经常用于表连接的字段
索引不是目的是手段
索引不包含空值
替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间
set verify on
set verify off;
相当于开关变量，用于控制是否显示新旧的sql语句
select id ,last_name ,salary from s_emp where title='&job_title';
更改交互的提示信息：
accept p_dname prompt ' 提示信息';
定义变量：
define p_dname='abc';
DDL(structure)
create
alter
truc
drop
DML(row)
transaction
commit
rollback
savepoint
insert
update
delete
querry(select)
select rowid from test;
rowid(从表中查除记录最快方式）
object-id file-id block row-id
oracle函数大全
SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
3.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
---------
9
6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞 3 北京市海锭区 6 9999.99 7
7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',10,’*’,17,’*’
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s' from 'string')
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0
30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2008-10-30 2008-10-31
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-10月 -08
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL>select
months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
MON_BETW
---------
-60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2008-10-30 17:34:25 2008-10-31 00:34:25
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
30-10-2008 星期四
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
to_char (trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
HH HHMM
------------------- -------------------
2008-10-30 17:00:00 10/30/2008 17:37:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2008/10/30 17:40:38
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('高') from dual;
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;
YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,'none',
3 2,'insert',
4 3,
5 'select',
6 6,'update',
7 7,'delete',
8 8,'drop',
9 'other') cmd from v$session where type!='background';
SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GREA
--
AC
SQL> select greatest('啊','安','天') from dual;
GREATE
--
安
56.LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"sd0710"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25
58.USER
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
sd0710
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
1149
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;
USERENV('TERMINA’)
----------------
COMPUTER
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 sd0710
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from sd0710.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from sd0710.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from sd0710.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400
66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950
**********************************************************************************************************************************
▲ORACLE是什么？
数据库产品
▲流行的数据库
Oracle Oracle
IBM db2
sybase ASE
Informxs
▲oracle 提供什么？
rdbms（关系型数据库管理系统）
中间件 Application Server
DT开发工具
ERP解决方案
▲关于oracle 版本
企业版 标准版 个人版
version 8-->8i-->9i-->10g
tarena目前使用的是oralce 9i 9201 版本（企业版）
i---internet g---gred
▲关系DB操作
投影（projection）
过滤（filter）
关联（join）
约束（constraint）
索引（index)
▲oracle 环境
what sqlplus
sqlplus是ORACLE提供的工具，是连接oracle数据库一种方式。
连接oracle：
wuhan%sqlplus 用户名/密码
wuhan%sqlplus sd0710/sd0710
SQL> --表示你已经与数据库建立连接
☆常见的问题
⑴使用自己的用户名登陆服务器无法运行sqlplus
wuhan%sqlplus -->command not fount
⑵不能使用VI编辑sql语句
造成的原因通常是由于你自己的环境变量设置问题，设置以后就可解决
$PATH-没有设置$ORACLE_HOME/bin
$EDITOR-没有指定
最简单的解决的方式是：
SQL> host
wuhan%cd
wuhan%cp ~openlab/.cshrc .
生效
wuhan%exit
SQL>
▲怎么知道跟哪个数据库连接?（一台机器可以有多个数据库）
通过环境变量ORACLE_SID指定的实例名
▲oracle中表
⑴user table
⑵system table (data dictinary)
》user table的DDL操作会触发system table的DML操作
▲你的第一条select 语句
》select table_name from user_tables;(当前用户下用那些表）
运行select的标志是在语句后加分号
重复运行最后一条语句
键入/回车
SQL> /--运行上一次执行的sql
desc user_tables;(查看当前用户下系统表的结构）
sqlplus命令:connect describe list column change 等等
⑴oracle 数据库特有的命令其他数据库没有这样的命令
⑵可以缩写取前四个字母 describe =>desc
⑶不以';'作为命令的结束
可以执行的命令：
sqlplus命令:
操作系统命令:（实际是一个子进程去执行使用！或host)
是sqlplus特有的命令
如果你在执行语句时报错怎么解决
select table_name from user_tables;
desc myTable
掌握了以上两条命令你将解决以下问题：
表名不存在
字段名不存在
利用错误号来查错误
！oerr ora 出错代码
例如：SQL>!oerr ora 942 (装完系统后会装一个oerr工具，用于通过错误号来查看错误的具体信息)
!--实现sqlplus 与shell的切换（实际上！是sqlplus的子进程）
host命令 同！使用
SQL>! 回车
shell>
shell>exit
回到sqlplus
掌握 s_emp，s_dep表结构
》show user 显示当前登陆的身份.
》set pause on
》set pause 'please input enter key'
》set pause off 分页显示.
》clear screen 清屏
oracle中默认日期和字符是左对齐，数字是右对齐
table or view does not exist ; 表或示图不存在
edit 命令用于自动打开vi修改刚修执行过的sql的命令。
修改方法二：
l 3 先定位到行
c /旧串/新串
保存刚才的sql语句： save 命令 第二次保存时要替换之前的角本 save 文件名 replace
把刚才保的sql重新放入 buffer中
spool 文件名
此命令会把所有的操作存在某个文件中去
spool off
减少硬分析规范sql语句的写法
练习1：查看s_emp表中员工的年工资
select first_name , salary*12 salary from s_emp;
select first_name , salary*12 "annual salary" from s_emp;
▲给列起别名的命令：
方式1字段后空格跟别名
方式2利用关键字 as
注：使用双引号将大小写敏感保持引号内容原样输出，如果不加双引号时，默认为大写
为 年薪起 'annual salary' 别名(带有空格或大小写敏感) ->"annual salary"
▲拼接操作：
select first_name||','||last_name from s_emp ;
select first_name||last_name "employees" from s_emp ;
oracle中表达字符串用单引号来表达：
select first_name||' '||last_name from s_emp;(在两个字段之间拼接一个空格)
查看当前用户所有的表：
练习2：在当前用户下所有表在表前有select * from字符串表名后加';'
例如显示：SELECT * FROM s_emp ;
SELECT * FROM s_dept ;
SELECT * FROM s_customer ;
》select 'select * from' || table_name ||';'
from user_tables;
动态的拼sql语句写入文件
spool test.sql
select 'select * from' || table_name ||';'
from user_tables;
spool off
注：spool命令用储存终端
动态生成脚本例子
shell>edit crtsql.sql
set head off--去除表头
set feed off -- 去除最后一行
set echo off -- 去除执行SQL的显示
spool test.sql
select 'select * from' || table_name ||';'
from user_tables;
spool off;
存盘
注:
运行脚本
方式1：SQL>@crtsql.sql
方式2：shell> sqlplus 用户名/密码 @srtsql.sql
练习3：(查出s_emp表中所有员工的一年的总收入)
select first_name , salary*12*( 1+nvl(commission_pct/100 , 0 ) ) " year salary " from s_emp;
不是所有的人都有提成，没有提成的员工commission_pct字段为空，对于null的理解，它即不为0也不为空是一个不确定的值，注意空值对order by 、单列函数的影响
nvl函数 将空值转为实际意义的值专用于处理空值的影响.
nvl函数实现重载支持date、char、number
练习4：列出公司所有部门（排重）
select distinc name
from s_dept;
练习5：列出不同的部门不同的职位（复合唯一）
select distinct dept_id,title
from s_emp;
▲column 定义格式化输出
SQL>column last_name
SQL>column last_name Heading 'Employee|Name' format a15;
SQL>column last_name clear;
SQL>column salary justify left format $99,999.00 ( 定义工资的显示形式 )
salary显示格式受本地环境变量影响
》setenv NLS_LANG AMERICAN_AMERICA.US7ASCII//English
》setenv NLS_LANG 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'//chinese
▲ORDER BY 排序 升序和降序 ASC 升序(默认) DESC 降序
select last_name AS employee from s_emp order by employee desc -跟别名
select id，last_name from s_emp order by 2 desc--跟字段号
order by 跟字段有空值按无穷大排序
先部门号升序，后工资降序
select * from s_emp order by dept_id , salary desc
关键字distinct也会触发排序操作。
▲过滤操作： where 子句
查看部门号为42的所有员工
select * from s_emp where dept_id=42;
查看工资高于1000的所有员工
select * from s_emp where salary>1000
找出名字为Ben员工的工资
select salary from s_emp where first_name='Ben'
在create table 存入数据字典的表名是大写的，查某个具体表名时，表名的字符串必须要为大写
select table_name from user_tables where table_name='S_EMP'; （若写s_emp查不出结果）
工资在1500－2500之间的员工
select last_name,salary from s_emp where salary between 1500 and 2000;
日期的默认的格式 DD-MON-RR(缺省使用单引号）
BETWEEN AND 在什么之间 NOT BETWEEN AND 注意区间：[ ]是一个闭区间
IN ( LIST) 另一种形式 =any
NOT IN (list) 空值会有影响 (等于list其中任何一个就行，为提高效率常把比例高的放在前面)<=>
另一种形式<>ALL
not in (1,3,null) --不会找到任何记录，not in 要与所有值比较然后得到结果，不能与null比较
LIKE 模糊配置 NOT LIKE 通配比较
IS NULL 是空
AND
OR
NOT
练习4:找出表名以S_开头的所有表
你可能这样写:
select table_name from user_tables where table_name like 's_%' --小写
SQL> no rows selected --Oracle在执行见表后自动将表明转换成大写
select table_name from user_tables where table_name like 'S_%'
一个表名为SS的表也被查找出来，显然不是我们要查找的记录，因为'_'在oracle中表示任意字符
对于一些特殊字符，要用到escape转义
select table_name from user_tables where table_name like 'S\_%' escape '\';
哪些人没有提成
select last_name ,commission_pct from s_emp where commission_pct is not null;
42部门工资大于1000员工
select first_name ,salary from s_emp
where dept_id=42 and salary >1000
▲单行函数：
☆dual 哑表dual--系统提供一张用于计算的表，一行一列
字符函数：
lower 转小写 select lower('HUGO') from dual;
upper 转大写 select upper('hugo') from dual;
initcap 首字符大写 select initcap('tarena') from dual;
concat 连接字符串 select concat(first_name , last_name) from s_emp;
substr 求子串 select substr('whsdasd0710' ,1,6) from dual; (取前六个字符) select substr('whsdasd0710',-2) from dual; (取后两个字符)
length 求字符长度 select length('tarena') from dual;
nvl 空值函数
select first_name , substr(first_name , -2 ) from s_emp; (查出s_emp表中所有用户名字的最后两个字符)
练习5： 找出不确定大小写名字为ben的员工
select first_name , salary from s_emp where lower(first_name)='ben';-不能使用index
优化提示：
如果数据在表中存的很整齐不要使用转换函数，where first_name='Ben' 效率高，能用到index
在DML语句中使用转换函数使数据格式整齐，便于提高查询效率
显示员工first_name后两个字母
select first_name ,substr(first_name,-2) from s_emp;
select first_name ,substr(length(first_name)-1,2) from s_emp;
▲数值函数：
round 函数 select round(45.935, 2) from dual; 不带参数时默认为0位小数(四舍五入）
trunc 函数 select trunc(45.995, 1) from dual; （截取）
日期函数：DD-MON-RR （日期格式敏感）
日期信息:世纪(yyyy隐含世纪信息）、年、月、日、时、分、秒
sysdate 返回当前系统时间 select sysdate from dual;
更改当前会话的设置格式：
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
显示昨天、今天、明天
select sysdate-1, sysdate+1, sysdate , sysdate+1 from dual; 注意单位是以天为单位
十分钟后时间
select sysdate+10/24/60 from dual;
MONTHS_BETWEEN (DATE1 , DATE2 ) 求两个日期之前相差的月数
add_months(date , 4 ) 在 date上再添加4个月
select next_day(sysdate,'FRIDAY') from dual ; 求这个日期的下一个FRIDAY
last_day 求月的最后一天
round 函数: select round(sysdate, 'MONTH') from dual; 参数可以为： MONTH YEAR(看上半年还是下半年)
日期的运算：
从现在起第一个周五的日期
select next_day(sysdate,'FRIDAY') from dual;
当前日期当月最后一天
select trunc(last_day(sysdate)+1) from dual;
round()不支持隐式数据类型转换
round(10-OCT-06)--不支持
sysdate='10-OCT-06'
round(sysdate)
下个月1号的日期
select add_months(trunc(sysdate, 'MONTH'), 1 ) from dual ;
select trunc(last_day(sysdate)+1,'month') from dual;
关于日期的两种形式：
DD-MON-RR(default)
转换函数：
to_char
从数字转化为char
从日期转化为char to_char(date, 'fmt' )
select to_char(sysdate, 'yyyy mm dd hh24:mi:ss') from dual;
select to_char(sysdate ,'YEAR MONTH ' from dual;
to_date
字符转日期 select to_date('2000 11 20', 'yyyy mm dd ') from dual;
select round(to_date('10-OCT-06' ,'dd-mon-RR') ) from dual;
to_number
字符转数字
select to_number('10') from dual
▲读日期
显示42部门员工入职的时间 日期的格式 yyyy mm dd hh24:mi:ss
select first_name ,to_char(start_date,'yyyy mm dd hh24:mi:ss') from s_emp where dept_id=42;
五月份入职的员工
select first_name ,start_date from s_emp where to_char(start_date,'mm')='05';
select first_name ,start_date from s_emp where to_char(start_date,'fmmm')='5';--fm压缩前导0
▲写日期
怎么插一个日期到表里
方式1：to_date()
方式2：缺省日期格式
create table test_hugo(c1 date);--建表
insert into values('10-OCT-06');--输入入日期值（默认日期格式）
--默认的日期格式系统会补世纪信息（根据但前时间和公式算出来）和时分秒（00：00：00午夜）
insert into test_hugo values(to_date('2007 08 02 01:01:01','yyyy mm dd hh24:mi:ss')) ;(指定日期格式）
*****************************************************************************************************************
*********************************
** oracle 学习笔记第二天 **
** hugo **
***********************************
触发排序的操作：distinct 、order by、group by
为什么有关联？
任何一张表都不能满足需求，所以要用多张表
关联类型：
内联接：inner join严格匹配
外连接：outter join一个都不能少，显示不能匹配的记录,模拟空记录
▲等值连接（内联接）：
首先了解s_emp ,s_dept结构
》查看员工的姓名和员工部门名称:(要考虑到表中实际数据中空值的影响）
select first_name , name from s_emp, s_dept 此语句会产生300条记录，笛卡儿积
s_emp (25) * s_dept(12) = 300 两表没有任何关联时会产生笛卡儿积
只有部门号相等的记录才有意义
select first_name , name from s_emp, s_dept where s_emp.dept_id=s_dept.id;
作表关联时经常为表起别名，可以省去输入很长的表名
select first_name , name from s_emp e, s_dept d where s_emp e.dept_id=d.id;
》每个员工所在的部门和部门所在的地区
select first_name , name from s_emp, s_dept, s_region where s_emp.dept_id=s_dept.id and s_dept.region_id=s_region.id;
Ben在什么部门上班
select e.first_name , d.name ,r.name
from s_emp e, s_dept d, s_region r
where e.dept_id=d.id
and d.region_id=r.id
and e.first_name='Ben';
Asia 地区有哪些部门
非等值连接（内联接）：
》找出员工名称和员工的工资级别
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal between b.losal and b.hisal;
select a.ename , a.sal, b.grade from emp a , salgrade b where a.sal>=b.losal and a.sal<=b.hisal;
》king的工资是几级
select a.ename , a.sal, b.grade from emp a , salgrade b
where a.sal between b.losal and b.hisal
and a.ename='KING';
▲自连接：（一张表字段有关系必然导致表记录间有关系，给一张表起不同的别名将表的字段关系转换成
两个表记录的关系）
select first_name , manager_id from s_emp;
查出所有员工的部门领导的名称：( 这种sql会少一条记录，总经理没有被配置上）
select e.first_name "Employee" , m.first_name "Manager"
from s_emp e , s_emp m
where e.manager_id=m.id;
▲谁是总经理(没有manager_id)
select first_name from s_emp
where id in (select id from s_emp
where manager_id is null);
-------------规范的写法(外连接＋is null)-----------------
select e.first_name "Employee", m.first_name "Manager" from s_emp e , s_emp m where e.manager_id=m.id(+)
and e.manager_id is null;
外连接：(（+）的一方会模拟空记录配置另一方，表示表达式另一端全部显示)这就称为外连接,一个记录都不能少
select e.first_name "Employee" , m.first_name "Manager"
from s_emp e , s_emp m where e.manager_id=m.id(+);
显示e.manager_id这张表
》统计公司的部门：
select distinct deptno from emp ;
select deptno from dept ;
★解决XX中没有XX的标准方式：外连接加非空条件
》找出没有员工的部门：
select e.deptno,d.deptno ,d.dname
from emp e,dept d
where d.deptno=e.deptno(+) and e.deptno is null;
detail信息
summary信息
组函数（group function)：
group by 分组子句 对分组后的子句进行过滤还可以用having 条件 对分组后的条件进行过滤 where 是对记录进行过滤
avg(distinct | all )求平均值
count(distinct | all )统计
max(distinct | all ） 求最大值
min(distinct | all )求最小值
sum(distinct | all ) 求和
(★所有组函数会忽略空值 , avg sum只能作用于数字类型）
》求有提成员工的提成的平均值；
select avg(commission_pct) from s_emp;//所有有提成人的平均值（忽略空值）
select avg(nvl(commission_pct ,0 ) ) from s_emp;
select sum(commission_pct)/count(*) from s_emp;
》有多少人有提成：
select count( commission_pct ) from s_emp ;
count(*) --用于统计记录数：
员工分部在多少个不同的部门：count 默认为作all的动作
select count(dept_id) from s_emp;//处理所有非空值
select count(distinct dept_id) from s_emp;//处理排重的非空值
求各个部门的平均工资：group by 子句也会触发排序
select dept_id , avg(salary) aa from s_emp group by dept_id order by aa ;
select dept_id , avg(salary) aa from s_emp group by dept_id ;
注意：group by 子句后面跟有条件只能是查询的结果中的字段,所以我们会人为在结果要加入一些group by 要用的字段,sql语句中出现group by select后面只能跟group by出现的字段
select region_id , count(*) from s_dept 此句会有错
select max(region_id) , count(*) from s_dept; (强制语法上可以正确,但是不能保证结果也会正确）
》求各个部门的平均工资
select dept_id,avg(salary) from s_emp group by dept_id;
》求各个部门不同工种的平均工资（列出部门号）：
select dept_id , title, avg(salary) from s_emp group by dept_id , title ;
☆group by跟的字段越多粒度越细，效率越慢
》哪些部门的平均工资比2000高:
select dept_id, avg(salary) aa from s_emp group by (dept_id) having avg(salary)>2000;
》42部门的平均工资:
select dept_id,avg（salary) from s_emp where dept_id=42 group by dept_id;
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id=42;
》除了42部门以外的部门的平均工资:
select dept_id , avg(salary) from s_emp group by (dept_id ) having dept_id!=42;
select dept_id , avg(salary) from s_emp where dept_id!=42 group by (dept_id ) ;(此种sql效率要高，先过滤再计算）
▲where 与 having区别
where 过滤的是记录，跟单行函数。
having 过滤的是分组以后的结果，跟组函数不能是单行函数。
》求各个部门的平均工资:(显示部门名称）
select d.name "dept",r.name "area",avg(salary) "avgSalary"
from s_emp e,s_dept d ,s_region r
where e.dept_id=d.id
and d.region_id=r.id
group by d.name,r.name
//为了过 oracle sql 语法关而写max(d.name) ***
//dept_id 定下来后，s_dept.name ,s_region.name 只有一个
select max(d.name) , max(r.name) , avg(e.salary)
from s_emp e, s_dept d , s_region r
where e.dept_id = d.id
and d.region_id=r.id
group by e.dept_id ;
▲关于子查询: Subqueries
找出所有员工中，工资最低的那个员工：( 利用子查询 ）
select first_name, salary from s_emp where salary = ( select min(salary) from s_emp);//where不能使用组函数将组函数作为结果返回
//这样写会出错姓名和工资不一致
select max(first_name), min(salary) from s_emp;
子查询运行的顺序： 先运行子查询再运行主查询 子查询一般出现在运算符的右边
单值运算符：运算后面只能跟一个值
多值运算符：可以对两个以上的值进行操作
》谁跟Smith干一样的活：
select last_name , title from s_emp where title =( select title from s_emp where last_name='Smith' )
and last_name <> 'Smith' ;
//上面的写法可能还存在bug，没有考虑到数据的全面性,有潜在性问题，当有两个人叫Smith并且做相同的工作，返回多条记录用等号，会漏掉记录。
★在执行模拟的数据不报错不一定sql正确，客户的数据更真实
//这种写法才考虑的比较全面
select last_name , title from s_emp where title in ( select title from s_emp where last_name='Smith' ) and last_name <> 'Smith' ;
使用子查询时应注意： 单行子查询返回多个结果时会有错误 single-row subquery returns more than one value
select * from s_emp where salary < ( select avg(salary) from s_emp) ;
哪些部门的平均工资比32部门的平均工资要低：
select dept_id ,avg(salary)
from s_emp
group by dept_id
having avg(salary)> (select avg(salary)
from s_emp
where dept_id=32)
查出哪些员工的工资比平均工资低：
第一步先查出各个部门的平均工资：
select min(avg(salary ) ) from s_emp group by dept_id;
第二步再查出哪个部门的工资是最低的:
select dept_id, avg(salary) from s_emp group by dept_id having avg(salary) = (select min(avg(salary) ) from s_emp group by dept_id ) ;
★结论：
⑴能用in实现都可以用内联接实现
⑵能用not in实现的可以用外连接
性能提示：使用not in效率差
》哪些人是领导：
select first_name ,id from s_emp where id in (select manager_id from s_emp);
》哪些人是普通员工：
----子查询----
select * from s_emp where id not in ( select manager_id from s_emp
where manager_id is not null);
not in 有空值找不到，加where manager_id is not null
》哪个部门里没有员工：
select deptno from dept where deptno not in ( select deptno from emp where deptno is not null);
》哪个部门平均工资最低
select dept_id ,avg(salary)
from s_emp
group by dept_id
having avg(salary)=(select min(avg(salary))
from s_emp
group by dept_id);
★》谁的工资比本部门的平均工资高(from 跟子查询）
-------规范写法------
select first_name ,salary
from s_emp e,
(select dept_id ,avg(salary) avgsal
from s_emp
group by dept_id) a
where e.dept_id = a.dept_id
and e.salary > a.avgsal;
E--R图 实体关系图entity relation
开发流程先进行需求分析，进行系统设计，建表，再进行开发编码,测试最终产品上线试运行。
把软件设计模型转化为数据中的表，设计时要考虑性能的设计
第一范式：一个属性描述一件事情，每个column 描述事情唯一，因为没有对每个column的值作限制，所以有重复记录
第二范式：至少有一个column取值唯一(UID),记录可区分,部分记录存在冗余，存在非主属性传递依赖。
第三范式：打破非主属性的依赖关系，非主属性严格依赖主属性，降低数据存贮压力，将数据分成多张表。
约束：
数据库提供完整性约束，防止葬数据保证数据的一致性
实体完整性--primary key (pk) 主键约束 不允许有重复和空值(唯一且非空）
引用完整性--foregin key (fk) 外键约束 两张表parent table child table
unique key (uk) 唯一可以为空
not null 非空
先插主表后插子表
先删子表在删父表
数据库设计时的注意：
索引： 为了提高效率而设计的一种与业务无关的
考虑表点用的物理空间：
考虑表之间的关系：
一对多关系： 利用FK+PK实现，多的一方引用外键
一对一关系： 可以利用FK+UK实现，
多对多关系： 通过中间增加一个附加表来实现，附加表利用联合主键来实现，联合起来的主键唯一。
DDL语句：数据库定义语句：
table (表）
view(示图）
sequence(序列号）
index(索引）
创建表语句：
create table [schema].表名 ( 字段名， 字段类型 约束条件); schema 默认就是当前用户,严格来访问表名完整的写法是schema.tablename
数据类型：
表名的命令规则： 首字母为字母，不得超过30个字符
char(size) 定长 不管是否达到最大宽度，都会点最大的宽度。2000
varchar2(size) 可变长 按实际的字节占用空间4000
number 所有的数字类型都称为number
number(n, m ) n------n位宽度 m-----小数点后的宽度
number(2,4)小数点后4 位，有效位2位 values(0.0099) 这样可以 values(0.01)这样出错
LONG 2GB 大文本一个表最我只允许定义一个LONG类型(不建议使用）
CLOB 大对象形式存放(在表里只存一个指针)
BLOB 存二进制大对象(声音，图像之类）
使用缺省值：
default 作用演示：
create table test(c1 number default 10, c2 number);
约束的演示：
主键约束的定义：
create table test(c number primary key ); 列级约束
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
列级约束定义不了联合主健
外键约束的定义：(先定义父表，再定义子表）引用完整性
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
on delete cascade (及联删除，删除父表时子表也跟着删除）
on delete set null (及联删除父表时子表中引用的字段为null）
uk与pk区别：uk允许为空
不给约束起名字时，系统给约束起名时的规律为：数据库用户名_数字(约束名也不能重名）
定义一个约束的两种形式：
列级约束 表级约束
非空约束：
not null (利用desc可能看到）primary key 自动具有非空约束的特点
primary key约束：
主键约束的定义：
第一种定义形式：
create table test(c number primary key ); 列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
foregin key (fk) 外键约束:
(先定义父表，再定义子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
check 约束：
create table test(c1 number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .
怎么创建一个脚本文件： xxx.sql结尾
执行角本的方法：
在sqlplus环境中执行：@filename.sql
在shell环境中执行： sqlplus sd0706/sd0706 @filename.sql
▲伪列
oracle提供给结果自动编号的机制
select rownum,fisrt_name from s_emp where rownum=1;
select rownum,fisrt_name from s_emp where rownum<=3;
rownum不能等于n，rownum between m and n不可以（rownum>=m不能实现，仅满足rownum = 1或者rownum<=n ）
select rownum,fisrt_name from s_emp where rownum=2;--报错
要查询 rownum between m and n条件，先写子查询将rownum变成列，然后rownum between m and n
查询s_emp 第5条至第10条记录
select first_name from (select rownum r , first_name from s_emp ) a where a.r between 5 and 10;
select id,first_name from (select rownum r , a.* from (select * from s_emp) a ) where r between 5 and 10;
》工资最高的前5个人
select rownum,first_name,salary from
(select first_name ,salary from s_emp order by salary desc ) a
where rownum <=5;
*****************************************************************************************************************
*************************************
** oracle 学习笔记第三天 **
** hugo **
*************************************
E--R图 实体关系图entity relation
开发流程先进行需求分析，进行系统设计，建表，再进行开发编码,测试最终产品上线试运行。
把软件设计模型转化为数据中的表，设计时要考虑性能的设计
第一范式：一个属性描述一件事情，每个column 描述事情唯一，因为没有对每个column的值作限制，所以有重复记录
第二范式：至少有一个column取值唯一(UID),记录可区分,部分记录存在冗余，存在非主属性传递依赖。
第三范式：打破非主属性的依赖关系，非主属性严格依赖主属性，降低数据存贮压力，将数据分成多张表。
约束：
数据库提供完整性约束，防止葬数据保证数据的一致性
实体完整性--primary key (pk) 主键约束 不允许有重复和空值(唯一且非空）
引用完整性--foregin key (fk) 外键约束 两张表parent table child table
unique key (uk) 唯一可以为空
not null 非空
先插主表后插子表
先删子表在删父表
数据库设计时的注意：
索引： 为了提高效率而设计的一种与业务无关的
考虑表点用的物理空间：
考虑表之间的关系：
一对多关系： 利用FK+PK实现，多的一方引用外键
一对一关系： 可以利用FK+UK实现，
多对多关系： 通过中间增加一个附加表来实现，附加表利用联合主键来实现，联合起来的主键唯一。
DDL语句：数据库定义语句：
table (表）
view(示图）
sequence(序列号）
index(索引）
创建表语句：
create table [schema].表名 ( 字段名， 字段类型 约束条件); schema 默认就是当前用户,严格来访问表名完整的写法是schema.tablename
数据类型：
表名的命令规则： 首字母为字母，不得超过30个字符
char(size) 定长 不管是否达到最大宽度，都会点最大的宽度。2000
varchar2(size) 可变长 按实际的字节占用空间4000
number 所有的数字类型都称为number
number(n, m ) n------n位宽度 m-----小数点后的宽度
number(2,4)小数点后4 位，有效位2位 values(0.0099) 这样可以 values(0.01)这样出错
LONG 2GB 大文本一个表最我只允许定义一个LONG类型(不建议使用）
CLOB 大对象形式存放(在表里只存一个指针)
BLOB 存二进制大对象(声音，图像之类）
使用缺省值：
default 作用演示：
create table test(c1 number default 10, c2 number);
约束的演示：
主键约束的定义：
create table test(c number primary key ); 列级约束
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
列级约束定义不了联合主健
外键约束的定义：(先定义父表，再定义子表）引用完整性
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
on delete cascade (及联删除，删除父表时子表也跟着删除）
on delete set null (及联删除父表时子表中引用的字段为null）
uk与pk区别：uk允许为空
不给约束起名字时，系统给约束起名时的规律为：数据库用户名_数字(约束名也不能重名）
定义一个约束的两种形式：
列级约束 表级约束
非空约束：
not null (利用desc可能看到）primary key 自动具有非空约束的特点
primary key约束：
主键约束的定义：
第一种定义形式：
create table test(c number primary key ); 列级约束
第二种定义形式：
create table test(c number , primary key(c) ) ; 表级约束
create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc1
create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键
foreign key (fk) 外键约束:
(先定义父表，再定义子表）
carete table parent(c1 number primary key );
create table child (c number primary key , c2 number references parent(c1));
或表级约束定义:
create table child( c number primary key , c2 number , foreign key(c2) references parent(c1));
check 约束：
create table test(c1 number check(c1>1000));
此表中要求c1的值必须要大于1000 才为有效值 .
怎么创建一个脚本文件： xxx.sql结尾
执行角本的方法：
在sqlplus环境中执行：@filename.sql
在shell环境中执行： sqlplus sd0704/sd0704 @filename.sql
=========================
创建表的语法：
create table 表名 ( 字段名 字段类型 约束类型（可选））；
利用已知表建一张新表：注会把非空约束带过来，其它约束要自己添加
create table s_emp_42 as select * from s_emp where dept_id = 42;
只取要表结构，不想要表中数据的建表方式：
create table s_emp_copy as select * from s_emp where 1=2;
(这是一个小技巧,在JDBC的学习中会用到 where 1=1 的形式,注意体会）
查看一张表的约束：( 查数据字典示图)
desc user_constraints;(这个数据字典中会查到相应的信息）
select constraint_name, constraint_type from user_constraints where table_name='S_EMP';
P pk
R fk
C check
U UK
V 这种只定义在示图中(with check option 相当于组示图加了一个约束）
O 也是出现在示图中
非空约束和CHECK都是用C来表示
查看字段约束的方法：
desc user_cons_columns;
select column_name, position from user_cons_columns where constraint_name='S_EMP_ID_PK' ;
position 的含义：联合主键，约束名一样。
user_constraints user_cons_columns 两张表的约束名相等，表名相等，两张表一关联就可以查出所需的信息。
select constraint_name , r_constraint_name from user_constraints where constraint_type='R' and table_name='S_EMP' ;
数据库建立时，数据字典就会建好。
user_constraints; 自己拥有的
all_constraints; 你自己拥有的加上你可以访问的
dba_constraints 所有的
查看当前数据库数据字典的字典（这个视图很重要）
desc dict;
select table_name from dict where table_name like '%cons%';
示图：
user_objects; user_tables;
select distinct object_type from user_objects;
注意 insert into 后面可以跟子查询
insert into s_emp_42 select * from s_emp where dept_id =42;
UPDATE 修改字段值：
update s_emp set dept_id =10 where id =2 ;
update s_emp set commission_pct =10 ; 没有where条件时说明是改表中所有的值.
注意：如有外键引用时常会出现外键引用值没有找到等错误？
delete 删除记录命令语法：
delete 操作可恢复
delete 操作后空间一直被占用
delete 删除大表效率低
delete from s_emp where dept_id=42;
delete form s_emp ; 没有where条件时说明删除表中所有的值
注意：如有外键引用时，删除一张表时常会出现不能删除的情况，
原因一 是因为此时正在有人操作表中记录
原因二 此表有其他的表引用，没能设及联删除：
delete 删除一张大表时空间不释放，非常慢是因为占用大量的系统资源，支持回退操作，空间还被这张表占用着。
truncate table 表名 (删除表中记录时释放表空间)
介绍事务的概念：一起成功一起失败
commit 提交，此时说明前面所有语句都成功执行，DDLorDCL命令隐含自动提交
rollback 回退操作，此时会恢复至上一次提交时的状态。
savepoint 设置保存点
演示并发操作：－－提交很重要
开三个session
session1：表test插2调记录，改变一条记录没有提交称为active tranction
session2：看不到session1更改的记录，此时update表test会进入等待
session3：drop test 报错 resource busy an acquire with NOWAIT specified
有DML正在操作这张表
DML 操作时oracle会给表加：（锁的粒度行级－并发度高）sybase以前是页级锁在同一配置下加锁，
锁的时间：事务有多长是就会锁都长时间
×××写程序时一定要commit不然锁不释放
表级共享锁： 对于操作一张表中的不同记录时，互不影响（你加我也能加）
行级排它锁：对于一行记录，oracle 会只允许只有一个用户对它在同一时间进行修改操作
wait() 等到行级锁被释放，才进行数据操作
drop一张表时也会对表加锁，DDL排它锁,所以在删除一张表时如果当前还有用户操作表时不能删除表
alter table 命令用于修改表的结构(这些命令不会经常用)：
增加约束：
alter table 表名 add constraint 约束名 primary key (字段）;
解除约束：(删除约束)
alter table 表名 drop primary key（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
alter table father drop primary key cascade ; (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
alter table 表名 drop constraint 约束名;
(怎样取一个约束名：1、人为的违反约束规定根据错误信息获取!
2、查询示图获取约束名!)
alter table 表名 disable from primary key ; (相当于把一个表的主键禁用)
alter table 表名 enable primary key ;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）
增加字段：
alter table 表名 add(字段字，字段类型)
删除字段：
alter table 表名 drop(字段)
alter table 表名 drop column 字段 ; (8i 以后才支持）
给列改名:920才支持
alter table 表名 rename column 旧字段名 to 新字段名;
修改字段
(此时应注意的问题，更改时要看具体值情况之间的转达换， 改为字符类型时，必须要为空)
alter table 表名 modify( 字段，类型)
更改表中的字段：
update 表名 set 字段 ＝ 值 where 条件
更改表名
rename 旧表名 to 新表名 ；
删除表：
trucate table 表名:(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表
关于oralce中产生序列(sequence):利用sequence生成id会产生gap（不连续），可能有rollback，down机，并发使用
create sequence 序列名alter system flush shared_pool;
(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值）
sequence 的参数：
increment by n 起始值, start with n 递增量, maxvalue n 最大值, minvalue n 最小值，cycle | no cycle 轮回， cache n 绶存(第一次取时会一次取多少个id存起来）
查看 sequence 示图：
desc user_sequences ;
select sequence_name , cache_size , last_number from user_sequences where sequence_name like 's_';
select 序列名.currval from dual 查看当前的序列数
select 序列名.nextval from dual 查看下一个序列数，它会自动给当前的序列加１
为列：nextval currval
(开另一个session时取当前值不成功时，应该先取下一个值，再取当前值)
清空当前会话的内存：
定义SEQUENCE定义cache或nocache的影响
cache从内存中取，减少对系统表的使用，提高并发度
nocache要通过nextval看下一个值
alter system flush shared_pool;（执行此命令要有DBA权限，一般用户执行出错）
flush shared_pool定义cache的sequence会将cache中值清空
修改序列：(此命令不常用，只需了解就行不必深究）
alter sequence 序列名 修改项；
删除序列sequence
drop sequence 序列名;
创建示图： creating views（属于了解知识）
desc user_views;
select text from user_views where view_name='TEST1_V1' ;
示图就相当于一条select 语句,定义了一个示图就是定义了一个sql语句,示图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle 8i 以后的新示图）MV 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题， 主要应用在数据仓库中用要用于聚合表）
使用示图的好处：限制数据访问权限.
如何创建一个示图：
建议建立view时使用create or replace如果使用create，view存在会先删除后创建
create or replace views test_vi as select * from test1 where c1=1;
此时往表test1（base table 基表）中插入数据时：表中没能变化，示图中的数据发生改变
从示图中插数据时相对应的表会发生改变:
往示图中插数据时，会直接插进基表中，查看示图中的数据时，相当于就是执行创建时的select语句。
简单示图：能进行DML操作。
复杂示图：来源于多张表，不能执行DML操作。
物化视图：用于数据仓库，给领导看，mv查询速度快，但基表数据无法更新，存在刷新问题
关于rownum：内嵌视图
rownum 有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。rownum常用于分页显示。
练习：查询出第5条数据和第10条数据之间：将前10条选出来在where5－10
select first_name , rnum from ( select rownum rnum , first_name from s_emp where rownum <=10 ) where rnum between 5 and 10;
练习：哪些员工的工资比本部门的平均工资高？
select first_name , salary , avgsal from s_emp e , ( select dept_id , avg (salary ) avgsal from s_emp group by dept_id ) a where e.dept_id =a.dept_id and e.salary > a.avgsal;
另一种写法效率低有多少员工就计算多少此平均值
select first_name,salary
from s_emp o
where salary > (select avg(salary) from s_emp e
where e.dept_id = o.dept_id)
在示图上加一个 with check option 就相当于给示图加上了约束
create view test_v as select * from test where c =1 with check option ;
同义词:相当于别名的作用(***只需了解***）系统自建的同义词： user_tables
create synonym asd_s_emp for asd_0607.s_emp ;
目的就是为了给asd_0607_s_emp表起另一个代替的名称asd.s_emp;注意这个同义词只能自己使用;
create public synonym p_s_emp fro asd_0607.s_emp; 创建公共的同义词，但是要权限.
删除同义词：
drop synonym 同义词名称
创建索引： Creating indexes(概念很重要对系统的性能影响非常大）
建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。truncate 表时索引结构在，但是数据不存在。
full table scan 全表扫描 --找多的记录
用索引就是为了快速定位数据：(理解时就以字典的目录为例）--对于数据量大的表，找少的记录适合用索引
查看表的rowid：告诉我们表的物理位置
select rowid , first_name from s_emp;
rowid 定义的信息有： object block table
每条记录都有自己的rowid ，定位记录最快的方式使用rowid，index使用rowid定位
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,对于表大情况才能现出优势这就是我们建索引的最终目的，
创建一个索引：
create index 索引名 on 表名 ( 字段名）;
create index testindex on test(c1, c2);
哪些字段应该建索引：
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率最高.
什么时候建联合索引
先解决什么时候建索引付出空间、维护的代价。
1，这个字段经常出现在where里的字段
2，经常用于表连接的字段
索引不是目的是手段
索引不包含空值
替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间
set verify on
set verify off;
相当于开关变量，用于控制是否显示新旧的sql语句
select id ,last_name ,salary from s_emp where title='&job_title';
更改交互的提示信息：
accept p_dname prompt ' 提示信息';
定义变量：
define p_dname='abc';
DDL(structure)
create
alter
truc
drop
DML(row)
transaction
commit
rollback
savepoint
insert
update
delete
querry(select)
select rowid from test;
rowid(从表中查除记录最快方式）
object-id file-id block row-id
oracle函数大全
SQL中的单记录函数
1.ASCII
返回与指定的字符对应的十进制数;
SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2.CHR
给出整数,返回对应的字符;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
赵 A
3.CONCAT
连接两个字符串;
SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual;
高乾竞电话
----------------
010-88888888转23
4.INITCAP
返回字符串并将字符串的第一个字母变为大写;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
---------
9
6.LENGTH
返回字符串的长度;
SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
高乾竞 3 北京市海锭区 6 9999.99 7
7.LOWER
返回字符串,并将所有的字符小写
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER
返回字符串,并将所有的字符大写
SQL> select upper('AaBbCcDd') upper from dual;
UPPER
--------
AABBCCDD
9.RPAD和LPAD(粘贴字符)
RPAD 在列的右边粘贴字符
LPAD 在列的左边粘贴字符
SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;
LPAD(RPAD('GAO',10,’*’,17,’*’
-----------------
*******gao*******
不够字符则用*来填满
10.LTRIM和RTRIM
LTRIM 删除左边出现的字符串
RTRIM 删除右边出现的字符串
SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;
LTRIM(RTRIM('
-------------
gao qian jing
11.SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL> select substr('13088888888',3,8) from dual;
SUBSTR('
--------
08888888
12.REPLACE('string','s1','s2')
string 希望被替换的字符或变量
s1 被替换的字符串
s2 要替换的字符串
SQL> select replace('he love you','he','i') from dual;
REPLACE('H
----------
i love you
13.SOUNDEX
返回一个与给定的字符串读音相同的字符串
SQL> create table table1(xm varchar(8));
SQL> insert into table1 values('weather');
SQL> insert into table1 values('wether');
SQL> insert into table1 values('gao');
SQL> select xm from table1 where soundex(xm)=soundex('weather');
XM
--------
weather
wether
14.TRIM('s' from 'string')
LEADING 剪掉前面的字符
TRAILING 剪掉后面的字符
如果不指定,默认为空格符
15.ABS
返回指定值的绝对值
SQL> select abs(100),abs(-100) from dual;
ABS(100) ABS(-100)
--------- ---------
100 100
16.ACOS
给出反余弦的值
SQL> select acos(-1) from dual;
ACOS(-1)
---------
3.1415927
17.ASIN
给出反正弦的值
SQL> select asin(0.5) from dual;
ASIN(0.5)
---------
.52359878
18.ATAN
返回一个数字的反正切值
SQL> select atan(1) from dual;
ATAN(1)
---------
.78539816
19.CEIL
返回大于或等于给出数字的最小整数
SQL> select ceil(3.1415927) from dual;
CEIL(3.1415927)
---------------
4
20.COS
返回一个给定数字的余弦
SQL> select cos(-3.1415927) from dual;
COS(-3.1415927)
---------------
-1
21.COSH
返回一个数字反余弦值
SQL> select cosh(20) from dual;
COSH(20)
---------
242582598
22.EXP
返回一个数字e的n次方根
SQL> select exp(2),exp(1) from dual;
EXP(2) EXP(1)
--------- ---------
7.3890561 2.7182818
23.FLOOR
对给定的数字取整数
SQL> select floor(2345.67) from dual;
FLOOR(2345.67)
--------------
2345
24.LN
返回一个数字的对数值
SQL> select ln(1),ln(2),ln(2.7182818) from dual;
LN(1) LN(2) LN(2.7182818)
--------- --------- -------------
0 .69314718 .99999999
25.LOG(n1,n2)
返回一个以n1为底n2的对数
SQL> select log(2,1),log(2,4) from dual;
LOG(2,1) LOG(2,4)
--------- ---------
0 2
26.MOD(n1,n2)
返回一个n1除以n2的余数
SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;
MOD(10,3) MOD(3,3) MOD(2,3)
--------- --------- ---------
1 0 2
27.POWER
返回n1的n2次方根
SQL> select power(2,10),power(3,3) from dual;
POWER(2,10) POWER(3,3)
----------- ----------
1024 27
28.ROUND和TRUNC
按照指定的精度进行舍入
SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;
ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55
29.SIGN
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
SQL> select sign(123),sign(-100),sign(0) from dual;
SIGN(123) SIGN(-100) SIGN(0)
--------- ---------- ---------
1 -1 0
30.SIN
返回一个数字的正弦值
SQL> select sin(1.57079) from dual;
SIN(1.57079)
------------
1
31.SIGH
返回双曲正弦的值
SQL> select sin(20),sinh(20) from dual;
SIN(20) SINH(20)
--------- ---------
.91294525 242582598
32.SQRT
返回数字n的根
SQL> select sqrt(64),sqrt(10) from dual;
SQRT(64) SQRT(10)
--------- ---------
8 3.1622777
33.TAN
返回数字的正切值
SQL> select tan(20),tan(10) from dual;
TAN(20) TAN(10)
--------- ---------
2.2371609 .64836083
34.TANH
返回数字n的双曲正切值
SQL> select tanh(20),tan(20) from dual;
TANH(20) TAN(20)
--------- ---------
1 2.2371609
35.TRUNC
按照指定的精度截取一个数
SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;
TRUNC1 TRUNC(124.16666,2)
--------- ------------------
100 124.16
36.ADD_MONTHS
增加或减去月份
SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;
TO_CHA
------
200002
SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;
TO_CHA
------
199910
37.LAST_DAY
返回日期的最后一天
SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;
TO_CHAR(SY TO_CHAR((S
---------- ----------
2008-10-30 2008-10-31
SQL> select last_day(sysdate) from dual;
LAST_DAY(S
----------
31-10月 -08
38.MONTHS_BETWEEN(date2,date1)
给出date2-date1的月份
SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
MON_BETWEEN
-----------
9
SQL>select
months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
MON_BETW
---------
-60
39.NEW_TIME(date,'this','that')
给出在this时区=other时区的日期和时间
SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time
(sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;
BJ_TIME LOS_ANGLES
------------------- -------------------
2008-10-30 17:34:25 2008-10-31 00:34:25
40.NEXT_DAY(date,'day')
给出日期date和星期x之后计算下一个星期的日期
SQL> select next_day('18-5月-2001','星期五') next_day from dual;
NEXT_DAY
----------
25-5月 -01
41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;
TO_CHAR(SYSDATE,'
-----------------
30-10-2008 星期四
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
to_char (trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;
HH HHMM
------------------- -------------------
2008-10-30 17:00:00 10/30/2008 17:37:00
42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;
ROWID ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES
43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;
conver
------
strutz
44.HEXTORAW
将一个十六进制构成的字符串转换为二进制
45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制
46.ROWIDTOCHAR
将ROWID数据类型转换为字符类型
47.TO_CHAR(date,'format')
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2008/10/30 17:40:38
48.TO_DATE(string,'format')
将字符串转化为ORACLE中的一个日期
49.TO_MULTI_BYTE
将字符串中的单字节字符转化为多字节字符
SQL> select to_multi_byte('高') from dual;
TO
--
高
50.TO_NUMBER
将给出的字符转换为数字
SQL> select to_number('1999') year from dual;
YEAR
---------
1999
51.BFILENAME(dir,file)
指定一个外部二进制文件
SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));
52.CONVERT('x','desc','source')
将x字段或变量的源source转换为desc
SQL> select sid,serial#,username,decode(command,
2 0,'none',
3 2,'insert',
4 3,
5 'select',
6 6,'update',
7 7,'delete',
8 8,'drop',
9 'other') cmd from v$session where type!='background';
SID SERIAL# USERNAME CMD
--------- --------- ------------------------------ ------
1 1 none
2 1 none
3 1 none
4 1 none
5 1 none
6 1 none
7 1275 none
8 1275 none
9 20 GAO select
10 40 GAO none
53.DUMP(s,fmt,start,length)
DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值
SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D
54.EMPTY_BLOB()和EMPTY_CLOB()
这两个函数都是用来对大数据类型字段进行初始化操作的函数
55.GREATEST
返回一组表达式中的最大值,即比较字符的编码大小.
SQL> select greatest('AA','AB','AC') from dual;
GREA
--
AC
SQL> select greatest('啊','安','天') from dual;
GREATE
--
安
56.LEAST
返回一组表达式中的最小值
SQL> select least('啊','安','天') from dual;
LE
--
啊
57.UID
返回标识当前用户的唯一整数
SQL> show user
USER 为"sd0710"
SQL> select username,user_id from dba_users where user_id=uid;
USERNAME USER_ID
------------------------------ ---------
GAO 25
58.USER
返回当前用户的名字
SQL> select user from dual;
USER
------------------------------
sd0710
59.USEREVN
返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
ISDBA 查看当前用户是否是DBA如果是则返回true
SQL> select userenv('isdba') from dual;
USEREN
------
FALSE
SQL> select userenv('isdba') from dual;
USEREN
------
TRUE
SESSION
返回会话标志
SQL> select userenv('sessionid') from dual;
USERENV('SESSIONID')
--------------------
1149
ENTRYID
返回会话人口标志
SQL> select userenv('entryid') from dual;
USERENV('ENTRYID')
------------------
0
INSTANCE
返回当前INSTANCE的标志
SQL> select userenv('instance') from dual;
USERENV('INSTANCE')
-------------------
1
LANGUAGE
返回当前环境变量
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LANG
返回当前环境的语言的缩写
SQL> select userenv('lang') from dual;
USERENV('LANG')
----------------------------------------------------
ZHS
TERMINAL
返回用户的终端或机器的标志
SQL> select userenv('terminal') from dual;
USERENV('TERMINA’)
----------------
COMPUTER
VSIZE(X)
返回X的大小(字节)数
SQL> select vsize(user),user from dual;
VSIZE(USER) USER
----------- ------------------------------
6 sd0710
60.AVG(DISTINCT|ALL)
all表示对所有的值求平均值,distinct只对不同的值求平均值
SQLWKS> create table table3(xm varchar(8),sal number(7,2));
语句已处理。
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('gao',1111.11);
SQLWKS> insert into table3 values('zhu',5555.55);
SQLWKS> commit;
SQL> select avg(distinct sal) from sd0710.table3;
AVG(DISTINCTSAL)
----------------
3333.33
SQL> select avg(all sal) from sd0710.table3;
AVG(ALLSAL)
-----------
2592.59
61.MAX(DISTINCT|ALL)
求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次
SQL> select max(distinct sal) from scott.emp;
MAX(DISTINCTSAL)
----------------
5000
62.MIN(DISTINCT|ALL)
求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次
SQL> select min(all sal) from sd0710.table3;
MIN(ALLSAL)
-----------
1111.11
63.STDDEV(distinct|all)
求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
SQL> select stddev(sal) from scott.emp;
STDDEV(SAL)
-----------
1182.5032
SQL> select stddev(distinct sal) from scott.emp;
STDDEV(DISTINCTSAL)
-------------------
1229.951
64.VARIANCE(DISTINCT|ALL)
求协方差
SQL> select variance(sal) from scott.emp;
VARIANCE(SAL)
-------------
1398313.9
65.GROUP BY
主要用来对一组数进行统计
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
10 3 8750
20 5 10875
30 6 9400
66.HAVING
对分组统计再加限制条件
SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;
DEPTNO COUNT(*) SUM(SAL)
--------- --------- ---------
20 5 10875
30 6 9400
67.ORDER BY
用于对查询到的结果进行排序输出
SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;
DEPTNO ENAME SAL
--------- ---------- ---------
10 KING 5000
10 CLARK 2450
10 MILLER 1300
20 SCOTT 3000
20 FORD 3000
20 JONES 2975
20 ADAMS 1100
20 SMITH 800
30 BLAKE 2850
30 ALLEN 1600
30 TURNER 1500
30 WARD 1250
30 MARTIN 1250
30 JAMES 950
扫一扫
3422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
