Oracle exsist Exercise 2

Table Structure

Create Table u
(
Name char (64) not null,
Id integer not null primary key
)
Tablespace Cici;

 
Comment on table u
Is 'user table ';
 

Create Table grou
(
Name char (64) not null,
Id integer not null primary key
)
Tablespace Cici;

 
Comment on table grou
Is 'permission group table ';
 

Create Table u_grou
(
G_id integer not null,
U_id integer not null
)
Tablespace Cici;

 
Comment on Table u_grou
Is 'relational table ';
 

Alter table u_grou
Add foreign key (g_id)
References grou (ID );
Alter table u_grou
Add foreign key (u_id)
References U (ID );

 

Data

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 );

 

Query the permission groups of users whose user ID is 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
)
);

 

 

Query the permission groups of users whose name is 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
);

 

Query Process

 --->Select * from u where u. Name = 'u1' and U. ID = 1If there is a value, the returned result is true, so there is data

--->Select * from u where u. Name = 'u1' and U. ID = 2If there is a value, the returned result is true, so there is data

==> All data in the u_grou table is found

Select * From grou where grou. ID = 1If there is a value, the returned result is true, so there is data

Select * From grou where grou. ID = 2If there is a value, the returned result is true, so there is data

==>GrouAll table data is detected

 

 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 andRownum = 1
) And grou. ID = u_grou.g_id
);

Query Process ??

Exist Essence

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