| Basic Select Statements |
| Select All Columns and All Records in a Single Table or View | SELECT *
FROM <table_name>; |
| | SELECT *
FROM all_tables; |
| Select Named Columns | SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>; |
| | SELECT table_name, tablespace_name, num_rows
FROM all_tables; |
|
Create Table As (CTAS)
Note: Redo only created when in ARCHIVE LOG mode | CREATE TABLE <table_name> AS
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>; |
| | CREATE TABLE t AS
SELECT *
FROM all_tables;
SELECT * FROM t; |
|
SELECTs can go anywhere | SELECT DECODE((SELECT 'x' FROM DUAL), (SELECT 'x' FROMDUAL), (SELECT 'y' FROM DUAL)) AS RESULT
FROM (SELECT 'm' FROM DUAL)
WHERE (SELECT 1 FROM DUAL) = (SELECT 1 FROM DUAL)
AND (SELECT 2 FROM DUAL) BETWEEN (SELECT 1 FROM DUAL) AND(SELECT 3 FROM DUAL)
AND NVL((SELECT NULL FROM DUAL ), (SELECT 'z' FROM DUAL)) = (SELECT 'z' FROM DUAL)
ORDER BY (SELECT 1 FROM DUAL); |
| |
| Select Statement With SAMPLE Clause |
|
Sample Clause Returning 1% Of Records | SELECT *
FROM <table_name>
SAMPLE (percentage_of_rows); |
| | CREATE TABLE t AS
SELECT object_name
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
SELECT COUNT(*)
FROM t;
SELECT COUNT(*) * 0.1
FROM t;
SELECT *
FROM t
SAMPLE(1);
SELECT *
FROM t
SAMPLE(1);
SELECT *
FROM t
SAMPLE(1); |
| |
| Select Statement With WHERE Clause |
|
Sample Clause Returning 35% Of Records After Filtering With AWHERE Clause 使用sample(0.1)就是返回0.01%的数据，与MS SQL SERVER 的top是相同的 | SELECT *
FROM <table_name>
SAMPLE (3.5)
WHERE .... |
| | SELECT COUNT(*)
FROM t
WHERE object_name LIKE '%J%';
SELECT COUNT(*) * 0.35
FROM t
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%'; |
| |
| Select Statement With GROUP BY Clause |
|
Select with Group By Clause | SELECT <column_name>, <aggregating_operation>
FROM <table_name>
GROUP BY <column_name>; |
| | SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type; |
| |
| Select Statement With HAVING Clause |
|
Select With Having Clause | SELECT <column_name>, <aggregating_operation>
FROM <table_name>
GROUP BY <column_name>
HAVING <aggregating_op_result> <condition> <value>; |
| | SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type
HAVING COUNT(*) < 6;
SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type
HAVING COUNT(*) > 5; |
| |
| Scalar Select |
| Select In Select Clause | SELECT (
SELECT <single_value
FROM <table_name>
FROM <table_name>; |
| | SELECT (SELECT 1 FROM DUAL) FROM DUAL; |
| |
| Select Unique Values |
| Distinct | SELECT DISTINCT <column_name_list>
FROM <table_name>; |
| | SELECT DISTINCT object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; |
| Unique | SELECT UNIQUE <column_name_list>
FROM <table_name>; |
| | SELECT UNIQUE object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; |
| |
| Select Statement Using Functions |
|
Date Function Example | SELECT <date_function(<column_name>))
FROM <table_name>; |
| | desc all_objects
SELECT object_name, TO_DATE(timestamp, 'YYYY-MM-DD:HH24:MI:SS')
FROM all_objects
WHERE ROWNUM < 11; |
|
Numeric Function Example | SELECT <numeric_function(<column_name>))
FROM <table_name>; |
| | desc user_extents
SELECT SUM(bytes)/1024/1024 USED_MB
FROM user_extents;
SELECT segment_type, SUM(bytes)/1024/1024 USED_MB
FROM user_extents
GROUP BY segment_type; |
|
String Function Example | SELECT <string_function(<column_name>))
FROM <table_name>; |
| | desc all_objects
SELECT object_name, LOWER(object_name) LOWER_ONAME
FROM all_objects
WHERE ROWNUM < 11; |
| |
| Select For Update |
|
Lock Record(s) | SELECT <column_name_list)
FROM <table_name_list>
FOR UPDATE; |
| | CREATE TABLE parents (
pid NUMBER(10),
cash NUMBER(10,2));
CREATE TABLE children
(cid NUMBER(10),
fid NUMBER(10),
fin_level VARCHAR2(35));
DECLARE
CURSOR x_cur IS
SELECT pid
FROM parents;
x_rec x_cur%ROWTYPE;
x NUMBER(10,2) := 18000.64;
y NUMBER(10,2) := 100;
BEGIN
DELETE FROM parents;
DELETE FROM children;
FOR i IN 1..25
LOOP
INSERT INTO parents
VALUES (y, x);
x := x+1235.31;
y := y-1;
END LOOP;
y := 0;
OPEN x_cur;
LOOP
FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%NOTFOUND;
y := y+1;
INSERT INTO children (cid, fid)
VALUES (y, x_rec.pid);
y := y+1;
INSERT INTO children (cid, fid)
VALUES (y, x_rec.pid);
END LOOP;
CLOSE x_cur;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE cursor_loop3 IS
CURSOR x_cur IS
SELECT pid, cash
FROM parents
WHERE cash < 35000
FOR UPDATE;
BEGIN
FOR x_rec IN x_cur
LOOP
UPDATE parents
SET cash = FLOOR(cash)
WHERE CURRENT OF x_cur;
END LOOP;
COMMIT;
END cursor_loop3;
/ |
| FOR UPDATE with NOWAIT | See Deadlocks Demo page |
| FOR UPDATE with WAIT | See Deadlocks Demo page |
| FOR UPDATE with SKIP LOCKED | See Deadlocks Demo page |
| |
| Partition Select |
|
Select From Named Partition | SELECT DISTINCT <column_name_list>
FROM <table_name> PARTITION (<partition_name>); |
| | CREATE TABLE pt (
deptno NUMBER(10),
state VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION nw VALUES ('OR', 'WA'),
PARTITION sw VALUES ('AZ', 'CA', 'NM'));
INSERT INTO pt VALUES (1, 'WA');
INSERT INTO pt VALUES (1, 'OR');
INSERT INTO pt VALUES (1, 'CA');
SELECT COUNT(*) FROM pt;
SELECT COUNT(*) FROM pt PARTITION(nw);
SELECT COUNT(*) FROM pt PARTITION(sw); |
| |
| CASE Insensitive Select |
|
Select From Named Partition | SELECT DISTINCT <column_name_list>
FROM <table_name> PARTITION (<partition_name>); |
| | conn / as sysdba
GRANT select ON v_$nls_parameters TO uwclass;
SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE'%SORT%';
conn uwclass/uwclass
CREATE TABLE cis_test (
col1 VARCHAR2(10));
INSERT INTO cis_test VALUES ('one');
INSERT INTO cis_test VALUES ('TWO');
INSERT INTO cis_test VALUES ('thRee');
INSERT INTO cis_test VALUES ('FouR');
INSERT INTO cis_test VALUES ('fiVE');
SELECT * FROM cis_test;
SELECT col1 FROM cis_test ORDER BY 1;
ALTER SESSION SET nls_sort=binary_ci;
SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE'%SORT%';
SELECT col1 FROM cis_test ORDER BY 1; |
| |
| PL/SQL Select Into |
|
Selecting In PL/SQL Objects | SELECT <clause>
INTO <clause>
FROM <clause>
WHERE <clause> |
| | CREATE TABLE t (
testcol NUMBER(3));
CREATE SEQUENCE seq;
SELECT seq.NEXTVAL FROM DUAL;
/
INSERT INTO t
(testcol)
VALUES
(seq.NEXTVAL);
/
SELECT * FROM t;
BEGIN
SELECT seq.NEXTVAL FROM DUAL;
END;
/
set serveroutput on
DECLARE
x INTEGER;
BEGIN
SELECT seq.NEXTVAL
INTO x
FROM DUAL;
dbms_output.put_line(x);
END;
/ |
646
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
