sql语法的一些使用方法

select * from project_origin_list where implement_plan is null

select project_name ||'，'|| year deptyear  from project_origin_list//连接字符串

select (submit_dept*1000-year) newnumber,project_name from project_origin_list where submit_dept*10>100

select project_code*100/10 newprojectcode from project_origin_list where  year*100-100>submit_dept

select project_name from project_origin_list intersect select child_project_name from project_child_list//交集

select * from project_child_list where charge_user between 400 and 500

select count(*) newcount from  project_child_list where  charge_user between 400 and 500

select sum(charge_dept) newdept,sum(charge_user) from project_child_list

select sum(charge_dept)/sum(charge_user) newtype from project_child_list where id is not null

select * from project_child_list

select avg(charge_user)/avg(charge_dept),max(charge_dept),sum(charge_dept) from project_child_list

select max(charge_user),min(charge_user) from project_child_list

select stddev(charge_user) from project_child_list //平均差

select VARIANCE(charge_user) from project_child_list //方差

select abs(project_code) newcode from project_origin_list//绝对值

select total_money, ceil(total_money),floor(total_money)from project_origin_list where id='245'//ceil:比total_money大的最小整数，,floor：比total_money小的最大整数

select exp(total_money) from project_origin_list//取对数

select  mod(project_code,'20') newnumber from project_origin_list //求余数

select total_money from project_origin_list where sign(total_money-25)='1'//如果参数的值为负数那么SIGN 返回-1 如果参数的值为正数那么SIGN 返回1
如果参数为零那么SIGN 也返回零

SQRT平方根

select charge_dept,charge_user ,CHR(charge_dept), CHR(charge_user) from project_child_list//ASCLL的转换。

select concat(project_no,child_project_name) "aaa aaa"from project_child_list//conact作用和||相同

select initcap(project_no)from project_child_list//initcap把第一个字母大写其余转换成消协

select lower(project_no),upper(project_no)from project_child_list//lower转换成小写，//upper转化成大些

SELECT project_no , LPAD(project_no, 20,'*'),rpad(project_no,20,'*') FROM project_child_list//LPAD左扩展，rpad右扩展

select project_no,RTRIM(project_no,'X'),LTRIM(project_no,'X') from project_child_list//向右剪切和向左剪切删除所填写的字符，遇到其他字符则停止

SELECT project_no,replace(PROJECT_NO,'XX'),replace(project_no,'XX','AA') newno from project_child_list//替换默认为空

select project_no,substr(project_no,1,2),substr(project_no,3,2),substr(project_no,3) from project_child_list//第一个数字位起始位置，第二个是长度

select project_no,TRANSLATE(project_no,'XXHB','AA')FROM PROJECT_CHILD_LIST//这一函数有三个参数目标字符串源字符串和目的字符串在目标字符串与源字符
串中均出现的字符将会被替换成对应的目的字符串的字符

select project_no,insTR(project_no,'X',2,1)FROM PROJECT_CHILD_LIST//从第二个位置搜索第一个满足'X'条件的数据

select child_project_name,length(rtrim(child_project_name)) from project_child_list//返回长度

select total_money,length(to_char(total_money)) from project_origin_list //to_char数字转字符

select submit_dept*submit_dept,to_number(submit_dept)*to_number(submit_dept) from project_origin_list//to_number字符转数字

select GREATEST('11','222','333'), LEAST('11','222','333')  from project_child_list//GREATEST选出最大值，LEAST选出最小值

SELECT * FROM PROJECT_ORIGIN_LIST ORDER BY ATTACH_MODULE DESC,TOTAL_MONEY DESC//ORDER BY 排序

SELECT * FROM PROJECT_ORIGIN_LIST ORDER BY ATTACH_MODULE DESC,TOTAL_MONEY DESC//第八列进行排序

select sum(total_money) from project_origin_list group by attach_module//group by 和汇总函数一起用

select attach_module,total_money, count(total_money),avg(total_money),sum(total_money) from project_origin_list where id is not null  group by attach_module,total_money having avg(total_money)>25
//having的用法和汇总函数一起用

select o.project_name,c.child_project_name from project_origin_list o,project_child_list c where c.parent_project=o.id//等值查询

SELECT O.PROJECT_NAME,C.CHILD_PROJECT_NAME FROM PROJECT_CHILD_LIST C JOIN PROJECT_ORIGIN_LIST O ON O.ID=C.PARENT_PROJECT//内查询

SELECT O.PROJECT_NAME,C.CHILD_PROJECT_NAME FROM PROJECT_CHILD_LIST C right outer JOIN PROJECT_ORIGIN_LIST O ON O.ID=C.PARENT_PROJECT//右连接

select c.id,c1.id from project_child_list c,project_child_list c1//内查询

select * from project_child_list where parent_project=(select id from project_origin_list where id='242')//子查询

select * from project_child_list where parent_project in (select id from project_origin_list )//子查询


select o.project_name,c.child_project_name from project_origin_list o,project_child_list c where c.parent_project=o.id and c.parent_project in(select id from project_origin_list)//子查询

select * from expert_info_list where expert_flag='专家' and ','||specialty||',' like '%,006,%'