Mysql之DQL(查询)笔记

4.导入数据
    mysql -uroot -p密码
    show databases;   查看有哪些数据库
    create database bjpowernode;  创建数据库
    use bjpowernode;   使用数据库
    show tables;     查看数据库中的有哪些表
    source D:\course\05-MySQL\resources\bjpowernode.sql  初始化数据
5.   .sql 文件称为sql脚本，当太多使用source命令完成初始化
6. 删除数据库    
    drop database bjpowernode;
7.查看表结构
    desc dept;   查看表结构
8.表中的数据
     select * from emp;
9.常用命令
    select database(); 查看当前使用的是哪个数据库
    select version(); 查看mysql的版本号
    \c 结束一条语句
    exit 退出mysql
10.查看创建表的语句
    show create table emp;
11.简单的查询语句(DQL)
    select 字段名，字段名，字段名... from 表名;
    提示：任何一条sql语句以";"结尾
         sql 语句不区分大小写
    select ename,sal*12 from emp;
    select ename,sal*12 as yearsal from emp;  给查询结果的列重命名
    select ename,sal*12 as '年薪' from emp;  //别名有中文
    标准sql语句中要求字符串使用单引号括起来，不建议使用双引号“”
    select ename,sal*12 yearsal from emp;  as关键字可以省略
    select * from emp; 查询所有字段，//实际开发中不建议使用*，效率较低
12.条件查询
    语法格式
    select
                           字段，字段....
    from
        表名
    where
         条件;
          执行顺序：先from，然后where，最后select
    select ename from emp where sal = 5000; 查询工资等于5000的员工姓名
    select sal from emp where ename = 'SMITH'; 查询SMITH的工资
        //字符串使用单引号括起来
    select ename,sal from emp where sal >= 3000; 找出工资高于3000的员工
    select ename,sal from emp where sal !=/<> 3000; 找出工资不等于3000的员工
    select ename,sal from emp where sal >=1100 and sal <= 3000;
    select ename,sal from emp where sal between 1100 and 3000;
        //between ..and... 是闭区间[1100~3000]
        between and在使用的时候必须左小右大。
    找出工资在1100和3000之间的员工，包括1100和3000
    between and除了可以使用在数字方面之外，还可以使用在字符串方面。
    select ename from emp where ename between 'A' and 'C' //左闭右开
    select ename,sal,comm from emp where comm is null; 找出那些人津贴为NULL
    在数据库中NULL不是一个值，不代表空，用is null 或者is not null
    select ename,sal,comm from emp where comm = null
    select ename,sal,comm from emp where comm is not null;找出那些人津贴不为NULL
    select ename,sal,comm from emp where comm is null or comm = 0;找出那些人没有津贴
    select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
        找出工作岗位是MANAGER和SALESMAN的员工
    select ename,sal from emp where sal > 1000
    select ename,deptno emp where deptno = 20 or deptno = 30
    selec ename,sal,deptno emp where sal > 1000 and (deptno = 20 or deptno = 30)
    and和or联合起来使用:找出薪资大于1000并且部门编号是20或30部门的员工，
    注意:当运算符的优先级不确定的时候加小括号
    in等同于or：找出工作岗位是MANAGER和SALESMAN的员工
    select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'；
     select ename,job from emp where job in('MANAGER','SALESMAN');
    select ename,sal from emp where sal in(800,5000);
        //in后面的值是具体的值不是区间
    select ename,job from emp where sal not in(800,5000);   not in:不在这几个值当中
    模糊查询like
    (在模糊查询中，必须掌握两个特殊的符号，一个是%,一个是_)
    %代表任意多个字符，_代表任意1个字符
    select name from t_user where ename like '%O%';
    select name from t_user where ename like '_A%'; 找出名字中第二个字母是A的
    select name from t_user where ename like '%_%'; 找出名字中有下划线的
    select ename from emp where ename like '%T';找出名字中最后一个字母是T的
    
13. 排序(升序，降序)
    
    select ename,sal from emp order by sal; 按照工资升序，找出员工名和薪资
    注意：默认是升序，怎么指定升序或降序呢？asc升序，desc降序
    select ename,sal from emp order by sal;//升序
      select ename,sal from emp order by sal asc;//升序
    select ename,sal from emp order by sal desc;//降序
    select ename,sal from emp order by sal desc,ename asc;
    按照工资的降序排列，当工资相同的时候再按照名字的升序排序
    注意：越靠前的字段越能起主导作用，只有当前面的字段无法完成排序的时候，才会启用后面的字段
    select ename,job,sal from emp where job = 'SALESMAN'/ in('SALESMAN') order by sal desc;
    找出工作岗位是SALESMAN的员工，并且要求按照薪资的降序排列
    执行代码 select .. from .. where .. order by ...
    执行顺序from where order by  select
14. 分组函数
    count 计数
    sum 求和
    avg 平均值
    max 最大值
    min 最小值
    记住：所有的分组函数都是对“”某一组“”数据进行操作的
     select sum(sal) from emp; 找出工资总和
    select max(sal) from emp;找出最高工资
    select min(sal) from emp;找出最低工资
    select avg(sal) from emp;找出平均工资
    select count(*/ename) from emp;找出总人数
    分组函数一共有5个(计求平大小)
      分组函数还有另一个名字:多行处理函数
     多行处理函数的特点:输入多行，最终输出的结果是1行
    select count(comm) from emp;    分组函数自动忽略NULL
    select sum(comm) from emp;
    select sum(comm) from emp where comm is not null;//不需要额外添加这个条件，sum函数自动忽略NULL
    找出工资高于平均工资的员工
    select ename,sal,avg(sal) from emp where sal > avg(sal);  //error
     思考以上的错误信息：无效的使用过了分组函数?
    原因：SQL语句中有一个语法规则，分组函数不可直接使用在where子句当中，why?
    因为group by是在where执行之后才会执行。
    select         5
        ..
    from         1
         ..
    where         2
        ..    
    group by         3
        ..
    having         4
        ..
     order by         6    
        ..
    select ename,sal.avg(sal) from emp  having sal > avg(sal)
    count(*)和count(具体的某个字段)，他们有什么区别？
         count(*)统计总记录条数(和某个字段无关)
        count(comm)表示统计comm字段中不为NULL的数据总数量
    分组函数也能联合起来用
    select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
    找出工资高于平均工资的员工？
    select avg(sal) from emp as e;
    select ename,sal from where sal >(select avg(sal) from emp);
    
15.单行处理函数
    输入一行，输出一行
    计算每个员工的年薪
    select ename,(sal+comm)*12 as yearsal from emp;
    重点：所有数据库都是这样规定的，只要有NULL参与的运算结果一定是NULL，
    使用ifnull函数
    select emame,(sal + ifnull(comm,0))*12 as yearsal from emp;
    ifnull()空处理函数？
    ifnull(可能为NULL的数据，被当做什么处理):属于单行处理函数
    select ename,ifnull(comm,0) as comm from emp;
16. group by 和having
    group by 按照某个字段或者某些字段进行分组
    having 是对分组之后的数据进行再次过滤
    找出每个工作岗位的最高薪资
    select max(sal),job from emp group by job;
    注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的，
  并且任何一个分组函数(计求平大小)，都是在group by语句执行结束之后才会执行
    当一条sql语句没有group by的话，整张表的数据会自成一组
    select ename,max(sal),job from emp groupby job;//有结果，但结果没有意义
    记住一个规则：当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段
    每个工作岗位的平均薪资
    select avg(sal) as avgsal ,job from emp group by job;
    多个字段能不能联合起来一块分组？
    找出每个部门不同工作岗位的最高薪资
    select deptno,job,max(sal) from emp group by deptno,job;
    找出每个部门的最高薪资，要求显示薪资大于2900的数据
    select deptno,max(sal) from emp group by deptno having max(sal) > 2900;
    //这种效率低,建议能够使用where过滤的尽量使用where
    select deptno,max(sal) from emp where sal > 2900 group by deptno;
    找出每个部门的平均薪资,要求显示薪资大于2000的数据
    select deptno,avg(sal) as avgSal from emp group by deptno;
    select deptno,avg(sal) as avgSal from emp group by deptno having avg(sal) > 2000;
    //这里明显不能使用where 因为先删除了一些sal值，数据不准确
    select deptno,avg(sal) from emp where avg(sal) > 2000 group by deptno;
    // 错误了。        这种情况只能使用having过滤。
    //where后面不能使用分组函数
    总结一个完整的DQL语句怎么写？
    select .. from .. where ... group by ... having .... order by ....

1.关于查询结果集的去重
    select distinct job from emp; //distinct 关键字去除重复记录
    select ename,distinct job from emp;//这是错误的sql语句
    记住:distinct只能出现在所有字段的最前面
    select distinct deptno,job from emp;联合去重
    select count(distinct job) from emp;统计岗位的数量
2. 连接查询
    2.1什么是连接查询？
    在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表取出最终的结果，
    在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表
    2.2 连接查询的分类?
    根据语法出现的年代来划分的话
    SQL92语法(一些老的DBA可能还在使用这种语法)DBA:数据库管理员
    SQL99语法(比较新的语法)
    根据表的连接方式来划分，包括
        内连接
            等值连接
            非等值连接
            自连接
        外连接    
            左外连接(左连接)
            右外连接(右连接)
        全连接    (不讲，很少用)
    2.3在表的连接查询方面有一种现象被称为：笛卡尔积现象(笛卡尔乘积现象)
    找出每一个员工的部门名称，要求显示员工名和部门名(两张表)
    select ename,dname from emp,dept;//这种会出现笛卡尔积现象，
    笛卡尔积现象，当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积
    关于表的别名：select e.ename,d.dname from emp e,dept d;
        表的别名好处？
        1.执行效率高
        2.可读性好
    2.4怎么避免笛卡尔积现象？当然是加条件进行过滤
    思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
        不会，次数还是不变，只不过显示的是有效记录
    找出每一个员工的部门名称，要求显示员工名和部门名
    select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//SQL92,以后不用
    2.5内连接之等值连接：最大特点是：调教是等量关系
    查询每个员工的部门名称，要求显示员工名和部门名
    SQL92:
    select e.ename,d.dname from emp e.dept d where e.detpno = d.deptno;
    SQL99(常用):
    select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
    (join ... on )
    //inner可以省略的，带着inner目的是可读性号一些
    select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno
    语法：  ......   
            A
              join    
            B
              on
            连接条件
              where
            .....
              SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了，
    2.6内连接之非等值连接：最大特点：连接条件中的关系是非等量关系
    找出每个员工的工资等级，要求显示员工名，工资，工资等级
    select ename,sal from emp e;
    select * from salgrade s;
    select
        e.ename,e.sal,s.grade
    from
        emp e
    (inner)join     
        salgrade s
    on
         e.sal between s.losal and s.hisal
     2.7自连接:最大的特点:一张表看做两张表，自己连接自己
    找出每个员工的上级领导，要求显示员工名和对应的领导名
    select
        e1.ename as '员工名',e2.ename as '领导名'
    from
        emp e1
     join
        emp e2
    on
        e1.mgr = e2.empno;  
    2.8 外连接
    什么是外连接，和内连接有什么区别？
    内连接：假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接，AB两张表没有主副之分，两张表是平等的
    外连接：假设A和B表进行连接，使用外连接的话，AB两张表有一张表时主表，一张表是副表，主要查询主表中的数据，捎带查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配
    外连接的分类？
        左外连接：表示左边的这张表是主表
        右外连接：表示右边的这张表时主表
    左连接有右连接的写法，右连接也有对应的左连接的写法
    找出每个员工的上级领导？(所有的员工必须全部查询出来)
    内连接
    select
        e1.ename,e2.ename
    from
        emp e1
    join
        emp e2
    on
        e1.mgr = e2.empno;
    外连接(左外连接)
    select
        e1.ename as '员工',e2.ename as '领导'
    from     
        emp e1
    left(outer) join
        emp e2
    on     
        e1.mgr = e2.empno;
    //outer可以省略
    外连接(右外连接)
    select
        e1.ename '员工',e2.ename '领导'
    from
        emp e1
    right (outer)join
        emp e2
    on
        e1.mgr = b.empno;
    外连接最重要的特点是：主表的数据无条件的全部查询出来
    找出那个部门没有员工？
    select
        d.deptno,d.dname,e.ename
    from
        dept d
    left join
        emp e
    on
        e.deptno = d.deptno
    where
        e.empno is null;
    2.9三张表怎么连接查询？
    解释一下：
    .....
        A
    join    
        B
    on
        .....
    join
        C
    on
        ......
    表示A表和B表先进行表连接，连接之后A表继续和C表进行连接
    找出每一个员工的部门名称以及工资等级(3张表)
    select
        e.ename,d.dname,s.grade
    from
        emp e
    left join
        dept d
    on
        e.deptno = d.deptno
    left join
        salgrade s
    on
        e.sal between s.losal and s.hisal;
    找出每个员工的部门名称，工资等级，以及上级领导(4张表)
    select
        e1.ename,d.dname,s.grade,e2.ename
    from
        emp e1
    join
        dept d
    on
        e1.deptno = d.deptno
    join
        salgrade s
    on    
        e.sal between s.losal and s.hisal
    left join    
        emp e2
    on
        e1.mgr = e2.empno;
3.子查询
    3.1什么是子查询？子查询都可以出现在哪里？
    select语句当中嵌套select语句，被嵌套的select语句就是子查询
    子查询可以出现在哪里?
    select
        ..(select)
    from
        ..(select)        
    where
        ..(select)
    3.2where子句中使用子查询
    找出高于平均工资的员工信息
    select avg(sal) as avgSal from emp;
    select
        ename,sal,avg(sal)/*
     from
        emp
    where
        sal >(select avg(sal) as avgSal from emp);     
    3.3from后面嵌套子查询
    找出每个部门平均薪水的等级
    select deptno,avg(sal) as avgSal from emp group by deptno;
    select
        t.*,s.grade
    from  
        (select deptno,avg(sal) as avgSal from emp group by deptno) t
    left join
        salgrade s
    on
        t.avgSal    between s.losal and s.hisal;
    找出每个部门平均的薪水等级
    1.每个员工的薪水等级
    select e.ename,e.deptno,e.sal,s.grade from emp e left join salgrade s on e.sal between s.local and s.hisal
    2.每个部门平均的薪水等级
    select e.deptno,avg(s.grade) from emp e left join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
    3.4在select后面嵌套子查询
    找出每个员工所在的部门名称，要求显示员工名和部门名
    select dname,deptno from dept;
    select
        ename,(select d.dname from dept d where e.deptno = d.deptno) as dname
    from
        emp e;
4. union(可以将查询结果集相加)
    找出工作岗位是SALESMAN和MANAGER的员工？
    select ename,job from emp where job = 'SALESMAN' or 'MANAGER';
    select ename,job from emp where job in('SALESMAN' ,'MANAGER');
    select ename,job from emp where job = 'SALESMAN'
    union
    select ename,job from emp where jon = 'MANAGER';
    两张不相干的表中的数据拼接在一起显示？
    select ename from emp
    union
    select dname from dept;  //error
5. limit(重点中的重点，以后分页查询全靠它)
    5.1 limit是mysql特有的，其他数据库中没有，不通用，
    5.2limit取结果集中的部分数据，这时它的作用就体现出来了
    5.3语法机制
        limit startIndex,length
              起始位置的索引    length表示取几个
    取出工资前5名的员工(思路:降序取前5个)
    select ename,sal from emp order by sal desc limit 0,5;
    5.4limit是sql语句最后执行的一个环节
        select       5
        from         1
        where       2
        group by     3
        having        4
        order by     6
        limit            7
      5.5找出工资排名在第4到第9的员工
    select ename,sal from emp order by sal desc limit 3,6;
    5.6通用的标准分页sql?
    每页显示3条记录:
    第一页    0,3
    第二页    3,3
    第三页    6,3
    第四页    9,3
    第五页    12,3
            每页显示pageSize条记录：
    第pageNo页：(pageNo -1 ) * pageSize,pageSize
    pageSize是什么？是每页显示多少条记录
    pageNo是什么？显示第几页
    java代码{
        int pageNo = 2;//页码是2
        int pageSize = 10;//每页显示10条    
        limit (pageNo - 1) * pageSize,pageSize;    
    
}