SQL/MySQL/Data Type/Null — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 10:16, 26 мая 2010
Содержание
- 1 Concatenate string with NULL value
- 2 Dealing With NULL Data
- 3 Disallowing NULLs
- 4 IS NOT NULL in where clause
- 5 List NOT NULL value and order it
- 6 NULL means "not having a value"
- 7 NULL value in where clause
- 8 Read NULL value
- 9 Retrieve NOT NULL value
- 10 Retrieve NULL value
- 11 Select NOT NULL value
- 12 Use the IS NULL and IS NOT NULL operators
- 13 Working with NULL Values
Concatenate string with NULL value
/*
mysql> select CONCAT("a",null);
+------------------+
| CONCAT("a",null) |
+------------------+
| NULL |
+------------------+
1 row in set (0.00 sec)
*/
select CONCAT("a",null);
Dealing With NULL Data
/*
mysql> SELECT ExamID,SustainedOn,Comments FROM Exam
-> WHERE Comments IS NULL;
+--------+-------------+----------+
| ExamID | SustainedOn | Comments |
+--------+-------------+----------+
| 1 | 2003-03-12 | NULL |
+--------+-------------+----------+
1 row in set (0.00 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",NULL);
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 Comments IS NULL;
Disallowing NULLs
/*
mysql> DROP TABLE Employee;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE Employee (
-> Name VARCHAR(50) NOT NULL,
-> Phone VARCHAR(15) NOT NULL
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> Describe Employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name | varchar(50) | | | | |
| Phone | varchar(15) | | | | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> INSERT INTO Employee (Name, Phone)
-> VALUES ("Joe Wang", "666 2323");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employee (Name) VALUES ("John Doe");
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Employee (Name, Phone) VALUES ("John Doe", NULL);
ERROR 1048 (23000): Column "Phone" cannot be null
mysql> Select * from Employee;
+----------+----------+
| Name | Phone |
+----------+----------+
| Joe Wang | 666 2323 |
| John Doe | |
+----------+----------+
2 rows in set (0.00 sec)
*/
DROP TABLE Employee;
CREATE TABLE Employee (
Name VARCHAR(50) NOT NULL,
Phone VARCHAR(15) NOT NULL
);
Describe Employee;
INSERT INTO Employee (Name, Phone)
VALUES ("Joe Wang", "666 2323");
INSERT INTO Employee (Name) VALUES ("John Doe");
INSERT INTO Employee (Name, Phone) VALUES ("John Doe", NULL);
Select * from Employee;
IS NOT NULL 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.12 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, Department, Category
-> FROM Sale
-> WHERE Category IS NOT NULL
-> ORDER BY Name;
+-----------+------------+----------+
| Name | Department | Category |
+-----------+------------+----------+
| Bach | Classical | General |
| Bach | Classical | General |
| Bloodshot | Popular | Rock |
| Blues | Popular | Blues |
| Boheme | Classical | Opera |
| Cantatas | Classical | General |
| Embrace | Popular | New Age |
| Essence | Popular | New Age |
| Favorites | Classical | General |
| Jazz | Popular | Jazz |
| Lake | Classical | Dance |
| Mud | Popular | Country |
| Music | Classical | Dance |
| Opera | Classical | Opera |
| Opera | Classical | Opera |
| Paris | Popular | Jazz |
| Road | Popular | Country |
| Stages | Popular | Blues |
| Woman | Popular | Blues |
+-----------+------------+----------+
19 rows in set (0.02 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, Department, Category
FROM Sale
WHERE Category IS NOT NULL
ORDER BY Name;
List NOT NULL value and order it
/*
mysql> select * from employee_person;
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| id | address | phone | email | birthday | sex | m_status | s_name | children |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| 1 | 200, Regina Street | 7176666 | net@hotmail.ru | 1971-04-26 | M | Y | Ane Regina | NULL |
| 2 | 1232 Alberta Road | 5553312 | jo@hotmail.ru | 1968-03-02 | M | Y | Jane Van | 3 |
| 3 | 90 Potter A | 3331211 | gp@ymail.ru | 1967-09-22 | M | N | Sandhya Pil | 2 |
| 4 | 202, Donna Street | 7176167 | twink@hotmail.ru | 1976-08-09 | F | Y | Manish Sha | NULL |
| 5 | Apartment #8, UBC, Van Street | 8973242 | holy@ymail.ru | 1974-10-14 | F | N | NULL | NULL |
| 6 | 46 SFU Street | 6451234 | kill@el.ru | 1978-12-31 | M | N | NULL | NULL |
| 7 | 432 Mercury Ave | 7932232 | mac@hotmail.ru | 1966-08-21 | M | Y | Mary Shelly | 3 |
| 8 | 8 Little YaleTown | 5442994 | edd@gmail.ru | 1975-01-14 | M | N | NULL | NULL |
| 9 | 64 Temp Road | 4327652 | nan@pmail.ru | 1969-05-19 | M | Y | Man Nanda | 1 |
| 10 | 132 Metro House, Henry Street | 5552376 | ra@hotmail.ru | 1968-07-06 | M | N | NULL | NULL |
| 11 | 1 Grace Town, Van Avenue | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M | Y | Muriel Lovelace | 4 |
| 12 | 97 Oakland Road | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M | Y | Rina Brighton | 3 |
| 13 | 543 Applegate Lane | 3434343 | levy@cmail.ru | 1968-09-03 | F | Y | Matt Shi | 2 |
| 14 | 76 Fish Street | 7432433 | tink@email.ru | 1965-04-28 | M | N | NULL | NULL |
| 15 | 98 Gun Street | 6500787 | danny@fhardy.ru | 1966-06-23 | M | Y | Betty Cudly | 3 |
| 16 | #5 Winnepag Homes | 5433243 | mike@cmail.ru | 1964-03-06 | M | Y | Stella Stevens | 2 |
| 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.ru | 1970-04-18 | F | Y | Edgar Alan | 1 |
| 18 | Apartment #9, Together Towers | 5476565 | odessey@hotmail.ru | 1973-10-09 | M | N | NULL | NULL |
| 19 | Apartment #9, West Towers | 5476565 | jire@hotmail.ru | 1973-01-20 | M | N | NULL | NULL |
| 20 | 90 Yale Town | 7528326 | help@more.org | 1968-01-25 | F | N | NULL | NULL |
| 21 | 4329 Eucalyptus Avenue | 4254863 | money@cold.ru | 1964-06-13 | M | Y | Ruby Richer | 2 |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
21 rows in set (0.01 sec)
mysql> select id, s_name
-> from employee_person
-> where s_name IS NOT NULL
-> ORDER BY s_name;
+----+-----------------+
| id | s_name |
+----+-----------------+
| 1 | Ane Regina |
| 15 | Betty Cudly |
| 17 | Edgar Alan |
| 2 | Jane Van |
| 9 | Man Nanda |
| 4 | Manish Sha |
| 7 | Mary Shelly |
| 13 | Matt Shi |
| 11 | Muriel Lovelace |
| 12 | Rina Brighton |
| 21 | Ruby Richer |
| 3 | Sandhya Pil |
| 16 | Stella Stevens |
+----+-----------------+
13 rows in set (0.00 sec)
*/
Drop table employee_person;
CREATE TABLE employee_person (
id int unsigned not null primary key,
address varchar(60),
phone int,
email varchar(60),
birthday DATE,
sex ENUM("M", "F"),
m_status ENUM("Y","N"),
s_name varchar(40),
children int
);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, "200, Regina Street", 7176666, "net@hotmail.ru", "1971-04-26", "M", "Y", "Ane Regina");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, "1232 Alberta Road", 5553312, "jo@hotmail.ru", "1968-03-02", "M", "Y", "Jane Van", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, "90 Potter A", 3331211, "gp@ymail.ru", "1967-09-22", "M", "N", "Sandhya Pil", 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, "202, Donna Street", 7176167, "twink@hotmail.ru", "1976-08-09", "F", "Y", "Manish Sha");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, "Apartment #8, UBC, Van Street", 8973242, "holy@ymail.ru", "1974-10-14", "F", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, "46 SFU Street", "6451234", "kill@el.ru", "1978-12-31", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, "432 Mercury Ave", 7932232, "mac@hotmail.ru", "1966-8-21", "M", "Y", "Mary Shelly", "3");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, "8 Little YaleTown", 5442994, "edd@gmail.ru", "1975-01-14", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, "64 Temp Road", 4327652, "nan@pmail.ru", "1969-05-19", "M", "Y", "Man Nanda", "1");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, "132 Metro House, Henry Street", 5552376, "ra@hotmail.ru", "1968-07-06", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, "1 Grace Town, Van Avenue", 5433879, "soundofsilence@boxer.net", "1957-11-04", "M", "Y", "Muriel Lovelace", "4");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, "97 Oakland Road", 5423311, "kingarthur@roundtable.org", "1968-02-15", "M", "Y", "Rina Brighton", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, "543 Applegate Lane", 3434343, "levy@cmail.ru", "1968-09-03", "F", "Y", "Matt Shi", "2");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, "76 Fish Street", 7432433, "tink@email.ru", "1965-04-28", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, "98 Gun Street", 6500787, "danny@fhardy.ru", "1966-06-23", "M", "Y", "Betty Cudly", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, "#5 Winnepag Homes", 5433243, "mike@cmail.ru", "1964-03-06", "M", "Y", "Stella Stevens", 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, "652 Devon Building, 6th Jade Avenue", 5537885, "mona@darling.ru", "1970-04-18", "F", "Y", "Edgar Alan", 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, "Apartment #9, Together Towers", 5476565, "odessey@hotmail.ru", "1973-10-09", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, "Apartment #9, West Towers", 5476565, "jire@hotmail.ru", "1973-1-20", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, "90 Yale Town", 7528326, "help@more.org", "1968-01-25", "F", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, "4329 Eucalyptus Avenue", 4254863, "money@cold.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2);
select * from employee_person;
select id, s_name
from employee_person
where s_name IS NOT NULL
ORDER BY s_name;
NULL means "not having a value"
/*
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)
*/
SELECT 0 IS NULL, 0 IS NOT NULL, "" IS NULL, "" IS NOT NULL;
NULL value 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.12 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.00 sec)
mysql> SELECT Name, Department, Category
-> FROM Sale
-> WHERE Category IS NOT NULL
-> ORDER BY Name;
+-----------+------------+----------+
| Name | Department | Category |
+-----------+------------+----------+
| Bach | Classical | General |
| Bach | Classical | General |
| Bloodshot | Popular | Rock |
| Blues | Popular | Blues |
| Boheme | Classical | Opera |
| Cantatas | Classical | General |
| Embrace | Popular | New Age |
| Essence | Popular | New Age |
| Favorites | Classical | General |
| Jazz | Popular | Jazz |
| Lake | Classical | Dance |
| Mud | Popular | Country |
| Music | Classical | Dance |
| Opera | Classical | Opera |
| Opera | Classical | Opera |
| Paris | Popular | Jazz |
| Road | Popular | Country |
| Stages | Popular | Blues |
| Woman | Popular | Blues |
+-----------+------------+----------+
19 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, Department, Category
FROM Sale
WHERE Category IS NOT NULL
ORDER BY Name;
Read NULL value
/*
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.07 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.00 sec)
mysql> SELECT Name, Department, Category
-> FROM Sale
-> WHERE Category IS NULL
-> ORDER BY Name;
+---------+------------+----------+
| Name | Department | Category |
+---------+------------+----------+
| Pure | Popular | NULL |
| Satie | Classical | NULL |
| Soul | Popular | NULL |
| Toscana | Classical | NULL |
| Violin | Classical | NULL |
+---------+------------+----------+
5 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, Department, Category
FROM Sale
WHERE Category IS NULL
ORDER BY Name;
Retrieve NOT NULL value
/*
mysql> select * from employee_person;
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| id | address | phone | email | birthday | sex | m_status | s_name | children |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| 1 | 200, Regina Street | 7176666 | net@hotmail.ru | 1971-04-26 | M | Y | Ane Regina | NULL |
| 2 | 1232 Alberta Road | 5553312 | jo@hotmail.ru | 1968-03-02 | M | Y | Jane Van | 3 |
| 3 | 90 Potter A | 3331211 | gp@ymail.ru | 1967-09-22 | M | N | Sandhya Pil | 2 |
| 4 | 202, Donna Street | 7176167 | twink@hotmail.ru | 1976-08-09 | F | Y | Manish Sha | NULL |
| 5 | Apartment #8, UBC, Van Street | 8973242 | holy@ymail.ru| 1974-10-14 | F | N | NULL | NULL |
| 6 | 46 SFU Street | 6451234 | kill@el.ru | 1978-12-31 | M | N | NULL | NULL |
| 7 | 432 Mercury Ave | 7932232 | mac@hotmail.ru | 1966-08-21 | M | Y | Mary Shelly | 3 |
| 8 | 8 Little YaleTown | 5442994 | edd@gmail.ru | 1975-01-14 | M | N | NULL | NULL |
| 9 | 64 Temp Road | 4327652 | nan@pmail.ru | 1969-05-19 | M | Y | Man Nanda | 1 |
| 10 | 132 Metro House, Henry Street | 5552376 | ra@hotmail.ru | 1968-07-06 | M | N | NULL | NULL |
| 11 | 1 Grace Town, Van Avenue | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M | Y | Muriel Lovelace | 4 |
| 12 | 97 Oakland Road | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M | Y | Rina Brighton | 3 |
| 13 | 543 Applegate Lane | 3434343 | levy@cmail.ru | 1968-09-03 | F | Y | Matt Shi | 2 |
| 14 | 76 Fish Street | 7432433 | tink@email.ru | 1965-04-28 | M | N | NULL | NULL |
| 15 | 98 Gun Street | 6500787 | danny@fhardy.ru | 1966-06-23 | M | Y | Betty Cudly | 3 |
| 16 | #5 Winnepag Homes | 5433243 | mike@cmail.ru | 1964-03-06 | M | Y | Stella Stevens | 2 |
| 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.ru | 1970-04-18 | F | Y | Edgar Alan | 1 |
| 18 | Apartment #9, Together Towers | 5476565 | odessey@hotmail.ru | 1973-10-09 | M | N | NULL | NULL |
| 19 | Apartment #9, West Towers | 5476565 | jire@hotmail.ru | 1973-01-20 | M | N | NULL | NULL |
| 20 | 90 Yale Town | 7528326 | help@more.org | 1968-01-25 | F | N | NULL | NULL |
| 21 | 4329 Eucalyptus Avenue | 4254863 | money@cold.ru | 1964-06-13 | M | Y | Ruby Richer | 2 |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
21 rows in set (0.02 sec)
mysql> select id, s_name from employee_person where s_name IS NOT NULL;
+----+-----------------+
| id | s_name |
+----+-----------------+
| 1 | Ane Regina |
| 2 | Jane Van |
| 3 | Sandhya Pil |
| 4 | Manish Sha |
| 7 | Mary Shelly |
| 9 | Man Nanda |
| 11 | Muriel Lovelace |
| 12 | Rina Brighton |
| 13 | Matt Shi |
| 15 | Betty Cudly |
| 16 | Stella Stevens |
| 17 | Edgar Alan |
| 21 | Ruby Richer |
+----+-----------------+
13 rows in set (0.00 sec)
*/
Drop table employee_person;
CREATE TABLE employee_person (
id int unsigned not null primary key,
address varchar(60),
phone int,
email varchar(60),
birthday DATE,
sex ENUM("M", "F"),
m_status ENUM("Y","N"),
s_name varchar(40),
children int
);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, "200, Regina Street", 7176666, "net@hotmail.ru", "1971-04-26", "M", "Y", "Ane Regina");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, "1232 Alberta Road", 5553312, "jo@hotmail.ru", "1968-03-02", "M", "Y", "Jane Van", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, "90 Potter A", 3331211, "gp@ymail.ru", "1967-09-22", "M", "N", "Sandhya Pil", 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, "202, Donna Street", 7176167, "twink@hotmail.ru", "1976-08-09", "F", "Y", "Manish Sha");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, "Apartment #8, UBC, Van Street", 8973242, "holy@ymail.ru", "1974-10-14", "F", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, "46 SFU Street", "6451234", "kill@el.ru", "1978-12-31", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, "432 Mercury Ave", 7932232, "mac@hotmail.ru", "1966-8-21", "M", "Y", "Mary Shelly", "3");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, "8 Little YaleTown", 5442994, "edd@gmail.ru", "1975-01-14", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, "64 Temp Road", 4327652, "nan@pmail.ru", "1969-05-19", "M", "Y", "Man Nanda", "1");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, "132 Metro House, Henry Street", 5552376, "ra@hotmail.ru", "1968-07-06", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, "1 Grace Town, Van Avenue", 5433879, "soundofsilence@boxer.net", "1957-11-04", "M", "Y", "Muriel Lovelace", "4");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, "97 Oakland Road", 5423311, "kingarthur@roundtable.org", "1968-02-15", "M", "Y", "Rina Brighton", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, "543 Applegate Lane", 3434343, "levy@cmail.ru", "1968-09-03", "F", "Y", "Matt Shi", "2");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, "76 Fish Street", 7432433, "tink@email.ru", "1965-04-28", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, "98 Gun Street", 6500787, "danny@fhardy.ru", "1966-06-23", "M", "Y", "Betty Cudly", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, "#5 Winnepag Homes", 5433243, "mike@cmail.ru", "1964-03-06", "M", "Y", "Stella Stevens", 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, "652 Devon Building, 6th Jade Avenue", 5537885, "mona@darling.ru", "1970-04-18", "F", "Y", "Edgar Alan", 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, "Apartment #9, Together Towers", 5476565, "odessey@hotmail.ru", "1973-10-09", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, "Apartment #9, West Towers", 5476565, "jire@hotmail.ru", "1973-1-20", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, "90 Yale Town", 7528326, "help@more.org", "1968-01-25", "F", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, "4329 Eucalyptus Avenue", 4254863, "money@cold.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2);
select * from employee_person;
select id, s_name from employee_person where s_name IS NOT NULL;
Retrieve NULL value
/*
mysql> select * from Student;
+-----------+------------+-----------+
| StudentID | first_name | last_name |
+-----------+------------+-----------+
| 1 | John | Jones |
| 2 | NULL | Burton |
| 3 | Emily | Scarlett |
| 4 | Bruce | Lee |
| 5 | Anna | Wolff |
| 6 | Vic | Andrews |
| 7 | NULL | Alaska |
+-----------+------------+-----------+
7 rows in set (0.01 sec)
mysql> select * from Student WHERE first_name IS NULL;
+-----------+------------+-----------+
| StudentID | first_name | last_name |
+-----------+------------+-----------+
| 2 | NULL | Burton |
| 7 | NULL | Alaska |
+-----------+------------+-----------+
2 rows in set (0.02 sec)
*/
Drop table Student;
CREATE TABLE Student (
StudentID INT NOT NULL PRIMARY KEY,
first_name VARCHAR(50),
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, last_name) VALUES (2, "Burton");
INSERT INTO Student (StudentID, last_name) VALUES (7, "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 IS NULL;
Select NOT NULL value
/*
mysql> select * from employee_person;
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| id | address | phone | email | birthday | sex | m_status | s_name | children |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| 1 | 200, Regina Street | 7176666 | net@hotmail.ru | 1971-04-26 | M | Y | Ane Regina | NULL |
| 2 | 1232 Alberta Road | 5553312 | jo@hotmail.ru | 1968-03-02 | M | Y | Jane Van | 3 |
| 3 | 90 Potter A | 3331211 | gp@ymail.ru | 1967-09-22 | M | N | Sandhya Pil | 2 |
| 4 | 202, Donna Street | 7176167 | twink@hotmail.ru | 1976-08-09 | F | Y | Manish Sha | NULL |
| 5 | Apartment #8, UBC, Van Street | 8973242 | holy@ymail.ru | 1974-10-14 | F | N | NULL | NULL |
| 6 | 46 SFU Street | 6451234 | kill@el.ru | 1978-12-31 | M | N | NULL | NULL |
| 7 | 432 Mercury Ave | 7932232 | mac@hotmail.ru | 1966-08-21 | M | Y | Mary Shelly | 3 |
| 8 | 8 Little YaleTown | 5442994 | edd@gmail.ru | 1975-01-14 | M | N | NULL | NULL |
| 9 | 64 Temp Road | 4327652 | nan@pmail.ru | 1969-05-19 | M | Y | Man Nanda | 1 |
| 10 | 132 Metro House, Henry Street | 5552376 | ra@hotmail.ru | 1968-07-06 | M | N | NULL | NULL |
| 11 | 1 Grace Town, Van Avenue | 5433879 | soundofsilence@boxer.net | 1957-11-04 | M | Y | Muriel Lovelace | 4 |
| 12 | 97 Oakland Road | 5423311 | kingarthur@roundtable.org | 1968-02-15 | M | Y | Rina Brighton | 3 |
| 13 | 543 Applegate Lane | 3434343 | levy@cmail.ru | 1968-09-03 | F | Y | Matt Shi | 2 |
| 14 | 76 Fish Street | 7432433 | tink@email.ru | 1965-04-28 | M | N | NULL | NULL |
| 15 | 98 Gun Street | 6500787 | danny@fhardy.ru | 1966-06-23 | M | Y | Betty Cudly | 3 |
| 16 | #5 Winnepag Homes | 5433243 | mike@cmail.ru | 1964-03-06 | M | Y | Stella Stevens | 2 |
| 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | mona@darling.ru | 1970-04-18 | F | Y | Edgar Alan | 1 |
| 18 | Apartment #9, Together Towers | 5476565 | odessey@hotmail.om | 1973-10-09 | M | N | NULL | NULL |
| 19 | Apartment #9, West Towers | 5476565 | jire@hotmail.ru | 1973-01-20 | M | N | NULL | NULL |
| 20 | 90 Yale Town | 7528326 | help@more.org | 1968-01-25 | F | N | NULL | NULL |
| 21 | 4329 Eucalyptus Avenue | 4254863 | money@cold.ru | 1964-06-13 | M | Y | Ruby Richer | 2 |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
21 rows in set (0.00 sec)
mysql> select id, children from employee_person where children IS NOT NULL;
+----+----------+
| id | children |
+----+----------+
| 2 | 3 |
| 3 | 2 |
| 7 | 3 |
| 9 | 1 |
| 11 | 4 |
| 12 | 3 |
| 13 | 2 |
| 15 | 3 |
| 16 | 2 |
| 17 | 1 |
| 21 | 2 |
+----+----------+
11 rows in set (0.00 sec)
*/
Drop table employee_person;
CREATE TABLE employee_person (
id int unsigned not null primary key,
address varchar(60),
phone int,
email varchar(60),
birthday DATE,
sex ENUM("M", "F"),
m_status ENUM("Y","N"),
s_name varchar(40),
children int
);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, "200, Regina Street", 7176666, "net@hotmail.ru", "1971-04-26", "M", "Y", "Ane Regina");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, "1232 Alberta Road", 5553312, "jo@hotmail.ru", "1968-03-02", "M", "Y", "Jane Van", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, "90 Potter A", 3331211, "gp@ymail.ru", "1967-09-22", "M", "N", "Sandhya Pil", 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, "202, Donna Street", 7176167, "twink@hotmail.ru", "1976-08-09", "F", "Y", "Manish Sha");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, "Apartment #8, UBC, Van Street", 8973242, "holy@ymail.ru", "1974-10-14", "F", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, "46 SFU Street", "6451234", "kill@el.ru", "1978-12-31", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, "432 Mercury Ave", 7932232, "mac@hotmail.ru", "1966-8-21", "M", "Y", "Mary Shelly", "3");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, "8 Little YaleTown", 5442994, "edd@gmail.ru", "1975-01-14", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, "64 Temp Road", 4327652, "nan@pmail.ru", "1969-05-19", "M", "Y", "Man Nanda", "1");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, "132 Metro House, Henry Street", 5552376, "ra@hotmail.ru", "1968-07-06", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, "1 Grace Town, Van Avenue", 5433879, "soundofsilence@boxer.net", "1957-11-04", "M", "Y", "Muriel Lovelace", "4");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, "97 Oakland Road", 5423311, "kingarthur@roundtable.org", "1968-02-15", "M", "Y", "Rina Brighton", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, "543 Applegate Lane", 3434343, "levy@cmail.ru", "1968-09-03", "F", "Y", "Matt Shi", "2");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, "76 Fish Street", 7432433, "tink@email.ru", "1965-04-28", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, "98 Gun Street", 6500787, "danny@fhardy.ru", "1966-06-23", "M", "Y", "Betty Cudly", 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, "#5 Winnepag Homes", 5433243, "mike@cmail.ru", "1964-03-06", "M", "Y", "Stella Stevens", 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, "652 Devon Building, 6th Jade Avenue", 5537885, "mona@darling.ru", "1970-04-18", "F", "Y", "Edgar Alan", 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, "Apartment #9, Together Towers", 5476565, "odessey@hotmail.ru", "1973-10-09", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, "Apartment #9, West Towers", 5476565, "jire@hotmail.ru", "1973-1-20", "M", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, "90 Yale Town", 7528326, "help@more.org", "1968-01-25", "F", "N");
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, "4329 Eucalyptus Avenue", 4254863, "money@cold.ru", "1964-06-13", "M", "Y", "Ruby Richer", 2);
select * from employee_person;
select id, children from employee_person where children IS NOT NULL;
Use the IS NULL and IS NOT NULL operators
/*
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)
*/
SELECT 1 IS NULL, 1 IS NOT NULL;
Working with NULL Values
/*
mysql> /*
mysql> NULL means a missing unknown value
mysql> is treated somewhat differently than other values.
mysql> */
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)
*/
/*
NULL means a missing unknown value
is treated somewhat differently than other values.
*/
SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;