Mysql基本操作（二）

查看版本信息

• 登录后 \s

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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()

1 2 3 4 5 6 7

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

• 登陆后 status

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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()当前日期、时间

1 2 3 4 5 6

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

1 2 3 4 5 6 7 8

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目录！

1 2 3 4 5 6

格式：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用户可以修改任意用户的密码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

*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权限！

1 2 3 4 5 6 7

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语句

1 2 3 4 5 6 7 8

*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;姚志明用户的链接性质

1 2 3 4 5 6 7

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

• 查看用户权限select distinct concat('User: ''',user,'''@''',host,''';') as query from user;这里用了mysql下的concat()函数用于把查找的字符串链接起来