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

一、学习内容

1. 导入示例数据库，教程 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
（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语句表的后面。
支持如下运算符：
5. 分组语句 GROUP BY
聚合函数/分组函数/多行处理函数（不能直接使用在where条件语句后）：

分组查询主要涉及到两个子句，分别是：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。
  

6. 排序语句 ORDER BY
   排序采用order by子句，order by后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序，如果存在where子句那么order by必须放到where语句的后面。asc(升序)/desc(降序)，默认为升序。

7. 函数
   时间函数
   mysql常用日期时间函数：http://www.cnblogs.com/geaozhang/p/6740457.html
   
   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 <表名>
  数值函数
  
  字符串函数
  

8. SQL注释

• SQL语句中的单行注释使用 – create database database_x --创建数据库database_x

• SQL语句中的多行注释采用 /…/

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

9. SQL代码规范
   [SQL编程格式的优化建议] https://zhuanlan.zhihu.com/p/27466166
   [SQL Style 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);