增加
- insert into t1 values(1,2);
修改 - replace into t1 values(1,2);(主键或唯一键冲突,则先删除在插入)
- update t1 set a=1 where name=“zhai”;
*删除 - delete from t1;
- delete from t1 where a=1;
查找 - select * from t1;( * 是全列查找)
结果排序
- order by name, age desc;默认升序,desc 降序
where name like ‘w%’;
where name not like ‘z%’;
where age is not null;
group by 分组
在group by 后面不能用where过滤,用having过滤.
复合查询
- 多表查询
- select sal,grade,ename, bname from emp dept salfrade where emp.deptno = dept.deptno and (sal between lo and hi);
- 自连接(在同一张表连接查询)
- 显示员工FORD的上级领导的编号和姓名(mgr是员工领导的编号–empno)
- select empno, ename from emp where empno = (select mgr from emp where ename = ‘FORD’);
- select leader.empno, leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename=‘FORD’;(使用表的别名)
- 子查询(嵌套查询)
- 1.单行子查询(返回一条记录的子查询)
- 显示SMITH同一部门的员工(部门deptno)
- select * from emp where deptno = (select deptno from emp where ename=‘SMITH’);
- 2.多行子查询
- 查询和10号部门的工作相同的雇员的名字ename,岗位job,工资sal,部门号deptno,但是不包含10自己的
- select ename, job, sal, deptno from emp where job in(select distinct job from emp where deptno = 10) and deptno<>10;(distinct 去重)
- in all any关键字
- 3.多列子查询
- 查询和SMITH的部门deptno和岗位job完全相同的所有雇员,不含SMITH本人
- select ename from emp where (deptno, job) = (select deptno,job from emp where ename=‘SMITH’) and ename!=‘SMITH’;
- 4.在from中使用子查询(把子查询当做一个临时表)
- 1/显示高于自己部门平均工资的员工的姓名ename、部门deptno、工资sal、平均工资
- select avg(sal), deptno from emp group by deptno
- select ename, deptno, sal, avg(sal) from emp, (select avg(sal), deptno from emp group by deptno) tmp where emp.sal > avg(sal) and emp.deptno = tmp.deptno;
- 2/查找每个部门工资最高的人的姓名、工资、部门、最高工资
- select ename, sal, deptno, max(sal) from emp, (select max(sal) from emp group by deptno) tmp where emp.deptno = tmp.deptno and emp.sal = max(sal);
- 3/显示每个部门的信息(部门名dept.dname,编号emp.deptno,地址dept.loc)和人员数量
- select dept.dname emp.deptno, dept.loc from dept, (selct count(*) from emp group by deptno) tmp where dept.deptno = tmp.deptno;
- 5.合并查询
- union 去掉重复行
- union all 不去掉重复行
- 1.单行子查询(返回一条记录的子查询)