Mysql Note

于 2020-04-02 15:52:16 发布 , 阅读量:81 , 评论量:2

MySQL安装配置
mysql数据库重命名方案
create table as select ... 与 create table like tbl
mysql load data
MySQL求中位数
行列转换
mysql分区类型

MySQL安装配置

下载

https://dev.mysql.com/downloads/mysql/下载Linux-Generic版本 uname -a查看系统架构, 选择相应版本. … x86_64 x86_64 x86_64 GNU/Linux 这里选择64位minimal版, 使用wget命令下载至本地:

wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.18-linux-x86_64-minimal.tar

解压缩

tar xvf mysql-8.0.18-linux-x86_64-minimal.tar 下载的这个版本解压后会有三个文件:

 mysql-8.0.18-linux-x86_64-minimal.tar.xz
 mysql-router-8.0.18-linux-x86_64-minimal.tar.xz
 mysql-test-8.0.18-linux-x86_64-minimal.tar.xz
其中中有第一个文件是有用的, 解压用到的只是该文件 tar xvf mysql-8.0.18-linux-x86_64-minimal.tar.xz

安装

参考MySQL documentation

检查mysql依赖包libaio是否已安装, centos 下 yum search libaio, 如示安装则安装之yum install libaio. 否则会报错error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory 创建mysql用户组

shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql

配置安装

# 进入要安装到的目录
shell> cd /usr/local
# 解压到当前目录
shell> tar xvf /bs/mysql-8.0.18-linux-x86_64-minimal.tar.xz
# 设置为符号链接
shell> ln -s mysql-8.0.18-linux-x86_64-minimal/ mysql
# 目录加入环境变量
shell> export PATH=$PATH:/usr/local/mysql/bin

shell> cd mysql
shell> mkdir data
shell> chown mysql:mysql data
shell> chmod 750 data
# 初始化, 结束后会生成初始密码
shell> bin/mysqld --initialize --user=mysql
# 结果出现如下字样, 初始化成功, 并谨慎保存临时密码, 后续登录有用
# A temporary password is generated for root@localhost: ZJAhY?Avg6:q
shell> bin/mysql_ssl_rsa_setup
# 启动服务
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server

修改初始密码

# 登录mysql, 密码即为上步中生成的临时密码
shell> bin/mysql -u root -p

# 修改密码
mysql> alter user 'root'@'localhost' identified by 'xxxxxxx';

创建非root用户

一般root用户权限应仅限本地登录, 远程登录尽量以非root用户登录. 创建新用户方法如下: 登录mysql后

mysql> ? grant;  # ? 表示帮助, 此命令会显示grant命令的文档
# 参考文档例子, 创建新用户
mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

. 指数据库.表所以. 为所有数据库和表. ‘jeffrey’@’%’ 为所有主机可登录.‘jeffrey’@‘127.23.32.33’ 为ip127.23.32.33可登录.

navicat登录时提示caching-sha2-password并登录失败

在安装mysql8的时候如果选择了密码加密，之后用客户端连接比如navicat，会提示客户端连接caching-sha2-password,是由于客户端不支持这种插件，可以通过如下方式进行修改：

#更新密码（mysql_native_password模式）    
mysql> ALTER USER 'jeffrey'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

mysql数据库重命名方案

# mysql重命名数据库名称方案
select table_name ,
concat('rename table baodao07.',table_name ,' to baodao1907.',table_name, ';') sh 
from information_schema.TABLES where TABLE_SCHEMA='baodao07'
;

`create table as select ...` 与 `create table like tbl`

mysql新建表语句create table as select ... 与 create table like tbl区别： as方式只复制表结构（不包括索引等）与数据；
like方式只复制表结构，包括索引等

mysql `load data`

character set设置字符集，解决中文字符不识别报错问题
terminated by设置分隔符
enclosed by 设置字段括起字符
lines terminated by 选项设置行分隔符
IGNORE number LINES选项可以被用于在文件的开始处忽略行, 1表示忽略列名行。

load data infile "e:/Download/43082.csv" into table rpt_04 character set gb18030 fields terminated by ','enclosed by '"' lines terminated by '\n' ignore 1 lines;

参考：一二

MySQL求中位数

构造表

DROP TABLE IF EXISTS producte;
CREATE TABLE producte(user_id INT, 
price DECIMAL(10,2));
INSERT INTO producte SELECT 1,2.2;
INSERT INTO producte SELECT 1,3.2;
INSERT INTO producte SELECT 1,3.6;
INSERT INTO producte SELECT 1,5.2;
INSERT INTO producte SELECT 2,6.2;
INSERT INTO producte SELECT 2,7.2;
INSERT INTO producte SELECT 2,9.2;
INSERT INTO producte SELECT 2,9.2;
INSERT INTO producte SELECT 2,11.2;

各user_id中位数

select user_id, avg(price)
from(
    select e.user_id, e.price
    from producte e, producte d
    where e.user_id = d.user_id
    group by e.user_id, e.price
    having sum(case when e.price=d.price then 1 else 0 end)>=abs(sum(sign(e.price-d.price)))
    )t
group by user_id

求全部价格中位数

select avg(price)
from (
    select e.price
    from producte e, producte d
    group by e.price
    having sum(case when e.price=d.price then 1 else 0 end)>=abs(sum(sign(e.price-d.price)))
    )t

求众数

SET @rows:=0;
SET @group:='';

SELECT * FROM(
SELECT @rows:=CASE WHEN @groups = user_id then @rows+1 ELSE 1 end rowid,@groups:=user_id, price, cnt
FROM(
SELECT user_id, price, count(0) as cnt
FROM producte
GROUP BY user_id, price
ORDER BY user_id,count(0) DESC
)t
)v
WHERE rowid=1

行列转换

drop table if exists tbl;
CREATE TABLE tbl(
month_of_report int,
total_amt DECIMAL(10,2),
total_cnt int,
total_usr int);
INSERT INTO tbl SELECT 2016, 100.2, 20, 5;
INSERT INTO tbl SELECT 2017, 150.2, 30, NULL;
INSERT INTO tbl SELECT 2018, 300.2, 80, 0;

SELECT * FROM tbl;

SELECT cls, 
MAX(CASE WHEN month_of_report=2016 then total else null END) '2016',
MAX(CASE WHEN month_of_report=2017 then total else NULL END) '2017',
MAX(CASE WHEN month_of_report=2018 then total else NULL END) '2018'
FROM(
SELECT month_of_report,'amt' cls, total_amt total FROM tbl
UNION ALL
SELECT month_of_report,'cnt' cls, total_cnt FROM tbl
UNION ALL
SELECT month_of_report, 'usr' cls, total_usr FROM tbl
)v
GROUP BY cls

mysql分区类型

RANGE分区- partition by range (func)(…) LIST分区 HASH分区 KEY分区 *COLUMNS分区

子分区: 在分区的基本上再进行分区, 也称为复合分区

评论：

* * * Snag Your Free Gift: https://avtoilm.uz/index.php?6fk3qm * * * hs=fb90f48a7dd6dc7549ecd95227581fb8* ххх* 2025-06-03 23:05:38

5qtu85
* * * <a href="https://avtoilm.uz/index.php?6fk3qm">Get Free Bitcoin Now</a> * * * hs=fb90f48a7dd6dc7549ecd95227581fb8* ххх* 2025-06-03 23:05:46

5qtu85