四、MySQL基础系列笔记之SQL DML语句

作者: 温新

分类: 【MySql】

阅读: 1651

时间: 2020-10-09 13:23:11

DML操作是指对数据库中表记录的操作,主要包括表记录的增删改查。

1、insert-插入记录

语法

insert into 表名 (字段1,字段2,...) values (值1,值2,...);

案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 向emp表中插入1条数据</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">insert</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">into</span> emp (name,salary) <span style="box-sizing: border-box;color: rgb(219, 45, 32)">values</span> (<span style="box-sizing: border-box;color: rgb(242, 176, 29)">'lisi'</span>,<span style="box-sizing: border-box;color: rgb(163, 78, 143)">1000</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">1</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">row</span> affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.10</span> sec)</span>

不指定字段名,但values后值的顺序要和字段的顺序一致

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">insert</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">into</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">values</span>(<span style="box-sizing: border-box;color: rgb(242, 176, 29)">'wangwu'</span>,<span style="box-sizing: border-box;color: rgb(163, 78, 143)">800</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">1</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">row</span> affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.04</span> sec)</span>

插入多条记录

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">insert</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">into</span> emp (name,salary) <span style="box-sizing: border-box;color: rgb(219, 45, 32)">values</span> (<span style="box-sizing: border-box;color: rgb(242, 176, 29)">'lily'</span>,<span style="box-sizing: border-box;color: rgb(163, 78, 143)">1000</span>) ,(<span style="box-sizing: border-box;color: rgb(242, 176, 29)">'aimi'</span>,<span style="box-sizing: border-box;color: rgb(163, 78, 143)">900</span>);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span> rows affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.03</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">Records: <span style="box-sizing: border-box;color: rgb(163, 78, 143)">2</span>  Duplicates: <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span>  <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Warnings</span>: <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span></span>

2、update-更新记录

语法

update 表名 set 字段1=值1,字段2=值2... [where 条件];

案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 修改lisi的工资为2000</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">update</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">set</span> salary=<span style="box-sizing: border-box;color: rgb(163, 78, 143)">2000</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">where</span> name=<span style="box-sizing: border-box;color: rgb(242, 176, 29)">'lisi'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">1</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">row</span> affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.07</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">Rows matched: <span style="box-sizing: border-box;color: rgb(163, 78, 143)">1</span>  <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Changed</span>: <span style="box-sizing: border-box;color: rgb(163, 78, 143)">1</span>  <span style="box-sizing: border-box;color: rgb(219, 45, 32)">Warnings</span>: <span style="box-sizing: border-box;color: rgb(163, 78, 143)">0</span></span>

3、delete-删除记录

语法

delete from 表名 [where 条件];

案例

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 删除名字为李四的记录</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">delete</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">from</span> emp <span style="box-sizing: border-box;color: rgb(219, 45, 32)">where</span> name = <span style="box-sizing: border-box;color: rgb(242, 176, 29)">'lisi'</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">Query</span> OK, <span style="box-sizing: border-box;color: rgb(163, 78, 143)">1</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">row</span> affected (<span style="box-sizing: border-box;color: rgb(163, 78, 143)">0.05</span> sec)</span>

命令总结

<span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 1、插入数据</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">insert</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">into</span> 表名 (字段1,字段2,...) <span style="box-sizing: border-box;color: rgb(219, 45, 32)">values</span> (值1,值2,...);</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 2、更新数据</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">update</span> 表名 <span style="box-sizing: border-box;color: rgb(219, 45, 32)">set</span> 字段1=值1,字段2=值2,...</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(205, 171, 83)"># 3、删除记录</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(219, 45, 32)">delete</span> <span style="box-sizing: border-box;color: rgb(219, 45, 32)">from</span> 表名 [<span style="box-sizing: border-box;color: rgb(219, 45, 32)">where</span> 条件];</span>

我是小白,期待和优秀的你一起同行!

小白

2020年10月09日

请登录后再评论