Oracle：数据查询语言-DQL-select

SELECT语句功能

投影操作：结果集是源表中的部分列。

选择操作：结果集是源表中的部分行。

连接操作：将两张表里的行按某种条件组合成一条长长的行放入结果集。

最基本的用法：SELECT ……FROM……

SELECT子句

用于指定字段名，多个字段名用逗号隔开，*代表所有列。

SELECT后面可以跟列，字符，表达式，DISTINCT关键字(去掉重复行)，函数，常量，组标识。完成对数据的处理。

FROM子句

用于指定表名。

例如：SELECT DISTINCT NAME FROM TEST;

WHERE子句

根据条件过滤出需要处理的数据，对行过滤。后面跟条件表达式(列名，常量，比较运算符，文字值，不能跟列别名)，可以用AND，OR连接多个条件表达式。

语法顺序：SELECT……FROM……WHERE……

例如：SELECT NAME FROM TEST WHERE ID = 10001;

ORDER BY子句

SELECT语句的最后一个子句，也是最后执行的子句，功能是改变记录的输出顺序，排序。

ASC(缺省/默认) 升序，DESC降序。

ORDER BY后面可以跟列名，表达式，列别名，列在结果集中的位置值。

ORDER BY 后面可以跟多列，用逗号隔开。

NULL值在ORDER BY 子句中排序的顺序是：降序排在最上，升序排在最下。

语法顺序：SELECT ——> FROM ——> WHERE ——> ORDER BY

执行顺序：FROM ——> WHERE ——> SELECT ——> ORDER BY

例如：SELECT * FROM TEST ORDER BY NAME DESC;

GROUP BY子句

功能是根据指定的列对行进行分组

语法顺序：SELECT……FROM……WHERE……GROUP BY……ORDER BY……

执行顺序：FROM……WHERE……GROUP BY……SELECT……ORDER BY……

例如：SELECT NAME FROM SERVICE GROUP BY NAME;

注意：

在SELECT语句中，如果没有GROUP BY子句，若在SELECT子句中有一个组函数，那么其他都必须是组函数，否则会报错。

在SELECT语句中，如果有GROUP BY子句，SELECT子句中可跟组函数，或GROUP BY后面的表达式、组标识，其他会报错。

GROUP BY中包含多列的情况，用逗号隔开，分组的粒度更细，每组的记录少了，但组多了。

行级信息和组级信息不可以同时显示出来。

HAVING 子句

功能是对组过滤，后面接条件表达式。

语法顺序：SELECT……FROM……WHERE……GROUP BY……HAVING……ORDER BY……

执行顺序：FROM……WHERE……GROUP BY……HAVING……SELECT……ORDER BY……

WHERE和HAVING 的区别：

1、WHERE过滤的是行(记录)

2、HAVING过滤的是分组(组标识，每组数据的聚合结果)

3、WHERE后面可以跟任意列名，单行函数，不能跟组函数

4、HAVING后面只能跟组函数、组标识、GROUP BY后面的表达式

5、WHERE子句执行在前，HAVING子句执行在后

6、WHERE子句和HAVING子句都不允许有列别名

设置结果集每页显示的记录数

SET PAGESIZE 记录数;

设置显示结果集时每行的长度

SET LINESIZE 长度;

设置当前会话的日期格式

ALTER SESSION SET NLS_DATE_FORMAT='格式';

例如：

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

把SELECT出来的结果导到一个文本文件中

SPOOL 路径\文件名.TXT;

SELECT * FROM 表名;

SPOOL OFF;

新建两个表，并插入一些数据：
CREATE TABLE CUSTOMER_TEST(ID VARCHAR2(48),CODE VARCHAR2(48),NAME VARCHAR2(58),TEL VARCHAR2(16),ADDRESS VARCHAR2(80),CREATETIME TIMESTAMP DEFAULT SYSDATE,REMARK VARCHAR2(80));

INSERT INTO CUSTOMER_TEST
  (ID,CODE,NAME,TEL,ADDRESS,CREATETIME,REMARK)
VALUES
  ('C000000000000000000001','C000000000000000000001','张三','15044447777','广东深圳宝安XXXX',TO_DATE('2015-01-01 09:30:29','yyyy-mm-dd hh24:mi:ss'),'ABCDEFG');

INSERT INTO CUSTOMER_TEST
  (ID,REMARK)
VALUES
  ('C000000000000000000002','C000000000000000000002','李四','15122336548',TO_DATE('2015-01-01 11:30:29',REMARK)
VALUES
  ('C000000000000000000003','C000000000000000000003','王五','13456892112',TO_DATE('2016-02-01 10:30:29',REMARK)
VALUES
  ('C000000000000000000004','C000000000000000000004','赵六','17766522233',TO_DATE('2016-02-10 09:30:29',REMARK)
VALUES
  ('C000000000000000000005','C000000000000000000005','刘七','13154546666','广东深圳宝安XXXXX',TO_DATE('2016-03-01 09:30:29','ABCDEFG');

COMMIT;

CREATE TABLE ORDER_TEST(ID VARCHAR2(48),CUSTOMERID VARCHAR2(48),COMMODITYIDS VARCHAR2(800),ORDERNUMBER VARCHAR2(48),ORDERTIME       TIMESTAMP DEFAULT SYSDATE,REMARK VARCHAR2(80),DELIVERYADDRESS VARCHAR2(80));

INSERT INTO ORDER_TEST
  (ID,CUSTOMERID,COMMODITYIDS,ORDERNUMBER,ORDERTIME,REMARK,DELIVERYADDRESS)
VALUES
  ('O000000000000000000001','C001,C002,C003,C004,C005','O000000000000000000001',TO_DATE('2016-01-01 17:09:50','不要错了','广东广州白云XXXXX');

INSERT INTO ORDER_TEST
  (ID,DELIVERYADDRESS)
VALUES
  ('O000000000000000000002','O000000000000000000002',TO_DATE('2016-03-10 17:09:50','广东广州白云XXXXX');
   
INSERT INTO ORDER_TEST
  (ID,DELIVERYADDRESS)
VALUES
  ('O000000000000000000003','O000000000000000000003',TO_DATE('2016-03-11 17:09:50','广东东莞虎门XXXXX');
   
INSERT INTO ORDER_TEST
  (ID,DELIVERYADDRESS)
VALUES
  ('O000000000000000000004','O000000000000000000004','广东东莞后街XXXXX');   
   
INSERT INTO ORDER_TEST
  (ID,DELIVERYADDRESS)
VALUES
  ('O000000000000000000005','O000000000000000000005',TO_DATE('2016-02-11 17:09:50','广东东莞后街XXXXX');   
   
COMMIT;

非关联子查询：在查询过程中内表不需要引用主表中的列

先执行子查询，子查询的结果作为条件，再执行主查询。子查询只执行一次。

查询在2016年2月有订单的客户：

sql> SELECT C.NAME

2 FROM CUSTOMER_TEST C

3 WHERE C.ID IN

4 (SELECT O.CUSTOMERID

5 FROM ORDER_TEST O

6 WHERE O.ORDERTIME >=

7 TO_DATE('2016-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')

8 AND O.ORDERTIME <=

9 TO_DATE('2016-02-29 23:59:59','yyyy-mm-dd hh24:mi:ss'));

NAME

-----------------

赵六

关联子查询：在子查询中引用主表中的列

3 WHERE EXISTS (SELECT 1

4 FROM ORDER_TEST O

5 WHERE O.ORDERTIME >=

6 TO_DATE('2016-02-01 00:00:00',courier; font-size: 14px;"> 7 AND O.ORDERTIME <=

8 TO_DATE('2016-02-29 23:59:59',courier; font-size: 14px;"> 9 AND C.ID = O.CUSTOMERID);

------------------

多列子查询：主查询的where条件中可以用多个列进行过滤(多列条件表达式)

查询收货地址相同，且购买商品相同的客户：

3 WHERE C.ID IN (SELECT O.CUSTOMERID

4 FROM ORDER_TEST O

5 WHERE (O.COMMODITYIDS,O.DELIVERYADDRESS) IN

6 (SELECT O.COMMODITYIDS,O.DELIVERYADDRESS

7 FROM ORDER_TEST O

8 GROUP BY O.COMMODITYIDS,courier; font-size: 14px;"> 9 HAVING COUNT(DISTINCT O.CUSTOMERID) > 1));

------------

王五

EXISTS：用于检查子查询是否至少会返回一行数据，实际上并不在乎子查询返回任何具体数据，而是在乎是否有值返回，有则是True否则为False。

3 WHERE EXISTS (SELECT 1

9 AND C.ID = O.CUSTOMERID);

---------------

NOT EXISTS：与exists相反。

查询在2016年2月没有订单的客户：

3 WHERE NOT EXISTS

4 (SELECT 1

10 AND C.ID = O.CUSTOMERID);

张三

李四

刘七

IN与EXISTS的比较

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用IN。

反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用EXISTS。

其实我们区分IN和EXISTS主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是EXISTS，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了。

表连接查询

交叉连接(CROSS JOIN)

假设TABLE1中有M条记录，TABLE2中有N条记录，那么交叉连接的结果集为M*N条记录。该连接产生的结果集称为笛卡尔集。

sql> SELECT COUNT(1) FROM CUSTOMER_TEST C,ORDER_TEST O;

COUNT(1)

----------

sql> SELECT COUNT(1) FROM CUSTOMER_TEST C CROSS JOIN ORDER_TEST O;

内连接(INNER JOIN)

任何一张表里的记录一定要在另一张表中找到匹配的记录，否则不能出现在结果集中。

3 INNER JOIN ORDER_TEST O

4 ON C.ID = O.CUSTOMERID

5 WHERE O.ORDERTIME >=

6 TO_DATE('2016-02-01 00:00:00',courier; font-size: 14px;"> 7 AND O.ORDERTIME <=

8 TO_DATE('2016-02-29 23:59:59','yyyy-mm-dd hh24:mi:ss');

-------------

2 FROM CUSTOMER_TEST C,ORDER_TEST O

3 WHERE C.ID = O.CUSTOMERID

4 AND O.ORDERTIME >=

5 TO_DATE('2016-02-01 00:00:00',courier; font-size: 14px;"> 6 AND O.ORDERTIME <=

7 TO_DATE('2016-02-29 23:59:59',courier; font-size: 14px;">--------------

外连接(LEFT JOIN,RIGHT JOIN,FULL JOIN)

左连接LEFT OUTER JOIN

查询每个用户单号：

sql> SELECT C.NAME,O.ORDERNUMBER

3 LEFT JOIN ORDER_TEST O

5 ORDER BY C.NAME;

NAME ORDERNUMBER

----------------------- ------------------------------------------

刘七

张三 O000000000000000000001

张三 O000000000000000000002

李四 O000000000000000000003

王五 O000000000000000000004

赵六 O000000000000000000005

RIGHT OUTER JOIN

sql> SELECT O.ORDERNUMBER,C.NAME

2 FROM ORDER_TEST O

3 RIGHT JOIN CUSTOMER_TEST C

ORDERNUMBER NAME

--------------------------------- -------------

刘七

O000000000000000000001 张三

O000000000000000000002 张三

O000000000000000000003 李四

O000000000000000000004 王五

O000000000000000000005 赵六

FULL OUTER JOIN

FROM后面跟子查询

在表连接过程中，有时候需要先对其中一个表先进行数据过滤在连接，这就是在FROM后面跟子查询。

这种情况会先执行子查询，将子查询的结果集作为一个表与另一个表进行连接。

查询在2016年2月有订单的客户及其订单号：

2 FROM (SELECT CUSTOMERID,ORDERNUMBER

3 FROM ORDER_TEST

4 WHERE ORDERTIME >=

5 TO_DATE('2016-02-01 00:00:00',courier; font-size: 14px; color: #0000ff;"> 6 AND ORDERTIME <=

7 TO_DATE('2016-02-29 23:59:59','yyyy-mm-dd hh24:mi:ss')) O

8 JOIN CUSTOMER_TEST C

9 ON C.ID = O.CUSTOMERID;

NAME ORDERNUMBER

-------------- ---------------------------

赵六 O000000000000000000005

结果集运算

UNION/UNION ALL：结果集为两个查询结果的并集。UNION会进行对数据进行去重操作；UNION ALL直接将两个结果集合并在一起，不会去重。

INTERSECT：结果集为两个查询结果的交集。

MINUS：结果集属于第一个查询结果，但不属于第二个查询结果。即为第一个查询结果集减去与第二个查询结果集的交集。

集合运算中，要求两个SELECT语句中列的个数、数据类型是一样的(即同构的)。

分页查询

Oracle的分页利用ROWNUM

ROWNUM是一个伪列，对查询返回的行进行编号即行号，从1开始递增。ORACLE的ROWNUM值是在获得每行之后才赋予的。

sql> SELECT ROWNUM,C.NAME FROM CUSTOMER_TEST C;

ROWNUM NAME

---------- -------------------

1 张三

2 李四

3 王五

4 赵六

5 刘七

ROWNUM的产生：

假如有一条查询语句为SELECT XX,YY FROM TABLE WHERE ZZ > 20 AND ROWNUM < 10。

执行FROM TABLE，从中取出第一条记录，系统给这条记录进行ROWNUM编号为1；

执行WHERE子句，判断记录是否符合条件表达式，符合则留下，不符合就丢弃；

从TABLE中取出第二条记录，这条记录的ROWNUM编号，为前一条符合条件被留下的记录的ROWNUM + 1；

重复前面的步骤，直到不符合ROWNUM < 10为止。

例如：当前页码pageIndex为3，页容量pageSize为2，查询当前页的数据：

sql> SELECT ROW_.*

2 FROM (SELECT ROWNUM RN,C.NAME FROM CUSTOMER_TEST C WHERE ROWNUM <= 3 * 2) ROW_

3 WHERE ROW_.RN > (3 - 1) * 2;

RN NAME

---------- -----------------

5 刘七

分页查询开始的startRowNum = (pageIndex - 1) * pageSize; 结束endRowNum = pageIndex * pageSize + 1。startRowNum与endRowNum都不包含在当前页中。

原文链接：https://www.f2er.com/oracle/211667.html

Oracle：数据查询语言-DQL-select

猜你在找的Oracle相关文章