MySQL 使用方法(一)

1 MySQL 的安装

在 Ubuntu 下:
sudo apt-get install mysql-server

在 MacOS 下,可以通过手动下载包进行安装,也可以用 homebrew 工具。对于后者,在安装结束后,还需做适当配置。

2 MySQL 的 root 用户

在安装完 mysql 后,会自动建立一个名为 mysql 的系统用户,一个名为 root 的 postgresql 用户,一个名为 mysql 的数据库。

mysql 中的 root 用户与系统中的 root 用户是两个不同的用户,前者只针对于 mysql。不过 mysql 中的 root 用户的性质和系统中的 root 用户类似,具有对 mysql 的超级访问权限。

Ubuntu 下安装 MySQL 的时候,会提示输入 root 用户的密码。如果之后想修改 root 用户的密码,则可以使用如下命令:
mysqladmin -u root -p'old_password' passowrd 'new_password'

注意,-p 后面没有空格。

如果安装的时候没有提示输入 root 用户的密码,则安装完成后可以通过如下命令设置 root 用户的密码:
mysqladmin -u root password 'new_password'

mysqladmin 是 mysql 提供的一个管理 mysql 用户信息,数据库等的命令。在用这个命令时,需要用以某个 mysql 用户登录 mysql(-u 和 -p 指定),然后再执行修改命令等操作。

这里除了用 mysqladmin 命令,还可以先登录 mysql,然后再用 SET PASSWORD 语句进行修改,推荐用此法。详见后面小节。

3 MySQL Shell

mysql shell 提供了一个用户和 mysql 之间的交互环境,包括登录,用户信息管理,数据库管理等操作。

通过在终端输入
mysql
进入 mysql shell。

4 登录

mysql -h hostname -P port -u username -ppassword database

其中:

  • -h 指定 mysql 服务器所在主机的主机名或者主机 IP。如果是在服务器上登录,可以不用指定;

    要想在某台主机上远程登录,一定要先在这台主机上安装 mysql 客户端。

    而且,用于远程访问的 mysql 用户必须指定该用户对应的主机字段必须为 ***%*** 或者为客户端的 IP 的地址。
    另外,如果是远程登录,还需要事先在 mysql 服务器上进行配置:
    `sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf`
    注释掉 ***bind-address*** 这句。
    
  • -u 指定用户名;
  • -p 指定密码。可以在 -p 后面指定,也可以暂时不用在 -p 后指定(但是 -p 不能省略),这样该命令执行的时候会提示用户输入密码。
>   需要注意的是,如果在 -p 后面立即指定密码,那么 -p 和密码之间不能有多余的空格。
  • 最后一个字段为数据库名,这里也可以不用立即指定,而是等到登录成功后,用 USE 操作指定。

5 用户管理

由于 root 具有添加,删除用户等所有权限,因此首先用 root 用户登录 mysql。

5.1 添加用户

CREATE USER 语句添加用户:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

其中,localhost 表示该用户只能在本地连接到该数据库;如果想让该用户从任何主机上连接到该数据库,则可以用通配符 %;如果相让该用户从某一特定主机上连接到该数据库,则这里写上主机名或者主机的 IP 地址。

5.2 赋予权限

GRANT 语句为用户赋予权限:
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

其中,

  • ALL PRIVILEGES 表示所有的权限,也可以赋予某几个权限。比如,SELECT, INSERT, UPDATE, DELETE, CREATE, DROP 等,但是不包括 GRANT OPTION(赋予其他用户权限的权限)。

    详见:MySQL 5.7 Reference Manual

  • \.** 表示所有的数据库的所有表,这里用的通配符 ***
  • ‘username‘@’localhost’ 必须存在,否则该操作会提示出错。比如,‘username‘@’%’ 表示可以在所有主机上通过该用户连接到数据库。
  • WITH GRANT OPTION 表示该用户拥有赋予其他用户权限的权限。

拥有 CREATE USER 权限的用户可以创建新的用户,但是只有具有 GRANT OPTION 权限的用户才能为某用户赋予权限。
GRANT 操作所赋予的权限必须在当前用户拥有的权限之内。假设当前用户对某个数据库不具有 DELETE 权限,那么即使这个用户拥有 GRANT OPTION 权限,也不能赋予某个用户 ALL PRIVILEGES(因为当前用户没有 DELETE 权限)。
另外,GRANT 操作是一个追加性的操作,而非重置性的。

当更改用户信息或者权限信息后,一般需要刷新这些信息:
FLUSH PRIVILEGES;

5.3 撤销权限

REVOKE 语句撤销某用户的权限:
REVOKE SELECT ON *.* FROM 'username'@'localhost';

5.4 删除用户

DROP USER 语句删除用户:
DROP USER 'username'@'localhost';

5.5 显示用户信息

所有的用户信息存储在数据库名为 mysql 中的 user 表里的。该表里有 Host, User 等字段。

显示所有用户:
SELECT Host, User from mysql.user;

显示当前用户:
SELECT USER();

SHOW CREATE USER 语句显示某个用户的非权限信息:
SHOW CREATE USER 'username'@'localhost';

SHOW GRANTS 语句显示某个用户的权限信息:
SHOW GRANTS FOR 'username'@'localhost';

5.5 更改用户

5.5.1 更改主机字段

更改用户的主机字段信息(改为 %),相当于是更改 mysql.user 表中某个用户对应的 Host 字段的信息:
UPDATE mysql.user SET Host = '%' where User = 'username';

5.5.2 更改用户密码

可用 ALTER USER 语句更改:
ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password';

或者用 SET PASSWORD FOR 语句更改:
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new_password');

或者在系统 shell 里执行如下命令:
mysqladmin -u username -p'old_password' password 'new_password'

推荐用第一种方法。

6 其他细节

  • 键入 ctr + d 或者 exit 退出 mysql 命令行环境;
  • 每一条 sql 语句必须以分号结尾;
  • 一条 sql 语句可以表示为多行输入,因为遍历到分号后才认为这条 sql 语句结束;
  • 假设一条 sql 语句用多行输入,如果在输入了多行后,想取消这条命令,那么可以输入 \c,然后回车(后面不加分号),表示撤销该条命令;
  • 如果一条查询语句中显示结果中有很多列,看上去不是很方便,此时可以在语句末尾加上 \G,表示按列显示。