分类 数据库 下的文章

mysql纵表变横表

数据以流水存放

CREATE TABLE `tab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `n` varchar(3) DEFAULT NULL,
  `t` varchar(1) DEFAULT NULL,
  `v` float DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `tab` VALUES ('1', '101', 'A', '1');
INSERT INTO `tab` VALUES ('2', '102', 'A', '2');
INSERT INTO `tab` VALUES ('3', '102', 'B', '3');
INSERT INTO `tab` VALUES ('4', '103', 'B', '4');

上SQL

select n,
sum(case t when 'A' then v else null end) as A,
sum(case t when 'B' then v else null end) as B
from tab group by n order by n

结果

n   A   B
101 1   
102 2   3
103     4

关于mysql的主从复制和文件位置

首先是文件位置,以前放到了docker里,结果docker容器崩溃了,数据也就找不回来了,
想到放共享文件夹,球猫说专业名词叫volume,好吧,其实就是个v的参数,下边开始从ubuntu开始,

mkdir mydata
docker run -ti -h mysql_1 --name mysql_1 -v /home/user/mydata:/mysqldata -p 10001:3306 ubuntu
apt-get update
apt-get install mysql-server
apt-get install vim
vi /etc/mysql/mysql.conf.d/mysqld.cnf
#修改datadir为/mysqldata,注释掉bind-address=127.0.0.1
cp /var/lib/mysql/. /mysqldata -r -a
service mysql restart
mysql -u root -p

CREATE USER 'test' IDENTIFIED BY '****';
create database testDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
grant all privileges on testDB.* to test@"%" identified by '****' with grant option;
flush privileges;
exit

应该就搞定了,可以通过客户端访问了,结果发现mydata文件夹是空的,后来通过find找到在这里,看来要写绝对路径/var/lib/docker/volumes/mydata/_data/

下面就是主从复制了,再建一个mysql容器

mkdir mydata2
docker run -ti -h mysql_2 --name mysql_2 -v /home/user/mydata2:/mysqldata -p 10002:3306 ubuntu
...

似乎很多重复过程,可以中间exit后保存一份镜像再继续run
docker commit -m "mysql ok" mysql_1 ubuntu:mysql

如果主库有了数据,就需要先备份一下

mysqldump -uroot -p'****' -S /run/mysqld/mysqld.sock --all-databases > /mysqldata/mysql_bak.$(date +%F).sql
cp /var/lib/docker/volumes/mydata/_data/mysql_bak.2018-03-2x.sql mydata2
mysql -uroot -p'****' -S /run/mysqld/mysqld.sock < /mysqldata/mysql_bak.2018-03-2x.sql

获取所有容器的ip

docker inspect -f '{{.Name}} - {{.NetworkSettings.IPAddress }}' $(docker ps -aq)

主机

vi /etc/mysql/mysql.conf.d/mysqld.cnf
#去掉Server-id和log-bin的注释
service mysql restart
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'test'@'172.17.0.*' IDENTIFIED BY '****';
SHOW MASTER STATUS;
#记下日志的名字mysql-bin.000001和位置446

从机

vi /etc/mysql/mysql.conf.d/mysqld.cnf
#去掉Server-id的注释改为2
vi /mysqldata/auto.cnf
#修改uuid
service mysql restart
mysql -u root -p
CHANGE MASTER TO MASTER_HOST='172.17.0.x', MASTER_USER='test', MASTER_PASSWORD='****', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=446;
start slave;
SHOW SLAVE STATUS\G

这样就好了,一下是一些笔记

show binlog events;
show binlog events in 'mysql-bin.000001';
show variables like '%server_uuid%';
stop slave;set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;start slave;
find / -name mysqld.sock
cat /var/log/mysql/error.log
#!/bin/sh
service mysql start
/bin/bash

参考资料,排名不分先后
http://www.cnblogs.com/phpstudy2015-6/p/6485819.html
https://blog.csdn.net/seteor/article/details/17264633
https://www.cnblogs.com/phpstudy2015-6/p/6706465.html

sqlserver的一些笔记

如何让获取有哪些库

SELECT Name FROM Master..SysDatabases ORDER BY Name

如何让获取有哪些表

SELECT Name FROM SysObjects Where XType='U' ORDER BY Name

如何获取表的结构

SELECT (case when a.colorder=1 then d.name else null end) 表名,  
a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识, 
(case when (SELECT count(*) FROM sysobjects  
WHERE (name in (SELECT name FROM sysindexes  
WHERE (id = a.id) AND (indid in  
(SELECT indid FROM sysindexkeys  
WHERE (id = a.id) AND (colid in  
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  
AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,  
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,  
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,  
isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
FROM  syscolumns a 
left join systypes b on a.xtype=b.xusertype  
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
left join syscomments e on a.cdefault=e.id  
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where (b.name is not null)and(d.name='表名称')
order by a.id,a.colorder

参考:
http://www.cnblogs.com/ynbt/archive/2012/07/16/2593389.html

和water学sqlserver

最近SQLServer经常拒绝服务,Water教了一着如何监视

通过性能监视器监视链接池化
单击开始,指向程序,指向管理工具,然后单击性能运行性能监视器。
在图表背景中右击,然后单击增加计数器。
在性能对象下拉列表框中,单击SQL Server:通用统计。
在出现的列表中,单击用户链接。
单击增加,然后单击关闭。 

还有生动的录像,尝试上传,不一定能传上来,SQL.gif

这个录像是用一个叫LICEcap的软件做的,真生动,下边就是观察日志了。

mysql的时区设置

突然发现,mysql里的时间戳的时间都是不对的,比如现在是10:51,可是读出的时间是19:51

select now();

什么鬼,还不是差8小时,开始找问题,先看系统时间,用date命令,显示没有问题Fri Jul 28 10:53:16 CST 2017
那么就是mysql的时区问题了,莫非到了西某区

show variables like "%time_zone%";

查询结果:system_time_zone PDT,PDT是个什么时区?经查询,Pacific Daylight Time,太平洋夏令时,我国也实行过夏令时,比较晕,看看怎么能调整成我们的时区吧。顺便查了一下CST,吓了一跳

CST可以为如下4个不同的时区的缩写:
美国中部时间:Central Standard Time (USA) UT-6:00
澳大利亚中部时间:Central Standard Time (Australia) UT+9:30
中国标准时间:China Standard Time UT+8:00
古巴标准时间:Cuba Standard Time UT-4:00

这是怎么回事,还好可以确认验证

date -R

显示Fri, 28 Jul 2017 11:07:57 +0800,印证了我在中国,下一步就是调教mysql了

set global system_time_zone = '+8:00';

报错[Err] 1238 - Variable 'system_time_zone' is a read only variable,怎么办,莫非我的mysql没有重起过?

sudo service mysql restart

结果查出来还是那个鬼时区。还是老样子,执行

set global time_zone = '+8:00';
flush privileges;

但是远程要先关闭。时间是对了,重起服务又回去了,不行啊。改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf,在[mysqld]下添加default-time_zone = '+8:00'重起mysql后可以,再尝试重起服务器。PDT依然在,我们的+8:00也在了,可以满足使用了。
最后找到的根本解决办法:

mv /etc/localtime  /etc/localtime-old
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

终于都是CST了。

ubuntu系统的时区设置:sudo cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
sudo tzselect还有sudo ntpdate time.windows.com