一、多表關(guān)系

多表關(guān)系

一對多

多對多

# 多對多
# 學生表
create table student(
    id      int primary key auto_increment  comment '主鍵ID',
    name    varchar(10)                     comment '姓名',
    no      varchar(10)                     comment '學號'
) comment '學生表';

insert into student
values
    (null, '黛綺絲','2000100101'),
    (null, '謝遜','2000100102'),
    (null, '殷天正','2000100103'),
    (null, '韋一笑','2000100104');

# 課程表
create table course(
    id      int primary key auto_increment  comment '主鍵ID',
    name    varchar(10)                     comment '課程名稱'
) comment '課程表';

insert into course
values
    (null, 'Java'),
    (null, 'PHP'),
    (null, 'MySQL'),
    (null, 'Hadoop');

# 學生課程關(guān)系表
create table student_course(
    id          int primary key  auto_increment     comment 'ID',
    studentid   int not null                        comment '學生ID',
    courseid    int not null                        comment '課程id',
    constraint fk_courseid foreign key (courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references student(id)
) comment '學生課程關(guān)系表';

insert into student_course
values
    (null, 1, 1),
    (null, 1, 2),
    (null, 1, 3),
    (null, 2, 1),
    (null, 2, 4);

外鍵關(guān)系表：

外鍵關(guān)系表

一對一

# 一對一
create table tb_user(
    id      int primary key auto_increment  comment 'ID',
    name    varchar(10)                     comment '姓名',
    age     int                             comment '年齡',
    gender  char(1)                         comment '性別：1.男 2.女',
    phone   char(11)                        comment '手機號'
) comment '用戶基本信息表';

create table tb_user_edu(
    id              int primary key auto_increment  comment 'ID',
    degree          varchar(20)                     comment '學歷',
    major           varchar(50)                     comment '專業(yè)',
    primaryschool   varchar(50)                     comment '小學',
    middleschool    varchar(50)                     comment '中學',
    university      varchar(50)                     comment '大學',
    userid          int unique                      comment '用戶id',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用戶教育信息表';


insert into tb_user
values
    (null, '黃渤', 45, 1, 18800001111),
    (null, '冰冰', 35, 2, 18800002222),
    (null, '碼云', 55, 1, 18800008888),
    (null, '李彥宏', 50, 1, 18800009999);

insert into tb_user_edu
values
    (null, '本科','舞蹈','靜安區(qū)第一小學','靜安區(qū)第一中學','北京舞蹈學院',1),
    (null, '碩士','表演','朝陽區(qū)第一小學','朝陽區(qū)第一中學','北京電影學院',2),
    (null, '本科','英語','杭州市第一小學','杭州市第一中學','杭州師范大學',3),
    (null, '本科','應用數(shù)學','陽泉第一小學','陽泉區(qū)第一中學','清華大學',4);

二、多表查詢概述

# 準備數(shù)據(jù)
create table dept(
    id      int primary key auto_increment  comment 'ID',
    name    varchar(50) not null            comment '部門名稱'
) comment '部門表';

insert into dept(id, name)
values
    (1, '研發(fā)部'),
    (2, '市場部'),
    (3, '財務部'),
    (4, '銷售部'),
    (5, '總經(jīng)辦');

create table emp (
    id          int primary key auto_increment      comment 'ID',
    name        varchar(50) not null                comment  '名稱',
    age         int check ( age > 0 && age < 120 )  comment '年齡',
    job         varchar(20)                         comment '職位',
    salary      int                                 comment '薪資',
    entrydate   date                                comment '入職時間',
    managerid   int                                 comment '直屬領導id',
    dept_id     int                                 comment '部門id'
) comment '員工表';

insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id)
values
        (1, '金庸',66,'總裁', 20000, '2000-01-01', null, 5),
       (2, '張無忌',20,'項目經(jīng)理', 12500, '2005-12-05', 1, 1),
       (3, '楊逍',33,'開發(fā)', 8400, '2000-11-03', 2, 1),
       (4, '韋一笑',48,'開發(fā)', 11000, '2002-02-05', 2, 1),
       (5, '常遇春',43,'開發(fā)', 10500, '2004-09-07', 3, 1),
       (6, '小昭',19,'程序員鼓勵師', 6600, '2004-10-12', 2, 1);

insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id)
values
    (7, '滅絕',60,'財務總監(jiān)', 8500, '2002-09-12', 1, 3),
    (8, '周芷若',19,'會計', 4800, '2006-06-02', 7, 3),
    (9, '丁敏君',23,'出納', 5250, '2009-05-13', 7, 3),
    (10, '趙敏',20,'市場部總監(jiān)', 12500, '2004-10-12', 1, 2),
    (11, '鹿杖客',56,'職員', 3750, '2006-10-03', 10, 2),
    (12, '鶴筆翁',19,'職員', 3750, '2007-05-09', 10, 2),
    (13, '房東白',19,'職員', 5500, '2009-02-12', 10, 2),
    (14, '張三豐',88,'銷售總監(jiān)', 14000, '2004-10-12', 1, 4),
    (15, '俞蓮舟',38,'銷售', 4600, '2004-10-12', 14, 4),
    (16, '宋遠橋',40,'銷售', 4600, '2004-10-12', 14, 4),
    (17, '陳友諒',42,null, 2000, '2011-10-12', 1, null);

多表查詢-笛卡爾積

消除笛卡爾積

# 多表查詢 -- 笛卡爾積
select * from emp, dept where emp.dept_id = dept.id;

多表查詢分類

三、內(nèi)連接（39）

內(nèi)連接

- 隱式內(nèi)連接
select 字段列表 from 表1, 表2 where 條件...;

- 顯式內(nèi)連接
select 字段列表 from 表1 [inner] join 表2 on 連接條件...;

內(nèi)連接查詢的是兩張表交集的部分

演示：

查詢每一個員工的姓名，及關(guān)聯(lián)的部門的名稱（隱式內(nèi)連接實現(xiàn)）
查詢每一個員工的姓名，及關(guān)聯(lián)的部門的名稱（顯式內(nèi)連接實現(xiàn)）

# 1. 查詢每一個員工的姓名，及關(guān)聯(lián)的部門的名稱（隱式內(nèi)連接實現(xiàn)）
# -- 表結(jié)構(gòu)：emp, dept
# -- 連接條件: emp.dept_id = dept.id;
select emp.name,dept.name from emp, dept where emp.dept_id = dept.id;

select e.name, d.name from emp e, dept d where e.dept_id = d.id;

# 2. 查詢每一個員工的姓名，及關(guān)聯(lián)的部門的名稱（顯式內(nèi)連接實現(xiàn)）
# -- 表結(jié)構(gòu)：emp, dept
# -- 連接條件: emp.dept_id = dept.id;
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;

select e.name, d.name from emp e join dept d on e.dept_id = d.id;

四、外連接

外連接

- 左外連接
select 字段列表 from 表1 left [outer] join 表2 on 條件...;

- 右外連接
select 字段列表 from 表1 right [outer] join 表2 on 條件...;

演示：

查詢emp表的所有數(shù)據(jù)，和對應的部門信息（左外連接）
2.查詢dept表的所有數(shù)據(jù)，和對應的員工信息（右外連接）

# 演示：
# 1.查詢emp表的所有數(shù)據(jù)，和對應的部門信息（左外連接）
select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;

select e.*,d.name from emp e left join dept d on e.dept_id = d.id;


# 2.查詢dept表的所有數(shù)據(jù)，和對應的員工信息（右外連接）
select d.*, e.* from emp e right outer join dept d on e.dept_id=d.id;

select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;

五、自連接

自連接

演示：

查詢員工及其所屬領導的名字
查詢所有員工 emp 及其領導的名字 emp，如果員工沒有領導，也需要查詢出來

# 演示：
# 1. 查詢員工 及其 所屬領導的名字
# -- 表結(jié)構(gòu)： emp a, emp b
# -- 條件：e1.managerid = e2.id
select a.name, b.name from emp a, emp b where a.managerid = b.id;


# 2. 查詢所有員工 emp 及其領導的名字 emp，如果員工沒有領導，也需要查詢出來
# -- 表結(jié)構(gòu)： emp a, emp b
select a.name '員工', b.name '領導' from emp a left join emp b on a.managerid = b.id;

聯(lián)合查詢-union，union all

聯(lián)合查詢

select 字段列表 form 表A ...
union [all]
select 字段列表 form 表B ...;

演示：

將薪資低于 5000 的員工, 和年齡大于 50 歲的員工全部查詢出來。

# 演示：
# 1. 將薪資低于 5000 的員工, 和 年齡大于 50 歲的員工全部查詢出來。
select * from emp where salary < 5000
union
select * from emp where age > 50;

六、子查詢

子查詢

標量子查詢

演示：

查詢“銷售部” 的所有員工信息
查詢在 “方東白” 入職后的員工信息

# 演示：
# 1. 查詢“銷售部” 的所有員工信息
# -- a. 查詢 "銷售部" 部門id
select id from dept where name = '銷售部';

# -- b. 根據(jù)銷售部id，查詢員工信息
select * from emp where dept_id = (select id from dept where name = '銷售部');

# 2. 查詢在 “方東白” 入職后的員工信息
# -- a. 查詢 ‘東方白’ 入職時間
select entrydate from emp where name = '房東白';
# -- b. 根據(jù)‘房東白’的入職時間，查詢?nèi)肼殨r間以后的員工信息
select * from emp where entrydate > (select entrydate from emp where name = '房東白');

6.2 列子查詢

列子查詢

練習：

查詢“銷售部” 和 “市場部” 的所有員工信息
查詢比財務部所有人工資都高的員工信息
查詢比研發(fā)部其中任意一人工資高的員工信息

# 1. 查詢“銷售部” 和 “市場部” 的所有員工信息
# -- a. 查詢 “銷售部” 和 “市場部” 的部門id
select id from dept where name = '銷售部' or name = '市場部';

# -- b. 根據(jù)id 查詢所有的員工信息
select * from emp where dept_id in (select id from dept where name = '銷售部' or name = '市場部');

# 2. 查詢比財務部所有人工資都高的員工信息
# -- a. 查詢財務部的所有人工資
select id from dept where name = '財務部';
select salary from emp where dept_id = (select id from dept where name = '財務部');

# -- b. 查詢比這個工資高的員工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '財務部'));


# 3. 查詢比研發(fā)部其中任意一人工資高的員工信息
# -- a. 查詢'研發(fā)部'的所有人的工資
select salary from emp where dept_id = (select id from dept where name = '研發(fā)部');
# -- b. 查詢 比 研發(fā)部任意一個人工資高的員工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研發(fā)部'));
select * from emp where salary > some (select salary from emp where dept_id = (select id from dept where name = '研發(fā)部'));

6.3 行子查詢

行子查詢

作業(yè)：

查詢與 “張無忌” 的薪資及直屬領導相同的員工信息；

# 1. 查詢與 “張無忌” 的薪資及直屬領導相同的員工信息；
# -- a. 查詢張無忌的 薪資 及直屬領導
select salary, managerid from emp where name = '張無忌';

# -- b. 查詢與‘張無忌’的薪資及直屬領導相同員工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '張無忌');

6.4 表子查詢

表子查詢

練習：

查詢與“鹿杖客”, “宋遠橋” 的職位和薪資相同的員工信息
查詢?nèi)肼毴掌谑?“2006-01-01” 之后的員工信息，及其部門信息

# 1. 查詢與“鹿杖客”, “宋遠橋” 的職位和薪資相同的員工信息
# -- a. 查詢與“鹿杖客”, “宋遠橋” 的職位和薪資
select job, salary from emp where name = '鹿杖客' or name = '宋遠橋';
# -- b. 查詢與“鹿杖客”, “宋遠橋” 的職位和薪資相同的員工信息
select * from emp where (job, salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋遠橋');

# 2. 查詢?nèi)肼毴掌谑?“2006-01-01” 之后的員工信息，及其部門信息
# -- a. 查詢?nèi)肼毴掌谑?“2006-01-01” 之后的員工信息
select * from emp where entrydate > '2006-01-01';

# -- b. 查詢?nèi)肼毴掌谑?“2006-01-01” 之后的員工信息，及其部門信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on d.id = e.dept_id

七、多表查詢案例

練習：根據(jù)需求，完成SQL語句的編寫

查詢員工的姓名、年齡、職位、部門信息。
查詢年齡小于30歲的員工姓名、年齡、職位、部門信息。
查詢擁有員工的部門ID、部門名稱。
查詢所有年齡大于40歲的員工，及其歸屬的部門名稱；如果員工沒有分配部門，也需要展示出來。
查詢所有員工的工資等級。
查詢“研發(fā)部”所有員工的信息及工資等級。
查詢“研發(fā)部”員工的平均工資。
查詢工資比“滅絕”高的員工信息。
查詢比平均薪資高的員工信息。
查詢低于本部門平均工資的員工人數(shù)。
查詢所有的部門信息，并統(tǒng)計部門的員工人數(shù)。
查詢所有學生的選課情況，展示出學生名稱，學號，課程名稱。

# 準備數(shù)據(jù)
create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪資等級表';

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

總結(jié)

多表關(guān)系

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySql基礎四-多表查詢

MySql基礎四-多表查詢

一、多表關(guān)系

二、多表查詢概述

三、內(nèi)連接（39）

四、外連接

五、自連接

聯(lián)合查詢-union，union all

六、子查詢

6.2 列子查詢

6.3 行子查詢

6.4 表子查詢

七、多表查詢案例

總結(jié)

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySql基礎四-多表查詢

一、多表關(guān)系

二、多表查詢概述

三、內(nèi)連接（39）

四、外連接

五、自連接

聯(lián)合查詢-union，union all

六、子查詢

6.2 列子查詢

6.3 行子查詢

6.4 表子查詢

七、多表查詢案例

總結(jié)

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

一、多表關(guān)系

四、外連接

五、自連接

聯(lián)合查詢-union，union all

七、多表查詢案例