exists 和 select 1 from



EXISTS用于检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回值True或False

 

EXISTS


指定一个子查询,检测行的存在。

语法


EXISTS subquery

参数

subquery

是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。有关更多信息,请参见select中有关子查询的讨论。

结果类型


Boolean

结果值


如果子查询包含行,则返回 TRUE。


一种通俗的可以理解为:将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE,这一行行可作为外查询的结果行,否则不能作为结果。exists引导的子句有结果集返回,那么exists这个条件就算成立了,


http://www.cnblogs.com/fredlau/articles/993829.html

http://www.cnblogs.com/mytechblog/articles/2105785.html

http://blog.csdn.net/xiwu1616/article/details/14160855/


<span style="font-size:14px;"> Insert into DGDISTRIBUTOR (DGDISTRIBUTORID,ORGCODE,ORGNAME,USERID,DATELASTUPDATED) 
 select DGDISTRIBUTORseq.nextval,'52004096','丹东市第二人民医院',100005690,sysdate from DGDISTRIBUTOR
 where ROWNUM<=1 and not exists (select * from DGDISTRIBUTOR where orgcode = '52004096')</span>

1、select 1 from mytable;与select anycol(目的表集合中的任意一行) from mytable;与select * from mytable 作用上来说是没有差别的,都是查看是否有记录,一般是作条件用的。select 1 from 中的1是一常量,查到的所有行的值都是它,但从效率上来说,1>anycol>*,因为不用查字典表。

2、查看记录条数可以用select count(1) from mytable;等价于select count(*) from mytable;


select * from organization where 1=1 and exists(select 1 from organization where orgid >1999999)



  • 2
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
if exists(select 1 from sys.sysforeignkey where role='FK_CHOOSE_C_CHOOSE_CO_COURSES') then alter table choose_course delete foreign key FK_CHOOSE_C_CHOOSE_CO_COURSES end if; if exists(select 1 from sys.sysforeignkey where role='FK_CHOOSE_C_CHOOSE_CO_STUDENT') then alter table choose_course delete foreign key FK_CHOOSE_C_CHOOSE_CO_STUDENT end if; if exists(select 1 from sys.sysforeignkey where role='FK_STUDENT_HAVE_STUD_SCHOOL') then alter table student delete foreign key FK_STUDENT_HAVE_STUD_SCHOOL end if; if exists(select 1 from sys.sysforeignkey where role='FK_TEACH_TE_TEACH_TEA_COURSES') then alter table teach_teachers delete foreign key FK_TEACH_TE_TEACH_TEA_COURSES end if; if exists(select 1 from sys.sysforeignkey where role='FK_TEACH_TE_TEACH_TEA_TEACHER') then alter table teach_teachers delete foreign key FK_TEACH_TE_TEACH_TEA_TEACHER end if; if exists(select 1 from sys.sysforeignkey where role='FK_TEACHER_HAVE_TEAC_SCHOOL') then alter table teacher delete foreign key FK_TEACHER_HAVE_TEAC_SCHOOL end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='choose_course2_FK' and t.table_name='choose_course' ) then drop index choose_course.choose_course2_FK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='choose_course_FK' and t.table_name='choose_course' ) then drop index choose_course.choose_course_FK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='choose_course_PK' and t.table_name='choose_course' ) then drop index choose_course.choose_course_PK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='courses_PK' and t.table_name='courses' ) then drop index courses.courses_PK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='school_PK' and t.table_name='school' ) then drop index school.school_PK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='have_student_FK' and t.table_name='student' ) then drop index student.have_student_FK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='student_PK' and t.table_name='student' ) then drop index student.student_PK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='teach_teachers2_FK' and t.table_name='teach_teachers' ) then drop index teach_teachers.teach_teachers2_FK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='teach_teachers_FK' and t.table_name='teach_teachers' ) then drop index teach_teachers.teach_teachers_FK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='teach_teachers_PK' and t.table_name='teach_teachers' ) then drop index teach_teachers.teach_teachers_PK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='have_teacher_FK' and t.table_name='teacher' ) then drop index teacher.have_teacher_FK end if; if exists( select 1 from sys.sysindex i, sys.systable t where i.table_id=t.table_id and i.index_name='teacher_PK' and t.table_name='teacher' ) then drop index teacher.teacher_PK end if; if exists( select 1 from sys.systable where table_name='choose_course' and table_type='BASE' ) then drop table choose_course end if; if exists( select 1 from sys.systable where table_name='courses' and table_type='BASE' ) then drop table courses end if; if exists( select 1 from sys.systable where table_name='school' and table_type='BASE' ) then drop table school end if; if exists( select 1 from sys.systable where table_name='student' and table_type='BASE' ) then drop table student end if; if exists( select 1 from sys.systable where table_name='teach_teachers' and table_type='BASE' ) then drop table teach_teachers end if; if exists( select 1 from sys.systable where table_name='teacher' and table_type='BASE' ) then drop table teacher end if; /*==============================================================*/ /* Table: choose_course */ /*==============================================================*/ create table choose_course ( course_id varchar(16) not null, student_id varchar(16) not null, score decimal(5,2), primary key (course_id, student_id) ); /*==============================================================*/ /* Index: choose_course_PK */ /*==============================================================*/ create unique index choose_course_PK on choose_course ( course_id ASC, student_id ASC ); /*==============================================================*/ /* Index: choose_course_FK */ /*==============================================================*/ create index choose_course_FK on choose_course ( course_id ASC ); /*==============================================================*/ /* Index: choose_course2_FK */ /*==============================================================*/ create index choose_course2_FK on choose_course ( student_id ASC ); /*==============================================================*/ /* Table: courses */ /*==============================================================*/ create table courses ( course_id varchar(16) not null, course_name varchar(32), course_content varchar(255), course_book varchar(64), primary key (course_id) ); /*==============================================================*/ /* Index: courses_PK */ /*==============================================================*/ create unique index courses_PK on courses ( course_id ASC ); /*==============================================================*/ /* Table: school */ /*==============================================================*/ create table school ( school_id varchar(16) not null, school_sname varchar(32) not null, school_fname varchar(255), school_admin varchar(16), school_jl varchar(255), primary key (school_id) ); /*==============================================================*/ /* Index: school_PK */ /*==============================================================*/ create unique index school_PK on school ( school_id ASC ); /*==============================================================*/ /* Table: student */ /*==============================================================*/ create table student ( student_id varchar(16) not null, school_id varchar(16), student_name varchar(16) not null, student_sex varchar(2) not null, student_birth date, student_party varchar(16), student_from varchar(32), student_addr varchar(64), student_tel varchar(32), student_head varchar(32), student_detail varchar(255), student_photo long binary, primary key (student_id) ); /*==============================================================*/ /* Index: student_PK */ /*==============================================================*/ create unique index student_PK on student ( student_id ASC ); /*==============================================================*/ /* Index: have_student_FK */ /*==============================================================*/ create index have_student_FK on student ( school_id ASC ); /*==============================================================*/ /* Table: teach_teachers */ /*==============================================================*/ create table teach_teachers ( course_id varchar(16) not null, teacher_id varchar(16) not null, primary key (course_id, teacher_id) ); /*==============================================================*/ /* Index: teach_teachers_PK */ /*==============================================================*/ create unique index teach_teachers_PK on teach_teachers ( course_id ASC, teacher_id ASC ); /*==============================================================*/ /* Index: teach_teachers_FK */ /*==============================================================*/ create index teach_teachers_FK on teach_teachers ( course_id ASC ); /*==============================================================*/ /* Index: teach_teachers2_FK */ /*==============================================================*/ create index teach_teachers2_FK on teach_teachers ( teacher_id ASC ); /*==============================================================*/ /* Table: teacher */ /*==============================================================*/ create table teacher ( teacher_id varchar(16) not null, school_id varchar(16), teacher_name varchar(16) not null, teacher_sex varchar(2), teacher_birth date, teacher_level varchar(16), teacher_duty varchar(16), teacher_addr varchar(64), teacher_tel varchar(32), primary key (teacher_id) ); /*==============================================================*/ /* Index: teacher_PK */ /*==============================================================*/ create unique index teacher_PK on teacher ( teacher_id ASC ); /*==============================================================*/ /* Index: have_teacher_FK */ /*==============================================================*/ create index have_teacher_FK on teacher ( school_id ASC ); alter table choose_course add foreign key FK_CHOOSE_C_CHOOSE_CO_COURSES (course_id) references courses (course_id) on update restrict on delete restrict; alter table choose_course add foreign key FK_CHOOSE_C_CHOOSE_CO_STUDENT (student_id) references student (student_id) on update restrict on delete restrict; alter table student add foreign key FK_STUDENT_HAVE_STUD_SCHOOL (school_id) references school (school_id) on update restrict on delete restrict; alter table teach_teachers add foreign key FK_TEACH_TE_TEACH_TEA_COURSES (course_id) references courses (course_id) on update restrict on delete restrict; alter table teach_teachers add foreign key FK_TEACH_TE_TEACH_TEA_TEACHER (teacher_id) references teacher (teacher_id) on update restrict on delete restrict; alter table teacher add foreign key FK_TEACHER_HAVE_TEAC_SCHOOL (school_id) references school (school_id) on update restrict on delete restrict;

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

米格战斗机

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值