几个SQL语句笔试题

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字段可以有多个的。