mysql常用命令
常用命令:
mysql -V,select version(),status,\s; show databases; select database()/user(); show tables; set password = password('123456'); flush privileges; show create table shop_user \G;
desc table; exit;
创建数据库:
create databse `test` default charset utf8 collate utf8_general_ci;
表修改:
alter table test rename to test2; // 修改表名 alter table test engine=myisam/innodb; // 修改表引擎 alter table test add i int after id; // 添加字段 alter table test drop i; // 删除字段 alter table test modify name char(10); // 修改字段类型
索引:
普通索引:index/key 唯一索引/组合索引:unique 关键索引:primiry key
索引:设置索引会新建索引表,insert/update/delete时,耗时增加,但根据索引select时,会大大减少查询时间,效率很高。
临时表:
create temporary table test2(id int not null, title varchar(20) not null default ''); insert into test2(id, title) values(1, 'Jabin'); select * from test2; drop table test2;
表复制:
create table test2 like test; // 表结构 create table test2 select * from test; // 表结构和数据
表重复数据处理:
防止重复:设置索引PRIMARY KEY (lastname, firstname)或UNIQUE (lastname, firstname) 统计重复:select count(*) as rep firstname, lastname from user group by firstname, lastname having req > 1; 过滤重复:select distinct fristname, lastname from user; 或 select firstname, lastname from user group by (firstname, lastname); 删除重复: create table tmp select firstname,lastname, sex from user group by (firstname, lastname); drop table user; alter table tmp rename to user; 或 alter ignore table user add primiry key (firstname, lastname);
数据导出/导入:
导出: 1. mysqldump -uroot -p test2 test > d:\test.sql // 导出某个表的数据,如有需要可导出整个数据库 2. mysqldump -uroot -p -d test2 test > d:\test.sql // 导出某个表的表结构 导入: 1. mysql -uroot -p test2 < d:\\test.sql 2. source d:\\test.sql; // 进入表后source