營運版MySQL增刪改查

標籤：mysql 增 刪 改 查

    整理是最好的記憶

    營運經常用到的sql語句，長期更新~~~~~

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()         |

+----------------+

| [email protected] |

+----------------+

4.查看二進位日誌是否開啟

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 into TableName (列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)

增加主鍵

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</mysql/zdj.sql ----恢複資料庫

mysqldump -uroot -p --all-databases >all2.sql 備份所有的庫

mysql -uroot -p <all2.sql 恢複所有的庫

9.使用者授權管理

格式：grant 許可權 on 資料庫名.表名 to 使用者@登入主機 identified by "使用者密碼";

@ 後面是訪問mysql的用戶端IP地址（或是 主機名稱） % 代表任意的用戶端，如果填寫 localhost 為本地訪問（那此使用者就不能遠端存取該mysql資料庫了）。

mysql> grant all privileges on *.* to [email protected]‘%‘ identified by "123456";

mysql> show grants for zabbix\G;   ---- 》查看建立使用者的許可權

*************************** 1. row ***************************

Grants for [email protected]%: 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‘;-->

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線程數

[[email protected] /]# mysqladmin processlist -uroot -p |wc -l

Enter password:

24

14.查看最大串連數

[[email protected] /]# mysql -uroot -p -e "show variables like ‘%max_connections%‘;"

Enter password:

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| extra_max_connections | 1     |

| max_connections       | 1000  |

+-----------------------+-------+

15.查看當前串連數

[[email protected] /]# 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;

[[email protected] ~]# 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

~~~~~未完待續

本文出自 “拔電源的營運空間” 部落格，請務必保留此出處http://zhangdj.blog.51cto.com/9210512/1878480

營運版MySQL增刪改查