DataWhale Mysql Task2 2019-04-03

DataWhale Mysql Task2

2.1 MySQL 基础 （二）- 表操作

项目三

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

#创建courses表
CREATE TABLE IF NOT EXISTS courses(
  student VARCHAR(1) NOT NULL ,
  class VARCHAR(14) NOT NULL
);
#输入courses的数据信息
INSERT INTO courses(student,class)
VALUES ('A','Math'),
       ('B','English'),
       ('C','Math'),
       ('D','Biology'),
       ('E','Math'),
       ('F','Computer'),
       ('G','Math'),
       ('H','Math'),
       ('I','Math'),
       ('A','Math');
#查询，列出所有超过或等于5名学生的课
SELECT class
FROM courses
GROUP BY class HAVING  COUNT(*) >= 5

项目四

交换工资（难度：简单）
创建一个 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 IF NOT EXISTS salary(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(1) NOT NULL ,
  sex VARCHAR(1) NOT NULL ,
  salary INT NOT NULL ,
  PRIMARY KEY (id)
);
#插入数据
INSERT INTO salary(name,sex,salary)
VALUES ('A','f',2500),
       ('B','m',1500),
       ('C','f',5500),
       ('D','m',500);
#查询数据
SELECT id,name,(CASE sex WHEN 'f' THEN 'm' WHEN 'm' THEN 'f' END ) AS sex,salary
FROM salary;

2.2 MySQL 基础 （三）- 表联结

项目五

组合两张表 （难度：简单）
在数据库中创建表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

#创建表
CREATE TABLE IF NOT EXISTS Person(
  PersonId INT NOT NULL ,
  FirstName VARCHAR(20) NOT NULL ,
  LastName VARCHAR(20) NOT NULL ,
  PRIMARY KEY (personId)
);
#创建表
CREATE TABLE IF NOT EXISTS Address(
  AddressId INT NOT NULL ,
  PersonId INT NOT NULL ,
  City VARCHAR(20) NOT NULL ,
  State VARCHAR(20) NOT NULL ,
  PRIMARY KEY (AddressId),
  CONSTRAINT PersonId FOREIGN KEY(PersonId) REFERENCES Person(PersonId)
);
#修改表字段长度
ALTER TABLE Person MODIFY COLUMN FirstName VARCHAR(100);
ALTER TABLE Person MODIFY COLUMN LastName VARCHAR(100);
ALTER TABLE Address MODIFY City VARCHAR(100);
ALTER TABLE Address MODIFY State VARCHAR(100);
#存入数据
INSERT INTO
  Person(PersonId, FirstName, LastName)
VALUES
       (0001,'yun','lin'),
       (0002,'huiyin','lin'),
       (0003,'ailing','zhang'),
       (0004,'tianyi','luo'),
       (0005,'wangji','lan');
INSERT INTO
  Address(addressid, personid, city, state)
VALUES
       (99999,0001,'shouer','KR'),
       (12345,0002,'fujian_cahngle','CN'),
       (42354,0003,'tangshan','CN'),
       (65756,0004,'null','null'),
       (67458,0005,'najing','CN');
#查询表
SELECT * FROM Person,Address
WHERE Person.PersonId=Address.PersonId

项目六：删除重复的邮箱（难度：简单）
编写一个 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          |
+----+------------------+

#创建表
create table email
(
  ID    int          not null
    primary key,
  Email varchar(255) null
);
#删除重复的邮箱
DELETE e2 FROM email e1 JOIN email e2
ON e2.Email = e1.Email WHERE e2.Id > e1.Id;