MySQL Common commands

There are tables in the library and there are fields in the table. For example, change the password Operation user table, the operation of the field is password
The command does not take effect until the # sign is added.

1. What database does the query library show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

2. Switch the library use MySQL;
mysql> use MySQL;
Database changed

3. View the table in the library show tables;
Mysql> Show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| Columns_priv |
| db |
| Event |
| Func |
| General_log

4. View the fields in the table desc user;
mysql> desc User;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------

5. View the Build Table statement show create TABLE tb_name\g;
Mysql> Show CREATE TABLE user\g; \g Vertical Row Display
1. Row
Table:user
Create table:create Tableuser(
Hostchar (COLLATE) utf8_bin not NULL DEFAULT ' ',
Userchar (+) COLLATE utf8_bin not NULL DEFAULT ' ',
Passwordchar (CHARACTER) SET latin1 COLLATE latin1_bin not NULL DEFAULT ' ',
Select_privEnum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',
Insert_privEnum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',
Update_privEnum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',
Delete_privEnum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',
Create_privEnum (' n ', ' Y ') CHARACTER SET UTF8 not NULL DEFAULT ' N ',

6. Check the current user select User ();
Mysql> Select User ();
+----------------+
| User () |
+----------------+
| [Email protected] |//is IP is 127.0.0.1
+----------------+
1 row in Set (0.54 sec)

7. Record MySQL's history command
[Email protected] ~]# pwd
/root
[Email protected] ~]# ll-a. mysql_history
-RW-------1 root root 994 December 08:25. mysql_history

8. View the database currently in use Select Databsase ();
mysql> use MySQL;
Database changed
Mysql> Select Database ();
+------------+
| Database () |
+------------+
| MySQL |
+------------+
1 row in Set (0.00 sec)

9. Creating the library create database db1;
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.57 sec)

Use DB1;
mysql> use DB1; Switch to 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;//View what is the statement that created the table
1. Row
table:t1
Create table:create TABLE T1 (
ID int (4) default NULL,
name char (+) default NULL
) engine= InnoDB default charset=latin1//InnoDB engine character set is Latin1
1 row in Set (0.57 sec)

12.mysql> CREATE TABLE T1 ( id int (4), name char (+)) Engine=innodb DEFAULT Charset=utf8;//Custom Character set when creating table
Query OK, 0 rows affected (0.55 sec)
mysql> drop table T1; Delete Table T1
Query OK, 0 rows affected (0.64 sec)
13. View the current database version select version ();
14. View the status of the database show status;
15. View each parameter (e.g. MY.CNF) show variables;
Show variables like ' max_connect% '; % pass to view specified parameters
Show variables like ' max_connect% ';
+--------------------+-------+
| variable_name | Value |
+--------------------+-------+
| max_connect_errors | 10 |
| max_connections | 151 |
+--------------------+-------+
2 rows in Set (0.00 sec)

16. Command line modify parameter set global max_connect_errors=1000; The memory is in effect. If you want it to restart or 1000 need to change the configuration file vi/etc/my.cnf

17. View queue show processlist;//equivalent to Linux PS or top
mysql> show processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| one | root | localhost | NULL | Query | 0 | NULL | Show Processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in Set (0.00 sec)

Show full processlist; The last column is very complete. such as which users are connected
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 Common commands