Datawhale - MySQL - 任务二 - MySQL 基础 （一）- 查询语句

Datawhale-MySQL-任务2-MySQL 基础 （一）- 查询语句

1、导入示例数据库

导入数据库，采用了易百的教程：MySQL导入示例数据库。
在这里我使用Navicat中导入。打开虚拟机系统，用Navicat连接虚拟机上的数据库，然后按F6打开命令行界面，输入

CREATE DATABASE IF NOT EXISTS yiibaidb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

然后下载易百的示例文件，解压后文件后缀名是sql。
在Navicat中导入该文件：

在yiidbaidb数据库上右击，在弹出的菜单中选择“运行SQL文件”，然后选中该文件后执行。

2、SQL是什么，MySQL是什么？

根据百度百科，SQL全称是结构化查询语言(Structured Query Language)，简称SQL(发音：/ˈes kjuː ˈel/ “S-Q-L”)，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。
MySQL则是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

3、 查询语句 SELECT FROM

语句解释
去重语句
前N个语句
CASE…END判断语句

语句解释
SELECT 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。
语法如下，其中多个列要用逗号,隔开。

SELECT column_name,column_name
FROM table_name;

或者查询表中所列

SELECT * FROM table_name;

去重语句
如果不想每个列中可能重复的值多次出现，可以使用关键字DISTINCT，用法如下：

SELECT DISTINCT column_name
FROM table_name;

前N个语句
如果想限制查询返回的数量，如只返回前N个值，在不同的数据库系统中实现方式可能不同，在MySQL、MariaDB、PostgreSQL或者SQLite，使用关键字LIMIT，用法如下：

SELECT column_name
FROM table_name
LIMIT n;

其中n为需要返回的数量，即前N个。

CASE…END判断语句
SQL中CASE的作用： 用于计算条件列表的表达式，并返回可能的结果之一。SQL的case 类似于编程语言里的 if-esle或者 switch，但它不用于控制sql程序的执行流程，而是作为列的逻辑使用。CASE具有两种格式，简单CASE函数和CASE搜索函数。

简单CASE函数

CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' 
END

CASE搜索函数

CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' 
END

这两种方式，可以实现相同的功能。简单CASE函数的写法相对比较简洁，但是和CASE搜索函数相比，功能方面会有些限制，比如写判断式。 还有一个需要注意的问题，CASE函数只返回第一个符合条件的值，剩下的CASE部分将会被自动忽略。比如说，下面这段SQL，你永远无法得到“第二类”这个结果：

CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
WHEN col_1 IN ('a')       THEN '第二类'
ELSE'其他' 
END

4、筛选语句 WHERE

语句解释
运算符/通配符/操作符

语句解释
数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。在SELECT语句中，数据根据筛选语句WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出，如下所示：

SELECT column_name
FROM table_name
WHERE column_name > 1;

以上语句从table_name表中检索column_name这个列，但不返回所有行，只返回column_name的值大于1的行。

运算符/通配符/操作符
运算符

• 比较运算符 - 学习如何使用比较运算符，包括大于，大于或等于，小于，小于或等于，+等于，不等于形成WHERE子句中的条件。
• 逻辑运算符 - 介绍逻辑运算符以及如何使用它们来测试条件的真实性。
• AND运算符 - 使用AND逻辑运算符组合多个布尔表达式。
• OR运算符 - 演示如何使用另一个逻辑运算符OR来组合多个布尔表达式。
• BETWEEN运算符 - 指导您使用BETWEEN运算符选择值范围内的数据。
• IN运算符 - 演示如何使用IN运算符检查值是否在值列表中。
• LIKE运算符 - 基于指定模式查询数据。
• IS NULL运算符 - 介绍NULL概念并演示如何检查表达式是否为NULL。
• NOT运算符 - 演示如何使用NOT运算符否定布尔表达式。

通配符
在搜索数据库中的数据时，SQL 通配符可以替代一个或多个字符。SQL 通配符必须与 LIKE 运算符一起使用。在 SQL 中，可使用以下通配符：
% 替代一个或多个字符
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]或者[!charlist] 不在字符列中的任何单一字符
通配符示例

操作符
操作符是一个保留字或字符，主要用于SQL语句的 WHERE子句来执行操作，比如比较和算术运算。操作符用于在SQL语句里指定条件，还可以联接一个语句里的多个条件。主要有：

• 比较操作符
• 逻辑操作符
• 求反操作符
• 算术操作符

比较操作符列表

操作符	描述	例子
=	检查如果两个操作数的值是相等或不相等，如果是的话，条件为真。	(a = b) is not true.
!=	检查如果两个操作数的值是否相等，如果值不相等，则条件为真。	(a != b) is true.
<>	检查如果两个操作数的值是否相等，如果值不相等，则条件为真。	(a <> b) is true.
>	检查值，如果左操作数大于右操作数的值，如果是的话，条件为真。	(a > b) is not true.
<	检查如果左操作数的值小于右操作数的值，如果是的话，条件为真。	(a < b) is true.
>=	检查如果左操作数的值大于或等于右操作数的值，如果是的话，条件为真。	(a >= b) is not true.
<=	检查如果左操作数的值小于或等于右操作数的值，如果是的话，条件为真。	(a <= b) is true.
!<	检查如果左操作数的值不小于右操作数的值，如果是的话，条件为真。	(a !< b) is false.
!>	检查如果左操作数的值是不大于右操作数的值，如果是的话，条件为真。	(a !> b) is true.

5、分组语句 GROUP BY

聚集函数
语句解释
HAVING子句

聚集函数（AGGREGATE FUNCTION，又称聚合函数）
SQL中提供的聚集函数可以用来统计、求和、求最值等等。SQL聚集函数包括：AVG()，COUNT()，MIN()，MAX()和SUM()。SQL聚合函数计算一组值并返回单个值。GROUP BY 语句用于结合聚集函数，根据一个或多个列对结果集进行分组。

聚集函数分类：
–COUNT：统计行数量
–SUM：获取单个列的合计值
–AVG：计算某个列的平均值
–MAX：计算列的最大值
–MIN：计算列的最小值

语句解释
GROUP BY子句是SELECT语句的可选子句，它根据指定列中的匹配值将行组合成组，每组返回一行。经常将GROUP BY与MIN，MAX，AVG，SUM或COUNT等聚集函数结合使用，以计算为每个分组提供信息的度量。
示例：

SELECT
    column1,
    column2,
    AGGREGATE_FUNCTION (column3)
FROM
    table1
GROUP BY
    column1,
    column2;

HAVING子句
要指定分组的条件，请使用HAVING子句。HAVING子句通常与SELECT语句中的GROUP BY子句一起使用。 如果使用带GROUP BY子句的HAVING子句，HAVING子句的行为类似于WHERE子句。以下是HAVING子句的语法：

SELECT
    column1,
    column2,
    AGGREGATE_FUNCTION (column3)
FROM
    table1
GROUP BY
    column1,
    column2
HAVING
    group_condition;

请注意，HAVING子句紧跟在GROUP BY子句之后出现。

6、排序语句 ORDER BY

语句解释
正序、逆序

语句解释
ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

正序、逆序
ORDER BY 关键字默认按照升序对记录进行排序。如果需要明确是正序还是降序排列，则按照正序对记录进行排序，可以使用ASC关键字，而按照降序对记录进行排序，可以使用 DESC 关键字。

7、函数

时间函数
数值函数
字符串函数

时间函数
时间函数用于调整日期和时间数据的外观，以适当的方式显示日期和时间数据、进行比较、计算日期之间的间隔等。

数值函数
数值函数可以对数据库里的值根据算术规则进行运算。

字符串函数
字符串函数用于在SQL里以不同于存储方式的格式来表示字符串。主要有串接、字串和TRANSLATE操作。

8、SQL注释

SQL语句中的行内注释使用--符号，示例如下：

SELECT column_name  -- 这是一条行内注释
FROM table_name;

多行注释需要成对使用/*和*/符号，示例如下：

SELECT column_name
/* 注释第一行
   注释第二行 */
FROM table_name;

注释里面的内容不会被执行，当SQL语句比较长或者难以理解的时候可以使用注释来标注。

9、SQL代码规范

[SQL编程格式的优化建议] SQL编程格式的优化建议 - 知乎
[SQL Style Guide] SQL style guide by Simon Holywell

作业

项目一

项目一：查找重复的电子邮箱（难度：简单）
创建 email表，并插入如下三行数据
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+

编写一个 SQL 查询，查找 email 表中所有重复的电子邮箱。
根据以上输入，你的查询应返回以下结果：
±--------+
| Email |
±--------+
| a@b.com |
±--------+
说明：所有电子邮箱都是小写字母。

首先创建表

然后插入数据

接着解题
代码如下：

SELECT Email From email
GROUP BY Email HAVING count(Email) > 1;

项目二

项目二：查找大国（难度：简单）
创建如下 World 表
±----------------±-----------±-----------±-------------±--------------+
| name | continent | area | population | gdp |
±----------------±-----------±-----------±-------------±--------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
±----------------±-----------±-----------±-------------±--------------+
如果一个国家的面积超过300万平方公里，或者(人口超过2500万并且gdp超过2000万)，那么这个国家就是大国家。
编写一个SQL查询，输出表中所有大国家的名称、人口和面积。
例如，根据上表，我们应该输出:
±-------------±------------±-------------+
| name | population | area |
±-------------±------------±-------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
±-------------±------------±-------------+

第一步，创建World表

第二步，插入数据

最后解题
代码如下：

SELECT name, population, area FROM World
WHERE (area > 3000000 OR (population > 25000000 AND gdp > 20000000));