数据库

首页数据库
16
Aug
0

上一行数据减去下一行数据

Oracle

--1.创建测试表
create table tmp as
select 1 id, 20 percent from dual union all
select 1 id, 30 percent from dual union all
select 1 id, 50 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual;
--2.SQL实现
select id,100-sum(percent)over(partition by id order by rownum) percent
from tmp;

SQL SERVER

select b.相减字段-a.相减字段 from(select from (select Row_Number() over ( order by 排序字段 ) as RN , from Table Name) where rn%2=0) a
inner join
(select from (select Row_Number() over ( order by 排序字段 ) as RN , from Table Name) where rn%2<>0) b on a.rn=b.rn-1

mysql

select * from
(select (@i:=@i+1) as no,id from job_log ,(select @i:=0)t where job_id =131) as t1
left join
(select (@k:=@k+1) as no,id from job_log ,(select @k:=0)t where job_id =131) as t2
on (t1.no = t2.no-1)
order by t1.no

16
Jul
0

mysql 触发器里使用游标

--存储过程
CREATE DEFINER = 'root'@'10.200.%.%'
PROCEDURE geely-itil-pc-test.StatisticStore()
BEGIN
-- 创建接收游标数据的变量
DECLARE c int;
DECLARE n varchar(20);
-- 创建总数变量
DECLARE total int DEFAULT 0;
-- 创建结束标志变量
DECLARE done int DEFAULT FALSE;
-- 创建游标
DECLARE cur CURSOR FOR
SELECT

id

FROM message_option;
-- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 设置初始值
SET total = 0;
-- 打开游标
OPEN cur;
-- 开始循环游标里的数据
read_loop:
LOOP

-- 根据游标当前指向的一条数据
FETCH cur INTO c;
-- 判断游标的循环是否结束
IF done THEN
  LEAVE read_loop;    -- 跳出游标循环
END IF;
-- 获取一条数据时,将count值进行累加操作,这里可以做任意你想做的操作,
SET total = total + c;

-- 结束游标循环
END LOOP;
-- 关闭游标
CLOSE cur;

-- 输出结果
SELECT

total;

END

-- 普通执行SQL语句
DROP TRIGGER IF EXISTS group_state_update_user_logout;
delimiter ;;
CREATE TRIGGER group_state_update_user_logout AFTER UPDATE ON operation_group FOR EACH ROW BEGIN

DECLARE cid integer DEFAULT 0;
DECLARE done int DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT  user_id  FROM user_group WHERE group_id = new.id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

IF (old.state <> new.state) then

OPEN cur;
read_loop:
LOOP
  FETCH cur INTO cid;
  IF done THEN
    LEAVE read_loop;
  END IF;
  UPDATE user_cache set last_check_time = '2017-01-01' WHERE user_id = cid;
END LOOP;
CLOSE cur;

END IF;
END
;;
delimiter ;

-- 生成SQL语句并执行 (函数和触发器暂时不支持动态语句,只能在存储过程中使用)

set exesql = CONCAT('UPDATE user_cache set last_check_time = \'2017-01-01\' WHERE user_id IN(',ids,')');
prepare stmt from @exesql;  -- 编译语句 
EXECUTE stmt; -- 执行语句 
EXECUTE prod USING ids; -- 执行语句 ids 替换?参数
DEALLOCATE prepare prod; -- 释放解析和执行sql语句
25
Apr
0

centos7.2安装mysql

安装

yum install mariadb*

运行

systemctl start mariadb.service
systemctl start mariadb

进入命令行

mysql -u root

开机自启动

systemctl enable mariadb

修改密码

# mysql -uroot -p /*输入密码进入*/  
/*第一个方式:直接编辑数据库字段*/  
MariaDB [(none)]> use mysql;  
MariaDB [mysql]> UPDATE user SET password=password('newpassword') WHERE user='root';  
MariaDB [mysql]> flush privileges;  
MariaDB [mysql]> exit  
/*第二个方式:修改密码,不用进入mysql*/  
MariaDB [(none)]> SET password for 'root'@'localhost'=password('newpassword');  
MariaDB [(none)]> exit; 

忘记root密码,需要重置

# systemctl stop mariadb /*先停掉当前的mysql进程,不然执行下一步说进程已经存在*/
# mysqld_safe --skip-grant-tables & /*后台直接这个mysql,界面中还会出现日志,直接ctrl+c进入命令行输入*/ 
# ps -ef | grep mariadb /*看进程,会突出显示--skip-grant-tables*/
  mysql     3607  3368  0 18:05 pts/0    00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql 
  --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log 
  --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
# mysql /*直接进入mysql,不需要密码等,执行第一步中方法a里两种方式中任何一种即可*/
MariaDB [(none)]> use mysql;
MariaDB [mysql]> UPDATE user SET password=password('newpassword') WHERE user='root';
MariaDB [mysql]> flush privileges; 
MariaDB [mysql]> exit; /*这个时候用参数--skip-grant-tables启动的mysql已经会要求输入密码才能进入了*/
# pkill mysql /*用pkill来杀,kill不死,kill之后自动起来一个*/
# systemctl start mariadb /*启动正常的mysql*/

配置文件

vim /etc/my.cnf 

最大连接数

max_allowed_packet=20M /*最大连接数*/  
max_connections = 500  

允许远程访问

use mysql;  
GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;  
quit  

查看当前白名单:
select Host,User from user;
设置白名单

登陆入进入mysql库
mysql -u root -p mysql
添加名单
GRANT ALL PRIVILEGES ON . TO '白名单帐号'@'白名单IP' IDENTIFIED BY '白名单密码' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON . TO 'root'@'192.168.%.%' IDENTIFIED BY '123' WITH GRANT OPTION;
下面这个是网段模式

刷入数据库
FLUSH PRIVILEGES;
结束,虽然都是SQL语句,但是在管理器执行,是不生效的

25
Apr
0

centos7.2安装redis

下载

wget http://download.redis.io/releases/redis-4.0.9.tar.gz

解压

tar -xzvf redis-4.0.9.tar.gz 

编译

make MALLOC=libc

安装

make install 

初始化配置

./utils/install_server.sh

查看运行状态

ps -ef|grep redis

systemctl status redis_6379.service

服务启动重启等

systemctl restart redis_6379

/usr/local/bin/redis-server /etc/redis/6379.conf

 redis_init_script 

修改密码

vim /etc/redis/6379.conf
requirepass password 打开注释requirepass 

进行命令行

redis-cli

config set requirepass passwrd  修改密码 不用重启

命令行验证密码

auth passwd

开机启动

systemctl enable redis_6379

开启远程访问

vim /etc/redis/6379.conf

#bind 127.0.0.1
protected-mode yes   -> protected-mode no

管理工具

https://redisdesktop.com/download
20
Jan
0

关于postgres连续周分组统计的解决方法

最近需要做一个每周趋势图
因为在一条SQL里需要对每个分组过滤开始和结束时间
所以略显麻烦,要花较多时间去弄,所以把思路先暂时放在这里.

  1. 首先拆分得到各周分别是当年的第几周
  2. (week from now())

2.由于我们是需要一序列,所以这里要用到一个函数generate_series
作用是产生一系列的连续填充数据 generate_series(start,stop, step interval)
第三个参数可以是天,周,年,或者bigint等数据类型,这里我们需要的是周'1 weeks'
另也可以直接加7天timestamp '2018-01-21'::timestamp + '1 day'

--这样我们能得到年和周的记录
select 
    extract (year from date_series.date) as year ,
    extract (week from date_series.date) as week,date_series.date
from (
      select generate_series(
          timestamp '2017-10-01', 
          timestamp '2018-01-31',  '1 weeks') as date
) date_series
  1. 上面用来进行分组展示,接下来是对每一周的起始时间的计算

    --得到一个日期到底是周几 0代表周日 取值范围:0-6
    select extract (dow from now())

  2. 根据周几再减或加几天得到一周的开始

    select timestamp '2018-01-21'::timestamp + '1 day'

  3. 其它就是过滤等等不提了 结束