MySQL(II) 任务3
内容：
1. 表操作
1.1 创建表
1.2 添加数据
1.3 删除表
1.4 修改表
1.5 代码
2. 表联结
2.1 INNER JOIN
2.2 LEFT JOIN
2.3 CROSS JOIN
2.4 UNION
2.5 代码
1. 表操作
1.1 创建表
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
1.2 添加数据
INSERT INTO 语句有两种方法。第一种方法明确了列名称和待插入值：
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
如果要对表格的所有列变量都插入值，则不需要写明列名称：
INSERT INTO table_name VALUES (value1, value2, value3, ...);
1.3 删除表
DROP TABLE table_name；
DELETE语句用于删除表格已有记录：
DELETE FROM table_name WHERE condition;
若要删节数据，用TRUNCATE语句：
TRUNCATE TABLE table_name;
1.4 修改表
添加某一列：
ALTER TABLE table_name ADD column_name datatype;
删除某一列：
ALTER TABLE table_name DROP COLUMN column_name;
修改某一列：
ALTER TABLE table_name MODIFY COLUMN column_name datatype;
1.5 代码
项目三 （难度：简单）
创建如下所示的 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 |
+---------+
注意：学生在每个课程中不应重复计算。
(a) 创建表
CREATE TABLE course( student VARCHAR(20) NOT NULL, class VARCHAR(20) NOT NULL, ); INSERT INTO course VALUES('A','Math'); INSERT INTO course VALUES('B','English'); INSERT INTO course VALUES('C','Math'); INSERT INTO course VALUES('D','Biology'); INSERT INTO course VALUES('E','Math'); INSERT INTO course VALUES('F','Computer'); INSERT INTO course VALUES('G','Math'); INSERT INTO course VALUES('H','Math'); INSERT INTO course VALUES('I','Math'); INSERT INTO course VALUES('A','Math');
(b) 查询
SELECT class FROM course GROUP BY class having COUNT(DISTINCT (student)) >=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 |
(a) 创建表
CREATE TABLE salary( id INT(20) NOT NULL, NAME VARCHAR(20) NOT NULL, sex VARCHAR(20) NOT NULL, salary INT(20) NOT NULL );
(b) 查询
使用case 语句
UPDATE salary SET sex=case when sex='m' then 'f' ELSE 'm' END;
截图：
2. 表联结
2.1 INNER JOIN
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
2.2 LEFT JOIN
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
2.3 FULL OUTER JOIN
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
2.4 UNION
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
2.5 代码
项目五：组合两张表 （难度：简单）
在数据库中创建表1和表2，并各插入三行数据：
表1：Person
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId 是表1的主键。
表2：Adress
+-------------+---------+
| 列名 | 类型 |
+-------------+---------+t
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AdressId 是表2的主键。编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State。
(a) 创建表
CREATE TABLE Person( PersonId INT(20) NOT NULL, Firstname VARCHAR(20) NOT NULL, Lastname VARCHAR(20) NOT NULL, PRIMARY KEY (PersonId) ); CREATE TABLE Address( AddressId INT(20) NOT NULL, PersonId INT(20) NOT NULL, City VARCHAR(20) NOT NULL, State VARCHAR(20) NOT NULL, PRIMARY KEY (AddressId) );
(b) 查询
SELECT Person.Firstname, Person.Lastname, Address.City, Address.State FROM Person LEFT JOIN Address ON Person.PersonId=Address.PersonId；
截图：
任务六：删除重复的邮箱 （难度：简单）
编写一个 SQL 查询，来删除 email 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
Id 是这个表的主键。 运行完查询后，应该返回如下几行：
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | a@b.com |
| 2 | c@d.com |
+----+------------------+
(a) 创建表
create table email( Id int(10) not null, Email varchar(20) not null, PRIMARY KEY (Id) ); insert into email VALUES(1,'a@b.com'); insert into email VALUES(2,'c@d.com'); insert into email VALUES(3,'a@b.com');
(b) 查询
DELETE FROM email WHERE NOT id IN( SELECT min(Id) FROM (SELECT * FROM email) AS e2 GROUP BY Email );
截图：
