MySQL查询结果翻译 mysql查询语言

一、简单了解一下 mysql 的 sql 类型：

1、数据定义语言 DDL：Create、Drop、Alter 操作。用于定义库和表结构的。

2、数据操纵语言 DML：insert、update、delete。对行记录进行增删改操作。

3、数据查询语言 DQL：select。用于查询数据的。

4、数据控制语言 DCL：grant、revoke、commit、rollback。控制数据库的权限和事务。

二、DQL：SELECT子语句查询

2.1、SELECT查询加强

-- 使用 where 子句
-- 如何查找 1992.1.1 后入职的员工?
SELECT * FROM emp
    WHERE hiredate > '1992-01-01'

-- 如何使用 like 操作符(模糊)
-- %: 表示 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;

-- ■ 查询表结构
DESC emp

-- 使用 order by 子句
-- 如何按照工资的从低到高的顺序[升序]，显示雇员的信息?
SELECT * FROM emp
    ORDER BY sal 
-- 按照部门号升序而雇员的工资降序排列 , 显示雇员信息?
SELECT * FROM emp
    ORDER BY deptno ASC , sal DESC;

2.2、分页查询

SELECT ...

        LIMIT start ，rows

表示从start+1开始，取出rows行，start是从0开始的

-- 分页查询
-- 按雇员的 id 号升序取出， 每页显示 3 条记录，请分别显示 第 1 页，第 2 页，第 3 页

-- 第 1 页
SELECT * FROM emp
    ORDER BY empno
    LIMIT 0, 3; 

-- 第 2 页
SELECT * FROM emp
    ORDER BY empno
    LIMIT 3, 3; 

-- 第 3 页
SELECT * FROM emp
    ORDER BY empno
    LIMIT 6, 3;

-- 推导一个公式

SELECT * FROM emp

        ORDER BY empno

        LIMIT 每页显示记录数 * ( 第几页 -1) , 每页显示记录数

-- 应用案例：请统计各个部门 group by 的平均工资 avg，
-- 并且是大于 1000 的 having，并且按照平均工资从高到低排序， order by
-- 取出前两行记录 limit 0,2

SELECT AVG(sal) AS avg_sal FROM emp
    GROUP BY deptno
    HAVING  avg_sal > 1000
    ORDER BY avg_sal DSCE
    LIMIT 0,2

数据分组的总结：

如果SELECT语句同时含有group by,having,limit,order by。那么他们的顺序是：

group by 

having

order by

limit

三、多表查询

3.1、多表查询--笛卡尔集

多表查询是指基于两张或两张以上的表查询，在实际应用中，查询单个表可能不能满足你的需求，应找到多张表所能关联起来的字段以及他们的关系。作为WHERE子语句的条件进行筛选。

笛卡尔集：假设集合A={a, b, c, d}，集合B={0, 1, 2}，则两个集合的笛卡尔积为:

{(a, 0), (a, 1), (a, 2),
(b, 0), (b, 1), (b, 2),
(c, 0), (c, 1), (c, 2),
(d, 0), (d, 1), (d, 2)
}

SELECT * FROM emp（13行），dept（4行）

会显示这两张表所有信息的组合，一共13*4=52行数据。emp表中的每一行数据都会跟dept中的四行数据进行组合，这种方式叫笛卡尔集。因此必须加筛选条件，找到两张表列中的有关联的，并在WHERE子句中限制。

如emp员工表中部门编号deptno和dept部门表中的deptno应该相等

WHERE emp.deptno = dept.deptno

多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集

--显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
--1. 雇员名,雇员工资 来自 emp 表
--2. 部门的名字 来自 dept 表
--3. 需求对 emp 和 dept 查询 ename,sal,dname,deptno
--4. 当我们需要指定显示某个表的列是，需要 表.列表

#多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
SELECT ename,sal,dname,emp.deptno
    FROM emp, dept
    WHERE emp.deptno = dept.deptno

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

-- 显示各个员工的姓名，工资，及其工资的级别?
-- 思路 姓名，工资 来自 emp 13
-- 工资级别 salgrade 5
-- 写sql , 先写一个简单，然后加入过滤条件...
select ename, sal, grade
    from emp , salgrade
    where sal between losal and hisal;

3.2、自连接----一张表组合成多张表的自连接查询

自连接是指在同一张表的连接查询（将同一张表看作两张表）

-- 思考题: 显示公司员工名字和他的上级的名字？
-- 员工名字 在 emp, 上级的名字的名字 emp，且上级名字并不是单独一个字段（列）
-- 员工和上级是通过 emp 表的 mgr 列关联

-- 自连接的特点：
-- 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确，可以指定列的别名 列名 as 列的别名

SELECT worker.ename AS '职员名' , boss.ename AS '上级名' 
    FROM emp worker, emp boss
    WHERE worker.mgr = boss.empno;

四、表的子查询---嵌套子查询

子查询是指嵌入在其它 sql 语句中的 select 语句 , 也叫嵌套查询

单行子查询：是指只返回一行数据的子查询语句

多行子查询：是指返回多行数据的子查询     返回多个结果，使用关键字 in筛选

4.1、使用嵌套子查询-- 多行子查询注意使用in

-- 请思考：如何显示与 SMITH 同一部门的所有员工?
/*
1. 先查询到 SMITH 的部门号得到
2. 把上面的 select 语句当做一个子查询来使用
*/
SELECT deptno
    FROM emp
    WHERE ename = 'SMITH'

-- 下面的答案.
SELECT * FROM emp
    WHERE deptno = ( SELECT deptno
                        FROM emp
                        WHERE ename = 'SMITH' )


-- 课堂练习:如何查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号, 但是不含10号部门自己=--- 的雇员.
--1、部门10下的工作，返回多行,,要去重
SELECT DISTINCT job FROM emp 
    WHERE deptno =10
--2、将这个查询结果作为一个查询约束条件，多行子查询
SELECT ename,job,sal,deptno FROM emp
    WHERE job in (SELECT DISTINCT job FROM emp WHERE deptno =10) 
                   AND deptno!=10

4.2、子查询当做临时表使用---构成多表查询

-- 查询 ecshop 中各个类别中，价格最高的商品的信息
-- 查询 商品表
-- 先得到 各个类别中，价格最高的商品 max + group by cat_id, 当做临时表
-- 把子查询当做一张临时表可以解决很多很多复杂的查询

SELECT cat_id , max(shop_price)
    FROM ecs_goods
    GROUP BY cat_id

SELECT goods_id, ecs_goods.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
    where temp.cat_id = ecs_goods.cat_id
    AND temp.max_price = ecs_goods.shop_price

4.3、all 和 any 的使用

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

 

--显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > ALL(
                    SELECT sal
                    FROM emp
                    WHERE deptno = 30
                   )

--另一种方法：
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > (
                  SELECT MAX(sal)
                  FROM emp
                  WHERE deptno = 30

                )

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

--如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
    FROM emp
    WHERE sal >ANY(
                    SELECT sal
                    FROM emp
                    WHERE deptno = 30
                   )

--另一种方法：
SELECT ename, sal, deptno
    FROM emp
    WHERE sal > (
                  SELECT MIN(sal)
                  FROM emp
                  WHERE deptno = 30

                )

4.4、多列子查询

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

 

--请查询 和宋江数学，英语，语文成绩 完全相同的学生

#返回的是宋江的数学英语语文成绩，三列数据
SELECT math,english,chinese FROM student
    WHERE name = "宋江";

SELECT * FROM student
    WHERE (math,english,chinese) = (SELECT math,english,chinese FROM student
                                        WHERE name = "宋江";
                                   )

--请思考如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
-- (字段 1， 字段 2 ...) = (select 字段 1，字段 2 from 。。。。)
-- 分析: 1. 得到 smith 的部门和岗位
SELECT deptno , job
    FROM emp
    WHERE ename = 'ALLEN'
-- 分析: 2 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
SELECT *
    FROM emp
    WHERE (deptno , job) = (
                            SELECT deptno , job
                                FROM emp
                                WHERE ename = 'ALLEN' 
                           ) 
                           AND ename != 'ALLEN'

4.5、在 from 子句中使用子查询

-- 请思考：查找每个部门工资高于本部门平均工资的人的资料

-- 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用

-- 请思考：查找每个部门工资高于本部门平均工资的人的资料
-- 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用

-- 1. 先得到每个部门的 部门号和 对应的平均工资
SELECT deptno, AVG(sal) AS avg_sal
    FROM emp GROUP BY deptno

-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
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 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

表复制

4.6.1、自我复制数据（蠕虫复制）

思考：如何删除一张表的重复记录？

思路

(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样

        create table my_tmp like my_tab02

(2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp

(3) 清除掉 my_tab02 记录

(4) 把 my_tmp 表的记录复制到 my_tab02

(5) drop 掉 临时表 my_tmp

 

-- 1. 先把 emp 表的记录复制到 my_tab01
INSERT INTO my_tab01 (id, `name`, sal, job,deptno)
        SELECT empno, ename, sal, job, deptno FROM emp;

-- 2. 自我复制
INSERT INTO my_tab01
    SELECT * FROM my_tab01;

-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02, 
-- 2. 让 my_tab02 有重复的记录
-- 这个语句 把 emp 表的结构(列)，复制到 my_tab02
CREATE TABLE my_tab02 LIKE emp; 
INSERT INTO my_tab02
    SELECT * FROM emp;
-- 3. 考虑去重 my_tab02 的记录
/*
思路
(1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
(2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
(3) 清除掉 my_tab02 记录
(4) 把 my_tmp 表的记录复制到 my_tab02
(5) drop 掉 临时表 my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
create table my_tmp like my_tab02

-- (2) 把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
insert into my_tmp
    select distinct * from my_tab02;
-- (3) 清除掉 my_tab02 记录
    delete from my_tab02; 

-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02
    select * from my_tmp; 
-- (5) drop 掉 临时表 my_tmp
drop table my_tmp;

合并查询--union、union all

在实际应用中，为了合并多个SELECT语句的结果，可以使用union、union all语句

1、union all：该操作符用于取得两个结果集的并集，不会去校重复行

        SELECT ename,sal,job FROM emp WHERE sal > 2500 UNION ALL 

        SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

2、union ：该操作符与union all相似，但是会自动去掉结果中的重复行

        SELECT ename,sal,job FROM emp WHERE sal > 2500 UNION 

        SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';