mysql5.7脚本代码例子_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;

#导入本地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`)