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) ---------- 25 SQL> SELECT COUNT(1) FROM CUSTOMER_TEST C CROSS JOIN ORDER_TEST O; 25 内连接(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都不包含在当前页中。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |