准备工作:建表
#使用数据库shop
use shop;
#创建表product
CREATE TABLE product
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER DEFAULT 0,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY (product_id));
#向表中插入数据
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2021-12-20'),
('0002', '打孔器', '办公用品', 500, 320, '2021-12-19'),
('0003', '运动T恤', '衣服', 4000, 2800, NULL),
('0004', '菜刀', '厨房用具', 3000, 2800, '2021-12-18'),
('0005', '高压锅', '厨房用具', 6800, 5000, '2021-12-17'),
('0006', '叉子', '厨房用具', 500, NULL, '2021-12-16'),
('0007', '擦菜板', '厨房用具', 880, 790, '2021-12-12'),
('0008', '圆珠笔', '办公用品', 100, NULL, '2021-12-11');
一.SELECT语句基础
通过SELECT语句查询并选取出必要数据的过程称为匹配查询或查询(query)
1.基本SELECT语句
基本SELECT语句包含了SELECT和FROM两个子句,SELECT子句中列举了希望从表中查询出的列的名称,FROM子句则指定了选取出数据的表的名称
SELECT <列名1,列名2,...,列名n>
FROM <表名>;
例:注意最后一个查询项product_name后面没有符号
SELECT product_id,product_name
FROM product;
2.使用WHERE语句从表中选取符合条件的数据
WHERE子句用来指定查询数据的条件,查询出只符合该条件的记录
SELECT <列名1,列名2,...,列名n>
FROM <表名>
WHERE <条件表达式>;
例子:
SELECT product_id,product_name
FROM product
WHERE product_type = '衣服';
3.相关法则
(1)星号*代表全部列
# 查询出全部列
SELECT *
FROM product;
(2)可以使用AS关键字为列设定别名,设定中文别名时需要使用双引号""括起来
SELECT product_id AS id,
product_name AS name,
purchase_price AS "进货单价"
FROM product;
(3)使用DISTINCT可以删除重复行。只是查询结果不重复,原表是没有变化的(没有删除)
# 使用DISTINCT删除product_type列中重复的数据
SELECT DISTINCT product_type
FROM product;
(4)SELECT子句和WHERE子句中可以使用运算表达式
SELECT product_id,
product_name AS name,
#SQL语句使用运算表达式
sale_price * 2 AS "sale_price * 2"
FROM product
#WHERE子句中使用运算表达式
WHERE sale_price - purchase_price >= 500;
(5)选取NULL的记录时,WHERE语句中是IS NULL;选取不是NULL的记录时,WHERE语句中是IS NOT NULL
SELECT product_id,product_name
FROM product
# 选取purchase_price是NULL的记录
#WHERE purchase_price IS NULL;
#选取purchase_price不是NULL的记录
WHERE purchase_price IS NOT NULL;
(6)表示"不是..."的含义,可用不等号<>,也可用NOT运算符(在查询条件中添加NOT运算符)
SELECT product_id,product_name,sale_price
FROM product
#在查询条件中使用不等号,并添加NOT运算符
WHERE NOT sale_price <> 500;
(7)希望同时使用多个查询条件时,可以使用AND(且,取交集)、OR(或,取并集)运算符。AND运算符优先于OR运算符,如想优先执行OR运算,可以使用括号优先处理
例:想查询商品种类为“办公用品”且登记日期是2021-12-11或2021-12-19的商品
SELECT product_name,product_type,regist_date
FROM product
WHERE product_type = '办公用品'
AND (regist_date = '2021-12-11'
OR regist_date = '2021-12-19');
(8)含有NULL时的真值
SQL之外的语言基本上只使用真和假这两种真值
SQL中还存在NULL,因为不知道这样一个值,所以真值的结果既不为真,也不为假,是除真假之外的第三种值——不确定。
三值逻辑下的AND真值表:
真 AND 不确定 = 不确定
不确定 AND 真 = 不确定
假 AND 不确定 = 假
不确定 AND 假 = 假
不确定 AND 不确定 = 不确定
三值逻辑下的OR真值表
真 OR 不确定 = 真
不确定 OR 真 = 真
假 OR 不确定 = 不确定
不确定 OR 假 = 不确定
不确定 OR 不确定 = 不确定
二.聚合查询
1.聚合函数
COUNT:计算表中的记录数(行数)
聚合函数会将NULL排除在外。但COUNT(*)例外,它不排除NULL,会得到包含NULL的数据行数,而COUNT(<列名>)会得到NULL之外的数据行数
# 计算全部数据的行数,共8行
SELECT COUNT(*)
FROM product;
# 计算regist_date不为NULL的数据的行数,共7行
SELECT COUNT(regist_date)
FROM product;
SUM:计算表中数值列中数据的合计值
# 计算销售单价和进货单价的合计值
SELECT SUM(sale_price),SUM(purchase_price)
FROM product;
AVG:计算表中数值列中数据的平均值
# 计算销售单价和进货单价的平均值
SELECT AVG(sale_price),AVG(purchase_price)
FROM product;
MAX:求出表中任意列中数据的最大值
MIN:求出表中任意列中数据的最小值
MAX 和 MIN也可用于非数值型数据
# 进货单价的最大值 和 登记日期的最小值
SELECT MAX(purchase_price),MIN(regist_date)
FROM product;
注:MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列
2.使用聚合函数删除重复值(原表不变):聚合函数的参数中使用DISTINCT
想要计算值的种类时,可以在COUNT函数的参数中使用DISTINCT。
#计算去除重复数据后的数据行数,结果为3,表示表中有3种不同的商品
SELECT COUNT(DISTINCT product_type)
FROM product;
在SUM函数中使用DISTINCT,遇到相同的数值时不会再加
SELECT SUM(sale_price),SUM(DISTINCT sale_price)
FROM product;
三.对表分组
1.GROUP BY语句
使用聚合函数会将整个表的数据进行统计处理,当想将数据按照某列来统计处理时,可以使用GROUP BY进行分组
在GROUP BY子句中指定的列称为聚合键或分组列
SELECT <列名1,列名2,...,列名n>
FROM <表名>
GROUP BY <列名1,列名2,...,列名n>;
例子:
使用GROUP BY语句按照商品种类统计数据行数
SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type;
不使用GROUP BY语句,就是统计全表数据
SELECT product_type,COUNT(*)
FROM product;
2.GROUP BY语句使用的一些说明
(1)当聚合键中包含NULL时,会将NULL作为一组特殊数据进行处理
SELECT purchase_price,COUNT(*)
FROM product
GROUP BY purchase_price;
(2)GROUP BY的书写位置
1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
其中前三项用于筛选数据,GROUP BY对筛选出的数据进行处理
SELECT purchase_price,COUNT(*)
FROM product
WHERE product_type = '衣服'
GROUP BY purchase_price;
四.为聚合结果指定条件
在GROUP BY后使用HAVING子句,对分组结果进行过滤。可以使用数字、聚合函数和GROUP BY中指定的列名(聚合键)
1.使用数字
找到分组后,数据行数为2的分组
SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type
HAVING COUNT(*) = 2;
2.使用聚合函数
找到分组后,销售额总和大于5000元的分组
SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type
HAVING SUM(SALE_PRICE) > 5000;
3.使用GROUP BY中指定的列名
找到分组后,商品类型为厨房用具的分组
SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type
HAVING product_type = '厨房用具';
注:因为product_name不包含在GROUP BY聚合键中,所以会报错
SELECT product_type,COUNT(*)
FROM product
GROUP BY product_type
HAVING product_name = '圆珠笔';
五.对查询结果进行排序
1.ORDER BY
SELECT <列名1,列名2,...,列名n>
FROM <表名>
ORDER BY <排序基准列1,排序基准列2,...,排序基准列n>;
(1)默认为升序排列,降序排列为DESC
# 默认按sale_price升序排列
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price;
# DESC,按sale_price降序排列
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price DESC;
(2)可以设置多个排序键
例:按sale_price升序排列。当sale_price相同时,按照product_id降序排序
SELECT product_id, product_name, sale_price, purchase_price
FROM product
ORDER BY sale_price, product_id DESC;
2.GROUP BY 子句中不能使用SELECT 子句中定义的别名,在 ORDER BY 子句中可以使用别名
因为SQL语句的执行顺序如下:
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
SELECT的执行顺序在 GROUP BY 子句之后,ORDER BY 子句之前。在ORDER BY中使用别名时,已经知道了SELECT设置的别名存在,但是在GROUP BY中使用别名时还不知道别名的存在
SELECT product_id AS ID,
product_name
FROM product
#ORDER BY子句中可以使用别名
ORDER BY ID DESC;
3.当用于排序的列名中含有NULL时,NULL会在开头或末尾进行汇总
NULL值被认为比任何非NULL值低。当升序排列ASC时,NULL出现在开头;当降序排列DESC时,NULL出现在结尾。如想指定存在NULL的行出现在首行或末行,需要特殊处理
例:建表
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(5),
date_login DATE,
PRIMARY KEY (id)
);
INSERT INTO user(name, date_login) VALUES
(NULL, '2017-03-12'),
('john', NULL),
('david', '2016-12-24'),
('zayne', '2017-03-02');
(1)需求: 将NULL值排在行末,同时将所有非NULL值按升序排列
对于数字/日期类型,在排序字段前加一个符号,降序
SELECT *
FROM user
ORDER BY - date_login DESC
对于字符型或者字符型数字,用ISNULL()函数或IS NULL比较运算符
SELECT *
FROM user
ORDER BY ISNULL(name), name ASC;
#ORDER BY name IS NULL, name ASC;
使用COALESCE函数。
SELECT *
FROM user
ORDER BY COALESCE(name,'zzzzz') ASC;
(2)需求:将NULL值排在行首,同时将所有非NULL值按降序排列
# 对于数字或者日期类型
SELECT *
FROM user
ORDER BY - date_login;
# 对于字符型或者字符型数字
SELECT *
FROM user
#ORDER BY name IS NOT NULL, name DESC;
ORDER BY !ISNULL(name), name DESC;
# 使用COALESCE函数
SELECT *
FROM user
ORDER BY COALESCE(name, 'zzzzz') DESC;
六.练习
1.编写一条SQL语句,从product表中选取出登记日期regist_date在2009年4月28日之后的商品,查询结果要包含product_name和regist_date两列
SELECT product_name,regist_date
FROM product
WHERE regist_date > '2009-04-28';
2.说出对product表执行如下3条SELECT语句时的返回结果
SELECT *
FROM product
WHERE purchase_price = NULL;
SELECT *
FROM product
WHERE purchase_price <> NULL;
SELECT *
FROM product
WHERE product_name > NULL;
由于NULL不能参与运算,所以没有查询的返回结果
3.写出两条执行结果如下的语句
语句1
SELECT product_name,sale_price,purchase_price
FROM product
WHERE product_name = 'T恤衫' OR product_name = '运动T恤' OR product_name = '高压锅';
语句2
SELECT product_name,sale_price,purchase_price
FROM product
WHERE product_type = '衣服' OR sale_price = '6800';
4.请写出一条SELECT语句,从product表中选取出满足“销售单价打九折之后利润高于 100
元的办公用品和厨房用具”条件的记录。查询结果要包括product_name列、product_type列以及销售单价打九折之后的利润(别名设定为profit)。
SELECT product_name,
product_type,
sale_price * 0.9 - purchase_price AS profit
FROM product
WHERE sale_price * 0.9 - purchase_price > 100
AND (product_type = '办公用品' OR product_type = '厨房用具');
5.请指出下述SELECT语句中所有的语法错误。
SELECT product_id, SUM(product_name)
FROM product
GROUP BY product_type
WHERE regist_date > '2021-12-12';
改正:
SELECT子句中聚合健product_type以外的列product_name使用聚合函数时,会报错
SELECT product_id, regist_date, MAX(product_type)
FROM product
GROUP BY product_type
HAVING regist_date > '2021-12-12';
6.编写一条SELECT语句,求出销售单价(sale_price列)合计值大于进货单价(purchase_price 列)合计值1.5倍的商品种类。执行结果如下所示
SELECT product_type,
SUM(sale_price) AS sum,
SUM(purchase_price) AS sum
FROM product
GROUP BY product_type
HAVING SUM(sale_price) > 1.5 * SUM(purchase_price);
7.根据下列执行结果,还原ORDER BY子句的内容
可以看到,是按日期降序,同时把日期NULL值放在最开始的行。日期相同的按sale_price升序
SELECT product_type,
product_name,
product_type,
sale_price,
purchase_price,
regist_date
FROM product
ORDER BY - regist_date, sale_price;