MYSQL学习笔记六

一、插入数据（INSERT）

1.1 INSERT语法

INSERT 语句有两种语法形式，分别是 INSERT…VALUES 语句和 INSERT…SET 语句。

1.1.1 INSERT…VALUES语句

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

<表名>：指定被操作的表名。
<列名>：指定需要插入数据的列名。若向表中的所有列插入数据，则全部的列名均可以省略，直接采用 INSERT<表名>VALUES(…) 即可。
VALUES 或 VALUE 子句：该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。

1.1.2 INSERT…SET语句

INSERT INTO <表名>
SET <列名1> = <值1>,
    <列名2> = <值2>,
    …

INSERT两种形式说明如下：

• 使用 INSERT…VALUES 语句可以向表中插入一行数据，也可以插入多行数据；
• 使用 INSERT…SET 语句可以指定插入行中每列的值，也可以指定部分列的值；
• INSERT…SELECT 语句向表中插入其他表的数据。
• 采用 INSERT…SET 语句可以向表中插入部分列的值，这种方式更为灵活；
• INSERT…VALUES 语句可以一次插入多条数据。

1.2 向表中全部字段添加值

创建测试表，命令如下：

CREATE TABLE testtable (
ID INT ,
fullname CHAR(40) NOT NULL,
age INT NOT NULL
) ;

eg：
在 testtable 表中插入一条新记录，ID 值为 1，fullname 值为“name1”，age 值为 18。

INSERT INTO testtable 
 (ID ,fullname ,age )
VALUES(1,'name1',18);

运行结果如下：

**注意：**虽然使用 INSERT 插入数据时可以忽略插入数据的列名称，若值不包含列名称，则 VALUES 关键字后面的值不仅要求完整，而且顺序必须和表定义时列的顺序相同。如果表的结构被修改，对列进行增加、删除或者位置改变操作，这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称，就不会受到表结构改变的影响。

1.3 向表中部分字段添加值

为表的指定字段插入数据，是在 INSERT 语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。
eg：
在 testtable 表中插入一条新记录，ID 值为2，fullname 值为“name1”。

INSERT INTO testtable 
(fullname ,age )
VALUES('name1',18);

运行结果如下：

1.4 使用INSERT INTO FROM语句复制数据

从一个或多个表中取出数据，并将数据数据存储到另一个表中。

INSERT INTO…SELECT…FROM

SELECT 子句返回的是一个查询到的结果集
INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

在数据库 firstdb中创建一个与 testtable表结构相同的数据表 testtablenew，创建表的sql脚本如下：

CREATE TABLE testtablenew  (
  ID INT ,
  fullname CHAR(40) NOT NULL,
  age INT NOT NULL
) 

上述命令执行结果如下：

从 testtable表中查询所有的记录，并将其插入 testtablenew表中。输入的 SQL 语句和执行结果如下所示。
SQL语句如下：

INSERT INTO testtablenew
(ID,fullname,age)
SELECT ID,fullname,age
FROM testtable

执行结果如下：

二、更新数据（UPDATE）

2.1 UPDATE语法

使用 UPDATE 语句的语法格式为：

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

<表名>：用于指定要更新的表名称。
SET 子句：用于指定表中要修改的列名及其列值。其中，每个指定的列值可以是表达式，也可以是该列对应的默认值。如果指定的是默认值，可用关键字 DEFAULT 表示列值。
WHERE 子句：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
ORDER BY 子句：可选项。用于限定表中的行被修改的次序。
LIMIT 子句：可选项。用于限定被修改的行数。
注意：修改一行数据的多个列值时，SET 子句的每个值用逗号分开即可。

2.2 修改数据

在 testtablenew表中，更新所有行的 age字段值为 20

UPDATE testtablenew
SET age=20;

执行结果如下：

2.3 根据条件修改数据

在 testtablenew表中，更新 ID值为 1 的记录，将 age字段值改为 15，将 fullname字段值改为“testname”,SQL行结果如下所示。

UPDATE testtablenew
SET fullname='testname',age=15
WHERE ID=1;

运行结果如下：

2.4 更新数据时，使用LIMIT控制更新数据的行数。

在 testtablenew表中，将符合条件的前2条数据的fullname字段值改为“001”,SQL行结果如下所示。

UPDATE testtablenew
SET fullname='001'
LIMIT 2

执行结果如下：

注意：LIMIT 也可以结合where等子句进行使用。LIMIT 2的意思是对满足条件的前2条数据进行处理。
有时候，会见到LIMIT 1,2的写法，在更新语句中需要注意的是，不能直接这样使用，如果需要使用，可以简单看一下

三、删除数据（DELETE）

3.1 DELETE语法

使用 DELETE 语句删除数据的语法格式为：

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

<表名>：指定要删除数据的表名。
ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。

3.2 删除表全部数据

eg：
删除 testtablenew 表中的全部数据， SQL 语句如下：

DELETE FROM testtablenew ;

注意：在不使用 WHERE 条件的时候，将删除所有数据。删除数据的时候一定要注意。

3.3 根据条件删除表中数据

在 testtablenew 表中，删除 ID为 2 的记录，在上一步已经将该表数据清空，这里需要先给该表插入几条数据，插入数据命令如下：

INSERT INTO testtablenew( ID ,fullname,age)VALUES(1,'001',18);
INSERT INTO testtablenew( ID ,fullname,age)VALUES(2,'002',18);
INSERT INTO testtablenew( ID ,fullname,age)VALUES(3,'003',18);
INSERT INTO testtablenew( ID ,fullname,age)VALUES(4,'004',18);
INSERT INTO testtablenew( ID ,fullname,age)VALUES(5,'005',18);
INSERT INTO testtablenew( ID ,fullname,age)VALUES(6,'006',18);
INSERT INTO testtablenew( ID ,fullname,age)VALUES(7,'007',18);

数据表数据截图如下：

删除上表数据中ID为2的记录，其SQL语句如下：

DELETE FROM testtablenew WHERE ID=2

运行结果如下：

3.4 TRUNCATE

3.4.1 TRUNCATE 语法

TRUNCATE  <表名>    ##表名指的是删除的表

TRUNCATE 的作用是删除表中的所有数据，不能与where一起使用。
eg：
删除testtablenew 表中的所有数据，其命令如下：

TRUNCATE  testtablenew 

注意：如果testtablenew 的主键是自增，使用TRUNCATE 对表数据清空，清空的数据不占有主键的值。