oracle-单表查询脚本

来源：互联网发布：毛少将知乎编辑：程序博客网时间：2024/06/05 09:29
-- 行列无限制查询 ， 输出的记录按照录入先后顺序，列按照建表的时候定义的先后顺序输出-- select *  代表输出所有的列，不做任何列输出限制SQL> select * from tbl_student;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                13     130.50002    david                                                               14     131.50003    tom                                                                 13 006    kent                                                                14     135.50009    jenny                                                               15 -- 不按天然顺序，自定义列输出顺序SQL> select stu_no,stu_age,stu_name,stu_height from tbl_student;STU_NO                                 STU_AGE STU_NAME                       STU_HEIGHT------ --------------------------------------- ------------------------------ ----------001                                         13 mary                               130.50002                                         14 david                              131.50003                                         13 tom                            006                                         14 kent                               135.50009                                         15 jenny                          -- 只输出部分列，对列输出做了限制SQL> select stu_no,stu_name from tbl_student;STU_NO STU_NAME------ ------------------------------001    mary002    david003    tom006    kent009    jenny-- 单条件行限制输出SQL> select * from tbl_student where stu_height>133;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------006    kent                                                                14     135.50SQL> select * from tbl_student where stu_height>131;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------002    david                                                               14     131.50006    kent                                                                14     135.50-- 复合条件行限制输出SQL> select * from tbl_student where stu_height>131 and stu_name='kent';STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------006    kent                                                                14     135.50SQL> select * from tbl_student where stu_height>131 or stu_name='kent';STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------002    david                                                               14     131.50006    kent                                                                14     135.50-- 行限制输出 (取非操作)SQL> select * from tbl_student where not (stu_height>131);STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                13     130.50-- 比较语句只要使用=,不能使用==SQL> select * from tbl_student where stu_height==null  2  ;select * from tbl_student where stu_height==nullORA-00936: missing expressionSQL> select * from tbl_student where stu_height=130.5;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                13     130.50-- null判断应用is/is not语句SQL> select * from tbl_student where stu_height=null;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------SQL> select * from tbl_student where stu_height is null;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------003    tom                                                                 13 009    jenny                                                               15 SQL> select * from tbl_student where stu_height is not null;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                13     130.50002    david                                                               14     131.50006    kent                                                                14     135.50-- 行列综合限制输出，多行书写，结构清晰SQL> select stu_no,stu_name  2  from tbl_student  3  where stu_height is null;STU_NO STU_NAME------ ------------------------------003    tom009    jenny-- 模糊查询， 名字中带aSQL> select *  2  from tbl_student  3  where stu_name like '%a%';STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                13     130.50002    david                                                               14     131.50-- 模糊查询，名字以m打头SQL> select *  2  from tbl_student  3  where stu_name like 'm%';STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------001    mary                                                                13     130.50SQL> select * from tbl_student where not(stu_age=13);STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------002    david                                                               14     131.50006    kent                                                                14     135.50009    jenny                                                               15 -- 使用<>完成不等于操作SQL> select * from tbl_student where stu_age<>13;STU_NO STU_NAME                                                       STU_AGE STU_HEIGHT------ ------------------------------ --------------------------------------- ----------002    david                                                               14     131.50006    kent                                                                14     135.50009    jenny                                                               15
0 0