Oracle数据库学习总结

一．基本的select语句

1.基本查询语法：

select  字段名1,字段名2  from 表名；

如：select ename,empno from emp;

 

2.可以对字段进行基本运算有：

A  对于NUMBER 类型的有: + ，- ，* ，/ ；

B  对于DATE类型的有：+ , - ;

另外，可以使用括号改变运算的优先级。

 

3.空值不参与运算，对null进行运算是无效的,但在字符连接时可以使用。

 

4.字段别名的使用:

select 字段名1 as  别名1，字段名2 as 别名2  from  表名；

如：select ename as “姓名”,empno as “员工号” from emp;

 

5.过滤重复列,使用关键词distinct来实现:

       如：select distinct deptno from emp; 过滤emp表中deptno部门号相同的.

 

6.查询表结构: desc 表名 如:

在PL/SQL中 file-> new -> command window ->

SQL> desc emp;

 

 

二．表数据的过滤与排序

 

1.数据过滤

使用where限制数据的查询：

select 列名 from 表 where 列名 运算符号 值；

如：select ename ,empno,sal from emp where sal >2000;

查询工资大于2000的员工的信息。

 

（1）字段中的信息大小写的问题：值必须区分大小写。

（2）复杂运算符

      两者之间:between...and，

      如：select ename sal from emp where sal between 2500 and 5000;

      范围之内:in (set)，

      如：select ename sal from emp where deptno in(20,30);

      是否存在:is null,

      如：select ename,empno,job from emp where mgr is not null;

      模糊查询：like

      如：select enaem ,job from emp where ename like ‘%A%’;

（3）逻辑运算:

非  not ：如果为真，结果为假；如果为假，结果为真，即取反操作；

与    and ：只有两个值都为真时，结果才为真；

或    or ：只要有一个为真时，结果就为真。

默认条件下优先级为not ，and ，or 为从高到底．

 

2.排序数据

 order by:

（1）两种方式:

ASC:升序

如：select ename ,sal ,job from emp where sal >2000 order by sal ASC;

DESC:降序

如：select ename ,sal ,empno from emp where sal >2000 order by sal DESC;

 

（2）默认情况下是ASC也可以按别名来排序的，既可以省略ASC。

 

三．函数

<A>单行函数

1.函数类别:

单行:返回单个结果:substr,length

多行:返回多个结果,any,all

 

2.单行函数分类:

（1)字符类：转换大小写:

（1）lower:转换为小写

（2）upper:转换为大写

（3）initcap:首字母大写

 

(2)字符处理类：

（1）concat:连接两个不同的列,concat只能接受两个参数

（2）substr:截取子串

（3）length:提取长度

（4）instr:返回索引

（5）lpad：左补齐和rpad：右补齐

（6）trim两边滤空

（7）replace：替换

 

(3)数字转换类:

（1）round:四舍五入

（2）trunc:截取

（3）mod:取余

 

（4)日期类函数:

（1）Oracle的内置函数sysdate: 可以查询系统当前日期与时间

（2）日期+-1,都代表一天的时间

 

（5).日期运算函数

（1）months_between:表示两个日期的月份之差

（2）add_months:表示给指定的日期加一个月数

（3）next_day:表示以当前时间为基准,下一个"目标日"的日期

（4）last_day:计算当前日期的最后一天

（5）round:对日期进行四舍五入

（6）trnuc:表示对日期进行截取

（7）to_char:函数将日期转换为字符类型

 

 

3 转换函数：

（1）.TO_CHAR:

可以对数字类型进行类型转换,转换的同时,你可以指定转换后的格式

（2）.TO_NUMBER:

作用是用来将一个指定的字符串类型的数字,转换为数字格式

(格式必须要匹配).

（3）.TO_DATE:

将字符串类型的日期,转换为日期类型

 

4 .关于通用函数:

(1) NVL:当修饰的字段的内容为null时,运算结果为指定的值

      如：NVL(expr1,expr2);

(2) NVL2:如果第一个表达式的值不为null,显示表达式2的值,否则(为null)显示表达式3的值

如：NVL2(expr1,expr2,expr3);

(3) NULLIF:如果前后两个表达式的内容相等的,那就返回null,否则,返回第一个表达式的值

     如：NULLIF(expr1,expr2);

(4) COALESCE函数:用来匹配多个字段的值,如果表达式1的值为null,显示表达式2的值,如果表达式2也为空,显示表达式3的值,依次类推

     如：COALESCE(expr1,expr2,expr3,…,expr);

 

5.条件表达式:

（1）case:表达式语句

 

（2）decode:函数decode()

 

<B>分组函数

1.统计数据

（1）取最大值：max

（2）取最小值：min

（3）取平均值：avg

（4）取记录数：count

（5）求和：sum

2.分组函数和distinct关键词的搭配

组函数名（distinct 字段名）

3.组函数与空值

空值不参与运算,直接被过滤掉

4.分组数据

group by子句的使用

(1）使用group by规则

（a）select后面的字段必须在group by子句中出现

（b）在组函数(count,max,min等)中出现的字段除外

(2）关于group by条件分组的问题

（a）group by可以和where来搭配where只能在group by的前面From EMP 后面不能有where

（b）where子句中不能包括组函数条件的表达只能使用having来表示

如：

select ename,max(sal),min(sal),avg(sal) from emp group by ename having sal>2000 order by ename ASC;

 

四．子查询

1.语法要求：

（1）子查询总是先于主语句的运行

（2）必须有(),表示是一个整体

（3）习惯上,把子查询放在条件的右边

（4）单行操作符只能接受一个值

2.子查询的分类：

（1）单行：运算符

（2）多行：in， any,  all

 

五.多表连接

1.查询的时候,可以from多个表这样会产生一个笛卡尔集

2.需求:取关联数据用where语句实现

3.语法格式:

（1）select 字段 from 表1,表2 where 表1.列=表2.列；

（2）可以为表定义一个别名,但是不能使用as(字段重命名可以使用)

（3）不同的连接条件可以使用and或or

4.使用不等值连接

连接条件:一个表中的记录,在另一个表中能够找到匹配的记录即可

5.外连接:把不满足条件的数据显示出来

（1）左外连接

（2）右外连接

6.自连接

从同一个表中联合查询

7.SQL99 新标准下的多表连接

 （1）交叉连接: CROSS JOIN

其结果是笛卡尔集,是没加任何条件所产生出来的数据集合

 （2）自然连接:NATURAL JOIN

默认情况下,以两个表中有相同名称的列来进行连接查询

 （3）使用using子句来查询

表1  join  表2  using（字段名）

 （4）使用on子句

指定连接具体条件

 （5）多个表连接查询

   表1  join  表2  on  条件  join  表3  on   条件  .......

 （6）外连接：

 左外连接：LEFT OUTER JOIN

 右外连接：RIGHT OUTER JOIN

 全连接：FULL OUTER JOIN

 

六．数据处理

1.插入数据:  INSERT  INTO

（1）表后面()内表示的是字段名称 与values()内表示是字段所对应的值

 (2）值要与列所表示的数据类型对应

(3）没有指定值的列默认为null查询的时候,可以使用is null来提取

 (4）写入的数据只在当前窗口内是有效的,打开一个新的SQL Window就无效，如果需要持久性保存下来需要进行事务处理

 

事务操作：

（1）提交commit

（2）rollback

（3）回滚到指定的点

 

如：

INSERT INTO emp(empno,ename,job,sal)  VALUES(7878,’ali’,’manager’,5000);

   Commit;

（5）在sql中,可以用"&变量名称"来定义表名,字段名,值等信息

（6）从其他表中拷贝数据

（1）字段类型要对应

（2）字段个数要对应

（7）使用循环的向表中插入数据，如：

INSERT  INTO   emp (empno,ename,job,sal)

VALUES(‘&员工号’,’&姓名’,’&职称’,’&工资’);

Commit;

 

2.修改数据： UPDATE

（1)语法:

update 表名 set 字段=值 where 条件

（2)要求：

（1）不同的字段用逗号隔开

（2）如果没有指定条件,默认更新所有的记录

（3）在更新语句中可以使用子查询

（4）关于更新时发生的数据关联问题，如果一个字段的值的来源是另外一个表,其值不能随意指定。

  如：UPDATE emp set comm = 200 where comm. Is null;

 

3.删除数据： DELETE

(1）语法：delete 表名 where 条件；

(2）可以使用子查询执行delete操作

(3）关于删除的完整性约束: 如果一个字段被另外一个表的字段引用,那个这个值就不能删除

 如：DELETE emp where sal <1500;

     DELETE emp where deptno=(select deptno from emp where empno is null);

 

七．创建和管理数据

1.表的分类:

（1）系统表

（2）自定义表

（3）数据字典表

2.表的命名规则:

（1）表名长度必须在30位以内

（2）表名要避开关键词

（3）表名最好是有特定意义，如：员工表 emp，部门表 dept等

 

3.表的创建:

（1）语法:

Create Table 表名(

列名1： 字段数据类型,

列名2： 字段数据类型,

列名3： 字段数据类型,

.....

);

如:

create table student(

name varchar2(20),

age number(5),

class varchar2(10),

stuid number(4));

commit;

（2）你可以在创建表的同时,指定默认值,设置主键等；

 

4.查看其他用户的表

（1）可以以sys身份可以看到scott用户下的表

    如：select * from scott.emp;

（2）scott不能看sys用户下的表的信息

 

5.字段类型的定义

（1）char---定长字符串

（2）varchar2----变长字符串

（3）number(n):表示整数位不能大于n个,小数位可以任意

（4）number(m,n):表示总长度为m位,其中小数位为n,整数为为m-n

（5）Date类型可以使用to_date把字符串类型转换为date类型

 

6.使用子查询来创建表

create table 表名 as 子查询语句

如：

create table new_emp as select * from emp

 where sal between 2000 and 5000;

 Commit;

7.更新表的结构

（1）追加列

Alter Table 表名 Add (列名  字段数据类型)

（2）修改列

Alter Table 表名 Modify (列名  字段数据类型)

（3）删除列

Alter Table 表名 Dorp (列名  字段数据类型)

 

8.对表进行重命名

使用rename  原表名  to  新表名；

如：rename emp to new_emp;

 

9.删除表的内容

  （1）delete删除表中一部分  如：delete emp where empno=7865;

  （2）truncate清空表的内容 如：truncate table emp;

  （3）drop 全部删除  如：drop table emp;

区别：

   Delete 删除表中的一部分数据，可以使用Rollback回滚，放弃修改；

   Truncate 删除的是表中所有的数据，并且不可以回滚，一旦执行数据就会掉失

   所以，在删除时一定要确认数据是否要删除。

 

10.注释

（1）对表的注释

        COMMENT ON TABLE 表名  IS  '注释内容'

（2）对列的注释

COMMENT ON COLUMN EMP.ENAME IS '注释内容'

12.从plsql工具中导出表的信息

 TOOLS---EXPORT TABLES---SQL INSERTS

 

八．表的约束

1.数据完整性的约束条件：

（1）非空 :not null约束

（2）唯一:unique约束

（3）主键:not null+unique

（4）外键:值是外来的

（5）检查:值是符合条件

2.注意事项

（1）可以给约束起个名字,也可以是使用默认的名字

（2）创建的两种时机：创建表的同时/修改表的定义的时候

（3）约束的定义位置：表级/列级

（4）在字典表中可以查看到这些约束信息

3.not null约束

 定义位置:not null只能在列级

4.unique唯一约束

（1）表级(自定义名称)

constraints uni_名称 unique(字段名)

（2）列级(使用系统默认名称)

字段名 unique

5.主键约束

（1）列级:字段名 primary key

（2）表级:,constraints pk_id primary key(id)

（3）主键==not null+unique

（4）联合主键:要求多个字段每一项都不能为null,并且合起来的值不能重复

6.外键约束

语法：

constraints 自定义名称 FOREIGN KEY (字段名)

references 表名(字段名)

7.关于外键的删除级联设置

（1）on delete cascade:级联删除

（2）on delete set null(不引用)

8.CHECK约束

可以表级也可以是列级

9.约束的添加

    语法:

alter table 表名 add primary key (字段名称)

10.删除已存在的约束

ALTER TABLE 表名 drop constraints 约束名

11.级联删除约束

在删除约束的同时,也删除跟该字段有关系的约束设置

12.有效化和无效化约束

使用disable和enable关键词

13.从数据字典中查询

（1）select * from user_constraint；     查询对应的表

（2）select * from user_cons_colunmns；  查询对应的列

 

九．视图

1.创建格式: 在创建视图的时候,可以使用别名

create or replace view 视图名词 as 数据集合(通常是一个查询语句)

如：create or replace view new_emp as select empno,ename sal job from emp;

    Select * from new_emp;

2.修改视图:

replace

create or replace view myview as select...

 

3.视图创建的规则

视图的内容可以是任意的集合

 

4.删除视图:

drop view 视图名称

如：drop view new_emp;

删除视图是对基础表不会产生任何的影响

 

5.TOP-N分析:

（1）语法：rownum 运算符 行编号

（2）ROWNUM=1不会为null

 

十．其它数据库对象

1.序列:

1) 特点：

（1）共享的

（2）提供有规律的数值

（3）提取方式:

序列名.nextval

如：

select seq.nextval from emp

INSERT INTO EMP(EMPNO) VALUES(MYSEQ.NEXTVAL)；

  

2)查询已定义的序列

SELECT * FROM USER_SEQUENCES

3)使用序列的约束

（1）nextval 下一个值

（2）currval  表示当前值

（3）currval的值必须在nextval被指定后才有效

4)删除序列

drop sequence myseq

2.索引:

(1)作用:提交数据的查询效率,尤其对一些数据量很大的表

(2)定义方式：

（1）自动创建:

主键，唯一性约束

（2）手动创建

如：CREATE INDEX ENAME_IDX ON emp(ename)

3)索引的创建时机问题:

要创建索引的的情况：

（1）列经常被用来做条件查询的时候

（2）表数据量很大的时候

不要创建索引的情况:

（1）表很小

（2）列不经常作为连接条件或出现在WHERE子句中

（3）查询的数据大于2%到4%

（4）表经常更新

3.同义词

（1）目的:为了缩短对名称长度的引用

（2）创建格式:

CREATE SYNONYM  同义词名词 for 对象(scott.emp)

        如：

CREATE SYNONYM SN_EMP FOR  SCOTT.EMP

CREATE SYNONYM SN_DEPT FOR SCOTT.DEPT

SELECT * FROM SN_EMP E,SN_DEPT D WHERE E.DEPTNO=D.DEPTNO

4.集合操作符

(1）合并集合语法:

查询语句1     UNION 查询语句2

如：

SELECT * FROM EMP WHERE SAL>2000

UNION

SELECT * FROM EMP WHERE JOB='MANAGER'

(2）注意事项:

（1）不同的表合并时,要注意字段个数要对应

（2）类型要对应

（3）显示的字段是第一个表的字段列名为新的集合的列名

(3）使用union all保留重复的记录

(4）intersect交集

(5）不保留重复的minus

 

(6）分页查询：

使用rownum限定

如：

SELECT * FROM (

       SELECT ENAME,EMPNO,JOB,ROWNUM RN FROM EMP

) WHERE RN BETWEEN 3 AND 9