MySQLTask1,2

项目1 查找重复的电子邮箱（难度：简单）

创建 email 表，并插入如下三行数据

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com

编写一个 SQL 查询，查找 email 表中所有重复的电子邮箱。
根据以上输入你的查询应返回以下结果：

Email
a@b.com

DROP TABLE email;
CREATE TABLE email (
ID INT NOT NULL PRIMARY KEY,
Email VARCHAR(255) NOT NULL
);
INSERT INTO email VALUES('1','a@b.com');
INSERT INTO email VALUES('2','c@d.com');
INSERT INTO email VALUES('3','a@b.com');

SELECT Email
FROM email
GROUP BY Email
HAVING COUNT(Email) > 1;

输出结果如下：

项目2 查找大国（难度：简单）

创建如下 World 表

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000
Albania	Europe	28748	2831741	12960000
Algeria	Africa	2381741	37100000	188681000
Andorra	Europe	468	78115	3712000
Angola	Africa	1246700	20609294	100990000

如果一个国家的面积超过 300 万平方公里，或者(人口超过 2500 万并且 gdp 超过 2000 万)，那么这个国家就是大国家。
编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。
例如，根据上表，我们应该输出:

name	population	area
Afghanistan	25500100	652230
Algeria	37100000	2381741

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);

SELECT name,population,area
FROM World
WHERE area > 3000000 OR (population > 25000000 AND gdp >20000000);

输出结果如下：

项目3

创建如下所示的 courses 表 ，有: student (学生) 和 class (课程)。
例如,表:

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Computer
G	Math
H	Math
I	Math
A	Math

编写一个 SQL 查询，列出所有超过或等于5名学生的课。
应该输出:

class
Math

Note:
学生在每个课中不应被重复计算。

CREATE TABLE Courses (
student VARCHAR(1) NOT NULL,
class VARCHAR(24) NOT NULL
);
INSERT INTO Courses
  VALUES('A','Math'),
  ('B','English'),
  ('C','Math'),
  ('D','Biology'),
  ('E','Math'),
  ('F','Computer'),
  ('G','Math'),
  ('H','Math'),
  ('I','Math'),
  ('A','Math');
 
SELECT class FROM 
(SELECT DISTINCT * FROM Courses) AS new_Courses
GROUP BY class
HAVING COUNT(class) >= 5;

输出结果如下：

项目4 交换工资（难度：简单）

创建一个 salary 表，如下所示，有m=男性 和 f=女性的值 。
例如:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。
运行你所编写的查询语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

CREATE TABLE Salary (
id int NOT NULL,
name VARCHAR(1) NOT NULL,
sex VARCHAR(1) NOT NULL,
salary INT NOT NULL
);
INSERT INTO Salary
  VALUES(1,'A','m',2500),
  (2,'B','f',1500),
  (3,'C','m',5500),
  (4,'D','f',500);

##方法一
UPDATE Salary
SET sex = 'x'
WHERE sex = 'm';
UPDATE Salary
SET sex = 'm'
WHERE sex = 'f';
UPDATE Salary
SET sex = 'f'
WHERE sex = 'x';

##方法二
UPDATE Salary
SET sex=
  CASE id
    WHEN 1 THEN 'f'
    WHEN 2 THEN 'm'
    WHEN 3 THEN 'f'
    WHEN 4 THEN 'm'
  END;

##方法三
UPDATE Salary
SET sex=
  CASE sex
    WHEN 'm' THEN 'f'
    WHEN 'f' THEN 'm'
  END;

##检验结果  
SELECT *
FROM Salary;

输出结果如下：

项目5 组合两张表 （难度：简单）

在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person

列名	类型
PersonId	int
FirstName	varchar
LastName	varchar

PersonId 是上表主键

表2: Address

列名	类型
AddressId	int
PersonId	int
City	varchar
State	varchar

AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

CREATE TABLE Person (
Personld int NOT NULL PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL
);
INSERT INTO Person
  VALUES(1,'A','a'),
  (2,'B','b'),
  (3,'C','c');

CREATE TABLE Address (
Addressld int NOT NULL PRIMARY KEY,
Personld int NOT NULL,
City VARCHAR(50) NOT NULL,
State VARCHAR(50) NOT NULL
);
INSERT INTO Address
  VALUES(123,1,'A','m'),
  (234,2,'B','f'),
  (345,3,'C','m');

SELECT p.FirstName,p.LastName,a.City,a.State
FROM Address a RIGHT OUTER JOIN Person p
ON p.Personld = a.Personld;

输出结果如下：

项目6 删除重复的邮箱（难度：简单）

编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。

Id	Email
1	a@b.com
2	c@d.com
3	a@b.com

Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

Id	Email
1	a@b.com
2	c@d.com

DELETE FROM email
WHERE id IN (SELECT * FROM 
        (SELECT MAX(id) 
         FROM  email
         GROUP BY Email
         HAVING COUNT(Email) > 1) AS new
);

##检验结果
SELECT *
FROM email;

结果如下：

Note：
子查询的 FROM 子句和 UPDATE / DELETE 使用同一张表，会出现1093错误，这时需通过多加一层 SELECT 语句让 mysql 认为检索的不是一张表，同时要为新表赋予别名。

出错的情况：
不能在FROM子句中为update/delete指定目标表’email’

DELETE FROM email
WHERE id IN (
SELECT MAX(id)
FROM email
GROUP BY Email
HAVING COUNT(Email)>1);

[Err] 1093 - You can't specify target table 'email' for update in FROM clause