首页 > 代码库 > 数据的基本查询

数据的基本查询

1. 准备数据

1.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);

1.2. 查询语句基础

语法:

SELECT

[DISTINCT]

Select_list

FROM table_name

[where_clause]

[group_by_clause]

[having condition]

[order_by_clause]

1.2.1. 查询关键字的顺序及功能

顺序序号

子句关键字

子句功能

1

SELECT

从指定表中取出指定列的数据

2

FROM

指定要查询要操作的表

3

WHERE

用来规定一种选择查询的标准

4

GROUP BY

对结果集进行分组,通常与聚合函数一起使用

5

HAVING

返回选取的结果集中行的数目

6

ORDER BY

指定分组的搜寻条件

1.3. 查询表中列的数据

SELECT * FROM BOOKINFO;

SELECT BOOKID,BOOKNAME,PUBLISH,PUBDATE FROM BOOKINFO;

--指定别名

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

1.4. 去除查询结果中的重复记录

语法:

SELECT DISTINCT select_list

FROM table_name;

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

1.4.1. distinct对NULL值得处理

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

SELECT DISTINCT(READER)FROM BOOKINFO;

1.5. 返回查询的部分数据(ROWNUM)

查询BOOKINFO表中前2行记录

SELECT BOOKNAME,PUBLISH,READER

FROM BOOKINFO

WHERE ROWNUM <3;

1.6. 单一条件查询

语法:

SELECT 列名1,列名2……

FROM 表名

WHERE 条件;

SELECT BOOKNAME,PUBLISH

FROM BOOKINFO

WHERE PUBLISH=‘科学出版社‘;

1.6.1. 查询中使用比较表达式

比较表达式

作用

=(等于)

 

<(小于)

 

>(大于)

 

<>(不小于)

 

<=(小于等于)

 

>=(大于等于)

 

!=(不等于)

 

1.6.2. 比较运算符

SELECT BOOKNAME,PUBLISH,PRICE

FROM BOOKINFO

WHERE PRICE>50;

1.6.3. 使用比较运算符比较字符

SELECT BOOKNAME,PUBLISH,AUTHOR

FROM BOOKINFO

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

1.7. 使用简单逻辑表达式

1.7.1. AND运算符

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

SELECT BOOKNAME,PUBLISH,PUBDATE,STORE

FROM BOOKINFO

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

1.7.2. OR运算符

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

SELECT BOOKNAME,PUBLISH,PRICE

FROM BOOKINFO

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

1.7.3. NOT运算符

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

SELECT BOOKNAME,PUBLISH

FROM BOOKINFO

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

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

FROM BOOKINFO

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

1.7.3.1. NOT运算符对NULL值得处理

--要求在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;

1.7.4. AND,OR,NOT的组合使用

当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;

1.8. 有关NULL值的判断

语法:

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

SELECT BOOKNAME,PUBLISH,PRICE,READER

FROM BOOKINFO

WHERE READER IS NOT NULL;

1.9.有关模糊查询

语法:

表达式 [NOT] LIKE 条件

LIKE子句结合通配符使用:

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

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

1.9.1. “_”通配符

SELECT BOOKNAME,PUBLISH,PRICE,READER

FROM BOOKINFO

WHERE BOOKNAME LIKE ‘通__理‘;

1.9.2. “%”通配符

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

SELECT BOOKNAME,PUBLISH,PRICE,READER

FROM BOOKINFO

WHERE PUBLISH LIKE ‘科学%‘;

1.10. 指定范围查询

语法:

表达式 [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‘);

1.11. 如何限制检索数据范围

语法:

表达式 [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;

1.12. 排序查询结果

1.12.1.基本排序

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;

1.12.2. 多列排序

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

SELECT BOOKNAME,PUBLISH,PRICE,READER,PUBDATE

FROM BOOKINFO

ORDER BY READER,PRICE ASC;

数据的基本查询