MySql数据库·管理表记录

一、管理表记录分为：

• 插入表记录（向表里添加行）
  插入1条或多条记录给所有列或指定列赋值
• 更新表记录（修改行中列的值）
  修改所有列表胡哦这符合条件列的值
• 删除表记录（删除表里的行）
  加条件删除和批量删除

二、语法格式：

插入行：

• 插入1行或多行
  INSERT INTO 库名.表名 VALUES (值列表1),(值列表2),(值列表3)...;
• 插入指定表头1行或多行数据
  INSERT INTO 库名.表名(字段1，字段2，字段3...) VALUES (值列表1),(值列表2),(值列表3)...;
• 使用set命令插入行
  INSERT INTO 库名.表名 SET 字段1=值1,字段2=值2...;
• 使用select 查询结果插入行(查询结果表头个数要和insert into 命令表头个数相同 且数据类型相同)
  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查询结果插入行

# 查询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修改行中列的数据

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

# 当gender性别为空时，赋值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)