SQL/MySQL/Where Clause/Where

Материал из SQL эксперт
Версия от 13:15, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Calculation in WHERE clause

   <source lang="sql">

/* mysql> Drop table Item; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE Item 

   -> (
   ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    Name VARCHAR(50) NOT NULL,
   ->    InStock SMALLINT UNSIGNED NOT NULL,
   ->    OnOrder SMALLINT UNSIGNED NOT NULL,
   ->    Reserved SMALLINT UNSIGNED NOT NULL,
   ->    Department ENUM("Classical", "Popular") NOT NULL,
   ->    Category VARCHAR(20) NOT NULL,
   ->    RowUpdate TIMESTAMP NOT NULL
   -> );

Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category) 

   ->           VALUES ("Bloodshot",      10,      5,       1, "Popular",

"Rock"), 

   ->                  ("Most",           10,      5,       2, "Classical",

"Opera"), 

   ->                  ("Jazz",           17,      4,       3, "Popular",

"Jazz"), 

   ->                  ("Class",           9,      4,       4, "Classical",

"Dance"), 

   ->                  ("Violin",         24,      2,       5, "Classical",

"General"), 

   ->                  ("Cha Cha",        16,      6,       6, "Classical",

"Vocal"), 

   ->                  ("Blues",           2,     25,       7, "Popular",

"Blues"), 

   ->                  ("Pure",           32,      3,      18, "Popular",

"Jazz"), 

   ->                  ("Mud",            12,     15,      19, "Popular",

"Country"), 

   ->                  ("The",             5,     20,      11, "Popular",

"New Age"), 

   ->                  ("Embrace",        24,     11,      12, "Popular",

"New Age"), 

   ->                  ("Magic",          42,     17,      13, "Classical",

"General"), 

   ->                  ("Lake",           25,     44,      24, "Classical",

"Dance"), 

   ->                  ("LaLala",         20,     10,       5, "Classical",

"Opera"), 

   ->                  ("Soul",           15,     30,      16, "Popular",

"Blues"), 

   ->                  ("Stages",         42,      0,       7, "Popular",

"Blues"), 

   ->                  ("Six",            16,      8,       6, "Classical",

"General"); Query OK, 17 rows affected (0.01 sec) Records: 17 Duplicates: 0 Warnings: 0 mysql> select * from Item; +----+-----------+---------+---------+----------+------------+----------+---------------------+ | ID | Name | InStock | OnOrder | Reserved | Department | Category | RowUpdate | +----+-----------+---------+---------+----------+------------+----------+---------------------+ | 1 | Bloodshot | 10 | 5 | 1 | Popular | Rock | 2005-10-09 09:19:49 | | 2 | Most | 10 | 5 | 2 | Classical | Opera | 2005-10-09 09:19:49 | | 3 | Jazz | 17 | 4 | 3 | Popular | Jazz | 2005-10-09 09:19:49 | | 4 | Class | 9 | 4 | 4 | Classical | Dance | 2005-10-09 09:19:49 | | 5 | Violin | 24 | 2 | 5 | Classical | General | 2005-10-09 09:19:49 | | 6 | Cha Cha | 16 | 6 | 6 | Classical | Vocal | 2005-10-09 09:19:49 | | 7 | Blues | 2 | 25 | 7 | Popular | Blues | 2005-10-09 09:19:49 | | 8 | Pure | 32 | 3 | 18 | Popular | Jazz | 2005-10-09 09:19:49 | | 9 | Mud | 12 | 15 | 19 | Popular | Country | 2005-10-09 09:19:49 | | 10 | The | 5 | 20 | 11 | Popular | New Age | 2005-10-09 09:19:49 | | 11 | Embrace | 24 | 11 | 12 | Popular | New Age | 2005-10-09 09:19:49 | | 12 | Magic | 42 | 17 | 13 | Classical | General | 2005-10-09 09:19:49 | | 13 | Lake | 25 | 44 | 24 | Classical | Dance | 2005-10-09 09:19:49 | | 14 | LaLala | 20 | 10 | 5 | Classical | Opera | 2005-10-09 09:19:49 | | 15 | Soul | 15 | 30 | 16 | Popular | Blues | 2005-10-09 09:19:49 | | 16 | Stages | 42 | 0 | 7 | Popular | Blues | 2005-10-09 09:19:49 | | 17 | Six | 16 | 8 | 6 | Classical | General | 2005-10-09 09:19:49 | +----+-----------+---------+---------+----------+------------+----------+---------------------+ 17 rows in set (0.00 sec) mysql> SELECT Department, Category, Name 

   -> FROM Item
   -> WHERE (InStock+OnOrder-Reserved)<15
   -> ORDER BY Department DESC, Category ASC;

+------------+----------+-----------+ | Department | Category | Name | +------------+----------+-----------+ | Popular | Country | Mud | | Popular | New Age | The | | Popular | Rock | Bloodshot | | Classical | Dance | Class | | Classical | Opera | Most | +------------+----------+-----------+ 5 rows in set (0.00 sec)

  • /

Drop table Item; CREATE TABLE Item ( 

  ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL,
  OnOrder SMALLINT UNSIGNED NOT NULL,
  Reserved SMALLINT UNSIGNED NOT NULL,
  Department ENUM("Classical", "Popular") NOT NULL,
  Category VARCHAR(20) NOT NULL,
  RowUpdate TIMESTAMP NOT NULL

);

INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category) 

         VALUES ("Bloodshot",      10,      5,       1, "Popular",      "Rock"),
                ("Most",           10,      5,       2, "Classical",    "Opera"),
                ("Jazz",           17,      4,       3, "Popular",      "Jazz"),
                ("Class",           9,      4,       4, "Classical",    "Dance"),
                ("Violin",         24,      2,       5, "Classical",    "General"),
                ("Cha Cha",        16,      6,       6, "Classical",    "Vocal"),
                ("Blues",           2,     25,       7, "Popular",      "Blues"),
                ("Pure",           32,      3,      18, "Popular",      "Jazz"),
                ("Mud",            12,     15,      19, "Popular",      "Country"),
                ("The",             5,     20,      11, "Popular",      "New Age"),
                ("Embrace",        24,     11,      12, "Popular",      "New Age"),
                ("Magic",          42,     17,      13, "Classical",    "General"),
                ("Lake",           25,     44,      24, "Classical",    "Dance"),
                ("LaLala",         20,     10,       5, "Classical",    "Opera"),
                ("Soul",           15,     30,      16, "Popular",      "Blues"),
                ("Stages",         42,      0,       7, "Popular",      "Blues"),
                ("Six",            16,      8,       6, "Classical",    "General");

select * from Item; SELECT Department, Category, Name FROM Item WHERE (InStock+OnOrder-Reserved)<15 ORDER BY Department DESC, Category ASC; 


      </source>


Combining WHERE Conditions

   <source lang="sql">

/* Prepare the data */ CREATE TABLE Exam ( 

  StudentID  INT NOT NULL,
  ExamID     INT NOT NULL,
  Mark       INT,
  IfPassed   SMALLINT,
  Comments   VARCHAR(255)

)TYPE = InnoDB;

/* Insert data for testing */ INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Java Test"); INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"C# Test"); INSERT INTO Exam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"JavaScript Test");

/* Real command */ SELECT StudentID, Mark, Comments FROM Exam WHERE ExamID = 1 AND IfPassed = 1; 

      </source>


Compare and calculate in Where clause

   <source lang="sql">

/* mysql> Drop table Sale; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Sale 

   -> (
   ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    Name VARCHAR(50) NOT NULL,
   ->    InStock SMALLINT UNSIGNED NOT NULL,
   ->    OnOrder SMALLINT UNSIGNED NOT NULL,
   ->    Reserved SMALLINT UNSIGNED NOT NULL,
   ->    Department ENUM("Classical", "Popular") NOT NULL,
   ->    Category VARCHAR(20)
   -> );

Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Cate gory) 

   -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro

ck"), 

   ->                 ("Opera",     12,      5,       2,        "Classical","Op

era"), 

   ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja

zz"), 

   ->                 ("Music",     4,       3,       4,        "Classical","Da

nce"), 

   ->                 ("Violin",    25,      2,       5,        "Classical", NU

LL), 

   ->                 ("Toscana",   16,      1,       6,        "Classical", NU

LL), 

   ->                 ("Blues",     7,       22,      7,        "Popular",   "B

lues"), 

   ->                 ("Pure",      38,      5,       11,       "Popular",   NU

LL), 

   ->                 ("Mud",       19,      11,      12,       "Popular",  "Co

untry"), 

   ->                 ("Essence",   5,       23,      12,       "Popular", "New
Age"),
   ->                 ("Embrace",   21,      12,      14,       "Popular", "New
Age"),
   ->                 ("Satie",     42,      17,      15,       "Classical", NU

LL), 

   ->                 ("Lake",      23,      47,      28,       "Classical", "D

ance"), 

   ->                 ("Favorites", 34,      15,      12,       "Classical", "G

eneral"), 

   ->                 ("Boheme",    25,      12,       5,       "Classical", "O

pera"), 

   ->                 ("Cantatas",  26,      13,      8,        "Classical", "G

eneral"), 

   ->                 ("Road",      27,      13,      17,       "Popular", "Cou

ntry"), 

   ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz

z"), 

   ->                 ("Woman",     29,      4,        7,       "Popular", "Blu

es"), 

   ->                 ("Bach",      21,      13,      16,       "Classical", "G

eneral"), 

   ->                 ("Opera",     12,      32,      12,       "Classical", "O

pera"), 

   ->                 ("Soul",      13,      30,      14,       "Popular", NULL

), 

   ->                 ("Stages",    44,      0,       8,        "Popular", "Blu

es"), 

   ->                 ("Bach",      15,      1,       8,        "Classical", "G

eneral"); Query OK, 24 rows affected (0.01 sec) Records: 24 Duplicates: 0 Warnings: 0 mysql> select * from Sale; +----+-----------+---------+---------+----------+------------+----------+ | ID | Name | InStock | OnOrder | Reserved | Department | Category | +----+-----------+---------+---------+----------+------------+----------+ | 1 | Bloodshot | 11 | 6 | 1 | Popular | Rock | | 2 | Opera | 12 | 5 | 2 | Classical | Opera | | 3 | Jazz | 13 | 4 | 3 | Popular | Jazz | | 4 | Music | 4 | 3 | 4 | Classical | Dance | | 5 | Violin | 25 | 2 | 5 | Classical | NULL | | 6 | Toscana | 16 | 1 | 6 | Classical | NULL | | 7 | Blues | 7 | 22 | 7 | Popular | Blues | | 8 | Pure | 38 | 5 | 11 | Popular | NULL | | 9 | Mud | 19 | 11 | 12 | Popular | Country | | 10 | Essence | 5 | 23 | 12 | Popular | New Age | | 11 | Embrace | 21 | 12 | 14 | Popular | New Age | | 12 | Satie | 42 | 17 | 15 | Classical | NULL | | 13 | Lake | 23 | 47 | 28 | Classical | Dance | | 14 | Favorites | 34 | 15 | 12 | Classical | General | | 15 | Boheme | 25 | 12 | 5 | Classical | Opera | | 16 | Cantatas | 26 | 13 | 8 | Classical | General | | 17 | Road | 27 | 13 | 17 | Popular | Country | | 18 | Paris | 18 | 25 | 10 | Popular | Jazz | | 19 | Woman | 29 | 4 | 7 | Popular | Blues | | 20 | Bach | 21 | 13 | 16 | Classical | General | | 21 | Opera | 12 | 32 | 12 | Classical | Opera | | 22 | Soul | 13 | 30 | 14 | Popular | NULL | | 23 | Stages | 44 | 0 | 8 | Popular | Blues | | 24 | Bach | 15 | 1 | 8 | Classical | General | +----+-----------+---------+---------+----------+------------+----------+ 24 rows in set (0.01 sec) mysql> SELECT Name, InStock, OnOrder, Reserved 

   -> FROM Sale
   -> WHERE (InStock+OnOrder-Reserved)<20
   -> ORDER BY Name;

+-----------+---------+---------+----------+ | Name | InStock | OnOrder | Reserved | +-----------+---------+---------+----------+ | Bach | 15 | 1 | 8 | | Bach | 21 | 13 | 16 | | Bloodshot | 11 | 6 | 1 | | Embrace | 21 | 12 | 14 | | Essence | 5 | 23 | 12 | | Jazz | 13 | 4 | 3 | | Mud | 19 | 11 | 12 | | Music | 4 | 3 | 4 | | Opera | 12 | 5 | 2 | | Toscana | 16 | 1 | 6 | +-----------+---------+---------+----------+ 10 rows in set (0.00 sec)

  • /

Drop table Sale;

CREATE TABLE Sale ( 

  ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL,
  OnOrder SMALLINT UNSIGNED NOT NULL,
  Reserved SMALLINT UNSIGNED NOT NULL,
  Department ENUM("Classical", "Popular") NOT NULL,
  Category VARCHAR(20)

);

INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Category) VALUES ("Bloodshot", 11, 6, 1, "Popular", "Rock"), 

               ("Opera",     12,      5,       2,        "Classical","Opera"),
               ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
               ("Music",     4,       3,       4,        "Classical","Dance"),
               ("Violin",    25,      2,       5,        "Classical", NULL),
               ("Toscana",   16,      1,       6,        "Classical", NULL),
               ("Blues",     7,       22,      7,        "Popular",   "Blues"),
               ("Pure",      38,      5,       11,       "Popular",   NULL),
               ("Mud",       19,      11,      12,       "Popular",  "Country"),
               ("Essence",   5,       23,      12,       "Popular", "New Age"),
               ("Embrace",   21,      12,      14,       "Popular", "New Age"),
               ("Satie",     42,      17,      15,       "Classical", NULL),
               ("Lake",      23,      47,      28,       "Classical", "Dance"),
               ("Favorites", 34,      15,      12,       "Classical", "General"),
               ("Boheme",    25,      12,       5,       "Classical", "Opera"),
               ("Cantatas",  26,      13,      8,        "Classical", "General"),
               ("Road",      27,      13,      17,       "Popular", "Country"),
               ("Paris",     18,      25,      10,       "Popular", "Jazz"),
               ("Woman",     29,      4,        7,       "Popular", "Blues"),
               ("Bach",      21,      13,      16,       "Classical", "General"),
               ("Opera",     12,      32,      12,       "Classical", "Opera"),
               ("Soul",      13,      30,      14,       "Popular", NULL),
               ("Stages",    44,      0,       8,        "Popular", "Blues"),
               ("Bach",      15,      1,       8,        "Classical", "General");

select * from Sale;

SELECT Name, InStock, OnOrder, Reserved FROM Sale WHERE (InStock+OnOrder-Reserved)<20 ORDER BY Name; 

      </source>


Do Calculation in Where and order

   <source lang="sql">

/* mysql> Drop table Sale; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE Sale 

   -> (
   ->    ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   ->    Name VARCHAR(50) NOT NULL,
   ->    InStock SMALLINT UNSIGNED NOT NULL,
   ->    OnOrder SMALLINT UNSIGNED NOT NULL,
   ->    Reserved SMALLINT UNSIGNED NOT NULL,
   ->    Department ENUM("Classical", "Popular") NOT NULL,
   ->    Category VARCHAR(20)
   -> );

Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Cate gory) 

   -> VALUES          ("Bloodshot", 11,      6,       1,        "Popular",  "Ro

ck"), 

   ->                 ("Opera",     12,      5,       2,        "Classical","Op

era"), 

   ->                 ("Jazz",      13,      4,       3,        "Popular",  "Ja

zz"), 

   ->                 ("Music",     4,       3,       4,        "Classical","Da

nce"), 

   ->                 ("Violin",    25,      2,       5,        "Classical", NU

LL), 

   ->                 ("Toscana",   16,      1,       6,        "Classical", NU

LL), 

   ->                 ("Blues",     7,       22,      7,        "Popular",   "B

lues"), 

   ->                 ("Pure",      38,      5,       11,       "Popular",   NU

LL), 

   ->                 ("Mud",       19,      11,      12,       "Popular",  "Co

untry"), 

   ->                 ("Essence",   5,       23,      12,       "Popular", "New
Age"),
   ->                 ("Embrace",   21,      12,      14,       "Popular", "New
Age"),
   ->                 ("Satie",     42,      17,      15,       "Classical", NU

LL), 

   ->                 ("Lake",      23,      47,      28,       "Classical", "D

ance"), 

   ->                 ("Favorites", 34,      15,      12,       "Classical", "G

eneral"), 

   ->                 ("Boheme",    25,      12,       5,       "Classical", "O

pera"), 

   ->                 ("Cantatas",  26,      13,      8,        "Classical", "G

eneral"), 

   ->                 ("Road",      27,      13,      17,       "Popular", "Cou

ntry"), 

   ->                 ("Paris",     18,      25,      10,       "Popular", "Jaz

z"), 

   ->                 ("Woman",     29,      4,        7,       "Popular", "Blu

es"), 

   ->                 ("Bach",      21,      13,      16,       "Classical", "G

eneral"), 

   ->                 ("Opera",     12,      32,      12,       "Classical", "O

pera"), 

   ->                 ("Soul",      13,      30,      14,       "Popular", NULL

), 

   ->                 ("Stages",    44,      0,       8,        "Popular", "Blu

es"), 

   ->                 ("Bach",      15,      1,       8,        "Classical", "G

eneral"); Query OK, 24 rows affected (0.00 sec) Records: 24 Duplicates: 0 Warnings: 0 mysql> select * from Sale; +----+-----------+---------+---------+----------+------------+----------+ | ID | Name | InStock | OnOrder | Reserved | Department | Category | +----+-----------+---------+---------+----------+------------+----------+ | 1 | Bloodshot | 11 | 6 | 1 | Popular | Rock | | 2 | Opera | 12 | 5 | 2 | Classical | Opera | | 3 | Jazz | 13 | 4 | 3 | Popular | Jazz | | 4 | Music | 4 | 3 | 4 | Classical | Dance | | 5 | Violin | 25 | 2 | 5 | Classical | NULL | | 6 | Toscana | 16 | 1 | 6 | Classical | NULL | | 7 | Blues | 7 | 22 | 7 | Popular | Blues | | 8 | Pure | 38 | 5 | 11 | Popular | NULL | | 9 | Mud | 19 | 11 | 12 | Popular | Country | | 10 | Essence | 5 | 23 | 12 | Popular | New Age | | 11 | Embrace | 21 | 12 | 14 | Popular | New Age | | 12 | Satie | 42 | 17 | 15 | Classical | NULL | | 13 | Lake | 23 | 47 | 28 | Classical | Dance | | 14 | Favorites | 34 | 15 | 12 | Classical | General | | 15 | Boheme | 25 | 12 | 5 | Classical | Opera | | 16 | Cantatas | 26 | 13 | 8 | Classical | General | | 17 | Road | 27 | 13 | 17 | Popular | Country | | 18 | Paris | 18 | 25 | 10 | Popular | Jazz | | 19 | Woman | 29 | 4 | 7 | Popular | Blues | | 20 | Bach | 21 | 13 | 16 | Classical | General | | 21 | Opera | 12 | 32 | 12 | Classical | Opera | | 22 | Soul | 13 | 30 | 14 | Popular | NULL | | 23 | Stages | 44 | 0 | 8 | Popular | Blues | | 24 | Bach | 15 | 1 | 8 | Classical | General | +----+-----------+---------+---------+----------+------------+----------+ 24 rows in set (0.00 sec) mysql> SELECT Name, InStock, OnOrder, Reserved 

   -> FROM Sale
   -> WHERE (InStock+OnOrder-Reserved)>20
   -> ORDER BY Name;

+-----------+---------+---------+----------+ | Name | InStock | OnOrder | Reserved | +-----------+---------+---------+----------+ | Blues | 7 | 22 | 7 | | Boheme | 25 | 12 | 5 | | Cantatas | 26 | 13 | 8 | | Favorites | 34 | 15 | 12 | | Lake | 23 | 47 | 28 | | Opera | 12 | 32 | 12 | | Paris | 18 | 25 | 10 | | Pure | 38 | 5 | 11 | | Road | 27 | 13 | 17 | | Satie | 42 | 17 | 15 | | Soul | 13 | 30 | 14 | | Stages | 44 | 0 | 8 | | Violin | 25 | 2 | 5 | | Woman | 29 | 4 | 7 | +-----------+---------+---------+----------+ 14 rows in set (0.01 sec)

  • /

Drop table Sale;

CREATE TABLE Sale ( 

  ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  InStock SMALLINT UNSIGNED NOT NULL,
  OnOrder SMALLINT UNSIGNED NOT NULL,
  Reserved SMALLINT UNSIGNED NOT NULL,
  Department ENUM("Classical", "Popular") NOT NULL,
  Category VARCHAR(20)

);

INSERT INTO Sale (Name, InStock, OnOrder, Reserved, Department, Category) VALUES ("Bloodshot", 11, 6, 1, "Popular", "Rock"), 

               ("Opera",     12,      5,       2,        "Classical","Opera"),
               ("Jazz",      13,      4,       3,        "Popular",  "Jazz"),
               ("Music",     4,       3,       4,        "Classical","Dance"),
               ("Violin",    25,      2,       5,        "Classical", NULL),
               ("Toscana",   16,      1,       6,        "Classical", NULL),
               ("Blues",     7,       22,      7,        "Popular",   "Blues"),
               ("Pure",      38,      5,       11,       "Popular",   NULL),
               ("Mud",       19,      11,      12,       "Popular",  "Country"),
               ("Essence",   5,       23,      12,       "Popular", "New Age"),
               ("Embrace",   21,      12,      14,       "Popular", "New Age"),
               ("Satie",     42,      17,      15,       "Classical", NULL),
               ("Lake",      23,      47,      28,       "Classical", "Dance"),
               ("Favorites", 34,      15,      12,       "Classical", "General"),
               ("Boheme",    25,      12,       5,       "Classical", "Opera"),
               ("Cantatas",  26,      13,      8,        "Classical", "General"),
               ("Road",      27,      13,      17,       "Popular", "Country"),
               ("Paris",     18,      25,      10,       "Popular", "Jazz"),
               ("Woman",     29,      4,        7,       "Popular", "Blues"),
               ("Bach",      21,      13,      16,       "Classical", "General"),
               ("Opera",     12,      32,      12,       "Classical", "Opera"),
               ("Soul",      13,      30,      14,       "Popular", NULL),
               ("Stages",    44,      0,       8,        "Popular", "Blues"),
               ("Bach",      15,      1,       8,        "Classical", "General");

select * from Sale;

SELECT Name, InStock, OnOrder, Reserved FROM Sale WHERE (InStock+OnOrder-Reserved)>20 ORDER BY Name; 

      </source>


Not equal in where

   <source lang="sql">

/* mysql> select * from Student; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 1 | John | Jones | | 2 | Gary | Burton | | 3 | Emily | Scarlett | | 4 | Bruce | Lee | | 5 | Anna | Wolff | | 6 | Vic | Andrews | | 7 | Steve | Alaska | +-----------+------------+-----------+ 7 rows in set (0.00 sec) mysql> select * from Student WHERE first_name != "Bruce" AND last_name != "Jones "; +-----------+------------+-----------+ | StudentID | first_name | last_name | +-----------+------------+-----------+ | 2 | Gary | Burton | | 3 | Emily | Scarlett | | 5 | Anna | Wolff | | 6 | Vic | Andrews | | 7 | Steve | Alaska | +-----------+------------+-----------+ 5 rows in set (0.02 sec)

  • /

Drop table Student; CREATE TABLE Student ( 

  StudentID INT NOT NULL PRIMARY KEY,
  first_name      VARCHAR(50) NOT NULL,
  last_name      VARCHAR(50) NOT NULL

)TYPE = InnoDB;

INSERT INTO Student (StudentID,first_name, last_name) VALUES (4,"Bruce", "Lee"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (1,"John", "Jones"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (2,"Gary", "Burton"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (7,"Steve", "Alaska"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (5,"Anna", "Wolff"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (6,"Vic", "Andrews"); INSERT INTO Student (StudentID,first_name, last_name) VALUES (3,"Emily", "Scarlett"); select * from Student;

select * from Student WHERE first_name != "Bruce" AND last_name != "Jones"; 


      </source>


Use where clause the narrow down results

   <source lang="sql">

/*mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.02 sec) mysql> SELECT firstname,lastName from employee where title="Programmer"; +-----------+----------+ | firstname | lastName | +-----------+----------+ | Fred | King | | John | Mac | | Arthur | Sam | | Alok | Nanda | +-----------+----------+ 4 rows in set (0.00 sec)

  • /

Drop table employee; CREATE TABLE employee ( 

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");

select * from employee;

SELECT firstname,lastName from employee where title="Programmer"; 

      </source>


Using Where Conditions

   <source lang="sql">

/* mysql> SELECT Name FROM Student WHERE StudentID = 3; +-------------+ | Name | +-------------+ | JJ Harvests | +-------------+ 1 row in set (0.00 sec)

  • /

/* Prepare the data */ DROP TABLE Student; CREATE TABLE Student ( 

  StudentID INT NOT NULL PRIMARY KEY,
  Name      VARCHAR(50) NOT NULL

)TYPE = InnoDB;

/* Insert data for testing */ INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang"); INSERT INTO Student (StudentID,Name) VALUES (2,"Cory But"); INSERT INTO Student (StudentID,Name) VALUES (3,"JJ Harvests");

/* Real command */ SELECT Name FROM Student WHERE StudentID = 3; 

      </source>


Where clause: calculation and equal condition

   <source lang="sql">

/* mysql> Drop table Books; 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.45 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.36 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.14 sec) mysql> SELECT Name, Category, InStock, OnOrder 

   -> FROM Books
   -> WHERE Category="Fiction" AND (InStock+OnOrder)>40
   -> ORDER BY Name;

+------+----------+---------+---------+ | Name | Category | InStock | OnOrder | +------+----------+---------+---------+ | One | Fiction | 28 | 14 | +------+----------+---------+---------+ 1 row in set (0.05 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" AND (InStock+OnOrder)>40 ORDER BY Name; 


      </source>


Where clause: compare

   <source lang="sql">

/* 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.04 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 InStock>30 OR OnOrder>30
   -> ORDER BY Name;

+--------+------------+---------+---------+ | Name | Category | InStock | OnOrder | +--------+------------+---------+---------+ | Dunces | Fiction | 5 | 35 | | From | NULL | 46 | 31 | | Hello | Nonfiction | 21 | 32 | | Poet | Nonfiction | 35 | 13 | +--------+------------+---------+---------+ 4 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 InStock>30 OR OnOrder>30 ORDER BY Name; 


      </source>


WHERE Clause Comparisons

   <source lang="sql">

/* mysql> SELECT ExamID, SustainedOn, Comments FROM Exam 

   -> WHERE SustainedOn > "2003-03-20";

+--------+-------------+-----------------+ | ExamID | SustainedOn | Comments | +--------+-------------+-----------------+ | 2 | 2004-03-13 | C# test | | 3 | 2005-03-11 | JavaScript Test | +--------+-------------+-----------------+ 2 rows in set (0.02 sec)

  • /

/* Prepare the data */ DROP TABLE Exam; CREATE TABLE Exam ( 

  ExamID      INT NOT NULL PRIMARY KEY,
  SustainedOn DATE,
  Comments    VARCHAR(255)

)TYPE = InnoDB;

/* Insert data for testing */ INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (1,"2003-03-12","Java Test"); INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (2,"2004-03-13","C# test"); INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (3,"2005-03-11","JavaScript Test");

/* Real command */

SELECT ExamID, SustainedOn, Comments FROM Exam WHERE SustainedOn > "2003-03-20"; 

      </source>


Where clause: nested conditions

   <source lang="sql">

/* 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.01 sec) mysql> SELECT Name, Category, InStock, OnOrder 

   -> FROM Books
   -> WHERE InStock>20 AND (Category IS NULL OR NOT (Category="Fiction"))
   -> ORDER BY Name;

+-------+------------+---------+---------+ | Name | Category | InStock | OnOrder | +-------+------------+---------+---------+ | From | NULL | 46 | 31 | | Hello | Nonfiction | 21 | 32 | | Poet | Nonfiction | 35 | 13 | +-------+------------+---------+---------+ 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 InStock>20 AND (Category IS NULL OR NOT (Category="Fiction")) ORDER BY Name; 


      </source>


Where clause: XOR

   <source lang="sql">

/* 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; 


      </source>
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Where_Clause/Where&oldid=5065»