DataWhale Mysql Task2 2019-04-03
2019-04-03 本文已影响0人
lala兔斯基
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;
