1.MySQL

一.单表查询

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