首页 / MYSQL / Mysql入门基础命令
Mysql入门基础命令
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql入门基础命令,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含14721字,纯文字阅读大概需要22分钟。
内容图文
![Mysql入门基础命令](/upload/InfoBanner/zyjiaocheng/502/aecab25d74e541c5bab334f19770030f.jpg)
mysql> desc db; +-----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+---------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | Db | char(64) | NO | PRI | | | | User | char(16) | NO | PRI | | | | 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 | | | 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 | | | Create_tmp_table_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Lock_tables_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 | | | Execute_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Event_priv | enum(‘N‘,‘Y‘) | NO | | N | | | Trigger_priv | enum(‘N‘,‘Y‘) | NO | | N | | +-----------------------+---------------+------+-----+---------+-------+ 22 rows in set (0.01 sec) #使用如下命令将建表语句全部列出来 mysql> show create table db\G; *************************** 1. row *************************** Table: db Create Table: CREATE TABLE `db` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT ‘‘, `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT ‘‘, `User` char(16) 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‘, `Event_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`,`User`), KEY `User` (`User`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘Database privileges‘ 1 row in set (0.00 sec)
1.4 查看当前登录的用户
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec)
1.5 查看当前正在使用的数据库
mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec)
1.6 创建一个新库
mysql> create database zabbix; Query OK, 1 row affected (0.38 sec)
1.7 创建一个新表
mysql> create table user(id int(4),name char(40),tel int(11)); Query OK, 0 rows affected (0.19 sec) mysql> show create table user\G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(4) DEFAULT NULL, `name` char(40) DEFAULT NULL, `tel` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
1.8 查看当前mysql数据版本
mysql> select version(); +------------+ | version() | +------------+ | 5.6.40-log | +------------+ 1 row in set (0.00 sec)
1.9 查看Mysql当前状态
mysql> show status; +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 0 | | Aborted_connects | 14 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 4 | | Bytes_received | 1343 | | Bytes_sent | 34853 | | Com_admin_commands | 0 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 0 | | Com_alter_tablespace | 0 | | Com_alter_user | 0 | | Com_analyze | 0 | | Com_begin | 0 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 2 |
1.10 查看Mysql参数
mysql> show variables\G; *************************** 1. row *************************** Variable_name: auto_increment_increment Value: 1 *************************** 2. row *************************** Variable_name: auto_increment_offset Value: 1 *************************** 3. row *************************** Variable_name: autocommit Value: ON *************************** 4. row *************************** Variable_name: automatic_sp_privileges Value: ON *************************** 5. row *************************** Variable_name: avoid_temporal_upgrade Value: OFF
1.11 修改Mysql的参数max_connect_errors
mysql> show variables like ‘max_connect%‘; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.01 sec) mysql> set global max_connect_errors = 1000; Query OK, 0 rows affected (0.00 sec) mysql> show variables like ‘max_connect%‘;#类似shell命令行*通配符 +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 1000 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec) #set global临时更改参数,如果永久修改则需要在/etc/my.cnf配置文件中修改
1.12 查看当前Mysql服务器的队列
mysql> show processlist; +----+------+------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+ | 2 | rep | 10.0.0.251:55984 | NULL | Binlog Dump | 3109 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 23 | root | localhost | zabbix | Query | 0 | init | show processlist | +----+------+------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec) #查看当前Mysql在做什么操作,也可以是否有锁表
1.13 创建一个普通用户并授权
mysql> grant all on *.* to zabbix identified by ‘123456‘; Query OK, 0 rows affected (0.00 sec) #本地创建一个zabbix用户,授权所有权限 mysql> grant all on zabbix.* to ‘zabbix01‘@‘10.0.0.251‘ identified by ‘123456‘; Query OK, 0 rows affected (0.01 sec) #指定特定的网络机器登录mysql数据库并授权zabbix数据库下的所有表 mysql> grant all on zabbix.* to ‘zabbix02‘@‘%‘ identified by ‘123456‘; Query OK, 0 rows affected (0.00 sec) #指定所有主机登录mysql数据库并授权zabbix数据库下的所有表 mysql> select user,host from mysql.user where user like ‘zabbix%‘; +----------+------------+ | user | host | +----------+------------+ | zabbix | % | | zabbix02 | % | | zabbix01 | 10.0.0.251 | +----------+------------+ 3 rows in set (0.00 sec)
二、Mysql基本操作
2.1 查询语句
第一种查询方式: mysql> select count(*) from mysql.user; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) 第二种查询方式: mysql> select * from mysql.user; mysql> select user,host from mysql.user; +-----------+------------+ | user | host | +-----------+------------+ | zabbix | % | | zabbix02 | % | | rep | 10.0.0.% | | zabbix01 | 10.0.0.251 | | root | 127.0.0.1 | | root | localhost | | wordpress | localhost | +-----------+------------+ 7 rows in set (0.00 sec) mysql> select user,host from mysql.user where user like ‘zabbix%‘; +----------+------------+ | user | host | +----------+------------+ | zabbix | % | | zabbix02 | % | | zabbix01 | 10.0.0.251 | +----------+------------+ 3 rows in set (0.00 sec)
2.2 插入语句
mysql> insert into zabbix.user values(3,‘test‘,1111111111),(4,‘boy‘,222222222); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from zabbix.user where name=‘test‘ or name=‘boy‘; +------+------+------------+ | id | name | tel | +------+------+------------+ | 3 | test | 1111111111 | | 4 | boy | 222222222 | +------+------+------------+ 2 rows in set (0.00 sec)
2.3 更新表语句
mysql> update zabbix.user set name=‘girl‘ where id=3; Query OK, 1 row affected (1.69 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from zabbix.user where id=3; +------+------+------------+ | id | name | tel | +------+------+------------+ | 3 | girl | 1111111111 | +------+------+------------+ 1 row in set (0.00 sec)
2.4 清空表语句
mysql> truncate table zabbix.user; Query OK, 0 rows affected (0.09 sec) mysql> select * from zabbix.user; Empty set (0.00 sec)
2.5 删除表语句
mysql> drop table zabbix.user; Query OK, 0 rows affected (0.34 sec) mysql> show tables; Empty set (0.00 sec)
2.6 删除数据库
mysql> show databases like ‘zabbix%‘; +--------------------+ | Database (zabbix%) | +--------------------+ | zabbix | +--------------------+ 1 row in set (0.00 sec) mysql> drop database zabbix; Query OK, 0 rows affected (0.12 sec) mysql> show databases like ‘zabbix%‘; Empty set (0.00 sec)
三、Mysql数据备份与恢复
3.1 Mysql备份
[root@web01 ~]# mysqldump -u root -p123456 wordpress > /tmp/wordpress_ $(date +%F).sql Warning: Using a password on the command line interface can be insecure. [root@web01 ~]# ls -l /tmp/ total 784 -rw-r--r-- 1 root root 801875 Nov 11 17:33 wordpress_2018-11-11.sql
3.2 Mysql恢复
#删除原wordpress数据库 [root@web01 ~]# mysql -u root -p123456 -e ‘drop database wordpress;‘ Warning: Using a password on the command line interface can be insecure. #查看原wordpress数据库是否删除成功 [root@web01 ~]# mysql -u root -p123456 -e "show databases"; Warning: Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | dev | | guohua | | jumpserver | | mysql | | oldboy | | oldgirl | | performance_schema | | spdb | +--------------------+ #创建空的wordpress数据库 [root@web01 ~]# mysql -u root -p123456 -e "create database wordpress"; Warning: Using a password on the command line interface can be insecure. #查看新的wordpress数据是否新建完成 [root@web01 ~]# mysql -u root -p123456 -e "show databases like ‘wordpress%‘"; Warning: Using a password on the command line interface can be insecure. +-----------------------+ | Database (wordpress%) | +-----------------------+ | wordpress | +-----------------------+ #从以备份的wordpress恢复wordpres数据库 [root@web01 ~]# mysql -u root -p123456 wordpress < /tmp/wordpress_2018-11-11.sql Warning: Using a password on the command line interface can be insecure. #检查wordpress库是否恢复成功 [root@web01 ~]# mysql -u root -p123456 -e "use wordpress;show tables;" Warning: Using a password on the command line interface can be insecure. +-------------------------+ | Tables_in_wordpress | +-------------------------+ | wolf_commentmeta | | wolf_comments | | wolf_links | | wolf_options | | wolf_postmeta | | wolf_posts | | wolf_term_relationships | | wolf_term_taxonomy | | wolf_termmeta | | wolf_terms | | wolf_usermeta | | wolf_users | +-------------------------+
Mysql入门基础命令
标签:数据备份与恢复 incr mil wait 基础命令 slave databases ESS second
本文系统来源:https://www.cnblogs.com/Wolf-Dreams/p/9942876.html
内容总结
以上是互联网集市为您收集整理的Mysql入门基础命令全部内容,希望文章能够帮你解决Mysql入门基础命令所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。