create table if not exists Person_182 (
Id int(10) not null auto_increment,
Email varchar(20) default null,
primary key(Id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into Person_182(Email) values ('a@b.com');
insert into Person_182(Email) values ('a@b.com');
insert into Person_182(Email) values ('a@b.com');
insert into Person_182(Email) values ('c@d.com');
insert into Person_182(Email) values ('c@d.com');
insert into Person_182(Email) values ('d@e.com');
select * from Person_182 order by Email;
2。答案
说明:提交时需要将Person_182修改为Person
SELECT Email
FROM Person_182
GROUP BY Email
HAVING COUNT(*) > 1;
SELECT Email
FROM Person_182
GROUP BY Email
HAVING COUNT(Id) > 1;
SELECT Email
FROM Person_182
GROUP BY Email
HAVING COUNT(Email) > 1;
SELECT distinct a.Email
FROM Person_182 a,Person_182 b
WHERE a.Id > b.Id
AND a.Email=b.Email
#Use self join
SELECT DISTINCT a.Email
FROM Person_182 a JOIN Person_182 b
ON (a.Email = b.Email)
WHERE a.Id <> b.Id
SELECT DISTINCT a.Email
FROM Person_182 a
WHERE EXISTS(
SELECT 1
FROM Person_182 b
WHERE a.Email = b.Email
LIMIT 1, 1
)
练习
建立熟悉的订单表
create table if not exists Orders (
O_Id int(10) not null auto_increment,
OrderDate date default null,
OrderPrice int(20),
Customer varchar(20) default null,
primary key(O_Id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 1000, 'Bush');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 1600, 'Carter');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 700, 'Bush');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 300, 'Bush');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 2000, 'Adams');
insert into Orders(OrderDate, OrderPrice, Customer) values ('2008/12/29', 100, 'Carter');
select * from Orders order by Customer;
需求1:我们希望查找每个客户的总金额(总订单)。我们想要使用 GROUP BY 语句对客户进行组合。
SELECT Customer,SUM(OrderPrice)
FROM Orders
GROUP BY Customer
需求2:我们希望查找订单总金额少于 2000 的客户。
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与合计函数一起使用。
SELECT Customer,SUM(OrderPrice)
FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
需求3:希望查找客户 “Bush” 或 “Adams” 拥有超过 1500 的订单总金额。
我们在 SQL 语句中增加了一个普通的 WHERE 子句:
SELECT Customer,SUM(OrderPrice)
FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
需求4:将Orders表按照OrderPrice降序(升序)后选择前5个元素?
select * from Orders order by OrderPrice asc limit 5;
select * from Orders order by OrderPrice desc limit 5;
需求5:将将Orders表按照OrderPrice降序后从第3条记录开始选择4条记录?
select * from Orders order by OrderPrice asc limit 2, 4
解释:
我们知道,在ms sql server中或access中,
若要查询前10条记录,使用top 10即可,
但在mysql中不支持这个写法,它用limit 10。
我们可以利用MySQL中SELECT支持的一个子句——LIMIT——来完成这项功能。
LIMIT可以实现top N查询,也可以实现M至N(某一段)的记录查询,具体语法如下:
SELECT * FROM MYTABLE
ORDER BY AFIELD
LIMIT offset, recnum
其中offset为从第几条(M+1)记录开始,recnum为返回的记录条数。例:
select * from mytable
order by afield
limit 2, 5
即意为从第3条记录开始的5条记录。
创建默认的日期时间类型。
OrderDate datetime NOT NULL DEFAULT NOW(),
CREATE TABLE OrdersTestxx
(
OrderId int NOT NULL auto_increment,
ProductName varchar(50) NOT NULL,
OrderDate datetime NOT NULL DEFAULT NOW(),
PRIMARY KEY (OrderId)
)