MySQL多表操作

来源：互联网发布：柜子设计软件手机编辑：程序博客网时间：2024/05/31 11:04

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="诸葛亮");

0 0