oracle--SQL

1.Constant ？ ；set null *

2.数据库安装的默认iD和口令：system/manager sys/change_on_install sysman/oem_temp

3.DML(DATA MANIPULATION LANGUAGE)数据库管理语言：select,insert,update,delete

DDL(DATA DEFINITION LANGUAGE) 数据定义语言:alter,drop,rename….

事务控制（transaction control）rollback,commit…

会话控制：alter session…..;set role….;

System control (系统控制): alter system…

4.连接字符串时：’a’||null=a

5.集合运算符（用于复合查询）

Union:返回两个查询中的所有行；没有重复的行

select last_name,hire from employees where department=90 union select last_name,hir from emp….

Union all：返回两个查询中所有的行；包括重复的行

Intersect：返回两个查询中得到的不同的行

Minus：返回第一个查询返回的但不是第儿歌查询返回的不同行；

6．ORACLE只对单引号中的常量区分大小写

7．跟随在SELECT后面的关键字unique 或者distinct可以保证指定列所得到的行是唯一的。

Select distinct id,name,address from table_name;

1.Constant ？ ；set null *

2.数据库安装的默认iD和口令：system/manager sys/change_on_install sysman/oem_temp

3.DML(DATA MANIPULATION LANGUAGE)数据库管理语言：select,insert,update,delete

DDL(DATA DEFINITION LANGUAGE) 数据定义语言:alter,drop,rename….

事务控制（transaction control）rollback,commit…

会话控制：alter session…..;set role….;

System control (系统控制): alter system…

4.连接字符串时：’a’||null=a

5.集合运算符（用于复合查询）

Union:返回两个查询中的所有行；没有重复的行

select last_name,hire from employees where department=90 union select last_name,hir from emp….

Union all：返回两个查询中所有的行；包括重复的行

Intersect：返回两个查询中得到的不同的行

Minus：返回第一个查询返回的但不是第儿歌查询返回的不同行；

6．ORACLE只对单引号中的常量区分大小写

7．跟随在SELECT后面的关键字unique 或者distinct可以保证指定列所得到的行是唯一的。

Select distinct id,name,address from table_name;

8.select * from tab 显示当前方案中所有的表和视图。

9．group by ,Where 字句中不能使用列别名

10.select id,name||’ ‘||last_name,address from table where id<=any(100,200,25);

select id,name||’ ‘||last_name,address from table where id<=all(100,200,25);

如果有一侧结果是NULL，那么返回结果为NULL。

11．select id,name||’ ‘||last_name,address from table where not id<=all(100,200,25);

12．In相当于=any；not in相当于!=/<>;between 100 and 900 (大于或者等于100到小于等于900之间的数)

13．Exists(子查询)

Select id,name from table where exists(select id,name from table2 where Id<100);

14.=，<>无法处理NULL值，需要is null;is not null

Select lastname,id from table where id is null;

15.like 和% _ 匹配进行查询；

Select job_id,tittle from jobs where job_id like ‘ac_%’ escape ‘’;(后面的是转义字符)

16．如果SELECT中使用了DISTINCT关键字，那么order by 应适用distinct指定的关键列；如果SELECT中的列使用了表达式，那么ORDER by也应适用相同的表达式作为关键字。如：select distinct ‘计算数字’||id, name from table order by ‘计算数字’||id;

17.默认下null是排在底部（升序）可以使用nulls first;nulls last关键字来改变

Select last_name,pct from table where last_name like ‘r%’ order by pct nulls first,last_name

18.case

Select country_name,region_id,

Case region_id when 1 then ‘europe’

When 2 then ‘america’

When 3 then ‘asia’

Else ‘other’ end continent

From countries;

区别：程序1只把符合WHEN子句的显示，其余的都用else来显示输出；程序2将把ELSE的值进行条件比较，如果都不符合WHEN子句了才适用ELSE的显示输出。

select a1,a2,(case when a1>=6 and a1<=17 then '

from a;

update a set a1=case when a1>20 then 22 when a1<20 then 33 end;(更改的是a1列的数据)
commit;

create table ta(cname varchar(10), score real)
insert ta select '数学', 82
union all select '语文', 80
create table tb(cname varchar(10), type int)
go
--示例
insert tb
select cname, type=(case cname when '数学' then 1
when '语文' then 2
else 0 end)
from ta
select * from ta
select * from tb
--清除
drop table ta
drop table tb

sql中没有if语句

19．Set pause on 控制屏幕滚动

Set pause ‘按回车键继续’

20．@c:oracleora92sqlplusadminhelphelpbld.sql helpus.sql 安装帮助

21．Store set filename 保存环境变量

Login.sql存放到sql*plus目录后，可以实现登陆后自动加载环境设置（使用DOS状态下才能自动加载显示提示符）

22．&和号

Select gongzi,name from gongzi where gongzi=&gongzi;

&&gongzi替换&gongzi的值：

Select gongzi,name from gongzi where gongzi=&gongzi;

Update gongzi set gongzi=gongzi+100 where gongzi=&&gongzi;

Set verify off关闭提示。

23．Accept (属于PLUS命令)

Accept pwd char prompt ‘enter your password:’ hide

Prompt

Prompt This query displays the employee id and name for the employees in the supply

Prompt ===================================================

Accept deptnumb number prompt ’enter department number:’’

Select gongzi,name from gongzi where gongzi=&gongzi;

24．单行函数不能用于SQL语句的having子句中，只有组合函数可以出现在having子句中。组合函数不能出现在select语句的where子句中。

字符类；

Nvl；nvl2；lower(ABCDE)都变小写；substr（C1,C2,X）;substrb(把X作为字节进行计算)；replace（c1,c2,c3）把出现在C1中C2字符串斗改为C3，c3默认下是NULL；upper

数值类

Abs(返回绝对值)；round（n1，n2）舍入

日期类

Currnet_date ; extract ; last_day ;

单行转换函数

To_char /to_date /to_dsinterva l/to_yminterval/to_number

Asciistr(C)

其他

Sys_context(会话属性，IP地址，终端和当前用户)

UID 当前用户的数字用户ID

Select sys_context(‘userenv’,’ip_address’,’ os_user’,’session_user’) from dual

25、组合函数（aggregate/group function）和单行函数的区别：单行函数是在检索出每个行时求值一次。组合函数则是在出现一个或者多个行构成的一个组时求值一次。

26.组合函数不处理NULL值也不返回NULL值，即使当只对NULL进行求值时也是如此。

如： 一个对null值的count或者sum得到的结果是0

如果需要求所有员工的平均工资（需要把重复的员工排出）：

Select name,count(distinct id)

27．组合函数的使用：可以出现在select语句的select或having子句中。在用于select子句中时，它们通常需要指定GROUP BY子句。如果没有指定group by子句，则默认的组合处理应用于整个结果集。

28．组合函数总结

Count[*|distinct|all x] 返回非NULL行的数量，如果使用*，则返回所有包括NULL

avg [distinct|all n] 返回统计平均值

group_id 返回一个组标识符，用来唯一的标识重复的组

Max

Min

29组合函数需要单独使用在select后，或者同时使用组合函数，无法和列名或者单行函数同时使用。除非语句包含了group by 子句中的列。

SQL> select * from gongzi;

NAME GONGZI

---------- ----------

l斌 3000

l斌 3100

l斌2 4000

SQL> select avg(distinct gongzi),name from gongzi;

select avg(distinct gongzi),name from gongzi

ERROR 位于第 1 行:

ORA-00937: 非单组分组函数

SQL> select avg(distinct gongzi),name from gongzi group by name;

AVG(DISTINCTGONGZI) NAME

------------------- ----------

3050 l斌

4000 l斌2

30．GROUP by 组合数据

我们使用group by子句告诉数据库如何组合或者分类相关的数据。每当我们在select语句的select子句使用一个组合函数时，都必须把所有的非组合/非常量列放在group by子句中；

组合函数无法用到where子句中。那么就是说如果想把组合函数作为筛选数据的条件时，需要having子句代替where. 如果也同时使用group子句，那么需要放在其后；同时group by后面的子句必然是使用having子句进行数据筛选

31．如果一个查询来自多个表并且这些表之间没有一种内在关系或者公共列，则这种情况被称为笛卡尔（cartesian）连接或者交叉（cross）连接。

32.传统的oracle连接与ansi/iso sql1999语法之间的区别是：在ansi中，连接类型是在from子句中明确指定的。

33．natural join:自然连接，建立连接的基础是两个表的列名字完全都一样

select location_id,city,department_name from locations natural join departments where。。

把两个表中完全相同的数据和列显示出来。

join ……using

如果连接的表中，多个列具有相同的名，但数据类型不同

select location_id,city,department_name from locations join departments using(location_id）

join on

没有公共的列名可供连接时，或者你想任意指定连接条件时。

34.在顶级查询的from子句中使用子查询，称为一个嵌入试图（inline view）。在一个查询的where子句中使用子查询的做法称为嵌套子查询（nested subquery）

35．标量子查询：查询只返回一行记录，使用一个列值，作为insert中values子句中，order by,where,select等子句中的一个列使用。

select last_name,id,(select max(salary) from emp where……) hsal from emplorry where …;

36．插入查询结果insert into check(account,create,date) select account,create,sysdate from checking where accound_type=’a’;

37．NULL的插入：insert into customers(id,name,address) values(001,’libin’,null)同insert into customers(id,name,address) values(001,’libin’,‘’)一样。

38.插入多个表中(需要一个参照表)

insert all

when prod_cate=’b’ then

into book_sales(column1,column2..) values(列/常量)

when prod_cate=’c’ then

into video_sales(column1,column2..) values(列/常量)

select列 from sales_detail;

以上说明：把sales_detail中的相关列的值分别进行判断，根据各相关条件插入不同的表中。all可以使用first关键字，all代表对每个when进行比较求值；first代表当遇到第一个符合条件的when后停止以后的when操作。

39．更新

update gongzi set (gongzi)=(select sum(gongzi) from gongzi2 where name=’l斌’) where name=’l斌’;

40．有更新，没有插入

merge into product_information pi

using (select product_id,list_price,min_price from new_prices) np

on (pi.product_id=np.product_id)

when matched then update set pi.list_price=np.list_price,pi.min_price=np.min_price

when not matched then insert (pi.product_id,pi.category_id,pi.list_price,pi.min.price)

values (np.product_id,33,np.list_price,np.minprice);

41.truncate table tablename

truncate与DELETE的区别

1. delete是DML语句会产生用于撤销操作的信息而truncate是DDL语句，自动提交事物。

2. truncate会在表中重设高水位标记和所有索引。

3. TRUncate不会触发DELETE操作。

4. 表被截断时该表使用的存储区以及所有的索引都会恢复到初始大小。不能截断启用参考完整性约束的情况的父级表

TRUNCATE与DROP table

1. 与删除并重新建立一个表的区别是truncate不做如小操作

2. 使相关对象是小

3. 删除索引，触发器或者参考完整性约束

4. 要求授予特权

42．for update选择行锁定，select name,gongzi from gongzi for update;

在commit,rollback之前，其他会话能看，不能改

43．lock table gongzi in exclusive mode;

行锁定总是独占的，而表锁定可以是共享的也可以是独占的（p220-223）

44．savepoint a; rollback to a; rollback to savepoint a;

45.一致性（consistency）

oracle永远不对读取操作进行锁定，但系统使用UNDO段来重新生成所需的映像。当undo_management被设置为AUTO并且超过undo_retention时间设置时，undo段会被释放以便重复使用，出现此现象会提示‘snapshot too old’快照过老。oracle通过使用system change numbers（系统更改编号scn）实现一致性。

set transaction isolation level serializable;事物级一致性

set transaction isolation level read committed;语句级一致性

46．内建数据类型

字符

数字

二进制

日期和时间

行ID

47．create glogbal temporary table gongzi_temp(empid number(2),status varchar2(10) default ‘fengyun’);

48. comment on table gongzi2 is’the table is for gongzi table backup’;comment on column name is ‘飞碟飞的 ’；

查看表注释=dba_tab_comments;all_tab_comments;user_tab_comments

select comments from all_tab_comments where table_name=’xx’;

列注释：dba_col_comments….

49.create table xx as select * from emp;复制表，只有not null约束会被复制过去

50.列的操作（列不能被重命名，需要用新的名字生成新的表）

添加列

alter table x add order_date date not null;

如果表中已经有记录，那么添加列不能指定NOT null 约束条件，需要用default子句

alter table x add ordre_date varchar2(22) default ‘john’ not null

修改列

alter table x modify (name varchar(22),status varchar(11));

把default取消的方法是把default的值改为null

删除列

alter table tablename drop column cascade constraints

相关列的索引和约束也会被删除。有约束需要使用cascade constraints

标记删除

alter table orders set unused column name;

alter table orders drop unused columns ; columns continue(如果没有删除完毕就结束了任务，需要此子句继续完成删除工作)

数据字典视图中dba_unused_col_tabs等提供了被标记删除列的信息

51．drop table cascade constraints;删除一个表时，相关的索引，约束，触发器和特权也会被删除，但并不删除视图，具体化视图或者其他引用该表的存储程序，oracle会把它们标记为无效。如果有参考性约束需要使用cascade constraints

52．重命名表

rename orders to orders_newname;完整性约束，索引和授权都会随之转换，但视图，同义词，存储的过程及函数不会转移，将失效。

alter table scott.order rename to order_new;这样的方式可以改变其他方案的表名

53．约束管理

a) not null

b) check （不能引用sysdate函数或者其他的表）

c) unique 保证指定列没有重复值

d) primary key 用来唯一地标识出表的每一行，并且防止出现NULL

e) foreign key 通过使用公共列在表之间建立一种父子关系。在表上定义的外部键可以指向主键或者其他表的唯一键。

create table ordre(name varchar2(22) constraint ck not null,id number check(id>0))

create table test(name varchar2(22) not null,check(name not like '里%'));多个约束中间用逗号分隔；

all_constraints或者all_cons_columns视图说明了约束关系（c=check；p=primary key;r=referential;u=unique）

54．修改约束，不能使用modify子句对check进行添加或者修改（只有not null可以使用modify）,应该使用alter table order add.. ；alter table test add unique(name);

55. unique(name);primary key(name,id);NULL不被作为重复相同的值

56. foreign key(name) references orders(name);如果引用表的主键（name是主键），那么可以foreign key(name) references orders；

57．当父级表中行被删除时，因为子表的记录存在，默认下不允许删除父级表相关记录。那么需要使用on delete cascade（删除子记录）;on delete set null（子记录为空）子句来指定如何对子表进行操作。

58．暂时关闭约束alter table x disable constraint fk ; alter table test disable unique(name);

59．删除约束

alter table orders drop constraint ck;

NOT null需要使用alter table x modify column null;

删除foreign key 和unique约束需要带cascade子句；

60．enable,disable值影响将来要添加到表或者要修改的数据。而alter table 语句中的validate和novalidate则作用于现有的数据。

enable validate：ENABLE默认设置。表中现有数据将被检验。（打开检验）

enable novalidate：不对现有数据进行检验，但打开约束检测以后的数据。（打开不检验）

disable validate：约束被关闭，但约束仍然有效，在表上不能进行dml操作，因为更改无法被检验（关闭检验），也就只读状态。

disable novalidate：DISABLE默认语句。约束关闭，对未来数据不进行检验（关闭不检验）

例句：alter table x modify constraint pk disable novalidate;

61.视图中组合使用得到的数据类型为不带任何精度的NUMBER类型

62.如果需要生成视图，但表并没有都准备好。那么需要使用生成错误的视图

create force view x as select c1,c2 from test

63.定义视图的子查询不能包含for update子句；

64.只读视图：create view x as select 。。。。。with read only；

65．可以在视图上生成约束，属于声明性质。并且必须使用 disable novalidate子句才能使用。

create view x (name constraint name_c references employ disable novalidate,phone_number unique disable novalidate) as select name,phone_number,address from employ where id>9