ORACLE学习笔记-查询篇

摘要：ORACLE学习笔记-查询篇

一、普通查询

/*查询表数据*/
select * from STU

[img]http://files.jb51.net/file_images/article/201408/2014081210241510.jpg[/img]

/*取出前3行数据*/
select * from stu where ROWNUM<=3

[img]http://files.jb51.net/file_images/article/201408/2014081210241611.jpg[/img]

/*模糊查询*/
select * from stu where stu_id like 'stu001%'

[img]http://files.jb51.net/file_images/article/201408/2014081210241612.jpg[/img] 说明：通配符“%”代表一个或者多个字符，通配符“_”代表一个字符。

/*别名*/
select STU_ID as 学号 from stu

[img]http://files.jb51.net/file_images/article/201408/2014081210241613.jpg[/img] 二、联合查询

/*普通联合查询*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU, KC WHERE STU.KC_NO = KC.KC_NO

[img]http://files.jb51.net/file_images/article/201408/2014081210241614.jpg[/img]

/*联合查询*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU INNER JOIN KC ON STU.KC_NO = KC.KC_NO ORDER BY STU.STU_ID

[img]http://files.jb51.net/file_images/article/201408/2014081210241615.jpg[/img]

/*左连接*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU LEFT JOIN KC ON STU.KC_NO = KC.KC_NO ORDER BY STU.STU_ID

[img]http://files.jb51.net/file_images/article/201408/2014081210241616.jpg[/img]

/*右连接*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU RIGHT JOIN KC ON STU.KC_NO = KC.KC_NO ORDER BY STU.STU_ID

[img]http://files.jb51.net/file_images/article/201408/2014081210241617.jpg[/img]

/*全连接*/
SELECT STU.STU_NAME, STU.STU_AGE, KC.KC_NAME FROM STU FULL JOIN KC ON STU.KC_NO = KC.KC_NO ORDER BY STU.STU_ID

[img]http://files.jb51.net/file_images/article/201408/2014081210241618.jpg[/img]

/*UNION关键字，联合两个表某些字段*/
SELECT STU_NAME FROM STU UNION SELECT KC_NAME FROM KC

[img]http://files.jb51.net/file_images/article/201408/2014081210241619.jpg[/img]