Oracle

重点在：五、六、七 章

----------------------------------------第一章基本概念---------------------------------------

http://localhost:5500/em
http://localhost:5500/isqlplus


命令行登陆sqlplus  : cmd ->  sqlplus username/pwd@ora10


oracle官方账号：我的邮箱 / 1361XXX


Oracle9i如果要启动的服务(Service)有三个
1.HTTPServer，如果想通过游览器执行sql。
2.监听器 ,监听客户端发过来的连接请求
3.OracleService,

Oracle9i默认账号:scott/tiger


---------------------开始


A.数据库基本概念
1.数据库(Database,DB)
2.数据库管理系统(Database Managermcnt System,DBMS)
3.数据库管理员(Database Administrator, DBA)
4.数据库系统(Database System,DBS) ，
  数据库系统包括：1.应用程序(语言开发的项目) 2.数据库管理系统(Oracle,DB2) 3.数据库(数据文件)

5.关系型数据库(Relationship Database ,RDB)
6.关系型数据库管理系统(RDBMS)
7.SQL语言(Structured Query Language)
  用于关系模型的数据库语言，用于和各类数据库的交互，提供通用的数据管理和查询功能。
  常用的Sql指令有：SELECT | INSERT | DELETE | UPDATE | CREATE | DROP

B.数据管理历程
1.手工管理阶段(由应用程序管理，编写程序进行数据的管理)
2.文件管理阶段
3.数据库管理阶段

C.数据库发展历程
第一代.非关系型数据库，包括层次型和网状型。
第二代.关系型数据库(RDBS) 。上世纪70年代中期问世.（现在用的比较多.如：Oracle数据库）
第三代.对象-关系数据库系统(ORDBS、OODBS) 。上世纪80年代至令。由于还没有太成熟的数据库。所以不常用
.


D.数据库分类
 网状数据库（织成一张网）
 层次型数据库(树型结构一样)
 关系型数据库(主外键关系的、表与表之间存在关系的)

 关系型数据库采用结构化查询语言(SQL)作为客户端程序与数据库服务器沟通的桥梁--客户端发出Sql指令到服务器，服务器执行相关的指令并返回其查询的结果。

E.数据建模
  数据建模就是将现实世界中客观存在的事物对其进行分析、抽象、确定数据的结构以及数据间的内在联系。然  后以数据的形式存储到计算机

E-R模型：实体-关系模型
E-R图：三要素 1。实体-用矩形表示 2.属性-椭圆表示，并用连线与实体连在一起 3.实体间联系-用菱形框表示.
例：

(姓名)                    (课程编号)
  |    m                选课          n     |
[学生] ----------------- ◇ ------------[课程]
  |                       |
(学号)                    (课程名称)

说明：实体间关系 (1:1一对一,1:n一对多,m:n多对多)


F.关系型数据库基本术语
 1.关系  ：一个二维表（也就是一张表中的每行数据、和多行数据之间的关系）
 2.关系名: 表名
 3.元组  ：行（记录）
 4.属性  ：字段/分量
 5.属性名：字段名
 6.主键  : 唯一确定元组的属性组(关键字)，唯一能标识每条记录。不能为null 。（唯一约束可以为null）
 7.域    : 取值范围 (age>0 )
 8.关系模式： 关系名（属性列表） 例：学生（学号，姓名，年龄）

----------------------------------------第二章基本术语、安装删除---------------------------------------
A.Oracle基本术语
数据库:磁盘上存储数据的集合(包括配置文件、数据文件、日志文件和控制文件等)
数据库实例:也就是运行中的数据库.它运行在数据库文件上的一组Oracle后台进程/线程以及一个共享内存区.数据库可以由实例装戴打开（OracleService就是启动Oracle实例）

数据库对象：表、视图、约束条件、索引、序列(自动生成)、同义词、存储过程、函数、触发器、包

数据库安全：用户、方案

方案：也就是一个用户的工作区，每个用户都有一个自己的方案，以便用户创建管理自己的数据库对象-表、视图等。当然也可以跨方案区、对其它方案的数据库对象进行操作、

权限(比如有insert-select的权限)、角色(DBA)、配额(DBA创建一个用户，这个用户也会对应一个方案，可以配置这个方案可以使用的最大空间，初始的空间、空间用尽了每扩展多少M);

B.Oracle存储结构
物理存储结构
    数据文件（Data File）        --保存用户真正的信息(表、视图等)
    重做日志文件（Redo Log File）    --把所有操作数据库的指令进行记录，恢复数据库时它会根所每条指令恢复
    控制文件（Control File）    --数据文件以及日志文件的一些信息。比如它们的位置名子等。
逻辑存储结构
    表空间(Table Space)     --数据库一个逻辑的空间、对应磁盘上的一个或多个物理数据文件。装Oracle后，它会自动创建System表空间(User表空间等)
    段(S)                --我们在表空间存的表、视图等就是一个段
    区                --段由多个区组成
    块(Block)            --区是由块组成(Oracle最小单位)

C.客户端工具
1。Sql Plus 命令行形式
2. Sql Plus WorkSheet 图形界面
3. iSqlPlus 网面形式 http://127.0.0.1/isqlplus

-----SQL语句分类

1.select查询语句
2.DML语句(数据操作语言)insert,update,delete,merge
3.DDL语句(数据定义语言)Create Alter Drop Truncate(删除表中的列)
4.DCL语句(数据控制语言)Grant(授权) Revoke(撤消)
5.事务控制语句 Commit Rollback Savepoint(部分保存/回gong)



----------------------------------------第三章sql语句基本操作---------------------------------------


-------------------------用户登陆

连接用户：connect system/abc
显示当前用户：show user;


alter user scott account unlock  : 解锁

sqlplus sys/abc as sysdba@ora10
ed  ：在cmd下使用记事本编辑
/   : 执行上一句


--------------------------建表
create table student
(

    name varchar2(20) not null,
    age number(3)        //三位整数。number型也可以是浮点型
);


-------------------------插入数据

insert into student values('accp',19);
insert into student values('accp',20);
insert into student values('accp',21);
commit;--由于上面插入的并没有生效。因此要commit

--------------------------查询
select * from student;
select name,age from student

--------------------------查看表结构
desc student;

--------------------------删除表
drop table student

--------------------------删除表中的数据
delete from student where name='accp';


-------------------------使用算术表达式(+ - * /)

select name,age*10 from student;
select name,age*(10-12) from student;
select mydate+10 from emp;         --日期加10天

---------------------------使用连接运算符("||")

select name||'我是一个经理' from  emp;  --输出 tom我是一个经理

select name||'这是名称'||age||'这是年龄'||add;||'这是地址' from student;


---------------------------给字段加别名

1.select name as 名称   from emp;
2.select age*10  年龄   from emp;
3.select sal*10  "薪水" from emp;


---------------------------在表达式中使用空值

注意：算术表达式中如果出现空值，则整个表达式结果为空，也就是不显示null，显示""。null值会被当作一个空(长度为0的)字符串处理) 。如果age1+age2(arg2为null) 则结果 为"";


---------------------------过滤掉重复行(distinct)

表中的数据
accp pwd
accp pwd
accp pwd
accp 123
accp 123
accp 123

select distinct name from wang;  
--查出结果为：
accp

select distinct name,pwd from wang;
--查出结果为：
accp pwd
accp 123

select distinct * from wang;  //如果为*，则显示所有字段，并且distinct 应用所有字段（也就是查询每条记录不一样的）
--查出结果为：
accp pwd
accp 123

注意：distinct 将应用后面的所有字段的组合(name,pwd);


-----------------------------查看结果的排序

select * from wang order by age

--多字段排序(先按第一个给出的字段对比，如果第一个相同再按后一个进行排序)
select * from wang order by age,sal desc       只对sal进行降序
select * from wang order by age asc,sal desc  对age进行升序而对sal都进行降序

--对字段别名进行排序
select sal*10 薪水 from emp order by 薪水 desc;

----------------------------条件查询

select * from wang where name='accp';

---------------------------日期条件查询

select * from user where udate = '02-8月-99';
select * from user where udate > '02-8月-99';

---------------------------把字符串转成日期 to_date('字符串日期','指定格式');

select * from user where udate = to_date('2001-1-1','yyyy-mm-dd hh:mi:ss');

--获得当前默认日期格式
select sysdate from dual;
输出：7-10月-08


--在Oracle中使用dual表。

因为在SqlServer中想打印当前日期可以使用 select getDate()或print getDate();
而在Oracle必须借助于dual表
如：
select sysdate from dual;
select 1+1 from dual;


----------比较运算符
=、> 、<、>=、<=、<>

select * from emp where sal Between 100  And 200 ：包括100和200
select * from emp where sal in (100,200);

----------逻辑运算符
AND 、 OR 、 Not

select sal from emp where sal>1000 and sal <2000;
select sal from emp where sal>1000 or sal <2000;
select sal from emp where ename not in ('admin','accp');

----------模糊查询

%: 表示0或多个字符
_：表示任意一个字符
escape:转义字符

例：select ename from emp where  ename like '_A_E%';         --- JAMES
    select ename from emp where  ename like '%A%' ;          ---多结果
    select * from wang where name like '%''%';             ---accp'accp
    select * from wang where name like '%@_%' escape '@';     ---a_b; 由于"_"表示任意一个字符。需要转义，因此使用escape '@'。代表@符后后面的"_"，进行转义；
    select * from wang where name like '%\%%' escape '\';       ---a%b;

----------往字段插入单引号

Oracle中二个单引号，就是一个单引号。
如果想插入"'"单引号。 如："accp'accp"；

insert into wang(name) values('accp''accp');
要加二个单引号。


----------判断null值
select * from wang where pwd is null
select * from wang where pwd is not null


---------运算符优先级
select * from wang where name='accp' or pwd='abc' and age>25;
实际上先算:pwd='abc' and age>25

可以使用括号改变优先级
select * from wang where(name='accp' or pwd='abc') and age>25;



---------------------------------------------第四章 Oracle的函数和Oracle数据类型---------------------------------------

-----------Oracle数据类型

char       : 字会型，最大长度2000byte，默认1byte，例:char(50) "hi!"： 它会补47个空格 （一个汉字占二个byte。char(2)：只能放一个汉字.能放2个英文字母）
nchar      : 基于NLS国家字符集的字符型。最大长度2000byte，默认1字符。以字符为单位。（装Oracle默认它会采用当前系统的字符集(中国：ZHS16GBK)）。例(nchar(2)。最多只能放二个汉字或二个英文)
varchar2   : 可变长度。最大长度4000byte.(推荐使用，Oracle专用,为了防止sql语言标准改变造成的不兼容问题)。
nvarchar2  : 可变长度。基于NLS国家字符集的字符型。也是以字符为单位。同nchar原理一样。
varchar    : 同varchar2。(varchar是sql语言标准。但sql语言标准有可能改变)
number(m,n)：数值型。m:总位数, n:小数位数 .m<=38位 ，例：number(10) 插入10 再插入10.25 ，查出来是10,10
date        : 日期 。insert into test(udate) values(sysdate);sysdate:当前日期

long       : 可变长度字符串。最大长度2G,不支持对符串内容进行搜索 (不推荐)
raw       : 可变长度二进制。最大长度2000B。(不推荐)
longraw    : 可变长度二进制。最大长度2GB。(不推荐)

blob       : 二进制大对象类型，最大长度4GB.
clob       : 字符大对象类型。最大长度4GB.(如果数据量大，推荐使用)
nclob       : 基于NLS国家字符集的字符型（同nchar与char的关系）。以字符为单位,最大长度4GB
bfile      : 二进制大对象类型，最大长度4GB.（它是把数据保存在外部，而不是在本身的数据库中）


----查看当前系统配置
select * from v$nls_parameters; 比如查看当前Oracle采用的是什么字符集.

select username from dba_users; 查看dba用户
select username from all_users;查看all用户


-----------函数

--单行函数 >>> 字符函数

1.字符大小写转换：

lower('Abc') ：转为小写
upper('Abc') : 转为大写
initcap("abc cde"):单词首字符大写( 以空格为分隔符 )结果为:Abc Cde

例：
select lower(ename) from emp;  转为小写
select initcap(ename) from emp;单词首字符大写

2.字符处理
concat('Hello','World') :连接字符串  ,concat()二个参数都可以是常量或者是数据库里的字符，

substr('HelloWorld',4,3): 结果为：loW

length('abc')         : 如果为：3

instr('abc','bc')     : 相当于java中的indexOf('bc') ：结果为：2 ，如果没有返回0

lpad ('abc',5,'-')     : 如果abc不够5位，则用'-'填充左边：--abc; select lpad(pwd,10,'-') from wang ;如果pwd为null,则不显示!

rpad ('abc',5,'-')     : abc--

trime('  abc   ')       :"abc"   select trim('   abc   ') from dual;

replece('abc','a','@')  :将a替换成@号 : select replace(ename,'A','@') from emp / update wang set name=replace(name,'c',' ');

例：
select concat('wang','kk') from dual;  // 结果:wangkk
select concat(name,age) as 连接后 from wang;  
select concat('你好',name) from wang;  // 结果：你好,XX
select concat(('你好',name),age) from wang
select substr(ename,2,1) from emp;
select name,lpad(pwd,10,'-') from wang;
select lpad(ename,10,' '),rpad(sal,10,' ') from emp;

--单行函数 >>> 数值函数
abc(-3.14)        绝对值        ： 3.14
round(3.1455)   四舍五入    :  3
round(3.1455,2)           :  3.15
round(314.1455,-2)         :  300 / select round(350.125,-2) from dual; : 400 /  update set sal = round(sal,-2);
trunc(3.1455,3)    截断.后的三位    :  3.145 / trunc(3.1455) : 3 /  trunc(38.1455,-1) : 30
ceil(3.14)    向上取整    :  4
floor(3.14)    向下取整    :  3
sign(-3.14)    判断数值正负    : -1 为正返回1，为负返回-1
power(3,2)    幂运算        ：3的2次幂 6
sqrt(9)        开平方根    : 3
mod(10,3)    取模10%3    : 1

--日期类型

默认日期格式是： DD-MON-YY

sysdate ： 得到当前日期

日期型+10 :加10天
日期型+15 :加15天
select sysdate-10  from emp;

日期型-日期型 : 二个日期机减

--日期函数

add_months(sysdate,2)  ：当前日期加2个月
last_day(sysdate)      : 当前日期的最后一天 31-10月-08
months_between(sysdate,udate) : 当前日期与udate相差几个月
trunc(sysdate,'month')          :截取到月
trunc(sysdate,'year')          :截取到年
next_day(sysdate,'星期三')    :以当前日期为准，下个周三是哪个日期

--转换函数(相当于java的中类型转换)

Oracle有隐式转换比如：日期

to_date('2007-07-17','yyyy-mm-dd');    
to_number('20')        : 转成数值
to_char(20)转换char    : 20
to_char(25553.88888,'$99,999.0000')       :$25,553.8889
to_char(sysdate,'yyyy-mm-dd hh:mi:ss') :  2008-10-08 11:14:18
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') :2008-10-08 23:14:52
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss am'):2008-10-08 23:18:26 下午

--常用日期格式符
yyyy
mm
dd
day        星期
am/pm        上午/下午
hh/hh12/hh24
mi        分钟
ss        秒

--通用函数

nvl(表达式)  :将null值替换成指定的值 nvl(udate,sysdate) nvl(name,'aa') ............
nvl2(name,表达式1,表达式2) : 如果a为null,返回表达式1的值，否则返回表达式2的值
nullif(name,name2)         : 如果name与name2相等，则返null，否则返回nume的值
coalesce(exp1,exp2,...更多...)   :如果exp1为null，返回exp1，否则检查exp2.....返回exp2 ,exp3....4..5
case
例：
select name,age,sal,
    case number when 'A' then 'A部门'
            when 'B' then 'B部门'
            else '未知'
    end 所属部门
from emp

decode() 和case效果一样
例:
select name,age,sal,
    decode(number,'A','A部门','B','B部门','未知')
    所属部门
from emp;



注意：函数可以嵌套使用，嵌套层次无限制




-------分组函数(多行函数)  单行函数:对一个值计算返回一个结果如：to_char(sysdate,'yyyy-mm')  多行函数：多个值计算返回一个结果 如：avg(age);
avg(age)
count(*)
max(age)
min(age)
sum(age)

group by : 用于分组查询
select ename from emp group by ename        : 对ename进行分组。注意 如果group by ename，那么select 后面只能是ename ，因为如果这样： select ename,age from emp group by ename,此时对ename分组，而age字段没办弄！
select ename,avg(sal) from emp group by ename;    : 此时的avg(sal)，是相对于ename分组后的对sal进行avg(sal)
select ename,avg(sal) from where sal>1000 emp group by ename desc; : 此时先把where sal>1000的查出来，where的优先级大于group by。

select name,pwd from wang group by name,pwd;  :多字段分组。name和pwd二个作一个。


注意：使用group by后， group by 字段，这时的字段必须在select 字段一样。

--having子句： 过滤分组
select name,pwd from wang group by name,pwd having pwd is null order by name desc .分组后的数据它的pwd是null

--分组函数最多可嵌套二层
select max(avg(sal)) from emp group by ename;


---------------------------------------------------第五章 多表联查与子查询---------------------------------------





-------------------------------------------------Oracle表连接

--------orale8i之前的常用表联接查询

--等值联接(第一个表中的字段和第二个表中的字段进行相等查询)
select * from table1,table2 where table1.id =table2.id  ：表1和表2有联系
select tabel1.name,table2.age,table3.sex from table1 t1,table2 t2,table3 t3 where t1.id=t2.nameId and t2.id=t3.age.id; :表1和表2有联系，表2和表3有联系
注意：如果where table1.id =table2.id不符合查询条件，则查询结果没有此记录，只显示条件成功的结果。 要解决此问题可使用：外连接

--非等值联接(第一个表中的字段和第二个表中的字段进行不相等查询)
select ename,sal,salgrade.grade from emp,salgrade s where sal>=s.losal and sal<=s.hisal;

--外连接(Outer Join)分为：左外连接，和右外连接
select * from table1,table2 where table1.id =table2.id(+) : 左外连接：左边(table1)每条记录依次和右边(table2)的记录进行匹配，如果不成功左边显示，右边不显示
select * from table1,table2 where table1.id(+)  =table2.id: 与左外连接相反

--自连接(Self join)（自已与自己连接，一张表出现二）
自连接：比如员工表（emp）包含,普通员工和上级。我们想查每个员工（包括上级）的上级。
select a.empno,a.ename,a.sal,b.ename from emp a,emp b where a.mgr=b.empno; : a.mgr是这个员工上级的编号（empno）


--------orale9i新引入的联接方式,支持Sql99规范

--交叉连接(Cross Join) :产生的结果是一个笛卡尔集。相当于等值连接不加where条件
select XXXX,XX,XX from table1 a cross join table2 b ;

--自然连接(Natural join):

自然连接：
相当于等值连接，也就是说它会在二个表中，自动找到相同字段(有可能a表中有多个字段与b表多个字段相同，那就进行多字段等值连接)进行等值连接
而且不能在select字段里加别名：如（a.xx,b.xxx）;
select ename,sal,dname from emp natural join dept;:emp表和dept表中的相同字段为：deptno

--Using子句(我们用where也能实现using相同功能)
由于自然连接是按多个相同字段进行等值连接，如果想对指定的字段进行等值连接可使用using。不能加表名前缀。
select eame,dname from emp join dept
using(deptno) ：只对相同字段deptno进行等值连接，如果也想对多个字段进行等值连接：using(deptno,字段2,字段3...)
：

--On子句(我们用where也能实现On相同功能),相加表名前缀
on子句：如果想按非同名的进行等值连接，或想设置任意连接条件，可用on子句
select ename,sal,dept.dname from emp join dept
on (emp.deptno=dept.deptno); :  on(emp.aa=b.bb)或on(emp.aa=b.aa and emp.sal > 1000); on子句可以加任意条件

--三张表连接
select ename,dname,city from
emp join dept using(deptno)
join citys using(cityno) :不能加表名别名

select a.ename,b.dname,c.city from
emp a join dept b on(a.deptno=b.deptno)
join citys c on(b.cityno=c.cityno)    :可以加表名别名（推荐）


--内连接(Inter Join)：新的语法格式 SQL99规范(和SqlServer的内连接一样)

--外连接(Outer Join)
   -左外连接(Left outer Join) select a.name,b.age from table a left join table2.b using(stu_id);
   -右外连接(Right outer Join)select a.name,b.age from table a right join table2.b using(stu_id);
   -满外连接(Full outer Join):二个表中不满足条件的也返回:相当于sqlserver的全连接。左表中不满足条件的和右表中满足条件的都查出来，查询结果会增加很多条。




-------------------------------------------------Oracle子查询

select ename from emp where sal < (select sal from emp where ename='张三'); :


----单行子查询

例1：查出大于张三工资的人！
select ename from emp where sal < (select sal from emp where ename='张三');    :要确保ename=“张三”只有一条记录，否则使用:where empno="张三的编号";

例2：查出大于平均值的人
select ename from emp where sal > (select avg(sal) from emp);

注意
1. where sal < (只返回一行一列记录);如果（子查询）里没有返回结果，则主查询也不返回结果 。
2. (子查询语句如果返回一条记录不能加 Order By语句)，因为(子查询句系统认为只返回一条记录，所以不用排序)。如果加了会出错。

----多行子查询

select ename from emp where sal in (select sal from emp where ename='张三' or ename='李四' order by date);

select ename from emp where sal not in (select sal from emp where ename='张三' or ename='李四'  order by date);

any运算符：和子查询里面的任意一个值比较运算符比较，只要有一个成功就行！
select ename from emp where sal > any (select sal from emp where ename='张三' or ename='李四'  order by date);

all运算符：大于(运算符)子查询里所有的查出结果
select ename from emp where sal > all (select sal from emp where ename='张三' or ename='李四' order by date desc);


select * from emp where sal > all(2500,100,1500)  等于： where sal > 2500
select * from emp where sal > any(2500,100,1500)  等于:  where sal >100



--------TopN查询

rownum(伪列)

例：如果想查top10，并且按ename 排序 ，只能用子查询：
select ename,rownum from (select * from emp order by ename) where rownum<10+1;  :   select a,b,c,d from (select a,b,c,d from wang order by date /*要想加别名：order by date table1 */); : from (一个临时表)
因为不用子查询:select ename,rownum from emp where rownum<11 order by ename ，rownum代表查询后的结果的编号，所以不能在这里用，而Order by 只能在Where所有条件结束后使用。


例：查询（每页10页，第二页的数据），按ename排序
select * from ( select ename,rownum myNo from (select * from emp order by ename) )  where myNo >10 and myNo<=20;


注意：如果进行查询时（不管是简单查询还是子查询），想这样写查询语句(有*号的)：select rownum,* from emp是错语的。
      必须要查询时为表加别名
      如：select rownum,a.* from emp a;
          select rownum,a.* from (select * from emp order by ename) a;


-------------------------------------------第六章 DML(数据管理语言)与事务控制---------------------------------------

-------------------DML语言

1.insert
2.delete
3.update
4.select

5.merge(合并) : 用于:a.数据合并 b.根据条件在表中执行数据的修改或者插入操作(如果插入的记录在目录表中已存在则执行update,否则执行insert)。

例：
create table test1
(
    cid number(10) ,name varchar2(30) , age number(10)
);
insert into test1 values(1001,'张三',23);
insert into test1 values(1002,'李四',27);

create table test2
(
    eid number(10) ,name varchar2(30) ,age number(10)
);

merge into test2 --目标对象
using test1      --使用test1表向test2合并
on (test.eid = test2.eid)  --合并条件
when matched then
    update set name=test1.name,age=test1.age
when not matched then
    insert (name,age) values(test1.name,test2.age);


----------------事务控制
------事务必须满足的ACID属性：
原子性：要么都成功要么都失败
一致性
隔离性：二个事务同时执行、a事务看到的是XXXX,b事务看到的是YYYY。
持久性

------事务结束：
1.commit或rollback
2.遇到DDl(Create、Alter)或DCL(commit、rollback)语句 :比如事务并没提交、而后面执行create等。则前面的事务自动提交
3.用户会话
4.系统崩溃:rollback

注意：
1.
如果使用insert ...........() .你再select.........，你会发现刚才insert的记录已插入，实际上并没有生效。你可以换个用户登陆(开一个新会话)
会发现刚才的insert并没有插入。也就是说你没有commit，那么你只能在你的会话里select.数据库并没有生效。

2.
如果使用updae................ ，你再select.......，你会发现update成功。实际上没有生效（因为没有commit），但update要更新的这行被lock了(记录锁)。重新登陆（一个新会话）。select......没有更新，而你在新会话里再进行update...没用(不允许更新)。因为些行被lock了（第一个会话没有commit）。


------遇到下面的情况事务会自动提交
1.后面执行一个DDL或DCL语句
2.正常结束会话

------查看系统是否自动执行事务
--查看设置
show autocommit  ;
    autocommit OFF
    autocommit IMMEDIATE
--改变设置
set autocommit On;  --如果设置了（不单是事务，就连每条语句都会自动commit）
set autocommit Off;


--------保存点(SavePoints)
insert into wang values('accp',10);
insert into wang values('admn',10);
savepoint p1;  --开始保存点....
insert into wang values('8888',11);
select * from wang /*   accp和admn和8888都查出来了*/
rollback to p1;    --回滚保存点
select * from wang /*   accp和adn查出来了，而8888没有查出来 */
rollback;
select * from wang /*上面三条insert都没有查出来*/


---------------------------------------------第七章 DDL(数据定义语言)与数据库对象--------------------------------------------

A.Oracle中一个用户就是对应一个方案（主案名与用户用相同，方案相当于数据库）
B.DDL语言和DML语言不一样，DDL语句一经执行，立即生效（不用commit），不能回滚。

数据库对象：
1.Table(表)         :
2.Constraint(约束)    :
3.View(视图)        :
4.index(索引)        :
5.Sequence(序列)    :  一组有规律的整数值
6.Sysnonym(同义语)    :  对象的别名

create table scott.wang (.............);--给scott方案下创建表


---查询当前方案下的所有表
select table_name from user_tables;


--使用子查询创建 新的表
1.指定字段
create table test(newNo,姓名/*也可以是中文*/,newAge)
as select empno,ename,age*10 as age /*如果使用表达式必须加别名*/ from emp;

2.复制所有，并把字段都复制进去
create table wang2 as select * from wang;

--修改表结构
alter table 语句修改表结构

--添加字段
alter table test add
(
    grade number(3),
    phone varchar2(20) default ‘暂无’
);

--修改字段 modify:
alter able test modify
(
    age number(2) not null,
    grade number(2),
    phone varchar2(200) default '010-000000'
    
);

--删除字段
alter table test drop (grade,phone);

--清空表中的数据是DDL而不是DML(Delete from wang)，不用commit，立即执行

truncate table test; 并删除表结构，并不删除表的字段。


--Drop Table删除表
会删除相关的索引、约束，但视图和序列保留，是DDL语句，不可撤消


--重命名表
rename oldStu to newStu  : rename 是DDL,不用commit



-----------Oracle包括二类表

一、用户自定义表

二、数据字典表

数据字典：
1.是Oracle数据库的核心，描述数据库及其所有对象。
2.数据字典由一系列的只读表和视图组成，这些表和视图属于Sys用户，但它也不能修改，是只读的。

数据字曲的内容：
1.数据库的物理 和 逻辑结构(表空间、段、区、块等)
2.对象定义和空间分配
3.完整性约束条件
4.用户
5.角色
6.权限
7.审计记录 : 可以用于对感兴趣的用户进行跟踪记录。

-----数据字典分为:表和视图二部分。
数据字典表：只读的
数据字典视图：只读的

数据字典视图分为三类：
dba : 所有的方案包含的对象信息（第个用户对应一个方案，相当于可以访问每个数据库的对象）
all : 用户可以访问的对象信息 (scott用户有可能可以访问其它用户的方案的权限)
user: 用户方案的对象信息(当前方案)

例子：
--第一类：user
select table_name from user_tables;  --当前方案下（当前用户）的表。
--第二类：all
select table_name from all_tables;   --当前用户可以访问的所有表
--第三类: dba用户
select table_name from dba_tables;   --所有用户拥有的所有对象的类型

--我能查看的所有数据库对象的类型：比如Index、Table等
select distinct object_type from all_objects;


-----------表的约束



--加约束
1.
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);

2.
create table test
(
name varchar2(20) not null,
constraint name_pk primary key(name),    --主键
CONSTRAINT emp_email_uk UNIQUE(email),  --唯一
emp_salary_min CHECK (salary>0),    --检查
constraint emp_dept_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) --外键

)

alter table test modify(name default '--'); --默认约束 insert into test values(default);

--删除约束
alter table test drop constraint id_CH;

--删除表的主键约束
alter table test drop primary key;

--删除级联约束（比如：主外键约束）
alter table test drop constraint PK_id cascade; --并删除子表中的外键约束

--删除复杂级联约束(比如：a和b字段是联合主键,a是test表的外键，要删除约束)
alter table test drop (stu_id) cascade constraints;

--禁用约束（暂时关闭）
alter table test disable constraint PK_id

--禁用级联约束（暂时关闭），
alter table test disable constraint PK_id cascade

--启用约束
alter table test enable constraint PK_id

注意：不能使用cascade关键字一并启动级联约束


--查询表约束
select * from user_constraints where lower(table_name)='test';   : user_constraints数据库字典视图
select * from user_cons_columns where lower(table_name) ='stu';  : user_cons_columns更详细的信息（比如哪一列上，约束的名称等）


----------------视图

使用视图效率高:因为对sql语句已经编译过.存在服务器中.

--创建视图
create or replace  /*or replace : 可写可不写，如果写了：如果此视图已存在，则替换*/
view  scott.MyView1(新编号，newName,新职位,newSal) /*(新编号,...)可以不加*/
as select empno,ename,job,sal from emp where sal > 2000;

--强制创建视图 force : 如果视图基于的表不存在。可以使用强制创建视图.但要保证以后会创建这样的表
create or replace force view  scott.MyView1
as select no,name from aaaaaa where age > 20 and age < 30 ;

--创建只读视图
create or replace view scott.MyView
as
select * from wang
with read only;  --只读

--查看视图结构
desc MyView1;

--临时视图：子查询就是。

--删除视图
drop view MyView1;

查看视图:select 新编号,newName from myview1 where rownum < 2;


---可以对视图进行DML操作(Insert,update,delete)

要视图进行DML操作必须:
1.创建视图时不能使用分组函数、group By 子句、distinct、rownum假列、不能用 sal * 10 sal;
2.创建的视图不能基于二个张表
3.基表非空的列，在视图中未包括，不能insert


---------------------索引

为了提升查询效率

唯一索引:  1.2.3.4  没有重复
 
非唯一索引:1.2.2.3.4.4 ，有重复但重复的数据紧挨着的。

唯一索引: 创建主键或唯一约束时，系统会默认创建。
非唯一索引：由用户创建

索引页是： 1 - 10 ,11-20 ....  或     A-M ,M-O,O-Z......   提交查询效率  


--创建索引
create index myindex on wang(name);

--删除索引
drop index myindex

--添加唯一索引
create unique index emp_email on employees(email)

--查看索引
select * from user_index 或
select * from user_ind_columns

--创建函数索引
create index myindex
on emp(lower(name))

select * from emp where lower(name)='zzz';

--索引使用原则：

要使用索引：
1.字段取值分布范围很广 ： 如用户名
2.字段包含大量空值
3.字段经常出现在where子句或连接条件中
4.表经理被访问，数据量大
5.表不经常被更新

--------------序列
--创建序列、自动增长
1.
create sequence tt increment by 1 maxvalue 9999999 cycle;
insert into test values(tt.nextval,'a');

2.
create sequence mysequence1
increment by 1  /*递增1*/
start with 1    /*超始值*/
nomaxvalue nocycle  /*不设置最大值*/

2等价于 ： create sequence mysss2;

--删除序列
drop sequence my..;


---------------同义词 (相当于起了个别名)

create /*public加上之后，在其它的方案上使用它*/ synonym aa from test;

select * from aa;

drop synonym aa;




-----------Oracle 分页

select empno from emp where rowid not in(select rowid from emp where rownum <currPage*pageSize+1) and rownum<pageSize+1;


==============================================转：尚学堂同学学习笔记============================

第一课：客户端
    1.Dos命令行下：sqlplus 用户名：scott 密码：tiger,sqlplus scott/tiger
    2.开始－运行－sqlplus 图形版的SQLPLUS
    3.http://localhost:5560/isqlplus

    Toad:管理 Plsql Developer

第二课：更改用户
    1.sqlplus sys/bjsxt as sysdba
    2.alter user scott account unlock;(解锁)

第三课：table_structures
    1.desc 表名
    2.select * from 表名
第四课：select语句
    1.select ename, sal*12 annual_sal from emp;
     select ename sal*12 "annual sal" from emp;双引号可以保持原大小写，并且中间可以加空        格，不加全变为大写
    2.desc dual，select 2*3 from dual，dual是系统自带的一张空表，计算数据时可以使用该表
    3.任何含有空值的算术表达式的计算结果是空值
    4.select ename||sal from emp;
    5.select ename || 'abcdefg' from emp;
    6.select ename || 'abcd''efg' from emp;当字符串中含有单引号时，可用两个单引号代表一        个单引号
    7.set linesize 200;--用于设定每行显示的宽度
    8.set pagesize 30;--设置显示的页数

第五课：distinct
    1.select distinct deptno from emp;
    2.select distinct deptno,job from emp;

第六课：where
    1.select * from emp where empno = 10;
    2.select * from emp where empno <> 10;
    3.select * from emp where ename = 'hebe';
    4.select * from emp where sal (not) between 800 and 1500;
    5.select * from emp where comm is (not) null;
    6.select * from emp where ename (not) in ('smith','philip','jay');
    7.select * from emp where ename like '_A%';
    8.select * from emp where ename like '_\%a%';系统默认转义符是\,可以自己指定转义符
     select * from emp where ename like '_$%a%' escape '$';

第七课：order by
    1.select * from dept;
    2.select * from dept order by dept desc;(默认为asc)
    3.select ename, sal, deptno from emp where sal > 2000 order by deptno asc,ename         desc;

第八课：sql_function
    1.select ename,sal*12 annual_sal from emp where ename not like '_A%' and sal > 1500
    order by sal desc;
    2.select lower(ename) from emp;
    3.select ename from emp where lower(ename) like '_a%';
    4.select substr(ename,2,3) from emp;从第二字符截，一共截三个字符。
    5.select chr(65) from dual;结果为A
    6.select ascii('a') from dual;结果为65
    7.select round(35.572) from dual;结果为36
    8.select round(35.572,2) from dual;结果为35.57
    9.select round(35.572,-1) from dual;结果为40
    10.select to_char(sal,'$99,999.9999') from emp;to_char函数主要用于对日期和数字进行格        式化
    11.select to_char(sal,'L99,999.9999') from emp;人民币符号，L代表本地符号。
    12.select birthdate from emp;
    显示为：BIRTHDATE
    ---------------------
    22-3月-87
    改为：select to_char(birthdate,'YYYY-MM-DD HH24:MI:SS') from emp;
    13.to_data函数
       select ename,birthdate from emp where birthdate > to_date('1987-3-22             11:22:33','YYYY-MM-DD HH24:MI:SS');不能直接写birthdate>'1987-2-22 11:22:33'会出现格        式不匹配，因为表中格式为DD-MM月－YY,
    14.select sal from emp where sal > to_number('$12,444.99','$99,999.99');
    15.select ename, sal*12+nvl(comm,0) from 这样防止comm为空时，sal*12相加也为空的情况

第九课：Group function 组函数
    1.select max(sal) from emp;
    2.select min(sal) from emp;
    3.select to_char(avg(sal), '$999,999,999.99') from emp;
    4.select round(sum(sal),2) from emp;
    5.select count(*) from emp where sal > 1500;
    6.select count(comm) from emp;
    7.select count(distinct deptno) from emp;

第十课：Group by语句
    1.select avg(sal) from emp group by deptno;
    2.select deptno,avg(sal) from emp group by deptno;
    3.select deptno,job,max(sal) from emp group by deptno,job;
    4.求薪水值最高的人的名称select ename,max(sal) from emp;出错，因为max只能有一个值，但        是等于max的值可能有好几个，不能匹配。
    可以写成select ename from emp where sal=(select max(sal) from emp);
    Group by 语句应注意，出现在select中的字段，如果没有出现在组函数中，必须出现在Group         by语句中。

第十一课：Having对分组结果筛选
    1.where是对单条记录进行筛选,Having是对分组结果进行筛选
    select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
    2.查询工资大于2000的雇员,按照部门编号进行分组,分组后平均薪水大于1500,按工资倒序排列
    select deptno,avg(sal) from emp where sal > 2000 group by deptno having avg(sal) >         1500 order by avg(sal) desc;

第十二课:子查询
    1.select 语句中嵌套select 语句,求哪些人工资在平均工资之上.
    select ename,sal from emp where sal > (select avg(sal) from emp);
    2.查找每个部门挣钱最多的那个人的名字.
    select ename, deptno from emp where sal in (select max(sal) from emp group by             deptno) 查询会多值.正确写法是:
    应把select max(sal),deptno from emp group by deptno当成一个表,语句如下:
    select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by         deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);

第十三课:self_table_connection
    select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

第十四课:SQL1999_table_connection
    1.select ename,dname,grade from emp e,dept d,sqlgrade s where e.deptno=d.deptno and          e.sal between s.losal and s.hisal and job <> 'CLERK';
    这种写法没有把过滤条件和连接条件分开来,由于这个原因,sql1999标准推出来了.
    2.select ename,dname from emp,dept;(旧标准)
     select ename,dname from emp cross join dept;(1999标准)
    3.select ename,dname from emp,dept where emp.deptno = dept.deptno;(旧)
     select ename,dname from emp join dept on (emp.deptno = dept.deptno)
    4.select ename,grade from emp e join salgrade s on(s.sal between s.losal and           `      s.hisal);
    5.select ename,dname,grade from emp e join dept d on(e.deptno = d.deptno) join                salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';
    6.select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.deptno);
    7.select e1.ename,e2.ename from emp e1 left (outer) join emp e2 on (e1.mgr =                e2.empno);
    8.select ename,dname from emp e right outer join dept d on (e.deptno = d.deptno);
    9.select ename,dname from emp e full join dept d on (e.deptno = d.deptno);

第十六至二十三课:求部门平均薪水等级
    1.A.求部门平均薪水的等级
    select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group          by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal)
    2.求部门平均的薪水等级
    select deptno,avg(grade) from (select deptno,ename,grade from emp e join              salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno;
    3.哪些人是经理
    select ename2 from (select e1.ename ,e1.mgr mgr1,e2.ename ename2 from emp e1 join         emp e2 on (e1.mgr = e2.empno)) t join emp on (t.mgr1 = emp.empno)
    select ename from emp where empno in (select distinct mgr from emp)
    4.不准用组函数,求薪水的最高值
    select distinct sal from emp where sal not in(
    select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal) )
    5.求平均薪水最高的部门编号
    select deptno,avg_sal from (
        select deptno,avg(sal) avg_sal from dept group by deptno
        )
    where avg_sal = (
    select max(avg_sal) from (
        select deptno,avg(sal) avg_sal from dept group by deptno
        )
    )
    select deptno,avg_sal from
        (select deptno,avg(sal) avg_sal from dept group by deptno)
    where avg_sal =
        (select max(avg(sal))  from dept group by deptno)
   
    6.求平均薪水最高的部门名称
    select dname from dept where deptno =
    (
    select deptno from
        (select deptno,avg(sal) avg_sal from dept group by deptno)
    where avg_sal =
        (
        select max(avg_sal) from
            (select deptno,avg(sal) avg_sal from dept group by deptno)
        )
    )
    7.求平均薪水等级最低的部门的部门名称
    错误写法:
    select min(avg_grade),deptno from
    (
    select deptno,avg(grade) avg_grade
    (
    select ename,deptno,grade from emp e join salgrade s on (e.sal between s.losal and         s.hisal)
    ) group by deptno
    ) group by deptno
    正确写法:
    select dname,t1.deptno,grade,avg_sal from
     (
     select deptno,avg_sal,grade from
       (select deptno,avg(sal) avg_sal from emp group by deptno) t   
     join salgrade s on (t.avg_sal between s.losal and s.hisal)
     ) t1 join dept on (t1.deptno = dept.deptno)
    where t1.grade =
     (
       select min(grade) from
       (
         select deptno,avg_sal,grade from
           (select deptno,avg(sal) avg_sal from emp group by deptno) t   
         join salgrade s on (t.avg_sal between s.losal and s.hisal)
       )
     )
   
    8.视图:view就是一张虚表,一个子查询
    conn sys/change_on_install as sysdba;
    grant create table,create view to scott;
    conn scott/tiger;
    创建视图
    create view v$_dept_avg_sal_info as
    select deptno,avg_sal,grade from
     (select deptno,avg(sal) avg_sal from emp group by deptno) t
    join salgrade s on (t.avg_sal between s.losal and s.hisal)
    使用视图后可以简写
    select dname,t1.deptno,grade,avg_sal_info from
     v$_dept_avg_sal t1 join dept on (t1.deptno = dept.deptno)
    where t1.grade =
     (
       select min(grade) from v$_dept_avg_sal_info
     )
    9.求比普通员工最高薪水还要高的经理人的名称
    先求普通员工的最高薪水
    select max(sal) from emp where empno not in
     (select distinct mgr from emp where mgr is not null);
   
    select ename from emp where empno in
     (select distinct mgr from emp where mgr is not null)
    and sal >
     (
     select max(sal) from emp where empno not in
       (select distinct mgr from emp where mgr is not null);   
     )

第二十四课
    备份与恢复
    DOS环境下:exp导出,imp导入
    --drop user yugang cascade;
    --cd \
    --cd temp
    =-delete *.*
    --exp
    --create user yugang indentified by yugang default tablespace users quota 10M on         users
    --grant create session,create table,create view to yugang
    --imp the data
    第一次输入的用户名密码为:yugang/yugang
    第二次输入的用户名密码为:导出数据的用户的用户名和密码
    --rollback
    create table emp2 as select * from emp;

第二十五课:rownum
    rownum是在Oracle中在表的后面加的一个尾字段,并且只能使用诸如rownum<n或rownum<=n这样的        形式,不能与>或>=相连接

    1.求薪水值最高的前5条记录.
    select ename,sal from emp order by sal desc where sal <= 5;
    2.求薪水值最高的第6~10条记录.
    select ename,sal from
     (
       select ename,sal,rownum r from
         (select ename,sal from emp order by sal desc)
     )
    where r >=6 and r <= 10;


第二十六课:homework_dml_transaction
    SQL面试题
    有三张表S、C、SC
    S(SNO、SNAME)代表（学号、姓名）
    C(CNO、CNAME、CTEACHER)代表（课号、课名、老师）
    SC(SNO、CNO、SCGRADE)代表（学号、课号、成绩）
    1.求出没选过郭富城老师的所有学生姓名
    2.列出2门以上（含2门）不及格的学生姓名及平均成绩
    3.既学过1号课程又学过2号课程的所有学生姓名
    1.select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno)
    where c.cteacher <> '郭富城';
    2.select sname from s where sno in
     (
       select sno from sc where scgrade < 60 group by sno having count(*) >= 2
     )
    3.select sname from s where sno in
     (
       select sno from sc where cno=1 and sno in
         (select distinct sno from sc where cno=2)
     )

    事务transaction代表一组不可分割的操作，要么全部执行，要么全部不执行，
    transaction起始于一条DML语句，结束于commit;语句，或者是DCL、DDL语句，在事务未提交前   
    可以通过rollback回滚事务，正常退出事务会自动提交，非正常退出事务会自动回滚。

第二十七课：create table
    create table student
    (
    id number(6),
    name varchar2(20),
    sex number(1),
    age number(3),
    sdate date,
    grade number(2) default 1,
    class number(4),
    email varchar2(50)
    );
   
第二十八~三十课 constraint
    not null,unique(当某字段有unique约束时，可以插入空值，空值之间不重复)、主键、外键、        check约束
    create table student
    (
    id number(6),
    name