MySQL多表查询

1.主键约束（非空，唯一，被引用）

//方式一
CREATE TABLE stu(sid INT PRIMARY KEY,sname VARCHAR(20),age INT);
//方式二
CREATE TABLE stu(sid INT,sname VARCHAR(20),age INT, PRIMARY KEY(sid));
//方式三
CREATE TABLE stu(sid INT,sname VARCHAR(20),age INT);
ALTER TABLE stu ADD PRIMARY KEY(sid);
//删除主键
ALTER TABLE stu DROP PRIMARY KEY;

2.外键（可以为空，但是不能不存在）

//1.从表
CREATE TABLE dept(
    deptno INT PRIMARY KEY,
    dname VARCHAR(20)
);
INSERT INTO dept VALUE(10,"研发部");
INSERT INTO dept VALUE(20,"财务部");
INSERT INTO dept VALUE(30,"运营部");

//2.主表
CREATE TABLE emp(
    empno INT PRIMARY KEY,
    ename VARCHAR(20),
    deptno INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (deptno) REFERENCES dept(deptno)
);

3.一对一

//1.从表
CREATE TABLE hasband(
    hid INT PRIMARY KEY,
    hname VARCHAR(20)
);

SELECT * FROM hasband;
INSERT INTO hasband VALUE(1,"刘备");
INSERT INTO hasband VALUE(2,"关羽");
INSERT INTO hasband VALUE(3,"张飞");
//2.主表（主表的主键作为外键）
CREATE TABLE wife(
    wid INT PRIMARY KEY,
    wname VARCHAR(20),
    CONSTRAINT fk_wife_hasband FOREIGN KEY(wid) REFERENCES hasband(hid)
);

INSERT INTO wife VALUE(1,"小乔");
INSERT INTO wife VALUE(2,"貂蝉");
INSERT INTO wife VALUE(3,"吴氏");

4.多对多

//学生表
CREATE TABLE student(
    sid INT PRIMARY KEY,
    sname VARCHAR(20)
);
//老师表
CREATE TABLE teacher(
    tid INT PRIMARY KEY,
    tname VARCHAR(20)
);
//关联表
CREATE TABLE stu_tea(
    sid INT,
    tid INT,
    CONSTRAINT fk_stu FOREIGN KEY(sid) REFERENCES student(sid),
    CONSTRAINT fk_tea FOREIGN KEY(tid) REFERENCES teacher(tid)
);
INSERT INTO student VALUE(1,"老大");
INSERT INTO student VALUE(2,"老二");
INSERT INTO student VALUE(3,"老三");
INSERT INTO student VALUE(4,"老四");
INSERT INTO student VALUE(5,"老五");

INSERT INTO teacher VALUE(1,"李老师");
INSERT INTO teacher VALUE(2,"张老师");
INSERT INTO teacher VALUE(3,"刘老师");
INSERT INTO teacher VALUE(4,"武老师");

INSERT INTO stu_tea VALUE(1,1);
INSERT INTO stu_tea VALUE(2,1);
INSERT INTO stu_tea VALUE(3,1);
INSERT INTO stu_tea VALUE(4,1);
INSERT INTO stu_tea VALUE(5,1);

INSERT INTO stu_tea VALUE(1,2);
INSERT INTO stu_tea VALUE(2,2);
INSERT INTO stu_tea VALUE(3,2);

INSERT INTO stu_tea VALUE(2,3);
INSERT INTO stu_tea VALUE(3,3);
INSERT INTO stu_tea VALUE(4,3);
INSERT INTO stu_tea VALUE(5,3);

INSERT INTO stu_tea VALUE(3,4);
INSERT INTO stu_tea VALUE(4,4);
INSERT INTO stu_tea VALUE(5,4);

5.多表查询

合并结果集
连接查询
    内连接
    左外连接
    右外连接
子查询

6.合并结果集（查询的列类型必须一致）

    CREATE TABLE aa(a INT,b VARCHAR(20));
    CREATE TABLE bb(c INT,d VARCHAR(20));
//1.查询全部
    SELECT * FROM aa
    UNION ALL
    SELECT * FROM bb;
//2.去重复查询
    SELECT * FROM aa
    UNION ALL
    SELECT * FROM bb;

7.内连接查询

//1.方言
    SELECT * 
    FROM emp,dept
    WHERE emp.deptno = dept.deptno;
//2.标准
    SELECT * 
    FROM emp INNER JOIN dept
    ON emp.deptno = dept.deptno
//3.自然
    SELECT * 
    FROM emp NATURAL JOIN dept

8.左外连接

    SELECT * 
    FROM emp LEFT OUTER JOIN dept
    ON emp.deptno = dept.deptno

9.右外连接

    SELECT * 
    FROM emp RIGHT OUTER JOIN dept
    ON emp.deptno = dept.deptno

10.全外连接

    左外连接与右外连接合并结果集去重复就是全外连接

11.子查询

//查询工资高于诸葛亮的员工信息
    SELECT * 
    FROM emp
    WHERE sal >(SELECT sal FROM emp WHERE ename="诸葛亮");