mysql学习笔记-连接

-- mysql查不了伪列 ???
-- 伪列
SELECT ename,job,sal,rownum FROM t_emptest;
SELECT ename,job,sal,rowid FROM t_emptest;

-- 块的大小是数据项的大小，就是记录（数据表中的一行）
SELECT * FROM t_emptest;

-- 增加列
ALTER TABLE t_depttest ADD loc VARCHAR(10) NOT NULL;


-- 删除数据
DELETE  FROM t_emptest WHERE EMPNO='7902' LIMIT 1;

-- 更新字段值
UPDATE t_depttest SET DEPTNO=40 WHERE Dname='OPERATIONS';
UPDATE t_depttest SET DEPTNO=30 WHERE Dname = 'SALES';

UPDATE t_depttest SET loc='New York' WHERE DEPTNO=10;
UPDATE t_depttest SET loc='Dallas' WHERE DEPTNO=20;
UPDATE t_depttest SET loc='Chicago' WHERE DEPTNO=30;
UPDATE t_depttest SET loc='Boston' WHERE DEPTNO=40;



-- 查询部门在New York 和Chicago的员工编号和员工名称
-- 下面两个语句查出来结果不一致，第一个查出来结果不对，可能跟and or 有关
SELECT ename,empno,t_emptest.DEPTNO ,t_depttest.loc FROM t_emptest,t_depttest
    WHERE t_emptest.DEPTNO=t_depttest.DEPTNO
        AND t_depttest.loc='New York' OR t_depttest.loc='Chicago';

SELECT ename,empno -- ,t_emptest.DEPTNO ,t_depttest.loc
 FROM t_emptest,t_depttest
    WHERE t_emptest.DEPTNO=t_depttest.DEPTNO
        AND t_depttest.loc in ('New York','Chicago');


-- 1.内连接，连接两张表
-- 用in 代替 OR
SELECT ename,empno,JOB,Dname,t_emptest.DEPTNO
    FROM t_emptest JOIN t_depttest
-- ON 带连接条件
ON t_emptest.DEPTNO=t_depttest.DEPTNO
-- 过滤条件
WHERE t_depttest.loc  in ('New York','Chicago');

-- 1.1.查询部门编号，部门名称，部门员工人数
SELECT d.deptno,d.dname,count(*)
    FROM t_depttest d  
        JOIN t_emptest e ON d.DEPTNO=e.DEPTNO
    GROUP BY d.DEPTNO,d.Dname;

-- 2.左外连接
--  返回满足连接条件的所有行，
--  而且还会返回不满足连接条件的连接操作符的左边表的其他行
-- count(EMPNO) 与count(*) 结果一个为0，一个为1，注意使用
SELECT d.deptno,d.dname,count(EMPNO)
    FROM t_depttest d  
        LEFT JOIN t_emptest e ON d.DEPTNO=e.DEPTNO
    GROUP BY d.DEPTNO,d.Dname;

-- 3.右外连接，和左外连接正好相反
-- 查询雇员编号，雇员名称，雇员薪水和雇员部门名

SELECT * FROM t_emptest;
UPDATE t_emptest SET DEPTNO=null WHERE EMPNO=7839;

SELECT e.EMPNO,e.ENAME,e.sal,d.Dname,e.DEPTNO FROM t_depttest d
    RIGHT  JOIN t_emptest e ON d.DEPTNO = e.DEPTNO;

-- 4.and 和where 结果不一样，这就是jion的区别
SELECT d.Dname,d.DEPTNO,e.ENAME FROM t_depttest d
    LEFT JOIN t_emptest e
        on d.DEPTNO=e.DEPTNO   -- on 后面的条件先执行
        AND d.DEPTNO = 10;

SELECT d.Dname,d.DEPTNO,e.ENAME FROM t_depttest d
    LEFT JOIN t_emptest e
        on d.DEPTNO=e.DEPTNO
        WHERE d.DEPTNO = 10; -- where 是查出来后再查一次

-- 4.1 找到雇员名称为blake的雇员名称和经理
SELECT e.ENAME,mgr.Ename FROM t_emptest e
    JOIN t_emptest mgr ON e.MGR=mgr.EMPNO
    WHERE e.ENAME='BLAKE';