小伙伴整理的mysql命令操作

原博客文章地址：http://www.cnweba.com/mysqlcmd/

DOS 命令列指令mysql注册和注销

注册 mysql服务

C:\mysql\bin>mysqld-nt –install MySql –defaults-file=C:\mysql\my.ini

注销 mysql服务

C:\mysql\bin>mysqld-nt –remove MySql

启动服务mysql

C:\>net start mysql

停止服务mysql

C:\>net stop mysql

mysql登录、增加用户、密码更改

一、连接数据库

格式：mysql -h主机地址 -u用户名 -p用户密码 （注：u与root之间可以不加空格，其他也一样）

1、连接到本机上的mysql

首先打开DOS窗口，进入到mysql安装目录下的bin目录下，然后再输入命令：mysql -uroot -p ，回车后输入密码（如果没有密码直接回车即可进入mysql中），进入后的提示符为：mysql> 。

2、连接到远程上的mysql，假设远程的ip地址为：100.100.100.100，用户名为：root，密码为：123，则输入命令如下：

mysql -h100.100.100.100 -uroot -p123

3、退出mysql命令：exit（回车）。

二、修改密码

格式：mysqladmin -u用户名 -p旧密码 password 新密码

1、给用户root加一个密码123。进入到mysql安装目录下的bin目录下，输入以下命令：mysqladmin -uroot password 123

2、将root用户密码该为456，输入以下命令：

mysqladmin -uroot -p123 password 456

三、新增用户（mysql环境中的命令，后面必须带分号作为命令结束符）

格式：grant select on 数据库.* to 用户名@登录主机 identified by \”密码”\;

1、新增一个用户test，密码为123，可以在任何主机上登录，对所有数据库有查询、插入、修改、删除的权限，首先进入到mysql安装目录下的bin目录下，然后输入命令：

grant select,insert,update,delete on *.* to test@localhost identified by \”123″\;

2、如果不想用户test有密码，输入命令：

grant select,insert,update,delete on *.* to test@localhost identified by \”"\;

3、创建可以在任何地方连接服务器的一个完全的超级用户，必须使用口令：123，输入口令：

grant all privileges on *.* to user@localhost identified by ’123′ with;

mysql中有关数据库方面的操作（注：以下所有操作都必须登录到mysql中，在mysql的提示符下进行，并且每个命令都要以分号结束）

一、mysql常用命令

create database name;#创建数据库

drop database name;#直接删除数据库，不提醒

mysqladmin drop database name;#删除数据库前，有提醒

show databases;#显示所有数据库

use dbname;#选择（打开）数据库

show tables;#显示表

describe tablename;#表的详细描述

select 中加上distinct去除重复字段

select version(),current_date;#显示当前mysql版本和当前日期

update user set password = password(“新密码”) where user=’root’;#修改mysql中root的密码

describe user;#显示mysql数据库中user表的列信息

flush privileges;#刷新数据库

alter table t1 rename t2;#重命名表

create table tablename (name varchar(20), sex char(1));#创建表

describe tablename;#显示表结构

insert into tablename values (“hyq”,”m”);#往表中加入记录

drop table tablename;#删除表

delete from tablename;#清空表

show columns from tablename;#显示表格列的属性

alter table tabelName add column fieldName dateType; #增加一个字段

alter table tabelName add column fieldName1 dateType,add columns fieldName2 dateType; #增加多个字段

select now();#查询时间

select user();#查询当前用户

select version();#查询数据库版本

select database();#查询当前使用的数据库

rm -f student_course/students.* #删除student_course数据库中的students数据表

create temporary table tabalename(name varchar(10)); #创建临时表：(建立临时表tabalename)

create table if not exists students(……);#创建表是先判断表是否存在

create table table2 select * from table1 where 1<>1;#从已经有的表中复制表的结构

create table table2 select * from table1;#复制表

alter table table1 rename as table2; #对表重新命名

alter table table1 modify id int unsigned;#修改列id的类型为int unsigned

alter table table1 change id sid int unsigned;#修改列id的名字为sid，而且把属性修改为int unsigned

创建索引

alter table table1 add index ind_id (id);

create index ind_id on table1 (id);

create unique index ind_id on table1 (id);//建立唯一性索引

删除索引

drop index idx_id on table1;

alter table table1 drop index ind_id;

select concat(id,’:',name,’=') from students;#联合字符或者多个列(将列id与”:”和列name和”=”连接)

select * from students order by id limit 9,10;#limit(选出10到20条)<第一个记录集的编号是0>

更新表中数据

update [low_priority] [ignore] tbl_name set col_name1=expr1 [, col_name2=expr2 ...] [where where_definition] [order by ...] [limit rows]

or

update [low_priority] [ignore] tbl_name [, tbl_name ...] set col_name1=expr1 [, col_name2=expr2 ...] [where where_definition]

注：

UPDATE 以新的值更新现存表中行的列。SET 子句指出要修改哪个列和他们应该给定的值。

WHERE 子句如果被给出，指定哪个记录行应该被更新。否则，所有的记录行被更新。

如果 ORDER BY 子句被指定，记录行将被以指定的次序更新。

如果你指定关键词 LOW_PRIORITY，UPDATE 的执行将被延迟，直到没有其它的客户端正在读取表。

如果你指定关键词 IGNORE，该更新语句将不会异常中止，即使在更新过程中出现重复键错误。导致冲突的记录行将不会被更新。

如果在一个表达式中从 tbl_name 中访问一个列，UPDATE 使用列的当前值。举例来说，下面的语句设置 age 列值为它的当前值加 1 ：

mysql> UPDATE persondata SET age=age+1;

UPDATE 赋值是从左到右计算的。举例来说，下列语句将 age 列设置为它的两倍，然后再加 1 ：

mysql> UPDATE persondata SET age=age*2, age=age+1;

如果你设置列为其当前的值，MySQL 注意到这点，并不更新它。

UPDATE 返回实际被改变的记录行数目。在 MySQL 3.22 或更新的版本中，C API 函数 mysql_info()返回被匹配并更新的记录行数目，以及在 UPDATE 期间发生的警告的数目。在 MySQL 3.23 中，你可以使用 LIMIT # 来确保只有给定的记录行数目被更改。如果一个 ORDER BY 子句被使用(从 MySQL4.0.0开始支持)，记录行将以指定的次序被更新。这实际上只有连同 LIMIT一起才有用。从 MySQL 4.0.4 开始，你也可以执行一个包含多个表的 UPDATE 的操作：UPDATE items,month SET items.price=month.priceWHERE items.id=month.id;（多表 UPDATE 不可以使用 ORDER BY 或 LIMIT）

二、备份数据库(到e盘)

1、mysqldump -h主机名 -u用户名 -p密码 dbname > e:\dbname.sql

2、如果不希望后来手工创建staffer,可以：mysqldump -h主机名 -u用户名 -p密码 –databases dbname>e:\dbname.sql

三、恢复数据库

1、mysqladmin -h主机名 -u用户名 -p密码 create dbname

2、mysqldump -h主机名 -u用户名 -p密码 dbname < dbname.sql

四、只导出建表指令

mysqladmin -h主机名 -u用户名 -p密码 -d dbname > a.sql

五、只导出数据指令

mysqladmin -h主机名 -u用户名 -p密码 -t dbname > a.sql

六：从文本向数据库导入数据

1、使用工具mysqlimport

这个工具的作用是将文件导入到和去掉文件扩展名名字相同的表里，如dbname.txt,dbname都是导入到staffer表中

常用选项及功能如下

-d or –delete 新数据导入数据表中之前删除数据数据表中的所有信息

-f or –force 不管是否遇到错误，mysqlimport将强制继续插入数据

-i or –ignore mysqlimport跳过或者忽略那些有相同唯一关键字的行， 导入文件中的数据将被忽略。

-l or -lock-tables 数据被插入之前锁住表，这样就防止了，你在更新数据库时，用户的查询和更新受到影响。

-r or -replace 这个选项与－i选项的作用相反；此选项将替代表中有相同唯一关键字的记录。

–fields-enclosed- by= char指定文本文件中数据的记录时以什么括起的， 很多情况下数据以双引号括起。 默认的情况下数据是没有被字符括起的。

–fields-terminated- by=char指定各个数据的值之间的分隔符，在句号分隔的文件中，分隔符是句号。您可以用此选项指定数据之间的分隔符。默认的分隔符是跳格符（Tab）

–lines-terminated- by=str此选项指定文本文件中行与行之间数据的分隔字符串或者字符。

默认的情况下mysqlimport以newline为行分隔符。您可以选择用一个字符串来替代一个单个的字符：一个新行或者一个回车。

mysqlimport命令常用的选项还有-v 显示版本（version）， -p 提示输入密码（password）等

这个工具有个问题，无法忽略某些列，这样对我们的数据导入有很大的麻烦，虽然可以手工设置这个字段，但会出现莫名其妙的结果

2、Load Data INFILE file_name into table_name(column1_name,column2_name)

这个命令在mysql>提示符下使用，优点是可以指定列导入

例：mysql>load data infile “e:/depart_no.txt” into tablename(department_id,position_id);

3、.tsv 文件导入到mysql可用load data语法

例：要将user.tsv文件到入到 tese库的user表中，命令如下

mysql>use user;

mysql>load data infile ‘user.tsv’ into table user;

（注：1、要导入的文件如：user.tsv;一定要在mysql的数据库目录下，../mysql/data/test；2、文件的格式和数据库中的格式一样，字段间用\tab隔开）

七、导入.sql文件命令（例如D:/mysql.sql）

mysql>use database;

mysql>source d:/mysql.sql;

八、MySQL不支持的功能

事务，视图，外键和引用完整性，存储过程和触发器

九、MySQL会使用索引的操作符号

<,<=,>=,>,=,between,in,不带%或者_开头的like

十、使用not null和enum

尽量将列定义为not null，这样可使数据的出来更快，所需的空间更少，而且在查询时，MySQL不需要检查是否存在特例，即null值，从而优化查询；

十一、使用索引的缺点

1)减慢增删改数据的速度；

2）占用磁盘空间；

3）增加查询优化器的负担；

当查询优化器生成执行计划时，会考虑索引，太多的索引会给查询优化器增加工作量，导致无法选择最优的查询方案；

十二、分析索引效率

方法：在一般的SQL语句前加上explain；

分析结果的含义：

1、table：表名；

2、type：连接的类型，(ALL/Range/Ref)。其中ref是最理想的；

3、possible_keys：查询可以利用的索引名；

4、key：实际使用的索引；

5、key_len：索引中被使用部分的长度（字节）；

6、ref：显示列名字或者”const”（不明白什么意思）；

7、rows：显示MySQL认为在找到正确结果之前必须扫描的行数；

8、extra：MySQL的建议；

十三、使用较短的定长列

1、尽可能使用较短的数据类型；

2、尽可能使用定长数据类型；

3、用char代替varchar，固定长度的数据处理比变长的快些；

4、对于频繁修改的表，磁盘容易形成碎片，从而影响数据库的整体性能；

5、万一出现数据表崩溃，使用固定长度数据行的表更容易重新构造。使用固定长度的数据行，每个记录的开始位置都是固定记录长度的倍数，可以很容易被检测到，但是使用可变长度的数据行就不一定了；

6、对于MyISAM类型的数据表，虽然转换成固定长度的数据列可以提高性能，但是占据的空间也大；

十四、使用optimize table整理碎片

对于经常修改的表，容易产生碎片，使在查询数据库时必须读取更多的磁盘块，降低查询性能。具有可变长的表都存在磁盘碎片问题，这个问题对blob数据类型更为突出，因为其尺寸变化非常大。可以通过使用optimize table来整理碎片，保证数据库性能不下降，优化那些受碎片影响的数据表。 optimize table可以用于MyISAM和BDB类型的数据表。实际上任何碎片整理方法都是用mysqldump来转存数据表，然后使用转存后的文件并重新建数据表；

十五、使用procedure analyse()显示最佳类型的建议

可以使用procedure analyse()显示最佳类型的建议，使用很简单，在select语句后面加上procedure analyse()就可以了；例如：

select * from students procedure analyse();

select * from students procedure analyse(16,256);

第二条语句要求procedure analyse()不要建议含有多于16个值，或者含有多于256字节的enum类型，如果没有限制，输出可能会很长；