mysql 脚本什么什么作用_mysql5.7脚本日常使用

#查看数据库物理存放目录

show variables like "%datadir%";

#查看所有数据库

show databases

#选择数据库

use your_db_name

#查看数据库表结构(需要先选择数据库【use your_db_name】)

describe your_table_name

#方法一：获取某个数据库的所有表的名字

SELECT table_name FROM information_schema.tables WHERE table_schema = 'your_db_name' AND table_type = 'base table'

#方法二：获取某个数据库的所有表的名字(需要先选择数据库【use your_db_name】)

show tables

#获取所有表的所有字段名

select table_name,column_name from information_schema.columns order by table_schema,table_name;

#获取表的所有字段名

select COLUMN_NAME from information_schema.COLUMNS where table_name = ‘your_table_name’ and table_schema = ‘your_db_name’;

#查看数据库所有索引

SELECT * FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = 'your_db_name';

#查看某一表索引

SELECT * FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = 'your_db_name' and a.table_name = 'your_table_name';

#查看某一表索引(需要先选择数据库【use mydatabyour_db_namesename】)

show index from your_table_name

#

SET FOREIGN_KEY_CHECKS = 0;

#查看开关状态

show variables like 'SQL_SAFE_UPDATES';

#修改下数据库模式

SET SQL_SAFE_UPDATES = 0;

#复制列

update demo_db.user_info set r=remark where id>0

#替换字符串

update user_info set r=replace(r,'http://www.xxx.com/','') where id>0;

update user_info set r=replace(r,'.html','') where id>0;

#是数字的列

SELECT book_id FROM books WHERE book_id REGEXP '[0-9]{1,}' limit 10000;

#不是数字的列

SELECT book_id FROM books WHERE book_id REGEXP '[0-9]{1,}'=0 limit 10000;

#查看当前连接情况

show processlist;

select * from information_schema.processlist;

# 记录存在则更新，不存在则插入

INSERT INTO testTabe (c1,c2,c3) VALUES (1,'insert c2','insert c3') ON DUPLICATE KEY UPDATE c2='update c2',c3='update c3';

#导入本地csv文件(摘自：https://blog.csdn.net/quiet_girl/article/details/71436108)

LOAD DATA LOCAL INFILE 'E:\\DB\\data.csv'

into table your_db_name.your_table_name

fields terminated by ',' optionally enclosed by '"' escaped by '"'

lines terminated by '\r\n';

#导入本地csv文件(摘自：https://www.cnblogs.com/ahudyan-forever/p/5817847.html)

LOAD DATA LOCAL INFILE 'E:\\DB\\data.csv'

INTO TABLE `your_db_name`.`your_table_name`

FIELDS ESCAPED BY '\\'

TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\r\n'

(`AREA_NAME`, `AREA_CODE`, `CITY_ID`, `PROVINCE_NAME`, `PROVINCE_CODE`)