SQL语法——细节问题（AUTO_INCREMENT、临时表、TRUNCATE TABLE、LOAD DATA INFILE）

本文链接：https://blog.csdn.net/cold___play/article/details/124204316

AUTO_INCREMENT

AUTO_INCREMENT列具有以下属性：

AUTO_INCREMENT列的起始值为1，当您NULL向列中插入值或在INSERT语句中省略其值时，它会增加1 。
要获取最后生成的序列号，请使用LAST_INSERT_ID() 函数。我们经常使用后续语句的最后一个插入ID，例如，将数据插入表中。最后生成的序列在会话中是唯一的。换句话说，如果另一个连接生成序列号，则可以通过连接使用LAST_INSERT_ID()函数获取序列号。
如果在表中插入新行并为序列列指定值，则如果列中不存在序列号，则MySQL将插入序列号，如果已存在则发出错误。如果插入一个大于下一个序列号的新值，MySQL将使用新值作为起始序列号，并生成一个大于当前序列号的唯一序列号，以供下次使用。这会在序列中产生间隙。
如果使用UPDATE语句将AUTO_INCREMENT列中的值更新为已存在的值，则如果列具有唯一索引，MySQL将发出重复键错误。如果将AUTO_INCREMENT列更新为大于列中现有值的值，MySQL将使用下一行的最后一个插入序列号的下一个数字。例如，如果最后一个插入序列号为3，则将其更新为10，新行的序列号为4。
如果使用DELETE语句删除最后插入的行，MySQL可能会也可能不会重复使用已删除的序列号，具体取决于表的存储引擎。如果删除一行，MyISAM表不会重复使用已删除的序列号，例如，表中的最后一个插入ID是10，如果删除它，MySQL仍会生成下一个序列号，即新行的序列号为11。与MyISAM表类似，InnoDB表在删除行时不重用序列号。

临时表

MySQL临时表具有以下特殊功能：

使用CREATE TEMPORARY TABLE创建临时表。请注意，TEMPORARY关键字是在CREATE和TABLE关键字之间添加的。
当会话结束或连接终止时，MySQL会自动删除临时表。当然，您可以使用 DROP TABLE语句在不再使用它时显式删除临时表。
临时表仅可供创建它的客户端访问。不同的客户端可以创建具有相同名称的临时表而不会导致错误，因为只有创建临时表的客户端才能看到它。但是，在同一会话中，两个临时表不能共享相同的名称。
临时表可以与数据库中的普通表具有相同的名称。例如，如果创建一个名为employees临时表为示例数据库，现有的employees表变得不可访问。您针对employees表发出的每个查询现在都引用临时 employees 表。删除employees临时表时，employees表可用并可再次访问。

创建临时表：

CREATE TEMPORARY TABLE top10customers
SELECT p.customerNumber, 
       c.customerName, 
       ROUND(SUM(p.amount),2) sales
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY sales DESC
LIMIT 10;

删除临时表：

DROP TEMPORARY TABLE table_name;

TRUNCATE TABLE

MySQL TRUNCATE TABLE语句的语法如下：

TRUNCATE TABLE table_name;

如果您使用InnoDB表，MySQL将在删除数据之前检查表中是否存在任何可用的外键约束。以下是案例：

如果表有任何外键约束，则TRUNCATE TABLE语句将逐个删除行。如果外键约束具有DELETE CASCADE操作，则还会删除子表中的相应行。
如果外键约束未指定DELETE CASCADE操作，则TRUNCATE TABLE逐个删除行，并且当遇到子表中的行引用的行时，它将停止并发出错误。
如果表没有任何外键约束，则TRUNCATE TABLE语句将删除表并重新创建一个具有相同结构的新空表，这比使用DELETE语句（尤其是对于大表）更快更有效。

如果使用其他存储引擎，则TRUNCATE TABLE语句将丢弃并重新创建一个新表。

请注意，如果表具有AUTO_INCREMENT列，则TRUNCATE TABLE语句会将自动增量值重置为零。

此外，TRUNCATE TABLE语句不使用DELETE语句，因此，不会调用与表关联的DELETE触发器。

PRIMARY KEY与 UNIQUE KEY

PRIMARY KEY只能有一个，而且不能有NULL值
UNIQUE 可以有多个，而且可以有NULL值。

导入 CSV 文件到 MySQL Table

在导入文件之前，您需要准备以下内容：

准备好要导入文件数据的数据库表。
CSV文件中的数据与表的列数和每列中的数据类型相匹配。
帐户具有FILE和INSERT权限。

CREATE TABLE discounts (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL,
    amount DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (id)
);

以下 discounts.csv文件包含第一行作为列标题和其他三行数据:

id,title,expired_date,amount
1,"Spring Break 2014",20140410,20
2,"Back to School 2014",20140901,25
3,"Summer 2014",20140825,10

以下语句将discounts.csv文件中的数据导入discounts表中:

LOAD DATA LOCAL INFILE '/Users/acton_zhang/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

文件的字段由逗号表示， FIELD TERMINATED BY ','并用ENCLOSED BY '"' 指定的双引号括起来。

CSV文件的每一行都由指示的换行符终止LINES TERMINATED BY '\n'。

因为文件具有包含列标题的第一行，不应将其导入表中，因此我们通过指定IGNORE 1 ROWS选项忽略它。

如果在执行的过程中出现以下错误
在这里插入图片描述
我们可以使用如下命令查看配置：

show variables like '%secure%';

在这里插入图片描述
这个参数的值是null，说明mysql中就没有设置secure_file_priv这个选项。如果这个参数是个特定的文件路径，就说明文件只有在这个路径下才能将它导入导出到mysql。参数secure_file_priv的值及含义如下：

secure_file_priv值	含义
NULL	禁止文件的导入导出
‘’	（空字符串）允许所有文件的导入导出
一个特定的路径地址	只有该路径地址下的文件可以导入导出到mysql

解决此问题我们可以通过修改 my.cnf 配置文件：

#写在 my.cnf配置文件中
#允许导入文件
secure_file_priv=''

然后重启数据库.

现在我们再导入一次：
在这里插入图片描述