经典sql语句

select * from dept order by name,age;
select sum(age) from dept;

/*与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。*/
select t.*,sum(age) over(order by name) from dept t;--按name排序，连续叠加
select t.*,sum(age) over(partition by depname) from dept t;--不连续叠加，按部门同一部门总和不变
select t.*,sum(age) over(partition by depname order by name) from dept t;--按name排序，连续叠加，按部门
select t.*,row_number() over(partition by depname order by name) from dept t;
select t.*,rownum  from dept t;

--row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号

（组内连续的唯一的）。
select t.*,count(*) over(partition by depname order by name) from dept t;--实现分组内部排序，count(*)只是配合使用over
select count(*) from dept;

select t.*,row_number() over(order by name) from dept t;
--Lead()就是取当前顺序的下一条记录
select t.name,Lead(name) over(order by name),lag(name) over(order by name) from dept t;

--数值函数的使用
select sum(age) s from dept having sum(age) > 100 group by depname --聚合后筛选
select trunc(4.216,2) from dual--数值截取   
select round(4.216,2) from dual--数值精度
select mod(4,3) from dual--数值取余
select * from dual

--字符处理函数
select concat(‘sd‘,‘23‘) from dual
select ‘sd‘||‘23‘||‘rt‘ from dual
select substr(‘asdfghj‘,3,3) from dual
select substr(‘asdfghj‘,length(‘asdfghj‘)-2) from dual
select instr(‘Hello World‘,‘or‘)from dual
select lpad(‘Smith‘,10,‘*‘)from dual

--空值处理函数
select nvl(description,0) from dept
select nvl2(description,description||‘has‘,0) from dept--如果第一个表达式为空就返回第三个表达式，不为空返回第二个表达式
select decode(name,‘1‘,‘xmh01‘,‘2‘,‘xmh02‘,‘no‘) ee from dept;
select case name when ‘1‘ then ‘xmh01‘ when ‘2‘ then ‘xmh02‘ else ‘no‘ end name from dept
select nullif(name,age) from dept;--如果第一个表达式与第二个表达式相等返回空值，否则返回第一个表达式的值

select * from (select rownum num,T.* from dept T where rownum <= 4) t1 where t1.num > 1
select * from dept where name in (select username from t_user)--in里的为基础表，数据较少
select * from dept d where exists (select 1 from t_user t where t.username = d.name)--exists里数据较大，外部的为基础表

select * from dept;
alter table dept add (depname varchar2(50))
update dept set depname = ‘dep2‘ where name not like ‘xmh%‘
insert into test values (‘wefsdfsf‘,‘my‘,‘5‘,‘rd‘,‘country2‘)

select * from t_user;
create table t_user2 (id varchar2(50),username varchar2(20),province varchar2(20),city varchar2(50),street varchar2(50))

select * from test t left join t_user u on t.username = u.username
select * from t_user t left join test u on t.username = u.username
select * from t_user t full outer join test u on t.username = u.username
select * from test t inner join t_user u on t.username = u.username
select distinct * from t_user;
select * from test