sql的常用功能

select
from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by 

--创建数据库  CREATE DATABASE 数据库名;
CREATE DATABASE RUNOOB;
--删除数据库 drop database <数据库名>
drop database RUNOOB;
--返回指定的第几条 row_number()over(order by id)
select * from(select id,row_number()over(order by id) num from b_teacher ) where num=2;
--返回前多少条数据
select * from b_teacher where ROWNUM<=2;
--模糊查询
select * from b_teacher where name like '%王%';
--通配符 % 代表的一个或者是多个  _代表是一个字符  [charlist]字符列中的任何单一字符  [^charlist]不在字符列的任何一个字符
select * from b_teacher  where name like '王_';
select * from b_teacher  where not  regexp_like(name,'[王张]')  ;--不包含
select * from b_teacher  where   regexp_like(name,'[王张]')  ;--包含

--IN 包含
select * from b_teacher where name in ('陈军杰','韩爱鸽');
select * from b_teacher where name in (select name from bt_teacher  where name like '%王%');

--BETWEEN 之间的数值
select id,LEAVE_DAY from b_leave where LEAVE_DAY between 1 and 10;

--AS 别名
select id , name as tname,CONCAT(kind,',',SEX) as text from b_teacher;
select kind||(nvl2(kind,',',''))||sex as text from b_teacher;--组合别名kind和sex设置为同一个,这个时候对于字段的组合可以起到作用--行的处理

select * from S_USER where name='王传平';
select * from b_leave where name='测试销假附件和请假天数回显'