拼接字段
select user_id || ' (' || XX_name || ')'
from XXXX
order by user_id;
--输出
--4114031824xxxxxx (XXXXXX中心)
select rtrim(user_id) || ' (' || rtrim(yf_name) || ')'
from dwd_zq_bss_loc_data_yyyymmdd;
--RTRIM()函数去掉值右边的所有空格
--LTRIM()函数去掉值左边的所有空格
--TRIM()函数去掉值左右边的所有空格
使用别名
列别名(alias)是一个字段或值的替换名
select rtrim(user_id) || ' (' || rtrim(yf_name) || ')' as "测试" from dwd_zq_bss_loc_data_yyyymmdd;
执行算数计算
SELECT product_id,
quantity,
item_price,
quantiy*item_price AS test
FROM OrderItems
WHERE order_num = 2008;
文本处理函数
LEFT()(或使用子字符串函数) 返回串左边的字符
LENGTH()(也是用DATALENGTH()或LEN()) 返回串的长度
LOWER() 将串转换成小写
SOUNDEX() 返回串的SOUNDEX值
UPPER() 将串转换成大写
select upper(xxxxx)
from xxxxxx ;
时间函数
select xxx1,xxx2,xxx3
from xxxx
where timeset = tochar(sysdate - 1, 'yyyymmdd');
AVG()函数
通过对表中行数计数并计算特定列值之和,求得该列的平均值。
select AVG(prod_price) AS avg_price
FROM Products;
只用于单个列,AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。为了获得多个列的平均值,必须使用多个AVG()函数。
COUNT()函数
COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
SELECT COUNT(*) AS num_cust
FROM Customers
MAX()函数
返回指定列中的最大值
SELECT MAX(prod_price) AS max_price
FROM Products;
SUM()函数
SUM()用来返回指定列值的和(总计)
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 2005;
组合聚集函数
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;
分组数据
GROUP BY
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GRUOP BY vend_id;
过滤分组
HAVING
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
HAVING和WHERE的差别
WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。
子查询
常用语WHERE字句中的IN操作符
例1:
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN('10004','10005');
把其中的WHERE字句转换为子查询而不是硬编码客户ID
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN(SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));
例2:作为计算字段使用子查询
需要显示Customers表中每个客户的订单总数。订单与相应的客户ID存储在Orders表中。
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS
orders
FROM Customers
ORDER BY cust_name;
联结表
使用WHERE字句创建联结(两个表)
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
联结多个表
SQL对一条SELECT语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表,然后定义表之间的关系。
例:
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;
此例子显示编号为20007的订单中的物品。订单物品存储在OrderItems表中。每个产品按其产品ID存储,它引用Products表中的产品。这些产品通过供应商ID联结到Vendors表中相应的供应商,供应商ID存储在每个产品的记录中。