MySQL多表查询和表设计

目录

关联查询 多表查询

笛卡尔积

等值连接和内连接

外链接

关联查询总结

表设计之关联关系

在修改表时添加外键约束

---

关联查询 多表查询

同时查询多张表的查询方式称为关联查询 n张表进行关联的时候至少需要n-1个

多少个连接条件？n-1个条件

1.查询每一个员工的姓名和其对应的部门名称

      select e.ename,d.dname  from emp e,dept d   where e.deptno=d.deptno;

2.查询在new york工作的所有员工信息

    select * from emp e,dept d where e.deptno=d.deptno and d.loc='new york';

笛卡尔积

关联查询必须写关联关系，如果不写则得到两张表的乘积，这个乘积称为笛卡尔积

工作中不允许出现因为 超级耗内存 有可能直接崩溃

等值连接和内连接

等值连接和内连接都是关联查询的查询方式

等值连接和内连接查询到的结果一样，都为两张表的交集数据

等值连接： select * from A,B where A.x=B.x and A.age=18;

内连接：select * from A join B on A.x=B.x where A.age=18;

1.查询每个员工的姓名和对应的部门名称

select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

2.查询在new york工作的所有员工信息

select e.* from emp e join dept d on e.deptno=d.deptno where d.loc='new york';

外链接

        右连接：会显示所有的副表信息

        左连接：会显示所有主表的所有信息

格式：

  select * from A left/right join B on A.x=B.x where A.age=18;

1.查询部门表的全部名称和对应的员工姓名

select d.dname,e.ename  from emp e right join dept d on e.deptno=d.deptno;

关联查询总结

关联查询的查询方式包括三种： 等值连接、内连接和外链接

如果查询两张表的交集数据使用等值连接和内连接，推荐使用内连接

如果查询一张表的全部数据和另外一张表的交集数据使用外连接，外链接只需要掌握一种即可

表设计之关联关系

在创建表时设置外键约束（一张表中的 字段是另一张表中的主键 我们就成为这个字段是外键）

通过 FOREIGN KEY 关键字来指定外键。

具体的语法格式如下：constraint fk_表名1_表名2

                                     foreign key(字节段) references 设置主键的表名(字节段);

示例：

创建 tb_dept1 的 SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );

创建数据表 tb_emp6，并在表 tb_emp6 上创建外键约束，让它的键 deptId 作为外键关联到表 tb_dept1 的主键 id，SQL 语句和运行结果如下所示。

mysql> CREATE TABLE tb_emp6
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_dept1
    -> FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
    -> );

以上语句执行成功之后，在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束，外键名称为 deptId，其依赖于表 tb_dept1 的主键 id。

注意：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致。

例如，两者都是 INT 类型，或者都是 CHAR 类型。如果不满足这样的要求，在创建从表时，就会出现“ERROR 1005(HY000): Can't create table”错误。

在修改表时添加外键约束

外键约束也可以在修改表时添加，但是添加外键约束的前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。

在修改数据表时添加外键约束的语法格式如下：

alter table <数据表名> add constraint <外键名>
 foreign key(<列名>) references <主表名> (<列名>);