select 单表查询语法总结

最新推荐文章于 2022-03-16 09:48:45 发布

cuiang9442

最新推荐文章于 2022-03-16 09:48:45 发布

阅读量254

点赞数

;

[@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是第3个选项，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;

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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/198459/viewspace-907117/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/198459/viewspace-907117/

cuiang9442

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
select 单表查询语法总结

;[@more@]/*SELECT 语法*//*select 完整语法*/SELECT [DISTINCT | UNIQUE] (*, column_name [AS my_name])FROM tablename[WHERE ...
复制链接

扫一扫