SQL/MySQL/Where Clause/Where — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 10:15, 26 мая 2010
Содержание
- 1 Calculation in WHERE clause
- 2 Combining WHERE Conditions
- 3 Compare and calculate in Where clause
- 4 Do Calculation in Where and order
- 5 Not equal in where
- 6 Use where clause the narrow down results
- 7 Using Where Conditions
- 8 Where clause: calculation and equal condition
- 9 Where clause: compare
- 10 WHERE Clause Comparisons
- 11 Where clause: nested conditions
- 12 Where clause: XOR
Calculation in WHERE clause
/*
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;
Combining WHERE Conditions
/* 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;
Compare and calculate in Where clause
/*
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;
Do Calculation in Where and order
/*
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;
Not equal in where
/*
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";
Use where clause the narrow down results
/*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";
Using Where Conditions
/*
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;
Where clause: calculation and equal condition
/*
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;
Where clause: compare
/*
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;
WHERE Clause Comparisons
/*
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";
Where clause: nested conditions
/*
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;
Where clause: 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;