简单SQL语句举例

‌select t2_je from t2;    最简单的查询

select * into tab2_bak from tab2;  备份一个表

select * from t2 where t2_lsbh in('111','1111') ; 带条件的查询

select * from t2 where t2_lsbh ='111' or t2_lsbh='1111' ; 等同于上个查询

比较符 <>  like in  not  条件逻辑关系  and  or 

select * from t2 where t2_lsbh like'1111%';  like用法 

select * from t3;

Update t3 set t3_dz='888'; 最简单的更新语句

Update t3 set t3_dz=t3_dz+'9999'; 字符串操作

Update t3 set t3_dz=substring(t3_dz,1,2)+'9999';字符串操作和截取字符串

update t2 set t2_je=0;

update t2 set t2_je=t2_sl*t2_dj; 数字运算

select sum(t2_sl) from t2；   求和

select round(sum(t2_sl),2) from t2;  圆整

select count(*) from t1;  行数统计

select * from t3; 查询

delete t3; 删除表数据

delete t3 where t3_bmbh like'01%';  带条件删除

drop table t3; 删除一个表

查询语句举例

select substring(t4_ywrq,1,6) ny,t4_bmbhbmbh,t3_bmmc bmmc,T4_BBJE fkje

from t4,t3

where t4_bmbh=t3_bmbh

and substring(t4_ywrq,1,4)='2009';

group by 分组合计

order by 排序

union all 查询的合并

select   substring(t4_ywrq,1,6),t4_bmbh,t3_bmmc,

sum(T4_BBJE) wzbfk,0 qtfk  from t4,t3

where t4_bmbh=t3_bmbh   and substring(t4_ywrq,1,4)='2009' and t4_bmbh='05'

group by substring(t4_ywrq,1,6),t4_bmbh,t3_bmmc

union all

select substring(t4_ywrq,1,6),t4_bmbh,t3_bmmc,0 wzbfk,sum(T4_BBJE) qtfk

from t4,t3    where t4_bmbh=t3_bmbh

and substring(t4_ywrq,1,4)='2009' and t4_bmbh='0825'

group by substring(t4_ywrq,1,6),t4_bmbh,t3_bmmc;

嵌套查询、分组合计，合并成一个表 

select ny,sum(wzbfk) wzbfk,sum(qtfk) qtafkfrom (select substring(t4_ywrq,1,6) ny,t4_bmbh,t3_bmmc,sum(T4_BBJE) wzbfk,0 qtfk

from t4,t3

where t4_bmbh=t3_bmbh

and substring(t4_ywrq,1,4)='2009' and t4_bmbh='05'

group by substring(t4_ywrq,1,6),t4_bmbh,t3_bmmc

union all

select substring(t4_ywrq,1,6) ny,t4_bmbh,t3_bmmc,0 wzbfk,

sum(T4_BBJE) qtfk

from t4,t3

where t4_bmbh=t3_bmbh

and substring(t4_ywrq,1,4)='2009' and t4_bmbh='0825'

group by substring(t4_ywrq,1,6),t4_bmbh,t3_bmmc)aa

group by aa.ny;

子查询作为in的值

select * from t1 where t1_bmbh not in (

select t3_bmbh from t3 where t3_bmbh like'1%');

CASE 语句

select t1_sjdh,t1_kcywrq,

case when t1_pjlx='N'  then '销售出库单'

when t1_pjlx='O'  then '生产领料单'

when t1_pjlx='Q'  then '其他出库单'

when t1_pjlx='R'  then '金额调整单'

else '盘亏出库单' endpjlx

from t1 where t1_kcywrq>=20090101;

左连接语句示例

select t6_tdbh,t6mx_wlbh ,t7_wlmc,t7_ggxh,t6mx_zsl,t6mx_kpsl,kpsl1

from t6,t7,t6mx left join

(select t8_tdls tdls,t8_tdfl tdfl,sum(t8_zsl)kpsl1 from xsfp,t8

where xsfp_fpls=t8_fpls

and xsfp_ywrq<=20091031

group by t8_tdls,t8_tdfl)aaa on t6mx_tdls=aaa.tdlsand t6mx_tdfl=aaa.tdfl

 

where t6_tdls=t6mx_tdls and t6_ywrq>=20090101and t6_ywrq<=20091031

and t7_wlbh=t6mx_wlbh ;