mysql常用命令

root
233
文章
0
评论
2021年3月16日17:23:23 评论 3492字阅读11分38秒

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)

 

 

 

 

 

 

继续阅读
weinxin
我的微信
这是我的微信扫一扫
  • 文本由 发表于 2021年3月16日17:23:23
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
Mysql 创建表 MySQL

Mysql 创建表

Mysql 创建表   Mysql创建数据库 CREATE DATABASE db_name //字符集 CHARACTER SET charset_name //校验规则 COLLATE ...
Mysql数据类型 MySQL

Mysql数据类型

Mysql数据类型   INT整形类型---属性 unsigned/signed 无符号/有符号 unsigned无符号,创建一个无符号的列 mysql> create table z...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: