ORACLE EXSIST 练习2

爱被打了一巴掌 2022-01-06 07:01 242阅读 0赞

表结构

create table U  
(  
  NAME CHAR(64) not null,  
  ID   INTEGER not null PRIMARY KEY  
)  
tablespace CICI;

comment on table U
      is '用户表';

create table GROU  
(  
  NAME CHAR(64) not null,  
  ID   INTEGER not null PRIMARY KEY  
)  
tablespace CICI;

comment on table GROU
      is '权限组表';

CREATE TABLE U\_GROU  
(  
  G\_ID INTEGER not null  ,  
  U\_ID   INTEGER not null    
)  
tablespace CICI;

comment on table TABLE U_GROU
      is '关系表';

alter table U\_GROU  
  add foreign key (G\_ID)  
  references GROU (ID);  
alter table U\_GROU  
  add foreign key (U\_ID)  
  references U (ID);

数据

INSERT INTO U VALUES ('U1',1);  
INSERT INTO U VALUES ('U1',2);  
  
INSERT INTO GROU VALUES ('G1',1);  
INSERT INTO GROU VALUES ('G1',2);  
  
INSERT INTO u\_GROU VALUES (1,1);  
INSERT INTO u\_GROU VALUES (1,2);  
INSERT INTO u\_GROU VALUES (2,2);

查询目的 查询用户id 为1的用户对应的权限组

SELECT \* FROM GROU WHERE EXISTS (  
       SELECT \* FROM U\_GROU WHERE GROU.ID = U\_GROU.G\_ID AND  EXISTS (  
              SELECT \* FROM U WHERE U.ID = U\_GROU.U\_ID AND U.ID = 1  
       )  
);

![Image 1][]

查询 name为U1的用户对应的权限组

**SELECT \* FROM GROU WHERE EXISTS (**  
**       SELECT \* FROM U\_GROU WHERE   EXISTS (**  
**              SELECT \* FROM U WHERE  U.name = 'U1'  and U.ID = U\_GROU.U\_ID**  
**       )   AND GROU.ID = U\_GROU.G\_ID**   
**);**

![Image 1][]

查询过程

\---> SELECT \* FROM U WHERE  U.name = 'U1' and u.id = 1 有值，返回真，所以有数据

\---> SELECT \* FROM U WHERE  U.name = 'U1' and u.id = 2 有值，返回真，所以有数据

===>U\_GROU 表的数据被全部查出

SELECT  \*  FROM GROU where GROU.ID = 1 有值，返回真，所以有数据

SELECT  \*  FROM GROU where GROU.ID = 2 有值，返回真，所以有数据

===>GROU表的数据被全查出

**SELECT \* FROM GROU WHERE EXISTS (**  
**         SELECT  \*  FROM U\_GROU WHERE   EXISTS (**  
**                SELECT \* FROM U WHERE  U.name = 'U1' and u.id = u\_grou.u\_id and rownum=1**  
**         )   AND GROU.ID = U\_GROU.G\_ID**   
**  );**

![Image 1][]

**查询过程 ？？**

exist实质

http://zlk.iteye.com/blog/903322

转载于:https://www.cnblogs.com/cici-new/archive/2013/01/26/2878215.html

[Image 1]: