SQL/MySQL/Date Time/Date Compare Order
Содержание
Compare date 2
/*
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.00 sec)
mysql> select id, birthday
-> from employee_person where
-> birthday >= "1970-01-01";
+----+------------+
| id | birthday |
+----+------------+
| 1 | 1971-04-26 |
| 4 | 1976-08-09 |
| 5 | 1974-10-14 |
| 6 | 1978-12-31 |
| 8 | 1975-01-14 |
| 17 | 1970-04-18 |
| 18 | 1973-10-09 |
| 19 | 1973-01-20 |
+----+------------+
8 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, birthday
from employee_person where
birthday >= "1970-01-01";
Compare date in where clause
/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe | Car | f | 1999-03-30 | NULL |
| RedBird | Yin | Car | m | 1979-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql> /* which were born after 1998, test the birth column: */
mysql> SELECT * FROM Bird WHERE birth > "1998-1-1";
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe | Car | f | 1999-03-30 | NULL |
+----------+-------+---------+------+------------+-------+
1 row in set (0.00 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Car","m","1979-03-30",NULL);
select * from Bird;
/* which were born after 1998, test the birth column: */
SELECT * FROM Bird WHERE birth > "1998-1-1";
Compare Date in where clause: between and
/*
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.00 sec)
mysql> select id, birthday
-> from employee_person where
-> birthday >= "1969-01-01"
-> AND birthday <= "1974-01-01";
+----+------------+
| id | birthday |
+----+------------+
| 1 | 1971-04-26 |
| 9 | 1969-05-19 |
| 17 | 1970-04-18 |
| 18 | 1973-10-09 |
| 19 | 1973-01-20 |
+----+------------+
5 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, birthday
from employee_person where
birthday >= "1969-01-01"
AND birthday <= "1974-01-01";
Order date
/*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.00 sec)
mysql> SELECT firstname, lastName, yearofservice
-> from employee
-> ORDER by yearofservice;
+-----------+----------+---------------+
| firstname | lastName | yearofservice |
+-----------+----------+---------------+
| Joe | Irvine | 1 |
| Jack | Sim | 1 |
| Mike | Harper | 1 |
| Paul | Simon | 1 |
| Peter | Champion | 2 |
| Danny | Gibson | 2 |
| Edward | Parhar | 2 |
| Arthur | Sam | 2 |
| Mary | Anchor | 2 |
| Alok | Nanda | 3 |
| Henry | Ali | 3 |
| Ane | Pandit | 3 |
| Fred | King | 3 |
| Roger | Lewis | 3 |
| John | Chen | 3 |
| Kim | Hunter | 4 |
| John | Mac | 4 |
| Jan | Pillai | 4 |
| Susan | Ra | 4 |
| Mary | Sunday | 5 |
+-----------+----------+---------------+
20 rows in set (0.01 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, yearofservice
from employee
ORDER by yearofservice;
Use BETWEEN and AND for a date data type
/*
mysql> Drop table timeTable;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE timeTable (
-> year YEAR(4),
-> month INT(2) UNSIGNED ZEROFILL,
-> day INT(2) UNSIGNED ZEROFILL
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO timeTable VALUES(2001,1,1),
-> (2002,6,20),
-> (2003,5,30),
-> (2004,2,2),
-> (2005,4,23),
-> (2006,3,23);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from timeTable;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2001 | 01 | 01 |
| 2002 | 06 | 20 |
| 2003 | 05 | 30 |
| 2004 | 02 | 02 |
| 2005 | 04 | 23 |
| 2006 | 03 | 23 |
+------+-------+------+
6 rows in set (0.01 sec)
mysql> select * from TimeTable WHERE year BETWEEN 2001 AND 2003;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2001 | 01 | 01 |
| 2002 | 06 | 20 |
| 2003 | 05 | 30 |
+------+-------+------+
3 rows in set (0.00 sec)
*/
Drop table timeTable;
CREATE TABLE timeTable (
year YEAR(4),
month INT(2) UNSIGNED ZEROFILL,
day INT(2) UNSIGNED ZEROFILL
);
INSERT INTO timeTable VALUES(2001,1,1),
(2002,6,20),
(2003,5,30),
(2004,2,2),
(2005,4,23),
(2006,3,23);
select * from timeTable;
select * from TimeTable WHERE year BETWEEN 2001 AND 2003;
Use BETWEEN AND for date data type
/*
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.00 sec)
mysql> select id, birthday
-> from employee_person where
-> birthday BETWEEN
-> "1969-01-01" AND "1974-01-01";
+----+------------+
| id | birthday |
+----+------------+
| 1 | 1971-04-26 |
| 9 | 1969-05-19 |
| 17 | 1970-04-18 |
| 18 | 1973-10-09 |
| 19 | 1973-01-20 |
+----+------------+
5 rows in set (0.01 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, birthday
from employee_person where
birthday BETWEEN
"1969-01-01" AND "1974-01-01";