1. 准备数据

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4002,‘photoshop设计基础‘,‘大众出版社‘,TO_DATE(‘2005-2-1‘,‘yyyy-mm-dd‘),46.50,‘刘晓静‘,‘1‘,9707,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4003,‘photoshop设计基础‘,‘大众出版社‘,TO_DATE(‘2005-2-1‘,‘yyyy-mm-dd‘),46.50,‘刘晓静‘,‘1‘,9707,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4004,‘photoshop设计基础‘,‘大众出版社‘,TO_DATE(‘2005-2-1‘,‘yyyy-mm-dd‘),46.50,‘刘晓静‘,‘0‘,null,null)

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4005,‘数字图形处理‘,‘科学出版社‘,TO_DATE(‘2002-8-1‘,‘yyyy-mm-dd‘),56.40,‘王刚,赵明‘,‘1‘,9702,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4006,‘数字图形处理‘,‘科学出版社‘,TO_DATE(‘2002-8-1‘,‘yyyy-mm-dd‘),56.40,‘王刚,赵明‘,‘0‘,null,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4007,‘多媒体基础‘,‘科学出版社‘,TO_DATE(‘2006-1-1‘,‘yyyy-mm-dd‘),43.70,‘赵明‘,‘1‘,9702,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4008,‘VC网络基础‘,‘青年出版社‘,TO_DATE(‘2004-3-1‘,‘yyyy-mm-dd‘),28.00,‘孙朋‘,‘0‘,null,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4009,‘VC网络基础‘,‘青年出版社‘,TO_DATE(‘2004-3-1‘,‘yyyy-mm-dd‘),28.00,‘孙朋‘,‘1‘,9703,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4010,‘数据库设计‘,‘大众出版社‘,TO_DATE(‘2007-9-1‘,‘yyyy-mm-dd‘),76.00,‘王六‘,‘1‘,9705,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4011,‘通讯原理‘,‘科学出版社‘,TO_DATE(‘2001-6-1‘,‘yyyy-mm-dd‘),45.50,‘佟健刚‘,‘1‘,9704,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4012,‘VC数据库程开发基础‘,‘科学出版社‘,TO_DATE(‘2006-4-1‘,‘yyyy-mm-dd‘),54.00,‘刘明,孙朋‘,‘0‘,null,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4013,‘VC数据库程开发基础‘,‘科学出版社‘,TO_DATE(‘2006-4-1‘,‘yyyy-mm-dd‘),54.00,‘刘明,孙朋‘,‘1‘,9701,null);

INSERT INTO BOOKINFO(BOOKID,BOOKNAME,PUBLISH,PUBDATE,PRICE,AUTHOR,STORE,READER,REMARKS)

VALUES(4014,‘数据库原理‘,‘科学出版社‘,TO_DATE(‘2005-1-1‘,‘yyyy-mm-dd‘),38.50,‘高义‘,‘0‘,null,null);

语法:

SELECT

[DISTINCT]

Select_list

FROM table_name

[where_clause]

[group_by_clause]

[having condition]

[order_by_clause]

SELECT * FROM BOOKINFO;

SELECT BOOKID,BOOKNAME,PUBLISH,PUBDATE FROM BOOKINFO;

--指定别名

SELECT BOOKID AS B_ID,BOOKNAME B_NAME,PUBLISH,PUBDATE FROM BOOKINFO;

语法：

SELECT DISTINCT select_list

FROM table_name;

SELECT DISTINCT(BOOKNAME),PUBLISH,PUBDATE FROM BOOKINFO;

如果表中有多个null的数据，服务器会把这些数据视为相等

SELECT DISTINCT(READER)FROM BOOKINFO;

查询BOOKINFO表中前2行记录

SELECT BOOKNAME,PUBLISH,READER

FROM BOOKINFO

WHERE ROWNUM <3;

语法：

SELECT 列名1,列名2……

FROM 表名

WHERE 条件;

SELECT BOOKNAME,PUBLISH

FROM BOOKINFO

WHERE PUBLISH=‘科学出版社‘;

比较表达式

作用

=（等于）

<（小于）

>（大于）

<>(不小于)

<=(小于等于)

>=(大于等于)

!=(不等于)

SELECT BOOKNAME,PUBLISH,PRICE

FROM BOOKINFO

WHERE PRICE>50;

SELECT BOOKNAME,PUBLISH,AUTHOR

FROM BOOKINFO

WHERE BOOKNAME<=‘多媒体基础‘;

AND运算符表示逻辑“与”的关系；当使用AND运算符组合两个逻辑表达式时，只有两个表达式均为TRUE时才返回TRUE;

SELECT BOOKNAME,PUBLISH,PUBDATE,STORE

FROM BOOKINFO

WHERE PUBLISH=‘科学出版社‘ AND STORE=‘1‘;

OR运算符实现逻辑“或”的运算关系，当使用OR运算符组合两个逻辑表达时，只要其中一个表达式的条件TRUE，结果便返回TRUE;

SELECT BOOKNAME,PUBLISH,PRICE

FROM BOOKINFO

WHERE PUBLISH=‘科学出版社‘ OR PRICE>50;

NOT运算符实现逻辑“非”的运算关系，用于对搜集条件的逻辑求反；

SELECT BOOKNAME,PUBLISH

FROM BOOKINFO

WHERE NOT PUBLISH=‘大众出版社‘; 

上面操作相当于下面操作:
SELECT BOOKNAME,PUBLISH

FROM BOOKINFO

WHERE  PUBLISH!=‘大众出版社‘; 

--要求在BOOKINFO表中查询编号不大于9704(包含没有读者)的所有图书记录；

--没有查出READER为NULL的数据，如果READER为NULL，READER>9704这个条件不成立；

SELECT BOOKNAME,PUBLISH,AUTHOR,STORE,READER

FROM BOOKINFO

WHERE NOT READER>9704;

--相当于

SELECT BOOKNAME,PUBLISH,AUTHOR,STORE,READER

FROM BOOKINFO

WHERE READER<=9704;

当AND和OR同时出现时，出现下面的运算关系:NOT优先级最高，而后是AND，最后是OR

Condition1 OR  Condition2 AND Condition3

Condition1 OR (Condition2 AND Condition3)

SELECT BOOKNAME,PUBLISH,AUTHOR,PRICE,STORE,READER

FROM BOOKINFO

WHERE PUBLISH=‘青年出版社‘ OR PUBLISH=‘科学出版社‘

AND NOT PRICE>50;

--相当于

SELECT BOOKNAME,PUBLISH,AUTHOR,PRICE,STORE,READER

FROM BOOKINFO

WHERE (PUBLISH=‘青年出版社‘ OR PUBLISH=‘科学出版社‘)

AND NOT PRICE>50;

语法:

判断一个值是否为NULL，采用IS [NOT] NULL判别式；

SELECT BOOKNAME,PUBLISH,PRICE,READER

FROM BOOKINFO

WHERE READER IS NOT NULL;

语法:

表达式 [NOT] LIKE 条件

LIKE子句结合通配符使用：

“_”可以替代一个字符；

“%”可以代替个数不确定的字符；

SELECT BOOKNAME,PUBLISH,PRICE,READER

FROM BOOKINFO

WHERE BOOKNAME LIKE ‘通__理‘;

“%”表示任意字符的匹配，且不计字符的多少；

SELECT BOOKNAME,PUBLISH,PRICE,READER

FROM BOOKINFO

WHERE PUBLISH LIKE ‘科学%‘;

语法：

表达式 [NOT] BETWEEN 表达式1 AND 表达式2

SELECT BOOKNAME,PUBLISH,PRICE,READER

FROM BOOKINFO

WHERE PRICE BETWEEN 35 AND 50;

--相当于

SELECT BOOKNAME,PUBLISH,PRICE,READER

FROM BOOKINFO

WHERE PRICE >=35 AND PRICE<=50;

--日期

SELECT BOOKNAME,PUBLISH,PRICE,READER,PUBDATE

FROM BOOKINFO

WHERE PUBDATE BETWEEN TO_DATE(‘2004-9-1‘,‘yyyy-mm-dd‘) AND  TO_DATE(‘2007-9-1‘,‘yyyy-mm-dd‘);

语法:

表达式 [NOT] IN (表达式1,表达式2… …);

SELECT BOOKNAME,PUBLISH,PRICE,READER,PUBDATE

FROM BOOKINFO

WHERE READER IN(9702,9705,9701);

--相当于

SELECT BOOKNAME,PUBLISH,PRICE,READER,PUBDATE

FROM BOOKINFO

WHERE READER=9702 OR READER=9701 OR READER=9705;

ORDER BY子句子句一定要放在所有子句的最后；

升序排序：ASC

降序排序：DESC

SELECT BOOKNAME,PUBLISH,PRICE,READER,PUBDATE

FROM BOOKINFO

WHERE READER IS NOT NULL

ORDER BY READER;

--使用NULLS LAST关键字将NULL值放到最后

SELECT BOOKNAME,PUBLISH,PRICE,READER,PUBDATE

FROM BOOKINFO

ORDER BY READER

NULLS LAST;

使用ORDER BY子句可以根据两列或多列的结果进行排序，只需要用逗号分隔不同的关键词即可；

SELECT BOOKNAME,PUBLISH,PRICE,READER,PUBDATE

FROM BOOKINFO

ORDER BY READER,PRICE ASC;