初始MySQL(四)(查询加强练习,多表查询)

目录

查询加强

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 emp
       WHERE hiredate > '1992-01-01'
#如何使用like操作符(模糊查询)
         -- %: 表示0 到多个任意字符              _: 表示单个任意字符
         -- 如何显示首字符为s的员工的姓名和工资
SELECT ename,sal FROM emp
       WHERE ename LIKE 'S%'

-- 如何显示首字符为s的员工姓名和工资
SELECT ename,sal FROM emp
       WHERE ename LIKE '__O%'
       
-- 如何显示没有上级的雇员的情况
SELECT * FROM emp
       WHERE mgr IS NULL; -- 判断一个值是不是为NULL,用IS,不用 = 
-- 查询表的结构
DESC emp

order by加强

-- 使用order by子句
-- 如何按照工资从低到高的顺序[升序],显示雇员的信息
SELECT * FROM emp
     ORDER BY sal;
-- 按照部门号升序工资降序排序,显示雇员信息
SELECT * FROM emp
     ORDER 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 emp
        ORDER BY empno
        LIMIT 0, 2
#第二页
SELECT * FROM emp
        ORDER BY empno
        LIMIT 2, 2
-- 推到一个公式
SELECT * FROM emp
        ORDER BY empno
        LIMIT 每一页显示的记录数*(页数-1),每一页显示的记录数

-- 按雇员的empno号降序取出,每页显示5条记录,请分别显示 第3页 ,第5页, 对应的sql语句
-- 第三页
SELECT * FROM emp
         ORDER BY empno ASC
         LIMIT 10,5;
-- 第五页
SELECT * FROM emp
         ORDER BY empno ASC
         LIMIT 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 emp
        GROUP BY deptno
        HAVING AVG(sal)>1000
        ORDER BY AVG(sal) DESC
        LIMIT 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,dept

SELECT ename,sal,dept.deptno
        FROM emp,dept
        WHERE emp.deptno=dept.deptno

-- 如何显示部门号为10的部门名,员工名和工资
SELECT dname,ename,sal,emp.deptno
       FROM emp,dept
       WHERE emp.deptno = 10 AND emp.deptno=dept.deptno;

-- 显示各个员工的姓名,工资,及其工资的级别
SELECT * FROM emp,salgrade
         WHERE sal BETWEEN losal AND hisal
     

自连接

将同一张表看作两张表

子查询

子查询是指嵌入到其他sql语句中的select语句,页交嵌套查询

单行子查询:只返回一行数据的子查询语句

请思考: 如何显示与SMITH同一部门的所有员工.

#多行子查询
-- 多行子查询返回多行数据的子查询,使用关键字 in
-- 练习: 如何查询和部门10的工作相同的雇员的名字,岗位,工资,部门名,但是不含10的

子查询当作临时表

all/any

在多行子查询中使用all操作符

请思考:显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号

#请思考:显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
#写法一:
SELECT * FROM emp;
SELECT MAX(sal)
           FROM emp
           GROUP BY deptno
           HAVING deptno = 30
SELECT ename,sal,deptno
            FROM emp
            WHERE sal>(SELECT MAX(sal)
           FROM emp
           GROUP BY deptno
           HAVING deptno = 30)
#写法二:
SELECT ename,sal,deptno
          FROM emp
          WHERE sal>ALL(
          SELECT sal FROM emp WHERE deptno = 30)     

请思考:显示工资比部门30的其中一个员工的工资高的员工的姓名,工资和部门号

#请思考:显示工资比部门30的所有员工的工资高的员工的姓名,工资和部门号
SELECT ename,sal,deptno
          FROM emp
          WHERE sal>ANY(
          SELECT sal FROM emp WHERE deptno = 30)  

多列子查询

多行子查询则是指查询返回多个列数据的子查询语句

请思考如何查询与smith的部门和岗位完全相同所有雇员(并不含smith本人)

(字段1 , 字段2) = (select 字段1 , 字段2 from ...)

前面的字段1必须和后面的字段1相匹配 , 前面的字段2和后面的字段2相匹配