一周MySQL集训day2：MySQL 基础（一）- 查询语句-CSDN博客

本文链接：https://blog.csdn.net/qq_30006749/article/details/87970787

这篇博客介绍了MySQL的基础知识，包括SQL的概念、MySQL的定义，以及查询语句SELECT FROM、筛选语句WHERE、分组语句GROUP BY、排序语句ORDER BY的使用。此外，还讲解了如何在Navicat中导入数据库文件，以及SQL中的函数、注释和代码规范。

摘要由CSDN通过智能技术生成

一周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 |
±--------+
说明：所有电子邮箱都是小写字母。

解答截图：
1. 先按要求创建 email表，并插入如下三行数据
2. 然后查找 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 |
±-------------±------------±-------------+

解答截图：
1. 先按要求创建 World 表
  
  2.然后编写一个SQL查询，输出表中所有大国家的名称、人口和面积。