SQL之表格的创建与修改以及更新与删除

1.CREATE 子句

在SQL中， CREATE 关键字用来进行数据库与表格等内容的创建。

利用CREATE为指定的数据库创建表时，需要指出表格的名称、字段名以及该字段的类型与约束。

在未使用 USE 确认要操作的数据库时，还需要用句点法标出数据库的名字，即数据库名.表名。

示例代码：

CREATE TABLE study_info.`student`(
    `id` INT PRIMARY KEY NOT NULL,
    `name` VARCHAR(10) NOT NULL,
    `gender` VARCHAR(2) DEFAULT '保密',
    `class_id` INT NOT NULL
);

1.1 TABLE

首先，与创建数据库不同。创建表格时，在CREATE子句后需要使用 TABLE 关键字。

1.2 表名称

在这之后需要为表创建一个名称。不同于 Python 在语法上的严谨，SQL 支持字母、数字、符号、中文等任意数据作为表格的名称。

但是，只推荐使用字母、数字、下划线命名，否则在操作时会出现意想不到的错误。

1.3 反引号

SQL 支持使用关键字作为表格或字段的名称。为了做出区分，对于表格名、字段名等数据可以利用反引号扩起来。这样可以保证SQL在读取内容时，不会出错。

1.4使用数据库名对表进行限定

若MySQL中存储了多个数据库，需要使用 USE 关键字，指定要操作的数据库。或者，在创建或查找表时，可以使用数据库名对表进行限定，即数据库名.表名。

1.5编写字段信息

设置好了表名，接下来需要为该表添加字段。

添加字段的部分需要用括号包裹，且SQL规定一个数据表至少包含一列。

定义字段是，需要说明字段的名称、数据类型同时也可以为字段添加约束。

1.6 字段名

字段名的规则与表名相同。

同样的为了避免字段名中出现关键字混淆的问题，还需要使用反引号包裹内容。

1.7 关于数据类型

在数据库中，表中的字段都会指定一个数据类型。

SQL 中提供多种数据类型，但最常用的依旧如下图所示。

比如， bit 类型又叫做位数据，它由 0 或 1 组成，可以使用它代替布尔值的False 与 True。

1.8 设定数据类型

在字段名后空一格，即可书写类型关键字。

可以为SQL的数据类型设置长度，只需要把表示长度数字用括号放置在关键字后。比如：VARCHAR(10)表示该字段只接受最大长度为10的字符

若不设置，则默认接受该类型能存储的最大长度。

1.9 约束

除此以外，我们还可以对列设定一些约束规则。

这些约束帮在后续插入数据时，避免录入无效的值。

SQL 中提供多种约束方式，接下来依次说明：

设置主键。若将 id 设置为该表的主键，则可以使用 PRIMARY KEY 进行约束。这样当在此列插入重复的数据时，SQL会进行报错提醒。
非空。若要限制必须为某个字段插入数据，可以使用NOT NULL 约束此列。这样必须为此列插入数据，而不接受空值。
设置默认值。若某一列的大部分数据都相同，可以通过 DEFAULT 为该列设定一个默认值。

比如，为 gender（性别）列设置默认值为“保密”，若该列没有接受到数据，则自动填入“保密”代替空值。

注意：

一个字段的名称、类型、约束之间要以空格分开；

而不同的字段间需要使用逗号分隔。

例题：为 study_info 这个数据库创建班级信息表 class_info

CREATE TABLE study_info.`class_info`(
`id` INT PRIMARY KEY NOT NULL,
`name` VARCHAR(255) NOT NULL,
`open_date` DATE NOT NULL
);

2.ALTER子句

有时表格创建完成后，发现需要做一些调整、修改。修改表的结构、或某行数据的值。

比如，想要给学生表添加一个生日（birth）列；或者想修改某一列的名称。

通过ALTER 可以对表格的结构进行添加、修改、删除。

ALTER TABLE 可以发起一次对表的修改，我们需要在该关键字后制定表的名称与归属。

在这个过程中，可以分别使用ADD、CHANGE、MODIFY、DROP等关键字分别进行增删改操作。

2.1 添加 ADD

首先是为现有表格添加一个新的字段。关键字 ADD 可以为表添加新的列。

添加时同样需要提供字段名、类型，若必要的话还需要设置约束。

例如：

1	ALTER TABLE study_info.`student` ADD `birthday` DATETIME;

例题：为 study_info 库的 student 表添加 age 字段，数据类型为 INT 长度为 3 约束为非空。

1	ALTER TABLE study_info.`student` ADD `age` INT(3) NOT NULL;

2.2 修改之CHANGE与MODIFY

若想对现存在的字段进行修改，SQL 提供 CHANGE 与 MODIFY 两个关键字。

它们的区别在于是否要对字段名进行修改，除此以外它们的结构相同。

CHANGE 关键字用于对字段的名称与结构进行修改。

若不需要修改名称，这里使用MODIFY即可。

例如：

ALTER TABLE 
    study_info.`student` 
CHANGE
    `birthday` `birth` DATE NOT NULL;

例题：对study_info库中student 表的 gender 字段进行修改，数据类型修改为 VARCHAR，长度为10，并设置默认值为空。

1	ALTER TABLE study_info.`student` MODIFY `gender` VARCHAR(10) DEFAULT NULL;

2.3 删除DROP

可以通过 DROP 直接删除整个表，或表中的某一列数据。

注意：

这一操作极度的危险！

在绝大部分情况下是不会直接执行删除命令的。

数据是无价的，存储海量数据可能需要几年的时间，但执行一次删除命令只需要几秒。

所以谨慎操作 DROP 命令，防止“删库跑路”的悲剧。

删除某一个字段，只需在 ALTER 子句中加入 DROP ，并将要删除的字段名紧跟其后：

1	ALTER TABLE study_info.`student` DROP birthday;

若删除的是一张完整的表，可以直接使用 DROP TABLE 制定要删除的表:

1	DROP TABLE study_info.`student`;

若要删除整个数据库，也可以使用：

1	DROP DATABASE 数据库名;

3.INSERT子句

成功的创建好表格后，需要为表格插入数据。在 SQL 中，通过 INSERT 关键字可以实现插入操作。

INSERT 提供了两种方法——全列插入与部分列插入。

3.1 全列插入

当要按照列定义的顺序，插入一整行数据，可以使用全列插入。

关键字 INSERT INTO 用于对表格中数据的插入。

有些文档中 INTO 会被省略，但会造成在部分数据库中发生错误！

所以，强烈建议使用标准写法 INSERT INTO。

例如：

1	INSERT INTO study_info.`student` VALUES (0, '小刘', '女', 1);

全列插入时，插入顺序要与创建字段的顺序相同。数据也要保证类型一致。

3.2 部分列插入

在创建student 表时， gender 列设置了默认值“保密”。

在插入数据时，可以不对该列赋值，此时数据库就会自动插入“保密”。

像这样，若只想对指定的列赋值，可以使用部分列插入。

INSERT INTO study_info.`student` 
(`id`, `name`, `class_id`)
VALUES
(1, '小张', 2);

在这里，只是为id、name 与 class_id 这些不能为空的字段赋值。

3.3 插入多行数据

大多数情况下数据表需要插入多行数据。

在SQL中，只需要把每一行要插入的数据依次放在VALUES后即可。

注意，每一行的数据都要分别放置在括号中，且用逗号隔开。

-- 整列多行插入
INSERT INTO study_info.`student` VALUES 
(0, '小刘', '女', 1),
(1, '小张', '男', 2),
(2, '小明', '女', 1);

-- 部分列多行插入
INSERT INTO study_info.`student` 
(`id`, `name`, `class_id`)
VALUES
(0, '小刘', 1),
(1, '小张', 2),
(2, '小明', 1);

3.4 类型与约束

插入了错误的类型与不符合约束的数据会造成插入失败。

比如，id 字段为主键时，该列就不能出现重复的值，否则会报错。

小技巧：

在终端中输入 DESC 指定的表名 可以快速查看表的结构。

4.UPDATE子句

对表中某一行数据进行修改，称为更新（update）某一值。

此时需要使用SQL中的 UPDATE 子句。

4.1利用主键确定行

观察一下学生表，若要修改小童的性别为“女”、班级编号设置为2，需要哪些步骤呢？

第一步，确认要操作的表，在这里是 student 表；

第二步，确认要修改的是这张表的哪一行，即确认小童所在的行主键为 5；

第三步，对该行指定的字段进行修改，即修改gender字段与 class_id 字段

使用 UPDATE 关键字即可发起一次行的更新，为了与表名进行区分依旧推荐使用大写。

在 UPDATE 后放置要操作的表，当表名与关键字有冲突时需要加入反引号。

1	UPDATE student SET gender = '女', class_id = 2 WHERE id=5;

4.2 SET

大部分情况下，只对某行数据的指定字段进行修改。指定要修改的字段，需要使用 SET 关键字。为了做区分，推荐使用大写字母编写。

4.3 修改指定字段的值

要修改指定字段的值。类似于Python中为变量赋值，只需要以“字段名 = 修改后的值”格式书写语句即可。指定多个字段时，以逗号隔开。

注意，修改后的值要符合该字段的数据类型与约束，否则会失败报错

比如，上例中将 gender 中的值改为“女”，class_id 中的值改为2。

4.4筛选多行的条件

也可以同时对多行进行操作。

若要将 student 表中所有性别为保密的人修改为“未填写”，可以设置筛选条件为 gender = ‘保密’。

也就是说，所有满足筛选条件的行，都会进行修改。

例如：

1	UPDATE student SET gender = '未填写' WHERE gender = '保密';

4.5 删除行

当然，除了对某行数据的更新，也可对指定条件的行直接删除。

DELETE 子句提供了此功能，完整格式为：

DELETE FROM 表名 WHERE 条件;

例如:删除学生编号为1，4的数据。

1	DELETE FROM student WHERE id IN (1, 4);

4.7 再次强调数据安全

数据是无价的，所以在对数据进行修改的修改操作上需要小心谨慎。

在编写 INSERT、UPDATE、DELETE 代码时，为了避免误操作造成的数据丢失，在pymysql中：游标提供了一种事务处理机制，即上述语句需要手动提交（commit）后才可执行。

在使用pymysql时，若游标执行的SQL命令包含 INSERT 子句、UPDATE 子句、DELETE 子句 中的某个字句。需要进行提交操作。

此时，使用execute执行这类DML语句时，执行的结果不会直接同步到数据库中，只返回该操作影响的行数。

若想让“修改语句”完全生效，需要使用连接的 commit 方法。

commit 不需要设置参数，它会把之前执行的所有SQL提交给数据库，使数据库同步更新。

# 导入pymysql模块
import pymysql
conn = pymysql.connect(
    host = "127.0.0.1",
    port = 3306,
    user = "root",
    password = "123456",
    database ="study_info")
cur = conn.cursor()
SQL = '''
UPDATE student SET gender = '女', class_id = 2 WHERE id=5;
'''
# 执行SQL语句，执行后并不会直接同步数据库
ret = cur.execute(SQL)
print(f"对{ret}行数据进行了操作")

# 提交操作结果，提交后才会同步数据库
conn.commit()