字符串操作:
concat('Hello', 'World') HelloWorld 连接字符串内容
substr('HelloWorld',1,5) Hello substr(字符串,第一个字符位置,结束字符位置)
length ('HelloWorld') 10 字符串长度
instr ('HelloWorld', 'W') 6 instr(字符串,要查询的字符) 字符在字符串的位置
lpad (salary,10,'*') *****24000 lpad(字符串,想要的总长度,补全字符)左边补全
rpad (salary, 10, '*') 24000***** repad(字符串,想要的总长度,补全字符)右边补全
trim ('H' FROM 'HelloWorld') elloWorld 在指定字符串中去除指定字符
trim(' HelloWorld') HelloWorld 去除字符串前后空格
trim('Hello World') Hello World 去除字符串前后空格
大小写转换:
lower ('SQL Course') sql course 将字符串所有大写转换成小写
upper('SQL Course') SQL COURSE 将字符串所有小写转换成大写
initcap ('SQL course') Sql Course 将字符串首字母转换成大写
数字操作函数:
round(45.926, 2) 45.93 默认整数四舍五入 或者 按照所取位数四舍五入
trunc(45.926, 2) 45.92 默认整数取整 或者按照所取位数取值
mod(1600, 300) 100 取余
日期操作:
Months_between ('01-SEP-95','11-JAN-94') 19.6774194 计算两个日期的差
add_months ('11-JAN-94',6) 11-Jul-94 加六个月
NEXT_DAY ('01-SEP-95','FRIDAY') 8-Sep-95
NEXT_DAY ('01-SEP-95',1) 3-Sep-95
NEXT_DAY ('1995-09-01',1) ORA01861:literal does not match format string
NEXT_DAY (to_date('1995-09-01','YYYY-MM-DD'),1) 3-Sep-95
LAST_DAY('01-FEB-95') 28-Feb-95
ROUND('25-JUL-95','MONTH') 1-Aug-95
ROUND('25-JUL-95' ,'YEAR') 1-Jan-96
TRUNC('25-JUL-95' ,'MONTH') 1-Jul-95
TRUNC('25-JUL-95','YEAR') 1-Jan-95
单行函数
to_char (date, 'format_model') ; 日期到字符串的转换
to_char(number, 'format_model') 数字到字符串的转换
to_number(char[, 'format_model']) 字符串到数字的转换
to_date(char[, 'format_model']) 字符串到日期的转换
其他常用单行函数
nvl (expr1, expr2) 如果expr1为空,这返回expr2
nvl2 (expr1, expr2, expr3) 如果expr1为空,这返回expr3(第2个结果)否则返回expr2 nullif (expr1, expr2) 如果expr1和expr2相等,则返回空
coalesce (expr1, expr2, ..., exprn) 如果expr1不为空,则返回expr1,结束;否则计算expr2,直到找到 一个不为NULL的值 或者如果全部为NULL,也只能返回NULL
条件表达式
case when ..............then
when…………..then
………….
Else……..
End ”…”
Decode(条件colum,与colum相等,结果,与colum相等,结果…………………,结果参数) 列名
多表关联查询
交叉连接:相当于没有连接条件的多表关联查询,结果是个笛卡尔乘积
table1 cross join table2
自然连接:相当于Oracle的“等于连接”,只不过是让系统自己去找两张表中字段名相同的字段作为 “等于连接”条件;(注意如果两个表中有相同的列名,但字段类型不一样,这会引发一个错误)
Table1 natural join table2
Using子句:Using子句可看成是 自然连接 的一种补充功能,我们知道自然连接会让系统自动查找两张表中的 所有列名相同的字段,并试图建立“等于连接”;但有的时候我们不期望这么做,而只是期望某个特定的字段作为“等于连接”的条件,这种情况下可以使用Using 子句来做限制
Using(参数名)
内连接:相当于Oracle的“等于链接” , 关键字:inner join。
Table1 join table2 on 条件 join table1(table2) on 条件
外连接:可细分为左外连接、右外连接、全外连接
左外连接:table1 left outer join table2 on 条件
右外连接:table1 right outer join table2 on 条件
全外连接:table1 full outer join table2 on 条件
分组计算函数
1、求和 (sum)
2、求平均值(avg)
3、计数(count)
4、求标准差(stddev)
5、求方差(variance)
6、求最大值(max)
7、求最小值(min)
8、求行数(row_number())
使用方法:select 分组计算函数() from table group by 计算依据
count(*) 返回满足选择条件的所有行的行数,包括值为空的行和重复的行
count(expr) 返回满足选择条件的且表达式不为空行数。
count(distinct expr) 返回满足选择条件的且表达式不为空,且不重复的行数。
不能在Where 条件中使用分组计算函数表达式,当出现这样的需求的时候,使用Having 子句
Select 分组计算函数(),参数
from table
group by 计算依据
having 分组计算函数()限制条件,如:=,>,<等
子查询
注意:
单行比较必须对应单行子查询(返回单一结果值的查询); 比如= , >
多行比较必须对应多行子查询(返回一个数据集合的查询);比如 IN , > ANY, > ALL 等
Select colum
From table,(select …from…where…)
where colum><= (select…from…where)
DML语句
Insert对应的列名和value,如果没写,则为null
Insert into table(colum………..) values(value………………..)
Insert into table(colum………..) select … from… where….
Update table set colum = value where …..
删除某些符合条件的记录:Delete from table where………..
删除一张表中的所有记录:delete from table
数据库对象-表
表的命名要求和表中列的命名要求:
1、必须以字母开头
2、长度不能超过30个字符
3、只能包含 A–Z, a–z, 0–9, _, $, and #
4、不能与数据库中的已有对象重名
5、不能使用Oracle 数据库的保留
数据类型:
VARCHAR2(size) 可变长字符串
CHAR(size) 定长字符串
NUMBER(p,s) 可变长数值
DATE 日期时间
LONG 可变长大字符串,最大可到2G CLOB 可变长大字符串数据,最大可到4G
RAW and LONG RAW 二进制数据
BLOB 大二进制数据,最大可到4G
BFILE 存储于外部文件的二进制数据,最大可到4G
ROWID 64进制18位长度的数据,用以标识行的地址
TIMESTAMP 精确到分秒级的日期类型(9i以后提供的增强数据类型)
INTERVAL YEAR TO MONTH 表示几年几个月的间隔(9i以后提供的增强数据类型-极其少见)
INTERVAL DAY TO SECOND 表示几天几小时几分几秒的间隔(9i以后提供的增强数据类型-极其 少见)
数据库对象-约束
约束的概念:Oracle 数据库使用“约束”来阻止对数据库表中数据的不合法的“增删改”动作。
常用的约束有如下几种:
NOT NULL (非空约束)
UNIQUE (唯一性约束)
PRIMARY KEY (主键约束)
FOREIGN KEY (外键约束)
CHECK (自定义约束)
约束的创建方法:
1、在创建表的时候同时创建约束
2、另外单独创建约束
如:
CREATE TABLE employees
(employee_id NUMBER(6),
first_name VARCHAR2(20), ...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
单独创建约束语法:
ALTER TABLE tablename ADD CONSTRAINT constraintname
constrainttype (column1,…);
使用集合操作
两个表总数据相加
UNION: 去除重复记录
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;
UNION ALL:保留重复记录
SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;
INTERSECT:取交集
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
MINUS:取差集
SELECT employee_id,job_id
FROM employees
MINUS
SELECT employee_id,job_id
FROM job_history;