创建和选择数据库
create database test;
use test;
数据库只需创建一次,但是每次开始mysql 会话时,都必须选择该数据库以供使用。也可以通过发出USE示例中所示的语句来完成此操作 。
可以在调用mysql时在命令行上选择数据库。只需在您可能需要提供的任何连接参数之后指定其名称即可。
mysql> mysql -h host -u user -p menagerie
Enter password: ********
创建表
创建数据库是比较容易的,但是这个时候我们创建的是空的,我们尝试使用 show tables 来查看内容
mysql> SHOW TABLES;
Empty set (0.00 sec)
所以我们尝试创建一个表。
举例:一个宠物信息 pet 的表,记录每个宠物的姓名,所有者,物种,性别,出生和死亡。
使用 create table 表名();
mysql> create table pet (
-> name varchar(20),
-> owner varchar(20),
-> species varchar(20),
-> sex char(1),
-> birth date,
-> death date
-> );
Query OK, 0 rows affected (0.352 sec)
我们可以尝试打印一下:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
1 row in set (0.000 sec)
如果要验证我们的表是按照预期方式创建,需要使用以下 describe 语句:
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.001 sec)
将数据加载到表中
在创建表之后,我们需要填充表,可以尝试 load data 和 insert 语句。
(但是注意一下 mySQL 需要使用格式的日期 ‘YYYY-MM_DD’)
我们可以先为每个动物创建一个包含一行的文本文件,然后使用单个语句将文件的内容加载到表中。
Fluffy Harold cat f 1993-02-04 \N
Claws Gwen cat m 1994-03-17 \N
Buffy Harold dog f 1989-05-13 \N
Fang Benny dog m 1990-08-27 \N
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11 \N
Whistler Gwen bird \N 1997-12-09 \N
Slim Benny snake m 1996-04-29 \N
而加载的语句可以这样写(找到自己的 pet.txt 文件)
mysql> load data local infile '/home/gao/Desktop/menagerie-db/pet.txt' into table pet;
当然也可以使用 insert 语句,添加一条新的记录
mysql> insert into pet values('Puff','Diane','hamster','f','1999-06-12',NULL);
从表中检索信息
选择所有数据
最简单的方式就是 select 从表中检索所有的内容
mysql> 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 |
| Puff | Diane | hamster | f | 1999-06-12 | NULL |
+----------+--------+---------+------+------------+------------+
注意:这里的 select * 就是选择所有列的简写。
如果我们的信息有错误,那我们有两种方法可以解决:
法一:编辑 pet.txt 来更正错误,然后清空表并使用 delete 和重新加载 load data
delete from pet;
load data local infile 'pet.txt' into table pet;
但是,如果执行此操作,则还必须重新输入 Puff 的记录。
所以我们更趋向于下面这种方法:
法二:使用 undate 语句仅修复错误的记录
update pet set birth='1989-08-31' where name='Bowser';
选择特定行
如果只是对表格中特定的问题感兴趣,我们可以选定任意行
mysql> select * from pet where name='Bowser';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
字符串比较通常不区分大小写,因此您可以将名称指定为’bowser’, 'BOWSER’等等。查询结果相同。
当然也可以查找特定的 birth
mysql> select * from pet where birth>='1998-1-1';
+--------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puff | Diane | hamster | f | 1999-06-12 | NULL |
也可以使用逻辑运算符, AND 和 OR
// 使用 AND 运算符
mysql> select * from pet where species='dog' and sex='f';
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
// 使用 OR 运算符
mysql> select * from pet where species='snake' or species='bird';
+----------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+----------+-------+---------+------+------------+-------+
当然 AND 和 OR 也可以混用, AND 的优先级高于 OR,一起用时最好使用括号。
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
选择特殊列
如果您不想看到表中的整个行,只需用逗号分隔您感兴趣的列的名称即可。
select name,birth from pet;
+----------+------------+
| name | birth |
+----------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1979-08-31 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Puff | 1999-06-12 |
+----------+------------+
如果要查询谁拥有宠物
select owner from pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
我们可以看到很多名字不止出现一次,为了最大程度减少输出,我们可以通过添加关键字 一次检索 每个唯一的输出记录 distinct
select distinct owner from pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Benny |
| Diane |
+--------+
也可以将 行选择 和 列选择 结合起来。
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 |
排序行
以某种有意义的方式对行进行排序时,通常更容易检查查询输出。要对结果进行排序,可以使用ORDER BY子句。
SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name | birth |
+----------+------------+
| Bowser | 1979-08-31 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puff | 1999-06-12 |
+----------+------------+
默认的排序顺序是升序,最小值在前。
如果要以相反的顺序(降序)排序,将DESC关键字添加到要排序的列的名称上:
SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name | birth |
+----------+------------+
| Puff | 1999-06-12 |
| Chirpy | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Buffy | 1989-05-13 |
| Bowser | 1979-08-31 |
+----------+------------+
当然也可以对多个列进行排序,也可以按照不同方向对不同的列进行排序。
举例:
要按升序对动物类型进行排序,然后按降序对动物类型内的出生日期进行排序。
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 |
| Puff | hamster | 1999-06-12 |
| Slim | snake | 1996-04-29 |
日期计算
要确定宠物几岁的话,可以使用 TIMESTAMPDIFF() ,参数是要表示结果的单位,以及两个日期之间的差值。
一个 别名(age)是用来制造最终输出列标签更有意义。
SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2021-03-09 | 28 |
| Claws | 1994-03-17 | 2021-03-09 | 26 |
| Buffy | 1989-05-13 | 2021-03-09 | 31 |
| Fang | 1990-08-27 | 2021-03-09 | 30 |
| Bowser | 1979-08-31 | 2021-03-09 | 41 |
| Chirpy | 1998-09-11 | 2021-03-09 | 22 |
| Whistler | 1997-12-09 | 2021-03-09 | 23 |
| Slim | 1996-04-29 | 2021-03-09 | 24 |
| Puff | 1999-06-12 | 2021-03-09 | 21 |
+----------+------------+------------+------+
也可以添加一个 ORDER BY 子句来按名称排序完成:
SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Bowser | 1979-08-31 | 2021-03-09 | 41 |
| Buffy | 1989-05-13 | 2021-03-09 | 31 |
| Chirpy | 1998-09-11 | 2021-03-09 | 22 |
| Claws | 1994-03-17 | 2021-03-09 | 26 |
| Fang | 1990-08-27 | 2021-03-09 | 30 |
| Fluffy | 1993-02-04 | 2021-03-09 | 28 |
| Puff | 1999-06-12 | 2021-03-09 | 21 |
| Slim | 1996-04-29 | 2021-03-09 | 24 |
| Whistler | 1997-12-09 | 2021-03-09 | 23 |
+----------+------------+------------+------+
要使用age而不是 来对输出进行排序name,只需使用其他ORDER BY子句即可:
SELECT name,birth,CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Puff | 1999-06-12 | 2021-03-09 | 21 |
| Chirpy | 1998-09-11 | 2021-03-09 | 22 |
| Whistler | 1997-12-09 | 2021-03-09 | 23 |
| Slim | 1996-04-29 | 2021-03-09 | 24 |
| Claws | 1994-03-17 | 2021-03-09 | 26 |
| Fluffy | 1993-02-04 | 2021-03-09 | 28 |
| Fang | 1990-08-27 | 2021-03-09 | 30 |
| Buffy | 1989-05-13 | 2021-03-09 | 31 |
| Bowser | 1979-08-31 | 2021-03-09 | 41 |
+----------+------------+------------+------+
对于那些已经死亡的动物的年龄,我们可以通过检查 death 值来确定。
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 | 1979-08-31 | 1995-07-29 | 15 |
+--------+------------+------------+------+
假如想知道动物生日的月份,该怎么做呢。
这里提供集中提取日期部分的方法。
YEAR() MONTH() DAYOFMONTH()
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 | 1979-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puff | 1999-06-12 | 6 |
+----------+------------+--------------+
比如查找月份为5的动物。
SELECT name,birth FROM pet WHERE MONTH(birth)=5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
用来查找当前月份的下一月份,可以这样写(编写的时候目前是3月份),所以结果显示4月份。
SELECT name,birth FROM pet WHERE MONTH(birth)=MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
+------+------------+
| name | birth |
+------+------------+
| Slim | 1996-04-29 |
+------+------------+
使用 NULL 值
NULL 表示 缺少的未知值,并与其他值的处理方式不同。
SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
注意不能使用算术比较操作符,比如 = ,<, <>
SELECT 1=NULL, 1 <> NULL, 1<NULL, 1>NULL;
+--------+-----------+--------+--------+
| 1=NULL | 1 <> NULL | 1<NULL | 1>NULL |
+--------+-----------+--------+--------+
| NULL | NULL | NULL | NULL |
+--------+-----------+--------+--------+
在MySQL中,0或NULL 表示false,其他表示true。布尔运算的默认真值是1。
使用时的一个常见错误NULL是假定无法在定义为的列中插入零或空字符串NOT NULL,但事实并非如此。这些实际上是值,而 NULL意味着“没有值。”
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 |
+-----------+---------------+------------+----------------+
模式匹配
MySQL提供标准的SQL模式匹配以及基于扩展的正则表达式的模式匹配形式,该正则表达式类似于vi,grep和 sed之类的Unix实用程序使用的扩展正则表达式 。
使用 LIKE 或 NOT LIKE 比较运算符。
比如:
查找 b 开头的名称。
SELECT * FROM pet WHERE name LIKE 'b%';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
查找 fy 结尾的名称:
SELECT * FROM pet WHERE name LIKE '%fy';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
查找包含 w 的名称
SELECT * FROM pet WHERE name LIKE '%w%';
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |
+----------+-------+---------+------+------------+------------+
查找正好包含五个字符的名称,请使用_模式字符的五个实例
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 |
+-------+--------+---------+------+------------+-------+
MySQL提供的另一种模式匹配使用扩展的正则表达式。在测试此类型的模式是否匹配时,请使用 REGEXP_LIKE()函数(或 REGEXP或 RLIKE 运算符,它们是的同义词 REGEXP_LIKE())。
以下列表描述了扩展正则表达式的一些特征:
-
. 匹配任何单个字符。
-
字符类[…]与括号内的任何字符匹配。例如, [abc]匹配a, b或c。要命名字符范围,请使用破折号。[a-z] 匹配任何字母,而[0-9] 匹配任何数字。
-
*
匹配零个或多个在其之前的事物的实例。例如,x* 匹配任意数量的x字符, [0-9]*匹配任意数量的数字,以及.*匹配任意数量的任何东西。 -
如果正则表达式模式匹配成功,则该模式匹配成功。(这与LIKE模式匹配不同,后者仅在模式匹配整个值时才成功。)
-
锚定的图案,使得它必须使用匹配的值的开头或结尾正在测试中,^在开始时或$在图案的端部。
SELECT * FROM pet WHERE name REGEXP '^b';
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
计数行
使用 COUNT 计算行数。
举例:
查找动物数量。
SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
如果 COUNT 要查找每个所有者拥有多少宠物,可以这样。
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
当然也可以查找每个物种的动物数量。
SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+
或者每个物种和性别组合的动物数量:
SELECT species, sex, COUNT(*) FROM pet GROUP BY species;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 2 |
| cat | f | 2 |
| dog | f | 3 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
又比如只对狗和猫执行查询,不对整个表查询。
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 |
+---------+------+----------+
如果只想知道已知性别的动物,则按性别计算的动物数:
SELECT species,sex,COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
使用多个表
除了 pet 之外,我们还想要记录有关的另外一些信息,我们创建对应的事件,对应的宠物,发生的时间,描述事件字段,时间类型字段。
可以这样子
CREATE TABLE event(
-> name VARCHAR(20),
-> date DATE,
-> type VARCHAR(15),
-> remark VARCHAR(255)
-> );
这里有一段 event.txt 文本,我们可以把对应的事件加载进去。
Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male
Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male
Buffy 1994-06-19 litter 3 puppies, 3 female
Chirpy 1999-03-21 vet needed beak straightened
Slim 1997-08-03 vet broken rib
Bowser 1991-10-12 kennel
Fang 1991-10-12 kennel
Fang 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First birthday
加载记录:
LOAD DATA LOCAL INFILE '/home/gao/Desktop/menagerie-db/event.txt' INTO TABLE event;
假设您想找出每个宠物产仔的年龄。我们之前已经看到了如何从两个日期计算年龄。母亲的产仔日期在 event表格中,但是要计算该日期的年龄,您需要她的出生日期,该日期存储在 pet表格中。这意味着查询需要两个表:
SELECT pet.name,
-> TIMESTAMPDIFF(YEAR,birth,date) AS age,
-> remark
-> FROM pet INNER JOIN event
-> ON pet.name = event.name
-> WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+--------+------+-----------------------------+
例如,要在您的宠物中查找繁殖对,您可以将pet与自身配对,以产生相似物种的活体雄性和雌性的候选对:
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species
AND p1.sex = 'f' AND p1.death IS NULL
AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name | sex | name | sex | species |
+--------+------+-------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
+--------+------+-------+------+---------+