182. Duplicate Emails 1。建表
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) )