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