oracle SQL查询和SQL函数
--ORACLE数据库中的伪列rowid,rownum
select rownum,rowid,ename,empno,sal from emp;
--数据定义语言DDL
--create表的约束
create table temp(
empno number,
ename varchar2(200),
deptno number,
edate date default sysdate,--默认值
address varchar2(200) not null,--非空
sal NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (sal>0),--检查
constraint aa foreign key(deptno) references dept(deptno),--外键
constraint bb unique(ename),--唯一
constraint cc primary key(empno));--主键
--ALTER
alter table temp modify (tid number(5));--修改列,当表中有值的时候不允许
alter table temp add(tadd varchar2(2000));--添加列
alter table temp drop column tadd;--删除列
--rename表的重命名
alter table emp rename to newname;
rename emp to newename
--重命名列名
alter table emp rename column ename to aaa;
--truncate
truncate table temp;--截断表，保留结构。不能回滚
--drop
--删除用户（级联）
drop user newer cascade;
--删除表空间(包含的所有内容)
drop tablespace including contents;
--删除表
drop table temp;--从数据库中删除表及全部数据
--数据操纵语言DML
select
insert
update
delete
--利用现有表创建新表
create table temp as select * from emp;
--向表插入现有表的数据
insert into temp(ename) select ename from emp ;
--使用别名 三种方法，真接写，AS，加""号
--delete 与truncate的区别
--事务控制语言TCL(commit,savepoint,rollback)
--提交
commit work;
commit;
--保存点（标记）
savepoint sname;
--回滚
rollback;
rollback to savepoint sname;
--事务示例
update emp set ename='aaa' where empno=7369;
savepoint savename;
delete emp where emp=7369;
savepoint savename1;
rollback to savepoint savename;
commit;
--数据控制语言 DCL（grant,revoke）
--将对象的权限授予用户
grant all on emp to username;
grant select,update,delete,insert on emp to username;
--将角色的权限授予用户
grant connect to username; --连接数据库
grant resource to username;--使用数据表空间
grant unlimited tablespace to username;--表空间无限增长
grant grant any privilege to username;--授予可以授权的权限
--删除用户权限
revoke connect for username; --删除角色权限
revoke select on emp for username;--删除对象权限
--使用集合操作符
union--联合（去重复）
union all--联合所有(不去重复)
intersect--交集
minus --减集
--示例
select deptno from emp union select deptno from dept;--取并集去重复
select deptno from emp union all select deptno from dept;--取并集
select deptno from emp intersect select deptno from dept;--取交集
--注意下面两个表的位置不同，结果的区别,从第一个结果里找出不存在于第二个集合里的记录
select deptno from emp minus select deptno from dept;
select deptno from dept minus select deptno from emp;--取差集
--函数
--单行函数
--
日期函数
select add_months(sysdate,2) from dual;--月加
select months_between(sysdate,sysdate) from dual;--求两日期月差
select last_day(sysdate)from dual;--当月最后一天
select round(sysdate,'YEAR') from dual;--YEAR舍入到最近的年，MONTH舍入最近的月DAY天
select next_day(sysdate,'星期二') from dual;--返回下个星期几的日期
select trunc(sysdate,'YEAR') from dual;--YEAR截断日期到年月日，与round的区别是只舍不入默认返回天
select extract(year from sysdate) from dual;--返回日期的特定部分year,month,day
--字符函数
select initcap('adsfad') from dual;--首写母大写
select lower('AAAA') from dual;--转换小写
select upper('bbb') from daul;--转换大写
select char(67) from dual;--返回ASCII码字符
select trim(leading '9' from '9123456789') from dual;--去前面
select trim(trailing '9' from '9123456789') from dual;--去后面
select trim('9' from '9123456789') from dual;--去所有
select trim(' 9123456789 ') from dual;--去空格
select ltrim('9123456789','9') from dual;--去左边9
select rtrim('9123456789','9') from dual;--去右边9
select length('string ') from dual;--返回字符串长度注意空格也算长度
select decode('abcd','abcd','1234') from dual;--逐值替换,可替换多个，完全相同才替换
select translate('jack','abcd','1234') from dual;--在jack中用1234翻译abcd来替换其中的字符
select replace('jack','ck','hh') from dual;--把'jack'中的ck替换为hh
select instr('jack','c') from dual;--找出JACK中的C所处的位置，索引是从1开始的
select substr('jack',2,1) from dual;--截断字符串
select concat('asdf','dddd') from dual;--合并字符串
--数字函数
ceil--向上取整
abs--取绝对值
sqrt--求平方
power(m,n)--取M的N次幂
cos--取余弦函数
sin--取正弦
sign--取符号
mod(m,n)--取模
round(m,n)--四舍五入
trunc(m,n)--截断
floor--向下取整
--转换函数
select to_char(sysdate,'YYYY-MM-DD') from dual;--日期转换
select to_char('456','c9999') from dual;--添加人民币符号CNY
select to_date('2005-2-3','YYYY-MM-DD') from dual;--将字符转为日期
select sqrt(to_number('100')) from dual;--数字转换函数,取平方根
--其它函数
select nvl('','空') from dual;
select nvl2('','不为空','空') from dual;
select nullif('123','123') from dual;--相等返回空，否则返回前一个值
--组合函数
select avg(sal) from emp;--求平均值
select sum(sal) from emp;--求和
select min(sal) from emp;--最小值
select max(sal) from emp;--最大值
select count(*) from emp;--求记录数
select count(comm) from emp;--统计不为空的记录数
select count(distinct deptno) from emp;--统计不重复记录数
select deptno,max(sal) from emp group by deptno;--分组统计最大值
select deptno,max(sal) from emp group by deptno having max(sal) > 2000;
--分析函数
--row_number函数对重复数据排序也不同
select ename,s
al,row_number() over( order by sal desc) as rank from emp;--排序，数据重复排序不同
--rank() over(partition by '',order by '')--重复记录序号跳过
select deptno,ename,sal,rank() over (partition by deptno order by sal desc,comm) rank from emp;
--dense_rank() over(partition by '',order by '')--重复记录连续
select deptno,ename,sal,dense_rank() over (partition by deptno order by sal desc,comm) rank from emp;