目录
一、简单查询
1.查询一个字段?
select 字段名 from 表名;
2.查询多个字段
select 字段名1,字段名2... from 表名;
3.查询全表数据
select * from 表名;
4.给查询的列起别名
select 列名 as 别名 from 表名;
别名是中文用单引号括起来
二、条件查询
条件运算符：=、<、>、>=、<=、!=
1.查询工资等于800的成员信息
select empno,ename from emp where sal = 800;
2.查询工资处于800到2000的成员信息
select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;
select empno,ename,sal from emp where sal between 800 and 2000;
between and 必须遵循左小右大
3.查询那些员工津贴为空
select empno,ename,sal,comm from emp where comm is null;
4.查询工作岗位是manager并且工资大与2500的员工信息
select empno,job,sal from emp where job = 'manager' and sal > 2500;
5.查询工作岗位是manager或salesman的员工
select empno,job,sal from emp where job = 'manager' or job = 'salesman';
6.查询工资大于2500，并且部门编号为10或20部门的员工（and优先级大与or）
select * from emp where sal>2500 and (deptno = 10 or deptno = 20);
7.查询工作岗位是manager和salesman的员工（in后面跟具体值）
select empno,job,sal from emp where job in ('manager','salesman');
8.查询名字中含有o的
select ename from emp where ename like '%o%';
9.查询名字以T结尾或者以k开始
select ename from emp where ename like '%T';
select ename from emp where ename like 'K%';
10.查询名字第二个字母是A的
select ename from emp where ename like '_A%';
三、排序
1.查询所有员工薪资，排序(默认升序)
select ename,sal from emp order by sal;
2.指定降序
select ename,sal from emp order by sal desc;
3.指定升序
select ename,sal from emp order by sal asc;
4.查询员工名字和薪资，要求按照薪资升序，如果薪资一样的话，再按照名字升序排列.
select ename,sal from emp order by sal asc,ename asc;
5.找出薪资在1250到3000之间的员工信息，按照薪资降序排列
select ename,sal from emp where sal between 1250 and 3000 order by sal desc;
四、单行处理函数
单行处理函数的特点:一个输入对应一个输出。
和单行处理函数相对的是:多行处理函数。(多行处理函数特点:多个输入，对应1个输出!)
1.lower转换小写
select lower(ename) as ename from emp;
2.upper转换大写
select upper(name) as NAME from t_student;
3.substr取子串（(substr(被截取的字符串，起始下标,截取的长度))
select substr(ename,1,1) as ename from emp;
4.length取长度
select length(ename) from emp;
5.trim去空格
select * from emp where ename = trim(' KING ');
6.str_to date将字符串转换成日期
7.date_format格式化目期
8.format设置千分位
9.round四舍五入
(输出124，0代表保留0个小数，1代表保留1个小数，-1四舍五入保留到十位数)
select round(123.56,0) as result from emp;
10.rand ()生成随机数
select rand() from emp;
select round(rand()*100,0) from emp;
11.ifnull 可以将null转换成一个具体值（ifnull(数据，被当作哪个值)）
只要有空参与的计算最终结果为空
计算每个员工年薪
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
12.case..when..then..when..then..else..end
当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%
select ename, job，(case job when ‘MANAGER' then sal*1.1 when ' SALESMAN’ then sal*1.5 else sal end) as newsal from emp;
五、分组函数（多行处理函数）
使用分组函数需要先分组，如果没有分组整张表默认为一组
1.找出最高工资
select max(sal) from emp;
2.计算最低工资
select min(sal) from emp;
3.计算工资和
select sum(sal) from emp;
4.计算平均工资
select avg(sal) from emp;
5.计算员工数量总和
select count(ename) from emp;
注意：
（1）分组函数自动忽略null
（2）分组函数count (*) 统计表中的总行数
count (具体字段) 统计该字段下所有不为null的。
（3）分组函数不能直接使用在where子句中
select
...
from
...
where
...
group by
...
having
...
order by
...
执行顺序：from->where->group by->select->order by
where运行时group by还没有执行
（4）所有分组函数可以组合在一起
六、分组查询
1.查询每个工作岗位的工资总和
按照工作岗位分组，求工资总和
select job,sum(sal) from emp group by job;
2.查询每个部门的最高薪资
按照部门编号分组，求每一组的最大值
select deptno,max(sal) from emp group by deptno;
3.查询每个部门不同工作岗位的最高薪资
部门与岗位联合分组
select deptno,job,max(sal) from emp group by deptno,job;
4.找出每个部门最高薪资，要求显示最高薪资大与3000的
使用having可以对分完组之后的数据进一步过滤。having不能单独使用，
having不能代替where， having必须和group by联合使用。
where和having优先选择where
select deptno,max(sal) from emp group by deptno having max(sal) > 3000;(效率较低)
select deptno,max(sal) from emp where sal > 3000 group by deptno;(效率较高)
5.查询每个部门平均薪资，要求显示平均薪资大与2500
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;
6.找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排。
select job,avg(sal) as avgsal from emp where job != 'manager'
group by job having avg(sal) > 1500 order by avgsal desc;
七、distinct关键字
去除重复记录
select distinct job from emp;
distinct出现在job , deptno两个字段之前，表示两个字段联合起来去重。
select distinct job,deptno from emp;
八、连接查询
从一张表中单独查询，称为单表查询。
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。
这种跨表查询，多张表联合起来查询数据，被称为连接查询。
根据表连接的方式分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
全连接
1.连接两个表进行查询
select ename,dname from emp,dept where emp.deptno = dept.deptno;（匹配此数较多，效率较低）
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;（起个别名）
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;(与上述相同，最新版本)
2.内连接
2.1等值连接
查询每个员工所在部门，显示员工名和部门名
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;（条件是等值关系）
2.2非等值连接
找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal>=losal and e.sal<=s.hisal;(条件不是等值关系)
2.3自连接（一张表看作两张表）
查询员工的上级领导,要求显示员工名和对应的领导名（员工的领导编号等于领导的员工编号）
select a.ename as '员工名',b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;
3.外连接
3.1右外连接(将join右边看作主表，将其全部查询出来，捎带查询关联左边的表)
select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;(outer可以省略)
3.2左外连接（将join左边看作主表）
select e.ename,d.dname from dept d left outer join emp e on e.deptno = d.deptno;(outer可以省略)
3.3查询每个员工的上级领导，要求显示所有员工的名字和领导名
select a.ename as '员工名',b.ename as '领导名' from emp a left join emp b on a.mgr = b.empno;
4.连接三张表
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件
...
案例:找出每个员工的部门名称以及工资等级还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级?
select
e.ename,e.sal,d.dname,s.grade ,l.ename
from
emp e
join
dept d
on
e.deptno = d.deptno
join
salgrade s
on
sal between losal and hisal
left join
emp l
on
e.mgr = l.empno;
九、子查询
select语句中嵌套select语句，被嵌套的select语句称为子查询。
2.1、where中的子查询语句
找出比最低工资高的员工姓名和工资
select ename,sal from emp where sal > (select min(sal) from emp);
2.2、from中的子查询语句
from后面的子查询，可以将子查询的查询结果当做一张临时表.
案例：找出每个岗位的平均薪资等级
第一步：找出每个岗位的平均工资
select job,avg(sal) from emp group by job;
第二步：将以上查询结果看作一张表
select
t.*,s.grade
from
(select job,avg(sal) as avgsal from emp group by job) as t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
2.3、select后面的子查询
注意:对于select后面的子查询来说，这个子查询只能一次返回1条结果,多于1条,就报错了
案例：找出每个员工的部门名称，要求显示员工名，部门名
select e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
十、union合并查询结果集
案例：查询工作岗位是manager和salesman的员工
select ename,job from emp where job = 'manager' or job = 'salesman';
select ename,job from emp where job = 'manager'
union
select ename,job from emp where job = 'salesman';
union的效率要高一些。对于表连接来说，每连接一次新表，则匹配的次薮满足笛卡尔积，成倍的翻。
但是union可以减少匹配的次数。在减少匹配次数的情况下，还可以完成两个结果集的拼接。
注意：union在进行结果集合并的时候，要求两个结果集的列数相同，
结果集合并时列和列的数据类型也要相同
十一、limit
limit作用：将查询结果集的一部分取出来。通常使用在分页查询当中。
完整用法:limit startIndex, length
startIndex是起始下标,length是长度
缺省用法: limit 5;这是取前5。
4.1按照薪资降序，取出排名在前五的员工
select
ename,sal
from
emp
order by
sal desc
limit 0,5;
注意：mysql中limit是在order by之后执行
4.2取出工资排名在（3~5）名的员工
select
ename,sal
from
emp
order by
sal desc
limit
2,3;
4.3分页
每页显示3条记录
每页显示pagesize条记录
第pageNo页: limit (pageNo - 1) * pagesize, pagesize
十二、关于DQL语句的总结
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
执行顺序?
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit
注意：下附各表建表语句以及插入数据。
-- Table structure for `dept`
-- ----------------------------
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`deptno` int(4) NOT NULL,
`dname` varchar(50) DEFAULT NULL,
`loc` varchar(50) DEFAULT NULL,
PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of dept
-- ----------------------------
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` int(4) NOT NULL,
`ENAME` varchar(10) DEFAULT NULL,
`JOB` varchar(9) DEFAULT NULL,
`MGR` int(4) DEFAULT NULL,
`HIREDATE` date DEFAULT NULL,
`SAL` double(7,2) DEFAULT NULL,
`COMM` double(7,2) DEFAULT NULL,
`DEPTNO` int(2) DEFAULT NULL,
PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of emp
-- ----------------------------
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` int(11) DEFAULT NULL,
`LOSAL` int(11) DEFAULT NULL,
`HISAL` int(11) DEFAULT NULL
) 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');
-- ----------------------------
-- Table structure for `t_student`
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`no` int(11) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`sex` char(1) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES ('1', 'zhangsan', 'm', '20', 'zhangsan@qq.com');
3159
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
