MySQL 基础 （二）- 表操作（三）- 表联结

项目三：查找>=5的学生课程（难度：简单）

1.任务

创建如下所示的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名学生的课。
Note:
学生在每个课中不应被重复计算。

2.实现过程

• 创建数据库（右键命令列界面）
  
• 用SQL语句创建表

mysql> use renwuer;
Database changed
mysql> create table xs(
    -> student    varchar(2)   not null,
    -> class      varchar(20)  not null);
  
Query OK, 0 rows affected

• 用SQL语句向表中添加数据

mysql> insert into xs values('A','Math');
mysql> insert into xs values('B','English');
mysql> insert into xs values('C','Math');
mysql> insert into xs values('D','Biology');
mysql> insert into xs values('E','Math');
mysql> insert into xs values('F','Computer');
mysql> insert into xs values('G','Math');
mysql> insert into xs values('H','Math');
mysql> insert into xs values('I','Math');
mysql> insert into xs values('A','Math');

• 编写一个 SQL 查询，列出所有超过或等于5名学生的课。
  数据库renwuer—表格xs—查询–新建查询

select class from xs
group by(class)
HAVING COUNT(DISTINCT student,class)>5 

3.结果

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

1.任务

创建一个 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，反之亦然)。要求使用一个更新查询，并且没有中间临时表。

2.实现过程

• 用SQL语句创建表

mysql> use renwuer;
Database changed
mysql> create table gz (
    -> id   int   unsigned   AUTO_INCREMENT   not null  primary key,
    -> name varchar(2)                        not null,
    -> sex  varchar(2)                        not null,
    -> salary int                        not null);
Query OK, 0 rows affected

• 用SQL语句向表中添加数据

mysql> insert into gz values('1','A','m','2500');
mysql> insert into gz values('2','B','f','1500');
mysql> insert into gz values('3','C','m','5500');
mysql> insert into gz values('4','D','f','500');

• 交换性别值（case when语法）SQL中的CASE WHEN语句

UPDATE gz set sex = (
case sex
when 'f' then 'm'
when 'm' then 'f'
else 'others'
end)

select * FROM gz

3.结果

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

1.任务

在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person
±------------±--------+
| 列名 | 类型 |
±------------±--------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
±------------±--------+
PersonId 是上表主键

表2: Address
±------------±--------+
| 列名 | 类型 |
±------------±--------+t
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
±------------±--------+
AddressId 是上表主键

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

2.实现过程

• 用SQL语句创建表

• 用SQL语句向表中添加数据

• 连接两个表

SELECT firstname,lastname,city,state
from person p  left join address a on p.personid = a.personid

3.结果

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

1.任务

编写一个 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 |
±—±-----------------+

2.实现过程

• 用SQL语句创建表

• 用SQL语句向表中添加数据

• 删除重复邮箱，并保留下id号较小的那个

delete em1
from email em1,email em2
where em1.email = em2.email AND em1.id>em2.id 
SELECT * FROM email

###3. 结果

学到的知识点：

1.count里面distinct一下

COUNT(DISTINCT student,class)

2.sql的case when 语句：参考其他博主

CASE WHEN有两种用法，一种是类似上面例子中那样的简单CASE函数：

CASE result
    WHEN '胜' THEN 1
    WHEN '负' THEN 2
ELSE 0
END
 

还有一种是CASE搜索函数：

CASE WHEN result='胜' THEN 1
     WHEN result='负' THEN 2
ELSE 0
END

3.删除重复邮箱的多种解法

(https://www.cnblogs.com/grandyang/p/5371227.html)