day 36

今日内容:
    查询语句:
        1.完整的查询语句
        2.一堆关键字
            查询的语句
        3.单表查询

        4.对表查询
        5.子查询



一. 一对一关系的补充:(外键 (foreign key)应该增加从表)

     客户表,学员表
     一个客户只对应一个客户  >>>一对一>>外键(两个表有关系就有外键)
     创建客户表:
        create table customer(id int primaary key auto_increment,name char(10).phone char(11),sex char(3))
     创建学生表:
        create table stydent(id int primaary key auto_increment,name char(10),sex char(3),class char(10),c_id int ,
                               foreign key(c_id) references customer(id)
                               on update cascade
                               on delete cascade

        )

二. 复制表:
    1. create table copy_table select * from t1 ; 完全复制(结构加数据)
    2. create table copy_table select * from t1 where 1>10; 拷贝结构 (后面加一个不成立条件 );

    共同点; 主键(索引) 描述 不能拷贝(自增)

三. 记录的详细操作
    1.增;
        insert [into] 表名[字段名] values|value(字段值.......)
        into 可以省略
        [字段名] 可选 :
            选了 后面的值 必须与 写的字段匹配
            不写 后面的值 必须和表的结构完全匹配
         value 插入一条记录
         values 插入多条记录
    2.删:
        delete from 表名 [where 条件]
         where 可选:
        有 修改满足条件的记录
        没有 就全部修改
        如果需要全部删除 使用:truncate table 表名:
        原因:delete 是逐行删除 效率低 删除的行会保留行数


    3.改:
        update 表名 set 字段名 = 新的值[,字段n=新的n] [where 条件]
        可以同时修改 多个字段 用逗号隔开 注意最后一个字段不能隔开
       where 可选:
        有 修改满足条件的记录
        没有 就全部修改

    4.查:
        完整的查询语句:
        select [distinct] {*|字段名,字段名|聚合函数| 表达式} from 表名
           [ where 条件
            group by 字段名
            having 条件
            order by 字段名
            limit 显示的条数]

        注意:关键字的顺序比必须与上述语法一致
        执行的顺序:from >>> where (逐行的去读) >>
        1. * 表示所有列
        2.distinct 表示去重 (只去除完全相同的记录) 可以手动指定要显示的列,从而去重
        3.表达式:加减乘除
][
def select();
    from():       打开文件
    where() :     读取每一行并判断是否满足条件(从硬盘读取全部的数据)
    group by ():  对数据进行分组 >> 方便对数据进行操作
    having():     在分组之后进行过滤   having 不单独出现 仅用于分组之后进行过滤
    distinct():   去重
    order by():   用于对筛选后的数据进行排序
    limit () :    限制显示的条数
    最后根据select后制定的字段来显示排序

练习：
    1.查询所有人的成绩
    select name,English+math as 总分 from stu;
    select name,English+10 as 英语 from stu;

    2. 需要在字段的数据前面加上字段名:
        name:赵云 English:90 math:19
    需要字符串拼接函数:concat(字符串)
    select  concat("name",name),
    concat("english",english),
    concat("math",math) from stu

    3. case **
       select
      (case
      when english +math <150 then
        concat(name,"shit")
      when english +math >=150 then
        concat(name,"nice")
       end)
       from stu

四.关键字

1. where :
    从硬盘上读取数据时的一个过滤条件
    1. < ,> <= ,>= !=
    2. like :(%:0个或多个   _:一个)  模糊查询  "李%":后有李的   "%李% ":只要有李的
    3. between x and y   : 区间
    4. in (集合)  or

    where 的筛选过程: 在没有索引的条件下 挨个比较

2. group by : 用于给数据分组(每字)
      1. 在生活中是为了好管理
      2. 在数据库中是为了方便统计
      使用:
        1. 创建数据;
        2 查询:
            select * from emp   group by dept >>没意义 但还是会显示 : 解决问题
                set global sql_mode="STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY"
            两种情况:
                1. sql_mode 没有设置  ONLY_FULL_GROUP_BY 显示每个组的第一条信息  没有意义 新版中 自带

                2. sql_mode 有设置 ONLY_FULL_GROUP_BY 直接报错
                    原因: * 表示的是所有的字段都要显示 ,但是分组后记录的细节被隐藏
                    这就意味着:出现在group by 后面的字段才能被显示 (不能全查 不能用*)

                分组是为了 统计数据 >>> 用到聚合函数

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

                 4. gropr_concat:拼接  没啥意义
                        select dept,gropr_concat(name)  from emp group by dept

              需求 :
               1. 求出每个部门的最高工资
                    select dept(部门名字),max(salary) from empty group by dept

                2. 求每个部门中每个岗位的平均工资
                    select  dept ,job ,avg(salary) from emp group by  dept ,job

                3.查询每个部门有几个人
                    select dept,count(*) from emp group by dept;

                4. 计算每个部门的平均工资:
                    select dept ,avg(salary) from emp group by dept

                5. 查询平均工资大于5000的部门
               select avg(salary) from emp where avg(salary) > 500 group by ??报错
               where 语句后面 不能使用聚合函数   不能使用筛选后的数据



3. hanving:对分组后的数据进行筛选|过滤  不能单独存在 都是和group by

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

       1. 查询平均工资大于5000的部门
       select dept,avg(salary) from emp group by dept having avg(salary) > 5000;

       2. 查询工资最高的人姓名和他的工资 >>>需要用到子查询

4. order by :用于对记录进行排序(默认为升序) *********

        desc为降序
        asc 为升序

    需求
    1. 按工资的从低到高的顺序排 显示所有的员工
    select * from order by salary

    2. 每个部门的员工 按照工资降序排序(有问题)
        select name,dept,salary, from emp group by name,dept,salary  order by salary desc;

     3. 按照每个部门的平均工资排序
     select dept,avg(salary) from emp group by dept order by avg(salary) desc;

 5. limit :用于限制显示的条数 ******
      limit[start,]count
      count (显示的条数)

     需求:
        1.看看表里的前三条数据
        select * from emp limit 3;

        2.表里的3-5条:
        select * from emp limit 2,3;

        3. 查看工资最高的那个人的信息

        select * from emp order by salary desc limit 1 ;


        limit 常用于 数据的分页展示  比如腾讯新闻的上拉加载新的一页

        select * from emp limit 10; 第一页     页数 减一 乘以条数 得到起始位置
        select * from emp limit 10,10; 第二页
        select * from emp limit 20,10; 第三页

五:多表查询
    在多个表中查询需要的数据
    例如:在班级表 和 学生表
    给你已给的班级名称 请查询所有的学员数据
    先查询班级表 得到一个班级的id  在根据id去取学院查找学生信息


   多查询的方式:
    数据准备:


    1.  笛卡尔积查询:用坐标中的一条记录 去链接另一张表的所有数据
            select * from temp,dept

                就像是把两张表的数据做了乘积
                这将导致 会产生大量的无用重复的数据
                    我们要的效果是:员工表中的部门id 与 部门表中的id 相同 就拼接在一起
                    用 where 筛选出正确的数据

            select * from emp,dept where emp.dept_id = dept.id

            on 关键字:用于多表查询的的条件限制
             select * from emp,dept on emp.dept_id = dept.id   报错 因为 on只能用在专门多表查询的语句

    2. 内连接查询:
       inner join  加 on
       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 : 合并查询(将第一个表的结果合并在一起,显示)
                    要求是结构必须相同
                    默认是去除重复的
           union all : 合并 但是不去除重复

         select  * from emp union select * from dept;

         select * from  emp left  join dept on  emp.dept_id= dept.id
         union
         select * from emp  right join dept on  emp.dept_id= dept.id

        总结:多表连接 在书写时 按照填空的方式来书写
             如果左边要全部显示 用left join
             如果右边要全部显示 用 right join
             全部显示: 将左连接的结果和右连接的结果 合并


    三表连查:
        数据准备:
            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);

        需求 : 找出yyh 这个老师教过的学生的信息
               思路:
                    第一件事: 到关系中 哪些老师教过哪些学生(学生的id) 形成一个临时表
                    第二件事:将上一步得到的临时表与学生表进行连接
                    第三件事:加上额外的筛选条件

                    select  tec.name teacher stu.name student from tec
                     inner join s_t on s_t.id=tec.id
                     inner join stu on stu.id = s_id

                      where tec.name ='yyh';


六 .子查询:将上一次的查询结果 作为本次查询的原始数据

    需求:查询出财务部工资最高的人的信息
       1. 先查询财务部的最高工资
        2. 拿着最该工资去找人
        select max(salary) from emp grour by dept having dept ="财务部"

         select * from emp where salary= (select max(salary) from emp grour by dept having dept ="财务部") ******