20161118学习笔记
118:
笛卡儿连接：当多张表在一起查询时，没有给定正确的连接条件，结果是第一张表的所有行和第二张表的
所有行进行矩阵相乘，得到n*m 行的结果集。
SQL> select ename,dname from emp cross join dept;
等值连接：在连接中给定一个相等的连接条件。
select ename,dname from emp,dept where emp.deptno=dept.deptno;
表的别名的使用方法如下：
1.便于书写
2.将同名的表区分
3.一旦定义了别名，表的本名就无效
4.只在该语句内有效
5.定义方式为表名后紧跟别名，用空各间隔。
select ename,dname from emp e,dept d where e.deptno=d.deptno;
SQL99 的书写方式，效率是相同的，SQL99 是国标
select ename,dname from emp e join dept d on(e.deptno=d.deptno);
不等连接：
外键连接：将一张表有，而另一张表没有的行也显示出来。
select ename,dname,emp.deptno from emp,dept where emp.deptno=dept.deptno;
select ename,dname,dept.deptno from emp,dept where emp.deptno(+)=dept.deptno;//+号的意思为将没有员工的部门，用NULL 来匹配
+号不能同时放在等号的两边，只能出现在一边
自连接：表的一列和同一个表的另一列作为连接的条件。
select w.ename "下级"，m.ename "上级" from emp w,emp m where w.mgr=m.empno(+);//（+）为了将没有上级的人也显示。
select w.ename "下级"，m.ename "上级" from emp w,emp m where w.mgr=m.empno(+) and w.deptno=30;//要想在结果总过滤去一些内容请求用and运算
119:sql99跪着的表连接操作
不能提高性能，但更加通用，此处略过
120：子查询
尽量使用with子句代替复杂的子查询
select max(sal) from emp; select ename from emp where sal= 5000;
select ename from emp where sal=(select max(sal) from emp);
简单子查询
1.先于主查询执行。
2.主查询调用了子查询的结果。
3.注意列的个数和类型要匹配。
4.子查询返回多行要用多行关系运算操作。
5.子查询要用括号括起来。
查询工资总和高于10 号部门工资总和的部门。
select deptno,sum(sal) from emp group by deptno having sum(sal)>(select sum(sal) from emp where deptno=10);
查询每个部门的最大工资是谁。
select deptno,ename,sal from emp where (deptno,sal)in(select deptno,max(sal)from emp group by deptno);
子查询返回多行，用=不可以，得用in。
子查询返回多列，所以对比的列也要匹配。
select ename,sal from emp where sal < any(1000,2000);//小于2000 就可以
select ename,sal,deptno from emp where sal<all(1000,2000);
select ename,sal,deptno from emp where sal<all(select avg(sal) from emp group by deptno);
小于all小于最小，大于all大于最大
小于any 小于最大，大于any 大于最小
select ename,sal,deptno from emp where sal>any(select avg(sal) from emp group by deptno);
select ename,empno,mgr from emp o where exists(select 3 from emp where mgr=o.empno);
Exists 操作，子查询一旦确认为true,就终止查询，不再运行了余下的子查询。但是not
exists 就不同了，数据库最怕的就是not,有的时候语句看起来cost 不高，但就是慢，考虑
改写sql，尽量避免not 操作。
上面这句话的意思是找领导，其中3 是常量，你写什么都可以。
当子查询有行时，Exists 返回true,把为true 的行查询出来.
当子查询没有行时为假，Exists 返回false,就是不符合的记录，接着查下一个循环。
======================================================
我们将一般的表叫做heap table（堆表），其含义为杂乱无章的存储数据，堆表是数据库
的重要组织形式。它有别于索引组织表和cluster 表。
表的名称规则
标准ASCII 码可以描述
字母开头
30 个字母内
不能是保留字
可以包含大小写字母，数字，_,$,#
不能和所属用户的其它同名称域的对象重名。不同名称域的对象可以重名。例如有一个视图
是t1,就不能有一个表的名称是t1，因为当你select * from t1;的时候，数据库不知道你
查询的哪个，存在两意性。但是表可以和索引重名，因为这样不会影响数据库的判断。不存
在两意性。千万不要使用汉语做表和列的名称，因为汉语是ascii 码所不能描述的，ORACLE
的核心是ASCII 编写的
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
PK_DEPT INDEX
DEPT TABLE
EMP TABLE
PK_EMP INDEX
BONUS TABLE
SALGRADE TABLE
T_TEST TABLE
S1 SEQUENCE
T1 TABLE
EMP2 TABLE
10 rows selected
user_objects 当前用户所拥有的所有对象。不包含你建立的public 对象。
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
T_TEST
T1
EMP2
7 rows selected
user_tables 你自己的表
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$Um76PjVBRd6rPP/Xiyb4NA==$0 TABLE
BIN$u38baPQoTFO93IPpl022Lw==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP2 TABLE
SALGRADE TABLE
T1 TABLE
T_TEST TABLE
9 rows selected
tab你所拥有的表和视图
121：
考虑列的顺序和属性，尽量把可能为 null 的列放在最后！
创建表：
SQL> create table t3(
2 name char(8),
3 salary number(5) default 0,
4 content varchar2(4),
5 hiredate date);
desc t3; //显示表结构
create table t4 as select ename name,sal salary from emp;//在现有表的基础上建立表
create table t5(c1,c2,c3) as select ename,empno,sal from emp where 9=1;//如果不想要数据，只建立表结构，请加一个假条件。
修改表结构
如果列为null，可以随便修改列的类型和宽度。
如果有数据，修改会受到限制。但不会破坏数据。
如果不改变数据类型，只改变宽度的话加大是可以的。
alter table t1 modify(name char(4));
rename t3 to t_3;//修改表名称
表注释，行注释略过
回收站部分略过
122：
Insert into t1(c1,c2,c3) values(v1,v2,v3);
Insert into t1(c2) values(sysdate);//将当前的日期插入。
当插入的列为日期的时候，最好强制转化为日期类型，默认的转换在环境变化的时候会报错。
子查询插入
不加values 关键字，一次可以插入多行
列的类型和位置要匹配
Insert into d1 select * from dept;
update emp set sal=sal+1;//修改
drop table emp2 purge;//purge是直接删除表，不保留到回收站
create table emp2 as select * from emp;
alter table emp2 add(dname varchar2(10));
update emp2 set dname=(select dname from dept where dept.deptno=emp2.deptno);
DELETE EMP2 WHERE SAL>2000;
SELECT * FROM E2 ORDER BY 1;
123：
事务不能太大，也不要太小！开关事务需要大量资源消耗！
提交事务commit
1. 手工直接提交commit
2.自动提交
ddl,dcl 语句
exit 命令退出sqlplus
3.提交后
写日志文件
事务结束
释放锁和回退
其它用户可以看到结果--修改过的结果，这就是数据库的读一致性。
撤消事务rollback
1. 手工直接撤消rollback
2.自动撤消
网络或数据库崩溃
强制退出sqlplus，atl+f4,杀进程方式退出SQLPLUS
3.撤消后
事务结束
修改前的数据恢复了，逻辑的回退
释放锁和回退
其它用户可以看到结果--未修改的结果
update emp set sal=1000 where deptno=10;
savepoint u10;
update emp set sal=2000 where deptno=20;
savepoint u20;
update emp set sal=3000 where deptno=30;
savepoint u30;
delete emp;
rollback to savepoint u30;
select ename,sal,deptno from emp order by deptno;
rollback to savepoint u20;
select ename,sal,deptno from emp order by deptno;
rollback to savepoint u10;
select ename,sal,deptno from emp order by deptno;
rollback;
select ename,sal,deptno from emp order by deptno;
124：约束
not null unique check primary key foregin key
创建表时指定约束
create table t1(name char(9)not null);
建立表后指定not null 约束
drop table t1 purge;
create table t1 as select * from dept;
alter table t1 modify(dname not null);
create table t1 (name char(9) UNIQUE,
mail char(8) constraint t1_mail_u UNIQUE);
建立表的时候指明唯一约束，一个是系统命名，一个是我们命名。
建立表之后指定UNIQUE 约束
drop table t1 purge;
create table t1 as select * from dept;
alter table t1 add constraint u_dname unique (dname);
Check 检查约束
Drop table t1 purge;
Create table t1(name varchar2(8)
check (length(name)>4),
Mail varchar2(10) );
alter table t1 add constraint check_mail check (length(mail)>4);
