MySQL使用笔记

表数据操作

插入

多行数据插入

insert into ticket_info (buyer_openid,ticket_date) 
values
('000751A016BDBF8EB40E7A902F7153C6','2017-12-01'),
('000751A016BDBF8EB40E7A902F7153C6','2017-12-01');

避免重复插入

Ignore

UNIQUE索引或PRIMARY KEY重复，无视本条执行语句，即不执行。

INSERT IGNORE INTO `table_name` (`email`, `phone`, `user_id`) 
VALUES ('test9@163.com', '99999', '9999');

Replace

UNIQUE索引或PRIMARY KEY重复，删除旧记录，插入新记录，返回受影响的行（删除和插入行数的和）。

REPLACE INTO `table_name`(`col_name`, ...) VALUES (...);

ON DUPLICATE KEY UPDATE

UNIQUE索引或PRIMARY KEY重复，则执行旧行UPDATE。

INSERT INTO table (a, b, c) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

删除

delete from user where User='test';

修改

修改表数据

update user set Password='test' where Host='11.11.11.11' and User='11';

查询

元数据操作

元数据查询

列出 MySQL 系统环境变量

SHOW VARIABLES

列出 MySQL 数据库

SHOW DATABASES 

列出MySQL数据表

SHOW TABLES[FROM db_name]   
show tables like 'ticket%'

列出数据表结构

SHOW CREATE TABLES tbl_name
describe tablename
desc tablename
select columns from tablename
show full columns from tablename;

列出表索引

SHOW INDEX FROM tbl_name [FROM db_name]

表结构调整

新增字段

alter table bulletin add citycode varchar(6) not null default 0; 

修改字段描述

ALTER TABLE order_info  MODIFY COLUMN order_status int(4) not null COMMENT '我的描述';

修改字段名称和属性

ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件

ALTER TABLE my_test_db CHANGE table_name_new table_name_old CHAR(32) NOT NULL DEFAULT '123';

删除字段

alter table user DROP COLUMN news;

权限管理

创建用户

CREATE USER 'user_name'@'11.11.11.11' identified BY 'my_password';

权限刷新

FLUSH PRIVILEGES;

权限授予

grant select on db.table to 'username'@'10.254.0.0/255.255.0.0' identified by 'xxx';  
grant select, insert on db_name.table_name to 'user_name'@'10.11.11.11';  
grant all on *.* to user_name@'10.11.11.11'
grant all on *.* to user_name@'10.11.%' identified by 'xxx'; 

权限查询

show grants for 'user_name'@'100.76.%';

权限撤销

revoke update,select,delete on db_name.*  from 'user_name'@'10.254.%';

常见错误

执行grant授权命令，提示can’t find any matching row in the user table
我们创建用户后，只是写入了user表，但是并没有及时写入权限表（grant table）。
执行 FLUSH PRIVILEGES命令，更新grant table，此时grant table里有了我们刚插入的用户，才能对它进行权限修改：

FLUSH PRIVILEGES;

字符集设置

设置会话字符集

set names utf8;

查看当前会话字符集

show variables like“character_set_%

数据导入和导出

导出数据

生成数据文件

select * from user_info into outfile '/tmp/table.txt' character set utf8;

查询结果重定向（table.txt纯文本）

mysql -uroot -p123456 -e "select * from order_info" > /tmp/table.txt

查询结果重定向（table.xlsx Excel文件）

mysql -uroot -p123456 -e "select * from order_info" > /tmp/table.xlsx

导入数据

加载数据文件(table.txt)

set names utf8;
load data local infile '/tmp/table.txt' into table user_info;

远程连接mysql

mysql -h 11.11.11.11 -P3306 -umyusernmae -pmypassword --default-character-set=utf8

数据库备份和复制

备份数据库

mysqldump -h localhost -u root -p123456 guestbook > guestbook2-29.sql

复制库表

CREATE TABLE mytbl_new LIKE production.mytbl;
INSERT mytbl_new SELECT * FROM production.mytbl;

mysql 进程信息

查看数据库进程信息

show full processlist;

删除数据库进程

kill -9 id

编码集问题

https://www.cnblogs.com/digdeep/p/5228199.html