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 ('[e-mail]', '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 CREATE TABLES tbl_namedescribe tablenamedesc tablenameselect columns from tablenameshow full columns from tablename;

列出表索引

SHOW INDEX FROM tbl_name [FROM db_name]

权限管理

创建用户

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';  

权限查询

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;

加载数据文件

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