Mysql 复习笔记- 基础篇3 [常见增删改查]

Mysql 复习笔记- 基础篇3 [常见增删改查] Mysql 复习笔记- 基础篇*3 [常见增删改查]

声明:此笔记不会出现比如说Mysql发展历史这种问题，多为实用的命令和使用中的必要知识，请海涵

这篇文档我们不会对查询进行复习，我们将会把查询的 *** 作的部分放到了后面的查询文档中，我们将复习到级联查询，子查询，链接查询等内容

---

Insert into

概述:利用INSERT语句将数据插入表中

$ mysql> CREATE TABLE Customers
$ -> (
$ -> cust_id char(10) NOT NULL ,
$ -> cust_name char(50) NOT NULL ,
$ -> cust_address char(50) NULL ,
$ -> cust_city char(50) NULL ,
$ -> cust_state char(5) NULL ,
$ -> cust_zip char(10) NULL ,
$ -> cust_country char(50) NULL ,
$ -> cust_contact char(50) NULL ,
$ -> cust_email char(255) NULL
$ -> );
$ Query OK, 0 rows affected (0.06 sec)

---

插入完整的行

编写依赖与特定列次序的SQL语句，这样做有时会出错，但编写方便。

养成指定顺序插入数据，虽然写起来繁琐，但不容易发生错误。注意每个列，都必须提供一个值。

解释: 插入一个新客户到Customers表，存储到每个表列的数据VALUES子句中给出，对每个表必须提 供一个值，如果某列没值，就应该使用NULL值。

$ mysql> INSERT INTO Customers
$ -> VALUES('1000000006',
$ -> 'Toy Land',
$ -> '123 Any Street',
$ -> 'New York',
$ -> 'NY',
$ -> '11111',
$ -> 'USA',
$ -> NULL,
$ -> NULL);
$ Query OK, 1 row affected (0.01 sec)

---

插入部分行

指定某列提供值，其他的不提供值

插入部分值，前提条件是表允许:

• 改列定义为允许NULL值

• 表改成默认值，如果不给，将使用默认

  如果没有这两个前天条件，就服务插入部分值。

解释:忽略表中cust_ontact 与cust_email 值

$ mysql> INSERT INTO Customers(cust_id,
$ -> cust_name,
$ -> cust_address,
$ -> cust_city,
$ -> cust_state,
$ -> cust_zip,
$ -> cust_country)
$ -> VALUES('1000000008',
$ -> 'Toy Land',
$ -> '123 Any Street',
$ -> 'New York',
$ -> 'NY',
$ -> '11111',
$ -> 'USA');
$ Query OK, 1 row affected (0.01 sec)

---

插入检索出的数据

利用SELECt 语句的输出结果插入表中，INSERT SELECT两条结合。

解释:将 CustomersNew 所有的数据导入 Customers

$ mysql> CREATE TABLE CustomersNew
$ -> (
$ -> cust_id char(10) NOT NULL ,
$ -> cust_name char(50) NOT NULL ,
$ -> cust_address char(50) NULL ,
$ -> cust_city char(50) NULL ,
$ -> cust_state char(5) NULL ,
$ -> cust_zip char(10) NULL ,
$ -> cust_country char(50) NULL ,
$ -> cust_contact char(50) NULL ,
$ -> cust_email char(255) NULL
$ -> );
$ Query OK, 0 rows affected (0.06 sec)

$ mysql> INSERT INTO CustomersNew(cust_id,
$  -> cust_contact,
$  -> cust_email,
$  -> cust_name,
$  -> cust_address,
$  -> cust_city,
$  -> cust_state,
$  -> cust_zip,
$  -> cust_country)
$  -> SELECT cust_id,
$  -> cust_contact,
$  -> cust_email,
$  -> cust_name,
$  -> cust_address,
$  -> cust_city,
$  -> cust_state,
$  -> cust_zip,
$  -> cust_country
$  -> FROM Customers;
$ Query OK, 1 row affected (0.01 sec)

---

从一个表复制到另一个表

只复制表结构，不复制数据。

$  mysql> CREATE TABLE Customers_New like Customers;
$  Query OK, 0 rows affected (0.03 sec)

创建一个CustCopy 表，并把Customers表中的数据复制过来。

$  mysql> CREATE TABLE CustCopy AS
$   -> SELECt *
$   -> FROM Customers;
$   Query OK, 8 rows affected (0.03 sec)
$   Records: 8 Duplicates: 0 Warnings: 0

查看一个表是如果创建的

$  mysql> SHOW CREATE TABLE Customers

查看表的结构

$ mysql> DESC Customers

注意:

• 任何SELECt 选择的子句都可以使用。WHERe ,GROUP BY等

• 可以利用链接从多个表插入数据

• 不过从多少个表检索出来的数据，数据都只能插入一个表中。

  SELECT INTO 可以用来测试SQL语句前，复制一个表出来测试，避免影响原来的表。

---

删除数据

到这里我们的insert into基本内容已经概述完毕了，我们在进行删除和修改之前，我们要进行说一个规范规则，及更新和删除的原则：

• UPDATE跟DELETE语句都具有WHERe子句，如果忽略WHERe子句建会应用到所有行，所以除非更 新所有行
• 保证每个表都有主键，WHERe应用到主键。
• 使用UPDATE和DELETE语句前先，先SELECT进行测试，确保编写的WHERe子句正确。
• 使用强制实施引用完整性数据库，防止误删除行。
• 现在MYSQL不带有WHERe子句的UPDATE或DELETE子句执行。

---

我们先进行功能概述

使用 DELETE

• 从数据库删除数据,

• 从表中删除特定的行 从表中删除所有的行

  $ mysql> DELETE FROM Customers WHERe cust_id = '1000000009';# 指定删除 表Customers 中的数据 id 为 1 的数据
  $ Query OK, 1 row affected (0.01 sec)
  

  解释:指定删除 表Customers 中的数据

  $ mysql> DELETe FROM Customers;# 指定删除 表Customers 中的所有数据，不建议这样使用，删除需谨慎，三思而后行
  $ Query OK, 1 row affected (0.01 sec)
  

• 一般我们的业务逻辑一般使用的逻辑删除(及添加删除标记)，不使用物理删除，我们可以把其中一个字段设置成是否有效，我们在这里举一个例子

  -- auto-generated definition
  create table movies
  (
      Movie_id          int auto_increment comment '类型;类型'
          primary key,
      Movie_name        varchar(255)         not null comment '电影名;电影名',
      Movie_Name_second varchar(255)         not null comment '英文名;英文名',
      Movie_director    varchar(255)         not null comment '导演;导演',
      Movie_starring    varchar(255)         not null comment '主演;主演',
      Movie_price       decimal(24, 2)       not null comment '票价;票价',
      Movie_Is_show     tinyint(1) default 0 not null comment '是否进行售卖',
      Movie_create      datetime             not null comment '创建时间'
  )
      comment 'movies_Table;电影信息表';
  

  解释：我们这个数据模型的Movie_Is_show我们可以理解为我们的删除标记

---

注意:

• DELETe语句删除行，但不能删除表本身
• 想删除表中所有的行，可以使用TRUNCATE TABLE语句

---

修改数据

利用UPDATE和DELETE语句进行 *** 作表数据。

UPDATE用来更新修改表中的数据

• 更新表中特定的行

  update user_copy
  set user_copy.User_Address = '3441292862@qq.com';
  

  解释：我们把user_copy数据库的User_Address数据表字段的值更改为3441292862@qq.com

• 更新表中所有行

  注意: 如果省略了WHERe子句， 就会更新所有行。

  update user_copy
  set user_copy.User_Address = '3441292862@qq.com'
  where user_copy.User_id=1;
  

  解释：我们把user_copy数据库的User_Address数据表字段并且User_id的值为1的记录的User_Address的字段值更改为3441292862@qq.com

---

UPDATE语句有三个部分组合

• 要更新的表
• 列名和他们的新值
• 确定要更新哪些行的过滤条件

---

更新一列多个值

$ mysql> UPDATE Customers SET cust_contact = 'Sam Roberts',cust_email = 'sam@toyland.com' WHERe cust_id = '1000000006';
$ Query OK, 1 row affected (0.00 sec)
$ Rows matched: 1 Changed: 1 Warnings: 0

解释: 使用SET命令，每个‘列=值’用逗号隔开，区分多个列。

---

更新某个列NULL 可以把列设置成NULL，如果表允许设置NULL

$ mysql> UPDATE Customers SET cust_contact = NULL,cust_email = 'sam@toyland.com' WHERe cust_id = '1000000006';
$ Query OK, 1 row affected (0.00 sec)
$ Rows matched: 1 Changed: 1 Warnings: 0

---

检索数据

是最常用的SQL语句了，用来索引一个或者多个表信息。

• 关键字(keyword)
• 作为SQL组成部分的字段，关键字不能作为表或者列的名字。

使用SELECT索引数据，必须至少给出两条信息， 想要什么？ 从什么地方获取

---

检查单个列

SELECT prod_name FROM Products;

解释：使用SELECt 语句从 Products 表中检索一个名为prod_name 的列，FROM 关键字从指定的 标名索引。

输出结果

SELECt prod_name FROM Products;

+---------------------+
| prod_name |
+---------------------+
| Fish bean bag toy |
| Bird bean bag toy |
| Rabbit bean bag toy |
| 8 inch teddy bear |
| 12 inch teddy bear |
| 18 inch teddy bear |
| Raggedy Ann |
| King doll |
| Queen doll |
+---------------------+
9 rows in set (0.00 sec)

• SQL语句分成多好容易阅读与调试，
• 如果语句较长 SQL语句必须以(;)结束。
• SQL语句不区分大小写，除了表名，跟值以外，SQL关键字使用大写，便于阅读

---

索引多个列

与索引单列对比，唯一的不同是必须，在SELECt 关键字后给出多个列名，列名直接用 ， 隔开，最 后一列不需要。

SELECT prod_id, prod_name, prod_price FROM Products;

解释：使用SELECt 从表Products 中选择数据，指定3个列名，prod_id, prod_name, prod_price

输出：

+---------+---------------------+------------+
| prod_id | prod_name | prod_price |
+---------+---------------------+------------+
| BNBG01 | Fish bean bag toy | 3.49 |
| BNBG02 | Bird bean bag toy | 3.49 |
| BNBG03 | Rabbit bean bag toy | 3.49 |
| BR01 | 8 inch teddy bear | 5.99 |
| BR02 | 12 inch teddy bear | 8.99 |
| BR03 | 18 inch teddy bear | 11.99 |
| RGAN01 | Raggedy Ann | 4.99 |
| RYL01 | King doll | 9.49 |
| RYL02 | Queen doll | 9.49 |
+---------+---------------------+------------+
 9 rows in set (0.01 sec)

---

检索所有列

SELECT * FROM Products;

使用通配符 * 表示返回表中所有的列

+---------+---------+---------------------+------------+--------------+
| prod_id | vend_id | prod_name | prod_price | prod_desc|
| BNBG01 | DLL01 | Fish bean bag toy | 3.49 | Fish bean bag toy,
complete with bean bag worms with which to feed it |
| BNBG02 | DLL01 | Bird bean bag toy | 3.49 | Bird bean bag toy,
eggs are not included |
| BNBG03 | DLL01 | Rabbit bean bag toy | 3.49 | Rabbit bean bag toy,
comes with bean bag carrots |
| BR01 | BRS01 | 8 inch teddy bear | 5.99 | 8 inch teddy bear,
comes with cap and jacket |
| BR02 | BRS01 | 12 inch teddy bear | 8.99 | 12 inch teddy bear,
comes with cap and jacket |
| BR03 | BRS01 | 18 inch teddy bear | 11.99 | 18 inch teddy bear,
comes with cap and jacket |
| RGAN01 | DLL01 | Raggedy Ann | 4.99 | 18 inch Raggedy Ann
doll |
| RYL01 | FNG01 | King doll | 9.49 | 12 inch king doll with
royal garments and crown |
| RYL02 | FNG01 | Queen doll | 9.49 | 12 inch queen doll
with royal garments and crown |
 +---------+---------+---------------------+------------+-----------------------+
9 rows in set (0.00 sec)

除非需要表中每一列，或者不明确指定列，否则不要使用* 通配符。

---

高级数据过滤

---

*** 作符(operator)

用来改变WHERe子句中的子句关键字，也成逻辑 *** 作符。

---

AND *** 作符

通过使用AND来给WHERe子句附加条件。

我们来举一个例子

索引出供应商DLL01制造且价格小于等于4美金的所有产品名称和价格。

SELECt prod_id, prod_price, prod_name FROM Products WHERe vend_id = 'DLL01' AND prod_price <= 4;

解释: SLELECT 语句中的子句WHERe包含两个条件，供应商指定DLL01,价格高于4美金，不显示，如 果价格小于 4美金，都不术语DELL01的，也不显示。

---

OR *** 作符

检索匹配任意条件。

 SELECt prod_name, prod_price FROM Products WHERe vend_id = 'DLL01' OR vend_id = 'BRS01';

解释: 索引供应商所有产品的产品名和价格，并匹配任意条件 DLL01或者BRS01.

---

计算次序

WHERe 运行AND与 RO 结合，进行复杂 *** 作，和高级过滤。

检索10美金以上，并且由DLL10或者BRSO1制造。

SELECt prod_name, prod_price FROM Products WHERe vend_id = 'DLL01' OR
vend_id = 'BRS01' AND prod_price >= 10;

返回的价格带有10美金一下的， 原因是AND有优先级 ，SQL在处理 OR前，先处理了AND,直接检索 BRS01,或者DLL01，而忽略了价格。

解决的方法是用 园括号进行分组 *** 作。

SELECt prod_name, prod_price FROM Products WHERe (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;

()圆括号具有比AND，RO更高的 *** 作计算顺序。

注意: 使用AND 和OR *** 作WHERe句子，都应该用圆括号明确分组 *** 作。

---

NOT *** 作符

NOT *** 作符总是与其他 *** 作符一起使用，用在要过滤的前面。

SELECt vend_id, prod_name FROM Products WHERe NOT vend_id = 'DLL01'
ORDER BY prod_name;

解释：列出不带有DLL01之外的所有产品。

---

IN *** 作符

IN *** 作符用来指定范围，范围中的每一条，都进行匹配。IN取值规律，由逗号分割，全部放置括号中。

SELECt prod_name, prod_price FROM Products WHERe vend_id IN ('DLL01', 'BRS01' ORDER BY prod_name;

解释: 用SELECt检索，DLL01和BRS01制造的所有产品，IN *** 作符后跟由逗号分割的合法值清单。

IN 相当与完成了OR相同的功能，下面的结果与上面输出结果一样

SELECT prod_name, prod_price FROM Products WHERe vend_id = 'DLL01' OR vend_id = 'BRS01'  ORDER BY prod_name

使用IN的优点：

• 语法清晰，特别是语法较长时 *** 作符少
• 计算次序容易管理
• IN比OR执行速度快
• 最大的优点，可以包含其他SELECt语句，能够更加动态的建立WHERe子句。

模糊查询LIKE 与 REGEXP *** 作符

当需要搜索产品文本中包含某个特定关键字的所有产品，使用通配符来创建比较特定的数据搜索模式。

• 通配符(wildcard) 用来匹配值的一部分特殊字符。
• 搜索模式(search pattern) 由字母值，通配符两租组合构成的搜索条件。

通配符是SQL的WHERe子句中的特殊含义字符，子句中使用通配符必须使用LIKE *** 作符。

百分号%通配符

表示任何符合出现任意次数。多字符比较

 SELECT prod_id, prod_name FROM Products WHERe prod_name LIKE 'Fish%';

解释:检索以Fish 开头的词汇，Fish之后任意词汇，区分大小写。

 SELECt prod_id, prod_name FROM Products WHERe prod_name LIKE '%bean bag%';

解释:检其中包含bean bag的词汇，Fish之后任意词汇，区分大小写。

 SELECt prod_id, prod_name FROM Products WHERe prod_name LIKE '%Fish';

解释:检索以Fish 结束的词汇，Fish之后任意词汇，区分大小写。

 SELECt prod_id, prod_name FROM Products WHERe prod_name LIKE 'F%y';

解释:匹配F开头，y结尾的所以产品

下划线 _ 通配符

下划线与%不同的是匹配单个字符，而不是多个字符。

 SELECt prod_id, prod_name FROM Products WHERe prod_name LIKE '_ inch teddy bear';

解释：一个_匹配一个字符串。

方括号[]通配符

select count(*) from movies.movies where Movie_name regexp '[a,b]';

匹配任意带有JM的字符串的列。

它与我们下面的语句的作用是相同的

select count(*) from movies.movies where Movie_name like '%a%' or Movie_name like '%b%';

通配符技巧

• 其他 *** 作如果能达到相同的效果，就不要用通配符。
• 使用通配符尽量，缩小检索范围。
• 主要通配符的位置

---

创建计算字段

存储在数据库表中的数据一般不是应用程序所需要的格式，例如：

• 显示两个信息，但不是在用一个表
• 不同列中，但程序需要把他们作为一个格式的字段检索出来
• 列数据是大小混合，但程序需要把所以数据按大写表示。 物品订单表存储的物品的价格和数量，但没有存储物品的总价，打印时，需要物品的总价格。 根据需要表的数据进行总数，平均数等计算。 上面的情况都我们需要从数据库中转换，计算格式化，而不是索引出来再进行计算。

计算字段是在运行时SELECt语句内创建的。

字段(field)

与列(column)意思类似，经常相互转换使用，字段通常用在计算字段的链接上。

在SQL内完成转换和格式化，比在客户机应用程序内完成，处理数度更快。

拼接字段(concatenate)

vendors 表包含供应商id 跟 位置信息，现在需要生成一个供应商表，需要格式化名称，列出供应商 位置。此报表需要单个值，而表中的数据存储在， vend_name 和 vend_country 中，还需要 建 vend_country 括起来。

• 将值联结到一起创建单个值。

  在SQL中使用一个特使 *** 作符来拼接两个列，+ *** 作符用加号(+),两个竖杆(||)表示。

  在mysql使用CONCAt()函数把项表链接起来，而|| 通等于 *** 作符OR 而&&通等于AND *** 作符。

下面是在mysql中执行的结果

select concat('name is',movies.movies.Movie_name,',director is', movies.movies.Movie_director) from movies.movies;

---

别名(as)

拼接的地址字段，没有一个名字，无法给客户机应用，所以需要字段 别名(alias) ,另一种叫法导出 列 (derived column)

别名可以用 AS关键字赋予 。

select concat('name is',movies.movies.Movie_name,',director is', movies.movies.Movie_director)as movies_info from movies.movies;

解释: 这样用AS Movie_name指定拼接输出结果的列名，

---

别名的另一个用法，列重命名

select movies.movies.Movie_director as movies_info from movies.movies;

把 vend_name 输出重命名为 movies_info.

执行算术计算

用于检索出数据进行计算。

检索出Orders 表中包含收到的所有订单，OrderItems表包含每个订单中的各项物品，

select movies.orders.Orders_price*10000 as ones
from movies.orders;

解释：查询Orders_price字段值*10000的结果

支持的运算字符 + 加，- 减，* 乘，除 /

---

使用数据处理函数

函数主要给数据提供处理与转换方便。

大多数SQL实现的函数

• 用于处理文本串(删除，充值，大小写转换)
• 用于在数值的数据上进行算术(返回绝对值，代数运算) *** 作。
• 用于处理日期时间值并从这些值中提取特定成份。
• 返回DBMS正使用的特殊信息(用户登录信息)。

---

字符串函数 函数名描　　述示　　例SUBSTRING字符串截取Select substring(‘1234567’,4,2)返回：‘45’CONCAT字符串拼接Select concat(‘JBNS’,‘My Jbns’)返回：JBNSMy JbnsLENGTH返回传递给它的字符串长度Select length(‘mySQL课程’)返回：11INSTR(str,substr)返回字符串 str 中子字符串的第一个出现位置**SELECT INSTR(‘foobarbar’, ‘bar’);**返回：4LTRIM/RTRIM/TRIM清除左/右/左+右空格SELECT LTRIm (’ 周智宇 ')返回：周智宇 （后面的空格保留）UPPER/ LOWER转成大写/SELECT upper (’ ssssss’)返回： SSSSSSRIGHT从字符串右边返回指定数目的字符SELECT RIGHt(‘买卖提.吐尔松’,3)返回：吐尔松REPLACE替换一个字符串中的字符SELECT REPLACE(‘莫乐可切.杨可’,‘可’,‘兰’)返回：莫乐兰切.杨兰

---

日期函数 函数名描　　述示　　例NOW取得当前的系统时间SELECT NOW()返回：‘2016-09-15 18:30:51’CURDATE取得当前的系统日期SELECT CURDATE();返回：‘2016-09-15’DATE_ADD日期加上后边的时间SELECT DATE_ADD(‘1998-01-02’, INTERVAL 31 DAY)返回：‘1998-02-02’DATE_SUB前时间减去后边的时间SELECT DATE_SUB(‘1998-01-02’, ‘1998-01-03’)DATEDIFF两个日期之间的指定日期部分的间隔SELECT DATEDIFF(‘1997-12-31 23:59:59’,‘1997-12-30’);返回：1DATE_FORMAT日期格式化输出(推荐使用) 二十四小时制度 SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%H:%i:%s’);返回：‘22:23:00’

(不推荐使用) 十二小时制度 SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%h:%i:%s’);返回：‘11:23:00’

(不推荐使用)日期显示格式一 SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%y:%m:%d’);返回：‘97-10-04’

(推荐使用) 日期显示格式二 SELECT DATE_FORMAT(‘1997-10-04 22:23:00’, ‘%H:%i:%s’);返回：'1997-10-04

---

数学函数 函数名描　　述示　　例RAND返回从 0 到 1 之间的随机 float 值SELECT RAND( )返回：0.79288062146374ABS取数值表达式的绝对值SELECT ABS(-43)返回：43CEILING/CEIL取大于或等于指定数值、表达式的最小整数SELECT CEILING(43.5)返回：44FLOOR取小于或等于指定表达式的最大整数SELECT FLOOR(43.5)返回：43POWER取数值表达式的幂值SELECT POWER(5,2)返回：25ROUND将数值表达式四舍五入为指定精度SELECT ROUND(43.543,1)返回：43.500SIGN对于正数返回+1，对于负数返回-1，对于0则返回0SELECT SIGN(-43)返回：-1MOD取余数SELECT MOD(8，3)返回：2

---

系统函数 FORMAT格式化输出**SELECT FORMAT(12332.1,4);**返回：‘12,332.1000’CURRENT_USER返回当前用户的名字SELECT CURRENT_USER返回：你登录的用户名CAST(value as type)获取一类型的值，并产生另一类型的值CAST(‘234’ as unsigned)返回：整数ConVERT(value, type)ConVERT(‘234’,unsigned)返回：整数ConVERT(‘234’,unsigned)返回：整数

---

更多函数内容

---

聚集函数

• 确定表中的行数
• 获得表中行组的和
• 找出表列(所有行，特定行)的最大，最小，平均值。
  上面的例子需要对表中的数据汇总，而不是实际数据本身，所以可以不需要返回时间数据，浪费资源

Mysql常见聚合函数 函数作用AVG()返回某列的平均值COUNT()返回某列的行数MAX()返回某列的最大值MIN()返回某列的最小值SUM()返回某列的和

运行在行组上，计算和返回单个值的函数。

• AVG(): 返回所有列或者某个列平均值。
  计算表中的行数并计算特定列值之和，求得改列的平均值。

mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM Products;
+-----------+
| avg_price |
+-----------+
|  6.823333 |
+-----------+
1 row in set (0.01 sec)

解释: 计算Products表中所以产品的平均价格。

计算特定行的平均值，

mysql> SELECt AVG(prod_price) AS avg_price
    -> FROM Products
    -> WHERe vend_id = 'DLL01';
+-----------+
| avg_price |
+-----------+
|  3.865000 |
+-----------+
1 row in set (0.01 sec)

解释: WHERe 子句过滤出DELL01平均值，并返回该供应商产品的平均值。

• COUNT()函数计算表中行的数目或符合特定条件的涵数目。

  • 忽略表列中包含的空值(NULL)与非空值，对表中数目进行计算。

  • 使用COUNT(column) 对特定列中具有值的行进行计算，忽略NULL值。

    mysql> SELECt COUNT(*) AS num_cust
    -> FROM Customers;
    +----------+
    | num_cust |
    +----------+
    |        5 |
    +----------+
    1 row in set (0.01 sec)
    

返回custoemrs 中客户的总数，不管行中各列的数值。

mysql> SELECt COUNT(cust_email) AS num_cust
    -> FROM Customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

值返回有email地址的客户计数，结果为3，表述只有3个客户有电子邮件地址。

MAX()返回指定的列中最大的值

mysql> SELECt MAX(prod_price) AS max_price
    -> FROM Products;
+-----------+
| max_price |
+-----------+
|     11.99 |
+-----------+
1 row in set (0.01 sec)

解释: 返回Products表中最贵的物品价格。

• MIN() 返回最小值

  mysql> SELECt MIN(prod_price) AS min_price
    -> FROM Products;
  +-----------+
  | min_price |
  +-----------+
  |      3.49 |
  +-----------+
  1 row in set (0.00 sec)
  

• SUM()返回指定的列值的总和

  mysql> SELECt SUM(quantity) AS items_ordered
    -> FROM OrderItems
    -> WHERe order_num = 20005;
  +---------------+
  | items_ordered |
  +---------------+
  |           200 |
  +---------------+
  1 row in set (0.01 sec)
  

解释：返回计算quantity 值之和，WHERe子句限制值统计某个订单的值。

用SUM()组合计算值

mysql> SELECt SUM(item_price*quantity) AS total_price
    -> FROM OrderItems
    -> WHERe order_num = 20005;
+-------------+
| total_price |
+-------------+
|     1648.00 |
+-------------+
1 row in set (0.01 sec)

解释: 合计所以订单 item_price价格 乘以quantity数量之和的总数，WHERe子句某个订单物品。

聚集不同值

• 对所有的行执行计算，指定ALL参数或者不改参数(默认是ALL行为)

• 只包含不同的值，指定DISTINCT参数

  mysql> SELECt AVG(DISTINCT prod_price) AS avg_price
    -> FROM Products
    -> WHERe vend_id = 'DLL01';
  +-----------+
  | avg_price |
  +-----------+
  |  4.240000 |
  +-----------+
  1 row in set (0.02 sec)
  

解释:与上一个例子不同的是，排除prod_price 中相同的值，只计算不同的值，数量少了所以平均值高了。

组合聚集函数

用SELECt 来组合聚集函数。

 mysql> 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;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|         9 |      3.49 |     11.99 |  6.823333 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)

• 聚集函数用来总汇数据， SQL支持5个聚集函数，计算速度比在客户端快多。

分组数据

用GROUP BY 跟 HAVINg子句，分组数据来汇总表内容子集。

创建分组

分组在SELECt语句的GROUP BY子句中建立。

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM Products
    -> GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   |         3 |
| DLL01   |         4 |
| FNG01   |         2 |
+---------+-----------+
3 rows in set (0.01 sec)

解释: SELECt语句指定两个列，vend_id 包含供应商ID，为num_prods 计算字段结果，GROUP BY 子句指示 vend_id 排序并分组数据，

GROUP BY子句重要规则:

• 包含任意数目的列，
• 如果在GROUP BY 子句中套入分组，数据将会最后规定的分组上进行总汇。
• GROUP BY 子句中列出的没列都必须是检索的列，有效的表达式，不能聚集函数。
• 大多数SQL不允许GROUP BY 带有长度可变的数据类型(文本，备注型字段)
• 除聚集计算语句外，SELECT 语句中，每个列都必须在GROUP BY子句中给出。
• 如果分组带有NULL值，将作为一个分组返回，如果多个将成一组。
• GROUP BY 子句必须出现在WHERe子句之后，

过滤分组

过滤分组规定包含哪些分组，排除哪些分组，用HAVINg子句，与WHERe子句类似，唯一差别的是WHERe用来过滤行，HAVINg过滤分组。也可以说HAVINg在数据分组后过滤，WHERe在数据分组前进行过滤。

HAVINg 支持所有WHERe的 *** 作符。

mysql> SELECT cust_id, COUNT(*) AS orders
    -> FROM  Orders
    -> GROUP BY cust_id
    -> HAVINg COUNT(*) >= 2;
+------------+--------+
| cust_id    | orders |
+------------+--------+
| 1000000001 |      2 |
+------------+--------+
1 row in set (0.00 sec)

解释: 过滤出两个以上订单的分组

WHERe与HAVINg子句结合使用

mysql> SELECt vend_id, COUNT(*) AS num_prods
    -> FROM Products
    -> WHERe prod_price >= 4
    -> GROUP BY vend_id
    -> HAVINg COUNT(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   |         3 |
| FNG01   |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

解释: 第一行使用聚集函数，WHERe子句过滤除所有prod_price少于4的行，按vend_id分组，HAVINg子句过滤计数2以上分组。

去掉WHERe 过滤

mysql> SELECt vend_id, COUNT(*) AS num_prods FROM Products  GROUP BY vend_id HAVINg COUNT(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
| BRS01   |         3 |
| DLL01   |         4 |
| FNG01   |         2 |
+---------+-----------+
3 rows in set (0.01 sec)

过滤出销售产品在4个，且价格是4一下的。

分组和排序

GROUP BY 与 ORDER BY区别

• GROUP BY
  • 排序产生的输出
  • 任意列都可以使用
  • 可以选择是否与聚集函数一起使用
• ORDER BY
  • 分组行，输出可能不是分组循序
  • 只可能使用选择列或表达式，且必须使用每个列表达式
  • 如果与聚集函数一起用，则必须使用
    注意: 不用依赖于GROUP BY 排序，应该使用GROUP BY 时，也该处ORDER BY子句。

检索除3个或以上的物品订单号与订购物品数目：

mysql> SELECt order_num, COUNT(*) AS items
    -> FROM OrderItems
    -> GROUP BY order_num
    -> HAVINg COUNT(*) >= 3;
+-----------+-------+
| order_num | items |
+-----------+-------+
|     20006 |     3 |
|     20007 |     5 |
|     20008 |     5 |
|     20009 |     3 |
+-----------+-------+
4 rows in set (0.00 sec)

按订购物品数目排序输出。

mysql> SELECt order_num, COUNT(*) AS items
    -> FROM OrderItems
    -> GROUP BY order_num
    -> HAVINg COUNT(*) >=3
    -> ORDER BY items, order_num;
+-----------+-------+
| order_num | items |
+-----------+-------+
|     20006 |     3 |
|     20009 |     3 |
|     20007 |     5 |
|     20008 |     5 |
+-----------+-------+
4 rows in set (0.00 sec)

解释: GROUP BY 子句用来分组数据， COUNT(*)函数返回订单中物品数目，HAVINg 子句过滤数据，返回3个或3个以上的物品订单，ORDER BY最后排序输出。

SELECt子句顺序 子句说明是否必须使用SELECT要返回的列或表达式是FROM从中检索数据仅从 表中选择数据时使用WHERe行级过滤否GROUP BY分组说明仅按组计算聚集使用HAVINg组级过滤否ORDER BY输出排序顺序否

更多查询的内容请期待------