数据库sql实用脚本

Mysql

数据表自连接查询

FROM子句中无法指定目标表更新

UPDATE user_accounts a    INNER JOIN user_accounts b ON a.id = b.idSET a.status = 0WHERE b.update_at <= DATE_ADD(NOW(), INTERVAL -2 DAY)

触发器before执行

Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger

DELIMITER $create trigger tri_check_index before update on message                   -- 在更新之前(before)触发for each rowbegin   if(old.status != 1 && new.status = 1) then    set @cur_check_index = (select max(check_index) from message) + 1;    set new.check_index = @cur_check_index;                               -- 设置新(new)数据的值  end if;end;$

创建或更新

匹配主键或唯一约束键，如果存在则执行Update，否则Insert

INSERT INTO `Table_name` (`id`, `type`, `create_at`, `update_at`)VALUES (3, 2, '2017-05-18 11:06:17', '2017-05-18 11:06:17')ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `type` = VALUES(`type`), `update_at` = VALUES(`update_at`);

---

SqlServer

选择合适的IGNORE_DUP_KEY选项

当你创建唯一索引时，你可以指定IGNORE_DUP_KEY选项，因此本文最开始创建唯一索引的选项可以是:

CREATE UNIQUE NONCLUSTERED INDEX AK_Product_Name ON Production.Product ( [Name] ) WITH ( IGNORE_DUP_KEY = OFF ); 

IGNORE_DUP_KEY这个名字容易让人误会。唯一索引存在时重复的值永远不会被忽略。更准确的说，唯一索引中永远不允许存在重复键。这个选项的作用仅仅是在多列插入时有用。

比如，你有两个表，表A和表B，有着完全相同的结构。你可能提交如下语句给SQL Server。

INSERT INTO TableA SELECT * FROM TableB;

SQL Server会尝试将所有表B中的数据插入表A。但如果因为唯一索引拒绝表B中含有和表A相同的数据插入A怎么办？你是希望仅仅重复数据插入不成功，还是整个INSERT语句不成功？

这个取决于你设定的IGNORE_DUP_KEY参数，当你创建唯一索引时，通过设置设个参数可以设定当插入不成功时怎么办，设置IGNORE_DUP_KEY的两种参数解释如下:

IGNORE_DUP_KEY=OFF

整个INSERT语句都不会成功并弹出错误提示，这也是默认设置。

IGNORE_DUP_KEY=OFF

只有那些具有重复键的行不成功，其它所有的行会成功。并弹出警告信息。

IGNORE_DUP_KEY 选项仅仅影响插入语句。而不会被UPDATE,CREATE INDEX,ALTER INDEX所影响。这个选项也可以在设置主键和唯一约束时进行设置。

---

Oracle

HAVING过滤器使用COUNT导致查询结果错误

-- 有驾驶员基本信息表和驾驶员违章记录表，想要查询违章驾驶员的手机号码。-- 为了便于说明问题，仅列出表中的关键字段。-- drivername   drivertel-- 蔡某某       13xxxxxxxx-- 陈某某       13xxxxxxxx-- 戴某某       15xxxxxxxx-- 邓某某       (NULL)-- 韩某某        13xxxxxxxx-- 革某某         (NULL)-- 王琪琪       15xxxxxxxx-- 陆某某        15xxxxxxxx-- 小埋         18xxxxxxxx-- lm某某        18xxxxxxxx-- driverName   violationName-- 蔡某某       闯红灯-- 蔡某某       超速-- 戴某某       闯红灯-- 蔡某某       闯红灯-- 蔡某某       闯红灯-- 王琪琪       不知道名称是啥-- 韩某某       逆行-- 陆某某       ~~~~(>_<)~~~~-- 最初的写法：SELECT a.drivername, a.drivertel, b.violationName FROM t_driverinfo a LEFT JOIN t_vehicleviolationrecord bON a.drivername=b.driverName GROUP BY a.drivername HAVING COUNT(*)>=1;-- 查询结果：-- drivername   drivertel     violationName-- lm某某        18129382910   (NULL)-- 小埋          18706073384   (NULL)-- 戴某某        15502938902   闯红灯-- 王琪琪        15502938902   不知道名称是啥-- 蔡某某        13587984934   闯红灯-- 邓某某        (NULL)        (NULL)-- 陆某某        15502938902   ~~~~(>_<)~~~~-- 陈某某        13598898998   (NULL)-- 革某某        (NULL)        (NULL)-- 韩某某        13298702987   逆行-- 出现错误的原因：在这个分组中不能使用COUNT(*)或COUNT(1)，因为这会把通过OUTER JOIN添加的行统计入内（非保留表的空值数据），-- 导致最终查询结果与预期结果不同。-- 改进写法：SELECT a.drivername, a.drivertel, b.violationName FROM t_driverinfo a LEFT JOIN t_vehicleviolationrecord bON a.drivername=b.driverName GROUP BY a.driverName HAVING COUNT(violationName)>=1;-- 或SELECT a.drivername, a.drivertel, b.violationName FROM t_driverinfo a INNER JOIN t_vehicleviolationrecord bON a.drivername=b.driverName GROUP BY a.driverName HAVING COUNT(*)>=1;-- 查询结果：-- drivername   drivertel     violationName-- 戴某某        15502938902   闯红灯-- 王琪琪        15502938902   不知道名称是啥-- 蔡某某        13587984934   闯红灯-- 陆某某        15502938902   ~~~~(>_<)~~~~-- 韩某某        13298702987   逆行

-- 问题的引出：C语言中，NULL==NULL的比较返回的是1SELECT 1=NULL;-- 结果为NULL，而不是0SELECT NULL=NULL;-- 结果为NULL，而不是1-- 总结：对于比较返回值为NULL的情况，可将其视为UNKOWN，即表示未知的。

-- 我们通过司机考勤，建立驾驶员和车辆的对应关系，在现实中，车队可能不会每天都进行考勤。-- 一般情况下驾驶员不会频繁更换车辆，因此，这就要求车队至少在驾驶员更换车辆时进行考勤。-- 在查询驾驶员和车辆对应关系时，提取某驾驶员的最后一条考勤信息即可。SELECT vehicleNo, deviceId, driverName, driverId, lineDeptId, signInTimeFROM  (SELECT    t1.vehicleNo,    t1.deviceId,    t1.driverName,    t1.driverId,    t1.lineDeptId,    t1.signInTime,    IF(@vno <> t1.vehicleNo, @r := 1, @r := @r + 1) AS r,    @vno := t1.vehicleNo  FROM    (SELECT * FROM driver_attendance ORDER BY vehicleNo, signInTime DESC) t1    INNER JOIN    (SELECT @r := 0, @vno := '') t2 -- 初始化用户变量    ON 1 = 1) ttWHERE r = 1 ;

-- 创建表并插入数据create table t(a int primary key);insert into t values(1);insert into t values(2);insert into t values(3);insert into t values(100);insert into t values(101);insert into t values(103);insert into t values(104);insert into t values(105);-- 问题一：求数据的连续范围-- 1、给数据增加行号SET @a:=0;SELECT a, @a:=@a+1 AS rn FROM t;-- 或SELECT a, @a:=@a+1 rn FROM t, (SELECT @a:=0) AS b;-- 或SELECT a, @a:=@a+1 rn FROM t, (SELECT @a:=0) AS a;-- 2、求数据与行号的差值SELECT a, rn, a-rn AS diff FROM(    SELECT a, @a:=@a+1 rn FROM t, (SELECT @a:=0) AS a) AS b;-- 3、分组统计得到结果SELECT MIN(a) start_range, MAX(a) end_range FROM(    SELECT a, rn, a-rn AS diff FROM(        SELECT a, @a:=@a+1 rn FROM t, (SELECT @a:=0) AS a    ) AS b) AS c GROUP BY diff;-- 问题二：求数据的间断范围-- 核心思路：构造自定义变量@a，使得如果数据连续，每行的值减去@a应该是1SELECT rn+1 start_range, a-1 end_range FROM(    SELECT a, @a rn, @a:=a FROM t, (        SELECT @a:=MIN(a)-1 FROM t    ) AS b) AS c WHERE a-rn <> 1;-- 在项目中的应用：求驾驶员最长连续签到的时间段SELECT * FROM(    SELECT e.*, @npre:=@ncur, @ncur:=e.driverId, IF(@npre=@ncur, @nrn:=@nrn+1, @nrn:=1) AS nrn FROM(        SELECT driverId, MIN(signInTime), MAX(signInTime), COUNT(*) FROM(            SELECT b.*, @pre:=@cur, @cur:=driverId, IF(@pre=@cur, @rn:=@rn+1, @rn:=1) AS rank, diff-@rn AS flag            -- rank是列的别名，不能直接引用。而@表示变量，可以引用，但是需要给它赋值。            -- “AS rank, diff-@rank”，这里rank和@rank的类型不同。            FROM(                SELECT DISTINCT driverId, DATE_FORMAT(signInTime, '%Y-%m-%d') signInTime, DATEDIFF(signInTime, NOW()) diff                FROM driver_attendance ORDER BY driverId, signInTime            ) AS b, (SELECT @pre:=1, @cur:=1, @rn:=1) AS c        ) AS d GROUP BY driverId, flag ORDER BY driverId, COUNT(*) DESC    ) AS e, (SELECT @npre:=1, @ncur:=1, @nrn:=1) AS f)AS g WHERE nrn=1

---