目录:

1. 复制表
2. 单表查询详细操作
3. where 关键字
4. group by 关键字
5. having 关键字
6. order by 与 limit 关键字
7. 多表查询
8. 多对多 三表联查案例
9. 子查询

一. 复制表

    拷贝表及表内所以数据:
    create table copy_table select *from table;
    仅拷贝表结构
    create table copy_table select *from table where 0 > 1;
    共同点: 索引,自增 不能拷贝

二. 单表查询详细操作

增
    insert [into] 表名[字段名] value|values(字段值...);
    into 可省略
    [字段名] 可选
        如果写了 后面的值必须与写了的字段完全匹配
        没写  后面的值必须与表的结构完全匹配
    value 插入一条记录
    values 插入多条记录
删
    delete from 表名 [where 条件]
     where 可选
        有条件就删除满足条件的记录
        没有则全部删除
        全部删除请使用 truncate table 表名

改
    update 表名 set 字段名 = 新的值[,字段n = 新的值n] [where 条件]
    可以同时修改多个字段 用逗号隔开
    where 可选
        有条件就修改满足条件的记录
        没有则全部修改

查
    select [distinct]   *|[字段名,...]|聚合函数 from 表名
            [where 条件
            group by 字段名
            having 条件
            order by 字段名
            limit 显示的条数]
     注意: 关键字的顺序必须与上述语法一致

    简单查询:
        1. * 表示所有列,都显示
        2. 也可以手动指定要显示的列,可以是多个
        3. distinct 用于去除重复的记录
            也可以手动指定要显示的列,从而去重
        4.表达式: 支持四则运算


        字符串拼接
            select concat('字符串',字段名),... from 表名;

            (case
                when 字段 + 字段 < | > | = 条件 then
                concat(字段,'字符串')
            end)
增
    insert [into] 表名[字段名] value|values(字段值...);
    into 可省略
    [字段名] 可选
        如果写了 后面的值必须与写了的字段完全匹配
        没写  后面的值必须与表的结构完全匹配
    value 插入一条记录
    values 插入多条记录
删
    delete from 表名 [where 条件]
     where 可选
        有条件就删除满足条件的记录
        没有则全部删除
        全部删除请使用 truncate table 表名

改
    update 表名 set 字段名 = 新的值[,字段n = 新的值n] [where 条件]
    可以同时修改多个字段 用逗号隔开
    where 可选
        有条件就修改满足条件的记录
        没有则全部修改

查
    select [distinct]   *|[字段名,...]|聚合函数 from 表名
            [where 条件
            group by 字段名
            having 条件
            order by 字段名
            limit 显示的条数]
     注意: 关键字的顺序必须与上述语法一致

    简单查询:
        1. * 表示所有列,都显示
        2. 也可以手动指定要显示的列,可以是多个
        3. distinct 用于去除重复的记录
            也可以手动指定要显示的列,从而去重
        4.表达式: 支持四则运算


        字符串拼接
            select concat('字符串',字段名),... from 表名;

            (case
                when 字段 + 字段 < | > | = 条件 then
                concat(字段,'字符串')
            end)

三. where 关键字

从硬盘中读取数据时 有 过滤条件
between ... and ... 显示某一区间的值
    select * from 表名 where 字段 between ... and ...;

like
    select * from 表名 where 字段 like '条件';

where 的筛选过程
    在没有索引的过程中 挨个比较 效率低
    所以我们应该为表添加索引

四. group by 关键字

分组
    用于给数据分组
        1. 在生活中为了方便管理
        2. 在数据库--MySQL中为了方便统计

    select * from 表名 group by 字段名;
    有两种情况
        1. 在sql_mode中没有设置 ONLY_FULL_GROUP_BY 显示每个组的第一条记录,没有意义
        2. sql_mode中有设置 ONLY_FULL_GROUP_BY 直接报错
            原因是: * 表示所有字段都要显示,但是分组后记录的细节被隐藏,只留下了

        分组是为了统计分组数据 如何统计?
            聚合函数

聚合函数:
    将一堆数据经过计算,得到一个数据
        sum()        求和
        avg()        求平均
        max() /min() 求最大值 / 最小值
        count()      求个数

    select 字段名|字段名,聚合函数 from 表名 group by 需要分组的字段名;

    注意: where 语句后面不能使用聚合函数

    总结:where 条件不能用于筛选分组后的数据

group_concat() 用于分组后将组中某些字段拼接成一个字符串

五. having 关键字

用于对分组后的数据进行过滤
having 不会单独出现 都是和group by 一起出现

having与where
    相同点: 都是用于过滤数据
    不同点:
        1. where 是最先执行 用于读取硬盘数据
           having 要等到数据读取完之后,才能进行过滤,比where晚执行
        2. where中不能使用聚合函数
            having可以使用聚合函数

六. order by 与 limit 关键字

order by
    用于对记录进行  排序
    默认为升序排序
    select *from 表名 order by 字段名;
    修改为降序排序
    select *from 表名 order by 字段名 desc;


limit
    用于限制显示数据的条数
    limit [start,]count
    start: 从start后面一条数据开始显示
    count : 显示条数
    select * from 表名 limit [start,]count
    # 查看 3-5 条数据
    start 为 2 时 起始位置为3
    select * from 表名 limit 2,2;

    limit 常用于 数据分页 显示
        10行数据为一页:
            select * from 表名 limit 0,10;  第一页
            select * from 表名 limit 10,10; 第二页
            select * from 表名 limit 20,10; 第三页

七. 多表查询

1.笛卡尔积查询
    什么是笛卡尔积查询
        用左表中的一条记录,去链接另一张表的所有记录
        就像是把两张表的数据做了相乘
        这将导致 产生大量的无用重复数据
            我们需要的结果是:员工表中的部门id 与 部门表中的id相同 就拼接在一起

        所有要使用 where 筛选出正确的数据
            select * from emp,dept where emp.dept_id = dept.id;

    on关键字:
        作用 用于多表查询时 进行条件限制
        # 错误的使用方法,因为 on 只能用在专门的多表查询语句
            select * from emp,dept on emp.dept_id = dept.id;


2. 内连接查询
    inner join
    语法:
        select * from emp inner join dept on emp.dept_id = dept.id;


3. 左外连接:
    left join
    左边表中的数据完全显示 | 右边表中的数据匹配成功则显示
        select * from emp left join dept on emp.dept_id = dept.id;


4. 右外连接
    right join
    右边表中的数据完全显示 | 左边表中的数据匹配成功则显示
    select * from emp right join dept on emp.dept_id = dept.id;


5. 全外连接
    full join ------ mysql 不支持,但是 oracle 支持

    union:默认去重
        表示合并查询,意思就是把多个查询结果合并在一起显示
    要求:
        被合并的表结构必须相同
    语法:
        合并的是 左连接与右连接查询的结果,表结构相同
            select * from emp right join dept on emp.dept_id = dept.id
            union
            select * from emp left join dept on emp.dept_id = dept.id;

    union all:合并但不去重

6. 总结:
    多表连接 在书写时 按照标准语法书写
    如果左表要全部显示,使用 left join
    如果右表要全部显示,使用 right join
    全部显示,把左连接结果与右连接结果合并

7. 三表及以上查询:(尽量避免,最多三张,多对多关系)
    select * from 表名一 left join 表名二 left join 表名三 on 查询条件;

八. 多对多 三表联查案例

三表查询:
    create table tec(id int,name char(10));
    insert into tec value(1,"egon");
    insert into tec value(2,"yyh");

    create table stu(id int,name char(10));
    insert into stu value(1,"大傻");
    insert into stu value(2,"中傻");
    insert into stu value(3,"小傻");
    create table s_t(s_id int,t_id int);
    insert into s_t value(1,2);
    insert into s_t value(2,2);
    insert into s_t value(3,1);

    mysql> select * from tec;
    +------+------+
    | id   | name |
    +------+------+
    |    1 | egon |
    |    2 | yyh  |
    +------+------+
    2 rows in set (0.00 sec)

    mysql> select * from stu;
    +------+--------+
    | id   | name   |
    +------+--------+
    |    1 | 大傻   |
    |    2 | 中傻   |
    |    3 | 小傻   |
    +------+--------+
    3 rows in set (0.00 sec)

    mysql> select * from s_t;
    +------+------+
    | s_id | t_id |
    +------+------+
    |    1 |    2 |
    |    2 |    2 |
    |    3 |    1 |
    +------+------+
    3 rows in set (0.00 sec)

需求: 找出老师 yyh 教过学生的信息
思路:
    第一件事 到关系表中去查询哪些老师交过哪些学生?
        形成了一个临时表
    第二件事 将上一步的到的临时表 与 学生表进行连接
    第三件事 加上筛选条件,例如老师的姓名

    yyh教过哪些学生?
        select * from tec inner join s_t on tec.id = s_t.t_id
        inner join stu on s_t.s_id = stu.id
        where tec.name='yyh';
    取别名:
        select tec.name teacher ,stu.name student from
        tec inner join s_t on tec.id = s_t.t_id
        inner join stu on s_t.s_id = stu.id
        where tec.name='yyh';

九. 子查询

一.什么是子查询:

使用上一次查询的结果,作为本次查询的原始数据(或是查询条件)

二. 什么时候用?

当查询需求比较复杂,一次性查询无法得到结果,需要多次查询时,
例如:给出一个部门名称,需要获得该部门所有的员工信息
需要先确定部门的id,
然后才能通过id确定员工
解决问题的方式是把一个复杂的问题拆分为若干个简单的问题

三. 如何使用?

    首先明确子查询就是一个普通的查询,当一个查询需要作为子查询使用时,用括号包裹即可
案列:
准备数据:
create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double);
insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张飞","男",24,1,"员工",3000),
(3,"关羽","男",30,1,"员工",4000),
(4,"孙权","男",25,2,"总监",6000),
(10,"刘备2","男",26,2,"总监",5800),
(5,"周瑜","男",22,2,"员工",5000),
(6,"小乔","女",31,2,"员工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司马懿","男",24,3,"员工",6000);

create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"行政"),(3,"财务");

四. 需求:财务部有哪些人:

数据在两张表中 可以使用链接查询:
    select *from emp inner join dept on emp.dept_id = dept.id where dept.name = "财务";

子查询方式:
数据在两张表中,先查询那张?
emp? 不行  不知道部门名 查dept
第一步 需要知道财务部的id
    select id from dept where  name = "财务";
第二步 用查询的到的id作为判断条件查询emp
    select name from emp where dept_id = 3;
    3不能写死 是上一个查询的结果 所以直接写在后面  加上括号就变成了子查询
    select name from emp where dept_id = (select id from dept where  name = "财务");

五. in 关键字子查询

查询平均年龄大于25的部门名称
1.求每个部门的平均年龄
    select avg(age) from emp group by dept_id;
2.筛选结果
    select dept_id,avg(age) as a from emp group by dept_id having a > 25;
    select name from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);

子查询方式:
平均年龄大于25的部门有哪些?
先要求出每个部门的平年龄!每个表示什么? 分组
    select name from dept where id in  (select dept_id from emp group by dept_id having avg(age) > 25);

多表查询方式:
先把数据拼接到一起 在加以筛选
    select dept.name from emp inner join dept
    on emp.dept_id = dept.id
    group by dept.name
    having avg(age) >25;

六. exists关键字子查询

exists后跟子查询,子查询有结果是为True,没有结果时为False,为true时外层执行,为false外层不执行
    select *from emp where exists (select *from emp where salary > 1000);
    select (exists (select *from emp where salary > 10000));

七. 综合练习:

查询每个部门工资最高的员工信息

先查询每个部门的最高工资
    select dept_id,max(salary) from emp group by dept_id;
    select *from
    emp inner join (select dept_id,max(salary) maxs  from emp group by dept_id) as t2
    on emp.dept_id = t2.dept_id
    where emp.salary =  t2.maxs;

子查询的语法特点 就是一个select   a中 包含另一个select    b
b 只能位于 a 的where 后面