本文主要给大家介绍运维版MySQL该如何增删改查，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下运维版MySQL该如何增删改查吧。
我们提供的服务有：成都网站设计、网站建设、外贸网站建设、微信公众号开发、网站优化、网站认证、苍梧ssl等。为上千家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的苍梧网站制作公司
1、查看有哪些用户
mysql> select user,host from mysql.user;
+------------+-----------+
| user | host |
+------------+-----------+
| root | 127.0.0.1 |
| mysql_data | localhost |
| root | localhost |
| zabbix | localhost |
+------------+-----------+
2、查看mysql版本 位数
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.32 |
+-----------+
mysql> show variables like '%version_%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| slave_type_conversions | |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | i686 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)
3、查看当前登录用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
4.查看二进制日志是否开启
mysql> show variables; 会把所有的mysql参数都显示出来
mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
5.查看默认存储引擎
mysql> show variables like 'storage_engine%';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
6.创建数据库
mysql> create database zabbix;
Query OK, 1 row affected (0.12 sec)
mysql> create database zabbix character set utf8; --->创建数据库并设置字符集
Query OK, 1 row affected (0.00 sec)
mysql> show databases; ---> 显示数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| testdb |
| zabbix |
+--------------------+
mysql> use zabbix; ---> 选择数据库
Database changed
mysql> show create database zabbix; ---> 查看建库的完整语句
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| zabbix | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
mysql> drop database zabbix; --->删除数据库
7.数据表
mysql> create table zabbix(user_id int primary key,user_name varchar(20),user_gender varchar(20)); --->创建一个表zabbix
Query OK, 0 rows affected (0.43 sec)
mysql> show tables; --->显示所有的表
+------------------+
| Tables_in_zabbix |
+------------------+
| zabbix |
+------------------+
mysql> desc zabbix; --->显示表结构
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| user_id | int(11) | NO | PRI | NULL | |
| user_name | varchar(20) | YES | | NULL | |
| user_gender | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
Insert intoTableName (列1,列2.... 列n） Values(值1,值2,....值n)
如果没有声明列明,则默认插入所有列.因此,值应该与全部列,按顺序一一对应.
mysql> insert into zabbix values('1','xiaoming','boy'); --->向表中插入一列数据
mysql> select * from zabbix;
+---------+-----------+-------------+
| user_id | user_name | user_gender |
+---------+-----------+-------------+
| 1 | xiaoming | boy |
| 2 | laowang | boy |
| 3 | marong | girl |
+---------+-----------+-------------+
Update 表名 Set 列1 = 新值 1,列2 = 新值2,列n = 新值n..... Where **
mysql> update zabbix set user_gender='girl' where user_id="1"; --->修改数据
mysql> select * from zabbix;
+---------+-----------+-------------+
| user_id | user_name | user_gender |
+---------+-----------+-------------+
| 1 | xiaoming | girl |
mysql> truncate zabbix; --->清空表数据
mysql> select * from zabbix;
Empty set (0.00 sec)
mysql> drop table zabbix; --->删除表
Query OK, 0 rows affected (0.05 sec)
mysql> desc zabbix.hosts;
+--------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| hostid | bigint(20) unsigned | NO | PRI | NULL | |
| proxy_hostid | bigint(20) unsigned | YES | MUL | NULL | |
| host | varchar(128) | NO | MUL | | |
| status | int(11) | NO | MUL | 0 | |
。。。。。。此处省略多行
mysql> select host from zabbix.hosts;
+---------------------------------+
| host |
+---------------------------------+
| AC-1 |
| AC-2 |
。。。。。。
mysql> select host,status from zabbix.hosts;
+---------------------------------+--------+
| host | status |
+---------------------------------+--------+
| Template OS Linux | 3 |
| Template App Zabbix Server | 3 |
| Template App Zabbix Proxy | 3 |
| Template App Zabbix Agent | 3 |
| Template SNMP Interfaces | 3 |
。。。。。。
mysql> select host,status from zabbix.hosts where status like '0';
+-----------------+--------+
| host | status |
+-----------------+--------+
| ddd | 0 |
| {#VM.UUID} | 0 |
| {#HV.UUID} | 0 |
| zabbix server | 0 |
。。。。。。
增加主键
alter table tbName add primary key(主键所在列名);
例:alter table goods add primary key(id)
该例是把主键建立在id列上
修改表之删除主键
alter table tbName drop primary key;
修改表之增加索引
alter table tbName add [unique|fulltext] index 索引名(列名);
修改表之删除索引
alter table tbName drop index 索引名;
8.备份数据库
mysqldump -u root -p zabbix>/zabbix.sql ----备份数据库zabbix
mysql -uroot -p zdj
mysqldump -uroot -p --all-databases >all2.sql 备份所有的库
mysql -uroot -p 9.用户授权管理 格式：grant权限 on数据库名.表名 to 用户@登录主机 identified by "用户密码"; @ 后面是访问mysql的客户端IP地址（或是 主机名） % 代表任意的客户端，如果填写 localhost 为本地访问（那此用户就不能远程访问该mysql数据库了）。 mysql> grant all privileges on*.*to zabbix@'%' identified by "123456"; mysql> show grants for zabbix\G; ---- 》查看创建用户的权限 *************************** 1. row *************************** Grants for zabbix@%: GRANT ALL PRIVILEGES ON *.* TO 'zabbix'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' mysql> delete from mysql.user where user='zabbix' and host='%';---> 删除用户 10.查看库大小： MariaDB [information_schema]> SELECT SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) FROM information_schema.tables WHERE TABLE_SCHEMA='drcom'; +------------------------------------+ | SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) | +------------------------------------+ | 26051771392 | +------------------------------------+ 1 row in set (0.42 sec) 结果是以字节为单位，除1024为K，除1048576为M。 11.查看表总数： SELECT count(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA='drcomweixin';--> mysql> select count(*) from mysql.user; 查看user表中有多少行 +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) 12.查看表大小： SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='库' AND TABLE_NAME='表名'; 13.show processlist; http://renxiangzyq.iteye.com/blog/835397 mysql线程数 [root@vps /]# mysqladmin processlist -uroot -p |wc -l Enter password: 24 14.查看最大连接数： [root@vps /]# mysql -uroot -p -e "show variables like '%max_connections%';" Enter password: +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | extra_max_connections | 1 | | max_connections | 1000 | +-----------------------+-------+ 15.查看当前连接数： [root@vps /]# mysql -uroot -p -e "show status like 'Threads%';" Enter password: +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 5 | | Threads_connected | 16 | | Threads_created | 1697 | | Threads_running | 3 | +-------------------+-------+ 16.mysql查看状态： mysql> show status; [root@vps ~]# mysqladmin -uroot -p***** status Warning: Using a password on the command line interface can be insecure. Uptime: 8135940 Threads: 17 Questions: 117931987 Slow queries: 0 Opens: 215 Flush tables: 1 Open tables: 208 Queries per second avg: 14.495 17.查看当前使用库： mysql> select database(); +------------+ | database() | +------------+ | mysql | +------------+ 1 row in set (0.00 sec) 18.修改密码： mysql> update mysql.user set password=password('123456') where user='root'; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 看完以上关于运维版MySQL该如何增删改查，很多读者朋友肯定多少有一定的了解，如需获取更多的行业知识信息 ，可以持续关注我们的行业资讯栏目的。
网页名称：运维版MySQL该如何增删改查
当前链接：http://bzwzjz.com/article/gcjshs.html