mysql

首页mysql
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语句,但是在管理器执行,是不生效的