Linux下mysql数据库常用命令一
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Linux下mysql数据库常用命令一，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含16494字，纯文字阅读大概需要24分钟。
内容图文
真实服务器下环境操作：192.168.24.37192.168.24.37(Server)081119
Last login: Tue Aug 11 15:08:10 2015 from 192.168.24.1
#远程连接数据库
[root@jxatei ~]# mysql -u root -h 117.40.239.9 -p #远程连接数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 948
Server version: 5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
#常用数据库命令操作
mysql> show databases; #显示所有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| back20150625ultrax |
| feifeicms |
| mysql |
| performance_schema |
| test |
| tt |
| ultrax |
+--------------------+
8 rows in set (0.01 sec)
mysql> use mysql; #打开mysql数据库
Database changed
mysql> show tables; # 显示mysql数据库中的所有表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log | |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.01 sec)
mysql> desc user; #查看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 | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)
mysql> select user(); #查看当前是哪个用户？
+----------------------+
| user() |
+----------------------+
| root@115.151.218.160 |
+----------------------+
1 row in set (0.01 sec)
mysql> select database(); #查看当前所使用的数据库
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.01 sec)
mysql> select version(); #查看当有数据库所使用的版本
+----------------+
| version() |
+----------------+
| 5.5.41-cll-lve |
+----------------+
1 row in set (0.01 sec)
mysql> show status; #查看当前mysql运行的状态
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 1 |
| Aborted_connects | 385 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 745 |
| Bytes_sent | 19679 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Uptime | 15954 |
| Uptime_since_flush_status | 15954 |
+------------------------------------------+-------------+
323 rows in set (0.01 sec)
mysql> show variables; #查看mysql的参数
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| version | 5.5.41-cll-lve |
| version_comment | MySQL Community Server (GPL) by Atomicorp |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| wait_timeout | 28800 |
| warning_count | 0 |
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
331 rows in set (0.01 sec)
mysql> show processlist; #查看当前mysql的服务队列
+-----+------+----------------------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+----------------------+-------+---------+------+-------+------------------+
| 948 | root | 115.151.218.160:2088 | mysql | Query | 0 | NULL | show processlist |
+-----+------+----------------------+-------+---------+------+-------+------------------+
1 row in set (0.01 sec)
mysql> create database study; #创建一个study数据库
Query OK, 1 row affected (0.14 sec)
mysql> use study; #打开study数据库
Database changed
mysql> create table t1(id int(4),name char(40),sex char(2)); #创建一个t1表
Query OK, 0 rows affected (0.10 sec)
#mysql查询语句
mysql> select * from mysql.user; #查询
mysql> insert into t1 values(1,‘stu‘,20); #插入一条数据
Query OK, 1 row affected (0.05 sec)
mysql> update t1 set name=‘study‘ where id=1; #修改一条记录
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[root@sky9896 skyboy]# mysqldump -u root -p study >/home/skyboy/study.sql #数据备份
Enter password:
[root@sky9896 skyboy]# ll
总用量 26132
-rw-r--r-- 1 root root 1842 8月 11 19:52 study.sql
[root@sky9896 skyboy]# mysql -u root -p study </home/skyboy/study.sql #数据恢复
Enter password:
mysql> drop table t1; #删除表
Query OK, 0 rows affected (0.05 sec)
mysql> drop database study ; #删除数据库
Query OK, 0 rows affected (0.01 sec)
mysql> show databases; #显示数据库，study不存了
+--------------------+
| Database |
+--------------------+
| information_schema |
| back20150625ultrax |
| feifeicms |
| mysql |
| performance_schema |
| test |
| tt |
| ultrax |
+--------------------+
8 rows in set (0.01 sec)
mysql> create database study #重新创建一个study数据库
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> use study;
Database changed
mysql> show tables; #目前是空表
Empty set (0.01 sec)
[root@sky9896 skyboy]# mysql -u root -p study </home/skyboy/study.sql #数据恢复
Enter password:
mysql> show tables; #通过恢复数据过，能看到有t1表了
+-----------------+
| Tables_in_study |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.01 sec)
192.168.24.37192.168.24.37(Server)081119
Last login: Tue Aug 11 15:08:10 2015 from 192.168.24.1
#远程连接数据库
[root@jxatei ~]# mysql -u root -h 117.40.239.9 -p #远程连接数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 948
Server version: 5.5.41-cll-lve MySQL Community Server (GPL) by Atomicorp
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
#常用数据库命令操作
mysql> show databases; #显示所有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| back20150625ultrax |
| feifeicms |
| mysql |
| performance_schema |
| test |
| tt |
| ultrax |
+--------------------+
8 rows in set (0.01 sec)
mysql> use mysql; #打开mysql数据库
Database changed
mysql> show tables; # 显示mysql数据库中的所有表
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| user |
+---------------------------+
24 rows in set (0.01 sec)
mysql> desc user; #查看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 | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)
mysql> select user(); #查看当前是哪个用户？
+----------------------+
| user() |
+----------------------+
| root@115.151.218.160 |
+----------------------+
1 row in set (0.01 sec)
mysql> select database(); #查看当前所使用的数据库
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.01 sec)
mysql> select version(); #查看当有数据库所使用的版本
+----------------+
| version() |
+----------------+
| 5.5.41-cll-lve |
+----------------+
1 row in set (0.01 sec)
mysql> show status; #查看当前mysql运行的状态
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 1 |
| Aborted_connects | 385 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| version_compile_os | Linux |
| wait_timeout | 28800 |
| warning_count | 0 |
+---------------------------------------------------+------------------------------------------------------------------------------------------------------------------------+
331 rows in set (0.01 sec)
mysql> show processlist; #查看当前mysql的服务队列
+-----+------+----------------------+-------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+----------------------+-------+---------+------+-------+------------------+
| 948 | root | 115.151.218.160:2088 | mysql | Query | 0 | NULL | show processlist |
+-----+------+----------------------+-------+---------+------+-------+------------------+
1 row in set (0.01 sec)
mysql> create database study; #创建一个study数据库
Query OK, 1 row affected (0.14 sec)
mysql> use study; #打开study数据库
Database changed
mysql> create table t1(id int(4),name char(40),sex char(2)); #创建一个t1表
Query OK, 0 rows affected (0.10 sec)
#mysql查询语句
mysql> select * from mysql.user; #查询
mysql> insert into t1 values(1,‘stu‘,20); #插入一条数据
Query OK, 1 row affected (0.05 sec)
mysql> update t1 set name=‘study‘ where id=1; #修改一条记录
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
[root@sky9896 skyboy]# mysqldump -u root -p study >/home/skyboy/study.sql #数据备份
Enter password:
[root@sky9896 skyboy]# ll
总用量 26132
-rw-r--r-- 1 root root 1842 8月 11 19:52 study.sql
[root@sky9896 skyboy]# mysql -u root -p study </home/skyboy/study.sql #数据恢复
Enter password:
mysql> drop table t1; #删除表
Query OK, 0 rows affected (0.05 sec)
mysql> drop database study ; #删除数据库
Query OK, 0 rows affected (0.01 sec)
mysql> show databases; #显示数据库，study不存了
+--------------------+
| Database |
+--------------------+
| information_schema |
| back20150625ultrax |
| feifeicms |
| mysql |
| performance_schema |
| test |
| tt |
| ultrax |
+--------------------+
8 rows in set (0.01 sec)
mysql> create database study; #重新创建一个study数据库
Query OK, 1 row affected (0.00 sec)
mysql> use study;
Database changed
mysql> show tables; #目前是空表
Empty set (0.01 sec)
[root@sky9896 skyboy]# mysql -u root -p study </home/skyboy/study.sql #数据恢复
Enter password:
mysql> show tables; #通过恢复数据过，能看到有t1表了
+-----------------+
| Tables_in_study |
+-----------------+
| t1 |
+-----------------+
1 row in set (0.01 sec)
本文出自 “Linux~DBA~MBA~EDU” 博客，请务必保留此出处http://sky9896.blog.51cto.com/2330653/1683785
Linux下mysql数据库常用命令一
标签：linux mysql 命令
本文系统来源：http://sky9896.blog.51cto.com/2330653/1683785
内容总结
以上是互联网集市为您收集整理的Linux下mysql数据库常用命令一全部内容，希望文章能够帮你解决Linux下mysql数据库常用命令一所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。