一、管理表记录分为：
- 插入表记录（向表里添加行）
插入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 |+-----------+--------+-------+
mysql> insert into studb.stuinfo setnumber="nsd211107",gender="boy";mysql> select * from studb.stuinfo;
# 查询tarena.employees中的name和employee_idmysql> 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")) ;
mysql> select * from studb.stuinfo;+-----------+--------+-------+| number | gender | name |+-----------+--------+-------+| nsd220310 | NULL | lili |...| NSD220309 | NULL | jim |+-----------+--------+-------+8 rows in set (0.04 sec)# 当gender性别为空时，赋值boymysql> 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)
# 删除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) |
