Mysql系列基础篇-SQL分类（多表的DQL）

数据库设计

多表关系

一对一

如：人和身份证
分析：一个人只有一个身份证，一个身份证只能对应一个人
实现方式：可以在任意一一方添加唯一外键指向另外一方的主键

一对多（多对一）

如：部门和员工
分析：一个部门有多个员工，一个员工只能对应一个部门
实现方式：在多的一方建立外键，指向一的一方的主键

多对多

如：学生和课程
分析：一个学生可以选多门课程，一个课程也可以被很多学生选择
实现方式：需要借助第三张中间表。中间表至少包含2个字段，这两个字段作为第三张表的外键，分别指向两张表的外键
案例：用户收藏旅游线路
创建旅游线路分类表

mysql> create table tab_category(cid int primary key auto_increment,cname varchar(100) not null unique);

创建旅游线路表

mysql> create table tab_route(
    -> rid int primary key auto_increment,
    -> rname varchar(100) not null unique,
    -> price double,
    -> rdate date,
    -> cid int,
    -> foreign key(cid) references tab_category(cid));

创建用户表

mysql> create table tab_user(
    -> uid int primary key auto_increment,
    -> username varchar(100) unique not null,
    -> password varchar(30) not null,
    -> name varchar(100),
    -> birthday date,
    -> sex char(1) default '男',
    -> telephone varchar(11),
    -> email varchar(100));

创建中间表

mysql> create table tab_favorite(
   -> rid int,  
   -> DATE DATETIME,
   -> uid int,
   -> primary key(rid,uid),    --rid和uid不能重复，设置联合主键，同一个用户不能收藏同一线路两次
   -> foreign key(rid) references tab_route(rid),
   -> foreign key(uid) references tab_user(uid));

多表查询

笛卡尔积

• 有两个集合A和B，取这两个集合的所有组成情况
• 要完成多表查询，需要消除无用的数据

部门表
create table depts(
id int primary key auto_increment,
name varhcar(32) not null);
员工表
create table emps(
id int primary key auto_increment,
name varchar(32),
gender char(1),
salary double,
join_date date,
depts_id int,
constraint depts_emps_s_emps_fk foreign key (depts_id) references depts(id));

内连接

基本思路：
1：从哪些表中查询数据
2：条近是什么
3：查询哪些字段

• 隐式内连接
  使用where条件消除无用的数据

查询所有员工信息和部门信息
select * from emps,depts where emps.depts_id=depts.id;
查询员工的姓名、性别和员工部门名称
select 
t1.name,
t1.gender,
t2.name 
from 
emps t1,
depts t2 
where 
t1.depts_id = t2.id;

• 显式内连接
  语法：
  select
  字段列表
  from
  表名1
  [ inner ] join
  表名2
  on
  条件 ；

select 
t1.name,
t1.gender,
t2.name 
from 
emps t1 
inner join 
depts t2
on 
t1.depts_id = t2.id;

外连接

左外连接：
语法：
select
字段列表
from
emps t1
left [outer] join
depts t2
on
t1.dept_id = t2.id;
查询的是左表的所以数据以及其交集部分

mysql> select t1.*,t2.name from emps t1 left outer join depts t2 on t1.depts_id = t2.id;
+----+--------+--------+--------+------------+----------+-----------+
| id | name   | gender | salary | join_date  | depts_id | name      |
+----+--------+--------+--------+------------+----------+-----------+
|  1 | 张三   | 男     |   5000 | 1997-10-01 |        1 | 开发部    |
|  4 | 小红   | 女     |   7000 | 1978-10-01 |        1 | 开发部    |
|  2 | 李四   | 男     |   6000 | 1998-10-01 |        2 | 市场部    |
|  5 | 小丽   | 女     |   4000 | 2000-10-01 |        2 | 市场部    |
|  3 | 王二   | 男     |   6000 | 1949-10-01 |        3 | 财务部    |
|  6 | 小明   | 男     |   3000 | NULL       |     NULL | NULL      |
+----+--------+--------+--------+------------+----------+-----------+
mysql> select t1.*,t2.name from depts t2 left outer join emps t1  on t1.depts_id = t2.id;
+------+--------+--------+--------+------------+----------+-----------+
| id   | name   | gender | salary | join_date  | depts_id | name      |
+------+--------+--------+--------+------------+----------+-----------+
|    1 | 张三   | 男     |   5000 | 1997-10-01 |        1 | 开发部    |
|    2 | 李四   | 男     |   6000 | 1998-10-01 |        2 | 市场部    |
|    3 | 王二   | 男     |   6000 | 1949-10-01 |        3 | 财务部    |
|    4 | 小红   | 女     |   7000 | 1978-10-01 |        1 | 开发部    |
|    5 | 小丽   | 女     |   4000 | 2000-10-01 |        2 | 市场部    |
+------+--------+--------+--------+------------+----------+-----------+

右外连接：
语法：
select
字段列表
from
emps t1
right [outer] join depts t2
on t1.dept_id = t2.id;
查询的是右表的所以数据以及其交集部分

mysql> select t1.*,t2.name from emps t1 right outer join depts t2 on t1.depts_id = t2.id;
+------+--------+--------+--------+------------+----------+-----------+
| id   | name   | gender | salary | join_date  | depts_id | name      |
+------+--------+--------+--------+------------+----------+-----------+
|    1 | 张三   | 男     |   5000 | 1997-10-01 |        1 | 开发部    |
|    2 | 李四   | 男     |   6000 | 1998-10-01 |        2 | 市场部    |
|    3 | 王二   | 男     |   6000 | 1949-10-01 |        3 | 财务部    |
|    4 | 小红   | 女     |   7000 | 1978-10-01 |        1 | 开发部    |
|    5 | 小丽   | 女     |   4000 | 2000-10-01 |        2 | 市场部    |
+------+--------+--------+--------+------------+----------+-----------+
mysql> select t1.*,t2.name from depts t2 right outer join emps t1  on t1.depts_id = t2.id;
+----+--------+--------+--------+------------+----------+-----------+
| id | name   | gender | salary | join_date  | depts_id | name      |
+----+--------+--------+--------+------------+----------+-----------+
|  1 | 张三   | 男     |   5000 | 1997-10-01 |        1 | 开发部    |
|  4 | 小红   | 女     |   7000 | 1978-10-01 |        1 | 开发部    |
|  2 | 李四   | 男     |   6000 | 1998-10-01 |        2 | 市场部    |
|  5 | 小丽   | 女     |   4000 | 2000-10-01 |        2 | 市场部    |
|  3 | 王二   | 男     |   6000 | 1949-10-01 |        3 | 财务部    |
|  6 | 小明   | 男     |   3000 | NULL       |     NULL | NULL      |
+----+--------+--------+--------+------------+----------+-----------+

子查询

• 子查询的结果是单行单列的，使用运算符来判断
  查询员工的工资小于平均工资的人

mysql> select * from emps where salary < (select avg(salary) from emps);
+----+--------+--------+--------+------------+----------+
| id | name   | gender | salary | join_date  | depts_id |
+----+--------+--------+--------+------------+----------+
|  1 | 张三   | 男     |   5000 | 1997-10-01 |        1 |
|  5 | 小丽   | 女     |   4000 | 2000-10-01 |        2 |
|  6 | 小明   | 男     |   3000 | NULL       |     NULL |
+----+--------+--------+--------+------------+----------+

查询“财务部”所有员工的信息

mysql> select * from emps where depts_id = (select id from depts where name = '财务部');
+----+--------+--------+--------+------------+----------+
| id | name   | gender | salary | join_date  | depts_id |
+----+--------+--------+--------+------------+----------+
|  3 | 王二   | 男     |   6000 | 1949-10-01 |        3 |
+----+--------+--------+--------+------------+----------+

• 子查询的结果是多行单列的，使用运算符in来进行判断
  查询“财务部”和“开发部”所有员工的信息

mysql> select * from emps where id in (select id from depts where name = '财务部' or name = '开发部');
+----+--------+--------+--------+------------+----------+
| id | name   | gender | salary | join_date  | depts_id |
+----+--------+--------+--------+------------+----------+
|  1 | 张三   | 男     |   5000 | 1997-10-01 |        1 |
|  3 | 王二   | 男     |   6000 | 1949-10-01 |        3 |
+----+--------+--------+--------+------------+----------+

• 子查询的结果是多行多列的，子查询作为一张虚拟表参与查询

mysql> select * from depts t1,(select * from emps where join_date < '1998-10-01') t2 where t1.id = t2.depts_id;
+----+-----------+----+--------+--------+--------+------------+----------+
| id | name      | id | name   | gender | salary | join_date  | depts_id |
+----+-----------+----+--------+--------+--------+------------+----------+
|  1 | 开发部    |  1 | 张三   | 男     |   5000 | 1997-10-01 |        1 |
|  1 | 开发部    |  4 | 小红   | 女     |   7000 | 1978-10-01 |        1 |
|  3 | 财务部    |  3 | 王二   | 男     |   6000 | 1949-10-01 |        3 |
+----+-----------+----+--------+--------+--------+------------+----------+

使用内连接代替子查询

mysql> select t1.*,t2.name  from emps t1 right join depts t2 on t1.depts_id = t2.id where join_date < '1998-10-01';
+------+--------+--------+--------+------------+----------+-----------+
| id   | name   | gender | salary | join_date  | depts_id | name      |
+------+--------+--------+--------+------------+----------+-----------+
|    1 | 张三   | 男     |   5000 | 1997-10-01 |        1 | 开发部    |
|    4 | 小红   | 女     |   7000 | 1978-10-01 |        1 | 开发部    |
|    3 | 王二   | 男     |   6000 | 1949-10-01 |        3 | 财务部    |
+------+--------+--------+--------+------------+----------+-----------+