7天快速掌握MySQL-DAY5

--创建表
create table person (
personid int not null primary key,
firstname varchar(20) not null,
lastname varchar(20) not null
);
--插入数据
insert into person values (101, 'ming', 'Yao');
insert into person values (102, 'lei', 'Wu');
insert into person values (103, 'zhi', 'Zheng');
--查询结果
mysql> select * from person;
+----------+-----------+----------+
| personid | firstname | lastname |
+----------+-----------+----------+
|      101 | ming      | Yao      |
|      102 | lei       | Wu       |
|      103 | zhi       | Zheng    |
+----------+-----------+----------+
3 rows in set (0.00 sec)
--创建表
create table address (
addressid int not null primary key,
personid int not null,
city varchar(20) not null,
state varchar(20) not null
);
--插入数据
insert into address values (8000, 9999, 'xian', 'UA');
insert into address values (8008, 102, 'beijing', 'AB');
insert into address values (9009, 103, 'hangzhou', 'AC');
--查询结果
mysql> select * from address;
+-----------+----------+----------+-------+
| addressid | personid | city     | state |
+-----------+----------+----------+-------+
|      8000 |     9999 | xian     | UA    |
|      8008 |      102 | beijing  | AB    |
|      9009 |      103 | hangzhou | AC    |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)

mysql> select firstname, lastname, a.city, a.state
    -> from person
    -> left join address as a on person.personid = a.personid;
+-----------+----------+----------+-------+
| firstname | lastname | city     | state |
+-----------+----------+----------+-------+
| ming      | Yao      | NULL     | NULL  |
| lei       | Wu       | beijing  | AB    |
| zhi       | Zheng    | hangzhou | AC    |
+-----------+----------+----------+-------+
3 rows in set (0.00 sec)

#问题分析
a. 删除重复的电子邮箱
b. 需要保留ID最小的那个（删除的ID比留下的那一条ID要大）
c. 考察自连接知识，把一张表当两张表来用。
--code--
mysql> select * from email;
+----+---------+
| ID | Email   |
+----+---------+
|  1 | a@b.com |
|  2 | c@d.com |
|  3 | a@b.com |
+----+---------+
3 rows in set (0.00 sec)

mysql> delete e1
    -> from email as e1, email as e2
    -> where e1.Email = e2.Email
    -> and e1.ID > e2.ID;
Query OK, 1 row affected (0.01 sec)

mysql> select * from email;
+----+---------+
| ID | Email   |
+----+---------+
|  1 | a@b.com |
|  2 | c@d.com |
+----+---------+
2 rows in set (0.00 sec)

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 
department Id。
+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+
创建Department 表，包含公司所有部门的信息。
+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+
编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，
Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| Sales      | Henry    | 80000  |
+------------+----------+--------+

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
 请创建如下所示seat表：
示例：
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
假如数据输入的是上表，则输出结果如下：
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
注意：
如果学生人数是奇数，则不需要改变最后一个同学的座位。