sql连接练习

create table boy(id int not null primary key auto_increment,u_name char(10),age int);
create table girl(id int not null primary key auto_increment,u_id int,lover_name char(10));
insert into boy values(null,'刘一',25);
insert into boy values(null,'陈二',28);

insert into boy values(null,'张三',22);

insert into boy values(null,'李四',21);

insert into boy values(null,'王五',24);

insert into girl values(null,1,'小红'),
(null,2,'小花'),(null,3,'小芳');
 

 --查出有对象的用户信息

select boy.id,u_name,age from boy,girl where boy.id=girl.u_id;

select boy.id,u_name,age from boy join girl on boy.id=girl.u_id;
select boy.id,u_name,age from boy cross join girl on boy.id=girl.u_id;
select boy.id,u_name,age from boy inner join girl on boy.id=girl.u_id;

 

-- 查询出所有的用户信息及其对象信息
select boy.id,u_name,age,girl.id,u_id,lover_name from boy left join girl on boy.id=girl.u_id

-- 查询出没有对象的用户信息
select boy.id,u_name,age from boy where boy.id not in (select id from girl)

 

 sql执行顺序

 from  join on where group by avg,sum,min,max having select distinct