一、数据库查询语句(单表查询篇)

一、数据库查询语句(单表查询篇)

单表查询：SELECT 语句仅从一个表/视图中检索数据，称单表查询。即其中的<普通表> 使用的是[<模式名>.]<基表名|视图名>。

---

该查询的语句所依赖的表是创建数据库实例默认勾选的两个实例：BOOKSHOP和DMHR

有关数据库实例安装的信息：

http://t.csdn.cn/aODlJ
1

1、简单查询

1.1 *和DINSTINCT

简介：为了方便用户提高工作效率，SQL 语言允许用户将 SELECT 后的<值表达式>省略为* ; *表示为全部列.

DISTINCT 保证重复的行将从结果中去除。若允许有重复的元组，改用 ALL 来 替换 DISTINCT，或直接去掉 DISTINCT 即可。

• 代码：

  #只要查询的表指定模式即可,其余列可以指定也可以不指定
  #1.查询production模式下的product表,名字列、作者列的信息
  select PRODUCT.NAME,PRODUCT.AUTHOR from PRODUCTION.PRODUCT;
  
  #2.查询production模式下的product表,名字列的信息
  select NAME from PRODUCTION.PRODUCT;
  
  /**
   
  */
  #3.查询production模式下的product表,所有列的信息
  select * FROM PRODUCTION.PRODUCT;
  
  -- 1.2查询结果去重(去除重复的数据)
  select DISTINCT NAME from PRODUCTION.PRODUCT;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

• 效果图：

---

1.2 MINUS、EXCEPT

简介：取左边部分，不包含交集

• 代码：

  -- 1.3使用minus、except查询tt表中有的,kk表中没有的数据
  create TABLE other.tt(num int);
  create TABLE other.kk(num int);
  insert into other.tt VALUES(1);
  insert into other.tt VALUES(2);
  insert into other.tt VALUES(3);
  insert into other.kk VALUES(2);
  insert into other.kk VALUES(3);
  insert into other.kk VALUES(4);
  
  select * FROM other.tt EXCEPT select * FROM other.kk;
  select * FROM other.tt MINUS select * FROM other.kk;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

• 效果图：

---

1.3 INTERSECT

简介：取交集

• 代码：

  -- 1.4使用intersect查看tt表和kk表中都有的数据
  select * FROM other.tt INTERSECT select * FROM other.kk;
  1
  2

• 效果图：

---

2、带条件查询

简介：带条件查询是指在指定表中查询出满足条件的元组，通过where子句和查询条件的为此和逻辑运算符组成 。

• 逻辑运算符有：AND，OR，NOT。
• 谓词包括比较谓词(＝、＞、＜、＞＝、＜＝、＜＞)，BETWEEN 谓词、IN 谓词、LIKE谓词、NULL 谓词、EXISTS 谓词

2.1 常用的逻辑运算符和谓词

简介：AND，OR，NOT，IN，NULL怎么使用

• 代码：

  -- 2.1给出当前销售价格在 10～20 元之间的所有图书的名字、作者、出版社和当前价格
  select name,author,publisher,nowprice from PRODUCTION.PRODUCT where nowprice >= 10 AND nowprice <=20;
  select name,author,publisher,nowprice from PRODUCTION.PRODUCT where nowprice between 10 AND 20;
  
  -- 2.2查询出版社为中华书局或人民文学出版社出版的图书名称与作者信息
  SELECT PRODUCT.NAME,PRODUCT.AUTHOR FROM PRODUCTION.PRODUCT WHERE PRODUCT.PUBLISHER IN ('中华书局','人民文学出版社');
  
  -- 2.3查询出版社不是中华书局或人民文学出版社出版的图书名称与作者信息
  SELECT PRODUCT.NAME,PRODUCT.AUTHOR FROM PRODUCTION.PRODUCT WHERE PRODUCT.PUBLISHER NOT IN ('中华书局','人民文学出版社');
  
  
  -- 2.6查询哪些人员的 EMAIL 地址为 NULL
  -- 使用 NULL 谓词的查询,空是使用is null/is not null不是使用等于号(=)
   select * from PERSON.PERSON WHERE email is null;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

• 效果图：

---

2.2 模糊查询和转义字符

简介：LIKE 谓词一般用来进行字符串的匹配，下划线(_)表示单个字符匹配,百分号(%)表示任意字符匹配， 模糊查询和转义符,如果我们的查询里面由%和但是不是表示模糊查询的呢?

• 代码：

  -- 2.4查询第一通讯地址中第四个字开始为“关山”且以 202结尾的地址
  -- 模糊查询like,下划线(_)表示单个字符匹配,百分号(%)表示任意字符匹配
  
  -- 3个下划线,百分号202
  select * from PERSON.ADDRESS where ADDRESS.ADDRESS1 like '___关山%202';
  
  
  -- 模糊查询和转义符,如果我们的查询里面由%和_但是不是表示模糊查询的呢?
  -- 查询person表中以 iu_520 结尾的地址，则 LIKE 谓词应为：此时这里的下划线不是代表模糊查询的匹配字符
  create TABLE OTHER.person(id int,name varchar(32));
  insert into OTHER.person VALUES(1,'江西省新余市渝水区城北大道iu_520');
  insert into OTHER.person VALUES(2,'广东省佛山市城北大道iu_520');
  commit;
  
  select * from other.PERSON where name like '%iu*_520' escape '*';
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15

• 效果图：

---

2.5 .ROW

简介：LIKE 谓词除支持使用列的计算外，还支持通过 ROW 保留字对表或视图进行 LIKE 计算。 该查询依次对表或视图中所有字符类型的列进行 LIKE 计算，只要有一列符合条件，则返回 TRUE

• 代码：

  -- 2.5查询出版社与中华有关的书籍信息
  -- 使用.ROW 进行 LIKE 谓词的查询
  select * from PRODUCTION.PRODUCT WHERE product.ROW like '%中华%';
  1
  2
  3

• 效果图：

---

2.7 组合逻辑

简介：可以用逻辑算符(AND，OR，NOT)与各种谓词相组合生成较复杂的条件查询

• 代码：

   --2.7查询当前销售价格低于 15 元且折扣低于 7 或出版社为人民文学出版社的图书名称和作者
   --可以用逻辑算符(AND，OR，NOT)与各种谓词相组合生成较复杂的条件查询
   select name,author from PRODUCTION.PRODUCT where nowprice < 15 and discount < 7 or publisher = '人民文学出版社';
  1
  2
  3

• 效果图：

---

3、集函数

简介：为了进一步方便用户的使用，增强查询能力，SQL 语言提供了多种内部集函数。集函数又称库函数，当根据某一限制条件从表中导出一组行集时，使用集函数可对该行集作统计操作，集函数可分为 10 类：

1. COUNT(*)；
2. 相异集函数 AVG|MAX|MIN|SUM|COUNT(DISTINCT<列名>)；
3. 完全集函数 AVG|MAX|MIN| COUNT|SUM([ALL]<值表达式>)；
4. 方差集函数 VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、STDDEV；
5. 协方差函数 COVAR_POP、COVAR_SAMP、CORR；
6. 首行函数 FIRST_VALUE；
7. 求区间范围内最大值集函数 AREA_MAX；
8. FIRST/LAST 集函数 AVG|MAX|MIN| COUNT|SUM([ALL] <值表达式>) KEEP(DENSE_RANK FIRST|LAST ORDER BY 子句)；ORDER BY 子句语法参考第 4.7 节；
9. 字符串集函数 LISTAGG/LISTAGG2；
10. 求中位数函数 MEDIAN。

集函数只能单独使用,只能返回一列,除非和group by使用

例子：

• 代码：

   -- 3.1查询折扣小于 7 的图书中现价最低的价格
   SELECT MIN(nowprice) FROM PRODUCTION.PRODUCT;
   -- 3.1错误示例，只能单独使用或者和group by联合使用
    SELECT name,MIN(nowprice) FROM PRODUCTION.PRODUCT;
   
   -- 3.2求折扣小于 7 的图书的平均现价
   SELECT AVG(nowprice) FROM PRODUCTION.PRODUCT WHERE DISCOUNT < 7;
   
   
   
   -- 3.3求折扣大于 8 的图书的总价格
   SELECT SUM(nowprice) FROM PRODUCTION.PRODUCT WHERE DISCOUNT > 8;
   
   
   
   -- 3.4查询图书的个数
   SELECT COUNT(*) FROM PRODUCTION.PRODUCT;
   SELECT DISTINCT COUNT(*) FROM PRODUCTION.PRODUCT;
   
   
   
   -- 3.5求图书的现价方差
   SELECT VARIANCE(NOWPRICE) FROM PRODUCTION.PRODUCT;
   
   
   
   -- 3.6求图书的现价标准差
   SELECT STDDEV(NOWPRICE) FROM PRODUCTION.PRODUCT;
   
   
   -- 3.7求图书的现价样本标准差
   SELECT STDDEV_SAMP(NOWPRICE) FROM PRODUCTION.PRODUCT;
   
   
   -- 3.8求产品原始价格 ORIGINALPRICE 和当头销售价格 NOWPRICE 的总体协方差
   SELECT COVAR_POP(ORIGINALPRICE, NOWPRICE) FROM PRODUCTION.PRODUCT; 
   
   
   -- 3.9求产品原始价格 ORIGINALPRICE 和当头销售价格 NOWPRICE 的样本协方差
   SELECT COVAR_SAMP(ORIGINALPRICE, NOWPRICE) FROM PRODUCTION.PRODUCT; 
   
   
   -- 3.10求产品原始价格 ORIGINALPRICE 和当头销售价格 NOWPRICE 的相关系数
   SELECT CORR(ORIGINALPRICE, NOWPRICE) FROM PRODUCTION.PRODUCT; 
   
   
   -- 3.11返回查询项的首行记录
   SELECT * FROM PRODUCTION.PRODUCT;
   SELECT FIRST_VALUE(NAME) FROM PRODUCTION.PRODUCT;
   
   
   -- 3.12求图书的现价在 20~30 之间的最大值
   SELECT area_max(NOWPRICE,20,30) FROM PRODUCTION.PRODUCT; 
   
   
   -- 3.13求每个用户最早定的商品中花费最多和最少的金额
   select CUSTOMERID, max(TOTAL) keep (dense_rank first order by ORDERDATE) max_val, min(TOTAL) keep (dense_rank first order by ORDERDATE) min_val from SALES.SALESORDER_HEADER group by CUSTOMERID;
   
   
   -- 3.14求出版的所有图书
   SELECT LISTAGG(NAME, ', ') WITHIN GROUP (ORDER BY NAME) LISTAGG FROM PRODUCTION.PRODUCT;
   
   
   -- 3.15求按照 type 分组之后，各组内 nowprice 的中位数
   SELECT MEDIAN(nowprice)FROM PRODUCTION.PRODUCT group by(type);
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41
  42
  43
  44
  45
  46
  47
  48
  49
  50
  51
  52
  53
  54
  55
  56
  57
  58
  59
  60
  61
  62
  63
  64
  65

• 效果图：

---

4、分析函数

简介：分析函数主要用于计算基于组的某种聚合值。如果不使用分析函数，则必须使用连接查询、子查询或者视图，甚至复杂的存储过程实现。DM分析函数为用户分析数据提供了一种更加简单高效的处理方式。引入分析函数后，只需要简单的 SQL 语句，并且执行效率方面也有大幅提高。 与集函数的主要区别是，分析函数对于每组返回多行，而集函数对于每个分组只返回一行。多行形成的组称为窗口，窗口决定了执行当前行的计算范围，窗口的大小可以由组中定义的行数或者范围值滑动.

分析函数可分为 11 类：

1. COUNT(*)；

2. 完全分析函数 AVG|MAX|MIN| COUNT|SUM([ALL]<值表达式>)，这 5个分析函数的参数和作为集函数时的参数一致；

3. 方差函数 VAR_POP、VAR_SAMP、VARIANCE、STDDEV_POP、STDDEV_SAMP、STDDEV；

4. 协方差函数 COVAR_POP、COVAR_SAMP、CORR；

5. 首尾函数 FIRST_VALUE、LAST_VALUE；

6. 相邻函数 LAG 和 LEAD；

7. 分组函数 NTILE；

8. 排序函数 RANK、DENSE_RANK、ROW_NUMBER；

9. 百分比函数 PERCENT_RANK、CUME_DIST、RATIO_TO_REPORT、PERCENTILE_CONT、NTH_VALUE；

10. 字符串函数 LISTAGG；

11. 指定行函数 NTH_VALUE。

例子：

• 代码：分析函数后跟over（partition by）分区和group by+集函数一致

   -- 4.1查询折扣大于 7的图书作者以及最大折扣
   /**
   如果使用的是集函数 MAX，那么得到的是所有图书中折扣的最大值，并不能查询出作者，
   使用了分析函数，就可以对作者进行分区，得到每个作者所写的图书中折扣最大的值。MIN 的含义和 MAX 类似
   */
   SELECT NAME,AUTHOR,DISCOUNT FROM PRODUCTION.PRODUCT;
   SELECT AUTHOR, MAX(DISCOUNT) OVER (PARTITION BY AUTHOR) AS MAX FROM PRODUCTION.PRODUCT WHERE DISCOUNT > 7;
   SELECT MAX(DISCOUNT) FROM PRODUCTION.PRODUCT;
  1
  2
  3
  4
  5
  6
  7
  8

• 图例：

• 效果图：

---

5、情况表达式

简介：指明一个条件值。将搜索条件作为输入并返回一个标量值，类似于if和else

例子：

• 代码：

   -- 5.1查询图书信息，如果当前销售价格大于 20 元，返回“昂贵”，如果当前销售价格小于等于 20 元，大于等于 10 元，返回“普通”，如果当前销售价格小于 10 元，返回“便宜
   select name,
   	CASE
   		WHEN nowprice > 20 THEN '昂贵'
   		WHEN nowprice > 10 and nowprice <= 20 THEN '普通'
   		else '便宜'
   	end AS 选择	
    from PRODUCTION.PRODUCT;
  1
  2
  3
  4
  5
  6
  7
  8

• 图例：

• 效果图：