目录
一、基础查询
1. 基础查询语句
SELECT <列名1> AS <别名1>,
<列名2> AS <别名2>,……
FROM <表名>;
- 若选取的是所有的列,使用*替代列名;
- 别名可以选填
- 汉语使用双引号(")括起来
- DISTINCT删除重复行
- 注释:1行: "--"; 多行: "/* */"
2. 符合条件的查询语句
SELECT <列名1>,
<列名2>,……
FROM <表名>
WHERE <条件表达式>;
---查询日期大于2020.10.11的物料类型
SELECT DISTINCT materialtype
FROM material_table
WHERE saledate >= '2020-10-11';
二、运算符
1. 算术运算符
2. 比较运算符
注意事项:
- SELECT子句可以使用常数或表达式
- 字符串类型的数据原则上按照字典顺序进行排序,不是数字的大小顺序
- 选取空记录(IS NULL / IS NOT NULL)
---查找amount>=1000的materialcode和双倍的labelprice
SELECT materialcode,
labelprice*2 AS labelprice_double
FROM material_table
WHERE amount >= 1000; ---where子句中使用了表达式
SELECT *
FROM material_table
WHERE materialtype IS NOT NULL;
3. 逻辑运算符
- 通过括号改变优先级
- 含有NULL时的真值:结果既不为真也不为假
含NULL的不确定情况的真值情况:
总结:AND中只有假和不确定的结果为假,其他与不确定的组合都是不确定;(AND中只要有一个假一定为假)
OR中只有真和不确定的结果为真,其他与不确定的组合都是不确定(OR中只要有一个为真一定为真)
练习1
首先创建product表,并存入相应的值。
CREATE TABLE product
(product_id varchar(4) NOT NULL,
product_name varchar(225),
product_type varchar(225),
sale_price decimal(10,2),
purchase_price decimal(10,2),
regist_date DATE,
primary key(product_id))
insert into product values('0001','T恤衫','衣服', 1000, 500,'2009-09-20'),
('0002','打孔器','办公用品', 500, 320,'2009-09-11'),
('0003','运动T恤','衣服', 4000, 2800,NULL),
('0004','菜刀','厨房用具', 3000, 2800,'2009-09-20'),
('0005','高压锅','厨房用具', 6800, 5000,'2009-01-15'),
('0006','叉子','厨房用具', 500,NULL,'2009-09-20'),
('0007','擦菜板','厨房用具', 880, 790,'2008-04-28'),
('0008','圆珠笔','办公用品', 100, NULL,'2009-11-11');
1. 从product中选取"regist_date" 在2009年4月28日之后的商品,包含product_name和regist_date
SELECT product_name,
regist_date
FROM product
WHERE regist_date > '2009-04-28';
2. NULL值
NULL值要通过IS NULL 和 IS NOT NULL查找,如果存的是NULL字符串,可以通过“=”查询,但是需要加上单引号(')
SELECT *
FROM product
WHERE purchase_price <> NULL;
---查询不到记录
SELECT *
FROM product
WHERE purchase_price = NULL;
---查询不到记录
SELECT *
FROM product
WHERE purchase_price IS NULL;
----查询purchase_price 为空的记录
3. 查询saleprice比purchase_price高出500日元以上的商品
SELECT product_name,
sale_price,
purchase_price
FROM product
WHERE sale_price-purchase_price >= 500; ---常用
SELECT product_name,
sale_price,
purchase_price
FROM product
WHERE NOT sale_price-purchase_price < 500
4. 选取满足“sale_price九折后利润高于100日元的办公用品和厨房器具”,
profit = 0.9*sale_price - purchase_price
SELECT product_name,
product_type,
(0.9*sale_price-purchase_price) AS profit
FROM product
WHERE (0.9*sale_price-purchase_price) >= 100
AND (product_type = '办公用品' OR product_type = '厨房用具');
---通过括号实现两个产品选取,也可以使用product_type IN ('办公用品','厨房用具')
三、聚合查询
1. 聚合函数
- 聚合函数可以与DISTINCT结合使用删除重复值
- 聚合函数会将NULL排除在外,但COUNT(*)除外
SELECT COUNT(DISTINCT product_type)
FROM product;
四、聚合查询
1. 分组查询
SELECT <列名1>,<列名2>,……
FROM <表名>
GROUP BY <列名1>,<列名2>;
注意:
- 使用GROUP BY时,SELECT子句中除聚合函数,不能有GROUP BY不存在的列名
- GROUP BY 不能使用别名
- 书写顺序SELECT ---> FROM ---> WHERE ---> GROUP BY
五、查询结果排序
SELECT <列名1>,<列名2>,……
FROM <表名>
ORDER BY <排序基准列1>,<排序基准列2>,……;
- 默认升序,DESC为降序
- 当排序的列名中含NULL时,NULL会在开头或末尾进行汇总,如果是降序NULL会在后面,如果是升序NULL会在前面,可以使用ISNULL来调整顺序
- ORDER BY中列名可以使用别名( FROM ---> WHERE ---> GROUP BY ---> HAVING --->SELECT ---> ORDER BY)
练习2
1. 指出语句的语法错误
SELECT product_id,
SUM(product_name) ---product_name不是数值型
FROM product
GROUP BY product_type
---GROUP BY 包含有SELECT子句中没有的字段,使用product_id
WHERE regist_date > '2009-09-01';
---WHERE在GROUP BY 之前
修改后:
SELECT product_id,
SUM(purchase_price)
FROM product
WHERE regist_date > '2009-09-01'
GROUP BY product_type ;
2. 求sale_price合计值是purchase_price合计值1.5倍的商品种类
思路:商品种类的分组汇总,汇总结果进行筛选,需要使用GROUP BY 和HAVING
SELECT product_type,
SUM(sale_price),
SUM(purchase_price)
FROM product
GROUP BY product_type
HAVING SUM(sale_price) >= 1.5*SUM(purchase_price)
3. 将以下结果通过ORDER BY 实现
思路:观察数据可以发现regist_date是降序排列并且NULL在前,然后观察到相同日期的排序中sale_price按照升序排列。因为降序排列的NULL值会在末尾,因此我们通过ISNULL判断将NULL排在第一位,之后再依据观察到的规律进行排序。
实现:
SELECT *
FROM product
ORDER BY ISNULL(regist_date) DESC, regist_date DESC, sale_price ---将NULL排在前面
结果:
总结
今天的课程有三点需要注意:
1. GROUP BY 时字段别名不可使用;ORDER BY时字段别名可以使用
2. NULL的使用:查询NULL值需要使用 IS NULL (IS NOT NULL);聚合函数中NULL值(COUNT(*)中NULL值也算记录数);ORDER BY 中的NULL排序
3. HAVING 一般跟随着GROUP BY出现