mysql 常用操作命令

1.使用\G参数改变输出结果集的显示方式

   select * from test \G  (不用分号)

   注意：\G为大写字母，不可使用小写；使用\G参数后，SQL语句后可以不加分隔符，如果加分隔符，则会报“error:no query specified”的错误。（在mysql的客户端是使用不了的，因为需要分号结尾在客户端，例如navicate）

   同理：这样的显示效果与MYSQL命令的-E参数是一样的。使用-E参数后，结果集默认以列的方式显示：mysql -uroot -E

 

2.登录mysql的命令：mysql --default-character-set=utf8 -h10.10.9.149 -ubicloud -p  ：使用 --default-character-set=utf8 参数登录mysql 跟 登录后使用 set names utf8 （也就是 set character_set_results=utf8，设置终端字符集） 来设置默认显示的字符集 一样

 

3.用户权限命令

   3.1 查看某用户权限：show grants for 用户名@主机  显示的是每一次授予权限

 

   3.2 给予权限：grant select,update on 数据库.表名 to 用户名@主机 (all的表示所有，数据库.*表示所有表)

         例如： grant all on *.* to 'test'@'localhost' identified by '新/旧密码' with grant option

         其中：identified by设置密码

                     with grant option：使该用户也拥有grant权限，能给别的用户设置权限

 

   3.3 删除权限：revoke select on 数据库.表名 fom 用户名@主机

 

4. 基本查看操作：

    4.0 查看mysql库的字符集 ：        show variables like "%char%"

          查看某个库的字符集：           show create database 库名

          查看某个表的字符集：           show create table 表名

          查看某个表的字段的字符集：show full columns from 表名

 

    4.1 查看库中表的具体情况（包括记录数，大小等）：show table status from 库名 like "%表名%";

    4.2 查看字段所有属性设置：show full columns from 表名( desc 表名 和 show columns from 表名  只是查看部分 )

    4.3 查看当前进入的数据库：select database();

    4.4 插入一个字段：alter table 表名 add 字段名 int(11) not null default '1' comment '这里是说明';

    4.5 插入多个字段：alter table 表名 add 字段名 int(11) not null default '1', add 字段名2 char(30) ; 

    4.6 修改字段方式：alter table 表名 modify 字段名 int(9) not nul default '2'

                                   alter table 表明 change 旧字段名 新字段名 int(11) not null default '1';

          (ps: 这两种方式都需要把字段设置的属性都写上，第二种则有修改字段名的功能)

    4.7 删除字段：alter table 表名 drop 字段名

    4.8.修改表名：alter table 旧表名 rename 新表名

    5.0 复制表的结构和数据，从而创建表：create table 表名 select * from 要复制的表名

    5.1 只复制表结构，从而创建表：create table 表名 like 要复制的表名

    5.2 修改表的注释说明：alter table 表名 comment "注释的内容"

    5.3 查看表大小和表索引大小：select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB from information_schema.tables where table_schema='库名' and table_name = '表名';  

 

5.  当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server；

     当选定了一个数据库时，character_set_database 被设定为这个数据库默认的字符集；
     在这个数据库里创建一张表时，表默认的字符集被设定为 character_set_database，也就是这个数据库默认的字符集；
     当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集

    5.1 创建数据库且指定库的默认字符

          create database test character set utf8;

    5.2 修改库的字符集

          alter database 库名 character set utf8;  (修改了库的字符集，表的字段字符集是不会跟随变化的)

 

    5.3 修改表的字段的字符集

          alter table 表名 default character set utf8/gb2312:修改表的默认  字符集，但是该修改只针对新增加的字段有效，旧字段依然用未修改前的字符集。

          alter table 表名 convert to character set utf8/gb2312:该修改字  符集对表的所有字段有效

 

    5.4 影响插入数据乱码的字符集有3个：终端字符集，库字符集，字段字符集 ，需要三者保持一致才行

         终端字符集：character_set_client 和 character_set_connection 和 character_set_result（可通过set names 字符集 来设置）

         库字符集： character_set_database 

         字段字符集 ：通过 show full columns from 表名 命名后，显示的collation列中查看

 

  6.索引创建 

    6.1.查看表的索引

          show index from 表名

          show keys from 表名

          其中：

           Table：       表的名称

           Non_unique：  如果索引不能包括重复词，则为0。如果可以，则为1。

           Key_name：    索引的名称。

           Seq_in_index：索引中的列序列号，从1开始。

           Column_name： 列名称。

           Collation：   列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

           Cardinality： 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

            Sub_part：    如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

            Packed：      指示关键字如何被压缩。如果没有被压缩，则为NULL。

            Null：        如果列含有NULL，则含有YES。如果没有，则该列含有NO。

            Index_type：  使用的索引类型（BTREE, FULLTEXT, HASH, RTREE）。

            Comment：     索引说明。

 

          6.2.创建索引2种语句

                 第一种：alter table 表名 add index 索引名称(字段名);

                 第二种：create index 索引名称 on 表名(字段名(索引长度)) //char和vachar类型是可以用字段的部分长度作为索引的，blog和text类型的，如果建立索引，就必须给予这个长度，不能够省略

  

                 唯一索引

                 alert table 表名 add unique index 索引名称(索引字段):它与普通索引类似，但不同的是，其索引列的值必须唯一，但允许有空值

  

                 主键

                 alter table 表名 add primary key 字段名: 它是一种特殊的唯一索引，且不允许有空值。一个表只能有一个主键索引。

 

                 多列主键：

                 alter table 表名 add primary key (字段1,字段2,.......)  

 

                 组合索引

                 alter table 表名 add index 索引名称(字段1,字段2....)

                 create index 索引名称 on 表名(字段1,字段2...)

 

          6.3 删除普通索引：

                alter table 表名 drop index 索引名称

                删除唯一索引：(注意：删除主键（主键是一种特殊的唯一索引）前如果该字段存在自增，则需要先删除自增属性,且一个表只能有一个字段为自增属性)   alter table 表名 drop index 索引名称

  

                删除主键：(注意：删除主键前如果该字段存在自增，则需要先删除自增属性,且一个表只能有一个字段为自增属性)     alter table 表名 drop primary key

 

                删除外键：(并非所有的存储引擎都支持外键索引，需注意)

               alter table 表名 drop foreign key 外键名      （注意：使用该命令删除了外键后，该字段还是一个普通索引，需要再使用删除普通索引的命令进行清除）

 

  7. 在查询语句没有输入完整前，如果不想继续执行，可使用 \c 来中断查询

 

  8. 修改mysql的root密码：

       8.1  在未进入mysql前使用以下命令： mysqladmin -uroot -p password 新密码， 会提示输入密码，输入的是旧的密码    （其中新密码需要使用双引号包起来）

 

        8.2   进入mysql后，修改mysql库中的user表中root用户的Password字段的值，然后使用 flush privileges语句来重新装载user表的数据

                例如：update mysql.user set Password = password("新密码") where User = "root" and Host = "localhost";

 

        8.3  使用set语句修改root密码：set password = password("新密码");

 

   9. root修改其他用户密码：

       9.1 使用set语句：set password for '用户名'@'主机名' = password("新密码");

 

       9.2 使用update语句更新mysql库的user中对应用户的Password字段，然后再用flush privilege语句

              例如：update mysql.user set Password = password("新密码") where User = "root" and Host = "localhost";

 

    10. 非root用户修改自己的密码：进入mysql后，使用set语句：set password for '用户名'@'主机名' = password("新密码");

 

    11. 使用mysqldump命令备份

           11.1 备份一个数据库：

                    mysql -u用户名 -p 数据库名 表名1 表名2 ... > 路径+文件名.后缀

                    例如：mysqldump -uroot -p bicloud bi_ad bi_business > /home/www/sql_bak/bi_ad_business.sql

 

           11.2 备份多个数据库（也就是备份不同数据库中的所有表）

                   mysqldump -u用户名 -p --databases 数据库名1 数据库名2 .... > 路径+文件名.后缀

 

           11.3 备份所有的数据库

                   mysqldump -u用户名 -p --all-databases > 路径+文件名.后缀

 

         11.4 复制整个数据库目录，但该方法只对myisam类型引擎的数据表有效

 

     12. 数据库表的还原(一般是针对mysqldump备份后的)

          12.1 mysql -u用户名 -p 数据库名 < 路径+备份文件  （其中数据库名不是必须的，如果在还原所有数据库时候，就不需要）

 

     13. 将数据表的数据导出到文本文件中

           13.1 进入mysql后，使用：select ... into outfile '文件名.后缀' [option]（select部分是正常的mysql语句查询出的结果， 该语句后面还可以带一些参数，具体查询相关资料）， 保存的文件一般是保存在数据库目录下对应的数据库文件夹中，比如通过yum安装的mysql的默认数据库目录是/var/lib/mysql/，该目录下对应的文件夹是各个数据库

           13.2 mysqldump -u用户名 -p用户密码 -T 存放的路径 数据库名 表名   既能保存表的“所有记录”到文本，同时生成含有sql语句的文件

         例如：mysqldump -uroot -pajia123 -T /var/lib/mysql/test/ test user

 

          13.3 使用mysql命令：mysql -u用户名 -p密码 -e "select语句" 数据库名 > 路径+文件名

         例如：mysql -uroot -pajia123 -e "select * from test.user" test > /home/ajia/mysql_outfile_dir/user.txt

 

    14. 导入文本文件到数据库中

        14.1 load data infile 文件 into table 表名 [option]

        14.2 mysqlimport -u用户名 -p用户密码 数据库名 文本文件 [option]

          例如：mysqlimport -uroot -pajia123 test /home/ajia/mysql_outfile_dir/user.txt

 在导入文本文件时候要确保每条数据间的分隔号是否跟导出该文本文件时候是一致的，不然就需要使用option选项来设置保持