mysql 增删查改

1.insert 语句

insert 语句可以向数据表写入记录，可以是一条记录，也可以是多条记录。

insert into 表名(字段1，字段2，.......）
values(值1，值2，.....)

insert into t_dept(deptno,dname,loc)
values(60,"产品部","北京"),(10,"行政部","北京")

#向技术部添加一条员工记录
insert into t_emp
(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(8001,"lili","salesman",8000,"1988-12-20",2000,null,(select deptno from t_dept where dname="技术部"))

insert 语句方言

mysql 的insert语句还有一种方言语法

insert into 表名 set 字段1=值1,字段2=值2,.....;

insert into t_emp 
set empno = 8002,ename="jack",job="saleman",mgr=8000,hiredate="1985-3-14",sal=2500,comm=null,deptno=50;

2.ignore关键字

ignore 关键字会让insert 只插入数据库不存在的记录。

insert [ignore] into 表名 ....;

3.update语句

update 语句用于修改表的记录

update [ignore] 表名
set 字段1=值1，字段2=值2，....
[where 条件1....]
[order by ....]
[limit ......]

#把每个员工的编号和上司的编号+1，用order by 子句完成
update t_emp set empno=empno+1,mgr=mgr+1
order by empno DESC;

#把月收入前三名的员工底薪减100元，用limit子句完成
update t_emp
set sal=sale-100
order by sal+ifnull(comm,0) desc
limit 3;

update语句的表连接

相关的子查询效率非常低，所以可以利用表连接的方式来改造update语句

update 表1 join 表2 on 条件
set 字段1 = 值1，字段2=值2，....;

表连接的update语句可以修改多张表的记录
update的表连接可以演变成下面的样子：

update 表1 , 表2 
set 字段1 = 值1，字段2=值2，....
where 连接条件;

#把allen调往research部门，职务调整为analyst
update t_emp e join t_dept d
set e.deptno=d.deptno,e.job="analyst",d.loc="北京"
where e.ename="allen" and d.dname="research"

#把底薪低于公司平局底薪的员工，底薪增加150元
update t_emp e join
(select avg(sal) as avg from t_emp) t
on e.sal<t.avg
set e.sal=e.sal+150;

update的表连接既可以是内连接，又可以是外连接

update 表1 [left|right] join 表2 on 条件
set 字段1=值1，字段2=值2，.....;

#把没有部门的员工，或者sales部门低于2000元底薪的员工，都调往20部门
update t_emp e left join t_dept d 
on e.deptno=d.deptno
set e.deptno=20
where e.deptno is null or
(d.dname="sales" and e.sal<2000)

4.delete语句

delete语句用于删除记录，语法如下

delete [ignore] from 表名
[where 条件1，条件2，.....]
[order by ......]
[limit ......];

使用ignore关键字，有外键限制时，也可以删除。

#删除10部门中，工龄超过20年的员工记录。
delete from t_emp 
where deptno=10 and datediff(now(),hiredate)/365>=20;

#删除20部门中工资最高的员工记录。
delete from t_emp 
where deptno=20 
order by sal+ifnull(comm,0) desc
limit 1;

delete 语句的表连接

因为相关子查询效率非常低，所以我们可以利用表连接的方式来改造delete语句

delete 表1,.... from 表1 join 表2 on 条件
[where 条件1，条件2，....]
[order by .....]
[limit .....];

#删除sales部门和该部门的全部员工记录。
delete e,d
from t_emp e join t_dept d 
on e.deptno=d.deptno
where d.dname="sales";

#删除每个低于部门平均底薪的员工记录
delete e
from t_emp e join
(select deptno,avg(sal) as sal from t_emp group by deptno) t
on e.deptno=t.deptno and e.sal<t.sal;

#删除员工king和他的直接下属的员工记录，用表连接实现
delete e,t
from t_emp e join 
(select empno from t_emp where ename="king") t
on e.mgr=t.empno;

delete语句的表连接既可以是内连接，也可以是外连接

delete 表1，..... from 表1 [left|right] join 表2 
on 条件 ....;

#删除sales部门的员工，以及没有部门的员工
delete e
from t_emp e left join t_dept d
on e.deptno=d.deptno
where d.dname="sales" or e.deptno is null;

delete语句是在事务机制下删除记录，删除记录之前，先将要删除的记录保存到日志文件里，然后再删除记录。
truncate语句再事务机制之外删除记录，速度远超过delete语句。

truncate table 表名