以char27为结束符oracle,oracle 数据库基础

关键字

DISTINCT

过滤where

where 将不满足的过滤掉

连接符||

把列与列，列与字符串连接在一起，用“||”表示，可以用来合成。

select last_name || job_id from employees;

条件：将符合的条件选择处理 where

select employee_id ,job_id,last_name fromemployees where department_id = 90;

字符和日期

字符和日期要包含在单引号中。

字符大小写敏感，日期格式敏感。

默认的日期格式是 DD-MON-RR

where解析顺序从右到左

BETWEEN .. AND...

select last_name ,job_id  from employees  where salary between 3000 and 5000;

IN(set)：等值与列表的值

select last_name ,job_id,manager_id  from employees  where manager_id in (100);

LIKE：模糊查询

% 代表零个或多个字符(任意个字符)。

_ 代表一个字符。

IS NULL：空值

逻辑运算

And,Or,Not

order by

使用 ORDER BY 子句排序

ASC(ascend): 升序[z8]

DESC[z9] (descend): 降序

ORDER BY 子句在SELECT语句的结尾。

例如：select last_name,job_id,department_id,hire_date from  employees order by hire_date;

按别名；来排序也可以：

select last_name,job_id,department_id,salary* 12 sal,hire_date from  employeesorder by sal;

转小写函数：LOWER

LOWER('HHHHHHHH')

select lower('HHHHHHHH') from dual;

输出结果：

hhhhhh

转大写：UPPER

UPPER('SQLCOURSE')

select UPPER('SQL Course') from dual;

输出结果

SQL COURSE

函数

字符控制函数

CONCAT('Hello', 'World')          HelloWorld

SUBSTR[z12] ('HelloWorld',1,5)                  Hello

LENGTH('HelloWorld')                        10

LPAD[z13] (salary,10,'*')

RPAD(salary, 10, '*')

INSTR[z14] ('HelloWorld', 'W')           6

TRIM[z15] ('H' FROM 'HelloWorld')          elloWorld

replace[z16] ('abcd', 'b', 'm')                                amcd

数字函数

ROUND: 四舍五入

ROUND(45.926,2)                     45.93

TRUNC:    截断

TRUNC(45.926,2)                      45.92

MOD: 求余

MOD(1600,300)                         100

日期和函数：

函数SYSDATE

日期

select last_name,(sysdate-hire_date)/7 fromemployees where department_id =  90;

例子：昨天  今天 明天

select (sysdate-1) 昨天,sysdate 今天,(sysdate+1)明天

2  from dual;

昨天           今天           明天

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

25-8月 -13     26-8月 -13     27-8月 -13

日期函数

转换函数

隐式

16.1、TO_CHAR 函数对日期的转换

TO_CHAR(date,'format_model')

必须包含在单引号中而且大小写敏感。

可以包含任意的有效的日期格式。

日期之间用逗号隔开。

TO_CHAR 函数

对数字的转换

TO_CHAR(number,'format_model')

TO_NUMBER 函数

将字符转换成数字:

TO_NUMBER(char[,'format_model'])

使用 TO_DATE

函数将字符转换成日期:

TO_DATE(char[, 'format_model'])

NVL函数

1、 ：将空值转换成一个已知的值：

不忽略空值

NVL函数使分组函数无法忽略空值

SELECT COUNT(DISTINCT department_id)

2  FROM  employees;

COUNT(DISTINCTDEPARTMENT_ID)

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

11

忽略空值

select avg(commission_pct) from employees;

AVG(COMMISSION_PCT)

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

0.222857142857143

NVL2 函数

NVL2 (expr1, expr2,expr3) : expr1不为NULL，返回expr2；为NULL，返回expr3

NULLIF 函数

NULLIF (expr1, expr2) :相等返回NULL，不等返回expr1

COALESCE 函数

COALESCE与NVL相比的优点在于COALESCE可以同时处理交替的多个值。

如果第一个表达式为空,则返回下一个表达式，对其他的参数进行COALESCE。

即：找第一个不为空的值。

分组函数(重点)

使用GROUP BY 子句数据分组。使用HAVING 子句过滤分组结果集

组合函数：avg，count，max，min，sum

组合函数语法：

SELECT     [column,] group_function(column),...

FROM                table

[WHERE  condition]

[GROUPBY      column]

[ORDERBY      column

COUNT(DISTINCT expr)

返回expr非空且不重复的记录总数

SELECT COUNT(DISTINCT department_id)

2  FROM  employees;

COUNT(DISTINCTDEPARTMENT_ID)

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

11

group by：分组数据

SELECT              column, group_function(column)

FROM               table

[WHERE            condition]

[GROUP BYgroup_by_expression]

[ORDER BY       column];

可以使用GROUPBY子句将表中的数据分成若干组

包含在GROUP BY 子句中的列不必包含在SELECT 列表中

group by 增强

多个列分组

过滤分组：HAVING子句

条件：

1、行已经被分组。

2.使用了组函数。

3.满足HAVING子句中条件的分组将被显示。

语法：

SELECT     column,group_function

FROM                table

[WHERE   condition]

[GROUP BYgroup_by_expression]

[HAVINGgroup_condition]

[ORDER BY       column]

多表中获得数据

使用外连接可以查询不满足连接条件的数据。

外连接的符号是(+)。

希望：在最后的结果中包含某些不成立的记录

elect d.deptno, d.dname, count(e.empno)人数from emp e,dept d

where e.deptno(+)=d.deptnogroup by d.deptno,d.dname;

左外连接

where e.deptno=d.deptno不成立的时候，等号左边代表的表任然被包含

右外连接

where e.deptno=d.deptno不成立的时候，等号右边代表的表任然被包含

满外联接

NATURAL JOIN 自然链接

(用比较少)

会以两个表中具有相同名字的列为条件创建等值连接。

在表中查询满足等值条件的数据。

如果只是列名相同而数据类型不同，则会产生错误。

自连接

使用 USING 子句创建连接(用比较少)

内容：在NATURALJOIN 子句创建等值连接时，可以使用USING 子句指定等值连接中需要用到的列。

使用USING可以在有多个列满足条件时进行选择。

不要给选中的列中加上表名前缀或别名。

NATURAL JOIN和USING子句经常同时使用。

使用ON 子句创建连接

自然连接中是以具有相同名字的列为连接条件的。

可以使用ON 子句指定额外的连接条件。

这个连接条件是与其它条件分开的。

ON 子句使语句具有更高的易读性。

使用 ON 子句创建多表连接

SELECT employee_id, city, department_name

FROM  employees e

JOIN  departments d

ON    d.department_id = e.department_id

JOIN  locations l

ON    d.location_id = l.location_id;

单函数操作

操作数据对象;接受参数返回一个结果;只对一行进行变换;每行返回一个结果;可以转换数据类型;可以嵌套;参数可以是一列或一个值

嵌套函数

条件表达式

在SQL语句中使用IF-THEN-ELSE逻辑

1、 if语句格式：

If 条件 then 语句1;

语句2;

End if;

2. IF 条件  THEN  语句序列1；

ESLE   语句序列 2；

END   IF；

3. IF  条件  THEN 语句;

ELSIF语句  THEN  语句;

ELSE    语句;

END  IF;

单表查询

单表查询比较简单这里就不做详细介绍

查询奖金是null的查询语句

--查询奖金为null的员工

1、select * from empwhere comm=null

第一种查出来是null的

2、select * from empwhere comm is null

多表查询

通过表的别名，将同一张表视为多张表

SQL> select d.ename||'的老板是'||b.ename

2  from emp d,emp b

3  where d.mgr=b.empno

什么时候用子查询：在查询是基于未知的值时应使用子查询

注意问题：

1、  括号

2、  合理书写风格

3、  可以在子查询的where，select，having[z26] ，from 后面放置

4、  不可以在group by 后面放置子查询

5、强调from后面的子查询

6、  主查询和子查询可以不是同一张表，只要子查询返回的结果主查询可以使用即可

7、单行子查询(返回值只有一条)只能使用单行操作

主查询里面可以有多个子查询

单行子查询

只返回一行。

使用单行比较操作符。

多行子查询

In:等于列表中的任何一个

Any：集合中任意一个值

All:和子查询返回的所有值比较

使用等值和不等值连接在SELECT 语句中查询多个表中的数据。

使用自连接

处理数据DML(数据操作语言)

关键字：insert update delete select  增删改查数据库里面的数据

Insert : insert : insert into table (cloum,….)values();

地址符&(增删改查都可以用)

当输入地址符，并带参数的时候会让我们自己在输入

更新update

Update  table set colum = value ………. Where id =?

update emp set sal=700,ename='lisi' where empno=1000

在update中使用子查询

删除:delete

delete和truncate区别：

1、delete是DML(可以回滚)语句，truncate是DDL语句(不可以回滚)

2、delete不回释放空间，truncate会

3、delete会产生碎片，truncate不会 (碎片会影响查询效率)

4、delete可以闪回，truncate不可以

5、.(MYSQL中讲) delete是逐条删除；truncate先摧毁表 再重建

Oracle 中delete 比truncate 快

格式: Delete  from  table where id =?

6．删除中使用子查询

Oracle 中delete 比truncate 快

事务

由增删改组成，

事务步骤：

1、  起始标志 ：DML语句

2、  结束标志：提交-à显示

事务是不能跨越(DML和DDL)

回滚存储点[z28] ：savepoint a;

Oracle事务隔离级别：3个

串行会影响性能(锁)

Read only oracle事务

DDL(数据定义语言)

是针对数据库表，表结构字段的操作

关键词：create/alter/drop/truncatetable

create/drop view/sequence/index/synonym

1、  子查询建表

Create  tabletablename  as select * from table;

2、  修改表

2.1追加新的列

ALTER TABLE table

ADD             (column datatype[DEFAULT expr]

[, column datatype]...);

例子：alter table test1add photo blob;

2.2修改现有的列

ALTER TABLE table

MODIFY       (columndatatype[DEFAULT expr]

[, column datatype]...);

2.3删除一个列

ALTER TABLE table

DROP         column     (column);

例子：alter table test1drop column photo;

2.4 重命名表(改变表的名称)

ALTER TABLE table_namerename column old_column_name

tonew_column_name

例子：alter table test1rename column tname to username;

3、  删除

3.1数据和结构都被删除

3.2所有正在运行的相关事物被提交

3.3所有相关索引被删除

3.4DROP TABLE 语句不能回滚，但是可以闪回

drop table tablename;

集合运算

集合运算关键字：union，union all，INTERSECT

union 去掉重复元素后的所有记录(并集)

显示员工当前和之前的工作情况，每次记录显示一次。

select * from emp where deptno=10

union

select* from emp where deptno=20;

union All：返回两个集合的所有记录，包括重复的

INTERSECT 运算符返回同时属于两个集合的记录

集合注意事项

1. 参与运算的各个集合必须列数相同 且类型一致

2. 采用第一个集合的表头作为最后的表头

3. 如果排序，必须在每个集合后使用相同的orderby

select deptno,job,sum(sal) from emp groupby deptno,job

union

select deptno,to_char(null),sum(sal) from emp group by deptno

union

select to_number(null),to_char(null),sum(sal) from emp;