SQL基础交互

第二章 检索数据

例如，我们从数据库表 products 中查询 prod_id 和 vend_id,各个列之间以逗号分隔，最后一列的后面不加逗号。

    SELECT prod_id, vend_id FROM products;

我们还可以从数据库表中查询所有列。例如:

    SELECT prod_id, vend_id, prod_name, prod_price, prod_desc FROM products;

除了列出所有的列之外，我们还可以使用*号通配符来做到这点。

    SELECT * FROM products;
一般而言，除非需要表中的每一列，否则最好不要使用 * 通配符。因为查询不需要的列通常会降低查询和应用程序的性能。阿里java开发手册中也明确指出，在表查询中，一律不要使用 * 作为查询的字段列表，需要查询哪些字段必须明确写明。

前面的查询中，SELECT 语句会返回所有匹配的行，但是，有时候我们不希望每个值每次都出现。例如，我们来查询 products 表中所有产品的供应商ID：

    SELECT vend_id FROM products;
结果有 9 行，但是却只包含 3 个不同的供应商 ID。这时候该怎么办呢？办法就是使用 DISTINCT 关键字。顾名思义，它表示数据库只返回不同的值。
    SELECT DISTINCT vend_id FROM products;

有点要注意的地方是，DISTINCT 关键字作用于所有的列，不仅仅是跟在其后的那一列。而且，DISTINCT 关键字要放在所有要查询的列的最前面。不可以放在中间位置。例如：
    
    SELECT DISTINCT vend_id, prod_price FROM products;
有时候，我们并不想一次返回所有的结果，而是返回一定数量的记录。我们可以使用 LIMIT 子句来实现这一目的。
    SELECT prod_name FROM products LIMIT 5;

这样便只返回了 5 条记录。
如果我们想返回后面的几条数据怎么办？这时候我们便需要指定从哪开始返回以及返回的行数。
    SELECT prod_name FROM products LIMIT 5 OFFSET 5;
这样我们便返回了后面的几条数据，由于总共只有9条数据，所以只返回了 4 条数据。这里有点要注意的是，第一个被查询的行是第 0 行，而不是第 1 行。
    SELECT prod_name FROM products LIMIT 1 OFFSET 0;
可以看到结果返回的是第 0 行而不是第 1 行

注释

随着 SQL 语句越来越复杂，我们需要添加一些注释，便于自己以及别人参考。

有三种不同类型的注释：

和 SQL 语句在同一行。

    SELECT prod_name -- 这是一条注释
    FROM products;

运行结果如下：

自己单独一行

# 这是一条注释
    SELECT prod_name 
    FROM products;

3.注释分布在多行
/*
这是一条注释
这是一条注释
这是一条注释
*/
SELECT prod_name 
FROM products;

返回

第三章 排序数据

大家好！本讲我们来讲下如何对查询结果进行排序。

单列排序

我们先来看下不排序的结果:

SELECT prod_name FROM products;

运行结果如下：

接下来我们对 prod_name 列进行排序。排序使用 ORDER BY 关键字，后面跟着要排序的列。例如：

SELECT prod_name FROM products ORDER BY prod_name;

运行结果如下：

可以看到结果按照升序进行了排序。

当我们不明确指定排序的顺序时，MySQL 默认按照升序排序。效果和我们明确指定升序是一样的，如果要明确指定按照升序排序，我们只要在需要排序的字段后面指定 ASC。例如：

SELECT prod_name FROM products ORDER BY prod_name ASC; 

运行结果如下：

可以看到结果和不明确指定排序顺序是一样的。

和升序相对应的，我们可以进行降序排序。语法和升序一样，只不过 ASC 改成 DESC。例如：

SELECT prod_name FROM products ORDER BY prod_name DESC;

运行结果如下：

我们可以看到，结果是降序排序的。

多列排序

除了可以对单列进行排序，我们还可以对多列进行排序，例如：

SELECT prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

运行结果如下：

像单列排序一样，我们同样可以明确指定排序顺序，例如：

SELECT prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;

运行结果如下：

我们可以看到，结果按照 prod_price 进行了降序排序，但是当 prod_price 相等时，prod_name 还是按照升序进行排序，这说明 DESC 关键字只对位于其前面的列名起作用，

如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。例如：

SELECT prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name DESC;

运行结果如下：

我们可以看到，当 prod_price 相等时，prod_name 同样按照降序进行了排序。

刚才讲的排序，ORDER BY 后面跟的列都是我们要查询的列，实际上，用非查询的列排序数据也是可以的。例如：

SELECT prod_name FROM products ORDER BY prod_price;

运行结果如下：

ORDER BY 后面除了跟列名之外，我们还可以使用查询列的相对位置，

SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3;

运行结果如下：

这种排序方式的优点是：不用重复输入列名。缺点是：1.有可能用错误的列进行排序，特别是在检索列改变后，忘记对order by子句做相应的改动。2.如果要进行排序的列不在检索的列中，就不能使用相对位置。

小结

课后题

单选题.下列哪一个关键字的作用是排序？

A. ORDER BY

B. ASC

C. DESC

D. SELECT

选择正确！ORDER BY关键字可以排序！

单选题.下列哪一个关键字的作用是降序排序？

A. ORDER BY

B. ASC

C. DESC

D. SELECT

选择正确！DESC关键字可以降序排序！

 

第四章 过滤数据

大家好！本讲我们来讲一下，如何对数据进行过滤。

在实际工作中，我们用到的数据库表一般都比较大，记录条数在百万级甚至千万级。对于记录数比较多的表，只有在极少数情况下，才需要查询表里的全部数据。当我们只需要查询表里的部分数据时，需要我们在查询数据的时候指定查询条件。

列的类型为数值的过滤

我们可以使用 WHERE 子句指定查询条件来过滤数据。可以在 WHERE 子句中使用的操作符包括：=，>，<，>=，<=，<>，BETWEEN AND，LIKE等。

下面我们分别演示下各个操作符的使用：

从 products 查询 prod_price 为9.49的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE
    prod_price = 9.49;

运行结果如下：

从 products 查询 prod_price 大于9.49的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE 
    prod_price > 9.49;

运行结果如下：

从 products 查询 prod_price 小于9.49的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE 
    prod_price < 9.49;

运行结果如下：

从 products 查询 prod_price 大于等于9.49的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE 
    prod_price >= 9.49;

运行结果如下：

从 products 查询 prod_price 小于等于9.49的 数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE 
    prod_price <= 9.49;

运行结果如下：

从 products 查询 prod_price 不等于9.49的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE 
    prod_price <> 9.49;

运行结果如下：

从 products 查询 prod_price 介于5.99到11.99之间的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE 
    prod_price BETWEEN 5.99 AND 11.99;

运行结果如下：

还有另一种写法：

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE  
    prod_price >= 5.99 AND prod_price <= 11.99;

运行结果如下：

可以观察到两种写法的结果都是一样的。

列的类型为字符串的过滤

刚才举的例子，列的类型为数值，如果列的类型为字符串，在和值进行比较时，需要给值加上单引号。

例如：从 products 查询 vend_id 为 DLL01 的数据

SELECT 
    prod_name, 
    prod_price, 
    vend_id 
FROM 
    products 
WHERE 
    vend_id = 'DLL01';

运行结果如下：

从 products 查询 vend_id 不是 DLL01 的所有数据

SELECT 
    prod_name, 
    prod_price, 
    vend_id 
FROM 
    products 
WHERE 
    vend_id <> 'DLL01';

运行结果如下：

NULL 值的过滤

在数据库里还有一类值叫NULL，它表示没有任何值，它与字段包含 0、空字符串或仅包含空格不同。确定值是否为 NULL 不能简单的用 = NULL，要用 IS NULL。例如：

SELECT 
    cust_id, 
    cust_name
FROM 
    customers 
WHERE 
    cust_email IS NULL;

运行结果如下：

多条件的过滤

刚才在过滤数据时使用的都是单一条件，可以使用OR、AND操作符将多个条件组合在一起。

AND 用来指示查询满足所有给定条件的行，我们来演示下 AND 的用法。

SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    vend_id = 'BRS01' 
    AND prod_price < 10;

运行结果如下：

OR用来指示检索满足任一给定条件的行。

SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    vend_id = 'BRS01' 
    OR prod_price < 10;

运行结果如下：
AND 和 OR 组合在一起。
假设我们要查询由 DLL01 或 FNG01 制造，且价格大于 9 美元的商品。

如果我们将 SQL 写成如下这样：

SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    vend_id = 'DLL01' OR vend_id = 'FNG01'
    AND prod_price > 9;
我们发现结果是不对的，查询出来的有些商品的价格是不大于 9 美元的。这是因为 AND 操作符的运算优先级大于 OR，查询的条件变成了商品由供应商 DLL01 制造或者由供应商 FNG01 制造并且商品价格大于 9 美元。正确的 SQL 语句应该写成如下形式：
SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    (vend_id = 'DLL01' OR vend_id = 'FNG01')
    AND prod_price > 9;

我们发现现在的结果符合我们的期望。
建议：当过滤条件里包括AND和OR时，都应该使用括号明确地指定操作符的执行顺序，不要依赖操作符的默认求值顺序，即使默认求值顺序如希望的那样。使用括号没有什么坏处，它能够消除歧义。

下面，假设我们要查询由 DLL01、FNG01 或 BRS01制造的商品。
SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    vend_id = 'DLL01' 
    OR vend_id = 'FNG01' 
    OR vend_id = 'BRS01';

像上面这种查询，我们还有一种比较简便的写法：
******IN('DLL01','FNG01','BRS01')    和  or写法类似
SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    vend_id IN('DLL01','FNG01','BRS01');

IN 的用法同样适用于数值型字段，例如：

SELECT 
    prod_id,
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    prod_price IN (3.49, 11.99, 9.49);

IN操作符后面还可以跟上 SQL 语句，例如：
SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM
    products
WHERE
    prod_price IN(SELECT DISTINCT 
        prod_price
    FROM
        product
    WHERE
        prod id = 'BR03' OR prod id ='BNG01');
IN 操作符的优点
    1.IN 操作符的语法更清楚，更直观。
    2.在与AND 或 OR组合使用时，求值顺序更容易管理。
    3.IN 操作符一般比一组OR 操作符执行的更快。 
    4.IN 最大的优点是可以包含其它SELECT语句

NOT 操作符，表示对后面的过滤条件取反；

例如：从 products 查询 vend_id 不是 DLL01 的所有数据

SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    NOT vend_id = 'DLL01';    // NOT 表示对后面的过滤条件取反

和下面的语句是等价的
SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    vend_id <> 'DLL01';    //<> 不等于 
在这种简单的条件语句中，NOT确实没有什么优势，但是当条件语句更复杂时，我们使用NOT可以非常方便地找出和条件不匹配的记录。

例如：从 products 查询 prod_price不是 3.49， 11.99， 9.49 三者任意一个的所有数据

SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    prod_price NOT IN (3.49, 11.99, 9.49);    //

LIKE 操作符

前面介绍的过滤条件中使用的值都是已知的，但有时候需要过滤的值不是已知的，例如，我们检索产品名以doll为结尾的产品。
这时候我们便用到 LIKE 操作符以及通配符。
SELECT
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    prod_name LIKE '%doll'; 

%为通配符，表示任何字符出现任意次数。通配符查询只能用于文本字段，非文本数据类型字段不能使用通配符搜索。

//通配符查询只能用于文本字段，非文本数据类型字段不能使用通配符搜索

%通配符可在搜索模式的任意位置使用。并且可以使用多个通配符。
例如：从 products 查找 prod_name 前缀含有 Fish 的数据

SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    prod_name LIKE 'Fish%'; 

从 products 查找 prod_name 中含有 teddy 的数据

SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    vend_id
FROM 
    products
WHERE 
    prod_name LIKE '%teddy%';

通配符不可以匹配NULL。
另外一个通配符是下划线_。下划线的用途与%是一样的，但下划线 _ 只匹配单个字符，而不是多个字符。
例：从 products 查询 prod_name 后缀为inch teddy bear且前面只有一个字符的数据。
SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    prod_id
FROM 
    products
WHERE 
    prod_name LIKE '_ inch teddy bear';
从 products 查询 prod_name 后缀为inch teddy bear且前面只有两个字符的数据。

SELECT 
    prod_id, 
    prod_price, 
    prod_name, 
    prod_id
FROM 
    products
WHERE 
    prod_name LIKE '__ inch teddy bear';
与%能匹配若干个字符不同，_总是刚好匹配一个字符。
通配符很好用，但是这种好用是有代价的，代价就是搜索要耗费更长的处理时间。
使用通配符的注意事项：
1.不要滥用通配符，如果其它操作符可以达到目的，就选用其它操作符
2.在确实需要使用通配符时，也不要把通配符放在搜索模式的开始处，把通配符置于开始处，搜索起来是最慢的。

在过滤数据时，我们还可以使用正则匹配。在 MySQL 里使用 REGEXP 关键字。

正则表达式

匹配开头：从 products 查询 prod_name 前缀为 Fish 的数据。
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^Fish';

匹配结尾：从 products 查询 prod_name 后缀为 doll的数据。
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP 'doll$';

SQL 语句查询 prod_name 以 1、2 或 3 开头的，运行结果如下：

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[123]';

SQL 语句查询 prod_name 以 1、2、3、4、5、6、7 或 8 开头的，运行结果如下：

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[1-8]';

SQL 语句查询 prod_name 不以 1、2 或 3 开头的，运行结果如下
SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[^123]';

SQL 语句查询 prod_name 不以 1、2、3、4、5、6、7 或 8 开头的，运行结果如下

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[^1-8]';
小结

            ----------->使用WHERE子句指定查询条件
过滤数据-----------------
            ----------->操作符------->=,>,<,>=,<=,<>,BETWEEN,AND,LINK等

第五章 函数

MySQL 内置了许多函数，包括数值函数、字符串函数以及日期函数。
数值函数
我们首先来看下常用的数值函数

1.ABS（x） -- 返回 x 的绝对值。

SELECT ABS(-1);-------->1
SELECT ABS(1);--------->1

2.CEIL（x） -- 返回大于或等于 x 的最小整数。

SELECT ceil(1.5);------>2
SELECT ceil(2); ------->2

3.FLOOR（x） -- 返回小于或等于 x 的最大整数。

SELECT FLOOR(1.5);----->1    
SELECT FLOOR(2);------->2

4.RAND（） -- 返回 0~1 之间的随机数。

SELECT RAND();

5.ROUND（） -- 函数用于数据的四舍五入，它有两种形式：

(1).ROUND（x， d）， x 指要处理的数，d 是指保留几位小数。
(2).ROUND（x）， 其实就是 ROUND（x，0）， 也就是默认 d 为 0。

SELECT ROUND(5.45, 1); --------->5.5;
SELECT ROUND(5.45);------------->5

6.TRUNCATE（x， y） -- 返回数据 x 保留到小数点后 y 位的值。

SELECT TRUNCATE(5.45, 1);----->5.4

7.POWER（x， y） -- 返回 x 的 y 次方。

SELECT POWER(3, 2);---->9

8.SQRT（x） -- 返回 x 的平方根。

SELECT SQRT(25);----->5;

9.MOD（x， y） -- 返回 x 除以 y 以后的余数。

SELECT MOD(8, 3);----->2;

字符串函数

1.TRIM（s） -- 去除字符串s首尾的空格。

    SELECT TRIM('   hello world!   ');--------------->hello world；

2.LTRIM（s） -- 去除字符串s首部的空格。

    SELECT LTRIM('   hello world!   ');--------------->hello world!
3.RTRIM（s） -- 去除字符串s尾部的空格。

    SELECT RTRIM('   hello world!   ');--------------->    hello world!

4.UPPER（s） -- 将字符串s大写
    SELECT UPPER('Hello World!');--------------->HELLO WORLD!

5.LOWER（s） -- 将字符串s小写
    SELECT LOWER('Hello World!');--------------->hello world!

6.LENGTH（s） -- 返回字符串s的长度

    SELECT LENGTH('Hello World!');--------------->12

7.LEFT（s， n） -- 返回字符串 s 的前n个字符

    SELECT LEFT('Hello World!', 5);--------------->Hello；
8.RIGHT（s， n） -- 返回字符串 s 的后n个字符

    SELECT RIGHT('Hello World!', 5);--------------->orld!

9.SUBSTRING（s， n） -- 返回从字符串中的第n个位置开始的字符串

    SELECT SUBSTRING('Hello World!', 5);--------------->o World!
10.SUBSTRING（s， n， len） -- 返回从字符串中的第n个位置开始长度为len的字符串。

    SELECT SUBSTRING('Hello World!', 5, 5);--------------->o Wor
11.CONCAT（s1， s2） -- 将字符串s1， s2等多个字符串合并为一个字符串

    SELECT CONCAT('Hello', ' World!');---------------> Hello World!

日期和时间函数
1.NOW（） -- 返回当前时间的日期和时间
    
    SELECT NOW();
2.CURDATE（） -- 返回当前时间的年月日

    SELECT CURDATE();
3.CURTIME（） -- 返回当前时间的时分秒

    SELECT CURTIME();
4.YEAR（d） -- 返回d中的年份值

    SELECT YEAR(NOW());
5.MONTH（d） -- 返回d中的月份值

    SELECT MONTH(NOW());
6.DAY（d） -- 返回d中的天值

    SELECT DAY(NOW());
7.HOUR（d） -- 返回d中的小时值

    SELECT HOUR(NOW());

8.MINUTE（d） -- 返回d中的分钟值

    SELECT MINUTE(NOW());
9.SECOND（d） -- 返回d中的秒值

    SELECT SECOND(NOW());
10.QUARTER（d） -- 返回日期d是第几季度

    SELECT QUARTER(NOW());
11.MONTHNAME（d） -- 返回d当中的月份名称

    SELECT MONTHNAME(NOW());
12.DAYNAME（d） -- 返回d是星期几

    SELECT DAYNAME(NOW());
13.DAYOFWEEK（d） -- 返回日期d是星期的第几天

    SELECT DAYOFWEEK(NOW());
14.DAYOFMONTH（d） -- 返回日期d是本月的第几天

    SELECT DAYOFMONTH(NOW());
15.DAYOFYEAR（d） -- 返回日期d是本年的第几天

    SELECT DAYOFYEAR(NOW());
16.DATE_ADD（d， INTERVAL n DAY） -- 计算日期d加上n天的日期

    SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY);
17.DATE_SUB（d， INTERVAL n DAY） -- 计算日期d减去n天的日期

    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY);
18.DATEDIFF（d1， d2） -- 计算日期d1和d2相差的天数

    SELECT DATEDIFF('2020-01-10', '2020-01-02');
19.DATE_FORMAT（d， format） -- 格式化日期

    SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
20.UNIX_TIMESTAMP（） -- 返回时间戳

    SELECT UNIX_TIMESTAMP(NOW());
21.FROM_UNIXTIME（） -- 将时间戳格式化为时间

    SELECT FROM_UNIXTIME(1595323516, '%Y-%m-%d %H:%m:%s');

DATE_FORMAT 和 FROM_UNIXTIME 的区别在于，DATE_FORMAT的第一个参数为日期，FROM_UNIXTIME的第一个参数为时间戳。

小结

第六章 数据处理

汇总数据函数

1.AVG() -- 求平均值

我们可以求某个字段所有行的平均值，例如：

SELECT 
    AVG(prod_price)
FROM 
    products;

运行结果如下：

我们可以给平均值起一个名字，例如：

SELECT 
    AVG(prod_price) avg_price
FROM 
    products;

运行结果如下：

我们还可以对符合条件的行求平均值，例如：

SELECT 
    AVG(prod_price)
FROM 
    products
WHERE 
    vend_id = 'BRS01';

运行结果如下：

这里有两点要说明下：1.AVG() 会忽略列值为NULL的行。2.AVG()只能用来确定特定数值列的平均值。

2.COUNT() -- 计数

我们使用COUNT()函数进行计数。

COUNT() 函数有两种使用方式：

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

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

例如：

SELECT 
    COUNT(*) 
FROM 
    customers;

运行结果如下：

SELECT 
    COUNT(cust_email)
FROM 
    customers; 

运行结果如下：

如果指定列名，则 COUNT()函数会忽略指定列的值为 NULL 的行，但如果COUNT()函数中用的是星号（*），则不忽略。

3.MAX() -- 求最大值

SELECT 
    MAX(prod_price)
FROM 
    products;

运行结果如下：

4.MIN() -- 求最小值

SELECT 
    MIN(prod_price)
FROM 
    products;

运行结果如下：

MAX(),MIN()函数会忽略值为NULL的行。

5.SUM() -- 求和

# 所有订单的金额之和。
SELECT 
    SUM(quantity * item_price)
FROM
    orderitems;

运行结果如下：

汇总函数用来汇总数据，这些函数都很高效，一般比自己在程序中计算要快得多，所以能用汇总函数解决的我们就不要自己在程序中计算。

分组

假设我们要求每个供应商的商品价格的平均值，如果一个供应商一个供应商的计算，显然过于麻烦。这时候我们可以使用GROUP BY子句。

SELECT 
    vend_id, 
    AVG(prod_price)
FROM 
    products
GROUP BY 
    vend_id;

运行结果如下：

在使用 GROUP BY 时，除计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中给出。GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

像对行进行过滤一样，我们也可以对分组进行过滤。与过滤行不同的是，过滤分组不能使用 WHERE 子句，因为 WHERE 子句没有分组的概念，我们可以使用另外一个子句 HAVING。WHERE 用来过滤行，HAVING 用来过滤分组。WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

例如：

#  每个供应商供应商品的平均价格。
SELECT 
    vend_id, 
    AVG(prod_price) AS avg_price
FROM 
    products
GROUP BY 
    vend_id;

运行结果如下：

使用 having 进行过滤

# 使用 having 进行过滤
SELECT 
    vend_id, 
    AVG(prod_price) AS avg_price
FROM 
    products
GROUP BY 
    vend_id
HAVING 
    avg_price > 5;

运行结果如下：

# 每个供应商的商品数
SELECT 
    vend_id, 
    count(*)
FROM 
    products
GROUP BY 
    vend_id;

运行结果如下：

每个供应商商品价格的最大值

# 每个供应商商品价格的最大值
SELECT 
    vend_id, 
    MAX(prod_price)
FROM 
    products
GROUP BY 
    vend_id;

运行结果如下：

每个供应商商品价格的最小值

# 每个供应商商品价格的最小值
SELECT 
    vend_id, 
    MIN(prod_price)
FROM 
    products
GROUP BY 
    vend_id;

运行结果如下：

每个订单的金额

# 每个订单的金额
SELECT 
    order_num,
    SUM(quantity * item_price)
FROM
    orderitems
GROUP BY order_num;

运行结果如下：

#SQL 子句的顺序 
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

小结

课后题

单选题.下列哪一个函数可以求平均值？

A. COUNT()

B. MAX()

C. AVG()

D. SUM()

返回

第九章 插入、更新、删除数据

本讲我们来讲一下如何向表中插入数据以及如何更新、删除表中的数据。

插入

INSERT 用来将行插入到数据库表中。插入有几种方式：

1. 插入完整的行。

1. 插入行的一部分。

1. 插入某些查询的结果。

INSERT 语法要求指定表名和插入到新表中的值。例如，我们将一个新顾客插入到 customers 表中。

插入前：

INSERT INTO Customers VALUES('1000000006', 'Toy Land', '123 Any Street', 
    'New York', 'NY', '11111', 'USA', 'Jordan', 'Jordan@gmail.com');

运行结果如下：

插入到新表中的值由 VALUES 子句给出，以这种方式插入数据时，我们必须为每一列提供一个值。而且各列必须以他们在表定义中出现的次序填充。虽然这种插入数据的语法很简单，但并不安全，应该尽量避免使用。因为上面的 SQL 语句高度依赖于表中列的定义次序。

比较安全的写法，如下:

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, 
    cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 
    'NY', '11111', 'USA', 'Jordan', 'Jordan@gmail.com');

运行结果如下：

在上述写法中，我们在表名后的括号里明确指定了列名，VALUES 中的第一个值对应于第一个指定列名,第二个值对应于第二个列名，如此等等。因为提供了列名，只要 VALUES 中的值和指定的列名能匹配上就行，不一定按照各列出现在表中的实际次序。这样做的好处是，即使表结构发生改变，这条 INSERT 语句仍然能正确工作。

由于明确指定了列名，所以我们可以随意指定列的顺序。

例如，我们也可以如下插入数据：

INSERT INTO Customers(cust_id, cust_contact, cust_email, cust_name, 
    cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('1000000006', 'Jordan', 'Jordan@gmail.com', 'Toy Land', 
    '123 Any Street', 'New York', 'NY', '11111','USA');

运行结果如下：

注意事项：

1.不要使用没有明确给出列的 INSERT 语句。给出列能使 SQL 代码继续发挥作用,即使表结构发生了变化。

2.不管使用哪种 INSERT 语法,VALUES 的数目都必须正确。如果不提供列名,则必须给每个表列提供一个值;如果提供列名,则必须给列出的每个列一个值。否则,就会产生一条错误消息,相应的行不能成功插入。

由于明确列出了列名，我们在进行插入数据时，可以省略某些列，这表示可以只给某些列提供值，给其他列不提供值。例如，我们可以如下插入数据：

INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA');

运行结果如下：

在这个例子中，我们省略了 cust_contact 和 cust_email 这两列。

如果表的定义允许，则可以在 INSERT 操作中省略某些列。省略的列必须满足以下某个条件：

1.该列定义为允许 NULL 值。

2.在表定义中给出默认值。这表示如果不给出值，将使用默认值。

如果表中不允许有 NULL 值或者默认值,这时却省略了表中的值, DBMS 就会产生错误消息,相应的行不能成功插入。

INSERT 还可以用来将查询出的数据插入到表中。就是所谓的 INSERT SELECT。它由一条 INSERT 语句和一条 SELECT 语句组成。

例如，未进行操作前, customersnew 表中的内容是空的：

我们从 customers 表中查询数据插入到 customersnew 表中。

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;

运行结果如下：

可以发现已经执行完成。

然后我们打开 customersnew 表, 可以发现数据已经插入成功：

在这个例子中，INSERT 和 SELECT 语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，DBMS 一点儿也不关心 SELECT 返回的列名。它使用的是列的位置，因此 SELECT 中的第一列(不管其列名)将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列，如此等等。

INSERT SELECT 中 SELECT 语句可以包含 WHERE 子句,以过滤插入的数据。

另外，有时候，我们需要把一个表的内容复制到另外一张表。这时候我们可以使用：

CREATE TABLE custcopy AS 
SELECT * FROM customers;

运行结果如下：

可以发现sql语句的执行与表内容的复制都已完成，其内容与 customers 中的内容一致

更新

下面我们来讲下如何更新数据：

更新表中的数据可以使用 UPDATE 语句，有两种使用 UPDATE 的方式：

1.更新表中的特定行。

2.更新表中的所有行。

使用 UPDATE 语句非常容易，基本的 UPDATE 语句由三部分组成：

1.要更新的表。

2.列名和它们的新值。

3.确定要更新哪些行的过滤条件。

举个简单的例子，例如我们要更新客户 1000000005 的电子邮箱地址，语句如下：

UPDATE customers 
SET cust_email = 'kim@thetoystore.com' 
WHERE cust_id = '1000000005';

执行结果如下：

UPDATE 语句以 WHERE 子句结束，它告诉 DBMS 更新哪一行。没有 WHERE 子句,DBMS 将会用这个电子邮件地址更新 Customers 表中的所有行, 这不是我们希望的。

更新多列的语法稍微不同，例如：

UPDATE Customers 
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com' 
WHERE cust_id = '1000000006';

执行结果如下：

在更新多个列时,只需要使用一条 SET 命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)。在此例子中,更新顾客 1000000006 的 cust_contact 和 cust_email 列。

要删除某个列的值，可设置它为 NULL。例如：

UPDATE Customers 
SET cust_email = NULL 
WHERE cust_id = '1000000005';

执行结果如下：

在使用 UPDATE 语句时，不要省略 WHERE 子句，因为稍不注意，就会更新表中所有的行。

删除

下面来讲下如何删除数据：

从一个表中删除数据,使用 DELETE 语句。有两种使用 DELETE 的方式:

1.从表中删除特定的行;

2.从表中删除所有行。

例如：从Customers 表中删除一行:

DELETE FROM Customers 
WHERE cust_id = '1000000006';

执行结果如下：

DELETE 不需要列名或通配符。DELETE 删除整行而不是删除列。要删除指定的列,请使用 UPDATE 语句。

如果想从表中删除所有行,不要使用 DELETE。可使用 TRUNCATE TABLE 语句,它完成相同的工作,而速度更快(因为不记录数据的变动)。

使用 UPDATE 和 DELETE 时所遵循的重要原则：

1.除非确实打算更新和删除每一行,否则绝对不要使用不带 WHERE 子句的 UPDATE 或 DELETE 语句。

2.在 UPDATE 或 DELETE 语句使用 WHERE 子句前,应该先用 SELECT 进行测试,保证它过滤的是正确的记录,以防编写的 WHERE 子句不正确。

小结

返回

第十章 创建和操纵表

SQL 不仅可以用来操纵表中的数据，还可以对表本身进行操纵，包括表的创建、更改和删除。

创建表

一般有两种创建表的方法：

1. 用可视化的管理数据库表的工具来创建。

1. 直接用 SQL 语句来创建。

其实，在用工具创建的时候，本质上也是通过 SQL 语句来创建，只不过这些 SQL 语句是工具帮我们生成的。

创建表使用 CREATE TABLE 关键字，创建过程中，给出如下信息：

1. 新表的名字，在关键字 CREATE TABLE 之后给出。

1. 表列的名字和定义，用逗号分隔。

我们以 customers_test 表的创建为例，创建语句如下：

CREATE TABLE customers_test
(
  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 
);

运行结果如下：

在创建新的表时，指定的表名必须不存在，否则会出错。为防止意外覆盖已有的表，SQL 要求首先手工删除该表，然后再重建它，而不是简单的用创建表语句覆盖它。

每个表列要么是 NULL 列，要么是 NOT NULL 列。当我们不明确指定 NULL 或 NOT NULL 时，默认为 NULL。

SQL 允许指定默认值，在插入行时如果不给出值，DBMS 将自动采用默认值。默认值在 CREATE TABLE 语句的列定义中用关键字 DEFAULT 指定。

例如：

CREATE TABLE orderitems_test
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1,
  item_price decimal(8,2) NOT NULL 
);

运行结果如下：

在这个例子中,这一列的描述增加了 DEFAULT 1,指示 DBMS 如果不给出数量则使用数量 1。

默认值经常用于日期或时间戳列。例如,通过指定引用系统日期的函数或变量, 将系统日期用作默认日期。

在前面的课程我们提到，NULL 值就是没有值或缺值。允许 NULL 值的列允许在插入行时不给出该列的值。当指定列为 NOT NULL 时，如果有设定默认值，在插入行时，允许不给出该列的值。当指定列为 NOT NULL 时，如果没有设定默认值，在插入或更新行时，该列必须有值。如果不给出值，在插入的时候会报错。

在选择使用 DEFAULT 值还是 NULL 时，我们更倾向使用 DEFAULT 值。

更新表

在需要更新表定义时，我们可以使用 ALTER TABLE 语句。

使用 ALTER TABLE 更改表结构，必须给出下面的信息:

1. 在 ALTER TABLE 之后给出要更改的表名（该表必须存在，否则将出错）;

1. 列出要做哪些更改。

例如，我们要给 vendors 表增加一个 vend_phone 列。

ALTER TABLE Vendors 
ADD vend_phone CHAR(20);

执行前：

运行结果如下：

删除刚才增加的列：

ALTER TABLE Vendors 
DROP COLUMN vend_phone;

运行结果如下：

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

1. 用新的列布局创建一个新表；

1. 使用 INSERT SELECT 语句从旧表复制数据到新表。

1. 检验包含所需数据的新表；

1. 重命名旧表(如果确定，可以删除它)；

1. 用旧表原来的名字重命名新表；

1. 根据需要，重新创建触发器、存储过程、索引和外键。

使用 ALTER TABLE 要极为小心，应该在进行改动前做完整的备份(表结构和数据的备份)。数据库表的更改不能撤销，如果增加了不需要的列，也许无法删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

删除表

删除表(删除整个表而不是其内容)非常简单,使用 DROP TABLE 语句即可:

例如：删除 custcopy 表

DROP TABLE CustCopy;

运行结果如下：

可以看到左侧区域中的 custcopy 表已经被删除。

请注意：删除表没有确认，也不能撤销，执行这条语句将永远删除该表。

这一课介绍了几条新的 SQL 语句。CREATE TABLE 用来创建新表，ALTER TABLE 用来更改表列(或其他诸如约束或索引等对象)，而 DROP TABLE 用来完整地删除一个表。这些语句必须小心使用，并且应该在备份后使用。

小结

课后题

编程题：我们要给 customers 表增加一个 customers_phone 列。