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

一、表操作

1、数据类型

参考：https://www.runoob.com/mysql/mysql-data-types.html

2、创建表

参考：https://www.runoob.com/mysql/mysql-create-tables.html

3、向表中添加数据

参考：https://www.runoob.com/mysql/mysql-insert-query.html

4、删除表

参考：https://www.runoob.com/mysql/mysql-drop-tables.html

DROP TABLE 表名;

删除没有被关联的普通表用上方方法。

删除被其他表关联的父表：
方法一：先删除子表，在删除父表
方法二：删除父表的外键约束，再删该表

5、修改表

参考：https://www.cnblogs.com/sqbk/p/5806797.html

1. 修改表名

ALTER TABLE 旧表名 RENAME 新表名;

2. 修改字段的数据类型

ALTER TABLE 表名 MODIFY 属性名 数据类型;

3. 修改字段名称和属性

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新属性;

4. 增加字段

ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];

其中，“属性名1”参数指需要增加的字段的名称；“FIRST”参数是可选参数，其作用是将新增字段设置为表的第一个字段；“AFTER”参数也是可选的参数，其作用是将新增字段添加到“属性名2”后面；“属性名2”当然就是指表中已经有的字段

5. 删除字段

ALTER TABLE 表名 DROP 属性名;

6. 更改表的存储引擎

ALTER TABLE 表名 ENGINE = 存储引擎名;

7. 删除表的外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;

8. 添加删除默认值

ALTER TABLE 表名 ALTER 字段名 SET DEFAUTL 默认值;
ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;

9. 添加删除主键

-- 给 表名 添加主键, 完整形式
ALTER TABLE 表名 ADD CONSTRAINT symbol PRIMARY KEY(字段名称1, 字段名称2...)

在删除主键时，有一种情况是需要注意的，我们知道具有自增长的属性的字段必须是主键，如果表里的主键是具有自增长属性的；那么直接删除是会报错的。如果想要删除主键的话，可以先去掉自增长属性，再删除主键。

-- 创建一个表
CREATE TABLE test(
id INT UNSIGNED KEY AUTO_INCREMENT
);

-- 删除主键，这样会报错，因为自增长的必须是主键
ALTER TABLE test DROP PRIMARY KEY;

-- 先用MODIFY删除自增长属性，注意MODIFY不能去掉主键属性
ALTER TABLE test MODIFY id INT UNSIGNED;
-- 再来删除主键
ALTER TABLE test DROP PRIMARY KEY;

11. 修改自增长值

ALTER TABLE 表名 AUTO_INCREMENT=值

12. 唯一索引

-- 添加唯一性约束
ALTER TABLE tb_name ADD [CONSTANT [symbol]] UNIQUE [INDEX | KEY] [索引名称](字段名称,...)

-- 创建测试表
CREATE TABLE user_tn(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
card CHAR(18) NOT NULL,
test VARCHAR(20) NOT NULL,
test1 CHAR(32) NOT NULL
);

-- username添加唯一性约束，如果没有指定索引名称，系统会以字段名建立索引
ALTER TABLE user_tn ADD UNIQUE(username);
-- card添加唯一性约束
ALTER TABLE user_tn ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);
-- 查看索引
SHOW CREATE TABLE user_tn;

-- test,test1添加联合unique
ALTER TABLE user_tn ADD CONSTRAINT symbol UNIQUE INDEX mulUni_test_test1(test, test1);

-- 删除唯一
ALTER TABLE tb_name DROP {INDEX|KEY} index_name;

-- 删除刚刚添加的唯一索引
ALTER TABLE user_tn DROP INDEX username;
ALTER TABLE user_tn DROP KEY uni_card;
ALTER TABLE user_tn DROP KEY mulUni_test_test1;

---

6、作业

作业三：超过5名学生的课（难度：简单）

创建如下所示的courses 表 ，有: student (学生) 和 class (课程)。

例如,表:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       | 
| B       | English    | 
| C       | Math       | 
| D       | Biology    | 
| E       | Math       | 
| F       | Computer   | 
| G       | Math       | 
| H       | Math       | 
| I       | Math       | 
+---------+------------+

编写一个 SQL 查询，列出所有超过或等于5名学生的课。

应该输出:

+---------+ 
| class   | 
+---------+
| Math    |
+---------+

---

作业四：交换工资（难度：简单）

创建一个 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  |
+----+------+-----+--------+

知识点：

1. UPDATE 更新操作
2. 条件判断 IF(expr1,expr2,expr3)
   如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF() 的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。
   

---

二、表联结

INNER JOIN、LEFT JOIN、RIGHT JOIN

参考：https://www.runoob.com/mysql/mysql-join.html
参考：https://www.runoob.com/w3cnote/sql-join-the-different-of-on-and-where.html

---

作业

作业五：组合两张表 （难度：简单）

在数据库中创建表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。

Person

Address

---

作业六：删除重复的邮箱（难度：简单）

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