MySQL 2 —— 数据库的创建和数据检索

这部分内容包括：

• Create a database
• Create a table
• Load data into the table
• Retrieve data from the table in various ways
• Use multiple tales

2.1 创建和选择数据库

创建 menagerie 数据库并选中：

SHOW DATABASES;
# 创建数据库
CREATE DATABASE menagerie;
# 选择数据库
USE menagerie
# 删除数据库
DROP menagerie

---

2.2 创建表

在创建数据库前可以查看所有 TABLE：

SHOW TABLES;

数据的类型和大小范围可以参考 click me

如果要创建一个宠物信息表，需要包含：name, owner, species, sex, birth, AND death:

CREATE TABLE pet(
    -> name VARCHAR(20),
    -> owner VARCHAR(20),
    -> species VARCHAR(20),
    -> sex VARCHAR(1),
    -> birth DATE,
    -> death DATE
    -> );

使用 DESCRIBE 查看 pet 的数据存储格式（列的类型和名称）:

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.021 sec)

---

2.3 将数据导入表中

可以在 http://downloads.sql.com/docs/menagerie-db.tar.gz 下载一个 tar 压缩包，里面有 pet.txt 文件，其中的一行内容如下：

Fluffy	Harold	cat	f	1993-02-04	\N

使用 LOAD 命令加载数据：

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

使用 INSERT 插入一条记录：

INSERT INTO pet
->VALUES ('tom', 'Lucy', 'hamster', 'f', '2021-03-2', NULL);

---

2.4 检索信息

语法：

SELECT what_to_SELECT
FROM which_TABLE
WHERE conditions_to_satisfy;

2.4.1 选择所有数据

SELECT * FROM pet;

+----------+--------+---------+------+------------+------------+
| name     | owner  | 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 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| tom      | yin    | hamster | f    | 2021-03-09 | NULL       |
+----------+--------+---------+------+------------+------------+

如果需要更新数据，如更新表中 tom 的年龄：

update pet set birth = '2021-03-09' WHERE name='tom';

2.4.2 选择特殊行

选择 tom 着一条记录：

SELECT * FROM pet WHERE name = 'tom';

选择出生时间晚于 “1998-01-01” 的动物：

SELECT * FROM pet WHERE birth > '1998-01-01';

选择雄性的狗：

使用 AND、OR 和 NOT 进行逻辑运算

SELECT * FROM pet WHERE species = 'dog' AND sex = 'm';
SELECT * FROM pet WHERE (species = 'dog' AND sex = 'm');

2.4.3 选择特殊的列

* 代表选择所有列，可以指定需要的列：

SELECT name, species FROM pet;

可以用 DISTINCT 过滤重复的元素，如筛选有宠物的人：

SELECT distinct owner FROM pet;

查看猫和狗的名字和出生日期：

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     | 1979-08-31 |
+--------+---------+------------+

2.4.4 行排序

将结果按照升序 / 降序输出：

# 先按名字排序，再按年龄排序，默认降序
SELECT name, species, birth FROM pet
ORDER BY species, birth;

# 使用 desc 升序
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 |
| Buffy    | dog     | 1989-05-13 |
| Bowser   | dog     | 1979-08-31 |
| tom      | hamster | 2021-03-09 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

2.4.5 日期计算

sql 提供了获取时间的函数：

1. YEAR() 函数获取年份
2. MONTH() 函数获取月份
3. RIGHT(val, n) 获取 val 的后 n 个字符：

SELECT name, birth, death, '2021-03-09',
(year('2021-03-09')-year(birth)) - ('03-09'>right(birth, 5)) AS age
FROM pet WHERE death IS null ORDER BY age;

筛选 5 月出生的宠物：

SELECT * FROM pet WHERE month(birth) = 5;

2.4.6 NULL

使用 > < = != 操作 NULL 无意义！

可以用 IS NULL 或 IS NOT NULL 来操作 NULL

SELECT 1 IS NULL, 1 IS NOT NULL;

+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

2.4.7 模式匹配

MySQL 提供标准的 SQL 模式匹配：

1. _ 匹配任何单个字符
2. % 匹配任意数目字符

如查找所有以 “b” 开头的名字的数据，使用关键字 LIKE：

SELECT * FROM pet WHERE name LIKE 'b%';

查找所有长度为 5 的名字：

SELECT * FROM pet WHERE name LIKE '_____';

+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

正则表达式

1. 通过 REGEXP 操作符
2. 通过 REGEXP_LIKE()函数

# 选择名字以 b 打头的记录
SELECT * FROM pet WHERE name REGEXP '^b';

# 选择 name 长为 5 的记录
SELECT * FROM pet WHERE name REGEXP '^[a-zA-Z]{5}$';

2.4.8 计数统计

查询所有动物总数：

SELECT COUNT(*) FROM pet;

使用 GROUP BY 对记录分组计数：

如：查看每个用户拥有的宠物数目

SELECT owner, COUNT(*) FROM pet GROUP BY owner ORDER BY COUNT(*);

+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Diane  |        1 |
| yin    |        1 |
| Benny  |        2 |
| Harold |        2 |
| Gwen   |        3 |
+--------+----------+

查看每种宠物的数目：

SELECT species, COUNT(*) FROM pet GROUP BY species;

查看每个物种和每种性别组合的动物数量（性别非 NULL）：

SELECT species, sex, COUNT(*) FROM pet
WHERE sex IS NOT NULL
GROUP BY species, sex;

2.4.9 多个表

创建 event table：

CREATE TABLE event (
    -> name VARCHAR(20),
    -> date DATE,
    -> type VARHCAR(15),
    -> remark VARCHAR(255));

导入数据：

LOAD DATA LOCAL INFILE '/home/yin/Downloads/menagerie-db/event.txt' INTO TABLE event;

合并两个表，查询内容：

SELECT pet.name,
remark
FROM pet INNER JOIN event ON pet.name = event.name
WHERE type = 'litter';

第三行通过 ON 来比较 name，将 pet 和 event 合并，由于 name 是两个 table 都有的，因此使用时需要指定 table！

---

MySQL 8.0 Reference Manual