一、管理表記錄分為：

• 插入表記錄（向表里添加行）
  插入1條或多條記錄給所有列或指定列賦值
• 更新表記錄（修改行中列的值）
  修改所有列表胡哦這符合條件列的值
• 刪除表記錄（刪除表里的行）
  加條件刪除和批量刪除

二、語法格式：

插入行：

• 插入1行或多行
  INSERT INTO 庫名.表名 VALUES (值列表1),(值列表2),(值列表3)...;
• 插入指定表頭1行或多行數(shù)據(jù)
  INSERT INTO 庫名.表名(字段1，字段2，字段3...) VALUES (值列表1),(值列表2),(值列表3)...;
• 使用set命令插入行
  INSERT INTO 庫名.表名 SET 字段1=值1,字段2=值2...;
• 使用select 查詢結(jié)果插入行(查詢結(jié)果表頭個(gè)數(shù)要和insert into 命令表頭個(gè)數(shù)相同 且數(shù)據(jù)類型相同)
  INSERT INTO 庫名.表名(字段1，字段2...) (SELECT查詢語句)

更新行：

• 批量修改（不加篩選條件）
  UPDATE 庫名.表名 SET 字段名1=值1,字段名2=值2...;
• 條件修改（加篩選條件）
  UPDATE 庫名.表名 SET 字段名1=值1,字段名2=值2... WHERE 篩選條件;

刪除行：

• 批量刪除（不加篩選條件）
  DELETE FROM 庫名.表名;
• 條件刪除（加篩選條件）
  DELETE FROM 庫名.表名 WHERE 篩選條件;

三、使用示例：

• 不指定表頭名插入多行

mysql> insert into studb.stuinfo values 
("nsd211101" , "bob","girl"),
("nsd211102" , "jim","boy"),
("nsd211103","lucy","girl");

mysql> select  * from studb.stuinfo;
+-----------+------+--------+
| number    | name | gender |
+-----------+------+------+
| nsd211101 | bob  | girl   |
| nsd211102 | jim  | boy    |
| nsd211103 | lucy | girl   |
+-----------+------+-------+
3 rows in set (0.00 sec)

• 指定表頭名插入多行

mysql> insert into studb.stuinfo(number,name)
values ("nsd220310","lili"),("nsd220319","lilei");

mysql> select  * from studb.stuinfo;
+-----------+--------+-------+
| number    | gender | name  |
+-----------+--------+-------+
| NSD220301 | girl   | lucy  |
| NSD220309 | boy    | jim   |
| UID       | man    | john  |
| nsd       | NULL   | jerry |
| nsd220310 | NULL   | lili  |
| nsd220319 | NULL   | lilei |
+-----------+--------+-------+

• set命令插入行

mysql> insert into studb.stuinfo set
number="nsd211107",gender="boy";
mysql> select  * from studb.stuinfo;

• select查詢結(jié)果插入行

# 查詢tarena.employees中的name和employee_id
mysql> select name,employee_id from tarena.employees 
where name in ("lucy","lili","bob") ;
+------+------+
| name | uid  |
+------+------+
| lucy |  134 |
| lili |  135 |
| bob  |  136 |
+------+------+
3 rows in set (0.00 sec)

# 將查詢到的name和employee_id插入到tarena.user的uid和name中
mysql> insert into tarena.user(uid,name) (
  select employee_id,name from tarena.employees 
  where name in ("lucy","lili","bob")
) ;

• update修改行中列的數(shù)據(jù)

mysql> select * from studb.stuinfo;
+-----------+--------+-------+
| number    | gender | name  |
+-----------+--------+-------+
| nsd220310 | NULL   | lili  |
...
| NSD220309 | NULL   | jim   |
+-----------+--------+-------+
8 rows in set (0.04 sec)

# 當(dāng)gender性別為空時(shí)，賦值boy
mysql> update studb.stuinfo set gender="boy" 
where gender is null;

mysql> select  * from studb.stuinfo;
+-----------+--------+-------+
| number    | gender | name  |
+-----------+--------+-------+
| nsd220319 | boy    | lilei |
...
| NSD220309 | boy    | jim   |
+-----------+--------+-------+
8 rows in set (0.00 sec)

• delete刪除表中行

# 刪除gender性別為boy的行
mysql> delete  from  studb.stuinfo where gender="boy";

mysql> select  * from studb.stuinfo;
+-----------+--------+------+
| number    | gender | name |
+-----------+--------+------+
| NSD220301 | girl   | lucy |
| UID       | girl   | john |
+-----------+--------+------+
2 rows in set (0.00 sec)