连表查询

连表查询

6.连表查询

从一张表中单独查询，称为单表查询
emp表和dept表联合起来查询数据，从emp表中取员工名字，从dept表中取部门名字。
这种跨表查询，多张表联合起来查询数据，被称为连接查询。

select ename,dname from emp, dept;

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的乘积，这种现象被称为：笛卡尔积现象。

6.1内连接之等值连接

查询每个员工所在部门名称，显示员工名和部门名？

emp e和dept d表进行连接。条件是：e.deptno = d.deptno

SQL92语法：
  select 
    e.ename,d.dname
  from
    emp e, dept d
  where
    e.deptno = d.deptno;
  sql92的缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。
SQL99语法：
  //inner可以省略（带着inner可读性更好！！！一眼就能看出来是内连接）
  select 
    e.ename,d.dname
  from
    emp e
  inner join
    dept d
  on
    e.deptno = d.deptno; // 条件是等量关系，所以被称为等值连接。
  sql99优点：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where

6.2内连接之非等值连接

找出每个员工的薪资等级，要求显示员工名、薪资、薪资等级？

select 
  e.ename, e.sal, s.grade
from
  emp e
join
  salgrade s
on
  e.sal between s.losal and s.hisal; // 条件不是一个等量关系，称为非等值连接。

6.3内连接自己连接

查询员工的上级领导，要求显示员工名和对应的领导名？

技巧：一张表看成两张表。
select 
  a.ename as '员工名', b.ename as '领导名'
from
  emp a
join
  emp b
on
  a.mgr = b.empno; //员工的领导编号 = 领导的员工编号

6.4外连接

内连接：（A和B连接，AB两张表没有主次关系。平等的。）
内连接的特点：完成能够匹配上ON后面的条件的数据查询出来。

// outer是可以省略的，带着可读性强。
select 
  e.ename,d.dname
from
  emp e 
right outer join 
  dept d
on
  e.deptno = d.deptno;

right代表什么：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表。
在外连接当中，两张表连接，产生了主次关系。

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

带有right的是右外连接，又叫做右连接。
带有left的是左外连接，又叫做左连接。
任何一个右连接都有左连接的写法。
任何一个左连接都有右连接的写法。

6.5多表连接

三张表，四张表怎么连接？

  语法：
    select 
      ...
    from
      a
    join
      b
    on
      a和b的连接条件
    join
      c
    on
      a和c的连接条件
    right join
      d
    on
      a和d的连接条件

找出每个员工的部门名称以及工资等级，要求显示员工名、部门名、薪资、薪资等级？

select 
    e.ename,e.sal,d.dname,s.grade
  from
    emp e
  join
    dept d
  on 
    e.deptno = d.deptno
  join
    salgrade s
  on
    e.sal between s.losal and s.hisal;

找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级？

select 
  e.ename,e.sal,d.dname,s.grade,l.ename
from
  emp e
join
  dept d
on 
  e.deptno = d.deptno
join
  salgrade s
on
  e.sal between s.losal and s.hisal
left join
  emp l
on
  e.mgr = l.empno;

7.子查询

select语句中嵌套select语句，被嵌套的select语句称为子查询。
子查询可以出现在 select后面，from 后面，where 后面。

7.1where子句中的子查询

找出比最低工资高的员工姓名和工资？

select ename,sal from emp where sal > (select min(sal) from emp);

7.2from子句中的子查询

from后面的子查询，可以将子查询的查询结果当做一张临时表。（技巧）

找出每个岗位的平均工资的薪资等级。

select 
      t.*, s.grade
    from
      (select job,avg(sal) as avgsal from emp group by job) t
    join
      salgrade s
    on
      t.avgsal between s.losal and s.hisal;

8.约束

在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！

8.1约束类型

非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）

我们这里重点学习四个约束：not null，unique，primary key，foreign key。

8.2 not null

非空约束not null约束的字段不能为NULL。

drop table if exists t_vip;
  create table t_vip(
    id int,
    name varchar(255) not null  // not null只有列级约束，没有表级约束！
  );
insert into t_vip(id,name) values(1,'zhangsan');

insert into t_vip(id) values(3);
  ERROR 1364 (HY000): Field 'name' doesn't have a default value

8.3unique

唯一性约束unique约束的字段不能重复，但是可以为NULL。

  drop table if exists t_vip;
  create table t_vip(
    id int,
    name varchar(255) unique,
    email varchar(255)
  );
  insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
  insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
  select * from t_vip;

  insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
  ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

  insert into t_vip(id) values(4);
  insert into t_vip(id) values(5);
  +------+----------+------------------+
  | id   | name     | email            |
  +------+----------+------------------+
  |    1 | zhangsan | zhangsan@123.com |
  |    2 | lisi     | lisi@123.com     |
  |    3 | wangwu   | wangwu@123.com   |
  |    4 | NULL     | NULL             |
  |    5 | NULL     | NULL             |
  +------+----------+------------------+
  name字段虽然被unique约束了，但是可以为NULL。

8.3.1联合唯一

name和email两个字段联合起来具有唯一性！！！！

drop table if exists t_vip;
create table t_vip(
  id int,
  name varchar(255),
  email varchar(255),
  unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
  name和email两个字段联合起来唯一！！！

insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
  ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'

在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（注意：oracle中不一样！）

8.4 primary key

主键值是每一行记录的唯一标识。主键值是每一行记录的身份证号！！！
主键的特征：not null + unique（主键值不能是NULL，同时也不能重复！）

drop table if exists t_vip;
    // 1个字段做主键，叫做：单一主键
    create table t_vip(
      id int primary key,  //列级约束
      name varchar(255),
            primary key(id)  // 表级约束
    );
    insert into t_vip(id,name) values(1,'zhangsan');
    insert into t_vip(id,name) values(2,'lisi');

    //错误：不能重复
    insert into t_vip(id,name) values(2,'wangwu');
    ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

    //错误：不能为NULL
    insert into t_vip(name) values('zhaoliu');
    ERROR 1364 (HY000): Field 'id' doesn't have a default value

8.4.1复合主键

drop table if exists t_vip;
    // id和name联合起来做主键：复合主键！！！！
    create table t_vip(
      id int,
      name varchar(255),
      email varchar(255),
      primary key(id,name)
    );
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');

//错误：不能重复
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
  ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'

在实际开发中不建议使用：复合主键。建议使用单一主键！
因为主键值存在的意义就是这行记录的身份证号，只要意义达到即可，单一主键可以做到。

一个表中主键约束能加两个吗？
    drop table if exists t_vip;
    create table t_vip(
      id int primary key,
      name varchar(255) primary key
    );
    ERROR 1068 (42000): Multiple primary key defined

结论：一张表，主键约束只能添加1个。（主键只能有1个。）

主键分类

自然主键：主键值是一个自然数，和业务没关系。
业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！

8.4.2主键自增

在mysql当中，有一种机制，可以帮助我们自动维护一个主键值

rop table if exists t_vip;
create table t_vip(
  id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！
  name varchar(255)
);
    insert into t_vip(name) values('zhangsan');
    insert into t_vip(name) values('zhangsan');
    insert into t_vip(name) values('zhangsan');
    insert into t_vip(name) values('zhangsan');
    insert into t_vip(name) values('zhangsan');
    insert into t_vip(name) values('zhangsan');

    +----+----------+
    | id | name     |
    +----+----------+
    |  1 | zhangsan |
    |  2 | zhangsan |
    |  3 | zhangsan |
    |  4 | zhangsan |
    |  5 | zhangsan |

8.5 foreign key

如果一个实体的某个字段指向另一个实体的主键，就称为外键
被指向的实体，称之为主实体（主表），也叫父实体（父表）。
负责指向的实体，称之为从实体（从表），也叫子实体（子表）

create table t_class(
  classno int primary key,
  classname varchar(255)
);
create table t_student(
  no int primary key auto_increment,
  name varchar(255),
  cno int,
  foreign key(cno) references t_class(classno)
);

insert into t_class(classno, classname) values(100, '北京市大兴区亦庄镇第二中学高三1班');
insert into t_class(classno, classname) values(101, '北京市大兴区亦庄镇第二中学高三1班');

insert into t_student(name,cno) values('jack', 100);
insert into t_student(name,cno) values('lilei', 100);
insert into t_student(name,cno) values('hanmeimei', 100);
insert into t_student(name,cno) values('zhangsan', 101);
insert into t_student(name,cno) values('lisi', 101);

外键可以为空，可以理解成一名学生肯定会关联到一个存在的班级，但来了一个转校生，还没有分班，他现在属于学生子表，但还没有关联到班级主表中的任何一条记录。

t_class是父表，t_student是子表

    **删除表**的顺序？    先删子，再删父。

    **创建表**的顺序？    先创建父，再创建子。

    **删除数据**的顺序？先删子，再删父。

    **插入数据**的顺序？先插入父，再插入子

子表中的外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束。

连表查询

连表查询

目录

6.连表查询

6.1内连接之等值连接

6.2内连接之非等值连接

6.3内连接自己连接

6.4外连接

6.5多表连接

7.子查询

7.1where子句中的子查询

7.2from子句中的子查询

8.约束

8.1约束类型

8.2 not null

8.3unique

8.3.1联合唯一

8.4 primary key

8.4.1复合主键

8.4.2主键自增

8.5 foreign key