几个SQL语句笔试题

时间:2022-03-15 02:02:54

1.表test1和表test2具有完全相同的结构,查出表test1中有但表test2中没有的数据:

drop table if exists test1;
create table test1(
username varchar(20) not null default '',
password varchar(20) not null default ''
);
drop table if exists test2;
create table test2(
username varchar(20) not null default '',
password varchar(20) not null default ''
); insert into test1 values ('zhangsan','san'),('lisi','si'),('wangwu','wu');
insert into test2 values ('zhangsan','san'),('lisi','si');

低级写法:

select * from test1 where (username, password) not in (select * from test2);

高级写法:

select * from test1 t1
where not exists (
select 1 from test2 t2 where t1.username = t2.username and t1.password = t2.password
)

exists与in的效率要分情况,当in后面跟的子查询的结果集很大时,效率没有exits高,否则的话,效率差不多。

但是,not exists的效率一定比not in要高。

2.在下表中查询出male、female人数都相同的班级:

create table school(
class int primary key,
male int,
female int
);
insert into school values (1,12,13),(2,13,14),(3,14,15),(4,15,16),(5,12,13),(6,12,14),(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;

以上步骤是思路过程

千万要注意,长的、复杂的查询语句不要怕,都是从短的、简单的语句慢慢扩展而来的,所以可先写简单的语句看看结果,然后往目标方法扩展就好了。长语句很可能不能一步写到位,一步步扩展,一步步接近,最终能满足写出来。

值得注意的是,如果想去重的话,可以用分组,这样group by字段重复的记录就会只显示一次了,而且group by字段可以有多个的。

按道理来讲,用group by分组之后,查询出来的记录每一行都是一组,除分组字段外,其他字段的值只是每组中在数据表的第一条记录对应字段的值,没有什么意义,所以一般不建议查,mysql查这些非分组字段不会报错,在sqlserver中会报错的。

select class,male,female from school group by male,female;--分组可以用来去重,group by字段重复的只显示一次

select class,male,female from school group by male,female having COUNT(1)=1;--COUNT(*)=1,看哪些是不重复的

select class,male,female from school group by male,female having COUNT(1)>1;--COUNT(*)>1,看哪些是重复的

这三条sql语句,在mysql中不会报错,但是在sqlserver中会报错误:选择列表中的列 'school.class' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。group by子句是不能加class字段了,那么聚合函数中怎么加这个字段呢?

尝试select class,male,female from school group by male,female having COUNT(class)>=1;还是报错,突然想到在select列表中不是直接查非分组字段,而是用聚合函数包裹,如:

select count(class),male,female from school group by male,female;

现在没有问题了。

总结:非分组字段要么不查询,要么用聚合函数包裹再写在select列表中。