sql笔记

1.将某一字段中，英文括号改为中文括号：

update 表 set 字段= replace(replace(字段,'(', '（'), ')', '）')

2.将表1与表2做匹配，匹配上的就将表2的一个字段替换到表1的某个字段。

update em_video_camera a 

set camera_remark=(select remark from fhc_test b where a.camera_code=b.camera_code),
      camera_posdes=concat(camera_code,camera_name) 

where camera_code in(select camera_code from fhc_test ) 

and camera_code not in

(

select b.camera_code 

from em_video_camera a,fhc_test b 

where a.camera_code=b.camera_code 

group by b.camera_code 

having  count(*)>1

);

原始语句：update em_video_camera a set camera_remark=(select remark from fhc_test b where a.camera_code=b.camera_code),
camera_posdes=concat(camera_code,camera_name) where camera_code in(select camera_code from fhc_test ) and camera_code not in(
select b.camera_code from em_video_camera a,fhc_test b where a.camera_code=b.camera_code group by b.camera_code having  count(*)>1)

3.left join 

 select a.create_time,a.remark,c.`name` from ncc_investigation_data as a 
LEFT JOIN ncc_measured_point as b on a.point_id=b.id
LEFT JOIN ncc_measured_type as c on b.type_id=c.id
where data_source=2 and create_time='2017/5/19' 

4-1.excel中找到重复的数据，并删除对应的一行数据

方法：选择一列 -->删除重复项 -->扩展选定区域 -->取消全选，选一列 -->确定

4-2.excel中快速对比两列或两组数据的相同项和不同项

=MATCH(A1,B:B,)

5.取出字符串中的数字

①.创建函数，调用函数
create function dbo.F_Get_Number (@S varchar(100))
returns int  www.2cto.com  
AS 
begin
    while PATINDEX('%[^0-9]%',@S)>0
          begin
                set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
          end
    return cast(@S as int)
end

测试：select dbo.F_Get_Number ('chuangjiang2ada33核心价值观adc')

ps：没有试验成功！

②.select substring(所查询字符串,patindex('%[^0-9][0-9]%',所查询字符串)+1,
patindex('%[0-9][^0-9]%',所查询字符串)-patindex('%[^0-9][0-9]%',所查询字符串))

ps:这个只能查询第一次在字符串出现的数字

6.EA反向工程导入方法

7.表，注释，字段，类型

7-1.(取得表的字段，字段类型，注释)

show full COLUMNS from 表名;

7-2.取出数据库中表名及注释

Select table_name 表名,TABLE_COMMENT表注释from

INFORMATION_SCHEMA.TABLES

Where table_schema = 'kmjkioc_theme' ##数据库名

AND table_name LIKE 'ncc_index'##表名;

7-3.取出数据库中表的字段及注释

Select COLUMN_NAME 列名, DATA_TYPE字段类型, COLUMN_COMMENT字段注释

from INFORMATION_SCHEMA.COLUMNS

Where table_name = 'ncc_index'##表名

AND table_schema = 'kmjkioc_theme'##数据库名

AND column_name LIKE 'id'##字段名

8.sql查询表中列、字段重复值

SELECT * FROM T_VALUE A
WHERE
(
A .ENTERPRICE_ID,
A .VALUE_DATE,
A .INDEX_ID,
A ."VALUE"
) IN (
SELECT
ENTERPRICE_ID,
VALUE_DATE,
INDEX_ID,
"VALUE"
FROM
T_VALUE
GROUP BY
ENTERPRICE_ID,
VALUE_DATE,
INDEX_ID,
"VALUE"
HAVING
COUNT (*) > 1
)

9.查询数据格式

数字：    SELECT * FROM 表名 WHERE  字段名 not LIKE '%[^0-9]%';

非数字：SELECT * FROM 表名 WHERE  字段名 LIKE '%[^0-9]%';

10.检验统一社会信用代码

select CASE
         WHEN regexp_like('TYSHXYDM',
                          '[0-9A-HJ-NPQRTUWXY]{2}\d{6}[0-9A-HJ-NPQRTUWXY]{10}') and
              length(TYSHXYDM) = 18 THEN
          '1'
         ELSE
          '0'
       END AS output
  FROM FRJC_JBXX;

11.检验组织机构代码

CREATE OR REPLACE FUNCTION jgid_verify2(organizationCode VARCHAR2)  /*      功能：验证组织机构代码，成功返回1，失败返回0      organizationCode：要验证的组织机构代码      相关资料：      http://baike.baidu.com/view/238601.htm    */   RETURN NUMBER AS    codeSum NUMBER(10) := 0;    code    VARCHAR(100);    code_9  varchar(1);    C9      NUMBER(2);    /*字符与字符的值，每个字符后两位为该字符的字符数值*/    Ci CHAR(250) := '000101202303404505606707808909A10B11C12D13E14F15G16H17I18J19K20L21M22N23O24P25Q26R27S28T29U30V31W32X33Y34Z35';    /*前8位字符的加权因子*/    type v_ar is varray(10) of number;    Wi v_ar := v_ar(3, 7, 9, 10, 5, 8, 4, 2);  BEGIN    /*判断是否为null*/    IF (organizationCode is NULL) THEN      BEGIN        RETURN 0;      END;    END IF;      code := RTRIM(LTRIM(REPLACE(organizationCode, '-', ''))); /*把-,前后空格去掉*/      /*验证长度是否正确*/    /*验证机构代码是由数字和大写字母组成*/    IF (LENGTH(code) != 9 or NOT REGEXP_LIKE(code, '^[A-Z0-9]+$')) THEN      BEGIN        RETURN 0;      END;    END IF;      /*前8位字符的字符数值分别乘于该位的加权因子，然后求和*/    for i in 1 .. Wi.count loop      codeSum := codeSum +                 to_Number(substr(Ci, INSTR(Ci, substr(code, i, 1)) + 1, 2)) *                 Wi(i);    end loop;      /* 计算校验码C9*/    C9     := 11 - (codeSum MOD 11);    code_9 := substr(code, 9, 1);      /*验证校验码C9*/    /*当C9的值为10时，校验码应是拉丁字母X */    /*当C9的值为11时校验码应是0*/    /*验证第9位是否等于计算出的校验结果*/    IF ((C9 = 10 and code_9 = 'X') or (C9 = 11 and code_9 = '0') or       (code_9 = to_char(C9))) THEN      BEGIN        return 1;      END;    END IF;      RETURN 0;  EXCEPTION    WHEN OTHERS THEN      raise;  END;

12.多表关联

select a.dept,a.id,a.name,a.job,c.org_struct_name from pbp_cpc_info aRIGHT join pbp_party_info bon a.dept = b.dept_idRIGHT join pbp_party_org con  a.dept=c.party_org_idand a.job in (505,506) ORDER BY a.dept,a.job;

13.简单查询:

例子1：

①

select * from pbp_assess_index where `year`=2016 and pcode in ('A000','A001','A002','B000','B001','B002','C000','C001','C002','D000','D001','D002','E000','E001','E002','G000','G001','G002')ORDER BY PCODE,ID ASC;

②

错误写法：

DELETE from pbp_cpc_info a where (name in (select name from pbp_cpc_info as b where a.id <> b.idand a.job=b.job and a.dept=b.deptand a.birthday=b.birthdayand a.dept in (053112304000,053112304066,053112304067,053112304065)and trade))

正确写法：

建表，删除数据，删除临时表，验证。

create table tmp as (select *  from pbp_cpc_info as a  where (name in (select name from pbp_cpc_info as b where a.id <> b.idand a.job=b.job and a.dept=b.deptand a.birthday=b.birthdayand a.dept in (053112304000,053112304066,053112304067,053112304065)and trade)) order by name,trade);

DELETE from pbp_cpc_info where id in (SELECT id from tmp);

drop TABLE tmp;select *  from pbp_cpc_info as a  where (name in (select name from pbp_cpc_info as b where a.id <> b.idand a.job=b.job and a.dept=b.deptand a.birthday=b.birthdayand a.dept in (053112304000,053112304066,053112304067,053112304065)and trade)) order by name,trade;

例子2：

create table tmp_1 as( select * from pbp_party_org where party_org_id in((select party_org_id from pbp_party_org where father_id='053112302000')union (select party_org_id from pbp_party_org where father_id in (select party_org_id from pbp_party_org where father_id='053112302000'))union (select party_org_id from pbp_party_org where id='8571ca8c-818d-11e7-bb31-be2e44b06b34')));

select *  from pbp_cpc_info as a  where (name in (select name from pbp_cpc_info as b   where a.id <> b.id  and a.job=b.job   and a.dept=b.dept  and a.birthday=b.birthday  and a.dept in (select party_org_id from tmp_1)  and trade)) order by name,trade,cp_cost_level; 

create table tmp_2 as (select *  from pbp_cpc_info as a where name in (select name from pbp_cpc_info as b   where a.id <> b.id  and a.job=b.job   and a.dept=b.dept  and a.birthday=b.birthday  and a.dept in (select party_org_id from tmp_1)) and cp_cost_level is null order by name,trade,cp_cost_level); 

DELETE from pbp_cpc_info where id in (SELECT id from tmp_2);

14.mysql中创建视图，表中使用union报错，解决办法。

加了括号，报错：

create table tmp_1 as((select * from pbp_party_org where father_id='053112302000')union (select * from pbp_party_org where father_id in (select party_org_id from pbp_party_org where father_id='053112302000'))union (select * from pbp_party_org where id='8571ca8c-818d-11e7-bb31-be2e44b06b34'));

去掉括号：

create table tmp_1 as(select * from pbp_party_org where father_id='053112302000')union (select * from pbp_party_org where father_id in (select party_org_id from pbp_party_org where father_id='053112302000'))union (select * from pbp_party_org where id='8571ca8c-818d-11e7-bb31-be2e44b06b34');

15,.excel中vlookup函数，如果遇到重复项，只会匹配最先遇到的那个，例如：

安永1安永1毕世彩2毕世彩2蔡思文3蔡思文3曹伟4曹伟4陈奎廖5陈奎廖5陈启良6陈启良6楚禹斌7楚禹斌7陈奎廖5陈奎廖8陈启良6陈启良9

16.excel中归类重复项，解决办法：

先“高亮重复项”，然后进行“筛选”

17.mysql删除某一列数据中的tab键符、空格

update pbp_cpc_info set name=replace(name,'　','');

update pbp_cpc_info set name=replace(name,' ','');

18.简单查询
SELECT COUNT(party_org_id) a,party_org_id  from pbp_party_org GROUP BY party_org_id ORDER BY party_org_id,a desc

select * from pbp_party_org
where party_org_id in (053112304121,053112304003
)

19.excel中查找上下两行完全相同的数据

=IF(AND(D20=D21,E20=E21,F20=F21),"重","")

例子：

20.乱七八糟的触发器报错：

ORA-04098: trigger 'KMJK.TRIG_LT_INSERT_copy' is invalid and failed re-validation

declare  num int;  ent_id varchar2(64);BEGIN  select count(*) into num from KMJK.T_ENTERPRICE where enterprice_id = :new.ENTERPRICE_ID;  if(num=1) then    KMJK.P_SDE_ADD_ENT(:NEW.ENTERPRICE_ID);  end if;

21.晚上对象给上了一课，关于join的：

left join 等的用法，可以在网上百度下，比如说这个图解join：https://coolshell.cn/articles/3463.html

其中圆饼只能是帮助理解，我就混淆了，一开始脑子里理解的是，查出的数据就是个左表全部数据或右表全部数据（傻子！）

现在知道了，原来查出的数据是以左表为主，右表中不包含左表相同这个字段（on后面接的，例如a.id=b.id）的以null代替，包含相同这个字段的数据的，后续的字段数据也查出来，O(∩_∩)O，说的好不如画的好，详看上面网址。这就是join的强大所在，在hive也好，oracle中也好，多用left join，right join，会节省很多的运行资源，提高执行效率，

22.oracle中sql优化，ps：好的博客就应该分享出来

原文地址：点击打开链接

笔记：

1.is null 与 is not null 使用时不会调用索引

2.通配符 % 放在后

3.不等于用 ‘  <>  ’

4.order by后面跟字段，尽量此字段为索引字段

5.选最有效率的表名顺序:两表小表先，三表在中间

6.尽量不用：‘  *   ’

7.减少访问数据库的次数，把分散的sql整合成一条

8.truncate在删除全表时比较快，不像delete时需要commit

9.多使用commit来释放资源

10.避免使用having操作，这个操作只会在检索出所有记录后才会对结果集进行过滤，这个处理需要排序，总结等操作。on、where、having从左至右依次执行，on是先把不符合条件的记录过滤后再执行统计，where是过滤数据后再执行sum。

11.减少对表的查询，换句话说：多用left join，right join 等

12.经常使用内置函数：

参考博客：点击打开链接

13.别名

14.索引

15.巧妙使用exists替换distinct或in（待研究······）

16.避免在索引上使用计算

例如：

低效：SELECT … FROM DEPT WHERE SAL * 12 > 25000; 

高效：SELECT … FROM DEPT WHERE SAL > 25000/12; 

17.用>=代替>

18.union替换or（适用于索引列）

正常的无索引的列，用or来的效率更高一些

19.优化group by ，将一些数据提前过滤掉。

23.查看权限，赋予权限

查看当前用户的权限：select * from user_sys_privs; 

查看当前用户被授予的角色： select * from user_role_privs;

查看所有用户：select * from dba_users;
24.行列转换

select a.qhmc as '社区', sum(IF(a.XB=2,a.sl,0)) as '男', sum(IF(a.XB=1,a.sl,0)) as '女' from (select qhmc,xb,count(1) sl from rk_jc_rkxx GROUP BY qhmc,xb) agroup by a.qhmc  

25.linux获取当前年月日时分秒

perl -e 

"print sprintf '%04d-%02d-%02d %02d:%02d:%02d',

(localtime(time()+3300))[5]+1900,

(localtime(time()+3300))[4]+1,

(localtime(time()+3300))[3],

(localtime(time()+3300))[2],

(localtime(time()+3300))[1],

(localtime(time()+3300))[0]"

26.left join 后,左表怎样合并或者去掉重复记录

原文地址：http://blog.csdn.net/txqd1989/article/details/53760860

在使用left join 后会发现如果右表数量有重复的话，查询出来的数量大于左表的数量，

如 表一 test1

   IDNAMESEXAGE

1111

2221

3331

表二 test2

IDNAMESEXKJXYH111kjxy-物采中心-20130001211kjxy-物采中心-20130003311kjxy-物采中心-20130002

 

普通的left join

Select  t1.* ,test2.kjxyh From test1 t1 Left Join test2 On t1.Name = test2.Name And  t1.sex =test2.sex

查询出来的结果：

IDNAMESEXAGEKJXYH1111kjxy-物采中心-201300011111kjxy-物采中心-201300031111kjxy-物采中心-201300022221
3331

查询结果为五条数据，不是我们想要的结果，下面看看如果我用这种方式查询的结果

select test1.*,t2.kjxyh from test1 left join(
Select * From
(
 select a.*,row_number() over(partition by Name,sex order by id) r
 from test2 a
 ) where r = 1 ) t2
 on  test1.Name = t2.Name And  test1.sex = t2.sex
 

IDNAMESEXAGEKJXYH1111kjxy-物采中心-201300012221
333

1

会发现数据如只有三条了。按照排序获取到了三条，以左表为基表，然后从test2中按照指定的排序获取到了test2中的kjxyh，但是有时候需要展示所有的kjxyh该怎么办呢，看看下面这条sql语句：

 select test1.*,b.kj from test1, 
 ( select test1.id,wm_concat(test2.kjxyh) kj from test1
 left join test2 on test1.name=test2.name and test1.sex=
 test2.sex group by test1.name,test1.sex,test1.id) b
 where test1.id in(b.id)
查询结果：

IDNAMESEXAGEKJ1111kjxy-物采中心-20130001,kjxy-物采中心-20130003,kjxy-物采中心-201300022221
333127.三个数据库中两表关联进行更新

mysql：update yz4202_jmjbxxb a,rk_ly_jbxx b set a.xl=b.xl  where a.gmsfhm=b.gmsfzh;ms sql sever：update yz4202_jmjbxxb a set gmsfhm = b.gmsfzh FROM a,rk_ly_jbxx b where a.gmsfhm=b.gmsfzh;oracle：update yz4202_jmjbxxb a set (gmsfhm) = (SELECT gmsfzh from rk_ly_jbxx b where a.gmsfhm=b.gmsfzh);