MySQL命令讲析

show

显示数据库、表、列的信息和云服务器状态

SHOW has many forms that provide information about databases, tables,

columns, or status information about the server.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| test               |

+--------------------+

4 rows in set (0.00 sec)

显示所有数据库

mysql> show grants for oldboy@'localhost';  

+---------------------------------------------------------------------------------------------------------------+

| Grants for oldboy@localhost                                                                                   |

+---------------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'oldboy'@'localhost' IDENTIFIED BY PASSWORD '*FE28814B4

+---------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

显示用户权限USAGE表示没有权限

mysql> show databases like '%my%';

+-----------------+

| Database (%my%) |

+-----------------+

| mysql           |

+-----------------+

1 row in set (0.00 sec)

模糊匹配

mysql> show create database oldboy;

显示创建数据库的语句

show CHARACTER SET;

显示gbk的校对规则

mysql> show tables;  

+------------------+

| Tables_in_oldboy |

+------------------+

| student          |

+------------------+

1 row in set (0.00 sec)

#查看表

mysql> show create table student\G  

*************************** 1. row ***************************

      Table: student

Create Table: CREATE TABLE `student` (

 `id` int(4) NOT NULL,

 `name` char(20) NOT NULL,

 `are` tinyint(2) NOT NULL DEFAULT '0',

 `dept` varchar(16) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

#查看创建表语句

mysql> show index from student\G  

*************************** 1. row ***************************

       Table: student

  Non_unique: 0

    Key_name: PRIMARY    #索引类型

Seq_in_index: 1

 Column_name: id     #索引列名称

   Collation: A

 Cardinality: 0

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

Index_comment:

#查看表的索引

select

select user,host from mysql.user;

查询mysql库的user表的用户

mysql> select database();  

+------------+

| database() |

+------------+

| NULL       |

+------------+

1 row in set (0.00 sec)

查看当前数据库

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.01 sec)

查看当前用户

mysql> select version();  

+------------+

| version()  |

+------------+

| 5.5.32-log |

+------------+

1 row in set (0.00 sec)

查看数据库版本

mysql> select now();  

+---------------------+

| now()               |

+---------------------+

| 2018-04-10 22:25:11 |

+---------------------+

1 row in set (0.00 sec)

查看当前时间

mysql> select count(distinct user) from mysql.user;

+----------------------+

| count(distinct user) |

+----------------------+

|                    7 |

+----------------------+

1 row in set (0.01 sec)

统计表记录的唯一值的命令

mysql> select * from test;  

+----+--------+

| id | name   |

+----+--------+

|  1 | oldboy |

+----+--------+

1 row in set (0.00 sec)

查看表所有数据

mysql> select * from test limit 2;  

+----+--------+

| id | name   |

+----+--------+

|  1 | oldboy |

|  2 | old111 |

+----+--------+

2 rows in set (0.00 sec)

查询表的前2行

mysql> select * from test limit 2,3;

+----+------+

| id | name |

+----+------+

|  3 | kk   |

|  4 | zuma |

+----+------+

2 rows in set (0.00 sec)

查询表的2到3行

mysql> select * from test where id=1;

+----+--------+

| id | name   |

+----+--------+

|  1 | oldboy |

+----+--------+

1 row in set (0.00 sec)

按条件查询表的数据

mysql> select * from test where id>2;

+----+------+

| id | name |

+----+------+

|  3 | kk   |

|  4 | zuma |

+----+------+

2 rows in set (0.00 sec)

条件范围查询表数据

mysql> select * from test where id>2 and id<5;  

+----+------+

| id | name |

+----+------+

|  3 | kk   |

|  4 | zuma |

+----+------+

2 rows in set (0.00 sec)

条件范围查询表数据

mysql> select id,name from test where id>1 and id<5 order by id desc into outfile '/tmp/a.txt';

Query OK, 3 rows affected (0.00 sec)

#导出表数据

explain

mysql> explain select * from test where name='oldboy'\G  

*************************** 1. row ***************************

          id: 1

 select_type: SIMPLE

       table: test

        type: ref

possible_keys: index_name  #已使用，没使用为NULL

         key: index_name    #已使用

     key_len: 60

         ref: const

        rows: 1

       Extra: Using where; Using index

1 row in set (0.00 sec)

#查看表字段是否使用索引

[root@db02 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "show full processlist;"|egrep -vi "sleep"

Id User Host db Command Time State Info

7 root localhost NULL Query 0 NULL show full process

抓取慢查询语句

desc

mysql> desc student;  

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| id    | int(4)      | NO   |     | NULL    |       |

| name  | char(20)    | NO   |     | NULL    |       |

| are   | tinyint(2)  | NO   |     | 0       |       |

| dept  | varchar(16) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

查看表结构

update

UPDATE语法可以用新值更新原有表行中的各列.SET子句指示要修改哪些列和要给予哪些值。WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。如果指定了ORDER BY子句，则按照被指定的顺序对行进行更新。

LIMIT子句用于给定一个限值，限制可以被更新的行的数目。

For the single-table syntax, the UPDATE statement updates columns of

existing rows in the named table with new values. The SET clause

indicates which columns to modify and the values they should be given.

Each value can be given as an expression, or the keyword DEFAULT to set

a column explicitly to its default value. The WHERE clause, if given,

specifies the conditions that identify which rows to update. With no

WHERE clause, all rows are updated. If the ORDER BY clause is

specified, the rows are updated in the order that is specified. The

LIMIT clause places a limit on the number of rows that can be updated

mysql> update mysql.user SET password=PASSWORD("oldboy123") WHERE user='root' and host='localhost';

update(修改) mysql.user(mysql数据库的user表) SET(指定哪列？改password列，要给予哪些值？PASSWORD值) password=PASSWORD("oldboy123") （改成oldboy123,PASSWORD必须加因为这个是加密的）WHERE(对谁进行更改？) user='root'（对root用户） and host='localhost';(和主机localhost修改)

mysql> update test set name='inct' where id=3;  

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

#修改id为3的name，修改表一定要加条件

mysql> update test set name='inct';

Query OK, 4 rows affected (0.00 sec)

Rows matched: 5  Changed: 4  Warnings: 0

#如果不加条件是全部修改，这种问题是致命的。

alter

修改语句

mysql> alter table student change id id int primary key auto_increment;  

#修改主键索引

mysql> alter table student drop index index_name;  

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

#删除普通索引

mysql> alter table student add index index_name(name);

Query OK, 0 rows affected (0.09 sec)

Records: 0  Duplicates: 0  Warnings: 0

修改表的普通索引

drop

mysql> drop user ""@localhost;

Query OK, 0 rows affected (0.00 sec)

删除用户

mysql> drop database oldboy_gbk;  

Query OK, 0 rows affected (0.01 sec)

删除数据库

drop table student;  

删除之前建的表

delete

删除语句

For the single-table syntax, the DELETE statement deletes rows from

tbl_name and returns a count of the number of deleted rows.

mysql> delete from test where id=1;  

Query OK, 1 row affected (0.00 sec)

#删除表指定的ID

create

创建语句

mysql> create database oldboy;

创建一个oldboy的数据库

mysql> create database oldboy_gbk  CHARACTER SET gbk COLLATE gbk_chinese_ci;

创建gbk数据库

mysql> create user 'oldboy'@'localhost' identified by 'oldboy123';  

Query OK, 0 rows affected (0.01 sec)

#创建用户

create table student(  

id int(4) not null,

name char(20) not null,

are tinyint(2) NOT NULL default '0',

dept varchar(16) default NULL

);

创建表

mysql> create index index_dept on student(dept(8));

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

创建表某列的前8个字节的普通索引

mysql> create index index_name_dept on student(name,dept);  

Query OK, 0 rows affected (0.05 sec)

Records: 0  Duplicates: 0  Warnings: 0

创建表的联合索引

mysql> create index index_name_dept on student(name(8),dept(10));

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

创建表的联合索引前N个字符

mysql> create unique index uni_ind_name on student(name);  

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

#创建表唯一索引

grant

授权MYSQL用户权限和指定特殊权限

The GRANT statement grants privileges to MySQL user accounts. GRANT

also serves to specify other account characteristics such as use of

secure connections and limits on access to server resources. To use

GRANT, you must have the GRANT OPTION privilege, and you must have the

privileges that you are granting.

mysql> grant all on oldboy.* to oldboy@'localhost';

Query OK, 0 rows affected (0.01 sec)

 #授权给oldboy用户

mysql> grant all on oldboy.* to 'oldboy11'@'localhost' identified by 'oldboy123';

Query OK, 0 rows affected (0.00 sec)

创建用户和密码并且授权

insert

插入语句

INSERT inserts new rows into an existing table. The INSERT ... VALUES

and INSERT ... SET forms of the statement insert rows based on

explicitly specified values. The INSERT ... SELECT form inserts rows

selected from another table or tables. INSERT ... SELECT is discussed

further in [HELP INSERT SELECT].

mysql> insert into test (id,name) values(1,'oldboy');

Query OK, 1 row affected (0.00 sec)

插入id=1 name=oldboy的内容到test表中

REVOKE

撤销权限

The REVOKE statement enables system administrators to revoke privileges

from MySQL accounts. Each account name uses the format described in

mysql> REVOKE INSERT ON oldboy.* FROM 'oldboy'@'localhost';  

撤销插入权限

truncate

mysql> truncate table test;  

#清空表数据

flush

刷新语句

The FLUSH statement has several variant forms that clear or reload

various internal caches, flush tables, or acquire locks. To execute

FLUSH, you must have the RELOAD privilege. Specific flush options might

require additional privileges, as described later.

mysql> flush privileges;

use

切换数据库相等于CD

mysql> use oldboy  

Database changed

切换到oldboy数据库