一.单表查询
sql
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `dept`
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`DEPTNO` bigint(2) PRIMARY KEY AUTO_INCREMENT COMMENT '表示部门编号,由两位数字所组成',
`DNAME` varchar(14) COMMENT '部门名称,最多由14个字符所组成',
`LOC` varchar(13) COMMENT '部门所在的位置'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of dept
-- ACCOUNTING:财务部
-- RESEARCH:调研部
-- SALES:销售部
-- OPERATIONS:运营部
-- ----------------------------
INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');
-- ----------------------------
-- Table structure for `emp`
-- ----------------------------
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`EMPNO` bigint(4) PRIMARY KEY AUTO_INCREMENT COMMENT '雇员的编号,由四位数字所组成',
`ENAME` varchar(10) COMMENT '雇员的姓名,由10位字符所组成',
`JOB` varchar(9) COMMENT '雇员的职位',
`MGR` bigint(4) COMMENT '雇员对应的领导编号,领导也是雇员',
`HIREDATE` date COMMENT '雇员的雇佣日期',
`SAL` double(7,2) COMMENT '基本工资,其中有两位小数,五位整数,一共是七位',
`COMM` double(7,2) COMMENT '奖金,佣金(销售才有)',
`DEPTNO` bigint(2) COMMENT '雇员所在的部门编号'
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of emp
-- CLERK:职员
-- SALESMAN:销售员
-- MANAGER:部门经理
-- ANALYST:分析员
-- PRESIDENT:总裁
-- ----------------------------
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');
-- ----------------------------
-- Table structure for `salgrade`
-- ----------------------------
DROP TABLE IF EXISTS `salgrade`;
CREATE TABLE `salgrade` (
`GRADE` bigint(11) PRIMARY KEY AUTO_INCREMENT COMMENT '工资等级',
`LOSAL` int(11) COMMENT '此等级的最低工资',
`HISAL` int(11) COMMENT '此等级的最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of salgrade
-- ----------------------------
INSERT INTO `salgrade` VALUES ('1', '700', '1200');
INSERT INTO `salgrade` VALUES ('2', '1201', '1400');
INSERT INTO `salgrade` VALUES ('3', '1401', '2000');
INSERT INTO `salgrade` VALUES ('4', '2001', '3000');
INSERT INTO `salgrade` VALUES ('5', '3001', '9999');
练习
#查询所有员工信息
select * from emp;
#查询每个员工的编号、姓名、职位
select empno,ename,job from emp;
#查询所有部门信息
select * from dept;
distinct关键字可以用于一列,也可以用于多列
练习;
#查询所有有员工的部门编号
select distinct deptno from emp;
#查询员工表中有哪些部门和职位
select distinct job,deptno from emp;
#对number型数据可以使用算数操作符创建表达式(+ - * /)
#对date型数据可以使用算数操作符创建表达式(+ -)
练习;
#查询所有员工的年薪
select ename,(sal+ifnull(comm,0))*12 from emp;
#查询所有员工的年薪(使用别名)
select ename,(sal+ifnull(comm,0))*12 年薪 from emp;
#查询每月都有500元的餐补和200元交通补助并且年底多发一个月工资的年薪
select ename,(sal+ifnull(comm,0))*13+700*12 年薪 from emp;
#演示date类型数据的运算:查询员工的雇佣日期加上10;
select hiredate+'10' from emp;
练习:
#查询所有有领导的员工;
select ename,mgr from emp where not mgr is null;
#查询所有员工的年薪((月薪+奖金)*12);
select ename,(sal + ifnull(comm,0))*12 from emp;
函数:ifnull(expr1,expr2)
如果expr1不是null,ifnull()返回expr1,否则它返回expr2
使用is null(is not null)运算符;
1,查询有奖金的员工信息;
select * from emp where comm is not null;
2,查询公司的老板;
select * from emp where mgr is null;
注意:
1、字符串和日期要用单引号扩起来
2、数字类型直接书写
3、字符串是大小写不敏感的,日期值是格式敏感的
4、字符串敏感,需要添加binary关键字
练习
#要求查询出基本工资高于1500的所有员工信息;
select * from emp where sal > 1500;
#查询名字叫scott的员工所从事的工作;
select ename,job from emp where ename='scott';
#查询1981年入职的员工信息.;
select * from emp where year(hiredate)=1981;
常用算术比较运算符:
1,=,!=,<>,>,>=,<,<=:等于和不等于
2,between ... and ...:在两值之间 (包含开始和结尾);
3,in(list):匹配列出的值;
4,like :匹配字符串模式;
5,is null:是否为空;
练习:
使用比较运算符
#查询1981年之后入职的员工信息
select * from emp where year(hiredate)>1981;
#查询年薪小于3w的员工;
select ename,(sal+ifnull(comm,0))*12 from emp where (sal+ifnull(comm,0))*12 < 30000;
#查询所有不是销售人员的员工信息;
select * from emp where comm is null;
使用between运算符
#查询工资在2000-3000之间的员工信息;
select * from emp where sal between 2000 and 3000;
#查询工资不在2000-3000之间的员工信息
select * from emp where sal not between 2000 and 3000;
#查询1981年入职的员工
select * from emp where year(hiredate)='1981';
使用in运算符
#查询工资为800或1600或3000的员工
select * from emp where sal in(800,1600,3000);
#查询工资不为800或1600或3000的员工
select * from emp where sal not in(800,1600,3000);
使用like运算符
like运算符必须使用通配符才有意义:
匹配单个字符:_; -> 1个
匹配任意多个字符:%; -> 0个、1个、多个
#查询出所有雇员姓名是以a开头的全部雇员信息。
select * from emp where ename like 'a%';
#查询出雇员姓名第二个字母是m的全部雇员信息。
select * from emp where ename like '_m%';
#查询出雇员姓名任意位置上包含字母a的全部雇员信息。
select * from emp where ename like '%a%';
and:如果组合的条件都是true,返回true.
or:如果组合的条件 之一是true,返回true.
not:如果下面的条件是false,返回true.
优先级规则: 比较运算符 > not > and > or
练习:
#查询姓名中有e或者a的员工姓名
select * from emp where ename like('%e%') or ename like('%a%')
#查询工资在1500~3000之间的全部员工信息
select * from emp where sal >= 1500 and sal <=3000;
#查询出职位是办事员(clerk)或者是销售人员(salesman)的全部信息,并且工资在1000以上.
select * from emp where (job='clerk' or job='salesman') and sal > 1000;
使用order by 子句将记录排序
asc: 升序,缺省
desc: 降序
order by 子句出现在select语句的最后
order by 可以使用别名
练习:
#查询所有员工信息,按照工资排序
select * from emp order by sal asc;
#查询所有员工信息,按照年薪降序排序;
select *,(sal+ifnull(comm,0))*12 from emp order by (sal+ifnull(comm,0))*12 desc;
#查询所有员工信息,按照部门升序排,如果部门编号相同按照薪资降序排序;
select * from emp order by deptno asc, sal desc;
#查询所有员工每个月支付的平均工资及总工资
select avg((sal+ifnull(comm,0))),sum(sal+ifnull(comm,0)) from emp;
#查询月薪在2000以上的员工总人数.
select count(ename) from emp where sal > 2000;
#查询员工最高工资和最低工资差距
select max(sal)-min(sal) from emp;
--
-- lower(str):返回字符串str变为小写字母的字符
-- upper(str):返回字符串str变为大写字母的字符
-- 把helloworld转换为全大写,全小写.;
select lower('helloworld') from dual;
select upper('HELLOWORLD') from dual;
select date_add(now() ,interval 60 day)from dual;
select date_sub(now(), interval 60 day)from dual;
select current_date(),current_time() from dual;
select format(12345.3434,2) from dual;
select date_format(now(),'%y年%m月%d日%H时%i分%s秒')from dual;
select str_to_date('2015/11/29','%Y/%m/%d')from dual;
二.多表查询
练习
#按照职位分组,求出每个职位的最高和最低工资
select job ,min(SAL),max(SAL)
from emp e
GROUP BY job
#查询出每一个部门员工的平均工资
select DEPTNO,AVG(sal)
from emp
group by deptno
#查询各个部门和岗位的平均工资
SELECT d.dname,e.job,avg(sal)
from emp e join dept d on e.DEPTNO = d.deptno
GROUP BY e.deptno
having avg(sal)
#查询平均工资高于2000的部门和其平均工资
select d.dname,avg(sal)
from emp e join dept d on e.deptno = d.deptno
GROUP BY e.DEPTNO
having avg(sal) > 2000
#查询在1980,1981,1982,1983年各进公司多少人
select year(hiredate),count(ename)
from emp
where year(hiredate) in('1980','1981','1982','1983')
group by YEAR(hiredate);
#查询各个管理人员下员工的平均工资,其中平均工资不能低于1300,不计算老板
select mgr ,avg(sal) from emp where mgr is not null group by mgr
having avg(sal)> 1300;
#需求:查询员工名称和其对应经理的名称.
select e.empno, e.ename, mgr.ename
from emp e join emp mgr on e.mgr = mgr.EMPNO
#需求:查询员工和其经理,如果没有经理,显示总经理
select e.empno, e.ename, IFNULL(mgr.ename,'总经理')
from emp e left join emp mgr on e.mgr = mgr.empno
#1、查询大于公司平均工资的员工姓名
select * from emp where SAL >
(select avg(sal) from emp)
#2、查询出工资比MARTIN还要高的全部雇员信息
select * from emp where sal >
(select sal from emp where ename = 'Martin')
#3,查询'Jones'的部门同事姓名
select * from emp where deptno =
(select deptno from emp where ename='jones')
#需求:查询工资等于部门经理的员工信息.
select * from emp where sal in(
select sal from emp where job = 'manager'
)
#需求:查询工资大于任意部门经理的员工信息.
select * from emp where sal > any(
select sal from emp where job = 'manager')
#需求:查询工资小于任意部门经理的员工信息.
select * from emp where sal < ANY
(select sal from emp where job ='manager')
#查询出每个部门的编号、名称、部门人数、平均工资:
#一般人sql 笛卡尔积 4 *15 = 60
select d.deptno,d.dname,count(e.empno),ifnull(avg(sal),0)
from dept d left join emp e on e.deptno = d.deptno
group by d.deptno,d.dname
#DBA的SQL 4* 3 = 12
select d.deptno,d.dname,ifnull(tp.c,0),ifnull(tp.ag,0)
FROM dept d left join
(select deptno, count(empno) c,avg(sal) ag
from emp group by deptno ) tp
on d.deptno = tp.deptno
insert into dept(dname,loc) values ('开发部','广州市');
insert INTO dept value(null,'测试部','广州市');
insert into dept value(null,'开发部','广州市'),(null,'测试部','广州市');
update dept
set
dname = '真,李经理'
where deptno = 41
delete from dept where deptno > 40
select job ,max(sal),min(sal)from emp group by job;
#如果现在要进行分组的话,则SELECT子句之后,只能出现分组的字段和集合函数,其他的字段不能出现:
#正确做法:
select job ,count(deptno)
from emp
GROUP BY job
#错误的做法:
SELECT deptno,job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;
#查询在80,81,82,83年各进公司多少人
select year(hiredate),count(ename)
from emp
where year(hiredate) in('1980','1981','1982','1983')
GROUP BY year(hiredate)
#使用表连接从多个表中查询数据
#需求:查询员工编号,员工名称,员工所属部门的编号和名称.
select e.empno,e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno
#查询员工的姓名,工资,所在部门的名称,以及工资的等级.
select e.ename,e.sal,d.dname,s.grade
from emp e,dept d,salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and hisal;
#需求:查询员工编号,员工名称,员工所属部门的编号和名称.
select e.empno, e.ename, e.deptno, d.dname
from emp e join dept d on e.deptno = d.deptno
#练习:
#1,查询出公司员工工资等级
select e.ename,e.sal,s.grade
from emp e join salgrade s on e.sal between s.losal and s.hisal
#2,查询出公司员工的姓名和所属部门
select e.ename , d.dname
from emp e join dept d on e.DEPTNO = d.DEPTNO
#3,查询所有有奖金的员工的姓名,部门,城市
select e.ename,d.dname,d.loc
from emp e join dept d on e.deptno = d.DEPTNO
where comm is not NULL;
#需求:查询每一个部门名称和总人数.
select d.dname, count(e.ename)
from emp e right join dept d on e.deptno = d.deptno
group by d.DNAME
#需求:查询出至少有一个员工的所有部门编号、名称,并统计出这些部门的平均工资、最低工资、最高工资。
select d.DEPTNO,d.DNAME,avg(sal),min(sal),max(sal)
from emp e right join dept d on e.deptno = d.deptno
group by d.deptno,d.dname
having count(e.ename) > 0
#查询员工的编号,名称和部门名称.
select e.empno,e.ENAME,d.dname
from emp e left join dept d on e.deptno = d.deptno
#需求:查询员工名称和其对应经理的名称.
select e.ename,MGR.ename
from emp e join emp mgr on e.mgr = MGR.empno
#需求:查询员工和其经理,如果没有经理,显示总经理
select e.ename,ifnull(MGR.ename,'总经理')
from emp e left join emp mgr on e.mgr = MGR.empno
#1,查询大于公司平均工资的员工姓名
select ename,sal
from emp
where sal>(
select avg(sal) from emp
)
#2,查询出工资比MARTIN还要高的全部雇员信息
select * from emp where sal >
(select sal from emp where ename = 'martin')
#3,查询'Jones'的部门同事姓名
select ename from emp where ename != 'jones' and deptno =
(select deptno from emp where ename = 'jones')
#查询平均工资高于公司平均工资的部门信息
select d.deptno,avg(sal)
from emp e join dept d on e.deptno = d.deptno
GROUP BY d.deptno
having avg(sal) >
(select avg(sal) from emp)
select deptno,avg(sal)
from dept join emp
using (deptno)
group by deptno
having avg(sal) >
(select avg(sal) from emp)
#查询出每个有员工部门的编号、名称、部门人数、平均工资:
select d.DEPTNO,d.dname,count(e.ename),ifnull(avg(sal),0)
from emp e join dept d on e.deptno = d.DEPTNO
group by d.deptno , d.dname
#分析性能:笛卡尔积数量
#可以先把每一个部门的编号,总人数,平均工资先查询出来.
select e.deptno , count(e.ename),ifnull(avg(sal),0)
from emp e
group by e.deptno
#再和dept表联合查询部门名称.
select d.deptno,d.dname,temp.count,temp.avg
from
(
select e.deptno , count(e.ename) count,ifnull(avg(sal),0) avg
from emp e
group by e.deptno
) temp
join dept d
on temp.deptno = d.deptno