MySQL系列一

引言

无论是java开发,大数据开发,还是数据分析,甚至于一些运营管理中都会接触到数据库的问题,所以掌握好Mysql的基础知识,是非常极其以及特别重要的,接下来我将分批总结下日常用到的Mysql知识点,没有涵盖的部分欢迎各位留言补充.

1. MySQL管理

1.1 启动及关闭MySQL服务器

Windows系统下
在windows系统下,打开命令窗口(cmd),进入MySQL安装目录的bin目录.

启动:

cd c:/mysql/bin
mysqld --console

关闭:

cd c:/mysql/bin
mysqladmin -uroot shutdown

Linux系统下
启动:

• 检查MySQL服务器是否启动:

ps -ef | grep mysqld

• 如果MySQL已经启动,以上命令将输出mysql进程列表,如果mysql未启动,使用一下命令启动mysql服务器

root@host# cd /usr/bin
./mysqld_safe &

关闭:

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

1.2 MySQL用户设置

添加用户
只需在mysql数据库重的user表添加新用户即可.举个例子:用户名:CBLi,密码:DBLi1205,并授权用户可以进行select,insert,update操作权限

方式一:

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'CBLi', 
           PASSWORD('DBLi1205'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'CBLi';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | CBLi | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

在添加用户时，请注意使用MySQL提供的 PASSWORD() 函数来对密码进行加密。 你可以在以上实例看到用户密码加密后为： 6f8c114b58f2ce9e.
注意：在 MySQL5.7 中 user 表的 password 已换成了authentication_string。如果你是5.7以上的版本,请尝试下列方法添加新用户.

mysql> update mysql.user set authentication_string=password('CBLi1205') where user="CBLi";
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 1

注意：password() 加密函数已经在 8.0.11 中移除了，可以使用 MD5() 函数代替。
注意：在注意需要执行 FLUSH PRIVILEGES 语句。 这个命令执行后会重新载入授权表。
如果你不使用该命令，你就无法使用新创建的用户来连接mysql服务器，除非你重启mysql服务器。
你可以在创建用户时，为用户指定权限，在对应的权限列中，在插入语句中设置为 ‘Y’ 即可.

方式二:
通过SQL的 GRANT 命令，以下命令会给指定数据库TUTORIALS添加用户 smile ，密码为 smile123 。

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON peoplework.*
    -> TO 'smile'@'localhost'
    -> IDENTIFIED BY 'smile123';

以上命令会在mysql数据库中的user表创建一条用户信息记录。

注意: MySQL 的SQL语句以分号 ; 作为结束标识。

删除用户

DROP USER smile

重置用户名

RENAME USER CBLi TO ‘创意李公馆’;

查看权限

SHOW GRANTS FOR CBLi

刷新权限

FLUSH PRIVILEGES;

撤销权限

REVOKE 权限列表 ON 表名 FROM 用户名

撤销所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名

权限层级
要使用GRANT或REVOKE，您必须拥有GRANT OPTION权限，并且您必须用于您正在授予或撤销的权限。

• 全局层级
  全局权限适用于一个给定服务器中的所有数据库,mysql.user

GRANT ALL ON *.*
REVOKE ALL ON *.*

• 数据库层级
  数据库权限适用于一个给定数据库中的所有目标，mysql.db, mysql.host

RANT ALL ON db_name.*
REVOKE ALL ON db_name.*

• 表层级
  表权限适用于一个给定表中的所有列，mysql.talbes_priv

GRANT ALL ON db_name.tbl_name
REVOKE ALL ON db_name.tbl_name

• 列层级
  列权限适用于一个给定表中的单一列，mysql.columns_priv
  当使用REVOKE时，您必须指定与被授权列相同的列。

权限列表

权限	解释
ALL [PRIVILEGES]	– 设置除GRANT OPTION之外的所有简单权限
ALTER	允许使用ALTER TABLE
ALTER ROUTINE	更改或取消已存储的子程序
CREATE	允许使用CREATE TABLE
CREATE ROUTINE	创建已存储的子程序
CREATE TEMPORARY TABLES	允许使用CREATE TEMPORARY TABLE
CREATE USER	允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW	允许使用CREATE VIEW
DELETE	允许使用DELETE
DROP	允许使用DROP TABLE
EXECUTE	允许用户运行已存储的子程序
FILE	允许使用SELECT…INTO OUTFILE和LOAD DATA INFILE
INDEX	允许使用CREATE INDEX和DROP INDEX
INSERT	允许使用INSERT
LOCK TABLES	允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS	允许使用SHOW FULL PROCESSLIST
REFERENCES	未被实施
RELOAD	允许使用FLUSH
REPLICATION CLIENT	允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE	用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT	允许使用SELECT
SHOW DATABASES	显示所有数据库
SHOW VIEW	允许使用SHOW CREATE VIEW
SHUTDOWN	允许使用mysqladmin shutdown
SUPER	允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE	允许使用UPDATE
USAGE	“无权限”的同义词
GRANT OPTION	允许授予权限

1.3管理MySQL的命令

USE 数据库名
选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

mysql> use CBLi;
Database changed

SHOW DATABASES;
列出数据路管理系统的数据库列表

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| RUNOOB             |
| cdcol              |
| mysql              |
| onethink           |
| performance_schema |
| phpmyadmin         |
| test               |
| wecenter           |
| wordpress          |
+--------------------+
10 rows in set (0.02 sec)

SHOW TABLES;
显示指定数据库的所有表,使用该命令前需要使用 use 命令来选择要操作的数据库

mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl     |
| runoob_tbl       |
| tcount_tbl       |
+------------------+
3 rows in set (0.00 sec)

SHOW COLUMNS FROM 数据表;
显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

mysql> SHOW COLUMNS FROM runoob_tbl;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id       | int(11)      | NO   | PRI | NULL    |       |
| runoob_title    | varchar(255) | YES  |     | NULL    |       |
| runoob_author   | varchar(255) | YES  |     | NULL    |       |
| submission_date | date         | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

SHOW INDEX FROM 数据表;
显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

mysql> SHOW INDEX FROM runoob_tbl;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| runoob_tbl |          0 | PRIMARY  |            1 | runoob_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

SHOW TABLE STATUS LIKE [FROM db_name] [LIKE ‘pattern’] \G;
该命令将输出Mysql数据库管理系统的性能及统计信息。

mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

番外话
创意李公馆(Creative Brainstorming Of Li)是一个专门售卖减压益智类玩具的店铺,目前主打益智拼接系列,有3D木质立体拼图,乐高式拼图,店内产品兼具时尚感与科技感,精致的包装,独特的创意也是送礼的佳选.感兴趣的小伙伴可以打开淘宝扫描下方二维码查看更多商品详情.