Sql(Oracle)

简单查询

语法：select 字段名1,字段名2,... from 表名；

1.查询部分列

语法：select 字段名。。。。 from 表名

2.查询全部列

语法：select * from 表名

3.为查询的列列取列名

语法： select字段名as别名，表达式[as]别名from表名；其中as可以省略。

注意：别名中如果出现空格或使用sql中的关键字/保留字，则需要使用“”引起来。

4.字符串连接 ||

语法： select 字段名|| “” ||字段名，字段名 from 表名

注意：oralce中的字符串常量以及日期常量，使用‘ ’(单引号)引起来。

5.去除查询结果中的重复数据

   语法：将distinct放在select后面，可以去除结果里的重复数据

select distinct 字段名 from 表名

ORDER BY 语句

语法：select ... from ... order by ...  ；Order by语句用于对查询结果进行排序

1.单列排序

1) 指定时可以是列的列名、列的别名、或者是列在select里出现时的下标。

2) 降序排列时使用关键字desc（将descend放在需要降序排列的列名后面）

   注意：null也参与排序，在oracle里是最大值。

2.多列排序

   语法：Order by还可以根据多个列进行排序，参与排序的类名字依次写在order by的后面，列名与列名之间用”,”隔开。排序时先按照第一列进行排序，第一列取值相同的再按照第二列取值排序，依次类推

条件查询（WHERE）

语法：select列名1，列2 from表名 where过滤条件order by列asc|desc

1.等值 =

   注意：在字符串比较时，''中的内容，严格区分大小写。

2. > >= < <= != AND OR

3. NULL值的处理 ：列名IS [NOT] NULL

注意：NULL值参与运算总返回NULL，
4.范围查询 列名 【NOT】 BETWEEN 小值 AND 大值

注意：小值在前，大值在后；包含边界值。

5.枚举查询： 列名 【NOT】 IN ( 值1,值2,.....,值n)

注意：in的效率较低，开发时不建议使用。

6.模糊查询：列名 【NOT】 LIKE '格式字符串'

  1）格式字符串：包括字符串常量、通配符(%代表0-N个字符;  _代表有且只有1个字符)

2）关于模糊查询中的转义字符

      语法：列名 like '格式字符串' escape '转义字符'。在查询时，如果碰到查询条件里含有特殊字符时，可以使用转义字符解决问题。但是oracle中没有固定的转义字符，如果需要使用，需通过escape关键字声明。

   注意：

 like后的内容，必须放在''中；like是模糊查询,''中必须有通配符

 模糊查询时，Oracle会将列值转换为字符串后进行比较

CASE... WHEN 语法结构

基本语法：case...when语句类似于java中的switch...case语句，用于根据不同条件返回不同的值，其语法结构为：

   

 case when exp1 then 结果1          when exp2 then 结果2  ....        else  其他结果    end

     其中exp1,exp2 , exp3 , exp4均为布尔表达式。执行流程是如果exp1为真，则返回value1 , 同时忽略对后面的exp2,exp3的判断。如果exp1为假，则接下来判断exp2。若exp2为真，则返回value2的值，依次类推。如果exp1,exp2,exp3均为假，则返回else后面的value4。需要注意的是else并不是必须出现在case when结构中的。如果我们没有指定else，同时case  when结构中所有的条件均为假时，返回NULL。

eq ：

  select first_name , salary ,    case       when salary < 6000 then 'low'       when salary between 6000 and 10000 then 'middle'       else 'high'     end  as "sal_level "from   ployees

注意：

ü then语句中的返回值可以是字符串，日期，数字，但一定不能是布尔值。

ü 所有返回值的数据类型必须一致。

单行函数

1.基本概念及预备知识

a) 所谓单行函数就是作用与表里的每一行数据，执行一次的函数。

a) DUAL表

      一行一列的特殊表，属于SYS用户。如果我们只需要函数执行一次，可以使用个表。dual表里的数据没有任何意义，只是为了维护select语句的完整性。

2.字符串相关函数

1）length(str) --获取字符串str的长度

2）字符串连接 ||

3）substr(str,begin,length)

对str做截取，从begin(下标从1开始)开始截length位

4）instr(str1,str2,begin) -- 在str1里查找str2，从begin（下标从1开始）开始查找。找到返回str2首字母所在的下标，否则返回-1。

5）lower(str) , upper(str) -- 将str转换成小写/大写

注意：与java一样，函数可以嵌套调用，只要参数类型匹配即可。执行时，从最内层开始，由里向外，逐层计算。

3.数学函数相关

1）mod(num1,num2) -- 取模，num1%num2

2）trunc(n,length) --对数字n进行截断，精度到小数点后length位

3）round(n1, length) --对数字n四舍五入，精度到小数点后length位

4）abs(num) --对num取绝对值

5)获取随机数：dbms_random.random() , 产生一个很大的随机数（可正，可负）

4.日期相关操作和函数

时间与日期是数据库中常用的数据类型，在处理很多运算的时候都需要用到。

1）SYSDATE表示当前系统时间

eg：1）Select sysdate from dual;  --->获取当前系统时间

     在sysdate提供的时间数据中，不仅包含年月日的信息，还包括时分秒的信息，

     甚至可以精确到毫秒。本例中我们只看到了年月日信息是因为oracle默认的日

     期显示格式省略了时分秒等额外信息。oracle里默认的日期格式为'dd-mon-rr'。

    2）显示明天的时间

 select sysdate+1 from dual

注意：对于日期进行算术运算时，默认单位是天

2）add_months(myDate, num) --对myDate做月份的算术运算

eg：--显示上个月的今天

     select add_months(sysdate, -1) from dual

3）last_day(myDate) --获取myDate所在月份的最后1天

eg：--获取当前月份的最后1天

    select last_day(sysdate) from dual

4）months_between(myDate1,myDate2) --计算两个日期之间相隔几个月，返回值为小数

5）round(myDate,日期格式字符串) --对myDate日期，根据指定日期格式做四舍五入

6）trunc(myDate, 日期格式字符串) --对myDate日期，根据指定日期格式做截断

可选日期格式字符串:  year--按年截断  month-按月截断  day-星期  省略格式字符串表示截断时分秒。

eg：select trunc(sysdate,'yyyy') from dual --年的第1天

     select trunc(sysdate,'mm') from dual --月的第1天

     select trunc(sysdate,'day') from dual --星期(周)的第1天

   select trunc(sysdate) from dual; --当天的零时零分零秒

5.类型转换相关操作和函数

1）to_number(str) --将给定的字符串str转换成数字类型值

eg：select to_number('1234') from dual; --->将字符串1234转换成数字1234

注意：如果给定的str不是有纯数字组成，sql无法执行，报异常。

2）to_char(myValue,'fmt') --将给定的myValue值按照指定格式fmt转换成字符串类型值

1）to_char(myNumber,'格式字符串') --将数字变成字符串

   常见格式：0或者9表示占位，还可以使用各种货币符号￥$等。

2）to_char(myTime,'格式字符串') --将给定的日期值转换成字符串时间表示【重点】

  因为oracle默认的日期显示格式省略了时分秒等额外信息。如果我们想在现实

    时间的时候看到时分秒的信息的话，就需要手动指定日期的显示格式了。

   常见格式字符串：

    年： yy   yyyy   rr   rrrr   year(年的英文全拼)

   月： mm   mon(英文单词缩写)   month

   日： dd   ddsp(英文全拼)  ddth   ddspth(英文全拼的序数词)

   星期： d   dy(英文单词缩写)   day

   小时： hh---am，pm   hh24

       分钟：mi    秒：ss

作用：

a) 把myTime日期类型根据指定的日期格式字符串，转换成字符串显示

eg：--按照年月日时分秒的格式，显示当前日期时间

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day') from dual;

b) 根据给定的日期格式字符串，获取日期的不同部分

eg：--显示当前月份

select to_char(sysdate, 'mm') from dual

3）to_date(strDate, 日期格式字符串) --根据给定的日期格式字符串，把字符串转成日期 

6.NVL函数

1) 问题引入：

      与java语言类似，Oracle中使用NULL代表一个值不存在。需要注意的是NULL值参与运算总返回NULL，这给我们的运算过程带来问题。

2) 问题解决：

         nvl(n1,n2) --判断n1是否是null，是null执行n2，否则执行n1。(n1==null?n2:n1)

组函数，分组语句GROUP BY

1.组函数

  我们前面学过的函数有一个共同的特点，就是只能对一条数据进行运算。比如to_char()在执行一次的情况下只能转化一个日期。在SQL语句中，还有另外一组函数，他们不是针对一条数据进行计算，而是对一组数据进行统计，这类函数统一称作组函数。

1）sum(列名)：求一组数据的总和

avg(列名)：求一组数据的平均值.

max(列名)：在一组数据中找出最大的一个值.

     min(列名)：在一组数据中找出最小的一个值

   注意：这些函数在进行运算时都会忽略NULL值

2）count

1) count(列名) --对查询结果中该列的非空值记数(统计结果中该列不为null的行数)

2) count(*) --对查询结果记数(统计查询结果的数据行数)

3) DISTINCT与count联合使用

4) count特殊用法：count(1)   count(null)【提高】

2.数据分组 GROUP BY

 1) 语法：select ... from ... where ... group by分组条件

 2）细节：组函数是把所有查询结果看成一个大组，然后对这个大组进行统计。

 group by后面跟的是分组列的列名字，他会将查询结果中分组列取值相同的数据归为一个小组

 规则1：只有出现在group by中的列才能出现在select子句中或order by子句中

 规则2：如果在group by子句中的列上应用了某些函数，select子句中或order by子句的相应列也必须应用同样的函数

 规则3：那些没有出现在group by中的列，只有配合分组函数可以出现在select子句中 或order by子句中

3.HAVING 子句 ：对分组后的结果进行过滤

1）语法： select ... from ... where ... group by... having过滤条件order by ...

2） where 和having区别：

Ø where : 对分组前的数据进行过滤

Ø having: 对分组后的结果进行过滤

         如果where和having都可以实现过滤，优先使用where，因为效率高。 如果使用分组后的结果过滤，必须使用 having。

4.select 语句的语法顺序

Ø 编写顺序：

     SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

Ø 执行顺序：

    WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

1) 通过where条件，对表中的每个数据进行一次判断，符合条件被加入到查询结果中

2) 如果使用了group by子句，会对查询结果进行分组操作。

3) 通过having子句，对分组之后的数据进行过滤

4) 执行select后面的各个函数和运算得到最终结果

5) 通过order by语句对最终结果进行排序.

 子查询

1.伪劣

伪劣概念

从字面意思进行理解，伪列就是假的，不存在的列。这些列在表中并不存在，但是可以通过查询语句查询出来。Oracle常用的伪列有ROWID和ROWNUM。

1）ROWID 伪劣

Rowid伪列能够在数据库中唯一标示一条记录。它的运算方法很复杂，是根据数据在硬盘上的存储位置( 扇区，磁道)运算得出的，所以通过rowid能够在整个数据库范围内唯一标识一条数据。同时，因为rowid中包含数据在磁盘上的存储地址，所以通过rowid查询对应的数据效率是最高的。但是rowid没有任何含义，通过rowid作为条件的查询并不常用。

2）ROWNUM伪劣

关于rownum的查询可以看出，rownum的功能实际上是给查询结果一个编号，第一个满足查询条件的数据，对应的rownum是1，第二个满足查询条件的数据rownum是2。如果有更多满足查询条件的数据，rownum依次递增。利用rownum，我们可以实现很多实用的查询。比如，我们想查询公司的全前5名员工，就可以通过rownum<=5来进行判断。Select  *  from employees where rownum <= 5 ;

rownum用作条件比较时，只能用于< ,  <=,  =1,  >=1条件

3）ROWNUM与ORDER BY联合使用

sql中order by子句是在查询完成之后执行的，而Rownum是在查询进行过程中产生。

4）伪劣补充知识点

1) 用于建立B Tree索引

2) 用于快速删除表中（某列取值）重复记录

   

 delete from emp e where e.rowid >  (select min(rowid) from emp          where e.ename = ename group by ename having(count(ename) > 1));

     员工姓名相同的条件下，保留rowid取值最小的，删除其余(当然使用主键也可以，但表中未必建有主键，而且使用rowid更快)

2.子查询

1）子查询概念

1) 查询的结果如果是一行一列，可以将结果看做一个值，参与条件比较或显示在SELECT子句。

2) 查询的结果如果是多行一列，可以将结果看做多个值，参与条件比较或显示在SELECT子句

3) 查询的结果如果是多列，可以将结果看做一个临时表，针对此临时表进行再次查询、分组、排序、表连接等各种操作

2）将子查询看做是一个值

查询工资低于公司平均工资的员工信息.

     第一步：查询公司的平均工资

   

  select avg(salary) from employees;   -----> result1

     第二步：查询工资低于平均工资的员工信息

   select first_name , phone_number , salary from employees where salary <result;

     合并sql：

   

  select first_name , phone_number , salary from employees      where salary < ( select avg(salary) from employees )  

3）将子查询看做多个值

当子查询的结果是多个值（N行1列）时，我们可以把这多个值看做是一个枚举，那么它可以直接参与in的运算。

步骤1：我们首先要先查出King所在的部门，代码和结果如下：

    

select department_id from employees where last_name = 'King';

步骤2：步骤1的结果告诉我们，因为名字不是唯一的，所以通过“King”这个姓可以

           得到两个部门编号（80,90）。可以查询部门in (dept)的员工

select employee_id,last_name,salary,department_id from employeeswhere department_id in (80,90);

     步骤3：合成SQL

       

 select employee_id,last_name,salary,department_id from employees        where department_id in                 (select department_id from employees where last_name = 'King');

4）将子查询看做一个临时表

 select *     from ( select * from employees order by salary desc )    where rownum <=5 ;

5）数据分页查询

步骤一：对所有员工按照工资排序，将排序后的查询结果看成一张临时表（tab1）

   

     select * from employees order by salary desc;   ---->tab1

步骤二：对此临时表进行查询，使用ROWNUM限制返回前10行数据，并且为rownum

       定义一个别名ron

       select tab1.* , rownum ron from tab1 where rownum<=10;   ---> tab2

步骤三：对tab2再次查询，提取其中的第6到第10行。

     

  select tab2.* from tab2 where tab2.ron <=10 and tab2.ron >=6;

步骤四：合并sql

  

     select tab2.*       from (   select tab1.*,rownum ron                from  ( select * from employees order by salary desc ) tab1               where rownum<=10            ) tab2       where tab2.ron between 6 and 10;

表连接查询 

 

在我们前面学习的查询技术中，有一个共同的特点：查询的数据全部来自同一张表。很多时候，我们需要同时查询多张表才能获取所需的完整数据。

1.内连接 1）概念定义

所谓内连接，就是先确定A, B两个表的连接条件，然后拿A表里面的每一条数据与B表中的所有数据依次比较，判断连接条件是否成立，如果连接条件成立，则将来自两个表中的两条数据合二为一，保存到结果集中。如果条件不成立，就继续和B表中的下一条数据进行比较。如果A表中的一条数据在整个B表里都无法找到一条与之对应的记录，那么这条数据就不会出现在查询结果中了。最终，结果集中保存的都是连接条件成立的数据。

2）SQL语法：

  了解了内连接的工作原理之后，我们来学习一下内连接的SQL语法 ：

  select  e.* , d.* from employees e inner join departments d   on e.department_id = d.department_id;

注意问题

     在使用内连接的时候要特别注意：

a) 必须指定正确的连接条件

b) 只有符合连接条件的数据才能出现在查询结果中。

通过上面查询的结果，可以看到有些员工没有出现在查询结果中，比如178号员工。在employees表里，178号员工没有部门编号，不属于任何部门。这样178号员工永远无法满足查询条件，在结果中自然不包括它 。

c)  如果在表连接过程中还有其他判定条件，那么条件仍然用where给定。

   eg：查询first_name是'Steven'的员工信息，以及他所在部门信息

  select e.* , d.* from employees e join departments d   on e.department_id=d.department_id  where e.first_name='Steven';

2.外连接

在SQL中，外连接又分为：左外连接，右外连接和全外连接三种。

  1）左外连接【重点掌握】：在两个表连接查询过程中，以连接符号左侧表为主，左侧表的所有数据全部被查出，包括没能在右侧表中找到对应数据的记录。左外连接的SQL语法为left outer join，其中outer可以省略。

 例如：查询公司所有的员工信息，以及他们所在部门的信息。sql命令如下

      select e.*,d.*       from employees e left join departments d       on e.department_id = d.department_id;

2） 右外连接【了解】：在两个表连接查询过程中，以连接符号右侧表为主。右外连接的SQL语法为right join。

  3）全外连接【了解】：在两个表连接查询过程中，两个表的所有记录都会出现，包括在对方表里没有对应数据的记录。全外连接的SQL语法为full join。

3.自连接

  在进行连接查询的时候，有时候需要用一个表自己连接自己。比如我们想查询员工信息以及其上级的编号和姓名。这时我们就将employees表当做两张表来看待就好了。但是为了表示区别，可以为employees分别取两个别名e1和e2。

    

 select e1.employee_id ,e1.first_name , e1.manager_id , e2.first_name as manager_name      from employees e1 join employees e2      on e1.manager_id = e2.employee_id;

4.笛卡尔连接

    如果我们在进行内连接或者外连接的时候，忘记指定连接条件，那么所有的数据都会被纳入到查询结果中。如果A表有10条数据，B表有4条数据的话，最终的结果中就会出现10 * 4 条数据。这种形式称作笛卡尔乘积。如果A，B表中的数据较多的话，会造成查询结果异常庞大，而且，通过笛卡尔乘积连接起来的数据么有实际意义，所以我们在使用连接查询时，要尽量避免这种情况的出现。

5.不等值连接

 所谓不等值连接，是指连接条件不是通过等号进行运算的。

select e.* , grade from employee e inner join salgrade s    on (e.sal between s.losal and s.hisal);

6.多表连接

当参与连接的表超过2张表以上时，我们就称这个链接为多表连接。

集合运算符

1。UNION 将查询结果合并，并去查重复的列（并集）

   eg：

 select * from employees where department_id=10 and department_id=20          union         select * from employees where department_id = 20 and department_id=30;

2.UNION ALL : 将查询结果合并，不会去除重复列
eg：

 select * from employees where department_id=10 and department_id=20          union all         select * from employees where department_id = 20 and department_id=30;

3.MINUS 从第一个结果集中去除第二个结果集中的内容（差集）

eg:

select * from employees where department_id=10 and department_id=20          minus         select * from employees where department_id = 20 and department_id=30;

4.INTERSECT 求两个结果集的交集

select * from employees where department_id=10 and department_id=20          intersect         select * from employees where department_id = 20 and department_id=30;

注意：使用集合运算符连接的两个查询结果可以不是同一张表，列也可以不是同一列，但必须满足如下条件：

a) 列的个数相同

b) 列的数据类型相同，不同则需要做类型转换

c) 最终查询结果中的列名和类型依赖于第一个结果集

Union 运算有一种很有趣的用法，可以将数据的显示形式有行转为列。

创建表

创建表的语句

  

CREATE TABLE tableName(   columnName columnType [CONSTRAINT constraintName] constraintType,   …    );

其中tableName（表名），columnName（列名），columnType（数据类型），constriaintName（约束名），constraintType（约束类型） 。

1.ORACLE 中的数据类型

1）. 字符类型：Char ,Varchar2

在使用char或varchar2时必须指定字符串的最大长度，如char(10 )、 varchar2(30)。

如果向表中插入长度超过最大长度的数据，将会发生错误。

两种char的区别在于，char为定长字符类型，varchar2变长字符类型。所谓定长字符串是指数据的长度是固定的，即使存入的数据小于最大长度，也会占用同样的存储空间，多于的长度用空格补齐。变长字符串的含义是列的长度随着数据的实际长度进行调整，只要不超过最大值就可以，空闲长度不需要空格补齐。

2）. 数字类型

如何表示整数 number(5) --> 表示5位整数

如何表示小数 number(7,2) --> 表示有效长度7位，小数部分2位

例：下列数字能否存储至该列

123456.12 --> number(7,2) (不能，有效长度8位)

12345.123 --> number(7,2) (能，小数部分四舍五入后保留2位，12345.12总共7位有效数字)

 

3.） 日期类型

DATE (保存年月日时分秒)

TIMESTAMP (保存年月日时分秒，精确到纳秒)

 

4）. 大对象类型

BLOB (二进制大对象)

CLOB (字符大对象)

大对象类型可以略提即可，大对象最大能保存的数据大小为4GB*数据库块大小(一般为8kb,16kb,32kb等跟操作系统有关)

初始化BLOB -->建表时 default empty_blob()

初始化CLOB -->建表时 default empty_clob()

 

注意：Oracle中没有布尔类型，需要表示布尔类型一般通过NUMBER(1)来表示，取值0代表false,取值1代表true

2.约束

1) 约束语法，

CONSTRAINT 约束名 + 约束类型

其中约束名和CONSTRAINT关键字同时使用，并可以省略

注意在为约束命名时尽量遵从一下规则：

表名_列名_约束类型缩写

如果不给约束命名，系统会自动为约束分配一个名字

 

2) Primary Key主键约束：用来唯一标示表中每一行记录，每张表只能有一个主键

主键约束同时具备唯一和非空的特性

缩写为PK

 

3) Not Null非空约束：用来标识某列取值能否为空，不加此约束默认NULL

缩写为NN

 

4) Unique唯一约束：用来标示该列取值在本表内只能唯一，但不对此列的空值进行约束（可以出现多个NULL）

缩写为UK

5) Check 检查约束：用来加检查条件

缩写为CK

 

6) 多个约束可以加在同一列上

7) Foreign Key 外键约束：用来定义表与表之间的关系并约束子表中关系列的取值，

注意：

外键列与主键列的数据类型必须匹配

外键列上是否需要同时增加NOT NULL约束

 

 create table orders(orderId number(6) constraint orders_orderid_pk primary key,orderdate date constraint orders_orderdate_nn not null,state varchar2(20) default sysdate constraint orders_state_nn not null constraint orders_state_ck check (state in ('Y','N')),totalPrice number(10,2) constraint orders_totalprice_nn not null constraintorders_totalprice_ck check (totalPrice > 0.0),username varchar2(20) constraint orders_username_nn not null constraint orders_yihang_username_fk references users (username) );

8) 组合约束

有时候，我们需要将一个约束加载两个锅多个列上，这种约束称作组合约束,现有成绩表结构如下：

create table scores(   studentId number(4) not null,   courseName varchar2(10) not null,   score number(3) not null,   unique(studentId, courseName));

约束的写法是另起一行（注意,号），说明约束的类型和作用列。

在分类上也把这种约束叫做表级约束，前面形式的约束叫做列级约束。所有的列级约束（除NOT NULL之外）都可以表示为表级约束（反之不行），

3.根据已有表创建新的表

我们还可以根据已有的表来创建新表，这样已存在的表中的

 

语法：

CREATE TABLE tableName AS SELECT … FROM …

或者 ：

CREATE TABLE tableName(columnName [CONSTRAINT constraintName] constraintType,…,…,… ) AS SELECT … FROM …

注意：表复制其实涉及一下三个操作

1) 根据查询的列的信息创建新表

2) 根据查询的行的信息为新表填充数据

3) 如使用第一种语法，旧表的not null约束会被复制到新表，其他约束不会复制

简单复制EMP表的结构和数据

create table newemp as select * from emp;

 

新表结构和原表不同的情况

create table newemp as select count(*) num, deptno from emp group by deptno;

 

只复制表结构，不复制数据

create table newemp as select * from emp where 1 = 2;

 

自定义复制EMP表结构和数据

create table newemp (       empno constraint newemp_empno_pk primary key,       ename constraint newemp_ename_nn not null,       deptno constraint newemp_deptno_nn not null) as select empno,ename,deptno from emp;

这种语法要求create部分与select部分的列数目一致，并不能直接建立外键约束

DML命令

1.INSERT

语法：

第一种，给出所有列的名称和值

INSERT INTO tableName (columnName1, columnName2, …)VALUES (columnValue1, columnValue2, …);

第二种，给出部分列的名称和值。

名称和值必须包括所有主键、不为空的列（除了指定了默认值的）

INSERT INTO tableName (columnName1, columnName2, …)VALUES (columnValue1, columnValue2, …);

第三种，只给出值，必须包括所有列的值

INSERT INTO tableName VALUES (columnValue1, columnValue2, …);

 

第四种，将查询结果作为多行插入

INSERT INTO tableName  SELECT …;

例：

create table newemp as select * from emp where 1 = 2;insert into newemp select * from emp where deptno = 10;

它的用途是一次可以插入多条记录

限制：

插入的数据一般来源于旧表

插入新表的列不一定跟旧表的列一致，但数据类型必须符合，如：

insert into newemp (empno, deptno, sal) select empno, deptno, comm from emp;

2.update

语法：

UPDATE tableName SET columnName1 = columnValue1, columnName2 = columnValue2, …WHERE … ;

3.delete

语法：

DELETE FROM tableName WHERE… ;

注意：update和delete操作需要考虑是否增加where条件

事务和锁

1. 什么是事务?

所谓事物是一个原子操作，也就是说一个事物中的多条SQL语句要么一起成功，

Oracle提供了将多条DML语句作为一次原子操作的办法：任意一条INSERT UPDATE DELETE语句会让事务开始，接下来的INSERT UPDATE DELETE都会处于同一事务当中，这些操作，作为一个整体，要么都成功，要么都撤销。

commit表示事务结束，并将这次事务中所作出的更改影响到数据文件，这称之为提交

rollback; 也代表事务结束，并将这次事务中所作出的更改都撤销，状态恢复到事务开始之前，这称为回滚。

 

 事务执行的前后数据的状态是一致的

2. SAVEPOINT

如果不希望撤销本次事务内所有的操作需要设置savepoint

 

3. 事务的开始和结束

对于DML语句事务在执行INSERT UPDATE DELETE之一时开始

当执行COMMIT或ROLLBACK时结束

 对于DDL DCL来说，事务在执行CREATE ALTER DROP GRANT REVOKE等语句时，执行成功则提交事务，失败不会影响当前事务

 

4. 什么是锁?

引入：

1) 现实生活中的锁，是为了保护私有财产

2) Java中的synchronized，是为了保护多个线程访问共享资源

3) Oracle中的锁，是为了保护多个连接同时修改同一条（组）数据

换句话说，锁是数据库对并发访问数据的一种保护机制

 

5. 什么时候应用锁?

数据库会在修改数据的同时，自动给要修改的数据加锁。

当执行insert update delete这些语句时：

事务开始，并且对这些语句影响到的记录加锁

当事务结束(commit, rollback )释放刚才加的锁

 

6. Oracle数据库中锁的特点

1) Oracle使用行级锁

Oracle执行增删改时给数据加的锁是以行为单位的，例：

尝试修改同一表中的不同记录

客户端一执行

update emp set sal = sal + 1000 where empno = 7369;

客户端二执行：

update emp set sal = sal + 600 where empno = 7788;

可以发现，这两条SQL语句没有发生阻塞的情况。

 

2) 查询（select语句）永远不会被阻塞

例：

客户端一执行

update emp set sal = sal + 1000 where empno = 7369;

客户端二执行：

select * from emp;

可以发现两条SQL互不干扰。

 

3) select … for update;

Oracle中有一种特殊的select语句，就是在select的最后加上for update子句，这条语句的效果类似于update insert delete，也会开启事务，并给查询到的记录加锁。

用途：

如果依照上例进行操作

客户端一执行

update emp set sal = sal + 1000 where empno = 7369;

客户端二执行：

update emp set sal = sal + 600 where empno = 7369;

如果客户端一迟迟（或永远）不结束事务，那么包括客户端二在内的其他客户端都将一直处于阻塞状态。

这个现象告诉我们，良好的编程习惯包括：

i. 要注意事务的正确结束

ii. 去获得锁之前，应当看看是不是别人已经获得了锁，再采取相应的策略

对于第ii.点，可以在select… for update语句后加入wait n或nowait来限制等待超时或根本不等待。

例如：

客户端一执行

update emp set sal = sal + 1000 where empno = 7369;

客户端二执行：

select * from emp where empno = 7369 for update wait 5;update emp set sal = sal + 600 where empno = 7369;

则客户端二会尝试等待5秒，5秒之内无法获得锁，则出现异常结束等待。当然5秒之内如果客户端一释放了锁（事务提交或回滚）则客户端二获得记录7369的锁，并开启它的事务。

或者

客户端二执行：

select * from emp where empno = 7369 for update nowait;update emp set sal = sal + 600 where empno = 7369;

则客户端二执行select… for update语句时会检测7369记录上是否已经有别的数据库连接给该记录加锁，如果有，则出现异常并结束等待，如果没有则自己获得锁，开启事务。

 

7. 事务特性

事务特性是指ACID这四个方面：

A（原子性）Atomicity

C（一致性）Consistancy

I（隔离性）isolation

D（持久性）Durability

前两条已经在第5讲时讲过，持久性是指一个事务一旦成功完成，它对数据库的改变必须是永久的，即便是在系统遇到故障的情况下也不会丢失。

下面重点讲述事务的隔离性。

 

8. 事务的隔离性以及隔离级别

事务隔离性主要体现在隔离级别上，数据库事务的隔离级别决定了两个并行事务间针对同一数据访问的可见性。

数据库事务隔离级别分一下几种：

没有事务

提交读(read committed) -- oracle支持（默认）

可重复读(repeatable read)

序列化读(serializable read) -- oracle支持

这些概念较为抽象，可以从一些错误现象来引入隔离级别的必要性：

 

脏读现象就是指客户端二在查询的过程中，查询到了无效的数据（脏数据），值得庆幸的是，该现象在使用Oracle的过程中，从未出现过，这时因为Oracle默认的事务隔离级别为提交读，该隔离级别就是为了防止发生这种脏读现象的。

在提交读的隔离级别下，读到的数据总是事务开始前，或事务结束后的，不会是一个中间状态的数据：

要注意的是：

序列化读并不是有些同学理解的阻止其他事务同时执行，而是在此事务隔离级别下，就好像只有自己操作数据库一样。

 如果其他事务对表中某条记录做更新操作，则序列化事务中再对同一条记录更新，不会阻塞，但会抛出异常。

如果希望设置事务隔离级别为读提交，则执行

set transaction isolation level read committed;

但实际这句话在SQLPlus里没有什么用处，因为默认就是。即使设置为序列化读之后，事务一旦结束仍会将隔离级别自动变回读提交

 

9. 隔离级别的应用

隔离级别由低到高为提交读、可重复读、序列化读。但并不是隔离级别设置的越高越好，越高的隔离级别意味着并发性越差（能同时处理的用户数）。一般的业务需求中提交读隔离级别就可以满足。

序列

1. Oracle的逻辑组成

按用户划分

-> Sys 权限最高的用户

-> user_tables, user_constraints, dual

-> System 数据库管理员

-> zjkcksd0901 (Schema 模式)

某个用户所有的数据库对象的集合

-> Table(s)  一种数据库对象

-> View (视图)

-> Sequence (序列)

-> Index (索引)

-> constraint (约束)

-> function (函数)

-> procedure (存储过程,包含业务逻辑代码) PL/SQL

-> cksd0905

-> Table(s)

2. 如何在插入记录时生成主键

方法1.通过max求得下一个数

select case when max(a) is null then 1 else max(a)+1 end from x;

隐患：

当数据量较多时，每次都运行max会影响效率

单考虑上述查询，当查询并发运行时会出现主键重复现象

改进办法：仍使用上SQL获取主键值，插入时如出现主键重复异常，捕获此异常并重新插入

 

方法2.通过SYS_GUID()产生主键

产生128bit的数字，如果仅希望主键产生一个唯一值的情况下，较为适合

 

方法3.使用单独表维护主键

首先创建以下表，并插入一条初始记录维护x表的主键

create table idGenerator(       table_name varchar2(20) primary key,       curr_val   number(20) not null);insert into idGenerator values ('x', 1);commit;

以后产生主键时使用：

select curr_val from idGenerator where table_name = 'x' for update;update idGenerator set curr_val = curr_val + 1 where table_name = 'x';commit;

第一条语句加for update是为了防止并发时产生重复主键，第二条更新是用来产生下一个值

 

方法4.使用数据库特定方案

3. Oracle中可以使用序列来解决主键生成问题。

语法：create sequence序列名;

 

-- start with 设置序列取值的开始位置

create sequence seq_1 start with 300001;

-- cache 5 从序列中取得下5个值放置在缓存,默认为20，主要目的是提高效率

create sequence seq_1 cache 5;

视图

1. 视图(View)的概念及语法：

命名的查询，虚拟表

create view 视图名 as 查询语句

 

2. 视图的作用

作用1：用于简化复杂的查询

作用2：隐藏基表结构

允许其他用户查询视图，但不能让其他用户直接访问基表

3. 注意事项：

注意1：视图不保存查询结果，是一个虚拟的表

注意2：视图是否能提高查询效率（不能）

注意3：也可以对视图增、删、改,但一般不这样用,而且限制很多

(要对视图增、删、改，视图只能对应一个基表)

注意4：select * from user_views; --查询视图信息

索引

1. 索引(Index)的概念

目的：提高查询的效率

 

2. 什么时候使用索引?

1) 在经常查询的列上建立索引

2) 在大表上(数据量大)

3) 索引是用来查询少量信息的(占数据总量的30%以下)

3. 语法：

create index emp_ename_idx on emp(ename);

  4. 注意事项：

注意1.索引不需要显式调用，数据库会自动决定什么时候应该应用索引

 

注意2.索引提高查询效率的原理：

1) 采用索引段来保存了排序之后的结果

2) 查询语句首先从索引段快速查询到rowid

3) 根据rowid的值到数据段找到对应数据

回忆rowid伪列:能够唯一标识表里的某一条记录，能够使用rowid最快速定位到记录

oracle在索引段数据的排序结构B(alance) * 树

 

注意3.

1) 索引要占用额外的存储空间，所以不是建的越多越好

2) 索引会影响插入和删除的效率

3) 建立主键，会自动在主键列上建立唯一索引

如何建立唯一索引

create unique index emp_ename_idx on emp(ename);

4) 建议外键列上建立索引，会提高表连接的效率

5) 对于下列情况不会应用ename列的索引

select * from emp where lower(ename) = 'smith';

解决方法

create index emp_ename_idx2 on emp(lower(ename));