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數據庫