0401任务二MySQL基础（一）——查询语句

怼烎@ 2022-02-25 08:08 347阅读 0赞

### 任务二MySQL基础（一）——查询语句 ###

*  一、学习内容
 *  二、作业

# 一、学习内容 #

**1. 导入示例数据库**，教程 [https://www.yiibai.com/mysql/how-to-load-sample-database-into-mysql-database-server.html][https_www.yiibai.com_mysql_how-to-load-sample-database-into-mysql-database-server.html]

*  下载示例数据库yiibaidb.sql
 *  将下载的文件放入临时文件夹中
 *  连接到MySQL服务器并创建数据库  
    （1） `show databases` 显示数据库管理系统的数据库  
    （2）`mysql> CREATE DATABASE IF NOT EXISTS yiibaidb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;`  
    或者`create database yiibaidb`创建数据库  
    （3）`use yiibaidb;`选择数据  
    （4）`source D:/worksp/yiibaidb.sql;` 导入数据  
    （5）`drop database yiibaidb` 删除数据  
    （6）`show tables`显示当前数据库中的表  
    （7）`desc (表名字)`查看表结构  
    **2. SQL是什么？MySQL是什么？**  
    （1.1）SQL - 数据库的语言，SQL代表结构化查询语言(Structured Query Language)。SQL是用于访问数据库的标准化语言。  
    （1.2）SQL包含三个部分：
 *  数据定义语言包含定义数据库及其对象的语句，例如表，视图，触发器，存储过程等。
 *  数据操作语言包含允许您更新和查询数据的语句。
 *  数据控制语言允许授予用户权限访问数据库中特定数据的权限。  
    (2.1)MySQL是一个数据库管理系统，也是一个关系数据库。它是由Oracle支持的开源软件。MySQL可以在各种平台上运行UNIX，Linux，Windows等。可以将其安装在服务器甚至桌面系统上。 此外，MySQL是可靠，可扩展和快速的。  
    **3. 查询语句 SELECT FROM**
    
    **语句解释**  
    SELECT的语法：

SELECT 
        column_1, column_2, ...
    FROM
        table_1
    [INNER | LEFT |RIGHT] JOIN table_2 ON conditions
    WHERE
        conditions
    GROUP BY column_1
    HAVING group_conditions
    ORDER BY column_1
    LIMIT offset, length;

（1.2）SELECT语句由以下列表中所述的几个子句组成：

- SELECT之后是逗号分隔列或星号(*)的列表，表示要返回所有列。
    - FROM指定要查询数据的表或视图。
    - JOIN根据某些连接条件从其他表中获取数据。
    - WHERE过滤结果集中的行。
    - GROUP BY将一组行组合成小分组，并对每个小分组应用聚合函数。
    - HAVING过滤器基于GROUP BY子句定义的小分组。
    - ORDER BY指定用于排序的列的列表。
    - LIMIT限制返回行的数量。

**去重语句**  
distinct有重复记录去重，只能在所有字段最前方。  
`select distinct * from table(表名) where (条件)`  
**前N个语句**  
limit只在mysql中存在，不通用。语法`limit 起始下标（未指定默认为0），长度；`  
eg:limit 5,从0开始取前5条数据。  
取前N个某字段最大/小的5条数据，此时可以先order by asc(升序)/desc(降序)，默认为升序。  
**CASE…END判断语句**  
[https://www.yiibai.com/mysql/case-statement.html][https_www.yiibai.com_mysql_case-statement.html]  
（1.1）简单CASE语句。  
语法：

CASE  case_expression
       WHEN when_expression_1 THEN commands
       WHEN when_expression_2 THEN commands
       ...
       ELSE commands
    END CASE;

（1.2）可搜索CASE语句  
简单CASE语句仅允许您将表达式的值与一组不同的值进行匹配。 为了执行更复杂的匹配，如范围，您可以使用可搜索CASE语句。 可搜索CASE语句等同于IF语句，但是它的构造更加可读。  
语法：

CASE
        WHEN condition_1 THEN commands
        WHEN condition_2 THEN commands
        ...
        ELSE commands
    END CASE;

**4. 筛选语句 WHERE**  
条件查询需要用到where语句，where必须放到from语句表的后面。  
支持如下运算符：![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70]  
5. 分组语句 GROUP BY  
聚合函数/分组函数/多行处理函数（不能直接使用在where条件语句后）：  
![在这里插入图片描述][20190401123742424.png]  
分组查询主要涉及到两个子句，分别是：group by和having

*  如果使用了order by，order by必须放到group by后面（先分组再排序）
 *  在SQL语句中若有group by 语句，那么在select语句后面只能跟分组函数+参与分组的字段。
 *  如果想对分组数据再进行过滤需要使用having子句  
    **注意：** where和having都是为了完成数据过滤，where和having后面都是添加条件，where在group by 之前完成过滤，having在group by 之后完成过滤。  
    **原则：** 尽量在where中过滤，无法过滤的数据，先分组再过滤可选择having。  
    ![在这里插入图片描述][2019040112481031.png]

1.  排序语句 ORDER BY  
    排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面。asc(升序)/desc(降序)，默认为升序。
2.  函数  
    **时间函数**  
    mysql常用日期时间函数：[http://www.cnblogs.com/geaozhang/p/6740457.html][http_www.cnblogs.com_geaozhang_p_6740457.html]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 1]  
    mysql数据库管理系统中对日期处理提供两个重要函数：`str_to_date、data_format`

*  str\_to\_date 将“日期字符串”转换成“日期类型”。str\_to\_date(“日期字符串”，“日期格式”)  
    mysql的日期格式：`%Y年 %m月 %d日 %H时 %i分 %s秒`，默认日期格式为：`%Y-%m-%d`
 *  date\_format 将日期类型date转换成具有特定格式的日期字符串VARCHAR.  
    date\_format（日期数据类型，“日期格式”）  
    date\_format主要用在查询语句中，客户需要日期以特定格式展示时需要该函数。  
    `select date_format(hiredate,'%m-%d-%Y') as hiredate from <表名>`  
    **数值函数**  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 2]  
    **字符串函数**  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 3]

1.  SQL注释

*  SQL语句中的**单行注释**使用 – `create database database_x --创建数据库database_x`
 *  SQL语句中的**多行注释**采用 /*…*/

create database database_x
    /*
    创建一个数据库
    名字叫做database_x
    */

1.  SQL代码规范  
    \[SQL编程格式的优化建议\] [https://zhuanlan.zhihu.com/p/27466166][https_zhuanlan.zhihu.com_p_27466166]  
    \[SQL Style Guide\] [https://www.sqlstyle.guide/][https_www.sqlstyle.guide]

# 二、作业 #

1、项目一：查找重复的电子邮箱（难度：简单）

--创建表email
    CREATE TABLE email (
    	ID INT NOT NULL PRIMARY KEY,
    	Email VARCHAR(255));  
    --插入数据
    INSERT INTO email VALUES('1','a@b.com');
    INSERT INTO email VALUES('2','c@d.com');
    INSERT INTO email VALUES('3','a@b.com');
    --查看数据
    SELECT * FROM email;
    +----+---------+
    | ID | Email   |
    +----+---------+
    |  1 | a@b.com |
    |  2 | c@d.com |
    |  3 | a@b.com |
    +----+---------+

方法一：按照Email分组后having过滤

SELECT * FROM email GROUP BY email HAVING count(*)>1;

方法二：

SELECT Email FROM
    	(SELECT Email,COUNT(Email) AS countemail FROM email GROUP BY Email) AS A
    	WHERE countemail>1;

2、项目二：查找大国（难度：简单）如果一个国家的面积超过300万平方公里，或者(人口超过2500万并且gdp超过2000万)，那么这个国家就是大国家。

--创建World表
    CREATE TABLE World (
    name VARCHAR(50) NOT NULL,
    continent VARCHAR(50) NOT NULL,
    area INT NOT NULL,
    population INT NOT NULL,
    gdp INT NOT NULL
    );
    --插入数据
    INSERT INTO World
      VALUES('Afghanistan','Asia',652230,25500100,20343000);
    INSERT INTO World 
      VALUES('Albania','Europe',28748,2831741,12960000);
    INSERT INTO World 
      VALUES('Algeria','Africa',2381741,37100000,188681000);
    INSERT INTO World
      VALUES('Andorra','Europe',468,78115,3712000);
    INSERT INTO World
      VALUES('Angola','Africa',1246700,20609294,100990000);
    --查看数据
    SELECT * FROM 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 |
    +-------------+-----------+---------+------------+-----------+

--查找大国。注意当and和or同时出现时，and优先级高于or.
     SELECT name FROM World
     	where area>=3000000 or (population>25000000 and gdp>20000000);

[https_www.yiibai.com_mysql_how-to-load-sample-database-into-mysql-database-server.html]: https://www.yiibai.com/mysql/how-to-load-sample-database-into-mysql-database-server.html
[https_www.yiibai.com_mysql_case-statement.html]: https://www.yiibai.com/mysql/case-statement.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70]: /images/20220225/5cbd6fe9532e4d7ca6ea62cd211482b6.png
[20190401123742424.png]: /images/20220225/7d07c3ed72494bebbfffb400fd45c6a8.png
[2019040112481031.png]: /images/20220225/25eb93cff0d3439085ad52f1dacbddb9.png
[http_www.cnblogs.com_geaozhang_p_6740457.html]: http://www.cnblogs.com/geaozhang/p/6740457.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 1]: /images/20220225/7687467ba53740f4abf535798e328fa6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 2]: /images/20220225/59da33fe86be4afcb5a1d1f1e65c8cab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0d1aWFiYmV5_size_16_color_FFFFFF_t_70 3]: /images/20220225/83f13899b1054c55996829c63aed841b.png
[https_zhuanlan.zhihu.com_p_27466166]: https://zhuanlan.zhihu.com/p/27466166
[https_www.sqlstyle.guide]: https://www.sqlstyle.guide/