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)
网友评论