前言

在当今信息爆炸的时代，拥有信息检索的能力很重要。
作为一名软件工程师，遇到问题，你会怎么办？带着问题去搜索引擎寻找答案？亦或是去技术官网，技术社区去寻找？
根据个人经验，一般遇到问题，基本搜索引擎都可以解决（抱着：我遇到的问题肯定别人也遇到过的心态去解决）
实在解决不了的，逛逛技术官网、社区，一般也可以解决！
以上解决问题的方法屡试不爽！

从今天开始，进军MySQL官网，跟着官网学MySQL！

今天要学习的章节是：Creating and Using a Database （创建和使用数据库2）

Creating and Using a Database （创建和使用数据库）

Retrieving Information from a Table （从表中检索信息）

从表中检索信息

数据准备(放在桌面Desktop文件夹下，或者其他本地路径)

pet.txt

Fluffy	Harold	cat	f	1993-02-04
Claws	Gwen	cat	m	1994-03-17
Buffy	Harold	dog	f	1989-05-13
Fang	Benny	dog	m	1990-08-27
Bowser	Diane	dog	m	1979-08-31	1995-07-29
Chirpy	Gwen	bird	f	1998-09-11
Whistler	Gwen	bird		1997-12-09
Slim	Benny	snake	m	1996-04-29

pet1.csv

Fluffy,Harold,cat,f,1993-02-04,
Claws,Gwen,cat,m,1994-03-17,
Buffy,Harold,dog,f,1989-05-13,
Fang,Benny,dog,m,1990-08-27,
Bowser,Diane,dog,m,1979-08-31,1995-07-29
Chirpy,Gwen,bird,f,1998-09-11,
Whistler,Gwen,bird,,1997-12-09,
Slim,Benny,snake,m,1996-04-29,

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

Selecting All Data（选择所有数据）

官网截图
实操截图
sql脚本

SELECT * FROM pet;

DELETE FROM pet;
LOAD DATA LOCAL INFILE '/localPath/pet.txt' INTO TABLE pet;

UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';

Selecting Particular Rows（选择特定行）

官网截图
实操截图
sql脚本

 SELECT * FROM pet WHERE name = 'Bowser';
 
 SELECT * FROM pet WHERE birth >= '1998-1-1';
 
 SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
 
 SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
 
 SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
       OR (species = 'dog' AND sex = 'f');

Selecting Particular Columns（选择特定列）

官网截图
实操截图
sql脚本

SELECT name, birth FROM pet;

SELECT owner FROM pet;

SELECT DISTINCT owner FROM pet;

SELECT name, species, birth FROM pet
       WHERE species = 'dog' OR species = 'cat';

Sorting Rows（对行进行排序）

官网截图
实操截图
sql脚本

SELECT name, birth FROM pet ORDER BY birth;

SELECT name, birth FROM pet ORDER BY birth DESC;

SELECT name, species, birth FROM pet
       ORDER BY species, birth DESC;

Date Calculations（日期计算）

官网截图
实操截图
sql脚本

SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet;
 
SELECT name, birth, CURDATE(),
     TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
     FROM pet ORDER BY name;

SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM pet ORDER BY age;
       
SELECT name, birth, death,
       TIMESTAMPDIFF(YEAR,birth,death) AS age
       FROM pet WHERE death IS NOT NULL ORDER BY age;
       
SELECT name, birth, MONTH(birth) FROM pet;

SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

SELECT name, birth FROM pet
       WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
       
SELECT name, birth FROM pet
       WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
       
SELECT '2018-10-31' + INTERVAL 1 DAY;
SELECT '2018-10-32' + INTERVAL 1 DAY;
SHOW WARNINGS;

Working with NULL Values（使用NULL值）

官网截图
实操截图
sql脚本

SELECT 1 IS NULL, 1 IS NOT NULL;

SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;

SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

Pattern Matching（模式匹配）

官网截图
实操截图
sql脚本

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

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

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

SELECT * FROM pet WHERE name LIKE '_____';

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b');

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b' COLLATE utf8mb4_0900_as_cs);
SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY '^b');
SELECT * FROM pet WHERE REGEXP_LIKE(name, '^b', 'c');

SELECT * FROM pet WHERE REGEXP_LIKE(name, 'fy$');

SELECT * FROM pet WHERE REGEXP_LIKE(name, 'w');

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.....$');

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$');

Counting Rows（计算行数）

官网截图
实操截图
sql脚本

SELECT COUNT(*) FROM pet;

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

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

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

SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
 
SELECT species, sex, COUNT(*) FROM pet
       WHERE species = 'dog' OR species = 'cat'
       GROUP BY species, sex;
       
SELECT species, sex, COUNT(*) FROM pet
     WHERE sex IS NOT NULL
     GROUP BY species, sex;
     
SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT owner, COUNT(*) FROM pet;

SET sql_mode = '';
SELECT owner, COUNT(*) FROM pet;

Using More Than one Table（使用多个表）

官网截图
实操截图
sql脚本

CREATE TABLE event (name VARCHAR(20), date DATE,
       type VARCHAR(15), remark VARCHAR(255));
       
LOAD DATA LOCAL INFILE '/localPath/event.txt' INTO TABLE event;

SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';
       
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;

本章关键知识点总结

Selecting All Data（选择所有数据）

查询万能框架：SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;
查询所有列 select * from table_nme;
删除表中所有数据 delete from table_name;
加载本地文件到mysql表中：LOAD DATA LOCAL INFILE ‘/localPath/pet.txt’ INTO TABLE table_name
更新数据：update table_name set column_name = ‘newValue’ where column_name = ‘oldvValue’;

Selecting Particular Rows（选择特定行）

查询特定行（等于）：SELECT * FROM table_name WHERE column_name = ‘value’;
查询特定行（大于等于）：SELECT * FROM table_name WHERE column_name >= ‘value’;
查询特定行（多条件与）：SELECT * FROM table_name WHERE column_name1 = ‘value’ AND column_name2 = ‘value’;
查询特定行（多条件或）：SELECT * FROM table_name WHERE column_name1 = ‘value’ OR column_name2 = ‘value’;
查询特定行（多条件与/或）：SELECT * FROM table_name WHERE (column_name1 = ‘value1’ AND column_name2 = ‘value2’) OR (column_name1 = ‘value3’ AND column_name2 = ‘value4’);
逻辑运算符AND和 OR可以混合使用，但 AND优先级高于 OR。如果同时使用这两个运算符，最好使用括号明确指示应如何对条件进行分组：

Selecting Particular Columns（选择特定列）

查询特定列（多列）：SELECT column_name1, column_name2 FROM table_name;
查询特定列（一列）：SELECT column_name FROM table_name;
查询特定列（去重）：SELECT DISTINCT column_name FROM table_name;
查询特定列（多列，多条件）：SELECT column_name1, column_name2, column_name3 FROM table_name WHERE column_name1 = ‘value1’ OR column_name1 = ‘value2’;

Sorting Rows（对行进行排序）

order by默认是升序的asc（不显式指定就是升序），desc代表降序
查询多列根据单列排序：SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1;
查询多列根据单列降序排序：SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1 desc;
查询多列根据多列排序（且每列排序规则不同）：SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1, column_name2 desc;

Date Calculations（日期计算）

curdate()：返回当前日期
timestampdiff()：返回两个日期之间的差值
is null与is not null判断
month()：返回日期的月份
year()：返回日期的年份
dayofmonth()：返回日期的天数
date_add()：日期加法
interval：时间间隔
mod()：取模
日期直接加1天：select ‘2018-10-31’ + interval 1 day;

Working with NULL Values（使用NULL值）

null值的判断：a IS NULL, a IS NOT NULL;
null容易使代码抛出空指针异常

Pattern Matching（模式匹配）

以x开头：like ‘x%’
以xy结尾：like ‘%xy’
包含x的：like ‘%x%’
包含N个字符（N个就是N个_）：like ‘_____’
regexp_like()：正则表达式匹配
- 以x开头的：REGEXP_LIKE(name, ‘^x’);
- 匹配x开头的（区分大小写）：
  - REGEXP_LIKE(name, ‘^x’ COLLATE utf8mb4_0900_as_cs);
  - REGEXP_LIKE(name, BINARY ‘^x’);
  - REGEXP_LIKE(name, ‘^x’, ‘c’);
- 匹配以xy结尾的：REGEXP_LIKE(name, ‘xy$’);
- 包含x的：REGEXP_LIKE(name, ‘x’);
- 恰好包含N个字符的（N是几就几个.）：REGEXP_LIKE(name, ‘^…$’);
  - 高级写法：REGEXP_LIKE(name, ‘^.{5}$’);

Counting Rows（计算行数）

查询表所有行：count(*)
单列分组后查询所有行：select column_name,count(*) from table_name group by column_name;
多列分组查询行数：select column_name1, column_name2, count(*) from table_name group by column_name1, column_name2;
多列分组加过滤查询行数：select column_name1, column_name2, count(*) from table_name where column_name1 = ‘value1’ or column_name1 = ‘value2’ group by column_name1, column_name2;
sql_mode = ‘ONLY_FULL_GROUP_BY’：严格模式，group by后必须select的列都要在group by后面
sql_mode = ‘’：宽松模式，不一定得有group by

Using More Than one Table（使用多个表）

创建表：create table table_name(column_name1 type1, column_name2 type2);
加载数据到表：LOAD DATA LOCAL INFILE ‘/localPath/event.txt’ INTO TABLE event;
内连接：inner join
别名：as

本章遇到问题

1.mysql导入csv，结果null值变为了0000-00-00
查询sql_mode_mode:
select @@sql_mode;
select @@global.sql_mode;
select @@session.sql_mode;
mysql的0000-00-00是一个主题，属于无效值，和null不同
解决：set sql_mode=''即可(主要是把No_zero_date去掉)

2.mysql导入csv，只导入了第一行
LOAD DATA local INFILE ‘/Users/xxx/Desktop/pet.csv’ INTO TABLE pet
FIELDS TERMINATED BY ‘,’ #分隔符
LINES TERMINATED BY ‘\n’#换行
解决：加上ENCLOSED BY ‘"’ #结尾符
核心在于：ENCLOSED BY ‘"’ #结尾符，别的文档对这个字段的解释不是很明确，
看来以后所有命令，每一个参数都要搞清楚哦

3.SELECT * FROM pet WHERE REGEXP_LIKE(name, BINARY ‘^b’); 报错
ERROR 3995 (HY000): Character set ‘utf8mb4_0900_ai_ci’ cannot be used in conjunction with ‘binary’ in call to regexp_like.
解决：SELECT * FROM pet WHERE REGEXP_LIKE(cast(name as binary), BINARY ‘^b’)

参考手册

MySQL8.4参考手册