树莓派——mysql的学习（2）

Xshell 5 (Build 0964)
Copyright (c) 2002-2016 NetSarang Computer, Inc. All rights reserved.

Type `help' to learn how to use Xshell prompt.
[c:\~]$ 

Connecting to 192.168.1.105:22...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.


The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.

Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Fri Nov 18 15:03:37 2016 from 192.168.1.101
pi@raspberrypi:~ $ mysql -u zhang -p menagerie
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.5.52-0+deb8u1 (Raspbian)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> Bye
pi@raspberrypi:~ $ 
pi@raspberrypi:~ $ #也可以在连接数据库的时候指定数据库
pi@raspberrypi:~ $ mysql -u zhang -p menagerie
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.5.52-0+deb8u1 (Raspbian)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> -- 查看当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| menagerie  |
+------------+
1 row in set (0.00 sec)

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.5.52, for debian-linux-gnu (armv7l) using readline 6.3

Connection id:      48
Current database:   menagerie
Current user:       zhang@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.52-0+deb8u1 (Raspbian)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         3 hours 29 min 31 sec

Threads: 3  Questions: 158  Slow queries: 0  Opens: 48  Flush tables: 1  Open tables: 41  Queries per second avg: 0.012
--------------

mysql> -- 创建表
mysql> -- 1.首先查看数据库中存在的表
mysql> show tables;
Empty set (0.00 sec)

mysql> -- 创建一个pet表
mysql> create talbe pet(
    -> name varchar(20),
    -> owner varchar(20),
    -> species varchar(20),
    -> sex char(1),
    -> birth date,
    -> death date
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'talbe pet(
name varchar(20),
owner varchar(20),
species varchar(20),
sex char(1)' at line 1
mysql> create table pet(
    -> name varchar(20),
    -> owner varchar(20),
    -> species varchar(20),
    -> sex char(1),
    -> birth date,
    -> death date
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> -- 查看当前数据库中存在的数据表
mysql> show talbes;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'talbes' at line 1
mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet                 |
+---------------------+
1 row in set (0.00 sec)

mysql> -- 查看表结构
mysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

mysql> -- 或者使用desc查看表结构
mysql> desc pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> show columns from pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> show create table pet;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| pet   | CREATE TABLE `pet` (
  `name` varchar(20) DEFAULT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> -- 另外的一种查看表结构的方法
mysql> use information_schema
Database changed
mysql> select * from columns where table_name='pet';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME    | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+
| def           | menagerie    | pet        | name        |                1 | NULL           | YES         | varchar   |                       20 |                     20 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(20) |            |       | select,insert,update,references |                |
| def           | menagerie    | pet        | owner       |                2 | NULL           | YES         | varchar   |                       20 |                     20 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(20) |            |       | select,insert,update,references |                |
| def           | menagerie    | pet        | species     |                3 | NULL           | YES         | varchar   |                       20 |                     20 |              NULL |          NULL | latin1             | latin1_swedish_ci | varchar(20) |            |       | select,insert,update,references |                |
| def           | menagerie    | pet        | sex         |                4 | NULL           | YES         | char      |                        1 |                      1 |              NULL |          NULL | latin1             | latin1_swedish_ci | char(1)     |            |       | select,insert,update,references |                |
| def           | menagerie    | pet        | birth       |                5 | NULL           | YES         | date      |                     NULL |                   NULL |              NULL |          NULL | NULL               | NULL              | date        |            |       | select,insert,update,references |                |
| def           | menagerie    | pet        | death       |                6 | NULL           | YES         | date      |                     NULL |                   NULL |              NULL |          NULL | NULL               | NULL              | date        |            |       | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+-------------------+-------------+------------+-------+---------------------------------+----------------+
6 rows in set (0.00 sec)
pi@raspberrypi:~ $ #将数据转载进表中
pi@raspberrypi:~ $ ls
Desktop    Downloads  Pictures  Templates  oldconffiles
Documents  Music      Public    Videos     python_games
pi@raspberrypi:~ $ sudo nano pet.txt
pi@raspberrypi:~ $ load data local infile '/pet.txt' into table pet;
-bash: load: command not found
pi@raspberrypi:~ $ mysql -u zhang -p menagerie
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.5.52-0+deb8u1 (Raspbian)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> load data local infile '/pet.txt' into table pet
    -> lines terminated by '\r';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
mysql> --  如果LOAD DATA LOCAL INFILE在服务器或客户端被禁用，试图执行该语句的客户端将收 到下面的错误消息：
mysql> -- 刚才通过文本文件装载数据记录失败了，我直接通过Navicate手动添加的
mysql> -- 使用insert插入Diane的仓鼠Puffball
mysql> insert into pet
    -> values ('Puffball','Diane','hamster','f','1993-03-30',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> -- 从表中检索信息
mysql> -- 从pet表中检索所有记录
mysql> select　＊　from　pet;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select　＊　from　pet' at line 1
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1993-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> -- 修改Bowser的生日为1989
mysql> update pet set birth = '1989-08-31' where name = 'Bowser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> -- 查看修改的结果
mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1993-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> -- 选择数据表中的某一行记录进行查看，例如刚才修改生日的Bowser
mysql> select * from pet where name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)

mysql> -- 查看那个动物是在1998年以后出生的
mysql> select * from pet where birth > '1998-1-1';
+--------+-------+---------+------+------------+-------+
| name   | owner | species | sex  | birth      | death |
+--------+-------+---------+------+------------+-------+
| Chirpy | Gwen  | bird    | f    | 1998-09-11 | NULL  |
+--------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)

mysql> -- 利用组合的条件，找出雌性的狗
mysql> select * from pet where species = 'dog' and sex ='f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)

mysql> -- 找出表中是蛇或者是鸟的宠物
mysql> select * from pet where species = 'snake' or species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)

mysql> -- 找出表中的雄性的猫或者雌性的狗
mysql> select * from pet where (species = 'cat' and sex = 'm')
    -> or (species = 'dog' and sex ='f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)

mysql> -- 查看表中某几列的数据
mysql> select name,birth from pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1993-03-30 |
+----------+------------+
9 rows in set (0.00 sec)

mysql> -- 查看表中谁有宠物
mysql> select owner from pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+
9 rows in set (0.00 sec)

mysql> -- 在上一次查询中有重复的记录，可以增加distinct关键字检索出每个唯一的输出记录
mysql> select distinct owner from pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Benny  |
| Diane  |
+--------+
4 rows in set (0.00 sec)

mysql> -- 查找表中的猫和狗的姓名，种类和生日
mysql> select name,species,birth from pet
    -> where species = 'dog' or species = 'cat';
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+
5 rows in set (0.00 sec)

mysql> -- 查看表中宠物的姓名和生日，并以生日按升幂排序
mysql> select name,birth from pet order by birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Puffball | 1993-03-30 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
+----------+------------+
9 rows in set (0.00 sec)

mysql> -- 查看表中宠物的姓名和生日，并以生日按降幂排序
mysql> select name,birth from pet order by birth desc;
+----------+------------+
| name     | birth      |
+----------+------------+
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Puffball | 1993-03-30 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+
9 rows in set (0.00 sec)

mysql> -- 按照升幂对动物的种类进行排序，然后按照降幂根据生日对各种动物种类进行排序
mysql> select name.species,birth from pet
    -> order by species,birth desc;
ERROR 1054 (42S22): Unknown column 'name.species' in 'field list'
mysql> select name.species,birth from pet order by species,birth desc;
ERROR 1054 (42S22): Unknown column 'name.species' in 'field list'
mysql> select name,species,birth from pet order by species,birth desc;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1993-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+
9 rows in set (0.00 sec)

mysql> -- 日期计算
mysql> -- 查询每个宠物的出生日期，当前日期和年龄
mysql> select name,birth,curdate(),
    -> (year(curdate())-year(birth))
    -> - (right(curdate(),5)<right(birth,5)
    -> as age
    -> from pet;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as age
from pet' at line 4
mysql> select name,birth,curdate(), (year(curdate())-year(birth)) - (right(curdate(),5)<right(birth,5)) as age from pet;
+----------+------------+------------+------+
| name     | birth      | curdate()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2016-11-19 |   23 |
| Claws    | 1994-03-17 | 2016-11-19 |   22 |
| Buffy    | 1989-05-13 | 2016-11-19 |   27 |
| Fang     | 1990-08-27 | 2016-11-19 |   26 |
| Bowser   | 1989-08-31 | 2016-11-19 |   27 |
| Chirpy   | 1998-09-11 | 2016-11-19 |   18 |
| Whistler | 1997-12-09 | 2016-11-19 |   18 |
| Slim     | 1996-04-29 | 2016-11-19 |   20 |
| Puffball | 1993-03-30 | 2016-11-19 |   23 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

mysql> -- 此处，YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符。比较MM-DD值的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1。整个表达式有些难懂，使用alias (age)来使输出的列标记更有意义。
mysql> -- 添加ORDER BY name子句按照名字对输出进行排序
mysql> select name,birth,curdate(), (year(curdate())-year(birth)) - (right(curdate(),5)<right(birth,5)) as age from pet order by name;
+----------+------------+------------+------+
| name     | birth      | curdate()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2016-11-19 |   27 |
| Buffy    | 1989-05-13 | 2016-11-19 |   27 |
| Chirpy   | 1998-09-11 | 2016-11-19 |   18 |
| Claws    | 1994-03-17 | 2016-11-19 |   22 |
| Fang     | 1990-08-27 | 2016-11-19 |   26 |
| Fluffy   | 1993-02-04 | 2016-11-19 |   23 |
| Puffball | 1993-03-30 | 2016-11-19 |   23 |
| Slim     | 1996-04-29 | 2016-11-19 |   20 |
| Whistler | 1997-12-09 | 2016-11-19 |   18 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

mysql> -- 添加ORDER BY age子句按照年龄对输出进行排序
mysql> select name,birth,curdate(), (year(curdate())-year(birth)) - (right(curdate(),5)<right(birth,5)) as age from pet order by age;
+----------+------------+------------+------+
| name     | birth      | curdate()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2016-11-19 |   18 |
| Whistler | 1997-12-09 | 2016-11-19 |   18 |
| Slim     | 1996-04-29 | 2016-11-19 |   20 |
| Claws    | 1994-03-17 | 2016-11-19 |   22 |
| Fluffy   | 1993-02-04 | 2016-11-19 |   23 |
| Puffball | 1993-03-30 | 2016-11-19 |   23 |
| Fang     | 1990-08-27 | 2016-11-19 |   26 |
| Buffy    | 1989-05-13 | 2016-11-19 |   27 |
| Bowser   | 1989-08-31 | 2016-11-19 |   27 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

mysql> -- 可以使用一个类似的查询来确定已经死亡动物的死亡年龄。你通过检查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;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+
1 row in set (0.00 sec)

mysql> -- 如果你想要知道哪个动物下个月过生日，怎么办？对于这类计算，年和天是无关的，你只需要提取birth列的月份部分。MySQL提供几个日期部分的提取函数，例如YEAR( )、MONTH( )和DAYOFMONTH( )。在这里MONTH()是适合的函数。为了看它怎样工作，运行一个简单的查询，显示birth和MONTH(birth)的值
mysql> select name,birth,month(birth) from pet;
+----------+------------+--------------+
| name     | birth      | month(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1993-03-30 |            3 |
+----------+------------+--------------+
9 rows in set (0.00 sec)

mysql> -- 找出下个月生日的动物也是容易的。假定当前月是4月，那么月值是4，你可以找在5月出 生的动物 (5月)
mysql> select name,birth from pet where month(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)

mysql> -- 如果当前月份是12月，就有点复杂了。你不能只把1加到月份数(12)上并寻找在13月出生 的动物，因为没有这样的月份。相反，你应寻找在1月出生的动物(1月) 。你甚至可以编写查询，不 管当前月份是什么它都能工作。采用这种方法不必在查询中使用一个特定的月份，DATE_ADD()允许在一个给定的日期上加上时间间隔。如果在NOW( )值上加上一个月，然后用MONTH()提取月份，结果产 生生日所在月份
mysql> select name,birth from pet
    -> where month(birth) = month(date_add(curdate(),interval 1 month));
+----------+------------+
| name     | birth      |
+----------+------------+
| Whistler | 1997-12-09 |
+----------+------------+
1 row in set (0.00 sec)

mysql> -- 完成该任务的另一个方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如 果月份当前值是12，则“回滚”到值0)：
mysql> 
mysql> select name,birth from pet
    -> where month(birth) = mod(month(curdate()),12)+1;
+----------+------------+
| name     | birth      |
+----------+------------+
| Whistler | 1997-12-09 |
+----------+------------+
1 row in set (0.00 sec)

mysql> -- 注意，MONTH返回在1和12之间的一个数字，且MOD(something,12)返回在0和11之间的一个数字，因此必须在MOD( )以后加1，否则我们将从11月( 11 )跳到1月(1)。
mysql> 
mysql> -- null值操作
mysql> -- NULL值可能令人感到奇怪直到你习惯它。概念上，NULL意味着“没有值”或“未知值”，且它被看作与众不同的值。为了测试NULL，你不能使用算术比较 操作符例如=、<或!=。
mysql> select 1 = null,1 <> null,1 < null,1 > null;
+----------+-----------+----------+----------+
| 1 = null | 1 <> null | 1 < null | 1 > null |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+
1 row in set (0.00 sec)

mysql> -- 很显然你不能通过这些比较得到有意义的结果。相反使用IS NULL和IS NOT NULL操作符
mysql> select 1 is null, 1 is not null;
+-----------+---------------+
| 1 is null | 1 is not null |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
1 row in set (0.00 sec)

mysql> select 0 is null, 0 is not null;
+-----------+---------------+
| 0 is null | 0 is not null |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
1 row in set (0.00 sec)

mysql> -- NULL操作的常见错误是不能在定义为NOT NULL的列内插入0或空字符串，但事实并非如此 。在NULL表示"没有数值"的地方有数值。使用IS [NOT] NULL则可以很容易地进行测试
mysql> 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.00 sec)

最后编辑于：2017.12.04 14:00:28

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 199,393评论 5赞 467
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 83,790评论 2赞 376
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 146,391评论 0赞 330
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 53,703评论 1赞 270
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 62,613评论 5赞 359
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,003评论 1赞 275
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,507评论 3赞 390
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,158评论 0赞 254
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,300评论 1赞 294
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,256评论 2赞 317
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,274评论 1赞 328
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,984评论 3赞 316
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,569评论 3赞 303
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,662评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,899评论 1赞 255
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,268评论 2赞 345
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 41,840评论 2赞 339

树莓派——mysql的学习（2）

推荐阅读更多精彩内容