在SQLPLUS下实现中英字符集转换
alter session set nls_language=AMERICAN;
alter session set nls_language=SIMPLIFIED CHINESE;
主要知识点
一有关表的操作
)建表
create table test as select * from dept; 从已知表复制数据和结构
create table test as select * from dept where =; 从已知表复制结构但不包括数据
)插入数据
insert into test select * from dept;
二运算符
算术运算符+ * / 可以在select 语句中使用
连接运算符|| select deptno|| dname from dept;
比较运算符> >= = != < <= like between is null in
逻辑运算符not and or
集合运算符 intersect union union all minus
要求对应集合的列数和数据类型相同
查询中不能包含long 列
列的标签是第一个集合的标签
使用order by时必须使用位置序号不能使用列名
例集合运算符的使用
intersect union union all minus
select * from emp intersect select * from emp where deptno= ;
select * from emp minus select * from emp where deptno=;
select * from emp where deptno= union select * from emp where deptno in (); 不包括重复行
select * from emp where deptno= union all select * from emp where deptno in (); 包括重复行
三常用 ORACLE 函数
sysdate为系统日期 dual为虚表
一）日期函数[重点掌握前四个日期函数]
add_months[返回日期加(减)指定月份后(前)的日期]
select sysdate Sadd_months(sysdate) S
add_months(sysdate) S from dual;
last_day [返回该月最后一天的日期]
select last_day(sysdate) from dual;
months_between[返回日期之间的月份数]
select sysdate S months_between(月sysdate) S
months_between(月月) S from dual
next_day(dday): 返回下个星期的日期day为或星期日星期六表示星期日
select sysdate Snext_day(sysdate) S
next_day(sysdate星期日) S FROM DUAL
round[捨入到最接近的日期](day:捨入到最接近的星期日)
select sysdate S
round(sysdate) S
round(sysdateyear) YEAR
round(sysdatemonth) MONTH
round(sysdateday) DAY from dual
trunc[截断到最接近的日期]
select sysdate S
trunc(sysdate) S
trunc(sysdateyear) YEAR
trunc(sysdatemonth) MONTH
trunc(sysdateday) DAY from dual
返回日期列表中最晚日期
select greatest(月月月) from dual
二）字符函数(可用于字面字符或数据库列)
字符串截取
select substr(abcdef) from dual
查找子串位置
select instr(abcfdgfdhdfd) from dual
字符串连接
select HELLO||hello world from dual;
)去掉字符串中的空格
select ltrim( abc) s
rtrim(zhang ) s
trim( zhang ) s from dual
)去掉前导和后缀
select trim(leading from ) s
trim(trailing from ) s
trim( from ) s from dual;
返回字符串首字母的Ascii值
select ascii(a) from dual
返回ascii值对应的字母
select chr() from dual
计算字符串长度
select length(abcdef) from dual
initcap（首字母变大写） lower(变小写)upper(变大写)
select lower(ABC) s
upper(def) s
initcap(efg) s from dual;
Replace
select replace(abcbxy) from dual;
translate
select translate(abcbxx) from dual; x是位
lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad(func=) s rpad(func) s from dual;
select lpad(dname=) from dept;
decode[实现if then 逻辑]
select deptnodecode(deptno其他) from dept;
三）数字函数
取整函数（ceil 向上取整floor 向下取整）
select ceil() Nfloor() N from dual;
取幂(power) 和 求平方根(sqrt)
select power() Nsqrt() N from dual;
求余
select mod() from dual;
返回固定小数位数 (round:四捨五入trunc:直接截断)
select round() Ntrunc() N from dual;
返回值的符号(正数返回为负数为)
select sign()sign() from dual;
四）转换函数
to_char()[将日期和数字类型转换成字符类型]
) select to_char(sysdate) s
to_char(sysdateyyyymmdd) s
to_char(sysdateyyyy) s
to_char(sysdateyyyymmdd hh:mi:ss) s
to_char(sysdate hh:mi:ss) s
to_char(sysdateDAY) s from dual;
) select salto_char(sal＄) nto_char(sal＄) n from emp
to_date()[将字符类型转换为日期类型]
insert into emp(empnohiredate) values(to_date(yyyymmdd));
to_number() 转换为数字类型
select to_number(to_char(sysdatehh)) from dual; //以数字显示的小时数
五）其他函数
user:
返回登录的用户名称
select user from dual;
vsize:
返回表达式所需的字节数
select vsize(HELLO) from dual;
nvl(exex):
ex值为空则返回ex否则返回该值本身ex（常用）
例如果雇员没有佣金将显示否则显示佣金
select commnvl(comm) from emp;
nullif(exex):
值相等返空否则返回第一个值
例如果工资和佣金相等则显示空否则显示工资
select nullif(salcomm)salcomm from emp;
coalesce:
返回列表中第一个非空表达式
select commsalcoalesce(commsalsal*) from emp;
nvl(exexex) :
如果ex不为空显示ex否则显示ex
如查看有佣金的雇员姓名以及他们的佣金
select nvl(commename) as HaveCommNamecomm from emp;
六）分组函数
max min avg count sum
整个结果集是一个组
) 求部门 的最高工资最低工资平均工资总人数有工作的人数工种数量及工资总和
select max(ename)max(sal)
min(ename)min(sal)
avg(sal)
count(*) count(job)count(distinct(job))
sum(sal) from emp where deptno=;
带group by 和 having 的分组
)按部门分组求最高工资最低工资总人数有工作的人数工种数量及工资总和
select deptno max(ename)max(sal)
min(ename)min(sal)
avg(sal)
count(*) count(job)count(distinct(job))
sum(sal) from emp group by deptno;
)部门的最高工资最低工资总人数有工作的人数工种数量及工资总和
select deptno max(ename)max(sal)
min(ename)min(sal)
avg(sal)
count(*) count(job)count(distinct(job))
sum(sal) from emp group by deptno having deptno=;
stddev 返回一组值的标准偏差
select deptnostddev(sal) from emp group by deptno;
variance 返回一组值的方差差
select deptnovariance(sal) from emp group by deptno;
带有rollup和cube操作符的Group By
rollup 按分组的第一个列进行统计和最后的小计
cube 按分组的所有列的进行统计和最后的小计
select deptnojob sum(sal) from emp group by deptnojob;
select deptnojob sum(sal) from emp group by rollup(deptnojob);
cube 产生组内所有列的统计和最后的小计
select deptnojob sum(sal) from emp group by cube(deptnojob);
七临时表
只在会话期间或在事务处理期间存在的表
临时表在插入数据时动态分配空间
create global temporary table temp_dept
(dno number
dname varchar())
on commit delete rows;
insert into temp_dept values(ABC);
commit;
select * from temp_dept; 无数据显示数据自动清除
on commit preserve rows:在会话期间表一直可以存在（保留数据）
on commit delete rows:事务结束清除数据（在事务结束时自动删除表的数据）
