目录
查询加强
where加强
order by加强
group by
分页查询
总结
多表查询(重点)
笛卡尔集及其过滤
自连接
子查询
子查询当作临时表
all/any
多列子查询
#先创建三张表
#第一张表
CREATE TABLE dept(deptno MEDIUMINT NOT NULL DEFAULT 0,dname VARCHAR(20) NOT NULL DEFAULT '',loc VARCHAR(13) NOT NULL DEFAULT ''
);
INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSHTON');
SELECT * FROM dept;
#第二张表
CREATE TABLE emp(
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,-- 编号
ename VARCHAR(20) NOT NULL DEFAULT "",-- 名字
job VARCHAR(9) NOT NULL DEFAULT "", -- 工作
mgr MEDIUMINT UNSIGNED,-- 上级编号
hiredate DATE NOT NULL,-- 入职日期
sal DECIMAL(7,2) NOT NULL,-- 工资
comm DECIMAL(7,2),-- 奖金
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 -- 部门编号
);
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1990-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1991-2-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7968,'1991-2-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7968,'1991-9-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1991-5-1',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1991-6-9',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1991-4-19',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1991-9-8',1500.00,NULL,30),(7900,'JAMES','CLERK',7698,'1991-12-3',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1991-12-3',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1991-1-23',1300.00,NULL,10);
SELECT * FROM emp;
#第三张表
CREATE TABLE salgrade(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,losal DECIMAL(17,2) NOT NULL, -- 该级别的最低工资hisal DECIMAL(17,2) NOT NULL -- 该级别的最高工资
);
INSERT INTO salgrade VALUES(1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999)
SELECT * FROM salgrade;
查询加强
where加强
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- 查询加强
#使用where子句如何插叙1992.1.1后入职的员工
SELECT * FROM empWHERE hiredate > '1992-01-01'
#如何使用like操作符(模糊查询)-- %: 表示0 到多个任意字符 _: 表示单个任意字符-- 如何显示首字符为s的员工的姓名和工资
SELECT ename,sal FROM empWHERE ename LIKE 'S%'-- 如何显示首字符为s的员工姓名和工资
SELECT ename,sal FROM empWHERE ename LIKE '__O%'-- 如何显示没有上级的雇员的情况
SELECT * FROM empWHERE mgr IS NULL; -- 判断一个值是不是为NULL,用IS,不用 =
-- 查询表的结构
DESC emp
order by加强
-- 使用order by子句
-- 如何按照工资从低到高的顺序[升序],显示雇员的信息
SELECT * FROM empORDER BY sal;
-- 按照部门号升序工资降序排序,显示雇员信息
SELECT * FROM empORDER BY deptno ASC , sal DESC;
group by
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- mysql 表查询 - 加强
-- (1)显示每种岗位的雇员总数,平均工资
SELECT COUNT(*) ,AVG(empno),job FROM emp GROUP BY job;
-- (2)显示雇员总数,以及获取补助的雇员数 count(某一列) 如果该列的值是null,是不会统计的
SELECT COUNT(*),COUNT(comm) FROM emp;
-- 统计没有获得补助的雇员数量
SELECT COUNT(*),COUNT(IF(comm IS NULL , 1,NULL)) FROM emp;
-- 显示管理者的总人数
SELECT COUNT(IF(job = 'MANAGER',1,NULL)),job FROM emp GROUP BY job;
-- 显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal)FROM emp;
分页查询
1.按雇员的id号升序取出,每页显示3条记录,请分别显示 第1页 ,第2页 , 第3页
2.基本语法: select...limit start,rows
上面表示从start +1 行开始取,取出rows行,start从0开始计算
select ... limit 0,2 表示从第0+1行开始取,取出2行
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- 分页查询
-- 按雇员的id号升序取出,每页显示3条记录,请分别显示 第1页 ,第2页 , 第3页#第一页
SELECT * FROM empORDER BY empnoLIMIT 0, 2
#第二页
SELECT * FROM empORDER BY empnoLIMIT 2, 2
-- 推到一个公式
SELECT * FROM empORDER BY empnoLIMIT 每一页显示的记录数*(页数-1),每一页显示的记录数-- 按雇员的empno号降序取出,每页显示5条记录,请分别显示 第3页 ,第5页, 对应的sql语句
-- 第三页
SELECT * FROM empORDER BY empno ASCLIMIT 10,5;
-- 第五页
SELECT * FROM empORDER BY empno ASCLIMIT 20,5;总结
如果select语句同时包含有group by,havng ,limit,order by ,那么他们的顺序是 (1)group by ,(2)having,(3)order by,(3)limit
应用案例:请统计各个部门的平均工资,并且是大于1000的,而且按照平均工资从高到低排序,取出两行记录
-- 请统计各个部门的平均工资,并且是大于1000的,并且按照平均工资从高到低排序
-- 取出前两行记录
SELECT AVG(sal),deptno FROM empGROUP BY deptnoHAVING AVG(sal)>1000ORDER BY AVG(sal) DESCLIMIT 0,2;多表查询(重点)
笛卡尔集及其过滤
直接来举例子吧,还是上面的数据集
显示雇员名,雇员工资及所在部门的名字
先分析一下
1.雇员名,雇员工资都在上面的emp表中
2.所在部门dept部门当中
3.我们需要对emp和dept查询
SELECT * FROM emp,dept 这就是我们的笛卡尔集
这条指令出来时52行,52行咋出来的呢?
其实是我们的emp的行乘以dept的行的出来的,就是emp的每一行和dept的每一行相互匹配
我们看看是不是哈!!!
下面是我们创建的emp表,一共13行
下面是我们的dept表,一共是4行,和上面的emp表相互匹配,就会得出我们的52行了!!
我们需要加上过滤条件where语句就可以筛选出我们想要的数据来了
注意(1)
注意(2)
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
-- 显示雇员名,雇员工资及所在部门的名字-- SELECT * FROM emp,deptSELECT ename,sal,dept.deptnoFROM emp,deptWHERE emp.deptno=dept.deptno-- 如何显示部门号为10的部门名,员工名和工资
SELECT dname,ename,sal,emp.deptnoFROM emp,deptWHERE emp.deptno = 10 AND emp.deptno=dept.deptno;-- 显示各个员工的姓名,工资,及其工资的级别
SELECT * FROM emp,salgradeWHERE sal BETWEEN losal AND hisal自连接
将同一张表看作两张表
子查询
子查询是指嵌入到其他sql语句中的select语句,页交嵌套查询
单行子查询:只返回一行数据的子查询语句
请思考: 如何显示与SMITH同一部门的所有员工.
#多行子查询
-- 多行子查询返回多行数据的子查询,使用关键字 in
-- 练习: 如何查询和部门10的工作相同的雇员的名字,岗位,工资,部门名,但是不含10的
子查询当作临时表
all/any
在多行子查询中使用all操作符
请思考:显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
#请思考:显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
#写法一:
SELECT * FROM emp;
SELECT MAX(sal)FROM empGROUP BY deptnoHAVING deptno = 30
SELECT ename,sal,deptnoFROM empWHERE sal>(SELECT MAX(sal)FROM empGROUP BY deptnoHAVING deptno = 30)
#写法二:
SELECT ename,sal,deptnoFROM empWHERE sal>ALL(SELECT sal FROM emp WHERE deptno = 30) 请思考:显示工资比部门30的其中一个员工的工资高的员工的姓名,工资和部门号
#请思考:显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
SELECT ename,sal,deptnoFROM empWHERE sal>ANY(SELECT sal FROM emp WHERE deptno = 30)
多列子查询
多行子查询则是指查询返回多个列数据的子查询语句
请思考如何查询与smith的部门和岗位完全相同所有雇员(并不含smith本人)
(字段1 , 字段2) = (select 字段1 , 字段2 from ...)
前面的字段1必须和后面的字段1相匹配 , 前面的字段2和后面的字段2相匹配
