知识总结:

1.SQL语句
DDL DML DQL DCL TPL
2.DQL
where
order by
group by having
嵌套(in any some all union [all]);
3.函数
函数使用
===================================================
```mysql
create tab;e users(
uid int(4),
uname varchar(20),
udept varchar(10),
ubirthday date,
usal float(8,2),
ujob varchar(20),
uptime date
)character set utf8;
查询本月过生日的员工,设计一个通用的语句, 不要用11月判断
select * from users where month(ubirthday) = month(now());

Emp员工表
empno    ename    job mgr hiredate sal comm deptno
DEPT部门表
部门编号    部门名称    坐落城市
deptno        dname            loc
找出部门10中所有精力和部门20中的所有办事员的详细资料
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job = 'CLERK';
找出部门10中所有精力和部门20中所有办事员,既不是经理又不是办事员,但其薪资>=2000的所有雇员的资料
select * from emp where deptno = 10 and job = 'MANAGER' or deptno = 20 and job = 'CLERK' 
or job not in('MANAGER','CLERK') and sal >= 2000;
找出不收取佣金或收取的佣金低于500的雇员
select * from emp where comm is null or comm < 500;
函数练习
显示正好为6个字符的雇员姓名
select ename from emp where length(ename) = 6;
显示所有雇员的姓名的前三个字符
select ename, substr(ename, 3) from emp;
显示所有雇员的姓名,用a替换所有'A'
select ename, replace(ename,'A', 'a') from emp;
显示不带有R的雇员姓名
select ename from emp where ename not like '%R%'
显示只有瘦子目录大写的所有雇员的姓名
找出遭遇35年之前受雇的雇员
显示所有雇员的姓名以及满10年服务年限的日期
查询平均工资大于2900的部门中的所有员工
select *from emo where deptno in (select deptno from emp group by deptno having avg(sal>2900);
查询工资比ALLEN多的所有员工
select * from emp where sal > (select sal from emp where ename = 'ALLEN');
查询薪金高于公司平均薪金的所有员工姓名 ,部门编号, 具体薪资
select ename, empno, sal from emp where sal > (select avg(sal) from emp);
列出与scott从事相同工作的所有员工信息
select * from emp where job = ( select job from emp where ename = 'SCOTT');
查询薪金大于部门30中的员工最高薪金的所有员工的姓名, 薪金和部门编号
select ename, sal, dempno from emp where sal > 
 (select max(sal) from emp where deptno = 30;
查询在部门sales工作的员工的姓名
select * from emp where deptno = (select deptno from deptno where dname = 'sales');
分组练习
显示每种工作的人数
select job count(empno) from emp group by job;
显示工作人数大于3的工作的平均工资
select empno,avg(sal) as AvgSalary from emp group by job having count(empno) > 3;
显示出经理不有几种不同的工资
select sal from emp where job = 'MANAGER' group by sal;
select distince sal from emp where job = 'MANAGER';