Datawhale-MySQL-任务3-MySQL 基础 （二）- 表操作

学习内容

1. MySQL表数据类型
   在学习SQL语言的过程中，表的概念非常重要。表是关系数据库的核心，那么数据库中的表是什么？表是按行和列组织的数据集合。 在DBMS术语中，表称为关系，行称为元组。注意 ：表具有指定的列数，但可以包含任意数量的行。
   SQL数据类型
2. 用SQL语句创建表

   语句解释
   设定列类型 、大小、约束
   设定主键

** 语句解释**
表是存储在数据库中的数据集合。 表由列和行组成。 要创建新表，请使用具有以下语法的CREATE TABLE语句：

CREATE TABLE table_name(
     column_name_1 data_type default value column_constraint,
     column_name_2 data_type default value column_constraint,
     ...,
     table_constraint
);

创建新表所需的最低信息是表名和列名。
由table_name指定的表名在数据库中必须是唯一的。 如果创建的表的名称与已存在的表相同，则数据库系统将发出错误。
在CREATE TABLE语句中，指定以逗号分隔的列定义列表。每个列定义由列名，列的数据类型，默认值和一个或多个列约束组成。
列的数据类型指定列可以存储的数据类型。 列的数据类型可以是数字，字符，日期等。

列约束
列约束控制可以存储在列中的值的类型。 例如，NOT NULL约束确保列不包含NULL值。列可能有多个列约束。 例如，users表的username列可以同时具有NOT NULL和UNIQUE约束。

设定主键
如果约束包含多个列，则使用表约束。 例如，如果表的主键包含两列，则在这种情况下，必须使用PRIMARY KEY表约束。
3. 用SQL语句向表中添加数据

语句解释
多种添加方式（指定列名；不指定列名）

语句解释
创建新表后，您可能希望更改其结构，因为业务需求会发生变化。 要修改表的结构，请使用ALTER TABLE语句。 ALTER TABLE语句用于对现有表执行以下操作：使用ADD子句添加新列。使用MODIFY子句修改列的属性，例如：约束，默认值等。使用DROP子句删除列。

4. 用SQL语句删除表

   语句解释
   DELETE
   DROP
   TRUNCATE
   不同方式的区别

语句解释
随着数据库在应用过程不断的数据增加，有时需要从数据库中删除过时的冗余表或不用的表。 要删除表，可使用DROP TABLE语句。以下是DROP TABLE语句的语法。

DROP TABLE [IF EXISTS] table_name;

5. 用SQL语句修改表
   修改列名
   修改表中数据
   删除行
   删除列
   新建列
   新建行

作业

项目三

项目三：超过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:
学生在每个课中不应被重复计算。

创建表

CREATE TABLE courses (
STUDENT CHAR(2)  NOT NULL,
CLASS VARCHAR(255) NOT NULL
);

插入数据

INSERT INTO courses VALUES('A','Math');
INSERT INTO courses VALUES('B','English');
INSERT INTO courses VALUES('C','Math');
INSERT INTO courses VALUES('D','Biology');
INSERT INTO courses VALUES('E','Math');
INSERT INTO courses VALUES('F','Computer');
INSERT INTO courses VALUES('G','Math');
INSERT INTO courses VALUES('H','Math');
INSERT INTO courses VALUES('I','Math');
INSERT INTO courses VALUES('A','Math');

解题思路
由于表中有重复项，首先需要去重，然后在去重后的结果中统计class大于等于5的项，代码如下：

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 |

创建Salary_TBL表

CREATE TABLE Salary_TBL (
ID INT PRIMARY KEY NOT NULL,
NAME VARCHAR(255) NOT NULL,
SEX  VARCHAR(6) NOT NULL CHECK (SEX IN ('M', 'F')),
SALARY INT NOT NULL
);

插入数据

INSERT INTO Salary_TBL VALUES('1','A','M','2500');
INSERT INTO Salary_TBL VALUES('2','B','F','1500');
INSERT INTO Salary_TBL VALUES('3','C','M','5500');
INSERT INTO Salary_TBL VALUES('4','D','F','500');

解题思路
先使用UPDATE函数更改性别的值。

UPDATE Salary_TBL
SET sex=IF(sex='F','M','F');

然后查询

select * 
FROM Salary_TBL;