home archives github knives links
tags mysql sql
categories
only title title and content
mysql笔记
2019-11-09

安装

sudo apt install mysql-server mysql-client
sudo mysql

使用

用户登录

参考: 菜鸟教程

# 连接到MySQL服务器
mysql -u root -p

添加用户

insert

use mysql
insert into user ( Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked )
  values
  ('localhost', 'niabie', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', password(''), 'N', '2019-11-07 15:17:00', null, 'N');
flush privileges;# 更新
key value
Host 'localhost'
User 'niabie'
Select_priv 'Y'
Insert_priv 'Y'
Update_priv 'Y'
Delete_priv 'Y'
Create_priv 'Y'
Drop_priv 'Y'
Reload_priv 'Y'
Shutdown_priv 'Y'
Process_priv 'Y'
File_priv 'Y'
Grant_priv 'Y'
References_priv 'Y'
Index_priv 'Y'
Alter_priv 'Y'
Show_db_priv 'Y'
Super_priv 'Y'
Create_tmp_table_priv 'Y'
Lock_tables_priv 'Y'
Execute_priv 'Y'
Repl_slave_priv 'Y'
Repl_client_priv 'Y'
Create_view_priv 'Y'
Show_view_priv 'Y'
Create_routine_priv 'Y'
Alter_routine_priv 'Y'
Create_user_priv 'Y'
Event_priv 'Y'
Trigger_priv 'Y'
Create_tablespace_priv 'Y'
ssl_type ''
ssl_cipher ''
x509_issuer ''
x509_subject ''
max_questions 0
max_updates 0
max_connections 0
max_user_connections 0
plugin 'mysql_native_password'
authentication_string password('')
password_expired 'N'
password_last_changed '2019-11-07 15:17:00'
password_lifetime null
account_locked 'N'

用户具有创建数据库等的权限,authentication_string不需要则填null

create

参考

create user lynx;
update user set Host='localhost', authentication_string=password('1111') where User='lynx';
flush privileges;# 更新
grant all on database.table to 'lynx'@'localhost';# 分配database的table权限给用户
show grants for lynx@localhost;# 显示用户权限

用户没有创建数据库的权限. 根据版本的变化, authentication_string赋值可能改为=''

修改数据库位置

show variables like '%dir%';
-- 或
select @@datadir

修改数据库存放位置

参考:digitalocean

  1. 暂停mysql服务
sudo systemctl stop mysql
sudo systemctl status mysql
  1. 转移目录
sudo rsync -av /var/lib/mysql {dir}
sudo mv /var/lib/mysql /var/lib/mysql.bak
  1. 修改mysql配置

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
datadir = {dir}/mysql/
  1. 修改apparmor访问rules

/etc/apparmor.d/tunables/alias

alias /var/lib/mysql -> {dir}/mysql/,
sudo systemctl restart apparmor
  1. 创建mysql所需的的最小文件结构
sudo mkdir /var/lib/mysql/mysql -p
  1. 重启mysql
sudo systemctl start mysql
sudo systemctl status mysql
# sudo rm -Rf /var/lib/mysql.bak
# sudo systemctl restart mysql

基本操作

# 创建数据库
create database homework3;
# 查看所有数据库
show databases;
# 选择数据库
use homework3;
# 查看所有表
show tables;
# 重命名表
rename table old_table_name to new_table_name;

查看相关信息

select version();
select now();
select user();
show variables like 'port';

语法

建表

create table if not exists customers(
    cid char(4) not null,
    cname char(20) not null,
    city char(20),
    discnt real,
    primary key ( cid )
) character set = utf8 -- 支持中文;

修改并查看注释(comment)

alter table customers
change column cid
cid char(4) not null comment '编号',
change column cname
cname char(20) not null comment '姓名',
change column city
city char(20) comment '城市',
change column discnt
discnt real comment '折扣';
show full columns from customers;

复制表

复制结构

create table table_new like table_old

复制数据

insert into table_new select * from table_old

insert去重

遇到重复的不进行insert

insert ignore into table (column)
 values
 ...

TODO

遇到重复的, 将完全覆盖

insert ignore into table (column)
 values
 ...

更新表数据

update table_name set item="shit" where fuck="fuck";

每组前n

参考

计算时间差

SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01');
SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01') as a;

其中MONTH部分可以更改为

通配符

select season_id, id, title, p_year, c_year from season
where title like '%鲁路修%'
order by p_year;

数据回滚

binlog2sql

安装

git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql
python3 -m pip install -r requirements.txt

使用

用不了, pymysql版本冲突