sql初级语法 笔记总结

num_field   number(12,2); 
表示num_field是一个整数部分最多10位、小数部分最多2位的变量。 

case.....when 用法（与decode（）作用很像）
select case zsxm_dm
         when '02' then
          '营业税'
          when '09' then
          '印花税'
         else
          '无税种'
       end
  from t_dm_gy_zsxm;

decode()函数使用技巧
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
　该函数的含义如下:

　　IF    条件=值1    THEN

　　RETURN(翻译值1)

　　ELSIF 条件=值2 THEN

　　RETURN(翻译值2)

　　......

　　ELSIF 条件=值n THEN

　　RETURN(翻译值n)

　　ELSE

　　RETURN(缺省值)

　　END IF
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

trunc(pz.xs_rq) 是指只要年月日，不要时分秒
对日期按格式截尾，如:SQL>   select   trunc(sysdate,'mm')   from   dual; 
  
  TRUNC(SYSDATE,'MM') 
  ------------------- 
  2003-1-1

trunc实际上是truncate函数，字面意思是截断，截尾。函数的功能是将数字进行截断。例如   tranc(1234.5678,2)的结果为1234.5600。tranc()并不四舍五入。再举例：   tranc(1234.5678,0)的结果为1234.0000；tranc(1234.5678,-2)的结果为1200.0000。

EXISTS   关键字和   IN   关键字的区别？
exists   是符合后面带的sql语句（select）判断有没有记录，in   表示判断所指定的某一字段名是不是在所给出的值的范围内

exists(select   1   from   Table_B   where   Table_B.XH   =   Table_A.XH) 
  这只是为得到是否存在Table_B.XH   =   Table_A.XH的记录。 
  select   1   也可以是select   0,   select     *,   select   'a'等等
update例子:
update t_dm_gy_yh t set t.xzqhxq_dm='620300' where t.xzqhxq_dm='620301';
修改表结构和注释
-- Add/modify columns
alter table DB_XTWH.T_DM_FP_FPZL add DEFP_BJ CHAR(1);
-- Add comments to the columns
comment on column DB_XTWH.T_DM_FP_FPZL.DEFP_BJ
  is '1为定额发票种类 "0"非定额发票种类';


NVL(eExpression1, eExpression2)
从两个表达式返回一个非 null 值。
如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。

-- union all 的用法 
select  *  from  tab1  where  tab1.bid='19' union  all  select  *  from  tab2    where  tab2.bid='19' 
 
斑竹加的话： 
1、join  产生的是笛卡尔积。 
2、union  会排除重复的记录 
3、union  all  不会排除重复的记录 

union(或union all)两边的结果集的列数必须一致，相同位置的列类型必须一致,但名字不一定一样；
用union all的时候,两张表实际上是合并掉了,组成一张新的表来查询,你这条SQL语句的结果只有两个字段,name和pwd
ql="select name as name100,pwd as pwd100 from class1 union all select namea as name100,pwda as pwd100 from class2"
...
然后读取数据的时候,这样读:
rs("name100")
rs("pwd100")

左外连接
left outer join就是orcal中的（+）

建立dblike

-- Drop existing database link
drop database link SCDB;
-- Create database link
create database link SCDB
  connect to DB_ZGXT identified by xxx
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.0.252)(PORT = 1521))) (CONNECT_DATA =(SERVICE_NAME = CQKF)))';


在Oracle/PLSQL中，lpad函数将左边的字符串填充一些特定的字符，其语法格式如下：  
     lpad( string1, padded_length, [ pad_string ] )
     其中string1是需要粘贴字符的字符串
     padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length;

     pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。
还有rpad()右补齐
常用于左侧补0
    例如：
         
lpad('tech', 7);     将返回' tech'

lpad('tech', 2);     将返回'te'
lpad('tech', 8, '0');     将返回'0000tech'
lpad('tech on the net', 15, 'z');     将返回 'tech on the net'
lpad('tech on the net', 16, 'z');     将返回 'ztech on the net'



不让db link 出现 us.oracle.com
alter database rename global_name to gsdspxby;





回闪操作:(仅限半个小时)

select count(*) from 表名 as of timestamp(to_timestamp('2008-8-1 17:50:00','yyyy-mm-dd hh24:mi:ss'));

dual的用法

Oracle的dual表作用:

1、查看当前用户，可以在 SQL Plus中执行下面语句

select user from dual;

2、用来调用系统函数

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;--获得当前系统时间

select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名

select SYS_CONTEXT('USERENV','language') from dual;--获得当前locale

select dbms_random.random from dual;--获得一个随机数

3、得到序列的下一个值或当前值，用下面语句

select your_sequence.nextval from dual;--获得序列your_sequence的下一个值

select your_sequence.currval from dual;--获得序列your_sequence的当前值

4、可以用做计算器

select 7*9 from dual;



判断一个字段的长度:       lenth(字段名)=长度
select * from t_dm_fp_fpzl w where length(w.mc)<12;


日期在2008-8-1和2008-8-30之间的写法
pz.xg_rq between to_date('2008-8-1','YYYY-MM-DD') and to_date('2008-8-30','YYYY-MM-DD')

对于trunc()函数的使用：
大家都知道trunc(3.1415926)后结果是3，其实trunc()函数还有很多其他好用的功能。

比如trunc()可以应用在date型数据中：
假如：
select sysdate from dual;
结果是 2005-11-02 16:41:53，那么

1、select trunc(sysdate) from dual;
结果是 2005-11-02，也就是说将时分秒截掉了

2、select trunc(sysdate,'yyyy') from dual;
结果是 2005-01-01，即得到的是sysdate这个时间所在的年的第一天

3、select trunc(sysdate,'mm') from dual;
结果是 2005-11-01，即得到的是sysdate这个时间所在的月第一天

4、select trunc(sysdate,'dd') from dual;
结果大家可以自己试验一下，后面还有可以使用的：
select trunc(sysdate,'hh24') from dual;
select trunc(sysdate,'mi') from dual;

比like快的写法  instr

select *
  from t_dm_gy_jyxm z
 where z.zsxm_dm = '04'
   and instr(z.gljg_dm, '25115') > 0;--0.109S
  
   select *
  from t_dm_gy_jyxm z
 where z.zsxm_dm = '04'
 and z.gljg_dm like '25115%';--0.141S

select *
  from t_dm_gy_jyxm z
 where z.zsxm_dm = '04'
   and instr(z.gljg_dm, '25115') = 0;--0.109S 相应于not like

select upper('a') from dual;--大写
select lower('A') from dual;--小写



截位函数

select substr('123456789',7,2) from dual;-----结果为78



根据查询结果插入表。
insert into t_dj_nsrxx
select * from t_dj_nsrxx@gsdscj;



查询集合的问题：
 1.生产环境和测试环境   两个结果集的 （并） 记录   的记录
SELECT               A,B               FROM               AB
UNION  （并）  
SELECT               B,A               FROM               AB@SC12


2.生产环境和测试环境   不存在的记录 （减）  的记录
SELECT               B,A               FROM               AB (大)
MINUS （减）
SELECT               A,B               FROM               AB@SC12 （小）

3.生产环境和测试环境   有结果级相交 （交叉）  的记录
SELECT               B,A               FROM               AB
INTERSECT （相交, 交叉）intersect
SELECT               A,B               FROM               AB@SC12
 
例：

--0.172s
select a.zsxm_dm,a.zspm_dm from t_dm_gy_zspm a
minus
select b.zsxm_dm,b.zspm_dm from t_dm_gy_zspm@sc12 b;

--17.313s
select * from t_dm_gy_zspm a where
not exists(
select 1 from t_dm_gy_zspm@sc12 b
where a.zsxm_dm=b.zsxm_dm
and a.zspm_dm=b.zspm_dm) ;





unique constraint (DB_XTWH.PK_T_XT_XTCS) violated

表的字段已有值。

desc 降序
asc  升序



1.查找表里有%的字段的写法

LIKE '%!%%' ESCAPE '!'；

2.oracle中查出带汉字的记录
--  部分是汉字
  select   * from XXXXXX where  lengthb(x.xxxx)/length(x.xxxx) !=1 and  lengthb(x.xxxx)/length(x.xxxx)!=2;
--  全是汉字  
  select   * from XXXXXX where  lengthb(x.xxxx)/length(x.xxxx) =2;
--  没有汉字
  select   * from XXXXXX where  lengthb(x.xxxx)/length(x.xxxx) =1;

3.查找表TEST中时间最新的前10条记录
select * from (select * from test order by dates desc) where rownum < 11;

4.视图
create or replace view vi_test as
select * from test;

5.同义词写法
-- Create the synonym
create or replace synonym T_FP_FPXHXXMX  ---同义词名称 （和表名一样）
  for DB_FPGL.T_FP_FPXHXXMX;            ----表名

6。查找重复记录
select names,num
from test
where rowid != (select max(rowid)
from test b
where b.names = test.names and
b.num = test.num)
或
--查询表中的哪些记录有重复
select * from test group by id having count(*)>1;
select id from test group by id having count(id)>1;
--删除重复的记录
delete from test a where a.rowid!=(select max(rowid) from test b where a.id=b.id);
7.exists
 
exists是判断是否存在这样的记录，in 主要用于具体的集合操作,有多少满足条件.

EXISTS   返回的是一个布尔值   而IN是用在返回一个结果集的！ 从速度来就前者比较快！ 
也就是说，in和exists需要具体情况具体分析，not in和not exists就不用分析了，尽量用not exists就好了。

select *
 from t_hd_yjszsx a
 where exists (select 1
          from t_dj_nsrxx b
         where b.gljg_dm like '251149%'
           and a.nsrnbm = b.nsrnbm
           and b.dj_zt = '20')
   and a.fpbl_dm not in ('1005')
   and a.zsxm_dm = '04';--用于查询企业所得税分配比例不是1005的纳税人有哪些;


8 replace 替换函数

select replace(w.fpgg,'?á','*'),w.fpgg from y_dm_fp_fp w;
update y_dm_fp_fp s set s.fpgg=replace(s.fpgg,'?á','*');
结果
REPLACE(W.FPGG,'?á','*')
FPGG
175*105
175?¨¢105
175*105
175?¨¢105



多用于去除采集的excel中带空格的问题
create table t_dm_fp_fpzl0826
as select * from t_dm_fp_fpzl;
--修改临时表 t_dm_fp_fpzl0826的字段（变大）
select replace(w.fpzl_dm,' '),w.* from t_dm_fp_fpzl0826 w;
--然后
update t_dm_fp_fpzl0826 s set s.fpzl_dm=replace(s.fpzl_dm,' ');

9.    Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用
sql中形成树
connect by 是结构化查询中用到的，其基本语法是：
select ... from tablename start with 条件1
connect by 条件2
where 条件3;

--从Root往树末梢递归
 select *
   from t_dm_gy_swjg w
  where length(w.swjg_dm) < 10
 connect by prior w.swjg_dm = w.sjswjg_dm
  start with w.swjg_dm = '251050000';
--从末梢往树ROOT递归
select *
  from t_xt_gns t
connect by prior t.sjgn_dm = t.gn_dm
 start with t.gn_dm = '7010';