Idle Works, Idle Thoughts

MySQL 学习笔记

MySQL 入门

MySQL 初次登陆

在安装MySQL后,默认允许匿名登陆或root无密码登录,如:

$ mysql
$ mysql -uroot

root是 MySQL 中默认的最高权限管理员用户。我们在初次登陆时,应该为root设置密码,并删除匿名用户。

root登陆后,查看所有用户及其权限:

mysql> select User,Host,Password from mysql.user;
+---------+-----------------------------+-------------------------------------------+
| User    | Host                        | Password                                  |
+---------+-----------------------------+-------------------------------------------+
| root    | localhost                   |                                           |
| root    | berlinixdemacbook-air.local |                                           |
| root    | 127.0.0.1                   |                                           |
| root    | ::1                         |                                           |
|         | localhost                   |                                           |
|         | berlinixdemacbook-air.local |                                           |
+---------+-----------------------------+-------------------------------------------+

Host表示可以从什么位置登陆 MySQL。

以上4个地址,其本质都是指代运行 MySQL 的这台机子。

MySQL 登陆常用参数

最常见的3个登录参数:

一、用-uUSER指定用户名:

-u <usr>, --user=user

如以用户root登录:

$ mysql -u root

二、用-uHOST指定运行MySQL的主机:

-h <host>, --host=host

如登陆本机MySQL:

-h localhost
-h 127.0.0.1

其中 localhost127.0.0.1 都指代运行 MySQL 的本机。

三、用-pPWD指定登录密码:

-p[pwd], --password=pwd

登陆密码(注意-ppwd前无空格)。通常不直接在命令行写密码,即仅写:

$ mysql -u root -h localhost -p

mysql命令行工具会提示让你输入密码验证。

可以在登陆时选择数据库,如:

$ mysql -u root -h 127.0.0.1 -p books

即进入MySQL后自动选择books数据库。

设置密码

1. 可以用SET PASSWORD语句来设置密码:

mysql> set password for root@localhost = password('PASSWORD');

每次可以设置一个 ‘user’@’host’ 的密码。

2. 可以用 UPDATE 语句来设置密码:

mysql> update mysql.user set password = password('PASSWORD') where user='root';

3. 可以通过 mysqladmin 命令来设置密码,如:

$ mysqladmin -uroot PASSWORD "NEW-PASSWORD"

删除匿名用户:

mysql> drop user ''@localhost;
mysql> drop user ''@'berlinixdemacbook-air.local';

设置密码后,刷新方可生效:

mysql> flush privileges;

查看当前的用户表,可见密码都是加密后的字符串:

mysql> select User,Host,Password from mysql.user;
+---------+-----------------------------+-------------------------------------------+
| User    | Host                        | Password                                  |
+---------+-----------------------------+-------------------------------------------+
| root    | localhost                   | *30C87DC6F24DDE6F90D52B85A1638B9855DA0612 |
| root    | berlinixdemacbook-air.local | *30C87DC6F24DDE6F90D52B85A1638B9855DA0612 |
| root    | 127.0.0.1                   | *30C87DC6F24DDE6F90D52B85A1638B9855DA0612 |
| root    | ::1                         | *30C87DC6F24DDE6F90D52B85A1638B9855DA0612 |
+---------+-----------------------------+-------------------------------------------+

查看MySQL上的用户

查看当前登陆用户(即MySQL 的whoami):

mysql> select user();

查看当前用户的权限:

mysql> show grants;

查看指定用户的权限(在Mac OS X MySQL 5.6.x中无效):

mysql> show grants for jack;

等同于:

mysql> show grants for jack@'%';

赋予权限

MySQL权限管理命令:

mysql> grant PRIVILEGES on DATABASE to USER

其中,PRIVILEGES 表示具体的权限;DATABASE 表示数据库名;USER 是用户名。

设置权限后,刷新后生效:

mysql> flush privileges

MySQL中grant赋予权限有添加用户(adduser)的功能。例如,创建一个用户,并赋予权限:

mysql> grant all on books.* to jack@localhost identified by 'PASSWD' with grant option; 

如果允许从所有地址访问,用:jack@'%',百分号表示任意地址,且应该用引号将百分号引起来。另外,'PASSWD'会加密存储,不要用password('PASSWD')

创建数据库并赋予访问权限

创建数据库:

mysql> create database books;

其中 books 是新建的数据库名。

创建用户并赋予权限:

mysql> grant all on books.* to jack@localhost identified by 'PASSWD' with grant option; 

这里创建了一个名为 jack 的用户,允许他从 localhost 登陆,且其登陆密码为 PASSWD 。

从一个数据备份中,导入数据库:

$ mysql -u jack -p books < ~/backup/books.sql

mysqldump

我们用msyqldump命令行工具备份或导出数据库。

有几种备份选择:

备份所有数据库:

$ mysqldump -ujack -p --all-databases > /tmp/all.sql

备份指定数据库:

$ mysqldump -ujack -p test > /tmp/test.sql

备份指定的表。可以包括多个表,以空格分隔:

$ mysqldump -ujack -p books orders users > /tmp/key.sql

上例备份了books这个数据库中的orders和users表。

备份表结构(不备份数据本身):

$ mysqldump -ujack -p --no-data books > /tmp/books.sql

mysqldump选项

--opt

是默认开启的选项。它是几个选项的缩写:

--master-data=[value]

在主从备份时,将Master的数据库导出可用此选项。在dump文件中记录二进制日志的文件名和偏移,这样slave可以由此知悉同步开始的位置。取值:

来自一个实际的dump文件:

CHANGE MASTER TO MASTER_LOG_FILE='books.000004', MASTER_LOG_POS=248;

使用--master-data选项需要reload权限。

使用--master-data选项即自动禁用--lock-tables,并开启--lock-all-tables

--hex-blob

dump二进制列(binary columns)时使用十六进制的形式(hexadecimal notation),如’abc’将转换为’0x616263’。以下数据类型的列会被转换:binary, varbinary, blob, bit.

常用操作

select语句

select 语句用于从 MySQL 数据库中选择数据。

Distinct的排重是指每个字段都相同的话,只保留一行,它是对整个一行进行比较,而非只是一个字段的排重。

DISTINCT is not a function, but a query decorator. This means, you use SELECT DISTINCT instead of SELECT if you want the query to supress all duplicates. Now the important part is, a row is a duplicate of another row, if (and only if) all columns have the same value.

count() 和 group by

查看订单数最多的5个客户:

select username, count(*) 
from orders 
group by username 
order by count(*) desc 
limit 5;

按用户和订单状态分组,查看订单最多的5个客户:

select username, status, count(status) 
from orders 
group by username,status 
order by count(status) 
desc limit 5;

查看每个月的订单数:

select extract(YEAR_MONTH from order_time), count(order_id) 
from orders 
group by extract(YEAR_MONTH from order_time) 
order by count(order_id) desc;

查看卖家的销量和销售额,按销售额排序,列出排名前20的卖家:

select seller, count(seller), round(sum(price),2) 
from items 
group by seller 
order by sum(price) desc 
limit 20;

函数

日期和时间

now() 返回当前日期时间,如:

mysql> select now();

输出:

2015-08-04 11:58:18

date() 返回日期部分,如:

mysql> select date(now());

输出:

2015-08-04

date_format() 返回自定义的日期时间格式,如:

mysql> select date_format(now(), '%Y/%m/%d');

输出:2015/08/04

datediff() 返回两个日期相间隔的天数,如:

mysql> select datediff(now(), '2014-10-10');

输出:298

timestampdiff() - 比较时间

timestampdiff()比较两个时间:

timestampdiff(unit, datetime_start, datetime_end);

其中,unit的取值可以是:frac_second(Micro Seconds), second, minute, hour, day, week, month, quarter, year.

例如:

MariaDB [books]> select timestampdiff(month, '2016-3-2', '2016-6-15') as monthDiff;
+-----------+
| monthDiff |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

MariaDB [books]> select timestampdiff(day, '2016-3-2', '2016-6-15') as dayDiff;
+---------+
| dayDiff |
+---------+
|     105 |
+---------+
1 row in set (0.00 sec)

数学函数

四舍五入取小数点后2位:

round(value, 2)

控制流程

if()

IF(expr1, expr2, expr3)

如果 expr1 为真,返回 expr2,否则返回 expr3。类似:

(expr1 ? expr2 : expr3)

CASE

CASE value 
WHEN [compare_value] THEN result
[WHEN [compare_value] THEN result ...]
[ELSE result]
END

它类似 switch/case 语句,如:

switch value:
    case compare_value: return result
    default: return result

示例。如下表:

| book | score |
|--|--|
|b|3.9|
|c|3.7|
|d|4.5|

批量更新书的得分:

update books
set score = 
case book when 'b' then 3.8
          when 'c' then 3.6  
          when 'd' then 4.7
end