随笔分类 -  一些sql语句

mysql varchar类型转换int类型找出最大值
摘要：(1) 不严谨的，最简单的 select MAX(字段名 + 0) from 表名; (2) 使用函数实现 select MAX(cast(字段名 as SIGNED INTEGER)) from 表名; 或者 select MAX(cast(字段名 as UNSIGNED INTEGER)) from 表名; 阅读全文
posted @ 2019-03-01 16:11 苍洱 阅读(348) 评论(0) 推荐(0)
GROUP_CONCAT 拼接顺序
摘要：用 group_concat 拼接后的顺序 group_concat(id order by id) 里面id更新需要更换 阅读全文
posted @ 2018-07-13 13:30 苍洱 阅读(3461) 评论(0) 推荐(1)
获取当前表自增的最大值
摘要：SHOW TABLE STATUS where name='tablename' 阅读全文
posted @ 2018-04-16 15:32 苍洱 阅读(306) 评论(0) 推荐(0)
mysql 下数据库升级脚本的编写
摘要：1 升级时必须得存储过程 1 /**/ 2 drop procedure if exists pro_upgrade; 3 DELIMITER // 4 CREATE DEFINER=`root`@`%` PROCEDURE `pro_upgrade`( 5 exec_boolen int , 6 阅读全文
posted @ 2017-11-24 13:49 苍洱 阅读(1266) 评论(0) 推荐(0)
几种常见数据库查询判断表和字段是否存在sql
摘要：1、MSSQL Server 表：select COUNT(*) from dbo.sysobjectsWHEREname= 'table_name'； 字段：select COUNT(*) fromsyscolumnsWHEREid=object_id(‘table_name’) andname= 阅读全文
posted @ 2017-11-14 13:31 苍洱 阅读(8056) 评论(1) 推荐(0)
分段统计数据
摘要：select fund_account,BrokerID, sum(case when RulesID>1000000 AND RulesID<2000000 then 1 else 0 end ), sum(case when RulesID>2000000 AND RulesID<3000000 阅读全文
posted @ 2017-11-13 16:23 苍洱 阅读(166) 评论(0) 推荐(0)
create-trigger-insert-pwd
摘要：delimiter | drop trigger if exists default_insert_Pwd; create trigger default_insert_Pwd before insert on traceroleid for each row if (new.Pwd is null or new.Pwd='' or new.Pwd='123456' )... 阅读全文
posted @ 2017-06-13 17:00 苍洱 阅读(133) 评论(0) 推荐(0)
join-case
摘要：SELECT trd.* ,(CASE WHEN ta.AccountID IS null THEN '' ELSE ta.AccountID END ) FROM traceroleid trd LEFT JOIN traceaccountmap ta on (CASE trd.Role WHEN 1 then ta.InvestConsultantID when 2 THEN ta... 阅读全文
posted @ 2017-06-13 16:59 苍洱 阅读(137) 评论(0) 推荐(0)
pro_select_roleinfo_p3
摘要：DELIMITER | drop procedure if exists pro_select_roleinfo_p3; CREATE PROCEDURE pro_select_roleinfo_p3 ( croleid VARCHAR(50), noffset INT, nrows INT ) BEGIN DECLARE linrows BIGINT ; SET... 阅读全文
posted @ 2017-06-13 16:58 苍洱 阅读(254) 评论(0) 推荐(0)
pro-select-limit-if
摘要：drop procedure if exists p9; CREATE PROCEDURE p9 () BEGIN DECLARE a INT; DECLARE b INT; DECLARE c INT; SET a = 5; SET b = 5; SET c = IF(b>0,b,18446744073709551615); SELECT c; SELEC... 阅读全文
posted @ 2017-06-13 16:57 苍洱 阅读(137) 评论(0) 推荐(0)
pro_update_role_pwd
摘要：DELIMITER | drop procedure if exists pro_update_role_pwd; CREATE PROCEDURE pro_update_role_pwd ( croleid VARCHAR(50), crolepwd VARCHAR(50) ) BEGIN #update traceroleid set Pwd =to_base64(C... 阅读全文
posted @ 2017-06-13 16:57 苍洱 阅读(128) 评论(0) 推荐(0)
to_base64 --- from_base64
摘要：UPDATE traceroleid_copy SET Pwd=to_base64(Pwd) SELECT from_base64(Pwd) FROM traceroleid_copy 阅读全文
posted @ 2017-06-13 16:56 苍洱 阅读(584) 评论(0) 推荐(0)
decode-encode --其他使用可能有问题
摘要：SELECT id,DECODE(name,'password') FROM test UPDATE test SET `name`=ENCODE(`name`,'password') 阅读全文
posted @ 2017-06-13 16:52 苍洱 阅读(141) 评论(0) 推荐(0)
test-event-create
摘要：# 1 创建存储过程 /* delimiter // create procedure test() begin update test SET name = date_format(now(),'%Y-%c-%d %h:%i:%s'); end; */ # 2 创建事件 调用存储过程 /* create event if not exists e_test on schedule ever... 阅读全文
posted @ 2017-06-13 16:50 苍洱 阅读(176) 评论(0) 推荐(0)
pro-select-like
摘要：1 DELIMITER | 2 drop procedure if exists pro_query; 3 CREATE PROCEDURE pro_query 4 ( 5 cname VARCHAR(50) 6 ) 7 BEGIN 8 9 #declare @sql varchar(1000) 10 11 SELECT * ,cname FROM... 阅读全文
posted @ 2017-06-13 16:49 苍洱 阅读(165) 评论(0) 推荐(0)