项目一:查找重复的电子邮箱
create database test;
use test;
create table email(
ID INT NO NULL PRIMARY KEY,
Email varchar(255)
);
insert into email values(1,'a@b.com');
insert into email values(2,'c@d.com');
insert into email values(3,'a@b.com');
查找重复的email语句:
select Email ,count(*) as count from email group by Email having count(检查字段)>1;
select 检查字段,count(检查字段) from table group by 检查字段 having count(检查字段) >1;
项目二:查找大国
use test;
create table World(
name varchar(50) not null,
continent varchar(50) not null,
area int not null,
population int not null,
gdp int not null
);
insert into World values('Afghanistan','Asia',652230,25500100,20343000);
INSERT INTO World
VALUES('Albania','Europe',28748,2831741,12960000);
INSERT INTO World
VALUES('Algeria','Africa',2381741,37100000,188681000);
INSERT INTO World
VALUES('Andorra','Europe',468,78115,3712000);
INSERT INTO World
VALUES('Angola','Africa',1246700,20609294,100990000);
Q:如果一个国家的面积超过300万平方公里,或者(人口超过2500万并且gdp超过2000万),那么这个国家就是大国家。 编写一个SQL查询,输出表中所有大国家的名称、人口和面积。
select name,population,area from World where area>3000000 or (population>25000000 and gdp >20000000);