SQL整理

1.要求：复制表T_User中的一条Id='1'的所有数据

（1）前提：表中没有主键

语句：insert into T_User select * from T_User where Id='1'或

insert into T_User（Id,Name,Sex） select  from Id,Name,Sex from T_User where Id='1'

结果：表中存在两条Id=‘1’的相同数据；

（2）前提：Id是主键

语句：(.net 链接SqlServer 时设置SqlParameter参数)insert into T_User select @Id,Name,Sex from T_User where Id='1'，@Id传适当的参数

结果：表中存在两条除Id不同，其它列都相同的数据；

2.要求：复制表T_User中的一条Id='1'的部分数据

语句：insert into T_User select Id,Name,'1' from T_User where Id='1'

3.要求:复制表T_Course中部分信息到T_User表中

语句：insert into T_User(Id,Name,Sex) select @Id,Name,Sex from T_Course where XXXX

4.查询表中重复记录：

select trim(cbbh)  from Z_DTYW_CBJBXX group by trim(cbbh) having count(trim(cbbh)) >1)：获得唯一的重复记录。

SELECT cbbh from Z_DTYW_CBJBXX where trim(cbbh) in( select trim(cbbh)  from Z_DTYW_CBJBXX group by trim(cbbh) having count(trim(cbbh)) >= 2)  ORDER BY cbbh：获得所有重复的记录。

5.Oracle递归查询：

（1）向下递归

select JGDM from Z_DTXT_JGCS start with 'id'  connect by prior id=paretnId：获得id以及所有属于id后代的记录

(2)向上递归

select JGDM from Z_DTXT_JGCS start with 'id'  connect by prior paretnId=id：获得id以及所有id前辈的记录

6.Oracle获得最新数据排序后，取前面几条数据：

SELECT * FROM (SELECT *  FROM T_PUBLISH  ORDER BY PUBLISHTIME DESC) WHERE ROWNUM <=4

7.A表取得所有数据，B表取字段B.sbxh=A.sbxh的记录

SELECT
WEIFANG.T_SYHWBZ.SBXH,
WEIFANG.T_SYHWBZ.ZWCM,
WEIFANG.T_GOODSDECLAREAUDITRESULT.SHYJ
FROM
WEIFANG.T_SYHWBZ LEFT JOIN （left join作用:获得左表所有数据，获得右表符合条件的数据。。。还有right join等）
WEIFANG.T_GOODSDECLAREAUDITRESULT
ON WEIFANG.T_SYHWBZ.SBXH=WEIFANG.T_GOODSDECLAREAUDITRESULT.SBXH

8.获取几张表中共同的记录

select "SBXH","ZWCM","YWCM","HC","SBRQ","SBDLB" ,"LZBZ","SBDWMC","SHDWMC","SBYBH","SBDWBH","TJBZ","BWDM","SHYJ"
from (
select "SBXH","ZWCM","YWCM","HC","SBRQ","SBDLB","LZBZ","SBDWMC","SHDWMC","SBYBH","SBDWBH","TJBZ","BWDM","SHYJ" from T_SYGTVIEW
UNION
select "SBXH","ZWCM","YWCM","HC","SBRQ","SBDLB","LZBZ","SBDWMC","SHDWMC","SBYBH","SBDWBH","TJBZ","BWDM","SHYJ" from T_SYHWBZVIEW
)

 create index index_name on table_name(列名，列名，列名)||（函数（列名））||（）
三、使用索引的原则

使用原则：

1、在大表上建立索引才有意义。

2、在where子句或是连接条件上经常饮用的列上建立索引。

3、索引的层次不要超过4层。

四、索引缺点分析

索引有一些先天不足：

1、建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。

2、更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，一维持数据和索引的一致性。

实践表明，不恰当的索引不但于事无补，反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。

比如在如下字段建立索引应该是不恰当的：

1、很少或从不引用的字段。

2、逻辑型的字段，如男或女（是或否）等。


*********************************存储过程***************************************
//插入
create or replace procedure addUser(xm_in varchar2,tel_in varchar2)
as
begin
 insert into TUSER(XM,TEL)values(xm_in,tel_in);
 commit;
end;
//删除
create or replace procedure deleteUser(xm_in varchar2)
as
begin
 delete from TUSER where xm=xm_in;
 commit;
end;
//带返回值的
create or replace procedure selectUser(xm_out out TUSER%rowtype)
as
cursor c is select xm,TEL from TUSER;
begin
for x in c loop
 xm_out:=x;
 dbms_output.put_line(xm_out.xm||';;'||xm_out.tel);
end loop;
end;
//带有返回值的
create or replace package procpkg is
   type refcursor is ref cursor;
   procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);
end procpkg;

create or replace package body procpkg is
  procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)
  is
  begin
  open p_ref_postypeList for select * from TUSER where xm like '%'||p||'%';
  end;
end procpkg;
//删除存储过程
drop procedure proname;
**********************************游标************************************************
oracle 游标有4个属性：%ISOPEN，%FOUND，%NOTFOUND，%ROWCOUNT。
create or replace procedure cursorE(outString out varchar2)
as
--For 循环游标
--（1）定义游标
--（2）定义游标变量
--（3）使用for循环来使用这个游标

       --类型定义
       cursor c_job
       is
       select xm,tel
       from TUSER;
       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_job%rowtype;
begin
       for c_row in c_job loop
       outString:=c_row.tel;
         dbms_output.put_line(c_row.xm||'-'||c_row.tel);
       end loop;
end;
//
create or replace procedure cursorEE(outString out varchar2)
as
--Fetch循环游标
       --类型定义
       cursor c_job
       is
       select xm,tel
       from TUSER;
       --定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
       c_row c_job%rowtype;
begin
  open c_job;
     loop
       fetch c_job into c_row;
       exit when c_job%notfound;
            dbms_output.put_line(c_row.xm||'-'||c_row.tel);
      end loop;
  close c_job;
end;
*************************************常用函数*************************************************
lower(char)：将字符串转化为小写的格式。
upper(char)：将字符串转化为大写的格式。
length(char)：返回字符串的长度。
substr(char, m, n)：截取字符串的子串，n代表取n个字符的意思，不是代表取到第n个
replace(char1, search_string, replace_string)
instr(C1,C2,I,J) -->判断某字符或字符串是否存在，存在返回出现的位置的索引，否则返回小于1;在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1

round(n,[m]) 该函数用于执行四舍五入，
如果省掉m，则四舍五入到整数。
如果m是正数，则四舍五入到小数点的m位后。
如果m是负数，则四舍五入到小数点的m位前。

trunc(n,[m]) 该函数用于截取数字。
如果省掉m，就截去小数部分，
如果m是正数就截取到小数点的m位后，
如果m是负数，则截取到小数点的前m位。

mod(m,n)取余函数

floor(n) 返回小于或是等于n的最大整数
ceil(n) 返回大于或是等于n的最小整数
eg、SELECT ceil(24.56) from dual; --返回25
SELECT floor(24.56) from dual; --返回24
abs(n) 返回数字n的绝对值

(1)sysdate 返回系统时间
eg、SQL> select sysdate from dual;
(2)oracle add_months函数
oracle add_months(time,months)函数可以得到某一时间之前或之后n个月的时间
eg、select add_months(sysdate,-6) from dual; --该查询的结果是当前时间半年前的时间
select add_months(sysdate,6) from dual; --该查询的结果是当前时间半年后的时间
(3)last_day(d)：返回指定日期所在月份的最后一天

   1.user:
    返回登录的用户名称
    select user from dual;

   2.vsize:
    返回表达式所需的字节数
    select vsize('HELLO') from dual;

   3.nvl(ex1,ex2): 　
    ex1值为空则返回ex2,否则返回该值本身ex1（常用）
    例：如果雇员没有佣金，将显示0，否则显示佣金
    select comm,nvl(comm,0) from emp;
*************************************函数*************************************************
1.decode(表达式,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)，如果表达式=值1，则返回‘值1’，‘值1’由自己定
2.select rank() over(patition by field1 order by field2) rank from table ：按字段field2 升序、field1字段分组，定等级
   rank() OVER对有相同的值，标记相同的值。row_number() 则不会。


/*****************************************高效查******************************************************/
SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM Z_DTYW_CBJBXX WHERE ROWNUM <= 40) a) WHERE rn >= 21