Oracle SQL day1
Oracle SQL(Oracle 9i 9.2.0.1.0)
一、DataBase
保存数据，以表的形式表现数据
二、SQL
SQL(structure query language 结构化查询语言)，是操作关系型数据库中的对象
DDL（data definition language 数据定义语言），用于建表或删表操作，以及对表约束进行修改。
create table , alter table , drop table 对表结构的增删操作。
DML（Data manipulation language 数据操作语言），向表中插入纪录，修改纪录
insert , update , delete , merge
transaction ,事务控制语言，由DML语句组成的，commit; ,rollback;
select 查询语句
dcl 授权语句 grant
三、Oracle
DBMS 数据库管理系统
有Oracle提供，还提供AS，应用服务器
DBA 数据库管理员
四、相关操作
在UNIX中使用 echo $ORACLE_SID 查看现有的Oracle数据库
SQL查询SID ：select instance_name from v$instance;
1、sqlplus 访问数据库命令（本地访问/远程访问），和数据库建立连接的命令，是数据库操作的环境
sqlplus 用户名/密码
2、show user 显示当前用户的用户名
改变身份可以直接connect 用户名/密码 --- 这个是sqlplus命令
在sqlplus中可以使用 ! 可以在shell和sqlplus间切换，!shell命令 可以在sqlplus中使用shell命令。
实际上是sqlplus开了子进程来执行shell命令。
3、Oracle数据库中的表分两类：用户表（用户使用操作的表），系统表（数据库系统维护的表，也叫数据字典）
对用户表的DDL操作出发了对系统表的DML操作！
五、基本语法
1、select查询语句
select table_name from user_tables;（查询系统表）
以上的查询语句就是查询本用户下所拥有的所有表的表名。
投影操作，只查看选择的字段的信息。
选择操作，查看字段中的特定某些信息。
联接操作，多表查询，通过表间连接，查寻出多表中的信息
（1）select table_name from user_tables;（查询系统表）
以上的查询语句就是查询本用户下所拥有的所有表的表名。
（2）sqlplus的buffer中会缓存最后一条sql语句，可以使用"/"来执行这最后一条sql语句，也可以使用
edit命令来编辑最后一条sql语句。
l命令（list）（sqlplus命令）可以显示buffer中最后一条命令。
sqlplus命令可以缩写
（3）desc [表名]
这是一条sqlplus命令，注意他不是sql语句，这条命令用于查看表的结构。describe的缩写
[字段名] [字段的类型]，这是使用完desc命令后显示的表结构。
（4）select [表的字段名1],[表的字段名2], ... from 表名;
select * from 表名; 查寻表中所有字段的信息
（5）关键字不能拆分，sql语句，以及表名，字段名是大小写不敏感的。
sql语句要以";"结尾，来表示sql语句结束，如果不加";"系统不会执行此条sql语句，并提示。
在Oracle中字符显示是左对齐，数值右对齐。
（6）在select 语句中可以使用数学表达式。
select [表达式（必须包含本表字段名）],[...],.... from 表名；
运算的优先级的先乘除后加减，同级自左向右运算，括号改变优先级。
（7）别名
select [字段名或表达式] ["别名"]，[...] ["..."],.... from 表名；
可以通过在字段名或表达式后加空格"别名"，可以给列，或者表达式结果其别名。
表达别名必须加双引号。
（8）字符串拼接使用||符号
select 目标字段名||' '||目标字段名 from 表名；
注意：在Oracle中的字符或字符串要用单引号，双引号用来起别名
别名中需要使用空格，或是大小写敏感时需要用".."包含。
练习：
自己写一条SQL语句，执行的结果是select * from ...;
其中...是每张系统表的表名
即在每张系统表的表名前加“select * from” ，后加“;”
select 'select * from '||table_name||';' from user_tables;
2、处理错误
（1）!oerr ora [错误号] ，系统可以显示错误的原因和如何修改。如果命令错误输入可以使用edit或ed来修改输入错误。
实际上是在编辑缓存文件中的最后一条sql语句。
也可以使用 (change) c /错误字段/正确字段，来进行替换操作进行修改。
只有在Linux平台使用
! 相当于 host ，没有断连接，只是切换了一下，执行shell命令
（2）edit命令来编辑最后一条sql语句。
3、sqlplus设置
set pause on 回车响应，分屏显示，只在本会话中有效
set pause off 关闭分屏显示。
set pause "..." 设置分屏显示的提示信息。
set pause on 先输出提示信息，回车响应，分屏显示
set head off 提头输出关闭
set feed off 结尾输出关闭
set echo off 回写关闭
spool 文件名.sql 写入指定文件
spool off 关闭写入。
4、sql脚本
也就是在文件中写有sql语句的文件，可以在sqlplus中运行。
引入sql脚本
sqlplus 用户名/密码 @sql脚本 （注意：在用户名密码输入结束后一定要加空格然后再写@sql脚本）
在脚本中最后一行写上“exit”，则运行完脚本以后，回到shell上
5、
Oracle中的空值 空值会当无穷大处理，其实空值根本就不会存储，只是看作是无穷大。
Oracle中控制处理函数 NVL(字段名，值)，这个字段中的空值替换为指定值，如果不为空，则会返回其原值。
例：select first_name,salary*12*(1+NVL(commission_pct,0)/100) "total salary" from s_emp;
distinct关键字，去掉重复行（这个关键字会触发排序操作）
例： select distinct dept_id,title from s_emp;
distinct 可以使dept_id与title的联合唯一
注意：distinct，关键字之后会对from之前的字段进行排重操作。
六、选择操作
1、order by 排序是按照存储顺序输出的，order by后面可以跟字段名，别名，位置，也可以有多个字段，当第一个字段的值一样时，按第二个字段排序
排序子句 ASC（默认，升序） DESC（降序）
order by 目标列名（别名） 排序顺序（不写排序顺序，会默认为升序排序）
例：select first_name from s_emp order by first_name;
select first_name from s_emp order by first_name desc;
注意：升序空值在结果的末尾，降序空值在结果的最前面。
oracle day02 总结
1、where子句（选择操作）
where子句使用在 select ... from ... 后面，用来选择所需（符合条件的）的记录
例：查询George的工资
select first_name,salary from s_emp where first_name='George';
where后面跟的是表达式 也就是 XXX=XXX， XXX between X and X ，XXX in（X，X，X）
like '...' 通配查询
between ... and ... ,表示结果在这之间，between and是一个闭区间，也就相当于... <= ... and ... >= ... 。
!=，<>，^=，这三个都标识不等于，<=，>=，=，这些运算符都可以使用。
... in (va1,val2,...) 判断结果是否在这个集合中存在，等于其中随变一个就行了，可以说in=any
in中集合的数据是有顺序的，概率高的数据放在前面可提高效率
not in<>all 没有顺序
空值会对not in造成影响，也就是不等于任何值，但是空值例外。
like '...' 字符串通配查询，'%'表示0或任意多个字符，'_'，表示任意一个字符。
注意：转义的用法：like ‘S/_%’ escape ‘/’
例：找出表名是以s_开头的表
select table_name from user_tables where table_name like 'S/_%' escape '/'; 把/定义为转义字符
... is null 使用来判断值是否为空。is not null 判断值不为空
例：查看哪些人没有提成
select first_name from s_emp where commission_pct is null;
... and ... 表示只有两个条件同时满足
... or ... 表示条件只要满足其中只一就可以
all ... 是要求都满足条件。
注意：Oracle中的字符串是严格区分大小写的。
（1）注意数据类型，数字类型直接写，字符用'......' ，缺省格式的Date可以用'......'，只有别名才用" "包含。
（2）选择合适的运算符
2、单行函数（一个输入，一个输出）
多行函数又叫组函数（多个输入，一个输出）例平均值
1.字符函数
字符是大小写敏感的
转小写 lower(字段名) --- 其中的参数可以是一个字符串常量或是一个字段名
转大写 upper(字段名)
首字母大写 initcap(字段名)
字符串拼接 concat(字段1, 字段2)
截取子串 substr(字段名, 起始位置，取字符个数)
返回字符串长度length(字符串)
dual表，是专门用于函数测试和运算的，他只有一条记录
可以使用"-"表示从右向左取，取的时候可以从左往右取。
例：select substr(first_name,-2,2) sub from s_emp;（取后两个）
select substr(first_name,2,2) sub from s_emp;（取前两个）
nvl是一般函数，支持任何数据类型
2.数值函数
四舍五入 round(数据,保留小数点后几位)
可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1表示个位（保留到十位）。
例：select round(15.36,1) from dual;
截取数字函数 trunc(数据，保留的位数（小数点后位数）) 截取个位之后补0
例：select trunc(123.456,1) from dual;
3.日期函数
日期格式，
全日期格式 世纪信息，年月日，时分秒。
缺省日期格式，日-月-年 dd-mon-rr
修改当前会话的日期格式，会按照指定的格式输出日期
alter session set nls_date_format='yyyy mm dd hh24:mi:ss';
返回当前日期 sysdate
例：select sysdate from dual;
select sysdate+1 from dual; 获得明天的日期，加1，单位是天
例: 10分钟以后的时间 select sysdate,sysdate+1/144 from dual;
日期是格式敏感的
求两个日期间相隔了多少个月 months_between(date1,date2)
加减指定数量的月份 add_months(date,月数)，月数可以为负，负值就是减去相应的月数。
从date日期开始的第一个星期五 next_day(date,FriDay)
返回月末的日期 last_day(date)
round 不能使用缺省的日期格式
例：select round(to_date('02-JAN-07','DD-MM-RR'),'MONTH') from dual;
截取日期 trunc(date,'年或月或日或时分秒')
例：select next_day(sysdate,2) from dual;
例：select trunc(add_months(sysdate,1),'month') from dual;
练习：
返回下个月的第一天的日期
select round(last_day(sysdate),'MONTH') from dual;
select add_months(trunc(sysdate,'MONTH'),1) from dual;
select trunc(add_months(sysdate,1),'MONTH') from dual;
4.不同数据类型间转换函数
将日期转成字符 tochar(date,'日期格式')
日期格式要用有效格式，格式大小写敏感 'yyyy mm dd hh24:mi:ss'（完整的日期可格式）
year'(全拼的年),'mm'(2位数字表示的月) 'month'(全拼的月)，'dy'(3位字母表示的星期) ，'day'(星期的全拼)，'ddspth' (日期的序数词)
例：显示完整的系统日期
select to_char(sysdate,'yyyy mm dd hh24:mi:ss')from dual;
查询3号入职的员工
select first_name,start_date from s_emp where to_char(start_date,'dd')='03';
select first_name,start_date from s_emp where to_char(start_date,'fmdd')='3'; 'fm'是去掉前导0
向表中插入日期(test是表名)
insert into test values('01-JAN-07');
insert into test values(to_date('2007 01 01 10:10:10','yyyy mm dd hh24:mi:ss'));
将字符转换成数字 to_number('...') ,缺省是按十进制来算，字符串只能是0---9的数字
select to_number('ab','xx') from dual; 把ab转成十六进制
将数字转字符to_char(number，'fmt') fmt是数字格式
select to_char(salary,'$99,999.99') from s_emp;
select to_char(salary,'$00,000.00') from s_emp; ##########表示越界，大于显示宽度
select to_char(salary,'L00,000.00') from s_emp;
将字符串转成日期 to_date('...','日期格式')
例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；
3、多表查询
表连接（关联查寻）
如果多表查询时不加where子句，也就是过滤条件或者是使用了无效的条件，就会产生两表之间记录的相互逐条匹配（组合），产生很多无效的结果（笛卡尔积）。
注意：在使用表连接时，要注意查询的表间的关系信息，表之间的字段所表示的信息的关系
1、等值连接
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表2 表别名2
where 表别名1.字段名3=表别名2.字段名4;
表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用
表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。
注意：当为表起了别名，就不能再使用表名.字段名。
例：列出所有Sales部门的员工
select e.first_name,d.name
from s_emp e,s_dept d
where e.dept_id=d.id and d.name='Sales';
列出每个员工的名称，他所在的部门名称，及地区名称
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;
执行脚本
cd $ORACLE_HOME
cd sqlplus
cd demo
sqlplus sd0703/sd0703 @demobld.sql
2、非等值连接
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表2 表别名2
where 表别名1.字段名3 ..... 表别名2.字段名4
....可以使比较运算符，也可以使其他的除了'='的运算符
例：列出每个员工的工资和他的工资级别
select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;
3、自连接
用别名把一张表中的数据分成两部分，然后在使用条件过滤。
select [表别名1.字段名1]，[表别名2.字段名2],...
from 表1 表别名1 ，表1 表别名2
where 表别名1.字段名3=表别名2.字段名4;
例：列出每个员工的经理名
select a.first_name ename,b.first_name cname
from s_emp a,s_emp b
where a.manager_id=b.id;
以上所提到的表连接，都叫做内连接，严格匹配两表的记录。
4、外连接（先做连接再做过滤）
会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。
例：select a.first_name enamei,a.id,b.first_name cname,b.id
from s_emp a,s_emp b
where a.manager_id=b.id(+);
即用a表中的数据去匹配b表的，若b表中有null，系统模拟纪录与其匹配
注意：要把那一方的记录全部都显示出来，还有注意条件(+)跟在要全部选出的对端。
外连接的应用：
员工都分布在哪些部门
select distinct deptno from emp;
列出哪个部门没有员工
select e.deptno,d.deptno
from emp e,dept d
where e.deptno(+)=d.deptno
and e.deptno is null;
Oracle day3 总结
组函数
avg(..),求平均值 sum(..),求和 这两个函数的参数只能是number型的。
例：求所有人的提成的平均值
select avg(nvl(commission_pct,0)) from s_emp;
求有多少人有提成
select count(commission_pct) from s_emp;
求员工都分布在多少个不同的部门
select count(distinct(dept_id)) from s_emp;
以下所提到的函数可以使用任意类型做参数。
count(..)，用来统计记录数，可以使用排重命令。count(...)默认使用的是all。
max(..),min(..)求最大值和最小值，
注意：组函数可以处理一组数据，返回一个值。
所有的组函数会忽略空值。
count(*),统计表中记录数，不忽略空值。
group 组
group by 分组子句，按指定的分组规则分组 ，这个group by 子句可以跟在 select 语句后或是 having后面。
group by子句也会出发排序操作，会按分组字段排序。
select [组函数或分组的字段名] ，... from 表名 group by [字段名1],[字段名2],.....；
例：找出各个部门的平均工资
select dept_id,avg(salary)
from s_emp;
group by dept_id;
在没有group by时，select后不能把普通字段和组函数同时使用
求各个部门不同职位有多少人
select dept_id,title,count(*)
from s_emp
group by dept_id,title;
求平均工资大于2000的部门
select dept_id,avg(salary)
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 max(d.name),max(r.name),avg(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 dept_id;
注意：只要写了group by子句，select后就只能用group by后的字段或者是组函数。
where子句只能够过滤记录，放单行函数。
having子句可以过滤组函数结果或是分组的信息，且写在group by子句后。
注意：要先过滤掉不需要的记录，然后再进行分组操作，提高效率。
四、子查询
子查询，就是可以嵌在任何的sql语句中的select语句,子查询也会触发排序
在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。
注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值，in）。
配合使用子查询返回的结果必须符合运算符的用法。
例:
求谁的工资是最低的
select first_name,salary
from s_emp
where salary =(select min(salary)
from s_emp
);
求谁和Smith是做一样工作的
select first_name,title
from s_emp
where title in (select title
from s_emp
where last_name='Smith')
求哪些人是普通员工
select first_name from s_emp
where id not in (select manager_id from s_emp
where manager_id is not null)
求哪些人是经理
select first_name from s_emp
where id in (select manager_id from s_emp)
from后面也可以用子查询
例：找出哪个人的平均工资比本部门的平均工资高
select e.first_name,e.salary,a.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;
重点掌握的三种形式：
1、哪个部门没有员工
select e.deptno,d.deptno
from emp e,dept d
where e.deptno(+)=d.deptno and e.deptno is null;
2、求各部门的平均工资
select max(d.name),max(r.name),avg(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 dept_id;
3、找出哪个人的平均工资比本部门的平均工资高
select e.first_name,e.salary,a.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;
将业务需求转换成可操作的表
一: 需求分析
二: 画E-R图（E entity 实体, R relation 关系）
三: 转换成表关系
四: 割接（新老系统交接）
五: 生产,试运行
E-R工具：Erwin
E-R图属性:
* 为强制的非空属性
o 可选属性(可以有值也可以没有)
#* 表示此属性唯一且非空
# 表示唯一的
实体关系:
mastbean maybean
数量关系: 一对一关系
一对多关系
多对一关系
多对多关系
第一范式，所有的属性都必须是单值，也就是属性只表示单一的意义。（记录可以重复，没有任何限制）
第二范式，属性要求唯一且非空，（记录不可重复，但是数据可能会出现冗余）。
第三范式，非主属性只能依赖于主属性，不能依赖于其他非主属性。（解决数据冗余问题）
六、约束
约束是针对表中的字段进行定义的。
primary key （主键约束 PK）保证实体的完整性，保证记录的唯一
主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，只有两个字段放在一起唯一标识记录，叫做联合主键。
foreign key （外建约束 FK）保证引用的完整性，
外键约束，外键的取值是受另外一张表中的主键或唯一值得约束，不能够取其他值，只能够引用主键会唯一键的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。
unuque key（唯一键，可以为空 UK），值为唯一
index（索引）是数据库特有的一类对象，view（视图）
典型的一对多 class 对应多个学生。
student table class table
______________________________ _________________________
| id | name | address| class_id| | id |class_desc|class_num|
|(PK)|______|________|___(FK)__| |(pk)|__________|_________|
| | | | | | | | |
一对一
student tabel shenfenzheng table
____________________ _________________________________
| id | name | address| | s_id |shenfen_desc|shenfen_num|
|(PK)|______|________| |(PK，FK)|____________|___________|
| | | | | | | |
多对多
student tabel zhongjian table kecheng table
____________________ _________________________________ __________________
| id | name | address| | s_id |shenfen_desc|shenfen_num| | kid | kechengname|
|(PK)|______|________| |(FK，FK)|____________|___________| | (PK)|____________|
| | | | |联合主键| | | | | |
引用对方表的主键,当作本身的主键,所以这个表的主键,既是主键又是外建
建表和其他相关操作
DDL语句
创建表：
create table 表名 ( 字段名1 类型(数据长度)(default ...) 约束条件， 字段名2 类型(数据长度) 约束条件 );
Oracle数据库中的数据类型
varchar(长度)，可变长字符串，按照实际长度存储 char(长度) 定长字符串存储
number(..,..),number 表示浮点数，或者是整数
LOB（large object) 大对象 不能直接用insert插入
CLOB 字符的大对象，存大文本
BLOB 二进制的大对象，也是以相当于指针的形式存放的。
data block数据块
create table test(c1 number default 1,c2 number); 没有插入的时候会用默认值
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) ) ; 用表级约束可以实现联合主键
处理错误
（1）!oerr ora [错误号] ，系统可以显示错误的原因和如何修改。如果命令错误输入可以使用edit或ed来修改输入错误。
实际上是在编辑缓存文件中的最后一条sql语句。
也可以使用 (change) c /错误字段/正确字段，来进行替换操作进行修改。
只有在Linux平台使用
! 相当于 host ，没有断连接，只是切换了一下，执行shell命令
（2）edit命令来编辑最后一条sql语句。
Oracle day4 总结
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));
如果两个字段都为唯一且非空,这时可以定义成UK+NOT NULL
(PK或UK)一对多(FK)
(PK+UK)一对一(FK) 或 (PK)一对一(PK)
多对对多关系,一般都通过一张中间表来分解成两个一对多的表
非空约束是唯一一个可以用desc看到的约束
check 约束
create table test(c1 number primary key); 设置主键
create table test(c1 number constraints test_c1 primary key); 定义约束名,默认约束名为SYS_ 在列后面定义约束称为列级约束
create table test(c1 number primary key(c1)); 所有列定义完后再定义约束称为表级约束(能定义联合主键)
cretae table test(c1 number,c2 number,priary key(c1,c2)); 定义联合主键
create table test(c1 number primary key,c2 number unique); unique 唯一约束
create table test(c1 number primary key,c2 number not null unique); 一个字段上可以加多个约束
create table test(c1 number check(c1>100)); 约束c1的值大于100 ，列级约束
create table test(c1 number,check(c1>100)); 表级约束
create table child(c1 number primary key); 先要定义父表
create table child(c1 number primary key, c2 number references parent(c1)); 然后定义子表 references parent定义外键
create table child(c1 number primary key, c2 number references parent(c1) on delete cascade); on delete cascade为级联删除,删除父表时子表也被删除
create table child(c1 number primary key, c2 number references parent(c1) on delete set null); on delete set null删除后将外键置空
create table child (c1 number primary key, c2 number,foreignkey(c2) references parent(c1));
DML操作
online transaction procession OLTP 联机事务处理
1、insert操作，插入记录
insert into 表名 values(值1，值2，......);
注意这种方法插入记录时，要对所有字段进行插入，没有非空约束时，又不想插入值时，要用空值替代，并且要按照字段的顺序插值（要清楚表结构），且要注意数据类型一致。
insert into 表名(字段名1，字段名2，.....) values(值1，值2，......);
这种方法可以对指定的字段进行插入，不想插值的就可以不写，前提是该字段没有非空约束。
例：insert into student value(1,'xxx','xxx');
insert into student(id,name,address) value(1,'xxx','xxx');
注意：有空值的话:
隐式插入
INSERT INTO s_dept (id, name) VALUES (12, 'MIS');
不往想为空的字段中插数据，系统默认为NULL
显示插入
INSERT INTO s_dept VALUES (13, 'Administration', NULL);
select * from s_emp where 1=2; 这样选不出纪录，方便察看表结构
2、update修改操作
update table 表名 set 字段名1=数据1或表达式1， 字段名2=数据2或表达式2
[where ....=....];
例：update shenfenzhen set num=99 where sid=2;
3、delete删除操作
delete from 表名 [where ...=...];
例：update shenfenzhen set num=99 where sid=2;
用delete 删除一张大表会花很长的时间。
用delete操作删除的记录可以通过 rollback命令回滚操作，会恢复delete操作删除的数据。
delete操作不会释放表所占用的空间，delete不适合删除记录多的大表。
delete操作会占用大量的系统资源。
事务
OLTP （ online transaction procession ） 联机事务处理
数据库中操作的应是事务，而不是DML语句
事务是有生命周期的，commit；事务结束
系统中充满了并发的transation,每个连接是一个session，每个操作都是一个transaction
DDL、DCL语句是自动提交的
sqlplus正常退出（exit），系统自动提交
上个事务的结束就是下个事务的开始
事务保证数据的一致性，保证原子操作的安全
一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据，只有本会话才能看见。
read committed，只可以读取已经做提交操作的数据，本会话可以看到自己的所作的没有提交的操作。
在活动事务中，当多个用户同时对同一张表进行操作时，会对表加上表级共享锁，当用户对操作该表某一条记录进行操作时会对该条记录加上行级排它锁，
只允许一个用户对该条记录进行DML操作，只有提交操作commit;或回滚操作rollback;时，才可让其他用户操作对该记录进行DML操作，也就是释放了该条
记录的行级排它锁。如果没有提交操作或回滚操作，那么该用户就不能对该条记录加锁，该用户的DML操作就会进入等待状态，但是在对表作drop操作（DDL操作）时，
如果还有用户在操作该表，也就是没有释放表级共享锁，就会直接报错。
事务越大，就会消耗更多的资源，并长时间持有事务会造成无法进行其他的操作，事物提交太频繁的话，会对I/O造成很大的负担，所以要合理确定事务的大小。
commit;提交操作，事物的结束
rollback;回滚操作，会将先前的活动事务中的操作（DML操作）的结果进行回滚，撤销全部操作，恢复成事务开始时的数据，也就是恢复成事务开始时的状态。
alter table命令
alter table 命令用于修改表的结构(这些命令不会经常用)：
增加字段：
alter table 表名 add(字段字，字段类型)
删除字段：
alter tbale 表名 drop column 字段; (8i 以后才支持)
给列改名:9.2.0才支持
alter table 表名 rename column 旧字段名 to 新字段名;
修改字段
alter table 表名 modify( 字段，类型)
(此时应注意的问题，更改时要看具体值情况之间的转达换， 改为字符类型时，必须要为空)
not null约束是使用alter table .. modify (.. not null)，来加上的。
alter table test modify(c11 null);
增加约束：
alter table 表名 add constraint [约束名] 约束(字段);
只能够增加表级约束。
解除约束：(删除约束)
alter table 表名 drop 约束;
alter table 表名 drop primary key;
（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）
alter table father drop primary key cascade;
(如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）
数据字典
数据字典是由系统维护的，包含的数据库的信息
数据字典示图
user_XXXXX 用户示图
all_XXXXX 所有示图
dba_XXXXX 数据库中所有示图
v$_XXXXX 动态性能示图
dict 表示数据字典的数据字典。
user_constraints 用户的表中约束的表
其中有constraint_name字段存放的是约束名，r_constraint_name字段表示外键引用pk或uk的名字
这两个字段之间有自连接的关系，也就是约束名和外键约束名之间的自连接。
user_cons_columns表，是用户的列级约束表
U 代表UK,R (references)代表FK,C 代表check和NOT NULL，P代表PK
找出s_emp表的主键约束
select c.constraint_name,c.constraint_type,cc.column_name
from user_constraints c,user_cons_columns cc
where c.constraint_name=cc.constraint_name and c.table_name=cc.table_name and c.table_name='S_EMP' and c.constraint_type='P';
练习： 列出每一个表的外键的定义，主表表名，主表字段名，子表表名，子表字段名 （画出E-R图）
alter table 表名 drop constraint 约束名;
(怎样取一个约束名：
a、人为的违反约束规定根据错误信息获取!
b、查询示图获取约束名!)
使约束失效或者生效
alter table 表名 disable from primary key; (相当于把一个表的主键禁用)
alter table 表名 enable primary key;
（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）
Oracle day5 总结
删除表：
drop table 表名;
drop table 表名 cascade constraints; 级联删除
trucate table 表名;
(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表)
更改表名
rename 旧表名 to 新表名;
索引（index）
创建索引：Creating indexes(概念很重要对系统的性能影响非常大）
建索引的目的就是为了加快查询速度。
索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。索引是会进行排序。
truncate 表时索引结构在，但是数据不存在。
FTS --- full table scan 全表扫描
用索引就是为了快速定位数据：(理解时就以字典的目录为例)
创建索引就是创建key和记录的物理位置（rowid）组成的键值对。索引是有独立的存储空间,但是和表是逻辑关联的,索引和表的关系是依附关系,表被删除了,索引也没有存在的意义也就被删除了
在建表时会根据表中的PK或UK自动的建立唯一性索引。
查看表的rowid：
select rowid,first_name from s_emp;
rowid 定义的信息有：object block table
每条记录都有自己的rowid
索引由谁创建：用户,建索引后会使DML操作效率慢，但是对用户查询会提高效率,这就是我们建索引的最终目的。
创建一个索引：
create index 索引名 on 表名 (字段名);
create index testindex on test(c1, c2);
索引分为唯一性索引，联合索引。索引中是不会维护空值的。
哪些字段应该建索引：创建索引就是为了减少物理读，索引会减少扫描的时间。
经常要用where的子句的地方，所以要用索引.用不用索引，关键要看所查询的数据与所有数据的百分比，表越大，查询的记录越少，索引的效率就越高.
替换变量：用&符号来定义替换变量支持交互性提示,对于字符性的数字，一定要写在单引号之间
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';
关于oralce中产生序列(sequence) 可以自动产生唯一值
create sequence 序列名;
(不带参数时默认为从1 开始每次递增 1，oracle中为了提高产生序列的效率一般一次性产生20个序列放入当前会话的序列池中备用以加快效率，序列会出现不连续的动作回退操作不会影响序列取值)
sequence 的参数：
increment by n 递增量
start with n 起始值
maxvalue n 最大值 nomaxvalue 定义的最大值
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时取当前值不成功时，应该先取下一个值，再取当前值)
清空当前会话的内存：
alter system flush shared_pool;（执行此命令要有DBA权限，一般用户执行出错）
修改序列：(此命令不常用，只需了解就行不必深究)
alter sequence 序列名 修改项；
删除序列sequence
drop sequence 序列名;
视图
创建视图：
creating views 视图名;
视图就相当于一条select 语句,定义了一个视图就是定义了一个sql语句,视图不占空间,使用view 不会提高性能，但是能简单化sql语句
（扩展知识： oracle 8i 以后的新示图）
MV 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题，物化示图中的数据存在延迟问题，主要应用在数据仓库中用要用于聚合表)
使用视图的好处：控制数据访问权限。
如何创建一个视图的例子：
create or replace view test_vi as select * from test1 where c1=1;
or replace的意义，如果view存在就覆盖，不存在才创建。
force|no force ，基表存在是使用，不存在是则创建该表。
此时往表test1（base table 基表）中插入数据时：表中没能变化，视图中的数据发生改变
从示图中插数据时相对应的表会发生改变:
往示图中插数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创建时的select语句。
限制对数据库的访问，简化查询。
简单视图：来自于单表，且select语句中不能包括函数，能进行DML操作。
复杂视图：来源于多张表，不能执行DML操作。
视图的约束
with read only 视图只读约束(O)
with check option 不允许插入与where条件不符的记录，类似于check约束的功能(V)
在select from 后也可以使用子查寻，这个写法也叫做内嵌视图
例：
select first_name,salary,avgsal from s_emp e,(select dept_id,avg(salary) avgsal from s_emp group by dept_id) s where e.dept_id=s.dept_id and e.salary>s.avgsal;
删除视图 drop views 示图名;
行号（rownum）
关于rownum：
rownum 有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。
rownum常用于分页显示。
rownum只用于读入内存的数据。
找出工资前三名的员工
select first_name,salary
from (select first_name,salary from s_emp order by salary desc)
where rownum<=3;
列出每一个表的外键的定义，主表表名，主表字段名，子表表名，子表字段名 （画出E-R图）
select c.table_name,cc.column_name,
p.table_name,pc.column_name,
p.constraint_type
from user_constraints c,user_cons_columns cc,user_constraints p,user_cons_columns pc
where c.constraint_name=cc.constraint_name
and p.constraint_name=pc.constraint_name
and c.r_constraint_name=p.constraint_name
and c.table_name='S_EMP'
and c.constraint_type='R';
动态生成脚本
set head off
set feed off
set echo off
spool seletab.sql
select 'select * from '||table_name||';'
from user_tables;
spool off
四大数据库的比较（SQL Server、Oracle、Sybase和DB2）
转载
开放性:
SQL Server
只能在windows上运行，没有丝毫的开放性，操作系统的系统的稳定对数据库是十分重要的。Windows9X系列产品是偏重于桌面应用，NT server只适合中小型企业。而且windows平台的可靠性，安全性和伸缩性是非常有限的。它不象unix那样久经考验，尤其是在处理大数据
Oracle
能在所有主流平台上运行（包括 windows）。完全支持所有的工业标准。采用完全开放策略。可以使客户选择最适合的解决方案。对开发商全力支持。
Sybase ASE
能在所有主流平台上运行（包括 windows）。 但由于早期Sybase与OS集成度不高，因此VERSION 11.9.2以下版本需要较多OS和 DB级补丁。在多平台的混合环境中，会有一定问题。
DB2
能在所有主流平台上运行（包括windows）。最适于海量数据。DB2在企业级的应用最为广泛，在全球的500家最大的企业中,几乎85%以上用DB2数据库服务器，而国内到97年约占5%。
可伸缩性，并行性
SQL server
并行实施和共存模型并不成熟。很难处理日益增多的用户数和数据卷。伸缩性有限。
Oracle
并行服务器通过使一组结点共享同一簇中的工作来扩展windownt的能力，提供高可用性和高伸缩性的簇的解决方案。如果windowsNT不能满足需要，用户可以把数据库移到UNIX中。 Oracle的并行服务器对各种UNIX平台的集群机制都有着相当高的集成度。
Sybase ASE
虽然有DB SWITCH来支持其并行服务器，但由于DB SWITCH在技术层面还未成熟，且只支
持版本12.5以上的ASE SERVER，因为DB SWITCH技术需要一台服务器充当SWITCH，从而在硬件
DB2
具有很好的并行性。DB2把数据库管理扩充到了并行的、多节点的环境。数据库分区是数据库的一部分，包含自己的数据、索引、配置文件、和事务日志。数据库分区有时被称为节点
安全性
SQL server
没有获得任何安全证书。
Oracle Server
获得最高认证级别的ISO标准认证。
Sybase ASE
获得最高认证级别的ISO标准认证。
DB2
获得最高认证级别的ISO标准认证。
性能
SQL Server
多用户时性能不佳
Oracle
性能最高， 保持开放平台下的TPC-D和TPC-C的世界记录。
Sybase ASE
性能接近于 SQL Server。但在UNIX平台下的并发性要优与 SQL Server。
DB2
性能较高适用于数据仓库和在线事物处理。
客户端支持及应用模式
SQL Server
C/S结构，只支持windows客户，可以用ADO,DAO,OLEDB,ODBC连接.
Oracle
多层次网络计算，支持多种工业标准，可以用ODBC，JDBC，OCI等网络客户连接。
Sybase ASE
C/S结构，可以用ODBC，Jconnect，Ct-library等网络客户连接。
DB2
跨平台，多层结构，支持ODBC,JDBC等客户
操作简便
SQL Server
操作简单，但只有图形界面。
Oracle
较复杂，同时提供GUI和命令行，在windowsNT和unix下操作相同
Sybase ASE
较复杂，同时提供GUI和命令行。但GUI较差，常常无法及时状态，建议使用命令行。
DB2
操作简单，同时提供GUI和命令行，在windowsNT和unix下操作相同
使用风险
SQL server
完全重写的代码，经历了长期的测试，不断延迟，许多功能需要时间来证明。并不十分兼
Oracle
长时间的开发经验，完全向下兼容。得到广泛的应用。完全没有风险。
Sybase ASE
向下兼容, 但是ct-library 程序不益移植。
DB2
在巨型企业得到广泛的应用，向下兼容性好。风险小。
day01 PL/SQL
1.pro*c/c++ :在C或C++中嵌入SQL语句
2.SQLJ插入到JAVA语言中.
3.PL/SQL专门用来访问Oracle数据库.
4.Procedural Language/sql
a.扩展: 1)变量和类型
2)控制结构
3)过程和函数
4)对象类型和方法
5.程序结构:
由PL/SQL块组成.
DECLARE
变量,游标等的声明(可选)
BEGIN
...执行主体
EXCEPTION
......异常主体(可选)
END
6.标识符
1,命名规则
字母开头
后跟任意的非空格字符,数字,货币符号,下划线,或#
最大的长度不超过30个字符
7.分界符:
**指数操作符 例如2**3:=8
"<>,!=,~=,^="这些是不等于号
":="这是复值符号
"="用来判断表达式是否相等
"||"这是字符串连接符 也可以用一个字符串连接一个数字
";"语句结束符
"%"属性的操作符
"<<"标号的起始符
">>"标号的结束符
--(单行),/**/(多行) 注释符
"and"与
"or"或
"NOT"非
<space><tab> 表示空白
8.文字
1.字符型文字
'Tom''s pen','hello,world','1234'
注当" ' "是字符串的一部分则用两个单引号
eg:''''这个代表一个单引号
2.数字型文字
123, -4, +56, 0, 9.0, 1.23E5, 9.12e4
3.布尔值
true,false,NULL
9,变量声明
变量的名字 变量的类型
例:v_firstName varchar2(20) NOT NULL :='123',--如果字符变量没有被赋值则不能加NOT NULL
ｖ_Ｎum number :=45 -- 初始化也可以用 default 45
常量声明:CONSTANT[NOT nullＬ] +变量类型
10,数据类型
a.标量类型
1.数字类型:BINARY_INTEGER,NUMBER(他的子类有:FLOAT,DEC,REAL,...等)
1)eg: nubmer(不以二进制保存)(用来保存或更新数据库表中的数据是用nubmer,对表进行交互)
number(3)
nubmer(4,3)表示这个数字位的有效位数是4,精度为3 1.234.
How about number(4,6) 可以这样理解0.001234 即4位有效位,小数位为6位
2)BINARY_INTEGER只能用来保存整数(底层以二进制进行保存)( 不 用来保存或更新数据库表中的数据
,而是用来计算
2.字符类型:CHAR VARCHAR2,VARCHAR
VARCHAR2(Oracle 标准) 与VARCHAR(NIIC 标准) 俩者等价,他们是变长的.string 是varchar地子类.
a VARCHAR2(20) :='abc' --则a的长度是3.
CHAR 定长eg; a char(20) :='122 -- 则a 的长度是20.
Long:在PL/SQL中使用
3) BOOLEAN
v_boolean boolean; --这里v_boolean:=null;
4,DATE:Oracle 9i之后增加了TIMESTAMP(除年,/月,日,时分秒,还包括秒的小数部分,INTERVAL(两个日期的时间差)两种
片段:
eg:
begin
--------------------------------------------------------------------------------------------------
1.dbms_output.put_line('Tom''s pen');--这表示输出:Tom's pen
end;
/ --表执行
--------------------------------------------------------------------------------------------------
2.set serveroutput on--输出到屏幕,这个在我们进入SQL/PLUS之后就输它.
/
----------------------------------------------------------------------------------------------------------------------------
3.怎样将PL/SQL语句保存到文件中.
edit 文件名
declare
v_fname varchar2(25);
v_dept_id number(7);
v_salary number(11,2);
begin
select first_name,dept_id,salary
into v_fname,v_dept_id,v_salary
from s_emp
where id=1;
----------------------------------------------------------------------------------------------------------------------------
4.%TYPE当表中的某个字段结构发生变化是使用.
ed:v_first_name student.first_name%TYPE--这里STUDNET中的first_name的长度改变则v_first_name 的长度也会改变.
例如:
declare
v_fname s-emp.first_name%TYPE;
v_dept_id s_emp.dept_id%TYPE;
v_salary s_emp.salary%TYPE;
begin
select first_name,dept_id,salary
into v_fname,v_dept_id,v_salary
from s_emp
where id=1;
----------------------------------------------------------------------------------------------------------------------------
b.组合类型
----------------------------------------------------------------------------------------------------------------------------
1)RECORD类型(记录类型):
----------------------------------------------------------------------------------------------------------------------------
声明:
a) TYPE 名字 is RECORD{
....
}
例如:
declare
TYPE T_EMP IS RECORD(
id S_EMP.id%TYPE,
last_name s_emp.last_name%TYPE,
first_name s_emp.first_name%TYPE,
......................
);
v_emp t_emp;
m_emp t_emp;
begin
select *
into v_emp//如果我查的是表中的所有字段就可以这样写要求顺序与t_emp中的相同.否则v_emp会出错.
from s_emp
where id=1;
dbms_output.put_line(v_emp.id||' '||v_emp.first_name);
end;
注:相同类型的变量可以直接赋值.
v_emp :=m_emp;(如上所示)
如果不同,则要一个字段一个字段的赋值.
上面的方法不适合表的结构发生变化的情况.
----------------------------------------------------------------------------------------------------------------------------
2)%ROWTYPE:返回一个基于数据库表/定义的类型.
当表结构发生变化了,则ROWTYPE结构也要发生变化.
例如:
declare
v_emp s_emp%ROWTYPE;
begin
select *
into v_emp//如果我查的是表中的所有字段就可以这样写.否则v_emp
from s_emp
where id=1;
dbms_output.put_line(v_emp.id||' '||v_emp.first_name);
end;
----------------------------------------------------------------------------------------------------------------------------
2) table 类型
TYPE 名字 is TABLE OF type INDEX BY BINARY_INTEGER;
注BINARY_INTEGER的范围是:--------------
例如:
DECLARE
TYPE t_emp IS TABLE OF s_emp(表) %ROWTYPE INDEX BY BINARY_INTEGER;
v_emp t_emp;
begin
select *
INTO v_emp(100)--这里的100是一个下标
form s_emp
where id=1;
SELECT *
INTO v_emp(200)
FROM s_emp
WHERE id=2;
dbms_output.put_line(v_emp(100).id||' '|| v_emp(100).first_name);
dbms_output.put_line(v_emp(200).id||' '|| v_emp(200).first_name);
end;
----------------------------------------------------------------------------------------------------------------------------
3)变量的作用域与可见性:
----------------------------------------------------------------------------------------------------------------------------
4)条件语句
----------------------------------------------------------------------------------------------------------------------------
a)IF boolean_expression1 THEN
......
ELSIF boolean_expression2 THEN
......
ELSE
......
END IF;
例如:
注:条件为NULL 或false 结果相同
注:exception is NULL 判断是否为空
exception is NOT NULL不为空
----------------------------------------------------------------------------------------------------------------------------
5)循环语句
----------------------------------------------------------------------------------------------------------------------------
简单的循环:
a)Loop
....
IF boolean_expt THEN-这里的IF
EXIT;
END IF; --当...满足是退出循环.
或者
exit when bool_exp;--当...满足是退出循环.
End Loop;
b)WHILE
WHILE BOOLEAN_EXPT lOOP
----
END LOOP;
c)
----------------------------------------------------------------------------------------------------------------------------
c.参考类型
select cc.column_name,c.constraint_name,p.table_name,pc.column_name,p.constraint_name from user_cons_constraints cc,user_cons_constraints pc,user_constraints c,user_constraints p where cc.constraint_name=c.constraint_name and c.r_constraint_name=p.r_constraint_name and p.constraint_name=
pc.constraint_name and cc.column_name='manager_id' and c.table_name='S_EMP'
先写子查询再写主查询
实例:求最高工资的10-20行之间的记录
select rn,last_name,salary from(select rownum rn,last_name,salary from(select last_name,salary from
s_emp order by salary)
where rownum<=20) where rn between 10 and 20
求最低工资的人(标准的SQL子查询实现方法)
select last_name,salary from s_emp where salary=(select min(salary) from s_emp);
哪个部门的平均工资是最低的(标准的SQL子查询实现方法)
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)
哪个部门的平均工资是最低的(用rownum)
select dept_id,avgsal from(select dept_id,avg(salary)avgsal from s_emp group by dept_id order by avgsal) where rownum<=1
各个部门员工工资的排名
select dept_id,last_name,salary,row_number() over(partition by dept_id order by salary) rank
from s_emp
10部门工资涨10%,20部门的工资涨20%,30部门的工资涨30%
select last_name,salary,
case when dept_id=10 then salary*1.1
when dept_id=20 then salary*1.2
when dept_id=30 then salary*1.3
else salary
end "after_salary"
from s_emp;
用类似功能的函数(decode())的写法:
select first_name,salary,
decode(dept_id,10,salary*1.1,
20,salary*1.2,
30,salary*1.3,
salary) after_salary
from s_emp;
每个部门不同职位有多少人
select job,deptno,count(*)
from emp
group by job,deptno
行列转置问题(count和decode的用法)
select job,count(decode(deptno,10,1)) dept_10,
count(decode(deptno,20,1)) dept_20,
count(decode(deptno,30,1)) dept_30
from emp group by job
筛选结果如下:
JOB DEPT_10 DEPT_20 DEPT_30
--------- ---------- ---------- -------------------
ANALYST 0 2 0
CLERK 1 2 1
MANAGER 1 1 1
PRESIDENT 1 0 0
SALESMAN 0 0 4
课下作业:想办法把0的位置删掉,也就是置空
关系数据库支持集合操作:
并(AUB):
union all(有重复记录,所以不会触发排序操作)
union(无重复记录,所以会触发排序操作)
交:
intersect
差(A-B):
minus
sql*plus命令大全
SQL*PLUS命令的使用大全
Oracle的sql*plus是与oracle进行交互的客户端工具。在sql*plus中，可以运行sql*plus命令与sql*plus语句。
我们通常所说的DML、DDL、DCL语句都是sql*plus语句，它们执行完后，都可以保存在一个被称为sql buffer的内存区域中，并且只能保存一条最近执行的sql语句，我们可以对保存在sql buffer中的sql 语句进行修改，然后再次执行，sql*plus一般都与数据库打交道。
除了sql*plus语句，在sql*plus中执行的其它语句我们称之为sql*plus命令。它们执行完后，不保存在sql buffer的内存区域中，它们一般用来对输出的结果进行格式化显示，以便于制作报表。
下面就介绍一下一些常用的sql*plus命令：
1. 执行一个SQL脚本文件
SQL>start file_name
SQL>@ file_name
我们可以将多条sql语句保存在一个文本文件中，这样当要执行这个文件中的所有的sql语句时，用上面的任一命令即可，这类似于dos中的批处理。
2. 对当前的输入进行编辑
SQL>edit
3. 重新运行上一次运行的sql语句
SQL>/
4. 将显示的内容输出到指定文件
SQL> SPOOL file_name
在屏幕上的所有内容都包含在该文件中，包括你输入的sql语句。
5. 关闭spool输出
SQL> SPOOL OFF
只有关闭spool输出，才会在输出文件中看到输出的内容。
6．显示一个表的结构
SQL> desc table_name
7. COL命令：
主要格式化列的显示形式。
该命令有许多选项，具体如下：
COL[UMN] [{ column|expr} [ option ...]]
Option选项可以是如下的子句:
ALI[AS] alias
CLE[AR]
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
LIKE { expr|alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT]|PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
1). 改变缺省的列标题
COLUMN column_name HEADING column_heading
For example:
Sql>select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- ---------
10 ACCOUNTING NEW YORK
sql>col LOC heading location
sql>select * from dept;
DEPTNO DNAME location
--------- ---------------------------- -----------
10 ACCOUNTING NEW YORK
2). 将列名ENAME改为新列名EMPLOYEE NAME并将新列名放在两行上：
Sql>select * from emp
Department name Salary
---------- ---------- ----------
10 aaa 11
SQL> COLUMN ENAME HEADING ’Employee|Name’
Sql>select * from emp
Employee
Department name Salary
---------- ---------- ----------
10 aaa 11
note: the col heading turn into two lines from one line.
3). 改变列的显示长度：
FOR[MAT] format
Sql>select empno,ename,job from emp;
EMPNO ENAME JOB
---------- ---------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
Sql> col ename format a40
EMPNO ENAME JOB
---------- ---------------------------------------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
4). 设置列标题的对齐方式
JUS[TIFY] {L[EFT]|C[ENTER]|C[ENTRE]|R[IGHT]}
SQL> col ename justify center
SQL> /
EMPNO ENAME JOB
---------- ---------------------------------------- ---------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
对于NUMBER型的列，列标题缺省在右边，其它类型的列标题缺省在左边
5). 不让一个列显示在屏幕上
NOPRI[NT]|PRI[NT]
SQL> col job noprint
SQL> /
EMPNO ENAME
---------- ----------------------------------------
7369 SMITH
7499 ALLEN
7521 WARD
6). 格式化NUMBER类型列的显示：
SQL> COLUMN SAL FORMAT $99,990
SQL> /
Employee
Department Name Salary Commission
---------- ---------- --------- ----------
30 ALLEN $1,600 300
7). 显示列值时，如果列值为NULL值，用text值代替NULL值
COMM NUL[L] text
SQL>COL COMM NUL[L] text
8). 设置一个列的回绕方式
WRA[PPED]|WOR[D_WRAPPED]|TRU[NCATED]
COL1
--------------------
HOW ARE YOU?
SQL>COL COL1 FORMAT A5
SQL>COL COL1 WRAPPED
COL1
-----
HOW A
RE YO
U?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW A
9). 显示列的当前的显示属性值
SQL> COLUMN column_name
10). 将所有列的显示属性设为缺省值
SQL> CLEAR COLUMNS
8. 屏蔽掉一个列中显示的相同的值
BREAK ON break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO, ENAME, SAL
FROM EMP
WHERE SAL < 2500
ORDER BY DEPTNO;
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
9. 在上面屏蔽掉一个列中显示的相同的值的显示中，每当列值变化时在值变化之前插入n个空行。
BREAK ON break_column SKIP n
SQL> BREAK ON DEPTNO SKIP 1
SQL> /
DEPTNO ENAME SAL
---------- ----------- ---------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
10. 显示对BREAK的设置
SQL> BREAK
11. 删除6、7的设置
SQL> CLEAR BREAKS
12. Set 命令：
该命令包含许多子命令：
SET system_variable value
system_variable value 可以是如下的子句之一：
APPI[NFO]{ON|OFF|text}
ARRAY[SIZE] {15|n}
AUTO[COMMIT]{ON|OFF|IMM[EDIATE]|n}
AUTOP[RINT] {ON|OFF}
AUTORECOVERY [ON|OFF]
AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
BLO[CKTERMINATOR] {.|c}
CMDS[EP] {;|c|ON|OFF}
COLSEP {_|text}
COM[PATIBILITY]{V7|V8|NATIVE}
CON[CAT] {.|c|ON|OFF}
COPYC[OMMIT] {0|n}
COPYTYPECHECK {ON|OFF}
DEF[INE] {&|c|ON|OFF}
DESCRIBE [DEPTH {1|n|ALL}][LINENUM {ON|OFF}][INDENT {ON|OFF}]
ECHO {ON|OFF}
EDITF[ILE] file_name[.ext]
EMB[EDDED] {ON|OFF}
ESC[APE] {/|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}
FLAGGER {OFF|ENTRY |INTERMED[IATE]|FULL}
FLU[SH] {ON|OFF}
HEA[DING] {ON|OFF}
HEADS[EP] {||c|ON|OFF}
INSTANCE [instance_path|LOCAL]
LIN[ESIZE] {80|n}
LOBOF[FSET] {n|1}
LOGSOURCE [pathname]
LONG {80|n}
LONGC[HUNKSIZE] {80|n}
MARK[UP] HTML [ON|OFF] [HEAD text] [BODY text] [ENTMAP {ON|OFF}] [SPOOL
{ON|OFF}] [PRE[FORMAT] {ON|OFF}]
NEWP[AGE] {1|n|NONE}
NULL text
NUMF[ORMAT] format
NUM[WIDTH] {10|n}
PAGES[IZE] {24|n}
PAU[SE] {ON|OFF|text}
RECSEP {WR[APPED]|EA[CH]|OFF}
RECSEPCHAR {_|c}
SERVEROUT[PUT] {ON|OFF} [SIZE n] [FOR[MAT] {WRA[PPED]|WOR[D_
WRAPPED]|TRU[NCATED]}]
SHIFT[INOUT] {VIS[IBLE]|INV[ISIBLE]}
SHOW[MODE] {ON|OFF}
SQLBL[ANKLINES] {ON|OFF}
SQLC[ASE] {MIX[ED]|LO[WER]|UP[PER]}
SQLCO[NTINUE] {> |text}
SQLN[UMBER] {ON|OFF}
SQLPRE[FIX] {#|c}
SQLP[ROMPT] {SQL>|text}
SQLT[ERMINATOR] {;|c|ON|OFF}
SUF[FIX] {SQL|text}
TAB {ON|OFF}
TERM[OUT] {ON|OFF}
TI[ME] {ON|OFF}
TIMI[NG] {ON|OFF}
TRIM[OUT] {ON|OFF}
TRIMS[POOL] {ON|OFF}
UND[ERLINE] {-|c|ON|OFF}
VER[IFY] {ON|OFF}
WRA[P] {ON|OFF}
1). 设置当前session是否对修改的数据进行自动提交
SQL>SET AUTO[COMMIT] {ON|OFF|IMM[EDIATE]| n}
2)．在用start命令执行一个sql脚本时，是否显示脚本中正在执行的SQL语句
SQL> SET ECHO {ON|OFF}
3).是否显示当前sql语句查询或修改的行数
SQL> SET FEED[BACK] {6|n|ON|OFF}
默认只有结果大于6行时才显示结果的行数。如果set feedback 1 ，则不管查询到多少行都返回。当为off 时，一律不显示查询的行数
4).是否显示列标题
SQL> SET HEA[DING] {ON|OFF}
当set heading off 时，在每页的上面不显示列标题，而是以空白行代替
day 02
1.for 循环
for loop_counter in [reverse] low..high loop
end loop;
eg:
declare
v_emp s_emp%rowtype;
begin
for v_cnt in 1..5 loop --in 后+reverse 则打印顺序就会从5至1;
select *
into v_emp
from s_emp
where id:=v_cnt;
dbms_output.put_line(v_emp.id||' '||v_emp.first_name||' ');
end loop;
end;
----------------------------------------------------------------------------------------------------------------------------
2.null 语句
eg:
if v_idx>40 then
.......
else
null;
end if;
----------------------------------------------------------------------------------------------------------------------------
3.sql in pl/sql1
ddl 语句:
drop ,create ,alter,grant,revoke(要用本地的动态语句去使用这些语句)
dml 语句:
select insert,delete,set transaction 这些可以直接写在pl/sql中.
tc(事务控制语句):
commit,rollback,savepint()这些可以直接写在pl/sql中.
----------------------------------------------------------------------------------------------------------------------------
4.内置的sql 函数
a) 字符函数:
b) 日期函数:
c) 数字函数:
d) 转换函数:
----------------------------------------------------------------------------------------------------------------------------
5.游标(cursor):
1)a)用来提取多行的数据集
2)cursor分类
a)显示游标:
b)隐式游标:
3)显式cursor的使用:
1)声明游标
CURSOR cursor_name IS 查询语句;
2)打开游标
OPEN cursor_name;
3)提取数据
FETCH cursor_name INTO var1,var2......
4)关闭游标
CLOSE cursor_name;
例如:1
declare
v_emp s_emp%rowtype;
CURSOR cur_emp IS
select * from s_emp where dept_id=44; --这里不能有INTO子句.
begin
OPEN cur_emp; --游标指向第一个位置.
FETCH cur_emp INTO v_emp;
dbms.output.put_line(v_emp.id||' '||v_emp.first_name);
FETCH cur_emp Into v_emp;
dbms.output.put_line(v_emp.id||' '||v_emp.first_name);
............. --可以有多个FETCH语句.
CLOSE cur_emp;
end;
例如:2
declare
v_id s_emp.id%type;
v_firstname s_emp.first_name%type;
CURSOR cur_emp IS
select * from s_emp where dept_id=44;--这里不能有INTO子句. 如果44是一个变量,则变量的声明在游标声明之前.
begin
OPEN cur_emp; --游标指向第一个位置.
FETCH cur_emp.id,cur_emp.first_name INTO v_id,v_firstname;
dbms.output.put_line(v_id||' '||v_firstname);
end;
4)游标的属性:
%FOUND(判断上一次游标执行之后的结果 有数据为true ,否则为false;使用while循环要先取一次记录.
%NOTFOUND(判断上一次游标执行之后的结果 有数据为false ,否则为true;
%ISOPEN(判断当前游标是否打开)
%ROWCOUNT(当前游标已经检索过的数据个数.
注:使用游标的属性:游标名%属性.
A) declare
v_emp s_emp%rowtype;
cursor cur_emp IS
select s.* from s_emp s where s.dept_id in (select d.id from s_dept d where d.region_id =1);
begin
open cur_emp;
Loop
exit when cur_emp%notfound;
fetch cur_emp into v_emp;
dbms_output.put_line(v_emp.first_name||' '||v_emp.last_name);
end loop;
close cur_emp;
end
B)
declare
v_id s_emp.dept_id%type;
v_name s_dept.name%type;
v_avg s_emp.salary%type;
course cur_emp is
select s.dept_id,d.name,avg(s.salary) pj from s_emp s,s_dept d where s.dept_id=d.id
group by s.dept_id,d.name
order by pj;
begin
open cur_emp;
Loop
exit when cur_emp%notfound;
fetch cur_emp into v_id,v_name,v_avg
dbms_output.put_line(v_id||' '||v_name||' '||v_avg);
end loop;
close cur_emp;
end
C)
for v_emp in cur_emp loop
DBMS_OUTPUT_LINE(v_emp.id||' '||v_emp.name||' '||v_emp.avg);
end loop;
D)
begin
for v_emp is (select * from s_emp) loop
DBMS_OUTPUT_LINE(v_emp.id||' '||v_emp.name||' '||v_emp.first_name;
end loop;
end;
5)带参数的游标
declare
cursor cur_emp(f_name s_emp.first_name%type,.....) is--参数类型后不能加精度限制 .参数的长度,精度,刻度不能加.
select id,first_name
where first_name=f_name;
begin
open cur_emp('Kate');
for(v_emp in cur_emp loop
dbms_output.put.line(v_emp.id||' '||v_emp.name);
end loop;
end;
6)for update:在查询语句后加for update ,它将给此行加一个行级排它锁,防止他人修改我的结果.
declare
cursor cur_emp is
select id,first_name
from s_emp
where dept_id=44
for update;
begin
for v_emp in cur_emp loop
update s_emp
set salary=salary*(1+0.1)
where current of cur_emp;
end loop;
end;
7)隐式游标:
declare
v_emp s_emp%rowtype;
begin
update s_emp set salary =salary*(1+0.1)
where dept_id=31;
dbms_output.put_line(sql%rowcount);
select * into v_emp from s_emp where id=1;
dbms_output.put_line(sql%rowcount);
select * into v_emp from s_emp where id=100;
--如果没有找到数据,则会抛出一个异常.他不会执行下面的语句.
if sql%notfound then
dbms_output.put_line('no emps found')
end if;
end;
6.异常处理:
A. 1)用户自定义异常
2)oracle 预定义异常.
B.
1)用户自定义异常
异常名 EXCEPTION;
2)RAISE 异常名(这时抛出异常).
3)
declare
begin
EXCEPTION
WHER 异常名 then
.....
when 异常名 then
....
.....
END;
例子:
declare
e_myexception exception;
v_emp s_emp%rowtype;
v_id s_emp.id%type =1;
begin
select * from into v_emp form s_emp where id=v_id;
if v_emp.salary<1000 then
raise e_myexception;
end if;
dbms_output.put_line(v_emp.id||' '||v_emp.salary);
exception
when e_myexception then
update s_emp set salary=1000 where id=v_id;
dbms_output.put_line('capture the exception');
when no_data_found then --ORACLE预定义异常.
dbms_output.put_line('capture the exception:no data found');
when others then
dbms_output.put_line('capture the other exception');
end;
7.子程序
A.匿名块:
B.带名块:
可以存储于数据库中.
可以在任何需要的地方调用
C.创建过程
create [or replace] procedure 过程名 (参数)
例子:
create or replace procedure pro_hello as/is
begin
dbms_output.put_line('hello,world');
end;
D)调用存储过程
begin
pro_hello[(参数)];
end;
E)带参数的存储过程.
create or replace procedure pro_hello (p_id s_emp.id%type) as/is
v_name s_emp.first_name%type;
begin
select first_name
into v_name
form s_emp
where id=p_id;
dbms_output.put_line('hello,world'||' '||v_name);
end;
这里的参数是IN 模式的参数.
create or replace procedure pro_hello (p_id IN s_emp.id%type) as/is
v_name s_emp.first_name%type;
begin
select first_name
into v_name
form s_emp
where id=p_id;
dbms_output.put_line('hello,world'||' '||v_name);
end;
2)out 模式的参数:只做查询,不作输出.它是用来返回值的.
create or replace procedure pro_hello (p_id out s_emp.id%type) as
v_name s_emp.first_name%type;
begin
select first_name
into v_name
form s_emp
where id=p_id;
--dbms_output.put_line('hello,world'||' '||v_name);
end;
declare
v_name s_emp.first_name%type;
begin
pro_hello(1,v_name);--这个是用来保存结果的.
dbms_output.put_line('hello,'||v_name);
end;
3)show errors:让系统显示错误.
4)in out 模式
5)参数的长度,精度,刻度不能加.
6)名子标识法
7)in模式的参数可以有缺省值,且in模式的参数应放在参数列表的末尾.
F)实参与形参(标识参数).Oracle笔记
最新推荐文章于 2024-04-22 21:47:07 发布
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
