2020-01-08

直接安装

sudo apt install mysql-server-5.7
过程中出现确认root账户密码确认.

Screenshot from 2020-01-08 13-51-47.png

安装后使用ps -ef |grep mysql查看当前服务是否运行

查看服务端是否已经在运行

上图显示是已经启动状态.
如果MySQL没有启动,使用一下命令来启动服务器

启动mysql：

方式一：sudo /etc/init.d/mysql start
方式二：sudo service mysql start

停止mysql：

方式一：sudo /etc/init.d/mysql stop
方式二：sudo service mysql stop

重启mysql：

方式一：sudo /etc/init.d/mysql restart
方式二：sudo service mysql restart

启动服务之后使用mysql -u root -p 进入 数据库操作终端.
-u 为用户名

登录成功

创建新的用户,

新用户具有SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 权限,用户名chy,密码是123456

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'chy'@'localhost'
    -> IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

查看一下刚刚创建的用户

mysql> SELECT user  FROM user WHERE user = 'chy';
+------+
| user |
+------+
| chy  |
+------+
1 row in set (0.00 sec)

mysql> SELECT host ,user , authentication_string  FROM user WHERE user = 'chy';
+-----------+------+-------------------------------------------+
| host      | user | authentication_string                     |
+-----------+------+-------------------------------------------+
| localhost | chy  | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

查看当前DATABASE用哪些用户

mysql> SELECT host ,user , authentication_string  FROM user;
+-----------+------------------+-------------------------------------------+
| host      | user             | authentication_string                     |
+-----------+------------------+-------------------------------------------+
| localhost | root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *D4B2E079DF1EA59B7663C65AA75613331418DE18 |
| localhost | chy              | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)

管理MySQL的常用命令

• use xxx(数据库名)

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

mysql> use mysql
Database changed

• SHOW DATABASES:

  列表列出当前服务器中所有的DATABASE,然后可以上用上面的指令进行USE
  sql的语句大小写并不敏感, show databases 也可以,

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

• SHOW TABLES:

  显示上面已经use 的数据库中的所有表.

mysql> SHOW TABLES; 
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
|servers                    |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

• SHOW COLUMNS FROM 数据表:

  显示数据表中的属性,属性类型,主键信息,和一些其他内容.

mysql> show COLUMNS FROM servers;
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| Server_name | char(64) | NO   | PRI |         |       |
| Host        | char(64) | NO   |     |         |       |
| Db          | char(64) | NO   |     |         |       |
| Username    | char(64) | NO   |     |         |       |
| Password    | char(64) | NO   |     |         |       |
| Port        | int(4)   | NO   |     | 0       |       |
| Socket      | char(64) | NO   |     |         |       |
| Wrapper     | char(64) | NO   |     |         |       |
| Owner       | char(64) | NO   |     |         |       |
+-------------+----------+------+-----+---------+-------+
9 rows in set (0.00 sec)

• SHOW INDEX FROM 数据表:

  显示数据表的详细索引信息，包括PRIMARY KEY。

mysql> SHOW INDEX FROM servers;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| servers |          0 | PRIMARY  |            1 | Server_name | A         |           0 |     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，查询结果按列打印

2020-01-08 14:50:06