• mysql的内连接外连接查询


    建表:

    create table emp(
    empno int primary key,
    ename char(6) not null,
    job char(9) not null,
    mgr int,
    hiredate date not null,
    sal int not null,
    comm int,
    deptno int not null
    )
    
    create table dept(
    deptno int primary key,
    dname char(10) not null,
    loc char(8) not null
    )
    
    create table salgrade(
    grade int primary key,
    losal int not null,
    hisal int not null
    )

    准备数据:

    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7566,'jones','manager',7839,'1981-04-02',2975,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7788,'scott','analyst',7566,'1987-04-19',3000,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7839,'king','president',null,'1981-11-17',5000,null,10);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7844,'turner','salesman',7698,'1981-09-08',1500,null,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7876,'adams','clerk',7788,'1987-05-23',1100,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
    insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7934,'miller','clerk',7782,'1982-01-23',1300,null,10);
    
    
    insert into dept(deptno,dname,loc) values(10,'accounting','new york');
    insert into dept(deptno,dname,loc) values(20,'research','dallas');
    insert into dept(deptno,dname,loc) values(30,'sales','chicago');
    insert into dept(deptno,dname,loc) values(40,'operations','boston');
    insert into salgrade(grade,losal,hisal) values(1,700,1200); insert into salgrade(grade,losal,hisal) values(2,1201,1400); insert into salgrade(grade,losal,hisal) values(3,1401,2000); insert into salgrade(grade,losal,hisal) values(4,2001,3000); insert into salgrade(grade,losal,hisal) values(5,3001,9999);

    emp表:

     

    dept表:

    salgrade表:

    查询:

     (内连接的等值连接)查找叫allen的人所在的部门名称:

    select e.ename,d.dname from emp e inner join dept d on e.deptno=d.deptno where e.ename='allen';
    

     (内连接的非等值连接)查找所有人的姓名、薪水、薪水等级和对应薪水等级的最低、最高薪水:

    select e.ename,e.sal,s.grade,s.losal,s.hisal from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
    

    (内连接的自连接)列出员工的名字和他们的直接领导的名字:

    select worker.ename,manager.ename from emp worker inner join emp manager on worker.mgr=manager.empno;
    

     内连接总结:任何一张表里的记录一定要在另一张表中找到匹配的记录,否则不能出现在结果集中。

     (外连接的左外连接)列出员工的名字和他们的直接领导的名字:

    select worker.ename,manager.ename from emp worker left outer join emp manager on worker.mgr=manager.empno;
    

    (外连接的右外连接)列出员工的名字和他们的直接领导的名字:

    select worker.ename,manager.ename from emp manager right outer join emp worker on worker.mgr=manager.empno;
    

    左右外连接总结:左外连接或右外连接的查询结果=内连接的结果+驱动表在匹配表中匹配不上的记录和一条null记录的组合。

    (外连接的全外连接):用的很少,暂时不介绍。

                                                                                            

  • 相关阅读:
    MQTT的优势
    http与https与tcp区别
    中科芯CKS-MBT30数据采集网关帮助工程师实现PLC远程上下载,减少出差成本
    CKS-MAT30远程程序上下载 支持欧姆龙西门子等PLC 远程下载、监控
    西门子S7以太网通讯协议
    DK云网关与普通DTU之间的区别
    腾讯笔试题
    二叉搜索树
    哈希,链接法解决冲突
    将16进制字符串转化为10进制数输出
  • 原文地址:https://www.cnblogs.com/i-hard-working/p/9555195.html
Copyright © 2020-2023  润新知