mysql常用命令
查看当前数据库的版本
//在客户端内部查看客户端的版本命令 mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.20 | +-----------+ 1 row in set (0.00 sec) //不在客户端内部查看版本的方式 [root@kk01 ~]# mysql -V mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using EditLine wrapper
显示都有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
打开指定的数据库
mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
查看数据库里有哪些表
mysql> show tables;
+-----------------------------------------------+
| Tables_in_sys |
+-----------------------------------------------+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
| io_global_by_file_by_bytes |
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes |
| io_global_by_wait_by_latency |
| latest_file_io |
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
在一个库里想看其它数据库里的表命令
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> select database(); +------------+ | database() | +------------+ | sys | +------------+ 1 row in set (0.00 sec) mysql> show tables from mysql; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin |
查看自己所在的库
mysql> select database();
+------------+
| database() |
+------------+
| sys |
+------------+
1 row in set (0.00 sec)
数据库表的操作命令
创建表
mysql> create table he(
-> ip int,
-> name varchar(20));
Query OK, 0 rows affected (0.01 sec)
查看表的结构
mysql> desc he;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ip | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
表内插入数据
mysql> insert into he (ip,name) values(1,'Beijing'); Query OK, 1 row affected (0.10 sec) mysql> insert into he (ip,name) values(2,'Shanghai'); Query OK, 1 row affected (0.00 sec) mysql> select * from he; +------+----------+ | ip | name | +------+----------+ | 1 | Beijing | | 2 | Shanghai | +------+----------+ 2 rows in set (0.00 sec)
查看表里的数据
mysql> select * from he;
+------+----------+
| ip | name |
+------+----------+
| 1 | Beijing |
| 2 | Shanghai |
+------+----------+
2 rows in set (0.00 sec)
对表内数据进行修改
mysql> select * from he; +------+----------+ | ip | name | +------+----------+ | 1 | Beijing | | 2 | Shanghai | +------+----------+ 2 rows in set (0.00 sec) mysql> update he set name='Changzhou' where ip=2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update he set name='Changji' where ip=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from he; +------+-----------+ | ip | name | +------+-----------+ | 1 | Changji | | 2 | Changzhou | +------+-----------+ 2 rows in set (0.00 sec)
删除表内的数据
mysql> select * from he;
+------+-----------+
| ip | name |
+------+-----------+
| 1 | Changji |
| 2 | Changzhou |
| 3 | QQ |
| 4 | OO |
+------+-----------+
4 rows in set (0.00 sec)
mysql> delete from he where ip=4;
Query OK, 1 row affected (0.01 sec)
mysql> select * from he;
+------+-----------+
| ip | name |
+------+-----------+
| 1 | Changji |
| 2 | Changzhou |
| 3 | QQ |
+------+-----------+
3 rows in set (0.00 sec)
继续阅读

我的微信
这是我的微信扫一扫
评论