;[@more@]
/*SELECT 语法*/
/*select 完整语法*/
SELECT [DISTINCT | UNIQUE] (*,column_name [AS my_name])
FROM tablename
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column_name];
/*select 语法细项*/
1. SELECT * FROM tablename;
2. SELECT column_name FROM tablename;
3. SELECT column_name1,column_name2,... column_nameN FROM tablename;
4. SELECT column_name [AS] my_name FROM tablename;
5. SELECT arithmetic expression FROM tablename;
6. SELECT DISTINCT column_name FROM tablename;
7. SELECT UNIQUE column_name FROM tablename;
8. SELECT column_name1 || column_name2 FROM tablename
/*选择查询*/
1.
1) WHERE ;
2) ORDER BY column_name1 DESC/ASC [NULLS LAST/ NULLS FIRST]，
column_name2 DESC/ASC [NULLS LAST/ NULLS FIRST]。。。
column_name255 DESC/ASC [NULLS LAST/ NULLS FIRST];
其中的column_name 不必在select出现在主句中，
但是，当主句中有distinct或者unique限制时，必须使用主句中的一些项目进行排序；
column_name 可以使用select主语句中已说明的别名；
column_name 最多可以有255个项目作为次排序使用，级别按顺序确定；
column_name 有时也可以在句子列表中引用这个字段。
DESC/ASC(降序排列/默认升序排列为值，字符，null值)
如果需要把null将值放在查询结果的顶部或底部，使用 NULLS FIRST 和 NULLS LAST 改变位置。
例子1：
/*客户名按其所在州呈降序排列，
由于城市没有制定排序模式，所以按照默认的升序排列*/
SQL> select lastname,firstname,city,state
2 from customers
3 where state = 'FL' or state = 'CA'
4 order by state desc ,city;
LASTNAME FIRSTNAME CITY STATE
---------- ---------- ------------ -----
NGUYEN NICHOLAS CLERMONT FL
MORALES BONITA EASTPOINT FL
SCHELL STEVE MIAMI FL
SMITH LEILA TALLAHASSEE FL
PEREZ JORGE BURBANK CA
DAUM MICHELL BURBANK CA
THOMPSON RYAN SANTA MONICA CA
7 rows selected
/*客户名按其所在州呈降序排列，
由于城市没有制定排序模式，所以按照默认的升序排列
city是第三个选项，state所以是第四个选项order by 4 desc,3*/
SQL> select lastname,firstname,city,state
2 from customers
3 where state = 'FL' or state = 'CA'
4 order by 4 desc,3;
LASTNAME FIRSTNAME CITY STATE
---------- ---------- ------------ -----
NGUYEN NICHOLAS CLERMONT FL
MORALES BONITA EASTPOINT FL
SCHELL STEVE MIAMI FL
SMITH LEILA TALLAHASSEE FL
PEREZ JORGE BURBANK CA
DAUM MICHELL BURBANK CA
THOMPSON RYAN SANTA MONICA CA
7 rows selected
例子2 /*使用nulls first 和 nulls last 在查询结果中改变 null值得位置*/
SQL> select lastname,firstname,referred
2 from customers
3 order by referred nulls first;
LASTNAME FIRSTNAME REFERRED
---------- ---------- --------
MORALES BONITA
THOMPSON RYAN
SMITH LEILA
PIERSON THOMAS
JONES KENNETH
LUCAS JAKE
MCKENZIE WILLIAM
LEE JASMINE
SCHELL STEVE
FALAH KENNETH
MONTIASA GREG
NELSON BECCA
MCGOVERN REESE
GIRARD CINDY
CRUZ MESHIA
GIANA TAMMY 1003
PEREZ JORGE 1003
SMITH JENNIFER 1003
NGUYEN NICHOLAS 1006
DAUM MICHELL 1010
20 rows selected
SQL> select lastname,firstname,referred
2 from customers
3 order by referred desc nulls last;
LASTNAME FIRSTNAME REFERRED
---------- ---------- --------
DAUM MICHELL 1010
NGUYEN NICHOLAS 1006
GIANA TAMMY 1003
SMITH JENNIFER 1003
PEREZ JORGE 1003
MORALES BONITA
THOMPSON RYAN
SMITH LEILA
LUCAS JAKE
LEE JASMINE
FALAH KENNETH
MONTIASA GREG
NELSON BECCA
SCHELL STEVE
MCKENZIE WILLIAM
MCGOVERN REESE
JONES KENNETH
PIERSON THOMAS
GIRARD CINDY
CRUZ MESHIA
20 rows selected
例子3：/*column_name 不必在select出现在主句中，
但是，当主句中有distinct或者unique限制时，必须使用主句中的一些项目进行排序*/
SQL> select distinct pubid
2 from books
3 order by cost;
ORA-01791: 不是 SELECTed 表达式
SQL> select pubid
2 from books
3 order by cost;
PUBID
-----
5
4
1
1
4
4
4
3
5
3
2
2
3
5
14 rows selected
2. 运算符
比较运算符： = ， < ， > ， <= ， >= ， 不等于：<> ， != ， ^=
[not]BETWEEN...AND :内包含
[not]IN ：列出所有结果在括号内
[not]LIKE ：使用通配符( %表示任意数量的字符 _ 表示一个字符 )可以混合使用
IS [not]NULL ：比较表项是否是空
逻辑运算符： AND ：两个都为TURE，结果才列出
OR ：有一个为TURE，结果就列出
NOT ：本身为FALSE，结果才列出
优先级： 首先是算术运算
然后是比较运算符(, =, like等) ：优先级相等，从左往右计算
最后是逻辑运算符：优先级逐次降低 NOT AND OR
例子： AND 的优先级高于 OR
SQL> select *
2 from books
3 where cost<18.75 or cost<14.20 and cost>10.00;
ISBN TITLE PUBDATE PUBID COST RETAIL CATEGORY
---------- ------------------------------ ----------- ----- ------- ------- ------------
0401140733 REVENGE OF MICKEY 2002-12-14 1 14.20 22.00 FAMILY LIFE
3437212490 COOKING WITH MUSHROOMS 2000-2-28 4 12.50 19.95 COOKING
8117949391 BIG BEAR AND LITTLE DOVE 2001-11-8 5 5.32 8.95 CHILDREN
0132149871 HOW TO GET FASTER PIZZA 2002-11-11 4 17.85 29.95 SELF HELP
9247381001 HOW TO MANAGE THE MANAGER 1999-5-9 1 15.40 31.95 BUSINESS
SQL> select *
2 from books
3 where (cost<18.75 or cost<14.20) and cost<10.00;
ISBN TITLE PUBDATE PUBID COST RETAIL CATEGORY
---------- ------------------------------ ----------- ----- ------- ------- ------------
8117949391 BIG BEAR AND LITTLE DOVE 2001-11-8 5 5.32 8.95 CHILDREN
/*日期规则*/
默认格式：yyyy-mm-dd
/*查看表结构*/
describe table_name;
/*特殊查询结果*/
1. 当任何一个select查询中，如果列名是随便一个'字符串'或者数字代替，
那么查询出的结果就是所用表的总列数，列名为该字符串或数字，各列内容为字符串内容或者该数字。
选多列的情况相同。
例子：
SQL> select 'book', 9 from books;
'BOOK' 9
------ ----------
book 9
book 9
book 9
book 9
book 9
book 9
book 9
book 9
book 9
book 9
book 9
book 9
book 9
book 9
14 rows selected
2. 希望查询出来的结果各列名为包含空格，特殊符号或者希望可以控制列名大小写时，必须把列别名用""引起来。
例子：SQL> select title "title", COST "retail-profit" FROM books;
title retail-profit
------------------------------ -------------
BODYBUILD IN 10 MINUTES A DAY 18.75
REVENGE OF MICKEY 14.20
BUILDING A CAR WITH TOOTHPICKS 37.80
DATABASE IMPLEMENTATION 31.40
COOKING WITH MUSHROOMS 12.50
HOLY GRAIL OF ORACLE 47.25
HANDCRANKED COMPUTERS 21.80
E-BUSINESS THE EASY WAY 37.90
PAINLESS CHILD-REARING 48.00
THE WOK WAY TO COOK 19.00
BIG BEAR AND LITTLE DOVE 5.32
HOW TO GET FASTER PIZZA 17.85
SHORTEST POEMS 21.85
HOW TO MANAGE THE MANAGER 15.40
14 rows selected
3. 插入一个换行符。用column1|| chr(10)||column2.
例子：SQL> select customer# || chr(10) || firstname || ' ' || lastname "customer infomation"
2 from customers;
customer infomation
--------------------------------------------------------------
1001
BONITA MORALES
1002
RYAN THOMPSON
1003
LEILA SMITH
1004
THOMAS PIERSON
1005
CINDY GIRARD
1006
MESHIA CRUZ
1007
TAMMY GIANA
1008
KENNETH JONES
1009
JORGE PEREZ
1010
JAKE LUCAS
1011
REESE MCGOVERN
1012
WILLIAM MCKENZIE
1013
NICHOLAS NGUYEN
1014
JASMINE LEE
1015
STEVE SCHELL
1016
MICHELL DAUM
1017
BECCA NELSON
1018
GREG MONTIASA
1019
JENNIFER SMITH
1020
KENNETH FALAH
20 rows selected
4. 当 ISBN VARCHAR2(10)
下列查询可以成立：
SQL> select *
2 from books
3 where isbn = 1915762492;