四、MySQL基础系列笔记之SQL DML语句
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日
请登录后再评论