1
|
SELECT
job_id
FROM
jobs
WHERE
job_id
LIKE
'IT\_%'
escape
'\';
|
优先级
算数运算符
连接运算符
比较运算符
IS [NOT] NULL ,LIKE, [NOT] IN
[NOT] BETWEEN
NOT
AND
OR
连接运算符
比较运算符
IS [NOT] NULL ,LIKE, [NOT] IN
[NOT] BETWEEN
NOT
AND
OR
Oracle函数
LOWER('SQL Cource') sql cource
UPPER('SQL Cource') SQL COURCE
INITCAP('SQL Cource') Sql Cource
UPPER('SQL Cource') SQL COURCE
INITCAP('SQL Cource') Sql Cource
字符控制函数
CONCAT('Hello','World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld','W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary,10.'*') 24000*****
TRIM('M' FROM 'HelloWorld') elloWorld
replace('abcd','b','m') amcd
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld','W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary,10.'*') 24000*****
TRIM('M' FROM 'HelloWorld') elloWorld
replace('abcd','b','m') amcd
数字函数
ROUND(45.926,2) 45.93
TRUNC(45.926,2) 45.92
MOD(1600, 300) 结果100
TRUNC(45.926,2) 45.92
MOD(1600, 300) 结果100
日期的数字运算
在日期上加上或减去一个数字结果仍然为日期
两个日期相减返回日期之间相差的天数
两个日期相减返回日期之间相差的天数
日期函数
MONTHS_BETWEEN 两个日期相差的月数 months_between('01-sep-95'.'11-jan-94') 19.6774194
ADD_MONTHS 向指定的日期加上若干月数 add_months('11-jan-94',6) '11-jul-94'
NEXT_DAY 指定日期的下一个日期 next_day('01-sep-95','friday') '08-sep-95'
LAST_DAY 本月的最后一天 last_day('01-feb-95') '28-feb-95'
ROUND日期四舍五入
TRUNC 日期截断
假设SYSDATE = '25-JUL-95'
ROUND(SYSDATE,'MONTH') 01-AUG-95
ROUND(SYSDATE,'YEAR') 01-JAN-96
TRUNC(SYSDATE,'MONTH') 01-JUL-95
TRUNC(SYSDATE,'YEAR') 01-JAN-95
ADD_MONTHS 向指定的日期加上若干月数 add_months('11-jan-94',6) '11-jul-94'
NEXT_DAY 指定日期的下一个日期 next_day('01-sep-95','friday') '08-sep-95'
LAST_DAY 本月的最后一天 last_day('01-feb-95') '28-feb-95'
ROUND日期四舍五入
TRUNC 日期截断
假设SYSDATE = '25-JUL-95'
ROUND(SYSDATE,'MONTH') 01-AUG-95
ROUND(SYSDATE,'YEAR') 01-JAN-96
TRUNC(SYSDATE,'MONTH') 01-JUL-95
TRUNC(SYSDATE,'YEAR') 01-JAN-95
源数据类型 | 目标数据类型 |
VARCHAR2 or CHAR | NUMBER |
VARCHAR2 or CHAR | DATE |
NUMBER | VARCHAR2 |
DATE | VARCHAR2 |
to_char(date,'format_model')
YYYY 2011
YEAR twenty eleven
MM 04
MONTH 4月
DY 星期一
DAY 星期一
DD 02
使用双引号向日期中添加字符
DD "of" MONTH 12 of OCTOBER
YYYY 2011
YEAR twenty eleven
MM 04
MONTH 4月
DY 星期一
DAY 星期一
DD 02
使用双引号向日期中添加字符
DD "of" MONTH 12 of OCTOBER
TO_CHAR 函数对数字的转换
TO_CHAR(number,'format_model')
9 数字
0 零
$ 美元符
L 本地货币符号
. 小数点
,千位符
NVL(hire_date,'01-JAN-97')将空值转换为一个已知的值
NVL2(expr1,expr2,expr3) expr1不为NULL,返回expr2,为NULL,返回expr3.
NULLIF(expr1,expr2) 相等返回NULL,不等返回expr1
COALESCE(commission_pot, salart, 10)找第一个不为空的值
NVL2(expr1,expr2,expr3) expr1不为NULL,返回expr2,为NULL,返回expr3.
NULLIF(expr1,expr2) 相等返回NULL,不等返回expr1
COALESCE(commission_pot, salart, 10)找第一个不为空的值
TO_NUMBER 和TO_DATE函数
TO_NUMBER(char, 'format_model') 函数将字符转换成数字
TO_DATE 函数将字符转换为日期
TO_DATE(char, 'format_model') 使用TO_DATE函数将字符转换成日期
TO_DATE 函数将字符转换为日期
TO_DATE(char, 'format_model') 使用TO_DATE函数将字符转换成日期
条件表达式
1
2
3
4
|
CASE
....
WHEN
...
THEN
...
WHEN
...
THEN
...
ELSE
...
END
|
1
2
3
4
5
|
SELECT
CASE
job_id
WHEN
'IT_PROG'
THEN
1.10*salary
WHEN
'ST_CLERK'
THEN
1.15*salary
ELSE
salary
END
"REVISED_SALARY"
FROM
employees;
|
--------------------
1
2
3
4
|
DECODE(col | expression, search1, result1,
search2, result2,
defaultresult
)
|
1
2
3
4
5
|
SELECT DECODE(job_id,
'IT_PROG'
,
1.10
*salary,
'ST_CLERK'
,
1.15
*salary,
'SA_REP'
,
1.20
*salary,
salary) REVISED_SALARY
FROM employees;
|
组函数使用
AVG 平均值
COUNT 数量
MAX最大值
MIN最小值
SUM 总和
COUNT 数量
MAX最大值
MIN最小值
SUM 总和
分组数据
GROUP BY子句用法
1
2
3
4
5
6
|
SELECT
column
, group_function(
column
)
FROM
table
[
WHERE
condition]
[
GROUP
BY
group_by_expression]
[
HAVING
group_condition]
[
ORDER
BY
column
]
|
可以在GROUP BY 字句中包含多个列,不能在WHERE 子句中使用组函数,可以在HAVING 子句中使用组函数。
使用HAVING 子句过滤分组
行已经被分组
使用了组函数
满足HAVING 子句中条件的分组将被显示
使用了组函数
满足HAVING 子句中条件的分组将被显示
连接查询
等值连接 不等值连接 外连接 自连接
1
2
3
4
|
SELECT
e.* ,j.*
FROM
employee e,job_grades j
WHERE
e.salary
BETWEEN
j.lowest_sal
AND
j.highest_sal;
|
右连接查询,以table2为依据
1
2
3
|
SELECT
table1.column1,table2.column1
FROM
table1, table2
WHERE
table1.column1(+)=table2.column1;
|
叉集
使用CROSS JOIN子句使连接的表产生叉集(叉集和笛卡尔集是相同的)。
1
2
3
|
SELECT
last_name, department_name
FROM
employees
CROSS
JOIN
departments;
|
自然连接
natual join 子句,会以两个表中具有相同名字的列为条件创建等值连接。
natual join 子句,会以两个表中具有相同名字的列为条件创建等值连接。
在表中查询满足等值条件的数据
如果只是列名相同而数据类型不同,则会产生错误。
1
2
3
|
SELECT
department_id ,department_name, location_id city
FROM
departments
NATUAL
JOIN
locations;
|
使用USING 子句创建连接
在NATUAL JOIN 子句创建等值连接时,可以使用USING 子句指定等值连接中需要用到的列。
使用USING可以在有多个列满足条件时进行选择。
不要给选中的列加上表明前缀或别名。
NATUAL JOIN 和USING 子句经常同时使用。
1
2
3
|
SELECT
e.department_id, e.last_name, d.locaiton_id
FORM employees e
JOIN
departments d
USING (department_id);
|
使用ON子句创建连接
自然连接中是以具有相同名字的列为连接条件的。
可以使用ON子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
使用ON子句创建多表(3张表)连接
1
2
3
|
SELECT
e.department_id,e.last_name, e.department_id, d.department_id, d.location_id
FROM
employees e
JOIN
departments d
ON
(e.department_id = d.department_id);
|
内连接和外连接
在SQL 1999中,内连接只返回满足条件的数据
两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行,这种连接称为
左(或右)外连接。
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行,这种连接称为
满外连接。
左外联接
1
2
3
4
|
SELECT
e.last_name, e.department_id, d.department_name
FROM
employee e
LEFT
OUTER
JOIN
departments d
ON
(e.department_id = d.department_id);
|
右外联接
1
2
3
4
|
SELECT
e.last_name, e.department_id,d.department_name
FROM
employees e
RIGHT
OUTER
JOIN
departments d
ON
(e.department_id = d.department_id);
|
满外连接
1
2
3
4
|
SELECT
e.last_name , e.department_id, d.department_name
FROM
employees e
FULL
OUTER
JOIN
departments d
ON
(e.department_id = d.department_id);
|
子查询
单行子查询操作符
= > >= < <= <>
多行子查询
IN 等于列表中的任意一个
ANY和子查询返回的任意一个值比较
ALL和子查询返回的所有值比较
1
2
3
4
5
6
7
8
|
SELECT
employee_id,last_name,job_id,salary
FROM
employees
WHERE
salqry <
ALL
(
SELECT
salary
FROM
employees
WHERE
job_id =
'IT_PROG'
)
AND
job_id <>
'IT_PROG'
;
|