mysql 常用操作
2018-09-10
MySQL 常用操作
命令行下管理和操作 MySQL。
查看都有哪些库：> show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
查看某个库的表：> use db;show tables;
mysql> use mysql;show tables;
Database changed
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
23 rows in set (0.01 sec)
查看表的字段：> desc tb;
mysql> desc user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.05 sec)
查看建表语句：> show create table tb; （为过滤掉某些无用的信息，可以在语句末端加上 \G）
mysql> show create table host\G;
*************************** 1. row ***************************
Table: host
Create Table: CREATE TABLE `host` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`Db` char(64) COLLATE utf8_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',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
PRIMARY KEY (`Host`,`Db`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges; Merged with database privileges'
1 row in set (0.00 sec)
ERROR:
No query specified
查看当前用户：> select user();
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.02 sec)
查看数据库版本：> select version();
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)
查看当前库：> select database();
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
创建库：> create database db1;
mysql> create database discuz;
Query OK, 1 row affected (0.11 sec)
创建表：> create table tb1 (`id` int(4), `name` char(40)); （` ` 中表示字段 后面跟的 init 和 char 是格式，格式后面的是最大长度），例子中在创建表时设定了存储引擎和字符集
mysql> create table tb1 (`id` int(4), `name` char(40)) ENGINE=MyISAM DEFAULT CHARSET=gbk;
Query OK, 0 rows affected (0.00 sec)
插入数据：> insert into tb1 (id,name) values(1, 'user1');
mysql> insert into tb1 (id,name) values(1, 'user1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1 (name,id) values('user2',2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1 (id) values(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1 (name) values(user4);
ERROR 1054 (42S22): Unknown column 'user4' in 'field list'
mysql> insert into tb1 (name) values('user4');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1 (name,id) values('user4',6);
Query OK, 1 row affected (0.00 sec)
查询：> select * from tb1; （也可以通过 discuz.tb1 定位）
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | NULL |
| NULL | user4 |
| 6 | user4 |
+------+-------+
5 rows in set (0.01 sec)
更新数据：> update tb1 set id=5 where name='user4'; （也可 discuz.tb1 定位）
mysql> update tb1 set id=5 where name = 'user4';
Query OK, 2 rows affected (0.02 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | NULL |
| 5 | user4 |
| 5 | user4 |
+------+-------+
5 rows in set (0.00 sec)
删除行：> delete from tb1 where name='user4';
mysql> delete from tb1 where name='user4';
Query OK, 2 rows affected (0.00 sec)
mysql> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | user1 |
| 2 | user2 |
| 3 | NULL |
+------+-------+
3 rows in set (0.00 sec)
清空表：> truncate table tb1; (discuz.tb1)
mysql> truncate table tb1;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from tb1;
Empty set (0.00 sec)
删除表：> drop table tb1;
mysql> drop table tb1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tb1;
ERROR 1146 (42S02): Table 'discuz.tb1' doesn't exist
删除数据库：> drop database discuz;
mysql> drop database discuz;
Query OK, 0 rows affected (0.06 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
修复表：> repair table tb1 [use frm];
查看 MySQL 状态：> show status;
修改 MySQL 参数：> show variables like 'max_connect%';
> show global max_connect_errors = 1000;
查看 MySQL 队列：> show processlist;
创建普通用户并授权：> grant all on . to user1 identified by '1234';
> grant all on db1.* to 'user2'@'10.0.2.100' identified by '1234';
> grant all on db1.* to 'user3'@'%' identified by '1234';
更改密码：> UPDATE mysql.user SET password=PASSWORD("your passwd") WHERE user='username';
在 shell 的命令行模式执行 MySQL 操作：
# mysql -uroot -ptest123 mysql -e "show tables;"
-e 前面的 MySQL 指的是库的名字，-e 选项后面双引号内的是 MySQL 的命令。