MySQL常见管理操作

创建数据库

mysql> create database scott;
Query OK, 1 row affected (0.03 sec)

mysql> show databases like 'scott';
+------------------+
| Database (scott) |
+------------------+
| scott            |
+------------------+
1 row in set (0.00 sec)

查看建库语句
mysql> show create database scott\G
*************************** 1. row ***************************
       Database: scott
Create Database: CREATE DATABASE `scott` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
# 默认字符集
CHARACTER SET utf8 指定字符集

mysql> help show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

 

##### 创建gbk的数据库
mysql> create database scott_gbk default character set gbk collate  gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database scott_gbk\G
*************************** 1. row ***************************
       Database: scott_gbk
Create Database: CREATE DATABASE `scott_gbk` /*!40100 DEFAULT CHARACTER SET gbk */
1 row in set (0.00 sec)

### 创建utf-8的数据库
mysql> create database scott_utf8 default character set utf8 collate  utf8_chinese_ci;
Query OK, 1 row affected (0.00 sec)

　　

进入数据库
    mysql> use scott;
    Database changed
查看当前数据库
    mysql> select database();
    +------------+
    | database() |
    +------------+
    | scott      |
    +------------+
    1 row in set (0.00 sec)
查看当前版本
    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.5.32    |
    +-----------+
    1 row in set (0.00 sec)
查看当前用户
    mysql> select user();
    +----------------+
    | user()         |
    +----------------+
    | root@localhost |
    +----------------+
    1 row in set (0.01 sec)
查看当前时间
    mysql> select now();
    +---------------------+
    | now()               |
    +---------------------+
    | 2017-03-28 05:17:45 |
    +---------------------+
    1 row in set (0.00 sec)

删除MySQL系统多余账户
语法 drop user "user"@"主机域"

　　

给用户授权
'''
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

创建用户并授权
grant   all privileges  on dbname.*  to username@localhost   identified by 'passwd'
授权命令   对应权限       目标数据库    用户和客户端主机         用户密码

    mysql> grant all privileges on test.* to scot@'localhost' identified by 'ddbddb';
    Query OK, 0 rows affected (0.00 sec)

    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

    mysql> select user,host from mysql.user;
    +--------+-----------+
    | user   | host      |
    +--------+-----------+
    | root   | 127.0.0.1 |
    | scot | localhost |
    | root   | localhost |
    | system | localhost |
    +--------+-----------+
    4 rows in set (0.00 sec)
查看用户权限
mysql> show grants for scot@localhost;
    +---------------------------------------------------------------------------------------------------------------+
    | Grants for scot@localhost                                                                                   |
    +---------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'scot'@'localhost' IDENTIFIED BY PASSWORD '*7A5BB1003DB731A1C401B833362E5DAC30DBC857' |
    | GRANT ALL PRIVILEGES ON `test`.* TO 'scot'@'localhost'                                                      |
    +---------------------------------------------------------------------------------------------------------------+
创建用户然后在查看权限
mysql> create user oldgirl@localhost identified by 'ddbddb';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for oldgirl@localhost;
    +----------------------------------------------------------------------------------------------------------------+
    | Grants for oldgirl@localhost                                                                                   |
    +----------------------------------------------------------------------------------------------------------------+
    | GRANT USAGE ON *.* TO 'oldgirl'@'localhost' IDENTIFIED BY PASSWORD '*7A5BB1003DB731A1C401B833362E5DAC30DBC857' |
    +----------------------------------------------------------------------------------------------------------------+
授权
mysql> grant all on test.* to oldgirl@'localhost';
    Query OK, 0 rows affected (0.00 sec)

　　

MySQL 用户可以授权的权限有哪些
all privileges 有哪些权限 公共18个

mysql> REVOKE INSERT ON test.* FROM 'scot'@'localhost';
Query OK, 0 rows affected (0.00 sec)

[root@mangodb ~]# mysql -uroot -p'ddbddb' -e 'show grants for scot@localhost;' |grep -i grant |tail -1 |tr ',' '\n' > all.txt
[root@mangodb ~]# cat all.txt
GRANT
 SELECT 查询
 INSERT 插入
 UPDATE 更新
 DELETE 删除
 CREATE 创建库和表
 DROP   删除库和表
 REFERENCES
 INDEX  索引
 ALTER  修改
 CREATE TEMPORARY TABLES 创建临时表
 LOCK TABLES    锁表
 EXECUTE
 CREATE VIEW    创建视图
 SHOW VIEW  演示视图
 CREATE ROUTINE     创建过程
 ALTER ROUTINE      修改过程
 EVENT      事件
 TRIGGER    触发器

 mysql> select * from mysql.user\G;

'''
不能让别人修改表结构，库 表的修改都要有DBA在后台去改
企业环境如何授权用户权限？
1. 对于web链接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此，
在安装期间除了 select，insert，update，delete ，4个外 还需要create，drop
等比较危险的权限
2. 生成数据库后，要收回create，drop授权
# REVOKE create ON *.* FROM 'jeffrey'@'localhost';
# REVOKE drop ON *.* FROM 'jeffrey'@'localhost';