mysql补充操作

1.日期计算

MySQL 有用来计算日期的函数，比如计算年龄或提取日期部分

①可以使用函数TIMESTAMPDIFF()计算当前日期的年和出生日期之间的差也可以按照直接使用语句(YEAR(CURDATE())-YEAR(birth))计算，其中函数CURDATE()是计算当前的日期。如果当前日期的日历年比出生日期早，则减去一年。以下代码是查询每个宠物的出生日期、当前日期和年龄（以年作为计算单位），其中关键字age是年龄这个计算结果的标签。

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet;


# 比较这两个查询语句的结果

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age  
    -> FROM pet;

此处，YEAR() 提取日期的年数，RIGHT() 提取日期最右面5个字符的 MM-DD (月份和日期)部分。MM-DD 值的表达式部分的值一般为 1 或 0，如果 CURDATE() 的年比 birth 的年早，则年份应减去 1。整个表达式看起来有些难懂，使用 age 来使输出的列标记更有意义。

②可以使用一个类似的查询来确定已经死亡动物的死亡年龄。你通过检查death值是否为NULL来确定是哪些动物已经死亡，然后对于那些非NULL值的动物，需要计算出death和birth值之间的差来知道他们在这个世界上所存在的时间：

mysql> SELECT name, birth, death,
    -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

查询使用death IS NOT NULL而非death != NULL，因为NULL是特殊的值，不能使用普通比较符来比较。

③如果你想要知道哪个动物下个月过生日怎么办？对于这类计算，年和天是无关的，你只需要提取birth列的月份部分。MySQL提供几个日期方面的提取函数，例如YEAR()、MONTH()和DAYOFMONTH()。在这里MONTH()是我们需要的函数。为了观察它的实现原理，可以运行以下简单的查询显示birth和MONTH(birth)的值：

mysql> SELECT name, birth, MONTH(birth) FROM pet;

2. NULL值操作

请注意在MySQL中，0或NULL意味着假而其它值意味着真。布尔运算的默认真值是1。

为了查询出哪个动物不再是活着的，使用death IS NOT NULL而不使用death != NULL的原因。

在GROUP BY中，两个NULL值被视为相同等价的。

执行ORDER BY语句排序时，如果运行ORDER BY … ASC，则NULL值出现在最前面，若运行ORDER BY … DESC，则NULL值出现在最后面。

SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

+-----------+---------------+------------+----------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL |
+-----------+---------------+------------+----------------+
|         0 |             1 |          0 |              1 |
+-----------+---------------+------------+----------------+
1 row in set (0.02 sec)

结果是判断你输入的真假

3.模式匹配

MySQL提供标准的SQL模式匹配，以及一种基于类Unix里的程序如vi、grep和sed里的扩展正则表达式模式匹配的格式。 SQL模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。

要想找出以“b”开头的名字的动物信息：
mysql> SELECT * FROM pet WHERE name LIKE 'b%';

要想找出以“fy”结尾的名字：
mysql> SELECT * FROM pet WHERE name LIKE '%fy';

要想找出包含“w”的名字：
mysql> SELECT * FROM pet WHERE name LIKE '%w%';

要想找出正好包含5个字符的名字，使用“_”模式字符：
mysql> SELECT * FROM pet WHERE name LIKE '_____';

由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。

扩展正则表达式的一些字符是：

‘.’匹配任何单个的字符。

字符类“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。

“ ”匹配零个或多个在它前面的东西。例如，“x”匹配任何数量的“x”字符，“[0-9]”匹配的任何数量的数字，而“.”匹配任何数量的任何东西。

如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。 为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”

为了找出以“b”开头的名字，使用“^”匹配名字的开始：
mysql> SELECT * FROM pet WHERE name REGEXP '^b';

如果你想强制使REGEXP比较区分大小写，使用BINARY关键字使其中一个字符串变为二进制字符串。

该查询只匹配名称首字母的小写‘b’。
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';

为了找出以“fy”结尾的名字，使用“$”匹配名字的结尾：
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

为了找出包含一个“w”的名字，使用以下查询：
mysql> SELECT * FROM pet WHERE name REGEXP 'w';

既然如果一个正则表达式出现在值的任何地方，他就会被模式匹配，就不必在先前的查询中在模式的两侧放置一个通配符以使得它匹配整个值，就像你使用了一个SQL模式那样。

为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

你也可以使用“{n}”重复n次操作符,重写前面的查询：
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

4.计算行数

数据库经常用于回答这个问题，“查询出某个类型的数据在表中出现的频数是多少?”

例如，你可能想要知道你有多少宠物，或每位主人有多少宠物，或你可能想要对你的动物进行各种类型的普查。

计算你拥有动物的总数目与“在pet表中有多少行?”是同样的问题，因为每个宠物都对应一条记录。COUNT(*)函数计算行数，所以计算动物数目的查询应为：

mysql> SELECT COUNT(*) FROM pet;

在前面的章节中，你检索了拥有宠物的人的名字。如果你想要知道每个主人有多少宠物，你也可以使用COUNT(*)函数：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owne

COUNT(*)和GROUP BY以各种形式分类你的数据。下列例子显示出以不同方式进行动物普查操作。

查看每种动物的数量：

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;

查看每种性别的动物数量：

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;

按种类和性别组合分类的动物数量：

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;

若使用COUNT(*)，你不必检索整个表。例如, 当只对狗和猫进行查询时，应为：

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = 'dog' OR species = 'cat'
    -> GROUP BY species, sex;

或，如果你仅需要知道已知性别的按性别分组的动物数目：

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;

5.转义序列

在字符串中，某些序列具有特殊含义。这些序列均以反斜线()开始，即所谓的转义字符。 MySQL 识别以下转义序列：

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+
1 row in set (0.02 sec)

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+
1 row in set (0.00 sec)

mysql> SELECT 'This\nIs\nFour\nLines';
+--------------------+
| This
Is
Four
Lines |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 'disappearing\ backslash';
+------------------------+
| disappearing backslash |
+------------------------+
| disappearing backslash |
+------------------------+
1 row in set (0.00 sec)

查看输出结果，有没有理解一点了

6.用户自定义变量

可以先在用户自己定义的变量中存储一个值然后在后面引用它；这样可以将该值从一个语句传递到另一个语句。用户自定义变量具有会话专一性，也就是说，一个客户端定义的变量不能被其它客户端看到或使用，当客户端退出时，该客户端连接的所有变量将自动释放。

用户变量的形式为 @var_name，其中变量名 var_name 可以由当前字符集中的字母数字字符、.、_和 $ 组成。 默认字符集是 cp1252 (Latin1)。可以用 mysqld 的 --default-character-set 选项更改字符集。

设置用户变量的一个途径是执行 SET 语句：

SET @var_name = expr [, @var_name = expr] ...

对于 SET 语句，可以使用 = 或 := 作为分配符，分配给每个变量的 expr 可以为整数、实数、字符串或者 NULL 值。

除了使用 SET 语句也可以为用户变量分配一个值。在这种情况下，分配符必须为 := 而不能用 = ，因为在非 SET 语句中 = 被视为比较操作符：

# 尝试输入以下语句观察结果
mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

用户变量可以用于表达式中。目前不包括明显需要文字值的语句，例如 SELECT 语句的 LIMIT 子句，或者 LOAD DATA 语句的 IGNORE number LINES 子句。

如果使用没有初始化的变量，其值是 NULL。

注：在 SELECT 语句中，表达式是在被发送到客户端后才进行计算。这说明在 HAVING、GROUP BY 或者 ORDER BY 子句中，在 SELECT 列表中不能使用将值赋值到变量的表达式。例如，下面的语句不能按期望进行：

mysql> SELECT (@aa:=id) AS a，(@aa+3) AS b FROM tbl_name HAVING b=5;

上面 HAVING 子句中使用 @aa 这个变量引用了 b，但是这并不能按期望生效：@aa 并不是当前行的值，而是前面所选定的行的 id 值。因此一般情况下是不建议在语句的一个部分为用户变量分配一个值而在同一语句的其它部分又使用该变量，这可能会得到期望的结果，当然也可能不会。

另一种情况是，先使用 SET 语句将值赋值到一个用户自定义变量，再在另一个非 SET 语句中，重新读取该用户自定义变量并在该语句中对原来的变量的值进行更改，此时该自定义用户的变量的值取决于表达式本身出现的先后顺序。下面的例子说明了该点：

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

对于该 SELECT 语句，MySQL 在第一行向客户端报告 @a 是一个字符串 ‘test’，并且将 @a 的所有访问结果转换为字符串，即使 @a 在第2行中设置为一个数字。只有在执行完 SELECT 语句后，@a 的值在下一语句中才是数字20。

要想避免这种问题，要么不在同一个语句中设置并使用相同的变量，要么在使用前将变量设置为0、0.0或者’'来定义其类型。