mysql常用操作

1.脚本

启动
mysql -u root -p'password' database
mysql -hhost -uname -p'password' database

导出表
mysqldump -u root -p database table > dump.sql

导入表
mysql -u root -p database < dump.sql

2.语句

数据库表数据统计
SELECT table_name,table_rows FROM information_schema.tables WHERE table_schema='database' ORDER BY table_rows DESC;

计算总数
select sum(column) from table;

关联查询
select 1 from table1 a,table2 b where a.id=b.id;

去重查询
select distinct column from table;

删除数据表
DROP TABLE table_name;

修改表名
ALTER TABLE table_name1 RENAME TO table_name2;

修改字段类型/长度/默认值
alter table table_name modify column column_name varchar(256) not null default "";

新增字段
alter table table_name add column_name varchar(256) not null default "";