数据库sql实用脚本

Mysql

数据表自连接查询

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

UPDATE user_accounts a
    INNER JOIN user_accounts b ON a.id = b.id
SET a.status = 0
WHERE 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 row
begin 
  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 b
ON 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 b
ON 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 b
ON a.drivername=b.driverName GROUP BY a.driverName HAVING COUNT(*)>=1;
-- 查询结果：
-- drivername   drivertel     violationName
-- 戴某某        15502938902   闯红灯
-- 王琪琪        15502938902   不知道名称是啥
-- 蔡某某        13587984934   闯红灯
-- 陆某某        15502938902   ~~~~(>_<)~~~~
-- 韩某某        13298702987   逆行

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

-- 我们通过司机考勤，建立驾驶员和车辆的对应关系，在现实中，车队可能不会每天都进行考勤。
-- 一般情况下驾驶员不会频繁更换车辆，因此，这就要求车队至少在驾驶员更换车辆时进行考勤。
-- 在查询驾驶员和车辆对应关系时，提取某驾驶员的最后一条考勤信息即可。

SELECT vehicleNo, deviceId, driverName, driverId, lineDeptId, signInTime
FROM
  (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) tt
WHERE 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应该是1
SELECT 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

---