【MySQL_2】基本语句

1. 导入示例数据库

• 教程

• MySQL 示例数据库结构

2. SQL是什么？MySql是什么

• SQL
  • （发音为字母 S-Q-L 或 sequel）是 Structured Query Language（结构化查询语言）的缩写。SQL 是一种专门用来与数据库沟通的语言
• MySQl
  • $\color{red}SQL是一种语言，MySQL是数据库管理系$SQL是一种语言，MySQL是数据库管理系
  • MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。

2. 查询语句 SELECT FROM

3.1. 语句解释

• select——从一个或者多个表中检索信息（SQL不区分大小写）

  1. select prod_name from products;
     

     • 从products表中检索一个名为prod_name的列

  2. select prod_id,prod_name,prod_price from products;
     

     • 检索多个列

  3. select * from products;
     

     • 检测所有列

3.2.去重语句

• 检索不同的值

  select vend_id from products;
  

  他会返回所有的供货商id（假如有9个商品，一共3个供货商，那么这个语句会返回9行。）

  select distinct ven_id from products;
  

  • 之返回3个供应商（去掉了重复项）

3.3前N个语句

• 值返回前N行

  select prod_name from products LIMIT N;
  

  • 返回前N行数据

  select prod_name from products limit 3 offset 5;
  

  • 从这一列的第3个开始的后5个数（不包括第三个）

3.4 CASE…END判断

1 select CName,头衔=case
2                 when CLevel='A1' then '初级程序员'
3                 when CLevel='A2' then '中级程序员'
4                 when CLevel='A3' then '高级程序员'
5                 else '骨灰级大师'
6             end
7 from Coder

参考：SQL Server中 case…end 用法

4. 过滤语句

4.1.语句解释

• 数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）

  select prod_name，prod_price from products where prod_price=3.9;
  

  • 从product 表中检索两个列，只返回prod_price值为3.49的行

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

4.2.1 操作符

1. 检查单个值

   select prod_name, prod_price from products where prod_price < 10;
   

   • 列出所有价格小于 10 美元的产品

2. 不匹配检查

   select ven_id, prod_name from products where vend_id <> 'DLL01'
   select ven_id, prod_name from products where vend_id != 'DLL01'
   

   • 子列出所有不是供应商 DLL01 制造的产品
   • != 和 <>通常可以互换

3. 范围检查

   select prod_name, prod_price from products where prod_price between 5 and 10;
   

   • BETWEEN 操作符——检索价格在 5 美元和 10美元之间的所有产品
   • 两个值必须用 AND 关键字分隔（包括指定的开始值和结束值）

4. 空值检查

   select prod_name from products where prod_price is null;
   

4.2.2. 通配符

• 利用通配符，可以创建比较特定数据的搜索模式
• 通配符（wildcard）—— 用来匹配值的一部分的特殊字符。
• 搜索模型（search pattern）—— 由字面值、通配符或两者组合构成的搜索条件

1. 百分号（%）

   select prod_id, prod_name from products where prod_name like 'Fish%';
   

   • 找出所有以词 Fish 起头的产品
   • 区别大小写

2. 下划线（_）

   select prod_id, prod_name from products where prod_name like '__inch teddy bear';
   

   • $\color{Red}但它只匹配单个字符，而不是多个字符$但它只匹配单个字符，而不是多个字符
   • 8 inch teddy bear 产品没有匹配，因为搜索模式要求匹配两个通配符而不是一个( _ 总是刚好匹配一个字符，不能多也不能少)

3. 方括号（[]）

   select cust_contact from Customers where cust_contact link '[JM]%'
   

   • 出所有名字以 J 或 M 起头的联系人
   • WHERE 子句中的模式为’[JM]%’。使用了两个不同的通配符。[JM]匹配方括号中任意一个字符，它也只能匹配单个字符，因此，任何多于一个字符的名字都不匹配。

5. 分组语句GROUP BY

5.1 聚集函数

• 聚类函数（aggregate function）——对某些行运行的函数，计算并返回一个值

1. AVG（）

select AVG(prod_price) as avg_price from products
~~~

• 求该列的平均值
• 忽略列值为 NULL 的行

2. COUNT（）

   • 使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值

   • 使用 COUNT(column)对特定列中具有值的行进行计数，忽略 NULL 值（不计空值）

   select count(*) as num_cust from Customsers;
   
   selcct count(cust_email) as num_cust_1 from Cutomsers;
   

   • num_cust_1 <= num_cust

3. MAX（）

   • MAX()返回指定列中的最大值。MAX()要求指定列名

   select max(prod_price) as max_price from products
   

4. MIN（）

   • 回指定列的最小值

select min(prod_price) as min_price from products;

5. SUM（）
   • SUM()用来返回指定列值的和（总计）
   • $\color{Red}在多个列上计算$在多个列上计算

select sum(item*quantity) as total_price from orderItems

• 组合集合函数

  select count(*) as num_items,
  		min(prod_price) as price_min,
  		max(prod_price) as price_max,
  		avg(prod_price) as price_avg
  from products;
  

  • 单条 SELECT 语句执行了 4 个聚集计算，返回 4 个值（Products表中物品的数目，产品价格的最高值、最低值以及平均值）

5.2 语句解释

select vend_id, count(*) as num_prods from products group by vend_id;

• select 语句指定了两个列：vend_id 包含了产品供应的 ID ，num_prods 为计算字段（用 COUNT(*)函数建立）
• GROUP BY 子句指示 DBMS 按 vend_id 排序并分组数据

5.3.HAVING 字句

• SQL 允许过滤分组，规定包括哪些分组，排除哪些分组

select cust_id, count(*) as orders from orders
group by cust_id
having count(*) >= 2;

• 过滤了 count（*） >=2（两个以上订单）的那些分组
• $\color{Red}having 和 where 区别$having和where区别
  • WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

6. 排序语句 oreder by

6.1.语句解释

• 为了明确地排序用 SELECT 语句检索出的数据，可使用 ORDER BY 子句

  select prod_name from products order by prod_name;
  

  • 对 prod_name 列以字母顺序排序数据

• 按照多个列排序

  select prod_id, prod_price, prod_name from products
  order by prod_price, prod_name;
  

  • 如果 prod_price 列中所有的值都是唯一的，则不会按 prod_name 排序

• 按照位置进行排序

  select prod_id, prod_price, prod_name from products
  order by 2,  3
  

  • ORDER BY 2 表示按 SELECT 清单中的第二个列 prod_name 进行排序。ORDER BY 2，3 表示先按 prod_price，再按 prod_name 进行排序。

6.2. 正序、逆序

• 可以升序、降序排序，只要指定DESC关键字。默认的是升序，DESC是降序，ASC是升序（但一般不用，因为默认就是它）

  select prod_id, prod_price, prod_name 
  from Products 
  order prod_price DESC, prod_name;
  

  • DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定 DESC，对 prod_name 列不指定。因此，prod_price 列以降序排序，而 prod_name 列（在每个价格内）仍然按标准的升序排序

7. 函数

7.1. 时间函数

参考——MySQL 获得当前日期时间 函数

7.2. 数值函数

7.3 字符串函数

参考——常用字符串函数

8.SQL注释

1. # 这是注释
   SELECT prod_name 
   FROM Products;
   

2. SELECT prod_name -- 这是一条注FROM Products;
   

3. /* SELECT prod_name, vend_id 
   FROM Products; */ 
   SELECT prod_name 
   FROM Products;
   

   • 单行注释，跨行注释

9. 代码规范

参考——SQL编程格式的优化建议

10. 项目小练习

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

  创建 email表，并插入如下三行数据

  ±—±--------+

  | Id | Email |

  ±—±--------+

  | 1 | [email protected] |

  | 2 | [email protected] |

  | 3 | [email protected] |

  ±—±--------+

  编写一个 SQL 查询，查找 Email 表中所有重复的电子邮箱。

  根据以上输入，你的查询应返回以下结果：

  ±--------+

  | Email |

  ±--------+

  | [email protected] |

  ±--------+

  说明：所有电子邮箱都是小写字母。

1. 利用Navicat新建列表（新建，然后保存）

2. 打开表后插入数据

结果：

• 【2】项目二：查找大国（难度：简单）

  创建如下 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 |

  ±-------------±------------±-------------+

• 结果：

11.参考

1. 【MySQL】2.基本语句

2. 《SQL必知必会》