MySQL8 - Day4 - Table
贡光明
Selecting all data
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | 0000-00-00 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | 0000-00-00 |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1990-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.01 sec)
Now the birth of Bower , the death of Chirpy and whistler didn’t seem quite right.
Two ways to fix this:
- DELETE and LOAD DATA again.
however, Puffball’s data will be lost.
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
- UPDATE statement
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE pet SET death = 'NULL' WHERE name = 'Chirpy';
ERROR 1292 (22007): Incorrect date value: 'NULL' for column 'death' at row 6
mysql> UPDATE pet SET death = NULL WHERE name = 'Chirpy';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE pet SET death = NULL WHERE name = 'Whistler';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1990-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)
Selecting Particular Rows
As shown in the preceding section, it is easy to retrieve an entire table. and typically you don’t want to see the entire table, particularly when it becomes large.
Instead, you’re usually more interested in answering a particular question, in which case you specify some constrains on the information you want.
Select one row
mysql> SELECT * FROM pet WHERE name = 'Bowser'
-> ;
+--------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
1 row in set (0.00 sec)
Specify conditions to select records
mysql> SELECT * FROM pet WHERE birth >= '1990-1-1';
+----------+--------+---------+------+------------+-------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puffball | Diane | hamster | f | 1990-03-30 | NULL |
+----------+--------+---------+------+------------+-------+
7 rows in set (0.00 sec)
Combine conditions to select records
AND has high precedence than OR.
mysql> SELECT * FROM pet WHERE species = 'cat' AND sex = 'f';
+--------+--------+---------+------+------------+-------+
| name | owener | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
+--------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM pet WHERE species = 'dog' OR sex = 'f';
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1990-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex ='f')OR (species = 'dog' OR sex = 'f');
+----------+--------+---------+------+------------+------------+
| name | owener | species | sex | birth | death |
+----------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puffball | Diane | hamster | f | 1990-03-30 | NULL |
+----------+--------+---------+------+------------+------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex ='f')OR (species = 'dog' AND sex = 'f');
+--------+--------+---------+------+------------+-------+
| name | owener | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
Selecting Particular Columns
Just find the typo of owner on the table, so baidu and change it
mysql> ALTER TABLE pet CHANGE owener owner varchar(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW TABLE
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> SHOW TABLES
-> ;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| pet |
+---------------------+
1 row in set (0.00 sec)
mysql> DESCRIBE PET;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
Select columns
mysql> SELECT name, owner FROM pet;
+----------+--------+
| name | owner |
+----------+--------+
| Fluffy | Harold |
| Claws | Gwen |
| Buffy | Harold |
| Fang | Benny |
| Bowser | Diane |
| Chirpy | Gwen |
| Whistler | Gwen |
| Slim | Benny |
| Puffball | Diane |
+----------+--------+
9 rows in set (0.00 sec)
Select columns, if the output is needed to minimize, add keyword DISTINCT
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
9 rows in set (0.00 sec)
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Benny |
| Diane |
+--------+
4 rows in set (0.00 sec)
Combine rows and columns
mysql> SELECT name, species,birth FROM pet
-> WHERE species ='dog' OR species='cat';
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+
5 rows in set (0.00 sec)
Sorting Rows
ORDER commend (ASCENDING)
mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name | birth |
+----------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Puffball | 1990-03-30 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
+----------+------------+
9 rows in set (0.00 sec)
ORDER commend (DESCENDING)
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name | birth |
+----------+------------+
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Puffball | 1990-03-30 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+----------+------------+
9 rows in set (0.00 sec)
ORDER commend (sort on multiple columns)
mysql> SELECT name, species, birth FROM pet ORDER BY species,birth DESC;
+----------+---------+------------+
| name | species | birth |
+----------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistler | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puffball | hamster | 1990-03-30 |
| Slim | snake | 1996-04-29 |
+----------+---------+------------+
9 rows in set (0.00 sec)
DATA calculations
TIMESTAMPDIFF() only calculation, database will not be changed.
mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2021-05-27 | 28 |
| Claws | 1994-03-17 | 2021-05-27 | 27 |
| Buffy | 1989-05-13 | 2021-05-27 | 32 |
| Fang | 1990-08-27 | 2021-05-27 | 30 |
| Bowser | 1989-08-31 | 2021-05-27 | 31 |
| Chirpy | 1998-09-11 | 2021-05-27 | 22 |
| Whistler | 1997-12-09 | 2021-05-27 | 23 |
| Slim | 1996-04-29 | 2021-05-27 | 25 |
| Puffball | 1990-03-30 | 2021-05-27 | 31 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
mysql> DESCRIBE PET;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
TIMESTAMPDIFF() + ORDER BY
mysql> SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Bowser | 1989-08-31 | 2021-05-27 | 31 |
| Buffy | 1989-05-13 | 2021-05-27 | 32 |
| Chirpy | 1998-09-11 | 2021-05-27 | 22 |
| Claws | 1994-03-17 | 2021-05-27 | 27 |
| Fang | 1990-08-27 | 2021-05-27 | 30 |
| Fluffy | 1993-02-04 | 2021-05-27 | 28 |
| Puffball | 1990-03-30 | 2021-05-27 | 31 |
| Slim | 1996-04-29 | 2021-05-27 | 25 |
| Whistler | 1997-12-09 | 2021-05-27 | 23 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
Check the death value is NULL
NULL is a special value that can’t be compared using the usual comparison operators.
mysql> SELECT name, birth, death,TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+
1 row in set (0.00 sec)
Extracting parts of dates, such as YEAR(),MONTH() and DAYOFMONTH()
mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name | birth | MONTH(birth) |
+----------+------------+--------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1990-03-30 | 3 |
+----------+------------+--------------+
9 rows in set (0.00 sec)
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 8;
+--------+------------+
| name | birth |
+--------+------------+
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
+--------+------------+
2 rows in set (0.00 sec)
Counting Rows
Counting rows with condition
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 2 |
| Gwen | 3 |
| Benny | 2 |
| Diane | 2 |
+--------+----------+
4 rows in set (0.00 sec)
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| f | 4 |
| m | 4 |
| NULL | 1 |
+------+----------+
3 rows in set (0.00 sec)
Counting rows with combine condition
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species,sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| bird | f | 1 |
| bird | NULL | 1 |
| snake | m | 1 |
| hamster | f | 1 |
+---------+------+----------+
8 rows in set (0.00 sec)
mysql> SELECT species, sex, COUNT(*) FROM pet WHERE species ='dog' OR species = 'cat' GROUP BY species,sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
4 rows in set (0.00 sec)
mysql> SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species,sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| bird | f | 1 |
| snake | m | 1 |
| hamster | f | 1 |
+---------+------+----------+
7 rows in set (0.00 sec)
类似资料：