返回顶部

收藏

mysql 在where语句中使用XOR

更多
/*
mysql> Drop table Books;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Books
    -> (
    ->    ID SMALLINT NOT NULL PRIMARY KEY,
    ->    Name VARCHAR(40) NOT NULL,
    ->    Category VARCHAR(15),
    ->    InStock SMALLINT NOT NULL,
    ->    OnOrder SMALLINT NOT NULL
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO Books
    -> VALUES (101, 'On', 'Nonfiction', 13, 11),
    ->        (102, 'News', 'Fiction', 15, 21),
    ->        (103, 'Hello', 'Nonfiction', 21, 32),
    ->        (104, 'Poet', 'Nonfiction', 35, 13),
    ->        (105, 'Dunces', 'Fiction', 5, 35),
    ->        (106, 'One', 'Fiction', 28, 14),
    ->        (107, 'From', NULL, 46, 31);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from Books;
+-----+--------+------------+---------+---------+
| ID  | Name   | Category   | InStock | OnOrder |
+-----+--------+------------+---------+---------+
| 101 | On     | Nonfiction |      13 |      11 |
| 102 | News   | Fiction    |      15 |      21 |
| 103 | Hello  | Nonfiction |      21 |      32 |
| 104 | Poet   | Nonfiction |      35 |      13 |
| 105 | Dunces | Fiction    |       5 |      35 |
| 106 | One    | Fiction    |      28 |      14 |
| 107 | From   | NULL       |      46 |      31 |
+-----+--------+------------+---------+---------+
7 rows in set (0.00 sec)

mysql> SELECT Name, Category, InStock, OnOrder
    -> FROM Books
    -> WHERE Category='Fiction' XOR InStock IS NULL
    -> ORDER BY Name;
+--------+----------+---------+---------+
| Name   | Category | InStock | OnOrder |
+--------+----------+---------+---------+
| Dunces | Fiction  |       5 |      35 |
| News   | Fiction  |      15 |      21 |
| One    | Fiction  |      28 |      14 |
+--------+----------+---------+---------+
3 rows in set (0.00 sec)

*/
Drop table Books;

CREATE TABLE Books
(
   ID SMALLINT NOT NULL PRIMARY KEY,
   Name VARCHAR(40) NOT NULL,
   Category VARCHAR(15),
   InStock SMALLINT NOT NULL,
   OnOrder SMALLINT NOT NULL
);

INSERT INTO Books
VALUES (101, 'On', 'Nonfiction', 13, 11),
       (102, 'News', 'Fiction', 15, 21),
       (103, 'Hello', 'Nonfiction', 21, 32),
       (104, 'Poet', 'Nonfiction', 35, 13),
       (105, 'Dunces', 'Fiction', 5, 35),
       (106, 'One', 'Fiction', 28, 14),
       (107, 'From', NULL, 46, 31);

select * from Books;

SELECT Name, Category, InStock, OnOrder
FROM Books
WHERE Category='Fiction' XOR InStock IS NULL
ORDER BY Name;

标签:SQL,MySQL

收藏

0人收藏

支持

0

反对

0

相关聚客文章
  1. Pangzi 发表 2015-06-06 16:05:58 MySQL中的那些坑
  2. 谢权 发表 2016-01-27 14:17:35 SQL入门练习(二)
  3. JeffJing 发表 2011-04-19 08:12:51 MySQL常用数据表操作SQL
  4. 小丁 发表 2017-02-18 22:39:44 关于日志级别的一些想法
  5. hanze 发表 2017-07-19 15:10:14 linux shell小脚本分享
  6. 尖兵 发表 2018-03-06 15:45:56 mysql报错ERROR 1093
  7. 邓龙华 发表 2015-12-20 15:51:52 MySQL好用的数学函数
  8. ITJesse 发表 2014-01-05 20:06:56 将SQL Server中的数据导入MySQL
  9. ITJesse 发表 2014-01-05 20:06:56 将SQL Server中的数据导入MySQL
  10. yeseng 发表 2016-07-14 16:01:51 Dropbox的神奇口袋:Dropbox架构详析第二篇
  11. zhu329599788@126 发表 2017-03-14 09:08:31 mysql的SQL_CALC_FOUND_ROWS 使用
  12. 蓝鲸 发表 2017-10-27 03:08:55 像Excel一样使用SQL进行数据分析(2)

发表评论