mysql常用命令

標籤：variables   linu   ase   database   host   show   sch   rac   ror   

庫裡面有表，表裡面有欄位。比如更改密碼操作user表，操作的欄位是password
命令前加#號，命令不生效。

1.查詢庫有哪些資料庫 show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)

2.切換庫 use mysql;
mysql> use mysql;
Database changed

3.查看庫裡的表 show tables;
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log

4.查看錶裡的欄位 desc user;
mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------

5.查看建表語句 show create table tb_name\G;
mysql> show create table user\G; // \G豎排顯示
1. row
Table: user
Create Table: CREATE TABLE user (
Host char(60) COLLATE utf8_bin NOT NULL DEFAULT ‘‘,
User char(16) COLLATE utf8_bin NOT NULL DEFAULT ‘‘,
Password char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT ‘‘,
Select_priv enum(‘N‘,‘Y‘) CHARACTER SET utf8 NOT NULL DEFAULT ‘N‘,
Insert_priv enum(‘N‘,‘Y‘) CHARACTER SET utf8 NOT NULL DEFAULT ‘N‘,
Update_priv enum(‘N‘,‘Y‘) CHARACTER SET utf8 NOT NULL DEFAULT ‘N‘,
Delete_priv enum(‘N‘,‘Y‘) CHARACTER SET utf8 NOT NULL DEFAULT ‘N‘,
Create_priv enum(‘N‘,‘Y‘) CHARACTER SET utf8 NOT NULL DEFAULT ‘N‘,

6.查看目前使用者 select user();
mysql> select user();
+----------------+
| user() |
+----------------+
| [email protected] |//就是ip是127.0.0.1
+----------------+
1 row in set (0.54 sec)

7.記錄mysql的曆史命令
[[email protected] ~]# pwd
/root
[[email protected] ~]# ll -a .mysql_history
-rw------- 1 root root 994 12月 25 08:25 .mysql_history

8.查看當前使用的資料庫 select databsase();
mysql> use mysql;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)

9.建立庫 create database db1;
mysql> create database db1;
Query OK, 1 row affected (0.57 sec)

use db1;
mysql> use db1; //切換到db1
Database changed

10.建立表 create table t1(id int(4), name char(40));
mysql> create table t1(id int(4),name char(40)); //建立表
Query OK, 0 rows affected (0.38 sec)

11.mysql> show create table t1\G; //查看建立表的語句是什麼
1. row
Table: t1
Create Table: CREATE TABLE t1 (
id int(4) DEFAULT NULL,
name char(40) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 //預設InnoDB引擎 字元集是Latin1
1 row in set (0.57 sec)

12.mysql> create table t1(id int(4),name char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; //建立表時自訂字元集
Query OK, 0 rows affected (0.55 sec)
mysql> drop table t1; //刪除表t1
Query OK, 0 rows affected (0.64 sec)
13.查看當前資料庫版本 select version();
14.查看資料庫狀態 show status;
15.查看各參數（如my.cnf） show variables;
show variables like ‘max_connect%‘; //%通配 查看指定的參數
show variables like ‘max_connect%‘;
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 10 |
| max_connections | 151 |
+--------------------+-------+
2 rows in set (0.00 sec)

16.命令列修改參數 set global max_connect_errors=1000; //記憶體中生效。如果想要它重啟還是1000需要改設定檔vi /etc/my.cnf

17.查看隊列 show processlist; //相當於linux ps或者top
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 11 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

show full processlist; //最後一列非常完整。如哪些使用者在串連
mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 11 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

mysql常用命令