查询加强
1)日期直接比较;2)模糊查询增强;3)表结构查询;4)order by 多列条件排序;
测试代码
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM slagrade;
-- 日期比较
-- 查找1992.1.1后入职的工作
SELECT * FROM emp
WHERE hiredate > '1992-01-01'; -- 比较时,日期格式要一致1-1与01-01
SELECT * FROM emp;
-- 模糊查询
-- %:表示0到多个字符 _:表示单个字符
-- 显示首字符为S的员工姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE 'S%';
-- 显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE '__O%';
-- 显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
-- 查询表结构 selectinc.sql
DESC emp;
-- order by
-- 按照工资从低到高的顺序显示雇员信息
SELECT * FROM emp
ORDER BY sal ASC; -- 默认ASC 从低到高升序
-- 按照部门号升序雇员的工资降序排列显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC, sal DESC; -- desc从高到低降序
分页查询
# 基本语法
SELECT ... LIMIT start, rows
#表示从start1行开始取,取出rows行,start 从0开始计算
测试代码
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM slagrade;
-- 日期比较
-- 查找1992.1.1后入职的工作
SELECT * FROM emp
WHERE hiredate > '1992-01-01'; -- 比较时,日期格式要一致1-1与01-01
SELECT * FROM emp;
-- 模糊查询
-- %:表示0到多个字符 _:表示单个字符
-- 显示首字符为S的员工姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE 'S%';
-- 显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE '__O%';
-- 显示没有上级的雇员的情况
SELECT * FROM emp
WHERE mgr IS NULL;
-- 查询表结构 selectinc.sql
DESC emp;
-- order by
-- 按照工资从低到高的顺序显示雇员信息
SELECT * FROM emp
ORDER BY sal ASC; -- 默认ASC 从低到高升序
-- 按照部门号升序雇员的工资降序排列显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC, sal DESC; -- desc从高到低降序
-- 按雇员的id号升序取出,每页显示三条记录,分别显示第1页,第1页,第3页;
-- 第一页
SELECT * FROM emp
ORDER BY empno
LIMIT 0 , 3; -- 从第0+1页开始取
-- 第二页
SELECT * FROM emp
ORDER BY empno
LIMIT 3 ,3;
-- 第三页
SELECT * FROM emp
ORDER BY empno
LIMIT 6 ,3;
-- 第四页
SELECT * FROM emp
ORDER BY empno
LIMIT 9 ,3;
-- 公式
-- select * from emp
-- order by empno
-- limit 每页行数*(第几页-1),每页行数
分组函数和分组子句加强
测试代码
-- 显示每种岗位的雇员总数、平均工资
SELECT job, FORMAT(AVG(sal),2) ,COUNT(*) FROM emp
GROUP BY job;
-- 显示雇员的总数和有补助的雇员数
SELECT COUNT(*) AS '雇员总数',COUNT(comm) AS '获得补助的雇员数' FROM emp; -- count(n)如果n为null则不会统计数量
-- 统计没有获得补助的雇员数
SELECT COUNT(IF(comm IS NULL,1,NULL)) AS '没有获得补助的雇员数' FROM emp;
-- 显示管理者的总人数
SELECT COUNT(DISTINCT mgr) AS '管理者总人数' FROM emp;
-- 显示雇员工资的最大差额
SELECT (MAX(sal) - MIN(sal)) AS '工资最大差额' FROM emp;
SELECT * FROM emp;
多子句查询
★多子句查询语句顺序
SELECT column1, column2, column3...from table_name
group by column
having conditon
order by column
limit start, rows;
测试代码
-- 统计各个部门(group by)的平均工资(avg)
-- 并且要求平均工资要大于1000(having),按照平均工资从高到低排序(order by)
-- 取出前两行记录(limit)
SELECT deptno, FORMAT (AVG(sal),2) AS '平均工资' FROM emp
GROUP BY deptno
HAVING AVG(sal) >1000
ORDER BY AVG(sal) DESC
LIMIT 0 , 2;
多表查询
基本介绍
多表查询是指基于两个和两个以上的表查询,在实际应用中查询单个表可能不能满足需求,需要使用到多个表进行数据查询。
测试代码
★多表查询的条件不能少于表的个数-1,否则会出现笛卡尔集!
#多表查询
-- 显示雇员名,雇员工资及所有在部门的名字【笛卡尔集】
-- 两表一起查询:
SELECT * FROM emp, dept;
-- 1)从第一张表中取出每一行与第二张表的每一行组合并返回结果;
-- 2)一共返回的记录数为第一张表的行数*第二张表的行数
-- 3)多表查询默认处理方式的结果成为笛卡尔积
-- 4)★进行过滤
SELECT ename, sal, dname FROM emp, dept
WHERE emp.`deptno` = dept.`deptno`; -- 过滤条件
-- 显示部门为10的部门名、员工名和工资
SELECT emp.`deptno`,dname,ename,sal FROM emp,dept
WHERE emp.`deptno` = dept.`deptno` AND emp.`deptno` = 10;
-- 显示各个员工的姓名、工资、及工资级别
SELECT ename, sal, grade FROM emp,salgrade
WHERE sal BETWEEN losal AND hisal;
-- 显示雇员名,雇员工资及所在部门的名字,并按部门排序(降序排序)
SELECT ename,sal,dname,emp.deptno FROM emp,dept
WHERE emp.`deptno` = dept.`deptno`
ORDER BY emp.deptno DESC;
自连接
自连接是指在同一张表的连接查询【将同一张表看做两张表】
1)把同一张表当作两张表使用;
2)需要给表取别名,表名 表的别名;
3)如果列明不明确可以指定列的别名。
# 多表查询的自连接
-- 显示公司员工姓名和上级名字
SELECT worker.`ename` AS '职工',boss.`ename` AS '领导' FROM emp worker,emp boss
WHERE worker.`mgr` = boss.`empno`;
多行子查询
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询;单行子查询,是指只返回一行数据的子查询语句;多行子查询,指返回多行数据的子查询,使用关键字in;
# 子查询
-- 显示SMITH的部门编号
SELECT deptno FROM emp
WHERE ename = 'SMITH';
-- 显示与SMITH同一部门的所有员工
SELECT * FROM emp
WHERE deptno= (
SELECT deptno FROM emp
WHERE ename = 'SMITH');
-- 多行子查询
-- 查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号,但是不含10自己的雇员
-- 10号部门的工作岗位(当作子查询结果使用)
SELECT DISTINCT job FROM emp
WHERE deptno = 10;
-- 和部门10的工作相同的雇员的名字、岗位、工资、部门号
SELECT ename, job, sal,deptno FROM emp
WHERE job IN (
SELECT DISTINCT job FROM emp
WHERE deptno = 10)
AND deptno != 10; -- 不含10自己的雇员
子查询当作临时表使用
# 查看ecs_goods表的有关信息
SELECT goods_id, cat_id, goods_name, shop_price FROM ecs_goods;
# 查看ecs_goods表中的商品价格最高的价格,并根据类别进行分组类别id(临时表)
SELECT cat_id, MAX(shop_price) FROM ecs_goods
GROUP BY cat_id;
SELECT goods_id, temp.cat_id, goods_name, shop_price
FROM (SELECT cat_id, MAX(shop_price) AS max_price FROM ecs_goods
GROUP BY cat_id) temp ,ecs_goods -- 临时表别名temp
WHERE temp.cat_id = ecs_goods.cat_id
AND temp.max_price = ecs_goods.shop_price;
在多行子查询中使用all、any操作符
-- 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal,deptno FROM emp
WHERE sal > ALL( -- 比所有高,比最高的高
SELECT sal FROM emp
WHERE deptno = 30);
-- 显示工资比部门30 的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal,deptno FROM emp
WHERE sal > ANY( -- 比其中一个高,比最低的高
SELECT sal FROM emp
WHERE deptno = 30);
多列子查询
多列子查询是指查询返回多个列数据的子查询语句。
-- 多列子查询
-- 查询与SMITH的部门和岗位完全相同的所有雇员(并且不包含smith本人)
-- 1.查询smith的部门和岗位
SELECT deptno, job FROM emp
WHERE ename = 'ALLEN';
-- 2.把1步骤当作子查询并且使用多列子查询的语法进行匹配
SELECT * FROM emp
WHERE (deptno, job) = (
SELECT deptno, job FROM emp
WHERE ename = 'ALLEN')
AND ename != 'ALLEN';
-- 查询与tom语文、数学、英语成绩相同的学生
SELECT * FROM student
WHERE (chinese, english, math) = (
SELECT chinese, english, math FROM student
WHERE `name` = 'tom')
AND `name` != 'tom';
练习代码
-- 查询每个部门(group by)工资高于(>)本部门平均工资(avg)的人的资料
-- 1.查询部门平均值
SELECT deptno, FORMAT(AVG(sal)) FROM emp
GROUP BY deptno;
-- 2.查询高于本部门的每个人
SELECT ename, sal, temp.avg_sal, emp.deptno FROM emp , (
SELECT deptno, AVG(sal) AS avg_sal FROM emp
GROUP BY deptno) temp
WHERE emp.`deptno` = temp.deptno AND emp.`sal` > temp.avg_sal;
-- 每个部门最高工资的雇员信息
-- 查询每个部门的最高工资
SELECT deptno, MAX(sal) FROM emp
GROUP BY deptno;
SELECT ename, sal, temp.max_sal, emp.deptno FROM emp, (
SELECT deptno, MAX(sal) AS max_sal FROM emp
GROUP BY deptno) temp
WHERE emp.`deptno` = temp.deptno AND emp.`sal` = temp.max_sal;
-- 查询每个部门(group by)的部门名(dept表)、编号(emp和dept)、地址(dept)和人员数量(emp)
-- 1.每个部门的人员数量
SELECT deptno, COUNT(ename) AS people FROM emp
GROUP BY deptno;
-- 2.把1作为临时表
SELECT dname, dept.deptno, dept.`loc`, people FROM dept, (
SELECT deptno, COUNT(ename) AS people FROM emp
GROUP BY deptno) temp
WHERE dept.`deptno` = temp.deptno;
-- 第二种写法, 表.* 表示把表中的所有信息都打出来
-- 在多表查询中,当多个表的列不重复时,才可以直接写出列明
SELECT temp.*, dname,loc FROM dept, (
SELECT deptno, COUNT(ename) AS people FROM emp
GROUP BY deptno) temp
WHERE dept.`deptno` = temp.deptno;