一周MySQL集训day2：MySQL 基础 （一）- 查询语句
1 任务
任务二
MySQL 基础 （一）- 查询语句
任务时间
请于2月28日22:00前完成，在本文章评论打卡。逾期尚未打卡的会被清退。
学习内容
1. 导入示例数据库，教程 MySQL导入示例数据库 - MySQL教程™
2. SQL是什么？MySQL是什么？
3. 查询语句 SELECT FROM
语句解释
去重语句
前N个语句
CASE...END判断语句
4. 筛选语句 WHERE
语句解释
运算符/通配符/操作符
5. 分组语句 GROUP BY
聚集函数
语句解释
HAVING子句
6. 排序语句 ORDER BY
语句解释
正序、逆序
7. 函数
时间函数
数值函数
字符串函数
8. 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 |
+---------+
说明：所有电子邮箱都是小写字母。
项目二：查找大国（难度：简单）
创建如下 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 |
+--------------+-------------+--------------+
2 导入示例数据库
2.1 命令行导入数据库文件
示例数据库已存百度网盘：yiibaidb
在MySQL中通过命令行导入示例数据库的详细过程可见：参考1
这里有一点需要注意的是：在cmd中用命令行启动MySQL时，应先初始化：mysqld --initialize-insecure --user=mysql，然后net start mysql 启动MySQL，接着是mysql -u root -p 登录。但在装了图形界面软件 Navicat for SQL，成功连接MySQL之后，就可以不用命令行打开MySQL也能直接用了！！！
接下来讲下如何在Navicat中直接导入.sql数据库文件
2.2 Navicat直接导入数据库文件
-
首先是成功安装图形界面软件 Navicat for SQL并与MySQL成功连接，并下载示例数据库：
yiibaidb。
-
在本地数据库中我们新建一个数据库用于存放导入的sql文件
-
设置编码，排序规则
-
打开新建好的数据库
-
右键选中“运行SQL文件”如图所示：
-
接着选择好数据文件，点击“开始”软件就开始导入了。
-
等待进度条完成了，并且出现如图中代码说明没有出现问题，则成功导入，点击关闭即可。
-
刷新右侧的显示数据库表的界面即可看到导入好的数据库表
注意： 这种方法同样的可以用来导出成.sql数据库文件
3 SQL是什么？MySQL是什么？
3.1 SQL是什么？
SQL（发音为字母 S-Q-L或 sequel）是 Structured Query Language（结构化查询语言）的缩写。 SQL是一种专门用来与数据库沟通的语言。
与其他语言（如英语或 Java、 C、 PHP这样的编程语言）不一样， SQL中只有很少的词， 这是有意而为的。 设计 SQL的目的是很好地完成一项任务——提供一种从数据库中读写数据的简单有效的方法。
大多数DBMS(数据库管理系统)，都支持SQL语言。不同的DBMS，SQL语法规则留有不同。
3.2 MySQL是什么？
MySQL是一种DBMS。它是一种基于客户机-服务器的数据库软件。
4 查询语句 SELECT FROM
-
语句解释：select 要检索的列表（多个列用逗号隔开）from 表名 从表中检索一个或多个数据列
-
去重语句：使用关键字：distinct，顾名思义，它指示数据库只返回不同的值。
注意： 不能部分使用 DISTINCT。DISTINCT关键字作用于所有的列，不仅仅是跟在其后的那一列。 -
前N个语句：这里需要注意的是，各种数据库中的这一 SQL实现并不相同。若使用 MySQL、 MariaDB、 PostgreSQL或者 SQLite，需要使用LIMIT子句。
eg：SELECT prod_name FROM Products LIMIT 5; LIMIT5指示 MySQL等 DBMS返回不超过 5行的数据。
注意： 第 0 行。第一个被检索的行是第 0行，而不是第 1行。因此， LIMIT 1 OFFSET 1会检索第 2行，而不是第 1行。
提示： MySQL和MariaDB支持简化版的LIMIT4OFFSET3语句，即LIMIT3,4。
易混点总结：
- LIMIT N 就是前N行。
- LIMIT N OFFSET M， 是从M+1行开始取N行。OFFSET的检索从0开始，LIMIT 1 和 LIMIT 1 OFFET 0 等价。
- CASE…END判断语句：case具有两种格式。简单case函数和case搜索函数。这两种方式，可以实现相同的功能。简单case函数的写法相对比较简洁，但是和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
-- 比如说，下面这段sql，你永远无法得到“第二类”这个结果
case when col_1 in ('a','b') then '第一类'
when col_1 in ('a') then '第二类'
else '其他' end
5 筛选语句 WHERE
-
语句解释：在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。
-
操作符（operator）：用来联结或改变 WHERE子句中的子句的关键字，也称为逻辑操作符（logical operator）。
空值检查：确定值是否为NULL，不能简单地检查是否= NULL。 SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。 这个WHERE子句就是IS NULL子句。
eg：SELECT prod_name FROM Products WHERE prod_price IS NULL;
AND操作符：要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。
OR操作符：OR操作符与 AND操作符正好相反，它指示 DBMS检索匹配任一条件的行。
IN操作符：IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。
NOT操作符：WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。 -
通配符（wildcard）：用来匹配值的一部分的特殊字符。
LIKE操作符：为在搜索字句中使用通配符，必须使用LIKE操作符。LIKE指示 DBMS，后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。
百分号（%）通配符：在搜索串中， %表示任何字符出现任意次数。
注意：请注意 NULL。通配符%看起来像是可以匹配任何东西，但有个例外， 这就是 NULL 。
下划线（_）通配符：下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。
使用通配符的技巧：
- 不要过度使用通配符。如果其他操作符能达到相同的目的， 应该使用其他操作符。
- 在确实需要使用通配符时，也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
- 运算符：圆括号可用来区分优先顺序。
6 分组语句 GROUP BY
- 聚集函数（aggregate function）：对某些行运行的函数， 计算并返回一个值。进行汇总信息。
- 创建分组 GROUP BY：分组是使用SELECT语句的GROUPBY子句建立的。GROUP BY子句指示 DBMS分组数据，然后对每个组而不是整个结果集进行聚集。（GROUP BY子句必须出现在WHERE子句之后， ORDER BY子句之前）
- 过滤分组 HAVING：HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是， WHERE过滤行，而HAVING过滤分组。 HAVING 支持所有 WHERE 操作符。WHERE在数据分组前进行过滤， HAVING在数据分组后进行过滤。
注意：使用 HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。
7.排序语句 ORDER BY
- 语句解释：为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。
注意： ORDER BY 子句的位置。在指定一条 ORDER BY子句时， 应该保证它是 SELECT语句中最后一条子句。如果它不是最后的子句，将会出现错误消息。
要按多个列排序， 简单指定列名，列名之间用逗号分开即可（就像选择多个列时那样）。 - 正序、逆序：默认升序排序。为了进行降序排序，必须指定DESC关键字。（DESC关键字只直接应用在前面列名）
- 分组和排序：GROUP BY和ORDER BY经常完成相同的工作，但它们非常不同，理解这一点很重要。
表 10-1中列出的第一项差别极为重要。一般在使用 GROUP BY子句时， 应该也给出 ORDER BY子句。 这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。 - 下面回顾一下SELECT语句中子句的顺序。
8 函数
与几乎所有 DBMS都等同地支持 SQL语句（如 SELECT）不同，每一个DBMS都有特定的函数。
- 日期和时间处理函数：用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差， 检查日期有效性）的日期和时间函数。应用程序一般不使用日期和时间的存储格式，因此日期和时间函数总是用来读取、 统计和处理这些值。由于这个原因，日期和时间函数在 SQL中具有重要的作用。 遗憾的是，它们很不一致，可移植性最差。大多数 DBMS具有比较日期、 执行基于日期的运算、 选择日期格式等的函数。关于具体 DBMS支持的日期-时间处理函数， 请参阅相应的文档。
- 数值处理函数：用于在数值数据上进行算术操作（如返回绝对值， 进行代数运算）的数值函数。数值处理函数仅处理数值数据。 这些函数一般主要用于代数、三角或几何运算，因此不像字符串或日期时间处理函数使用那么频繁。在主要 DBMS的函数中，数值函数是最一致、最统一的函数。
- 文本处理函数：用于处理文本字符串（如删除或填充值， 转换值为大写或小写）的文本函数。
9 SQL注释
注释的作用：
- 随着 SQL语句变长，复杂性增加，添加一些描述性的注释，便于今后参考，或者供项目后续参与人员参考。
- 适用于 SQL文件开始处的内容，它可能包含程序员的联系方式、程序描述以及一些说明。
- 暂时停止要执行的 SQL代码。如果你碰到一个长 SQL语句，而只想测试它的一部分，那么应该注释掉一些代码，以便 DBMS将其视为注释而加以忽略。
MySQL注释的方法：
- 单行注释可以用"#"
select 1 as cname; #this is a comment
+-------+
| cname |
+-------+
| 1 |
+-------+
-- (#后面直接根的就是注释)
- 单行注释的第二种写法用 “-- " 注意这个风格下”–【空格】" 也就是说“–" 与注释之间是有空格的。
select 123; -- this is a comment
+-----+
| 123 |
+-----+
| 123 |
+-----+
-- 注意啊-- 后面是要有一个空格的
- 多行注释可以用/**/
mysql> select 123; /* this is a comment */
+-----+
| 123 |
+-----+
| 123 |
+-----+
row in set (0.00 sec)
mysql> select 1+ /* this is a
comment */ 1;
+-------+
| 1+ 1 |
+-------+
| 2 |
+-------+
row in set (0.00 sec)
10 SQL代码规范
- 命名的建议
-
使用统一的、描述性强的字段命名规则
-
保证字段名是独一无二且不是保留字的，不要使用连续的下划线，不用下划线结尾
-
最好以字母开头
id 标识符——user_id 用户标识 item_id 商品标识
dt 表示某个事件发生的时间——ord_dt 订单时间 exam_dt 考试时间
num 表示某事相关的数字——sale_num 销量
name 用字母表示名称——stu_name 学生名 等
- 格式建议
最好使用标准SQL函数而不是特定供应商Oracle、Mysql等的函数以提高可移植性
大小写的运用，系统关键字大写，字段表名小写
灵活使用空格和缩进来增强可读性——两大法宝空白隔道与垂直间距
（未改进代码）
select name,id,sex
from (select *
from school_score
where class_cd=110)
where sex = 'man'
and exam_dt = '2016-06-01';
（改进代码）
--空白隔道+垂直间距+大小写+缩进
SELECT name, id, sex
FROM (SELECT *
FROM school_score
WHERE class_cd = 110)
WHERE sex = 'man'
AND exam_dt = '2016-06-01';
- 利用空格保持关键字对齐
- 在等号前后（=）在逗号后（,）单引号前后（’）加上空格
- 子查询缩进并对齐
改进后的代码更加清晰有序~~
- 语法建议
- 尽量使用BETWEEN而不是多个AND
- 同样，使用 IN 而不是多个OR
- 利用CASE语句嵌套处理更复杂的逻辑结构
- 避免UNION语句与临时表
11 作业
-
项目一：查找重复的电子邮箱（难度：简单）
创建 email表，并插入如下三行数据
±—±--------+
| Id | Email |
±—±--------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
±—±--------+
编写一个 SQL 查询，查找 email 表中所有重复的电子邮箱。
根据以上输入，你的查询应返回以下结果：
±--------+
| Email |
±--------+
| a@b.com |
±--------+
说明：所有电子邮箱都是小写字母。解答截图：
- 先按要求创建 email表，并插入如下三行数据
- 然后查找 email 表中所有重复的电子邮箱。
- 先按要求创建 email表，并插入如下三行数据
-
查找大国（难度：简单）
创建如下 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 表
2.然后编写一个SQL查询，输出表中所有大国家的名称、人口和面积。
- 先按要求创建 World 表
451
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
