oracle简单学习总结（二）——查询

一、建表

CREATE TABLE userInfor ( id int NOT NULL primary key, name varchar(50) NULL,birthday dateNOT NULL ,age int NOT NULL,sex int NOT NULL );

二、查询

1、查询指定表的所有数据

sql: SELECT * FROM userInfor ;

说明： 查询usertable中所有的数据信息

2、查询指定字段的数据

sql: select name ,birthday fromuserInfor ;

说明： 结果只包含2列 name和birthday。在语句中给出要显示的列名，列名之间用“，”分隔。表头的显示默认为全部大写。

3、 显示行号

sql: SELECT * ,ROWNUM FROM userInfor where id>3;

说明： 每个表都有一个虚列ROWNUM，它用来显示结果中记录的行号

问题： rowid 与 ROWNUM 区别

答： rowid和rownum都是虚列。rowid是物理地址，用于定位oracle中具体数据的物理存储位置，而rownum则是sql的输出结果排序；不能对rownum用">" ，这也就意味着，如果你想用 select * from emp where rownum > 5 则是失败的。同样道理，rownum如果单独用=,也只有在rownum=1时才有用。正确语句：select * from(select rownum no ,id,name from student) where no>2;

参考学习：http://tenn.iteye.com/blog/99339

4、显示计算列

在查询语句中可以有算术表达式，它将形成一个新列，用于显示计算的结果，通常称为计算列。表达式中可以包含列名、算术运算符和括号。括号用来改变运算的优先次序。

常用的算术运算符包括：
+：加法运算符。 -：减法运算符。 *：乘法运算符。 /：除法运算符。

sql:SELECT name,age,age*(10) age2 FROM userInfor ;

说明： age*(10) age2指明 增加一列计算出年龄的十倍，并把此列命名为age2（见5） ，它不是表中存在的列，而是计算产生的结果，称为计算列

5、使用别名

可以为表的列起一个别名，好处是，可以改变表头的显示。特别是对于计算列，可以为它起一个简单的列别名以代替计算表达式在表头的显示。

sql:SELECT name as 姓名,age userage,age*(10) "年龄（10倍）" FROM userInfor ;

说明： 在列名和别名之间要用AS分隔，如name 和它的别名“姓名”之间用AS隔开。AS也可以省略，如age 和它的别名“userage”之间用空格分割，用空格分割，要区别好前面为列名，后面是别名。别名如果含有空格或特殊字符或大小写敏感，需要使用双引号将它引起来。

6、连接运算符

sql:SELECT name 姓名,age 年龄,name||' IS '||age||' age' AS "用户年龄" FROM userInfor ;

说明： 连接运算符是双竖线“||”。通过连接运算可以将两个字符串连接在一起。

7、使用的关键字DISTINCT消除重复显示

sql:SELECT DISTINCT sex FROM userInfor ;

说明： 关键字DISTINCT消除重复显示

8、查询结果的排序

升序：asc

sql:SELECT * FROM userInfor ORDER BY sexASC ;

降序：desc

sql:SELECT * FROM userInfor ORDER BY sexDESC ;

多字段排序：

sql: SELECT * FROM userInfor ORDER BY sex desc , birthday asc ;

说明： ORDER BY 从句出现在SELECT语句的最后，后跟要排序的列。ASC表示升序排序，DESC表示降序排序。默认的排序顺序为升序。可以按多列进行排序，先按第一列，然后按第二列、第三列......。

9、简单条件查询

 

运算符	功 能	实 例
>,<	大于，小于	Select * from userInfor where age>10
>=.<=	大于等于，小于等于	Select * from userInfor where age>=10
=	等于	Select * from userInfor where age=10
!=,<>,^=	不等于	Select * fromuserInfor where age!=12

sql:SELECT * FROM userInfor where age>10 ;

说明： 在FROM从句后使用WHERE从句，在WHERE从句中给出限定的条件，因为限定条件是一个表达式，所以称为条件表达式。条件表达式中可以包含比较运算，表达式的值为真的记录将被显示。字符串和日期型数据的值是包含在单引号中的。字符的值对大小写敏感

10、复合条件查询 （NOT，AND，OR ）

 

运算符	说 明	实 例
AND	逻辑与，表示两个条件必须同时满足	Select t.* from userInfor t where t.age>2 and t.birthday=to_date('1986-02-08','yyyy:MM:dd');
OR	逻辑或，表示两个条件中有一个条件满足即可	Select t.* from userInfor t where t.age>2 or t.birthday=to_date('1986-02-08','yyyy:MM:dd');
NOT	逻辑非，返回与某条件相反的结果	Select t.* from userInfor t where not t.birthday=to_date('1986-02-08','yyyy:MM:dd');

sql:Select t.* from userInfor t where t.age>2 and not t.birthday=to_date('1986-02-08','yyyy:MM:dd');

说明： 运算的优先顺序是NOT，AND，OR 。如果要改变优先顺序，可以使用括号。

11、特殊运算符

 

运 算	功 能	实 例
[NOT] BETWEEN…AND…	用于测试是否在范围内	Select * from userInfor Where age between 10 and 12
[NOT] IN (…)	用于测试是否在列表中	Select * from userInfor Where age in(1,10, 12 );
[NOT] LIKE	用于进行模式匹配	Select * from userInfor Where name like '张%'
IS [NOT] NULL	用于测试是否为空值	Select * from userInfor Where name is not null
ANY ，SOME	同列表或查询中的每一个值进行比较，测试是否有一个满足，前面必须使用的 运算符包括=、!=、>=、<=、>、<等	Select * from userInfor Where age>any(select age from userInfor where name='张三')
ALL	同列表或查询中的每一个值进行比较，测试是否所有的值都满足，前面必须使用的运算符包括=、!=、>=、<=、>、<等	Select*from userInforWhere age<all(26,12,10)
[NOT] EXISTS	测试是否子查询至少返回一行	Select name 用户,age from userInfor where exists(select age from userInfor where birthday>to_date('1986-02-08','yyyy:MM:dd'));

说明： 1、BETWEEN：对于数值型或日期型数据，表示范围时用。下限在前，上限在后，不能颠倒。查询范围中包含上下限的值

2、LIKE：完成按通配符查找字符串的查询操作，该操作符适合于对数据进行模糊查询； %：代表0个或多个任意字符。_ ：代表一个任意字符。

问题1：any , some ,all 区别

解答：

* Some ：表示满足其中一个的意义，是用or串起来的比较从句。

sql: Select t.* from userInfor t Where t.age=some (select age from userInfor where sex=0);

 

理解： 先执行：select age from userInfor where sex=0

再执行：select t.* from userInfor t Where t.age=26or t.age=12;

* Any： 也表示满足其中一个的意义，也是用or串起来的比较从句，区别是any一般用在非“=”的比较关系中 ，这也很好理解，英文中的否定句中使用any肯定句中使用some，这一点是一样的。

sql: Select t.* from userInfor t Where t.age<any(select age from userInfor where sex=0);

 

理解： 先执行：select age from userInfor where sex=0

再执行：select t.* from userInfor t Where t.age<26or t.age<12;

* All： 表示满足其中所有的查询结果的含义，使用and串起来的比较从句。

sql:select t.* from userInfor t Where t.age<all(select age from userInfor where sex=0);

理解： 先执行：select age from userInfor where sex=0

再执行：select t.* from userInfor t Where t.age<26 and t.age<12;

问题二：in ， EXISTS的理解

解答：

*EXISTS

sql:Select name 用户,age from userInfor where exists(select age from userInfor where birthday>to_date('1986-02-08','yyyy:MM:dd'));

学习参考： http://www.blogjava.net/terry-zj/archive/2006/04/18/41662.html

http://www.360doc.com/content/09/1109/22/342503_8700654.shtml

http://chenshuai365-163-com.iteye.com/blog/1003247