1、连接数据库
mysql -h 10.100.6.124 -P 3307 -u root -p
注意:-P 和-p是两个含义,前者为端口号,后者为密码
2、常用查看命令
show databases;
show tables from db_name;show index from table_name;
查看表结构:
1) desc table_name;
2) show columns from table_name;
3) describe table_name;
4) show create table table_name;
查看数据库中表的记录数:
select table_name, table_rows, data_length, table_collation from information_schema.tables where table_schema=db_name;
查看数据库中表的列数:
select table_name, count(*) from information_schema.columns where table_schema=db_name group by table_name;
3、创建和删除数据库命令
create database db_name;
create database db_name character set gbk;
drop database db_name;
4、索引相关命令
alter table table_name add index index_name (column_name); //添加索引
alter table table_name add index index_name (column_name_1, column_name_2, column_name_3); //添加联合索引
drop index index_name on table_name; //删除索引
5、查看性能问题
explain select col_date, col_price from table_name where col_date ="20160118";
6、其他
执行脚本:
source /usr/my.sql;