mysql 使用说明-2

3.3.4 Retrieving Information from a Table

Select 命令从表格中取回信息

SELECT what_to_select   FROM which_table  WHERE conditions_to_satisfy;

what_to_select 是你想看到的结果，可以是一些列，也可以是“*“表示所有列，which_table是你想查找信息的目标表格，where是可选的，如果选了，

conditions_to_satisfy是一个或者多个必须满足的条件。

3.3.4.1 Selecting All Data

查找所有资料：

mysql> SELECT * FROM pet;



Select 命令能方便的查看整个表格，例如，当你从你的源始资料加载到数据库中，你可能突然想到，生日可能不太正确，生日应该是1989年，而不是1979年

最少有两种种方法可以做到

　　a.修改pet.txt，然后删除pet 表，再load data

　　mysql> DELETE FROM pet; 
　　mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;

　　b.Update 只修改错误的信息

　　mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

　　update只修改不符合要求的数据，不用重新加载源数据

3.3.4.2 Selecting Particular Rows

选择指定的行

从前面的情况可以看到，检索整个列表是很简单，只是忽略了Where关键字。但是比较常见的情况是你不需要整个表格，特别是当数据库很大的时候 。

 

相反，你只需要查看某一特定信息。

 

下面是查看Bowser的生日信息

 

mysql> SELECT * FROM pet WHERE name = 'Bowser';

从结果可以看出来，birth改成了1989年，不再是1979年。

注意，字符串类型是大小写不敏感的，bowser写成BOWSER结果是一样的。

其他任意行的信息你都可以检索，比如：

mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';

可以使用And语句：mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';



AND是逻辑操作符，还有OR

AND 和OR可以混全使用，但AND优先级比OR高，所以用括号是比较好的选择

3.3.4.3 Selecting Particular Columns

选择特定的列

有时你不想看整个表，而只对其中的一列信息感兴趣，比如，你只想看宠物的出生，

mysql> SELECT name, birth FROM pet;

查看宠物的主人

mysql> SELECT owner FROM pet;

这条语句只是简单的查出了主人，而有的却出现了多次，为了最小化输出，只需要加入Distinct

mysql> SELECT DISTINCT owner FROM pet;

你可以通过Where子句来组全行和列。

例如，只得到狗和猫的出生日期

 

3.3.4.4 Sorting Rows

对列排序

有时你需要排序，需要用到Order By命令

下面是对出生日期的排序

mysql> SELECT name, birth FROM pet ORDER BY birth;

默认排序是升序，如果你需要降序，需要加上DESC

mysql> SELECT name, species, birth FROM pet
    -> ORDER BY species, birth DESC;

 

事实 上，上面的DESC只根据birth列而对species没有影响。

3.3.4.5 Date Calculations

日期计算

mysql提供了一些函数，方便你计算日期，

计算宠物的寿命，使用TimeStampdiff（）函数，参数是你想得到结果的单位，这里是年

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



目的是达到了，但是如果结果能按一定的顺序进行排序 ，会比较方便进行查找 ，我们加入Order By试试

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

如果需要按年龄排序，只需要简单修改下：

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

一条相似的语句可以查看哪些宠物已经死去，

这里因为，我这数据库建立时默认生成的death 是0000-00-00所以也是非NULL，否则应该只有最后一行数据Bowser

如果你想知道哪个动物下个月过生日怎么办？要做这种计算，年和日都是相关的，你只想提取月份数据，mysql提供了几个函数去提取日期中的一部分，比如year(),month(),dayofmonth()

这里要用到的是month函数

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

现在要找出下个月过生的宠物也是很简单的，比如 ，现在 4月，那么下个月就是五月

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

注意，如果现在是12月，要记住你该查找的是1月而不是13月

你可以通过下面的语句来查询结果，而不用在意现在是哪个月，所以你不要使用特定的月份。Date add()函数能加一定的时间到给定的时间，如果你身Curdate（）加一定时间

然后就可以通过month()函数提取你想找的下个月过后日的宠物了。

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

另一种方法是对当前月份取余，对12的余数如果是0,那么加1

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

Month（）返回1~12的数，Mod（）返回0~11的数，所以1应该放在mod()后面，否则，就会从11月直接跳到了1月。

3.3.4.6 Working with NULL Values

处理空值

i当你使用Null时一定会”大吃一斤“，因为Null意味着未知的值，而与其他值有些不同

可以通过is null 和is not null操作符

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;

不能使用大于号，小于号来操作，看下面

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

因为任意一个数与Null运算的结果都是Null,你无法得到任何有意义的结果

在mysql中，0或者Null表示False,任何其他值为True,真值的默认Boolean值为1

两个Null被认为是相等 的，

如果你进行升序排序，Null会排在第一个，降序排在最后一个

一个比较常见的错误是，假想以为不能向一个定义为非空的列中插入0或者空字符串，但事实并不是这样的，可以通过下面的测试看出来

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



上面的图说明 可以向一个定义为not null的列中插入0 或者空的字符串。

 3.3.4.7 Pattern Matching

 模式匹配

mysql提供标准的类似sql的匹配模式，同时支持正则表达式扩展，类似unix上的vi ,grep，sed。

-　　　　匹配任意单个字符
%　　　　匹配任意个字符，包括0个

在mysql中，sql模式默认是大小写不敏感的，下面是一些例子，不要用= 和<>,e用Like , Not Like

查找以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个字母 的名字，用5个 _

mysql> SELECT * FROM pet WHERE name LIKE '_____';

注意，这是5个下划线，

其他一些用了正则的匹配模式，当你使用时应该加上REGEXP或者 NOT REGEXP(也可以用RLike 或者Not RLkie)

下面是一些扩展支持的正则的语法：

.　　　　匹配任意单字符

[...]　　匹配括号内的任意单字符，如:[abc]，会匹配a,b,或者c，用横杠表示一个范围，如：[a-z]会匹配任意字母（因为大小写不敏感），[0-9]会匹配任意数字

* 　　　　会匹配一个或多个它前面的表达式，如x* 会匹配任意个x，[0-9]*会匹配任意个数字，.*会匹配任意个任何字符

Regexp　　 只要是这种模式就会匹配成功，而Like则必须匹配完整的值。

^,$　　　　^匹配以某某开头，而$匹配以某某结尾

下面是匹配以字母 b开头的名字

mysql> SELECT * FROM pet WHERE name REGEXP '^b';

假如你的强迫症又犯了，你说，我就是要大小写敏感，那么也是可以的，用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';



之所以能匹配成功是因为只要模式相同，正则就能匹配成功，而不用前面那样要在两端加上%通配符

要匹配五个字符的名字，用^来限制开头，$限制结尾，在两者之间加上5个.

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';

你也可以用｛n｝操作符，表示重复n次

mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

3.3.4.8 Counting Rows

计算行数

建立数据库当然是为了用，比如你想知道，谁谁谁有几只宠物，或者你共有几只宠物，其实这只需要知道这个表有多少行就行了，因为一行一只宠物

Count(*)函数统计表有多少行，这样你就知道你有多少宠物了

mysql> SELECT COUNT(*) FROM pet;



如果你想知道每个人有几只宠物，

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

这里用了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;

有时你并不想知道整个列表的数据，比如你只想看看dog和cat的情况：

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;

那么，这里有一点问题，上面的bird那个空白怎么解释呢，如果你记得前面的内容，空字符串与Null是不相等的。


如果你只选择其中的一列，那么Group By也就选择对方列，否则下面的情况就会发生 
如果选择了完全匹配分组模式

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';

下面的语句就会报错：

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause

如果没有选择完全匹配分组模式：

这条语句就会把整个表当成一个组，然后选择的内容可能是任意一列的内容，就是说结果是随机的

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;

3.3.4.9 Using More Than one Table

使用多张表格

这张表只保存了现在这些宠物的部分信息，如果你想保存别的信息，或者又有小宠物出生blablabla

 

那么再建一张表吧

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

 

一定要加上路径：

mysql> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet INNER JOIN event
    ->   ON pet.name = event.name
    -> WHERE event.type = 'litter';

当从多张表格组合（join）信息时，你需要确认有多少记录可以匹配到另外一张表，这里很容易实现是因为都有name这一列，这里用了on子句去匹配 ，因为两张表里面都有name这一列

组合两张不同的表格是不必要的，有时你只需要在一张表格里面组合就行了（此处翻译可能有错误，请根据结果揣摩一下）

例如，你需要知道哪些可以繁殖配对。

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1 INNER JOIN pet AS p2
    ->   ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

在这条语句中，我们对列指定了别名，然后直接使用相对应的列。