1.表A和表B具有完全相同的结构,查出表A中有但表B中没有的数据:
drop table if exists A;
create table A(
id int ,
name varchar(20),
password varchar(20)
);
drop table if exists B;
create table B(
id int ,
name varchar(20),
password varchar(20)
);
insert into A values (1,"zhangsan","san");
insert into A values (2,"lisi","si");
insert into A values (3,"wangwu","wu");
insert into A values (4,"zhaoliu","liu");
insert into A values (5,"houqi","qi");
insert into B values (1,"zhangsan","san");
insert into B values (2,"wangwu","wu");
SQL语句:
select * from A a where (a.name,a.password) not in (select name,password from B);
注意:在where条件中,有不止一个字段时,要用括号括住。
2.在下表中查询出male、female人数都相同的班级:
drop table if exists school;
create table school(
class int primary key,
male int,
female int
);
insert into school values (1,12,13);
insert into school values (2,13,14);
insert into school values (3,14,15);
insert into school values (4,15,16);
insert into school values (5,12,13);
insert into school values (6,12,14);
insert into school values (7,13,14);
SQL语句:
select male,female from school;
select male,female from school group by male,female;--分组可以用来去重,group by字段重复的只显示一次
select male,female from school group by male,female having COUNT(*)=1;--COUNT(*)=1,看哪些是不重复的
select male,female from school group by male,female having COUNT(*)>1;--COUNT(*)>1,看哪些是重复的
select * from school where (male,female) in (select male,female from school group by male,female having COUNT(*)>1);
select * from school where (male,female) in (select male,female from school group by male,female having COUNT(*)>1) order by male;
select * from school where (male,female) in (select male,female from school group by male,female having COUNT(*)>1) group by male,female order by male;
以上步骤是思路过程,长的、复杂的查询语句不要怕,都是从短的、简单的语句慢慢扩展而来的,所以可先写简单的语句看看结果,然后往目标方法扩展就好了。
值得注意的是,如果想去重的话,可以用分组,这样group by字段重复的记录就会只显示一次了,而且group by字段可以有多个的。