多表關(guān)系：

?? ?項(xiàng)目開(kāi)發(fā)中，在進(jìn)行數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)時(shí)，會(huì)根據(jù)業(yè)務(wù)需求及業(yè)務(wù)模塊之間的關(guān)系，分析并設(shè)計(jì)表結(jié)構(gòu)。由于業(yè)務(wù)之間相互關(guān)聯(lián)，所以各個(gè)表結(jié)構(gòu)之間也存在著各種聯(lián)系，基本上分為三種。

?? ??? ??? ?外鍵：

?? ??? ??? ??? ?創(chuàng)建表時(shí)添加外鍵：

? ?-- 創(chuàng)建表時(shí)指定
? ? ????????create table 表名(
? ? ????????????????字段名 數(shù)據(jù)類(lèi)型,
? ? ????????????????...
? ????????????????? [constraint] [外鍵名稱(chēng)] foreign key (外鍵字段名) references 主表 (字段名)?? ?
????????????????);

?示例SQL：

#創(chuàng)建一個(gè)用戶(hù)表
create table tb_user(
    id int unsigned  primary key auto_increment comment 'ID',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性別, 1 男  2 女'
) comment '用戶(hù)信息表';


# 創(chuàng)建一個(gè) 用戶(hù)詳細(xì)信息表
create table tb_user_card(
    id int unsigned  primary key auto_increment comment 'ID',
    nationality varchar(10) not null comment '民族',
    birthday date not null comment '生日',
    idcard char(18) not null comment '身份證號(hào)',
    issued varchar(20) not null comment '簽發(fā)機(jī)關(guān)',
    expire_begin date not null comment '有效期限-開(kāi)始',
    expire_end date comment '有效期限-結(jié)束',
    user_id int unsigned not null unique comment '用戶(hù)ID',
    constraint fk_user_id foreign key (user_id) references tb_user(id)  //創(chuàng)建外鍵
) comment '用戶(hù)信息表';

?? ??? ??? ??? ?創(chuàng)建完表之后添加外鍵：

?? ??? ??? ??? ??? ?-- 建完表后，添加外鍵

alter table 表名 add constraint 外鍵名稱(chēng) foreign key (外鍵字段名) references 主表(字段名);

# 給emp表添加一個(gè)外鍵  一個(gè)部門(mén)對(duì)應(yīng)多個(gè)員工，一個(gè)員工所屬一個(gè)部門(mén)，dept表是父表，emp表是子表
alter table emp add  constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

?? ??? ??? ??? ?多表操作中保證數(shù)據(jù)的一致性，完整性和正確性。

? ??? ??? ??? ?物理外鍵：

? ? 概念：使用foreign key定義外鍵關(guān)聯(lián)另外一張表。
? ? ?缺點(diǎn)：
? ? ? ? ? ? ? ?影響增刪改的效率（需要檢查外鍵關(guān)系）
? ? ? ? ? ? ? ?僅用于單節(jié)點(diǎn)數(shù)據(jù)庫(kù)，不適用于分布式，集群場(chǎng)景
? ? ? ? ? ? ? ?容易引發(fā)數(shù)據(jù)庫(kù)死鎖問(wèn)題，消耗性能

?? ??? ??? ??? ??? ??? ?邏輯外鍵：

?? ??? ??? ??? ??? ??? ??? ?概念：在業(yè)務(wù)邏輯層中，解決外鍵關(guān)聯(lián)。
?? ??? ??? ??? ??? ??? ??? ?通過(guò)邏輯外鍵，就可以很方便的解決上述問(wèn)題。

?? ??? ?一對(duì)多（多對(duì)一）

?? ??? ??? ?部門(mén)和員工的關(guān)系（一個(gè)部門(mén)有多個(gè)員工）
?? ??? ??? ??? ?在數(shù)據(jù)庫(kù)表中多的一方，添加字段，來(lái)關(guān)聯(lián)一的一方的主鍵。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? emp表? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? dept表

SQL代碼示例：

CREATE TABLE dept (
  id int unsigned PRIMARY KEY AUTO_INCREMENT COMMENT 'ID, 主鍵',
  name varchar(10) NOT NULL UNIQUE COMMENT '部門(mén)名稱(chēng)',
  create_time datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  update_time datetime DEFAULT NULL COMMENT '修改時(shí)間'
) COMMENT '部門(mén)表';

INSERT INTO dept VALUES (1,'學(xué)工部','2023-09-25 09:47:40','2023-09-25 09:47:40'),
						  (2,'教研部','2023-09-25 09:47:40','2023-10-09 15:17:04'),
						  (3,'咨詢(xún)部2','2023-09-25 09:47:40','2023-11-30 21:26:24'),
						  (4,'就業(yè)部','2023-09-25 09:47:40','2023-09-25 09:47:40'),
						  (5,'人事部','2023-09-25 09:47:40','2023-09-25 09:47:40'),
						  (15,'行政部','2023-11-30 20:56:37','2023-11-30 20:56:37');




create table emp(
    id int unsigned primary key auto_increment comment 'ID,主鍵',
    username varchar(20) not null unique comment '用戶(hù)名',
    password varchar(50) default '123456' comment '密碼',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性別, 1:男, 2:女',
    phone char(11) not null unique comment '手機(jī)號(hào)',
    job tinyint unsigned comment '職位, 1 班主任, 2 講師 , 3 學(xué)工主管, 4 教研主管, 5 咨詢(xún)師',
    salary int unsigned comment '薪資',
    image varchar(300) comment '頭像',
    entry_date date comment '入職日期',
    dept_id int unsigned comment '部門(mén)ID',
    create_time datetime comment '創(chuàng)建時(shí)間',
    update_time datetime comment '修改時(shí)間'
) comment '員工表';


INSERT INTO emp VALUES 
(1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'5.png','2000-01-01',2,'2023-10-20 16:35:33','2023-11-16 16:11:26'),
(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'01.png','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:37'),
(3,'lujunyi','123456','盧俊義',1,'13309090003',2,8900,'01.png','2008-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:39'),
(4,'wuyong','123456','吳用',1,'13309090004',2,9200,'01.png','2007-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:41'),
(5,'gongsunsheng','123456','公孫勝',1,'13309090005',2,9500,'01.png','2012-12-05',2,'2023-10-20 16:35:33','2023-10-20 16:35:43'),
(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'01.png','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:45'),
(7,'chaijin','123456','柴進(jìn)',1,'13309090007',1,4700,'01.png','2005-08-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:47'),
(8,'likui','123456','李逵',1,'13309090008',1,4800,'01.png','2014-11-09',1,'2023-10-20 16:35:33','2023-10-20 16:35:49'),
(9,'wusong','123456','武松',1,'13309090009',1,4900,'01.png','2011-03-11',1,'2023-10-20 16:35:33','2023-10-20 16:35:51'),
(10,'linchong','123456','林沖',1,'13309090010',1,5000,'01.png','2013-09-05',1,'2023-10-20 16:35:33','2023-10-20 16:35:53'),
(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'01.png','2007-02-01',2,'2023-10-20 16:35:33','2023-10-20 16:35:55'),
(12,'xiaoliguang','123456','小李廣',1,'13309090012',2,10000,'01.png','2008-08-18',2,'2023-10-20 16:35:33','2023-10-20 16:35:57'),
(13,'yangzhi','123456','楊志',1,'13309090013',1,5300,'01.png','2012-11-01',1,'2023-10-20 16:35:33','2023-10-20 16:35:59'),
(14,'shijin','123456','史進(jìn)',1,'13309090014',2,10600,'01.png','2002-08-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:01'),
(15,'sunerniang','123456','孫二娘',2,'13309090015',2,10900,'01.png','2011-05-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:03'),
(16,'luzhishen','123456','魯智深',1,'13309090016',2,9600,'01.png','2010-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:05'),
(17,'liying','12345678','李應(yīng)',1,'13309090017',1,5800,'01.png','2015-03-21',1,'2023-10-20 16:35:33','2023-10-20 16:36:07'),
(18,'shiqian','123456','時(shí)遷',1,'13309090018',2,10200,'01.png','2015-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:09'),
(19,'gudasao','123456','顧大嫂',2,'13309090019',2,10500,'01.png','2008-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:11'),
(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'01.png','2018-01-01',2,'2023-10-20 16:35:33','2023-10-20 16:36:13'),
(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'01.png','2015-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:15'),
(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'01.png','2016-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:17'),
(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'01.png','2012-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:19'),
(24,'tongwei','123456','童威',1,'13309090024',5,5000,'01.png','2006-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:21'),
(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'01.png','2002-01-01',3,'2023-10-20 16:35:33','2023-10-20 16:36:23'),
(26,'yanshun','123456','燕順',1,'13309090026',5,5400,'01.png','2011-01-01',3,'2023-10-20 16:35:33','2023-11-08 22:12:46'),
(27,'lijun','123456','李俊',1,'13309090027',2,6600,'8.png','2004-01-01',2,'2023-10-20 16:35:33','2023-11-16 17:56:59'),
(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'6.png','2007-01-01',3,'2023-10-20 16:35:33','2023-11-17 16:34:22'),
(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'01.png','2020-03-01',NULL,'2023-10-20 16:35:33','2023-10-20 16:36:31'),
(36,'linghuchong','123456','令狐沖',1,'18809091212',2,6800,'1.png','2023-10-19',2,'2023-10-20 20:44:54','2023-11-09 09:41:04');

給emp表添加外鍵約束

# 添加外鍵約束 
    alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

?? ??? ?一對(duì)一

?? ??? ??? ?案例：用戶(hù)和身份證的信息

??關(guān)系：一對(duì)一關(guān)系，多用于單表拆分，將一張表的基礎(chǔ)字段放在一張表中，其他字段放在另一張表中，以提升操作效率。
??實(shí)現(xiàn)：在任意一方加入外鍵，關(guān)聯(lián)另一方的主鍵，并且設(shè)置外鍵為唯一的（unique）

?

-- ===========================================一對(duì)一=====================================
create table tb_user(
    id int unsigned  primary key auto_increment comment 'ID',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性別, 1 男  2 女',
    phone char(11) comment '手機(jī)號(hào)',
    degree varchar(10) comment '學(xué)歷'
) comment '用戶(hù)信息表';

insert into tb_user values (1,'白眉鷹王',1,'18812340001','初中'),
                        (2,'青翼蝠王',1,'18812340002','大專(zhuān)'),
                        (3,'金毛獅王',1,'18812340003','初中'),
                        (4,'紫衫龍王',2,'18812340004','碩士');


create table tb_user_card(
    id int unsigned  primary key auto_increment comment 'ID',
    nationality varchar(10) not null comment '民族',
    birthday date not null comment '生日',
    idcard char(18) not null comment '身份證號(hào)',
    issued varchar(20) not null comment '簽發(fā)機(jī)關(guān)',
    expire_begin date not null comment '有效期限-開(kāi)始',
    expire_end date comment '有效期限-結(jié)束',
    user_id int unsigned not null unique comment '用戶(hù)ID',
    constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用戶(hù)信息表';

insert into tb_user_card values (1,'漢','1960-11-06','100000100000100001','朝陽(yáng)區(qū)公安局','2000-06-10',null,1),
        (2,'漢','1971-11-06','100000100000100002','靜安區(qū)公安局','2005-06-10','2025-06-10',2),
        (3,'漢','1963-11-06','100000100000100003','昌平區(qū)公安局','2006-06-10',null,3),
        (4,'回','1980-11-06','100000100000100004','海淀區(qū)公安局','2008-06-10','2028-06-10',4);

?? ??? ?多對(duì)多

?? ??? ??? ?案例：學(xué)生與課程的關(guān)系：

?? ??? ??? ?關(guān)系：一個(gè)學(xué)生可以選修多門(mén)課程，一門(mén)課程可以供多個(gè)學(xué)生選擇。
?? ??? ??? ?實(shí)現(xiàn)：建立第三張表，中間表至少包含兩個(gè)外鍵，分別關(guān)聯(lián)兩方的主鍵。

--  ======================================多對(duì)多=============================
create table tb_student(
    id int auto_increment primary key comment '主鍵ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '學(xué)號(hào)'
) comment '學(xué)生表';
insert into tb_student(name, no) values ('黛綺絲', '2000100101'),('謝遜', '2000100102'),('殷天正', '2000100103'),('韋一笑', '2000100104');


create table tb_course(
   id int auto_increment primary key comment '主鍵ID',
   name varchar(10) comment '課程名稱(chēng)'
) comment '課程表';
insert into tb_course (name) values ('Java'), ('PHP'), ('MySQL') , ('Hadoop');


create table tb_student_course(
   id int auto_increment comment '主鍵' primary key,
   student_id int not null comment '學(xué)生ID',
   course_id  int not null comment '課程ID',
   constraint fk_courseid foreign key (course_id) references tb_course (id),
   constraint fk_studentid foreign key (student_id) references tb_student (id)
)comment '學(xué)生課程中間表';

insert into tb_student_course(student_id, course_id) values (1,1),(1,2),(1,3),(2,2),(2,3),(3,4);

? ? ? ? 表關(guān)系設(shè)計(jì)案例：

?? ??? ??? ?根據(jù)下圖需求：設(shè)計(jì)一個(gè)員工的工作經(jīng)歷表：

?? ??? ??? ??? ?

分析的知，一個(gè)員工可以有多段的工作經(jīng)歷，所以員工和工作經(jīng)歷對(duì)應(yīng)的表關(guān)系是：一對(duì)多。

?create table emp_expr(
? ? id int unique primary key auto_increment comment 'ID',
? ? begin_date date comment '開(kāi)始時(shí)間',
? ? end_date date comment '結(jié)束時(shí)間',
? ? name varchar(50) comment '公司名稱(chēng)',
? ? job varchar(50) comment '職位',
? ? emp_id int unique comment '員工ID'   #這個(gè)字段綁定的是員工表的主鍵ID
)comment '工作經(jīng)歷表';

添加外鍵SQL語(yǔ)句：

#給員工的工作經(jīng)歷表添加一個(gè)外鍵，綁定的是 員工表的主鍵id
alter table emp_expr add constraint fk_emp_expr_emp_id foreign key(emp_id) references dept(id);

多表查詢(xún)：

準(zhǔn)備多表查詢(xún)的數(shù)據(jù)

-- 部門(mén)管理
create table dept(
    id int unsigned primary key auto_increment comment 'ID, 主鍵',
    name varchar(10) not null unique comment '部門(mén)名稱(chēng)',
    create_time datetime comment '創(chuàng)建時(shí)間',
    update_time datetime comment '修改時(shí)間'
) comment '部門(mén)表' ;

insert into dept (id, name, create_time, update_time) values
        (1,'學(xué)工部',now(),now()),
        (2,'教研部',now(),now()),
        (3,'咨詢(xún)部',now(),now()),
        (4,'就業(yè)部',now(),now()),
        (5,'人事部',now(),now());


-- 員工管理
create table emp(
    id int unsigned primary key auto_increment comment 'ID,主鍵',
    username varchar(20) not null unique comment '用戶(hù)名',
    password varchar(32) not null comment '密碼',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性別, 1:男, 2:女',
    phone char(11) not null unique comment '手機(jī)號(hào)',
    job tinyint unsigned comment '職位, 1:班主任,2:講師,3:學(xué)工主管,4:教研主管,5:咨詢(xún)師',
    salary int unsigned comment '薪資',
    image varchar(300) comment '頭像',
    entry_date date comment '入職日期',
    dept_id int unsigned COMMENT '關(guān)聯(lián)的部門(mén)ID',
    create_time datetime comment '創(chuàng)建時(shí)間',
    update_time datetime comment '修改時(shí)間'
) comment '員工表';


-- 準(zhǔn)備測(cè)試數(shù)據(jù)
INSERT INTO `emp` VALUES (1,'shinaian','123456','施耐庵',1,'13309090001',4,15000,'01.png','2000-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:35'),
			(2,'songjiang','123456','宋江',1,'13309090002',2,8600,'01.png','2015-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:37'),
			(3,'lujunyi','123456','盧俊義',1,'13309090003',2,8900,'01.png','2008-05-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:39'),
			(4,'wuyong','123456','吳用',1,'13309090004',2,9200,'01.png','2007-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:41'),
			(5,'gongsunsheng','123456','公孫勝',1,'13309090005',2,9500,'01.png','2012-12-05',2,'2023-10-27 16:35:33','2023-10-27 16:35:43'),
			(6,'huosanniang','123456','扈三娘',2,'13309090006',3,6500,'01.png','2013-09-05',1,'2023-10-27 16:35:33','2023-10-27 16:35:45'),
			(7,'chaijin','123456','柴進(jìn)',1,'13309090007',1,4700,'01.png','2005-08-01',1,'2023-10-27 16:35:33','2023-10-27 16:35:47'),
			(8,'likui','123456','李逵',1,'13309090008',1,4800,'01.png','2014-11-09',1,'2023-10-27 16:35:33','2023-10-27 16:35:49'),
			(9,'wusong','123456','武松',1,'13309090009',1,4900,'01.png','2011-03-11',1,'2023-10-27 16:35:33','2023-10-27 16:35:51'),
			(10,'lichong','123456','林沖',1,'13309090010',1,5000,'01.png','2013-09-05',1,'2023-10-27 16:35:33','2023-10-27 16:35:53'),
			(11,'huyanzhuo','123456','呼延灼',1,'13309090011',2,9700,'01.png','2007-02-01',2,'2023-10-27 16:35:33','2023-10-27 16:35:55'),
			(12,'xiaoliguang','123456','小李廣',1,'13309090012',2,10000,'01.png','2008-08-18',2,'2023-10-27 16:35:33','2023-10-27 16:35:57'),
			(13,'yangzhi','123456','楊志',1,'13309090013',1,5300,'01.png','2012-11-01',1,'2023-10-27 16:35:33','2023-10-27 16:35:59'),
			(14,'shijin','123456','史進(jìn)',1,'13309090014',2,10600,'01.png','2002-08-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:01'),
			(15,'sunerniang','123456','孫二娘',2,'13309090015',2,10900,'01.png','2011-05-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:03'),
			(16,'luzhishen','123456','魯智深',1,'13309090016',2,9600,'01.png','2010-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:05'),
			(17,'liying','12345678','李應(yīng)',1,'13309090017',1,5800,'01.png','2015-03-21',1,'2023-10-27 16:35:33','2023-10-27 16:36:07'),
			(18,'shiqian','123456','時(shí)遷',1,'13309090018',2,10200,'01.png','2015-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:09'),
			(19,'gudasao','123456','顧大嫂',2,'13309090019',2,10500,'01.png','2008-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:11'),
			(20,'ruanxiaoer','123456','阮小二',1,'13309090020',2,10800,'01.png','2018-01-01',2,'2023-10-27 16:35:33','2023-10-27 16:36:13'),
			(21,'ruanxiaowu','123456','阮小五',1,'13309090021',5,5200,'01.png','2015-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:15'),
			(22,'ruanxiaoqi','123456','阮小七',1,'13309090022',5,5500,'01.png','2016-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:17'),
			(23,'ruanji','123456','阮籍',1,'13309090023',5,5800,'01.png','2012-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:19'),
			(24,'tongwei','123456','童威',1,'13309090024',5,5000,'01.png','2006-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:21'),
			(25,'tongmeng','123456','童猛',1,'13309090025',5,4800,'01.png','2002-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:23'),
			(26,'yanshun','123456','燕順',1,'13309090026',5,5400,'01.png','2011-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:25'),
			(27,'lijun','123456','李俊',1,'13309090027',5,6600,'01.png','2004-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:27'),
			(28,'lizhong','123456','李忠',1,'13309090028',5,5000,'01.png','2007-01-01',3,'2023-10-27 16:35:33','2023-10-27 16:36:29'),
			(29,'songqing','123456','宋清',1,'13309090029',NULL,5100,'01.png','2020-01-01',NULL,'2023-10-27 16:35:33','2023-10-27 16:36:31'),
			(30,'liyun','123456','李云',1,'13309090030',NULL,NULL,'01.png','2020-03-01',NULL,'2023-10-27 16:35:33','2023-10-27 16:36:31');

? ? 多表查詢(xún)：就是從多張表中查詢(xún)數(shù)據(jù)

?? ?笛卡爾積：笛卡爾乘積：笛卡爾乘積是指在數(shù)學(xué)中，兩個(gè)集合（A集合與B集合）的所有組合情況。

?

內(nèi)連接：

? ? ? ? 相當(dāng)于查詢(xún) A，B交集部分的數(shù)據(jù)

? ? ? ? 上面的案例中，給員工表添加了一個(gè)外鍵，要是有的員工表中的dept_id 為null，那么就不會(huì)顯示這些員工的dept_id 為null 的數(shù)據(jù)。

語(yǔ)法：

內(nèi)連接查詢(xún)的是兩張表交集部分的數(shù)據(jù)。

1 隱式內(nèi)連接

? ? ? ? select 字段列表

????????

-- ============================= 內(nèi)連接 ==========================
-- A. 查詢(xún)所有員工的ID, 姓名 , 及所屬的部門(mén)名稱(chēng) (隱式、顯式內(nèi)連接實(shí)現(xiàn))

# 隱式
select emp.id,emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

#顯式
select emp.id,emp.name,dept.name from emp join dept on emp.dept_id = dept.id;

-- B. 查詢(xún) 性別為男, 且工資 高于8000 的員工的ID, 姓名, 及所屬的部門(mén)名稱(chēng) (隱式、顯式內(nèi)連接實(shí)現(xiàn))

#隱式
select emp.id,emp.name,dept.name from emp,dept where emp.dept_id = dept.id and emp.gender = 1 and emp.salary > 8000;

#顯式
select emp.id,emp.name,dept.name from emp join dept on emp.dept_id = dept.id where emp.gender = 1 and emp.salary > 8000;

# 給表起別名
    # 使用as關(guān)鍵字
select e.id,e.name,d.name from emp as e join dept as d on e.dept_id = d.id;

# as關(guān)鍵字可以省略
select e.id,e.name,d.name from emp e join dept d on e.dept_id = d.id;

外連接：

左外連接：查詢(xún)左表所有數(shù)據(jù)（包括兩張表交集部分?jǐn)?shù)據(jù)）

如下圖，紅圈選中的數(shù)據(jù)。

語(yǔ)法：

-- 左外連接（常用）

select 字段列表 from 表1 left [outer] join 表2 on 連接條件....?

右外連接：查詢(xún)右表所有數(shù)據(jù)（包括兩張表交集部分?jǐn)?shù)據(jù)）

如下圖紅圈選中的數(shù)據(jù)。

語(yǔ)法：

-- 右外連接：

? ? ? ? select 字段列表 from 表1 right [outer] join 表2 on 連接條件?

?

示例：

-- =============================== 外連接 ============================
-- A. 查詢(xún)員工表 所有 員工的姓名, 和對(duì)應(yīng)的部門(mén)名稱(chēng) (左外連接)

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

-- B. 查詢(xún)部門(mén)表 所有 部門(mén)的名稱(chēng), 和對(duì)應(yīng)的員工名稱(chēng) (右外連接)

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

# 可以改成左外連接：
    select e.name,d.name from dept d left join emp e on e.dept_id = d.id;
-- C. 查詢(xún)工資 高于8000 的 所有員工的姓名, 和對(duì)應(yīng)的部門(mén)名稱(chēng) (左外連接)

select e.name,d.name from emp e left join dept d on e.dept_id = d.id where e.salary > 8000;

溫馨提示：

對(duì)于外連接，常用的是左外連接，因?yàn)橛彝膺B接的SQL也可以改造成為左外連接（兩張表調(diào)換個(gè)順序）。

子查詢(xún)：

介紹：

SQL語(yǔ)句中嵌套select語(yǔ)句，稱(chēng)為嵌套查詢(xún)，又稱(chēng)子查詢(xún)。

形式：

select * from t1 where column1 = (select column1 from t2 …);

說(shuō)明：

子查詢(xún)外部的語(yǔ)句可以是insert / update / delete / select 的任何一個(gè)，最常見(jiàn)的是 select。

分類(lèi)：

標(biāo)量子查詢(xún)：

子查詢(xún)返回的結(jié)果是單個(gè)值

列子查詢(xún)：

子查詢(xún)返回的結(jié)果是一列，可以有多行。

?行子查詢(xún)：

子查詢(xún)返回的結(jié)果是一行，可以是多列。

?表子查詢(xún)：

子查詢(xún)返回的結(jié)果是多行多列。

?

?示例：

-- ========================= 子查詢(xún) ================================
# 在寫(xiě)子查詢(xún)SQL的時(shí)候，我們一般先拆分需求，最后合并成一條SQL語(yǔ)句即可
-- 標(biāo)量子查詢(xún)
-- A. 查詢(xún) 最早入職 的員工信息

    #先查詢(xún) 最早入職的時(shí)間
    select min(entry_date) from emp;
    # 根據(jù)上面的到的最早入職時(shí)間查詢(xún)員工的信息
    select * from emp where entry_date = '2000-01-01';
    # 合并SQL語(yǔ)句
    select * from emp where entry_date = ( select min(entry_date) from emp);

-- B. 查詢(xún)?cè)?"阮小五" 入職之后入職的員工信息

    # 先查詢(xún) ‘阮小五的入職時(shí)間’
    select entry_date from emp where name = '阮小五';   -- 2015-01-01
    # 根據(jù)阮小五的入職時(shí)間，查詢(xún)之后入職的員工
    select * from emp where entry_date > '2015-01-01';
    # 拼接SQL
    select * from emp where entry_date > (select entry_date from emp where name = '阮小五');

-- 列子查詢(xún)
-- A. 查詢(xún) "教研部" 和 "咨詢(xún)部" 的所有員工信息

    # 先查詢(xún)到教研部和咨詢(xún)部的部門(mén)id
    select id from dept where name in ('教研部','咨詢(xún)部'); -- 3,2
    #根據(jù)上面查到的部門(mén)id 查詢(xún)里面包含的員工信息
    select * from emp where dept_id in (3,2);
    #合并
    select * from emp where dept_id in (select id from dept where name in ('教研部','咨詢(xún)部'));




-- 行子查詢(xún)
-- A. 查詢(xún)與 "李忠" 的薪資 及 職位都相同的員工信息 ;

    # 先查詢(xún) 李忠的薪資和職位
    select salary,job from emp;
    # 在根據(jù)李忠的薪資和職位 查詢(xún)與此相同的員工信息
    select * from emp where (salary,job) in (5000,5);
    # 合并
    select * from emp where (salary,job) in (select salary,job from emp);


-- 表子查詢(xún)
-- A. 查詢(xún)?nèi)肼毴掌谑?"2006-01-01" 之后的員工信息 , 及其部門(mén)信息

    # 先查詢(xún)?nèi)肼毴掌谑恰?006-01-01’之后的員工信息
    select * from emp where entry_date > '2006-01-01';
    #根據(jù)上面的入職信息 查詢(xún)部門(mén)信息
    select e.*,d.name from ( select * from emp where entry_date > '2006-01-01') e,dept d where e.dept_id = d.id;

連接查詢(xún)和子查詢(xún)的案例：

-- 1. 查詢(xún) "教研部" 的 "男性" 員工，且在 "2011-05-01" 之后入職的員工信息 。

select * from emp e,dept d where e.dept_id = d.id and d.name = '教研部' and e.gender = 1  and e.entry_date > '2011-05--1';

-- 2. 查詢(xún)工資 低于公司平均工資的 且 性別為男 的員工信息 。

# 查詢(xún)公司的平均工資
select avg(salary) from emp;

select * from emp p where p.salary < (select avg(salary) from emp);

-- 3. 查詢(xún)工資 低于本部門(mén)平均工資的員工信息 。

select * from emp e where e.salary < (select avg(salary) from emp where dept_id = e.dept_id);

-- 4. 查詢(xún)部門(mén)人數(shù)超過(guò) 10 人的部門(mén)名稱(chēng) 。

select d.name,count(*) from dept d,emp e where d.id = e.dept_id group by d.name having count(*) > 10;

再來(lái)一波猛的練習(xí)：

提供查詢(xún)的數(shù)據(jù)表：文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-759747.html

-- 部門(mén)管理
create table tb_dept(
    id int unsigned primary key auto_increment comment '主鍵ID',
    name varchar(10) not null unique comment '部門(mén)名稱(chēng)',
    create_time datetime not null comment '創(chuàng)建時(shí)間',
    update_time datetime not null comment '修改時(shí)間'
) comment '部門(mén)表';

insert into tb_dept (id, name, create_time, update_time) 
        values(1,'學(xué)工部',now(),now()),
        (2,'教研部',now(),now()),
        (3,'咨詢(xún)部',now(),now()), 
        (4,'就業(yè)部',now(),now()),
        (5,'人事部',now(),now());



-- 員工管理
create table tb_emp (
    id int unsigned primary key auto_increment comment 'ID',
    username varchar(20) not null unique comment '用戶(hù)名',
    password varchar(32) default '123456' comment '密碼',
    name varchar(10) not null comment '姓名',
    gender tinyint unsigned not null comment '性別, 說(shuō)明: 1 男, 2 女',
    image varchar(300) comment '圖像',
    job tinyint unsigned comment '職位, 說(shuō)明: 1 班主任,2 講師, 3 學(xué)工主管, 4 教研主管, 5 咨詢(xún)師',
    entrydate date comment '入職時(shí)間',
    dept_id int unsigned comment '部門(mén)ID',
    salary int unsigned comment '工資',
    create_time datetime not null comment '創(chuàng)建時(shí)間',
    update_time datetime not null comment '修改時(shí)間'
) comment '員工表';

INSERT INTO tb_emp
    (id, username, password, name, gender, image, job, entrydate,dept_id,salary, create_time, update_time) VALUES
    (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,20000,now(),now()),
    (2,'zhangwuji','123456','張無(wú)忌',1,'2.jpg',2,'2015-01-01',2,18000,now(),now()),
    (3,'yangxiao','123456','楊逍',1,'3.jpg',2,'2008-05-01',2,16800,now(),now()),
    (4,'weiyixiao','123456','韋一笑',1,'4.jpg',2,'2007-01-01',2,12000,now(),now()),
    (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,9000,now(),now()),
    (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,6000,now(),now()),
    (7,'jixiaofu','123456','紀(jì)曉芙',2,'7.jpg',1,'2005-08-01',1,6500,now(),now()),
    (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,7200,now(),now()),
    (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,5300,now(),now()),
    (10,'zhaomin','123456','趙敏',2,'10.jpg',1,'2013-09-05',1,12000,now(),now()),
    (11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,8900,now(),now()),
    (12,'hebiweng','123456','鶴筆翁',1,'12.jpg',5,'2008-08-18',3,7800,now(),now()),
    (13,'fangdongbai','123456','方東白',1,'13.jpg',5,'2012-11-01',3,6800,now(),now()),
    (14,'zhangsanfeng','123456','張三豐',1,'14.jpg',2,'2002-08-01',2,15800,now(),now()),
    (15,'yulianzhou','123456','俞蓮舟',1,'15.jpg',2,'2011-05-01',2,11500,now(),now()),
    (16,'songyuanqiao','123456','宋遠(yuǎn)橋',1,'16.jpg',2,'2007-01-01',2,8300,now(),now()),
    (17,'chenyouliang','123456','陳友諒',1,'17.jpg',NULL,'2015-03-21',NULL,4500,now(),now());



-- 薪資等級(jí)表
create table tb_salgrade(
    grade int comment '等級(jí)',
    losal int comment '該等級(jí)最低薪資',
    hisal int comment '該等級(jí)最高薪資'
) comment '薪資等級(jí)表';

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

全部習(xí)題代碼：

-- 1. 查詢(xún)員工的姓名 , 及所屬的部門(mén)名稱(chēng) (隱式內(nèi)連接實(shí)現(xiàn))

select te.name 員工姓名,td.name 部門(mén)名稱(chēng) from tb_emp te,tb_dept td where te.dept_id = td.id;

-- 2. 查詢(xún)員工的姓名 , 及所屬的部門(mén)名稱(chēng) (顯式內(nèi)連接實(shí)現(xiàn))

select te.name 員工姓名,td.name 部門(mén)名稱(chēng) from tb_emp te join tb_dept td on te.dept_id = td.id;

-- 3. 查詢(xún)員工的 姓名、性別、職位、部門(mén)名稱(chēng) （隱式內(nèi)連接）

select te.name 姓名,te.gender 性別,te.job 職位,td.name 部門(mén)名稱(chēng) from tb_emp te,tb_dept td where te.dept_id = td.id;

-- 4. 查詢(xún) 薪資 高于 10000 的員工的姓名、性別、職位、部門(mén)名稱(chēng)（顯式內(nèi)連接）

select te.name 姓名,te.gender 性別,te.job 職位,td.name 部門(mén)名稱(chēng) from tb_emp te join tb_dept td on te.dept_id = td.id where te.salary > 10000;

-- 5. 查詢(xún)員工表 所有 員工的姓名, 和對(duì)應(yīng)的部門(mén)名稱(chēng) (左外連接)

select te.name 員工姓名,td.name 部門(mén)名稱(chēng) from tb_emp te left join tb_dept td on te.dept_id = td.id;

-- 6. 查詢(xún)員工表 所有 員工的姓名, 和對(duì)應(yīng)的部門(mén)名稱(chēng) (右外連接)

select te.name 員工姓名,td.name 部門(mén)名稱(chēng) from tb_dept td right join tb_emp te  on te.dept_id = td.id;

-- 7. 查詢(xún)部門(mén)表 所有 部門(mén)的名稱(chēng), 和對(duì)應(yīng)的員工名稱(chēng) (右外連接)

select td.name 部門(mén)名稱(chēng),te.name 員工姓名 from tb_emp te right join tb_dept td on te.dept_id = td.id;

-- 8. 查詢(xún) "教研部" 的所有員工信息 （標(biāo)量子查詢(xún)）

select * from tb_emp where dept_id = (select id from tb_dept where tb_dept.name = '教研部');

-- 9. 查詢(xún)?cè)?"方東白" 入職之后的員工信息 （標(biāo)量子查詢(xún)）

select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方東白');

-- 10. 查詢(xún) "教研部" 和 "咨詢(xún)部" 的所有員工信息 （列子查詢(xún)）

select * from tb_emp where dept_id in (select id from tb_dept where tb_dept.name in ('教研部','咨詢(xún)部'));

-- 11. 查詢(xún)與 "韋一笑" 的入職日期 及 職位都相同的員工信息 （行子查詢(xún)）

select * from tb_emp where (entrydate,job) in (select entrydate,job from tb_emp where name = '韋一笑');

-- 12. 查詢(xún)?nèi)肼毴掌谑?"2006-01-01" 之后的員工信息 , 及其部門(mén)信息 （表子查詢(xún)）

select * from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept td where e.dept_id = td.id;

-- 13. 查詢(xún) 擁有員工的 部門(mén)ID、部門(mén)名稱(chēng) (沒(méi)有員工的部門(mén)無(wú)需展示)

select te.dept_id 部門(mén)id,td.name 部門(mén)名稱(chēng) from tb_emp te,tb_dept td where te.dept_id = td.id;

-- 14. 查詢(xún)所有 在 2010-01-01 之后入職的員工, 及其歸屬的部門(mén)名稱(chēng); 如果員工沒(méi)有分配部門(mén), 也需要展示出來(lái)

select te.*,td.name 部門(mén)名稱(chēng) from tb_emp te left join tb_dept td on te.dept_id = td.id where te.entrydate > '2010-01-01';

-- 15. 查詢(xún) "教研部" 員工的平均工資

select avg(salary) from tb_emp where dept_id = (select id from tb_dept where tb_dept.name = '教研部');

-- 16. 查詢(xún)工資比 "俞蓮舟" 高的員工信息。

select * from tb_emp te where te.salary > (select salary from tb_emp where tb_emp.name = '俞蓮舟');

-- 17. 查詢(xún) 工資 比該企業(yè)員工的平均薪資 還要高的員工信息

select * from tb_emp te where te.salary > (select avg(salary) from tb_emp);

-- 18. 查詢(xún)所有的部門(mén)信息, 并統(tǒng)計(jì)部門(mén)的員工人數(shù)

select count(*) 員工數(shù)量,td.name 部門(mén)名稱(chēng) from tb_dept td,tb_emp te where te.dept_id = td.id group by td.name;
-- 19. 查詢(xún)所有員工的 姓名, 工資 , 及 工資等級(jí) (有難度的哦)
select te.name 姓名,te.salary 工資,ts.grade 工資等級(jí) from tb_emp te left join tb_salgrade ts on te.salary between ts.losal and ts.hisal;

select te.name 姓名,te.salary 工資,
       (case
           when te.salary <= 3000 then '1'
           when te.salary <= 5000 then '2'
           when te.salary <= 8000 then '3'
           when te.salary <= 10000 then '4'
           when te.salary <= 15000 then '5'
           when te.salary <= 20000 then '6'
           when te.salary <= 25000 then '7'
           when te.salary <= 30000 then '8'
           else '未知等級(jí)'
           end) 薪資等級(jí)
from tb_emp te;

-- 20. 查詢(xún) "教研部" 所有員工的信息 及 工資等級(jí) (有難度的哦)
select te.*,ts.grade from tb_emp te
            left join tb_dept td on te.dept_id = td.id
            left join tb_salgrade ts on te.salary between ts.losal and ts.hisal
            where td.name = '教研部';

select e.*,
     (case
           when e.salary <= 3000 then '1'
           when e.salary <= 5000 then '2'
           when e.salary <= 8000 then '3'
           when e.salary <= 10000 then '4'
           when e.salary <= 15000 then '5'
           when e.salary <= 20000 then '6'
           when e.salary <= 25000 then '7'
           when e.salary <= 30000 then '8'
           else '未知等級(jí)'
      end) 薪資等級(jí)
from (select * from tb_emp te where te.dept_id = (select id from tb_dept td where td.name = '教研部')) e ;

-- 21. 查詢(xún) 工資 低于 本部門(mén)平均工資的員工信息 (有難度的哦)

select * from tb_emp te where te.salary < (select avg(salary) from tb_emp where tb_emp.dept_id = te.dept_id);

-- 22. 列出所有部門(mén)的詳細(xì)信息(包括部門(mén)ID, 部門(mén)名稱(chēng))和人數(shù)

select td.id 部門(mén)id,td.name 部門(mén)名稱(chēng),count(*) 部門(mén)人數(shù) from tb_dept td left join tb_emp te on td.id = te.dept_id group by td.name;

-- 23、取得每個(gè)薪資等級(jí)有多少員工  (有難度的哦)
select te.name 姓名,te.salary 工資,ts.grade 工資等級(jí) ,count(ts.grade) 等級(jí)數(shù)量 from tb_emp te
    left join tb_salgrade ts on te.salary between ts.losal and ts.hisal group by ts.grade;

select
    count(if(salary > 0 and salary < 3000,1,null)) as '1等級(jí)薪資人數(shù)',
    count(if(salary > 3001 and salary < 5000,1,null)) as '2等級(jí)薪資人數(shù)',
    count(if(salary > 5001 and salary < 8000,1,null)) as '3等級(jí)薪資人數(shù)',
    count(if(salary > 8001 and salary < 10000,1,null)) as '4等級(jí)薪資人數(shù)',
    count(if(salary > 10001 and salary < 15000,1,null)) as '5等級(jí)薪資人數(shù)',
    count(if(salary > 15001 and salary < 20000,1,null)) as '6等級(jí)薪資人數(shù)',
    count(if(salary > 20001 and salary < 25000,1,null)) as '7等級(jí)薪資人數(shù)',
    count(if(salary > 25001 and salary < 30000,1,null)) as '8等級(jí)薪資人數(shù)'
from tb_emp te;


-- 24. 取得每個(gè)部門(mén)最高薪水的人員名稱(chēng)


select * from tb_emp where (dept_id,salary) in (select dept_id,max(salary) from tb_emp group by dept_id);

到了這里，關(guān)于MySQL數(shù)據(jù)庫(kù)從小白到入門(mén)（二）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！