Oracle

一些概念

Linux中登录oracle的命令：

sqlplus 用户名/密码

show user 显示当前登录的身份.

set pause on （网页中分页）

set pause off 分页显示（SQL*plus中在网页中是不分页）. oracle中默认日期和字符是左对齐，数字是右对齐

table or view does not exist ; 表或示图不存在

edit 命令用于自动打开vi修改刚修执行过的sql的命令。

查看表的结构(包含的字段):desc s_dept(控制台);desc dept(浏览器) 查询：select table_name from user_tables;(控制台)

一、数据库基本概念

1)数据库管理系统：一种操纵和管理数据库的大型软件,是用于建立、使用和维护数据库，Oracle、DB2、Mysql、MS Server;

2)数据库: 按照数据结构来组织、存储和管理数据的仓库。

3)数据：

4)表（Table）：数据库中的表与我们日常生活中使用的表格类似，它也是由行（Row）和列（Column）组成的。

5)视图：其实是一个虚拟的表，在数据库中并不实际存在。视图数据是由查询数据库表产生的，它限制了用户能看到和修改的数据。

6)索引：索引是根据指定的数据库表列建立起来的顺序。它提供了快速访问数据的途径，并且可监督表的数据，使其索引所指向的列中的数据不重复。

7)存储过程：存储过程是为完成特定的功能而汇集在一起的一组SQL 程序语句，经编译后存储在数据库中的SQL 程序。

8)触发器：触发器是一个用户定义的SQL 事务命令的集合。当对一个表进行插入、更改、删除时，这组命令就会自动执行。

9)ER图：实体关系图

10)范式：

第一范式：数据库表的每一列都是不可分割的基本数据项； 第二范式：要求实体的属性完全依赖于主关键字primary key： SelectCourse 表(学号, 姓名, 年龄, 课程名称, 成绩,学分)

1、数据冗余

2、更新异常

3、插入异常

4、删除异常

第三范式：数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖；

11)事务特性：原子性、一致性、隔离性、持久性

设置oracle事务隔离界别：

set transaction isolation level serializable;

未提交读：事务A可以读取事务B未提交的数据。

提交读：事务A只能读取事务B提交了的数据

可重复读：在同一个事务A里，先后执行同一个查询语句，得到的结果是一样的。

序列化读：事务A在执行的时候不允许其他事务的执行，隔离级别最高。

12) SQL：结构化查询语言，是操作关系型数据库中的对象。

二、SQL语句

SQL Statement (SQL 语句)

1)DDL Data Defination Language数据库定义语言

create,alter,drop,truncate

2)DML Data Management Language数据库操纵语言

insert ,upate ,delete

3)DCL Data Control Language数据库控制语言

grant revoke

4)TCL Transaction Control Language)数据库事务控制语言

commit,rollback,savepoint

5)Select数据库查询语句

Select

三、数据库查询

1、查询操作

1）投影操作只查询表的某些字段如: select name,birthday from student;

2）关联操作 多个表操作

3）选择操作 根据条件筛选记录，可认为是添加了where子句

2、查询语句

select [dintinct] {*,column [alias],?} from tablename;

1）投影操作

①select table_name from user_tables;查询系统表

②desc 表名;查看表的结构

③去重复数据 distinct

例：select distinct dept_id,title from s_emp;

这样写的话,distinct 会管到后面的所有字段,即对所有字段排序后,再排重。即排重的是dept_id和title的组合。

在select 语句中可以使用数学表达式。

select first_name ,salary*12 from s_emp;

④别名，三种方法

1、select first_name name from s_emp;

2、select first_name as name from s_emp;

3、select first_name "first name" from s_emp; 如果别名中有空格或大小写敏感,要把双引号括起来；父查询可调用子查询的别名，当前查询调用当前别名需要看执行顺序；双引号别名调用时仍需双引号但是内部字母区分大小写；且识别空格，有多少是多少；多个空格在显示时只有一个空格，无空格则不显示，这是和使用相区别；只有双引号中能添加空格；若别名中有空格则加上” ” ⑤字符串拼接使用||符号

select first_name||’ ’||last_name from s_emp;（||’ ’|| 无论哪里出现空格都对拼接无障碍且无空格显示）

⑥空值的处理 ---nvl(column,defaultvalue)函数

select first_name,12*salary*(1+nvl(commission_pct/100,0)) from s_emp; nvl函数是指当第一个参数column如果为空(null值)的话，Oracle就会默认为第二个参数defaultvalue的值，如果第一个参数不为空的话，就选择第一个参数本身的值。

内容区分大小写；

2）选择操作:

①排序 order by 字段 | 别名 | 字段位置

升序（asc）|降序（desc）

select first_name,salary from s_emp order by salary;默认的是升序 ②排序字段的空值处理

降序时null值排在前,升序时null排在后

select first_name,salary from s_emp order by first_name,salary desc; 按first_name 升序排序,如果first_name相同,再按salary 排序

能够触发排序的关键字:distinct、order by、group by

3、where子句

where子句使用在select ... from ... 后面，用来选择所需（符合条件的）的记录where后面跟的是表达式也就是 XXX=XXX，XXX between X and X，XXX in （X，X，X），like '...'等

请注意select columns from table where clause

是先from表再where条件，最后select出你要的字段。

非数字类单引号引用；

1)通配查询

between ...and ...表示结果在这区间之间，between and是一个闭区间，

也就相当于... <= ... and ... >= ... 。

!=，<>，^=，这三个都是指不等于，且<=，>=，=，这些运算符也可以使用。

in (va1,val2,...)表示判断结果是否在这个枚举中存在，即每一个值都去匹配

like表示字符串通配查询，'%'表示任意多个字符，'_'，表示任意一个字符。 注意：转义的用法：like ‘S\_%’ escape ‘\’;

escape指定义什么是转义符，可以是任意的特殊符号，包括数字和字符串，但数字和字符串要是定义为转义符会不会对操作造成影响需要考虑考虑。

2）逻辑符

and表示只有两个或多个条件同时满足。

or表示条件只要满足其中只一就可以。

all表示条件都满足时。

not表示是可以与以上的条件产生反效果。

空值会对not in造成影响，也就是不等于任何值，但是空值例外，空值跟任何值比较都为空。not in不包含空值，null不被算在内。不包含在里面的值可能是有空值但是空值会被not in排除在。

is null表示判断值是否为空。

注意：Oracle中的字符串是严格区分大小写的。这里所谓的字符串是指数据表中的数据(信息)。

select {*,column[s] [alias],...} 在这里实现投影操作

from table_name

[where clause] 查询条件 注意:[ ] 表示这部分内容可有可无

[order by columns(asc|desc)] 排序子句

注意:[ ] 表示这部分内容可有可无;(|)选择其一

四、单行函数

1、字符函数

lower(字段名)表示转小写，其中的参数可以是一个字符串常量或是一个字段名 upper(字段名)表示转每个单词首字母大写；

initcap(字段名)表示首字母大写；

concat(字段1,字段2)表示字符串拼接；（||’‘||）

substr(字段名,起始位置，取字符个数)表示截取字符子串，位置是从1开始 length(字段名)表示该字段的字符长度，打出字符串长度

例：select substr(first_name,-2,2) sub from s_emp;（取后两个） select substr(first_name,2,2) sub from s_emp;（取前两个）

使用"-"表示取后面的字符串，取的时候仍是从左往右取。

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，单位是天

日期是格式敏感的

日期内置函数：

months_between（sysdate,addmonth(sysdate,5)） //两个月有多少天。 add_months(sysdate,-5) 在系统时间基础上延迟5月

add_months(sysdate,-5*12) 在系统时间基础上延迟5年

last_day(sysdate) 一个月最后一天

next_day(sysdate,’Friday’) 下个星期星期几

round (sysdate,’day’) 不是四除五入，是过了中午的留下，不过的略掉 trunc(sysdate,’month’) 不到一月的都省略

例：

round('25-MAY-95','MONTH') 01-JUN-95

round('25-MAY-95','YEAR') 01-JAN-95

trunc('25-MAY-95','MONTH') 01-MAY-95

trunc('25-MAY-95','YEAR') 01-JAN-95

4、不同数据类型间转换函数

将字符转换成数字 to_number('...')

将数字转字符to_char(number，'fmt') fmt是数字格式

将字符串转成日期 to_date('...','日期格式')

例：select to_char(to_date('2006 11 03','yyyy mm dd'),'dd-month-yy') from dual；

select to_char(3456.789,'9,999.999') from dept;变成一个字符串的格式，9是一种显示格式，运行结果3,456.789除了9之外还可以写0,本位上有的话直

接写，没有的话补0

select to_char(3456.78,'000,000.000') from dept;运行结果003,456.780 select to_char(3456.78,'fm$99,000.000') from dept;去除前面的空格

select to_number('123.4')+3 from dept;有默认的转换即去掉to_number，不同类型的转换可以强行也可以自动(不是所有的都可以)

格式是9时：9的整数个数不能小于数字的整数位数，小数部分没有的话会补0 0时：0的整数个数不能小于数字的整数位数，前后没有的都补0

改变NLS_LANG的值让输出结果的货币单位是￥或$，需修改环境变量，临时的即可

setenv NLS_LANG ' SIMPLIFIED CHINESE_CHINA.ZHS16GBK'

setenv NLS_LANG ' AMERICAN_AMERICA.US7ASCII'

在XP系统的浏览器或Oracle的PLSQL中可使用下面指令修改语言环境，之后并且要输入commit提交：

ALTER SESSION SET NLS_LANGUAGE=american;

ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';

5、关联操作

①等值查询：表之间的连接是通过相等的字段值连接起来的查询称为等值连接查询。

查询员工的名称,部门名称,地区名称

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;

查询ben在那个部门那个地区上班

select e.first_name,d.name,r.name from s_emp e,s_dept d,s_region r where e.dept_id=d.id and d.region_id=r.id and lower(e.first_name)='ben';

②非等值连接: 连接条件使用除等于运算符以外的其它比较运算符，比较被连接的列的列值。这些运算符包括>、>=、<=、<、!> 、!<和<>等

查询公司员工工资级别

工资级别表 salgrade字段: id,losal,hisal (级别,最小工资,最大工资)

select e.first_name,s.id from s_emp e,salgrade s where e.salary between s.losal and s.hisal;

③自连接：可以把一张表看成多张表

查询员工的经理名称

select e.first_name as emp_name,m.first_name as manager_name from s_emp e,s_emp m where e.manager_id=m.id;

为一个表起了别名,就要用它的别名,不能再用表的原来的名字

如果为一个表起了两个别名,那就是两张别名表自连接是指同一个表中记录与记录之间的关系

解决自连接方法:通过为表起别名

④外连接：一个都不能少

外连接查出的数据=内连接查出的数据+内连接匹配不上的数据

select e.first_name as emp_name,m.first_name as manager_name from s_emp

e,s_emp m where e.manager_id=m.id(+); 有(+)表示本方会为对方补空值 注意条件(+)跟在要全部选出的一方 , 即一方比一方多余的记录要输出的加（+），不能使用in 和or；

from t1,t2 where t1.c1 = t2.c2(+)

把t1表中匹配不上的记录重新找回来

from t1,t2 where t1.c1(+) = t2.c2

把t2表中匹配不上的记录重新找回来

Self join 自连接

From t1，t2

Where t1。C1= t2。C2（inner join）

From t1，t2

Where t1。C1= t2。C2（+）（outer join）

T1表中匹配不上的记录找回来

Where t1。C1（+）= t2。C2

T2表中匹配不上的记录找回来

Outer join=inner join+匹配不上来的；

五、组函数

1、group by表示分组函数，having表示对查询结果进行过滤

where子句用来筛选from子句中指定的操作所产生的行，不能跟组函数 group by子句用来分组where子句的输出

having子句用来从分组的结果中筛选行

2、组函数

a)avg查询平均值

b)count查询记录条件,参数可以是任何类型，忽略空值；多少条记录不包含空值；

c)max查询最大值，字符串从左边第一个开始比，第一相同就比第二个，第一个不相同大的徘前面；

d)min查询最小值 e)sum查询数字的和，貌似只用于数字，反正字符串 不能用；

所有组函数的计算都会忽略空值avg、sum只能用于数值类型

3、注意：group by子句也会触发排序操作，会按分组字段排序，即使用order by 查询提成平均值

select avg(nvl(commission_pct,0)) from s_emp;

查询员工分布在几个不同的部门

select count(distinct dept_id) from s_emp;

查询42部门的平均工资

select avg(salary) salary from s_emp where dept_id=' 42';

查询不同部门的平均工资

select e.dept_id,avg(salary) from s_emp e group by e.dept_id; 查询不同部门不同职位的平均工资

select e.dept_id, e.title,avg(salary) from s_emp e group by e.dept_id, e.title;

注意: 当使用group by语句时，select后面的只能是组函数和group by后面的字段，若不使用group by语句时，select后面的可以是普通字段或者全部是组函数

查询不同部门不同职位的平均工资 (要求显示部门名称)

select d.name, e.title,avg(salary) from s_emp e,s_dept d where e.dept_id=d.id group by d.name, e.title;

查询不同部门的平均工资 (要求显示部门名称) 根据部门编号和部门名称分组 select e.dept_id, d.name,avg(salary) from s_emp e,s_dept d where e.dept_id=d.id group by e.dept_id,d.name;

求42部门的平均工资

select e.dept_id,max(d.name),avg(salary) from s_emp e,s_dept d where e.dept_id=d.id and d.id=42 group by e.dept_id

4、SQL结构

SELECT {*,COLUMN1 [ALIAS1],...}

FROM TABLE_NAME

[WHERE CLAUSE] 查询条件

GROUP BY

HAVING

[ORDER BY CLAUSE] 排序子句

5、子查询：就是可以嵌在任何的sql语句中的select语句，把子查询的结果当做范围再区查询一遍

在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边

注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值，in）。配合使用子查询返回的结果必须符合运算符的用法

查询和smith做同样工作的员工

select last_name from s_emp where title =(select title from s_emp where lower(last_name)='smith') and lower(last_name) <> 'smith';

select last_name from s_emp where title in (select title from s_emp where lower(last_name)='smith') and lower(last_name) <> 'smith';

如果能保证子查询结果为单值可以用 =,如果不确定可以用 in

查询大于32部门平均工资部门的平均工资

select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp where dept_id=32);

查询哪些人不是领导

select first_name from s_emp where id not in (select manager_id from s_emp where manager_id is not null);

哪些员工的工资和本部门的平均工资一样

select first_name,dept_id,salary from s_emp where (dept_id,salary) in (select dept_id,avg(salary) from s_emp group by dept_id);

6、rownum实现分页

oracle查询前十条语句

select * from (

select A.*, rowmun rn from (select * from s_emp) A

where rowmun <= 10

)

where rn >= 1;

查询10~20条记录

select * from (

select a.*, rownum rn from (select * from s_emp) a

) where rn between 10 and 20;

From，where 后面可以跟子查询:SELECT first_name,salary

FROM s_emp t1,(SELECT dept_id,AVG(salary) avgsalary FROM s_emp group by dept_id) t2 WHERE t1.dept_id=t2.dept_id

AND t1.salary>t2.avgsalary;

rownum：对于一个查询返回的每一行，rownum伪列返回一个数值代表的次序。 rownum伪列特点：

1）要么等于1 要么小于某个值，不能直接等于某个值, 不能大于某个值

2）常用于分页显示

返回的第一行的rownum值为1，第二行的rownum值为2，依此类推。通过使用rownum伪列，用户可以限制查询返回的行数

如：select * from s_emp where rownum<11; 从s_emp表中提取10条记录

六、数据建模

画E-R 图

Entity 实体:有共同属性的一类对象的抽象

Entity 之间关系:

一对一(one-to-one)；一对多(one-to-many)；多对多(many-to-many) 完整性约束：

1、PK(Primary Key 主键约束)，唯一且非空,保证数据是干净的

如果一个表有任何一个字段是唯一的,可以用两个字段联合起来做的唯一标识,称为联合主键

引用完整性约束

2、FK(Foreign Key外键约束)至少存在两张表

把外键所在的表称子表,其引用的表称为父表,外键的一些规范约束，是在子表中设置外键，一定是与父表的主键对应

先建父表,后建子表

先删子表,再删父表

3、UK(Unique Key唯一约束) 唯一可以为空

4、not null非空约束

所谓的约束是指在建表的时候对字段设置，当插入(insert)数据时会根据约束对插入的数据进行检查，例如某一字段为not null，若插入该字段的记录(数据)是一个null值，就会报擦插入数据错误。

把ER图关系模式转化成表

关系：

一对多 多的那边做外键

多对多 用中间表把两个表连起来,学生表---课程表是多对多关系,需要建立一个中间表,学生选课表,这个包含两个表的主键做为它的自己的联合主键 一对一外键加个唯一约束

范式

第一范式(1NF):在关系模式R中的每一种具体关系r中，如果每个属性值都是不可再分的最小数据单位，则称R是第一范式的关系。

第一范式简单的说就是要求属性具有原子性，不可以再分，第一范式面临的问题？引出第二范式

第二范式(2NF)：如果关系模式R(U,F)中的所有非主属性都完全依赖于任意一个候选关键字(可以使组合式的主键)，则称关系R是属于第二范式。

第二范式简单的说，就是每个表都有个主键，其他字段完全依赖于该主键，第二范式的问题？引出第三范式。

第三范式(3NF)：如果关系模式R(U,F)中的所有非主属性对任何候选关键字(即主键)都不存在传递依赖，则称关系R是属于第三范式。

数据库定义语言

a)数据库对象：Table View Index Sequence

b)建表定义表的数据结构

数据类型

varchar(n) 可变类型，按实际数据的长度存储

char(n) 定长，按定义的长度存储

number(m,n) 数值类型，可以定义宽度，也可以不定义宽度，默认缺省为38 date 日期类型，一定不能定义宽度

char类型是一个固定长度的类型，会补空位；varchar和varchar2都是可变的， varchar是sql的标准，再别的数据库中都有，varchar2是oracle自身的标准。 char类型最多的字符是4000个！

建表的时候请注意字段与字段之间用“，”分割，最后一个字段不需要“，”标示，所谓字段如下的c1、c2等，数据是指数据表实际存在的信息，可通过insert、update等实现数据信息持久

create table sunzw_test(-----------创建表

c1 varchar2(10),

c2 char(10),

c3 number(3),

c4 date

);

insert into sunzw_test values('sunzw','123',200,to_date('2008 11 4 12:34:12','rrrr mm dd hh24:mi:ss'));---要注意数据格式和精确度 create table sunzw_test(

c1 varchar2(10) constraints sunzw_test_pk_c1 primary key,---把c1设为主键，列级约束

c2 char(10),

c3 number(3),

c4 date

);

create table sunzw_test(

c1 varchar2(10),

c2 char(10),

c3 number(3),

c4 date,

constraints sunzw_test_pk_c1 primary key(c1)---表级约束

);

注意：定义联合主键时,只能用表级约束

create table sunzw_test(

c1 varchar2(10),

c2 char(10) ,

c3 number(3),

c4 date,

constraints sunzw_test_pk_c1（约束名可有可无） primary key(c1,c2) );

约束

NOT NULL 约束 (这个字段不允许为空)

create table sunzw_test(c1 number not null,c2 number);---为c1定义了非空约束

NOT NULL 约束只能定义为列级约束

UNIQUE 唯一约束

create table sunzw_test(c1 number primary key,c2 number unique);---为c2定义唯一约束

唯一约束可以为空, 唯一约束不考虑空值联合主键约束,这时只能用表级约束 create table sunzw_test(

c1 number,

c2 number,

unique(c1,c2) ---c1和c2联合唯一

);

PRIMARY KEY (主键约束) 非空且唯一的定义

create table sunzw_test(c1 number primary key,c2 number);

create table sunzw_test(c1 number not null,c2 number,unique(c1)); create table sunzw_test(c1 number unique not null,c2 number);

父表:

create table sunzw_parent(

C1 number primary key,

C2 number

);

子表:

create table sunzw_child (

C1 number primary key,

C2 number references sunzw_parent(c1)---C2 是parent表的外键 );

先建父表,再建子表

错误

insert into sunzw_child values(1,1);---违反外键约束，因为父表中没有c1为1的记录让子表引用

正确

insert into sunzw_parent values(1,1);---先插父表

insert into sunzw_child values(1,1);---后插子表

注意:删除表时,要先删子表,再删父表

drop table sunzw_child;

drop table sunzw_parent;

或者

drop table sunzw_parent cascade constraints;

级联约束，这样就删掉了父表和子表的FK约束

表级约束定义格式

create table sunzw_child(

C1 number primary key,

C2 number,

foreign key(c2) references sunzw_parent(c1)

);

级联删除约束

create table sunzw_child (

C1 number primary key,

C2 number references sunzw_parent(c1) on delete cascade---级联删除,删除父表记录时,会级联把子表中的记录删除

);

C2是parent 表的外键

create table sunzw_child (

C1 number primary key,

C2 number references sunzw_parent(c1) on delete set null---级联删除,删除父表的记录时,会把子表中引用字段变为null

);

CHECK 检查约束(CK)

定义一条件一约束字段

列级定义:

create table sunzw_child (

C1 number primary key,

C2 number check(c2>200)

);

表级定义:

create table sunzw_child (

C1 number primary key,

C2 number not null,---同check(c2 is not null) 和非空的效果一样 check(c2>200)

);

创建三张表 学生表,课程表,选课表

drop table sunzw_student cascade constraints;

drop table sunzw_course cascade constraints;

drop table sunzw_stu_cur cascade constraints;

create table sunzw_student(

sid number(10) primary key,

name varchar2(10),

age number(2),

sex varchar2(2),

birthday date

);

create table sunzw_course(

cid number(10) primary key,

name varchar2(50));

create table sunzw_stu_cur(

sid number(10),

cid number(10),

score number(10),

primary key(sid,cid),

foreign key(sid) references sunzw_student(sid),

foreign key(cid) references sunzw_course(cid)

);

用已经存在的表创建新的表

create table sunzw_emp as select id,first_name,last_name,salary from s_emp where dept_id=41;

只会把非空约束带过来，其他的约束在建新表时会自动消掉，所以除非空约束外，其他约束还要自己再加，符合的数据也带过来

复制表的结构

create table sunzw_emp as select id,first_name,last_name,salary from s_emp where 1=2;

1=2表示只取表结构，不要表中数据的建表方式，即表中的数据记录

DROP TABLE 删除表

drop table sunzw_test1 cascade constraitns;---删除表时级联删除这个表的约束

TRUNCATE TABLE tablename;

TRUNCATE比DROP快，在删除一个表中的全部数据时，须使用TRUNCATE TABLE 表名;因为用DROP TABLE或DELETE FROM 表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次DROP，DELETE操作后，该TABLESPACE上百兆的空间就被耗光了

truncate table删除表中所有记录，并释放其占用的空间，执行快

数据不可恢复（不能commit）

delete from删除表中所有记录，不释放其占用的空间，执行慢

七、数据操纵语言（DML）

1、INSERT

INSERT 一次只能插一条

INSERT INTO tablenmae VALUES(colvalue,colvalue,?);

INSERT 语句中可以放函数 insert tablename values(12,sysdate)

INSERT 可以只插入某个列的值，INSERT into tablenmae (sunzw_emp（列名）) values（列值）

INSERT 可能违反各种约束

2、UDPATE

UPDATE tablename SET column=value,column=value? WHERE condition 注意:如果不加where条件,修改可影响到所有的记录,所有记录将被修改 只能一条一条跟新

3、DELETE

DELETE FROM tablename WHERE condition;

用DELETE 删除一张表的记录,删除之后,数据还能找回来

更多关于DML的写作规范及约束参考SQL语句.doc

八、数据库事务控制语言

银行转账:A账户向B账户转账1000

Create table sunzw_balance(id number(10),balance number(10)); insert into sunzw_balance values(1,150000);

insert into sunzw_balance values(2,40000);

1向2转账10001如何转账:

update sunzw_balance set balance=balance-1000 where id=1; update sunzw_balance set balance=balance+1000 where id=2;

服务器中断服务以及其它的原因都可能导致更新一个账户后,另一个账户没有更新

如何保证两个操作同时成功,同时失败呢?

要用事务来保证

update sunzw_balance set balance=balance-1000 where id=1; update sunzw_balance set balance=balance+1000 where id=2; commit|rollback;

commit 提交将更新的数据保存到数据

rollback 回滚将数据恢复到修改前的状态

savepoint 保存点

savepoint point1;创建一个保存点

rollback to point1;回滚到指定的保存点

注意：做DML操作中注意要提交或回滚事务DDL，DCL会自动做提交操作

数据库事务是指作为单个逻辑工作单元执行的一系列操作 事物的四个特性

atomic原子性

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行

consistent一致性

事务在完成时，必须使所有的数据都保持一致状态

insulation 隔离性

由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据 Duration持久性

事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持

事务能够保证相关的一组事务一起成功或一起失败

有两种级别的事务：

局部事务（数据库的操作都是针对一个数据库的同一个用户）

全局事务（分布式事务）（数据库操作是针对多个数据库或则一个数据库的不同用户下的表）

九、索引、序列、视图

sequence 给table解决pk问题

index给table解决select效率问题

view看table的子集和超集

sequence,index,view 都为table服务,

如果把表删了,index就删掉了,view会变成无效

1、伪列

伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新或删除它们的值，常用的伪列：rowid 和rownum rowid 系统提供的伪列表示记录的真实物理位置。可以使用rowid值来定位表中的一行，通常情况下，rowid值可以唯一地标识数据库中的一行

rowid伪列有以下重要用途：

1）能以最快的方式访问表中的一行

2）能显示表的行是如何存储的

3）可以作为表中行的唯一标识

如：select rowid,first_name from s_emp;

create table s_info_c(

id number(3) primary key,

name varchar2(10),

age number(3)

);

insert into s_info_c values (1,'zhang',23);

insert into s_info_c values (2,'sun',24);

insert into s_info_c values (3,'liu',30);

insert into s_info_c values (4,'sun',23);

insert into s_info_c values (5,'zhang',21);

删除所有重复的记录

delete from s_info_c where name in(

select name from s_info_c group by name having count(*)>1); 删除重复记录并保留其中一条记录，下面的效率不高，通过自建一个副本操作，面试常考

delete from s_info_c a where id>(select min(id) from s_info_c b where a.name=b.name);

高效率

delete from s_info_c a where rowid>(select min(rowid) from s_info_c b where a.name=b.name);

rownum：对于一个查询返回的每一行，rownum伪列返回一个数值代表的次序。 rownum伪列特点：

1）有个特点要么等于1 要么小于某个值，不能直接等于某个值,不能大于某个值

2）常用于分页显示

返回的第一行的rownum值为1，第二行的rownum值为2，依此类推。通过使用rownum伪列，用户可以限制查询返回的行数

如：select * from emp where rownum<11;从emp表中提取10条记录

ROWNUM是内存中生成的值，取出的值是1符合则继续往上加，如果不符合继续，orderby使用之前rownum已经排序，貌似重fron开始;子查询里rownum于表别名的rownum不一样，外部的查询XXX。Rownum是子查询里面的rownum不一样，是新生成的所以应该起别名为子查询里的rownum

SELECT rnm, first_name,id,salary

FROM (

SELECT rownum rnm, first_name,id,salary

FROM (

SELECT first_name,id,salary

FROM s_emp

ORDER BY salary desc

)

WHERE rownum <=10)

WHERE rnm>=6;

2、索引：用户创建，系统维护

建立索引的目的就是为了加快查询速度,建立索引后会使DML操作效率慢，但是对用户查询会提高效率。删除一个表时，相对应的索引也会删除。另外,索引是会进行排序的。如果要对索引的表做大量的修改操作,会影响效率，索引由数据库自身来维护在创建PK和UK约束时,系统会自动创建唯一性索引

字段建立了索引，select查询时会首先选择索引

索引类型:

1）唯一性索引

Create unique index sunzw_stu_index on sunzw_student(id);

2）单列索引:为单列创建的索引

3）联合索引

Create unique index sunzw_stu_uindex on sunzw_stu_cur(sid,cid); Sid和cid共同对应一个rowid

当多个字段经常同时出现在where子句中建立联合索引

什么样的字段适合建索引

1）查询频率比较高的字段这些字段经常做过虑条件,出现在where子语中

2）经常出现在表连接的字段、条件、FK上创建索引

3）字段中存在大量的空值可创建索引，索引中不存在空值

4）表越大，结果集越小

什么样情况不适合建索引

1）表比较小

2）这个字段不经常出现在where子语中

3）字段经常更新

注：PK和UK系统在建表的时候会自动建立索引

查询结果集很大重建索引

alter index sunzw_index_name rebuild;

根据现有的索引重建一个新的索引,比删掉索引重新建立索引要快，但是有空间消耗

查询表上的索引

select index_name,index_type,uniqueness from user_indexes where table_name ='SUNZW_STUDENT';

删除索引，例从未出现过where子句，不经常使用的最好删除，降低维护开销 drop index sunzw_index_name;

表达式(where c1_1=1)或函数(where round(c1)=1)会导致索引用不了，因为索引里记录的是字段的值，可以建基于函数或表达式的索引，但这样对索引做操作时目的是全表扫描

Create index test_index on salary+1000

建索引create index test_c1_ind on test（c1）

FTS full table scan全表扫描 ： 读出所有记录一个一个的匹配

基于index的扫描

Rowid代表一条记录的物理位置（包含一条记录是属于哪张表（属于哪个Object object_id），是属于哪个数据文件的（file_id）是属于这个文件里德哪个数据块（block_id）在这个block中的第几条记录（row_id））

索引时是树形其中自有叶子是存放的值和rowID，查找类似二叉树

通过rowid快速检索

降低I∕O数量

维护索引（update太多的表不宜建索引）

索引太多会降低效率因为要维护

Alter index test_c1_ind rebuild;(drop index and create index)两个效果一样，前这快 但占空间大，后者慢占空间小

从不用来定位的索引需删掉

如果把表删了，索引一定会被删了

PK uk上是一定会有索引的，你不建数据库会建

索引里没有Null，null必须全表扫描

索引一般条件：经常会出现在连接字段例如FK，

经常出现在where后面的

取值范围特别大的(bitmap index 小指索引 用得很少) 字段里有很多null； 表越大结果集越小索引效果越好（大前提）

索引不一定快，索引是手段

不合适条件：表比较小

不经常使用的 反复修改的

应该drop index的是：从未出现在where里面的

索引类型：

唯一性索引：PK Uk Create unique index xxx on test（c1） 对表进行更新时重复值会报错 用来解决唯一性问题 非唯一性索引： 单列索引： 加快查询效率

索引用不了：where后面跟表达式where c1+0=1

Where后面跟函数where round（c1）=1

实在要用可根据函数或表达式建索引

Where c1=‘1’，c1 number 能用

Where c1=1. c1 varchar2(10)不能用 隐式转换用到函数

Where substr（c1,1,2）=‘ab’不能用因为函数

Where c1 like ‘ab%’能用，这是范围扫描

3、VIEW 视图

视图就相当于一条select 语句,定义了一个视图就是定义了一个sql语句, 视图不占空间,使用视图不会提高性能

视图的作用：

1）简化sql编写

2）限制数据库的访问权限，保证表数据安全

创建视图：

create view 视图名;

Create view xxx as select * from test where c1 =1;

可以desc viewname

Select * from viewname

如：

create or replace view test as select * from test1 where c1=1; create or replace:如果view 存在就覆盖，不存在才创建

force|no force:基表存在时使用，不存在时则创建该表

注意:向视图中插入数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创建时的select语句

删除视图:

drop view 视图名;

视图的约束:

with read only视图只读约束

with check option 不允许插入与where条件不符的记录，类似于check约束的功能

create view test_cc

as

select * from test where c1>10

with check option constraints test_check_op;

联合索引：create index xxxxx on test（c1，c2）

联合外建 联合主键 经常出现where 。。。and。。。。需要建

如果视图存在 表不存 视图无法使用

建表Create table test（c1 number， c2 number）

Alter view viewname compile 自动做编译视图

Select count（*）from test

Select User_views from user_views where view_name=’viewname’ 可以看到视图定义

Select object_name，status from User_objects where object_name=‘viewname’可以看到视图状态

Create view s_emp as select * from hiloo。S_emp；hiloo拥有该表的账户

Grant select on s_emp to sd1007（所在账户）

Default 字段不会为空，你不提供他会自己添加

Oltp在线处理事务系统

4、Sequence序列

create sequence sunzw_s1;

select sunzw_s1.nextval from dual;

select sunzw_s1.currval from dual;

创建一个sequence 之后 ,先用sunzw_s1.nextval取一次,才能取到sunzw.s1.currval;

Sequence 是个可共享的对象,多个表可以共用一个,用来产生主键值

create sequence name

increment by n //步长

start with n 开始值

maxvalue n 最大值

cycle|nocycle表示达到最大值后从头开始

cache n|nocache指定cache的值。如果指定CACHE值，oracle就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失,所以可以在create sequence的时候用nocache防止这种情况。

order;--指定排序

select sequence_name,cache_size,last_number from user_sequences where sequence_name like 'SUNZW_S1';

查询sequence 的是不用缓存,缓存是指在sequence 一次取多个数据放在内在中

修改sequence

alter sequence name increment by n;

删除sequence

drop sequence seqname;

隔离级别

四种读的情况：dirty-read脏读,committed read重复读，repeatable read序列读，serializable read(或是幻影phantom read)

dirty-read：一个数据还没有提交就能读到

Committed read：一个事物提交了另一个事物才能读到

Repeatable read：一个事物更改了一个记录并提交后另一个事物仍然读到修改前的记录（oracle中没有隔离级别支持它！！）

phantom read：一个事物提交增加了一个记录，另一事物前后两次要求读取相同oracle数据库只有serializable read和Committed read

设置更高的隔离级别--- set transaction isolation level serializable; 此时在别的事物中更改记录，改事物中记录仍是原记录

集合

union/union all 并集；union会去掉相交集合中的重复值，union all不会去掉相交集合中的重复值，无论集合是否有交集union都会做去重的操作

select s.first_name,m.first_name from s_emp e,s_emp m where e.manager_id=m.id union all

select first_name from s_emp where manager_id is null

intersect 交集 也会自动去掉重复值

minus

case when函数

select first_name,salary,

case when dept_id=31 then salary*1.1

when dept_id=32 then salary*1.2

when dept_id=33 then salary*1.3

else salary

end as aft_sal

from s_emp;

如果不在选择范围内则返回原值

decode函数

select first_name,salary,

decode(dept_id,31,salary*1.1,

32,salary*1.2,

33,salary*1.3) as aft_sal（可以少as） from s_emp;

如果不在选择范围内则返回空，格式:

decode(字段,条件1,结果1[,条件2,结果2??])

select first_name,max(salary),

max(decode(dept_id,31,salary*1.1)) dept_31,

max(decode(dept_id,32,salary*1.2)) dept_32,

max(decode(dept_id,33,salary*1.3)) dept_33

from s_emp

group by first_name;

EXISTS一检测到符合的就返回执行下一个manager_id

SELECT last_name,id,dept_id

FROM s_emp outer

WHERE EXISTS (

SELECT 'X'

FROM S_EMP

WHERE manager_id = outer.id);

SELECT dname

FROM dept ww

WHERE EXISTS(

SELECT 9

FROM emp

WHERE deptNO=ww.deptNO);

NOTEXISTS ; 原理与EXISTS相同有相同的就返回，只是一个是返回一个是过滤；继续执行下面的

SELECT dname

FROM dept ww

WHERE EXISTS(

SELECT 9

FROM emp

WHERE deptNO=ww.deptNO);

差别在于带not的有结果要求全部不相同，另一个只要有一个相同的就行

EXISTS一检测到符合的就返回执行下一个manager_id

SELECT last_name,id,dept_id

FROM s_emp outer

WHERE EXISTS (

SELECT 'X'

FROM S_EMP

WHERE manager_id = outer.id);

SELECT dname

FROM dept ww

WHERE EXISTS(

SELECT 9

FROM emp

WHERE deptNO=ww.deptNO);

NOTEXISTS ; 原理与EXISTS相同有相同的就返回，只是一个是返回一个是过滤；继续执行下面的

SELECT dname

FROM dept ww

WHERE EXISTS(

SELECT 9

FROM emp

WHERE deptNO=ww.deptNO);

差别在于带not的有结果要求全部不相同，另一个只要有一个相同的就行

字段名之间用逗号隔开

表名与表名之间用逗号隔开

Select name，job from yuangong，bumeng (name 在yuangong表里，job在bumeng表里，两张表里都有员工号) 出现笛卡尔积每张表里各取一条信息相互匹配；

重复的字段名必须指出出处，且只能用表别名，前面用表别名where处也要用表别名,FORM 处不用表别名where处也可以不用；字段别名不可用

<=和>=注意等号位置 等号位置和字段无关

Count（*）统计记录数，有空值

Count（dde）无空值记录数；有列名

Where后面只能跟单行函数，组函数不可以因为where子句过滤的是记录

Having后面跟的是组函数

先执行子查询，当子查询返回多个值时，完成去重，然后将结果返回主查询，再执行主查询

设置变量setenv NLS_LANG.S/MPLIFID.CHINESE_CHINA.2HS16GBK?

默认设置setenv NLS_LANG.USTASCⅡ

WHERE 必须在 GROUP BY前面SELECT 后面有一个组函数其他就必须

都是组 函数，否则报错误；

Vsrcher2按字符串的实际长度存；字段取值不定长，一定有宽度 Char按字符窜的定义长度存，不足的不空格；字段取值定长； 没到长度系统会自动补

INSERT 数据按四舍五入小数位，且无视小数位数，只取规定位，而且小数位四舍五入；整数位多了报错；

Date一定不能定义宽度；

脚本文件：craete table table_name syntax

每条命令后面加“；“然后加commit

Sqlpuls sd0907/se0907 @test.sql 最后的“0907“后面要加空格然后再写；

已运行直接写文件名

Create table table_name ()AS subquery;(查询语句) 该表直接由查询返回结果决定，拷贝的数据和非空约束其他没有；

Intsert into tablename（id，last_name,salary,title）

Select last_name,salary,title from s_emp;

Where start_date<’01-jan-94’;

Rows create

order by 列名（列名，表达式【算术】，别名，位置） desc;（降序从大到小）

Order by 列名 升序

先Where后计算再排序

select first_name ,salary ,commission_pct from s_emp where salary>1000 order by salary desc,commission_pct asc（执行顺序 from?where -?select--?order）

null最大

Select lower(‘SQL Course’) from s_emp; lower 小写 upper大写 如果用于条件语句，在条件上写即可

To_number(‘ab’,’xx’)

To_char(salary,’$99.999.99’) 不并长前面无0

To_char(salary,’$00.000.00’) 并长前面有0

$可以不写，想表示￥，写L(须改变相应的环境变量),不能直接写￥，只能是0 或9

select first_name from s_emp where dept_id='42'<隐式数值类型转换> select first_name from s_emp where dept_id=42

to_number(‘42’)==42<数据字符>

select to_number('ab') from s_emp 错误

select to_number('ab','xx') from s_emp 十六进制-?十进制（171=10*16+11）

<>all Not between Not in Not Like IsNotNull

Where 条件一 AND 条件2 OR 条件3；等价于（满足条件一和条件二的）或者满足条件3的

Where 条件一 AND （条件2 OR 条件 3）等价于{（满足条件一和条件二 ）或者（ 条件一和条件三）}

nvl的两个参数必须为相同类型

表达字符和字符串使用‘’，

echo $ORACLE_SID.\得到数据库实例的名字

sqlplus esd1007/esd1007 数据库连接(本地方式) 一定要设置环境变量 sentenv PATH :$ORACLE_HOME/bin

在vi（edit）中写命令时不可以加;

在终端上写命令是要加;

Sqlplus command

desc 表名=describe 表名

select 列名 from表名；（小心分号）

select salary*12,first_name from s_emp where salary >1200 先做where

select salary*12,first_name from s_emp where salary*12 <12000 这个不能用索引

select salary*12,first_name from s_emp where salary>1000 这个快

where子句不可以做别名；

where dept_id in（32，42,31）等于where dept_id = any(32,42,31)；等于where dept_id=32,or条件2or条件3；