MySQL任务3：表操作、表联结

2.1 MySQL 基础 （二）- 表操作

学习内容

1、MySQL表数据类型

字符串类型：CHAR、TEXT
- 定长字符串：接受长度固定的字符串，在创建表时指定其长度。定长列不允许多于指定的字符数目，缺少的字符长度用空格填充或根据需要补为NULL。
- 变长字符串：存储任意长度的文本，只有指定的数据得以保存。优点是灵活，缺点是性能。
- 字符串值必须括在单引号内。

数值类型：
- 如：BIT（二进制）、DECIMAL（或NUMERIC）、FLOAT（或NUMBER）、INT等。
- 数值不应该括在引号内。

日期和时间值类型：
- 每种DBMS能识别自己特定的日期格式，请参阅相应文档。
- 如：DATE、DATETIME（或TIMESTAMP）、TIME等。

二进制类型：
- 可包含任何数据，甚至可包含二进制信息，如图像、多媒体、字处理文档等。
- 如：BINARY、 (LONG) RAW、VARBINARY等。

2、用SQL语句创建表

语句：
CREATE TABLE 表名
(
列名1  类型  NULL,
列名2  类型  NOT NULL,
);

语句解释：
表名紧跟CREATE TABLE关键字。
实际的表定义（所有列）括在圆括号中，各列之间用逗号分隔。
每列的定义以列名开始，后跟列的数据类型。
允许NULL值就是允许该列有缺值，NOT NULL就是不接受没有列值的行。

设定主键：只有不允许NULL值得列可作为主键。

3、用SQL语句向表中添加数据
语句解释
多种添加方式（指定列名；不指定列名）

4、用SQL语句删除表

语句：DROP TABLE 表名XX；    
语句解释：永久删除XX表。删去整个表，删除时没有确认，也不能撤销

语句：DELETE FROM 表名称 WHERE 列名=值
语句解释：删除表中的数据。用于删除表中的行，每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存。

语句：TRUNCATE TABLE 表名称;
语句解释：清空表中的数据。删除内容、释放空间但不删除定义(保留表的数据结构)。

5、用SQL语句修改表

修改列名：
alter table 表名 change  列名 address address1 varchar(30);

修改表中数据：
UPDATE 表名
SET 列名 = 值
WHERE 列名x = 值x;

删除行：
DELECT FROM 表名
WHERE 列名 = 值;

删除列：
alter table 表名 drop  列名;

新建列：
alter table 表名 add 列名 varchar(10);

新建行：
INSERT INTO 表名 ( 列名 )
VALUES ( 值 );

作业

项目三：超过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

Note: 学生在每个课中不应被重复计算。

答案：

错误答案：
SELECT class, COUNT(class) as stud_num
FROM courses
GROUP BY class
HAVING stud_num >= 5;

问题：学生A重复计数了。

解决：
SELECT class
FROM (
select distinct * from courses)
AS c
GROUP BY c.class
HAVING COUNT(class)>=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

答案：

UPDATE salary
SET sex = (
CASE WHEN sex = ‘m’
THEN ‘f’
ELSE ‘m’
END)

其他方法：
update salary set sex = if(sex = ‘m’, ‘f’,‘m’);

2.2 MySQL 基础 （三）- 表联结

学习内容

MySQL别名： 表别名只在查询执行中使用，不返回到客户端。

语句：
SELECT 列名1，列名2
FROM 表名1 AS 别名1， 表名2 AS 别名2
WHERE 别名1.列名 = 别名2.列名；

INNER JOIN： 基于两个表之间的相等测试，联结那些在相关表中有关联行的行。两个表之间的关系是以INNER JOIN指定的部分FROM字句，联结条件用特定的ON字句给出。

语句：
SELECT 列1， 列2，列3
FROM 表1 INNER JOIN 表2
	ON 表1.外键 = 表2.外键;

外键：某个表中的一列，它包含另一个表的主键值，定义了两个表的关系。

LEFT JOIN： 与内联结不同，外联结包含了那些在相关表中没有关联行的行，使用OUTER JOIN语法时，必须使用RIGHT或LEFT 关键字指定包括其所有行的表。

语句：
SELECT 表1.列1， 表2.列2
FROM 表1 LEFT OUTER JOIN 表2
	ON 表1.列1 = 表2.列1（列1为外键）；
解释：从表1中选择所有行，即除了包含表1与表2相关联的行，还包含表1中没有关联行的那些行

RIGHT指出OUTER JOIN右边的表；
LEFT指出OUTER JOIN左边的表；
左、右外联结可以互换使用，唯一的差别是所关联的表的顺序。

CROSS JOIN： 笛卡尔积联结，又称叉联结。由没有联结条件的表关系返回的结果为笛卡尔积，数据没有意义。

自连接 self-join： 在一条SELECT语句中不止一次引用相同的表，为避免DBMS产生歧义，使用表别名。

自联结：
SELECT  别名1.列
FROM 表 AS 别名1， 表 AS 别名2
WHERE 别名1.列x = 别名2.列x；

UNION： 组合数条SQL查询（>=2条）。默认从查询结果集中自动去除重复的行。如果想返回所有的匹配行，可使用UNION ALL而不是UNION。

SELECT 语句
UNION
SELECT语句
UNION
SELECT语句
ORDER BY 列名;

以上几种方式的区别和联系

作业

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

在数据库中创建表1和表2，并各插入三行数据（自己造）
表1: Person

列名	类型
PersonId	int
FirstName	varchar
LastName	varchar

PersonId 是上表主键

表2: Address

列名	类型
AddressId	int
PersonId	int
City	varchar
State	varchar

AddressId 是上表主键

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

答案：

SELECT p.FirstName, p.LastName, a.City, a.State
FROM Person p LEFT JOIN Address a
ON p.personId = a.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

答案：

DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id

【任务说明】
本次作业以及之后的都需要小伙伴自己创建表和插入数据啦。就当表操作的练习。请注意语句规范，可参考上次作业的参考答案。
表联结是SQL语句核心部分。因为在正式业务中必然会涉及到多表之间的数据调用。所以大家务必完全理解吃透这部分内容。