Oracle数据库--实用操作(1)

一、SQL命令与函数

         1、SQL 结构化查询语言，是Structured Query Language的首字母缩写词

         2、SQL 是数据库语言，Oracle使用该语言存储和检索信息

         3、表是主要的数据库对象，用于存储数据

         4、通过 SQL可以实现与 Oracle 服务器的通信

        

SQL 是通用的数据库语言

SQL 命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言

Oracle 支持的数据类型包括字符、数值、日期时间、RAW和 LOB 等

SQL 支持的操作符包括算术、比较、逻辑、集合和连接操作符

SQL 函数可大致分为单行函数、聚合函数和分析函数

        

         通常传统商业---使用Oracle

         互联网公司----使用MySQL,开源

SQL 命令可分为数据定义语言、数据操纵语言、事务控制语言和数据控制语言

数据定义语言（DDL）

数据操纵语言（DML）

事务控制语言（TCL）

数据控制语言（DCL）

         DDL数据定义语言

                   create、alter 、drop [这些关键字不区分大小写]

                   (建模工具，建模，可以导出DDL)

                   关系范式（1NF，2NF,3NF）

         DML数据操作语言[用的多] 增删改查

                   insert

                   select

                   update

                   delete

                   truncate删除表数据并释放空间                  

二.SQL函数

                   单行函数：对当行进行操作

                            to_char

                            to_date

                            length

                           

                   多行(聚合函数)对多行进行操作 

                            max()

                            min()

                            count()

                            sum()

                            avg()

                           

分析函数

分析函数根据一组行来计算聚合值

用于计算完成聚集的累计排名、移动平均数等

分析函数为每组记录返回多个行

分析函数用于计算一个行在一组有序行中的排位，序号从1开始

1、ROW_NUMBER--返回连续的排位，不论值是否相等

 

 select ename,sal,

 ROW_NUMBER()  over(order bynvl(sal,0) desc)  n

 from emp ;

 

 select t.n,t.ename,t.sal

 from (

 select ename,sal,

 ROW_NUMBER()  over(order bynvl(sal,0) desc)  n

 from emp ) t

 where t.n<4;     --取前3，利用嵌套

 

2、Rank--具有相等值的行排位相同，序数随后跳跃；1,2,2,4

  select ename,sal,

 Rank()  over(order by nvl(sal,0)desc)  n           --over表示通过用什么，来生成排序

 from emp ;

 

3、DENSE_RANK[密集排序：允许并列名次]--具有相等值的行排位相同，序号是连续的;1,2,2,3,4,

 select ename,sal,

 DENSE_RANK()  over(order bynvl(sal,0) desc)  n

 from emp ;                                               

伪列

         ROWNUM行号（记录）可变，逻辑序列；按不同规则排序，显示的逻辑序列不相同。

         ROWID记录的标识，不可变，是物理序列。

        

ROWNUM ，若查询字句中有order by,ROWNUM排序无效

                   解决-- 在外层加层嵌套(查询)

select t.*, rownum n

from

(selectename,TO_CHAR(hiredate,'yyyy-MM')   --TO_CHAR(hiredate,'yyyy-MM')取入职时间的年月，转成字符文本

from EMP

order by HIREDATE desc) t;

//选出工资排行前3 的                           

select t.*,rownum

from

(select ename,sal

from emp

order by nvl(sal,0) desc) t

where rownum<4;           --只能写<,此处写rownum>,=5都不行。

                  

//选出工资排行第3名之后的，即从第四名及其以后的[用rownum排序作为新列t,取t>3]                

select *

from(

select t.*,rownum n

from

(select ename,sal

from emp

order by nvl(sal,0) desc) t)

where n>3

;

PL/SQL

T/SQL -----是(不同数据库厂商的)方言，是对标准SQL的扩展，只适用于各自厂商的数据库。

 

                                               分页                            pk

         oracle                          rownum                      序列

         mysql                          limit                    自动增长

         sqlServer                  top                               标识

 

         有个部门为空，若要统计各个部门人数，即为0；而这个0在员工表里没有记录与之对应。

（2张表才关联）

内连接[等值连接]：匹配的选中，不匹配的丢掉。                             

外连接[不等值的连接]3种-----------部门没有人也能统计出来

         selecte.ename                                                       --(左)外连接=join左边的表中数据全出现

         fromemp e LEFT OUTER join dept d     --(左)外连接 表 dept,别名d｛右RIGHT,全连接Full｝

                   ONe.deptno=d.deptno;   --ON相当于where-条件

                   

select e.ename,d.DNAME

from emp e,dept d      --起别名

where e.depton(+)=d.depton;   --左外连接[简写]，不带+号的表中数据全出现，即使数据为null              

 

where e.depton=d.depton(+);   --右外连接[简写]

全连接，不可简写。

 

nvl();把null替换为0；

.............................................

select d.dname,nvl(t.c,0)

from dept d,

(select deptno,count(*) c

 from emp

 where deptno is not null

 group by deptno) t

 where d.deptno=t.deptno;

两张表---求交集[INTERSECT]、并集[union]、差[MINUS,]--代数运算

 

1.UNION 合并2个子查询的结果[结果意义得相同]，相同的值出现一次

 UNION All  追加结果集，有重复

select  deptno

from dept;

UNION All        --相同的值，也独立出现

select  deptno

from emp;

 

2.MINUS

select  deptno

from dept;

MINUS       --上面表中的结果，减去下面表中的结果。

select  deptno

from emp;

 

3.INTERSECT

select ename from emp;

INTERSECT      --交集

select ename from emp;

--踢出相同的  distinct

select DISTINCT  deptno

from emp;

 

 

例：查出每个部门的员工，并按工资排序

 select deptno,ename,sal,

 ROW_NUMBER()  over(PARTITION bydeptno order by nvl(sal,0) desc)  n  --PARTITION by deptno按deptno分组

 from emp ;

 

-----只选每个部门前3名

  select t.deptno,t.ename,t.sal,t.n

 from(

 select deptno,ename,sal,

 ROW_NUMBER()  over(PARTITION bydeptno order by nvl(sal,0) desc)  n

 from emp) t

 where t.n<4;

------只选每个部门第1名

 where t.n=1

注：

  在分析函数中分组，使用PARTITIONby ，更灵活；

 

例题、

1、查询获得所有在纽约  工作的员工。             --数据区分大小写，

select ename

from EMP            --员工表

where deptno=(      --部门编号                  --等于=，只能做单值匹配

select deptno

from dept           --部门表

where lower(loc)='new york'

);

 

<方法二>

select ename ,loc

from emp e,dept d

where e.deptno=d.deptno And lower(loc)='new york';

2、查询所有工资比Scott高的员工。

 select ename,sal

from emp

where sal>(

select sal

from emp

where ename='SCOTT'

);

 

谓词查询

1、等于=，只能做单值匹配；不能出现   where course=10 And id=11;

2、in 匹配多个值，相当于where id=10 or id=11;  复合多个并列语句

3、ALL   不能写=all;

4、some,any    =some和in的效果相同。

  

事务：

         多个操作  打包成整体。多个事务[操作]要么都通过，要么都失败。

事务是最小的工作单元，作为一个整体进行工作

保证事务的整体成功或失败，称为事务控制

用于事务控制的语句有：

COMMIT - 提交并结束事务处理

ROLLBACK - 撤销事务中已完成的工作

SAVEPOINT – 标记事务中可以回滚的点

 

ACID

原子性

一致性     多个人查询都是一样的

隔离性     多人的操作不应交叉执行。

持久化    

 

锁：

锁是数据库用来控制共享资源并发访问的机制。

锁用于保护正在被修改的数据；查询数据不加锁。

直到提交或回滚了事务之后，其他用户才可以更新数据

 

锁定的优点

并行性————允许多个用户访问同一数据

一致性 - 一次只允许一个用户修改数据

完整性 - 为所有用户提供正确的数据。如果一个用户进行了修改并保存，所做的修改将反映给所有用户。

 

锁分类：行级锁、表级锁

行级锁

对正在被修改的行进行锁定。其他用户可以访问除被锁定的行以外的行

行级锁是一种排他锁[A锁定资源，操作未完成，他人无法修改]，防止其他事务修改此行

在使用以下语句时，Oracle会自动应用行级锁：

INSERT

UPDATE

DELETE

SELECT … FOR UPDATE

SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新。即select的多行记录在未提交前是锁定的，其他人若要修改，则需等待。

使用COMMIT或ROLLBACK语句释放锁。ROLLBACK回滚--撤销[上个事务]，可设置回滚点。

SELECT … FOR UPDATE noWait;表示查询并锁定 所选行；若已被锁定，则不等待直接退出。无人锁定就锁定资源。

 

表级锁

使用命令显示地锁定表，应用表级锁的语法是:

 LOCKTABLE table_name IN mode MODE;   --锁定TABLE,IN 用MODE模式锁定

 

 行共享 (ROWSHARE ) –禁止排他锁定表

行排他(ROW EXCLUSIVE ) –禁止使用排他锁和共享锁

共享锁(SHARE )

锁定表，仅允许其他用户查询表中的行

禁止其他用户插入、更新和删除行

多个用户可以同时在同一个表上应用此锁

共享行排他(SHARE ROW EXCLUSIVE) –比共享锁更多的限制，禁止使用共享锁及更高的锁

死锁

当两个事务相互等待对方释放资源时，就会形成死锁

Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁

表空间

是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间 。

分区表

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

注意：已经存在的表没有方法可以直接转化为分区表 

允许用户将一个表分成多个分区

用户可以执行查询，只访问表中的特定分区

将不同的分区存储在不同的磁盘，提高访问性能和安全性

表的每个部分都称为“分区”

可以独立地备份和恢复每个分区

通过引用逻辑表

在创建表时，指定分区类型。

表分区的具体作用

Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具

分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改

什么时候使用分区表：
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。

表分区的类型：范围分区、散列分区、列表分区、复合分区

范围分区[按数值范围分区，]

以表中的一个列或一组列的值的范围将行映射到分区

由表的分区规范进行定义；partition by range(column_list)

由每个单独分区的分区规范进行定义：values less than(value_list);

范围分区的语法：

PARTITION BY RANGE (column_name)

(

 PARTITION part1 VALUES LESS THAN(range1),

 PARTITION part2 VALUES LESS THAN(range2),

  ...

 [PARTITION partN VALUES LESS THAN(MAXVALUE)]

);

 

范围分区示例

SQL> CREATE TABLE SALES2 (

PRODUCT_ID VARCHAR2(5),

SALES_DATE DATE NOT NULL,

SALES_COST NUMBER(10))

PARTITION BY RANGE (SALES_DATE)

(

 PARTITION P1 VALUES LESS THAN (DATE ‘2003-01-01’),

 PARTITION P2 VALUES LESS THAN (DATE ‘2004-01-01’),

 PARTITION P3 VALUES LESS THAN (MAXVALUE)

);

散列分区

允许用户对不具有逻辑范围的数据进行分区

通过在分区键上执行HASH函数决定存储的分区

将数据平均地分布到不同的分区

散列分区语法

PARTITION BY HASH (column_name)

PARTITIONS number_of_partitions;

或

PARTITION BY HASH (column_name)

( PARTITION part1 [TABLESPACE tbs1],

 PARTITION part2 [TABLESPACE tbs2],

  ...

 PARTITION partN [TABLESPACE tbsN]);

散列分区示例

SQL> CREATE TABLE EMPLOYEE

(

   EMP_ID NUMBER(4),

   EMP_NAME VARCHAR2(14),

   EMP_ADDRESS VARCHAR2(15),

   DEPARTMENT VARCHAR2(10)

)

PARTITION BY HASH (DEPARTMENT)

PARTITIONS 4; 

列表分区

允许用户将不相关的数据组织在一起

列表分区的语法：

PARTITION BY LIST (column_name)

(

 PARTITION part1 VALUES (values_list1),

 PARTITION part2 VALUES (values_list2),

  ...

 PARTITION partN VALUES (DEFAULT)

);

复合分区

范围分区与散列分区或列表分区的组合

复合分区的语法：

PARTITION BY RANGE (column_name1)

SUBPARTITION BY HASH (column_name2)

SUBPARTITIONS number_of_partitions

(

 PARTITION part1 VALUE LESS THAN(range1),

 PARTITION part2 VALUE LESS THAN(range2),

  ...

 PARTITION partN VALUE LESS THAN(MAXVALUE)

);

   PRODUCT_ID VARCHAR2 (5),

   SALES_DATE DATE NOT NULL,

   SALES_COST NUMBER (10)

)

PARTITION BY RANGE (SALES_DATE)

SUBPARTITION BY HASH (PRODUCT_ID)

SUBPARTITIONS 5   

(

   PARTITION S1 VALUES LESS THAN (TO_DATE(‘01/4月/2001',

   'DD/MON/YYYY')),

   PARTITION S2 VALUES LESS THAN (TO_DATE(‘01/7月/2001',

   'DD/MON/YYYY')),

   PARTITION S3 VALUES LESS THAN (TO_DATE(‘01/9月/2001',

   'DD/MON/YYYY')),

   PARTITION S4 VALUES LESS THAN (MAXVALUE)

);

在已分区的表中插入数据与操作普通表完全相同，Oracle会自动将数据保存到对应的分区

查询、修改和删除分区表时可以显式指定要操作的分区

分区维护操作 :

查询表上有多少分区
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

分区维护操作修改已分区表的分区。

分区维护的类型：

计划事件－定期删除最旧的分区

非计划事件－解决应用程序或系统问题

分区维护操作有：

1、添加分区

2、删除分区

3、截断分区

4、合并分区

5、拆分分区

添加分区– 在最后一个分区之后添加新分区

 ALTER TABLE SALES

    ADD PARTITION P4 VALUES LESS THAN (4000);   --增加分区P4，值<4000的存入

     

删除分区– 删除一个指定的分区，分区的数据也随之删除

ALTER TABLE SALES DROP PARTITION P4;

 

截断分区– 删除指定分区中的所有记录

ALTER TABLE SALES TRUNCATE PARTITION P3;

          

合并分区 - 将范围分区或复合分区的两个相邻分区连接起来

ALTER TABLE SALES

MERGE PARTITIONS S1, S2 INTO PARTITION S2;

 

拆分分区 - 将一个大分区中的记录拆分到两个分区中

ALTER TABLE SALES SPLIT PARTITION P2 AT(1500)

INTO (PARTITION P21, PARTITION P22);

创建表时，指定了数据分区（），查询时，可对各个分区查询

如：select id,TITLE,PRICE ,rownum,rowid from table_0100PARTITION(p2);    --查询p2分区的数据。

create table t_p1   --创建新的表，查出TABLE_0100表中p1分区的数据，并存入t_p1

as

select * from TABLE_0100 PARTITION(p1);