Mysql基本操作（二）

最新推荐文章于 2024-07-24 12:19:54 发布

原创最新推荐文章于 2024-07-24 12:19:54 发布 · 779 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #密码

数据库专栏收录该内容

2 篇文章

订阅专栏

查看版本信息

登录后 \s

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.19, for Win64 (x86_64)
Connection id:          38
Current database:       stugradems
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.19 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 14 hours 21 min 16 sec
Threads: 1  Questions: 355  Slow queries: 0  Opens: 75  Flush tables: 1  Open tables: 68  Queries per second avg: 0.006

登陆后 select version()

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.19    |
+-----------+
1 row in set (0.00 sec)

登陆后 status

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.6.19, for Win64 (x86_64)
Connection id:          38
Current database:       stugradems
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.6.19 MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 14 hours 25 min 9 sec

USER()当前用户
version()版本信息
current_date()当期日期
now()当前日期、时间

mysql> select version(),current_date(),now(),user();
+-----------+----------------+---------------------+----------------+
| version() | current_date() | now()               | user()         |
+-----------+----------------+---------------------+----------------+
| 5.6.19    | 2014-12-28     | 2014-12-28 23:39:29 | root@localhost |
+-----------+----------------+---------------------+----------------+

查看MySQL当前用户占用的连接数show processlist

mysql> show processlist;
+----+-------+-----------------+------+---------+------+-------+------------------+
| Id | User  | Host            | db   | Command | Time | State | Info             |
+----+-------+-----------------+------+---------+------+-------+------------------+
| 39 | test  | localhost:12548 | NULL | Sleep   |  572 |       | NULL             |
| 42 | root  | localhost:12572 | NULL | Query   |    0 | init  | show processlist |
| 45 | test2 | localhost:12586 | NULL | Sleep   |   10 |       | NULL             |
+----+-------+-----------------+------+---------+------+-------+------------------+

更改密码

mysqladmin命令，用户不必登录，但是一定更要进入mysql安装的bin目录！


格式：mysqladmin -u UserName -p password YourNewPassword;（会提示输入旧密码，正确输入就可以修改密码）
e.g.D:\mysql-5.6.19-winx64\bin>mysqladmin -u test -p password root;
Enter password: ****
D:\mysql-5.6.19-winx64\bin>

UPDATE user 语句

1.必须登录才可以执行这条语句
2.必须use database表明使用那个数据库！
3.用户名对mysql.user表的读写权限决定了能不能修改相关用户的密码，有读写权限就可以修改，没有就不能修
改,一般root用户可以修改任意用户的密码


*root*用户权限大，可以修改很多用户的密码
mysql> update user set password=PASSWORD('test') where user='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
*test*用户
*查看test用户的权限
mysql> show grants for test@localhost;
+--------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
+--------------------------------------------------------------------------------------------------------------------------------------+
*修改密码：
mysql> update user set password=PASSWORD('root') where user='test';

执行flush privileges;报错：ERROR 1227 (42000): Access denied; you need (at least one of) >the RELOAD privilege(s) for this operation
需要给test用户添加reload权限！


mysql> update user set password=PASSWORD('root') where user='test';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

set PASSWORD语句


*test用户*
mysql> set PASSWORD for test@localhost=PASSWORD('root');
mysql> flush privileges;
*root用户登录*
mysql> set PASSWORD for test@localhost=PASSWORD('test');
mysql> flush privileges;

总结：
1.在需要登录的情况下修改密码时，一定要用use mysql切换数据库！
2.在set PASSWORD命令中，要说明用户名的链接属性，本地链接：test@loclahost、远程链接test@%

查看用户

用户信息保存在mysql数据库下的user表中，登陆后执行select语句即可（注意是否有权限）
查看用户的权限信息show grants for test@localhost;姚志明用户的链接性质


mysql> show grants for test@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                                                    |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, RELOAD ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' |
+----------------------------------------------------------------------------------------------------------------------------------------------+