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
网友评论