mysql数据多表查询、主键、主外键

什么是外键？

1.主键Primary Key：是唯一标识一条记录，不能有重复的，不允许为空，用来保证数据完整性

2.外键： 是另一表的主键, 外键可以有重复的, 可以是空值，用来和其他表建立联系用的。所以说，如果谈到了外键，一定是至少涉及到两张表。

3.外键的作用：建立表之间的关系，保证数据库的完整性

---

案例

新建2张表：

部门表（dept）

create table department(
            id int primary key,
            name varchar(20) not null,
            description varchar(100)
);

添加数据

lNSERT INTO `department` VALUES ('1', 'Java开发部', 'Java软件开发');
INSERT INTO `department` VALUES ('2', 'C#开发部', 'C#软件开发');
INSERT INTO `department` VALUES ('3', 'PHP开发部', 'PHP软件开发');
INSERT INTO `department` VALUES ('4', 'Android开发部', '安卓手机软件开发');

---

员工表(emp)

create table employee(
            id int primary key,
            name varchar(10) not null,
            gender varchar(2) not null,
            salary float(10,2),
            age int(2),
            dept_id int
);

添加数据

INSERT INTO `employee` VALUES ('2', '赵云', '男', '2500', '23', '2');
INSERT INTO `employee` VALUES ('3', '刘备', '男', '5000', '35', '3');
INSERT INTO `employee` VALUES ('4', '诸葛亮', '男', '3500', '45', '4');
INSERT INTO `employee` VALUES ('5', '貂蝉', '女', '4000', '28', '1');
INSERT INTO `employee` VALUES ('6', '曹操', '男', '3000', '56', '1');

---

添加外键方式

第一种：利用sql

(员工表emp）添加外键，让它与主表（部门表dept）相关联。

ALTER TABLE employee ADD FOREIGN KEY(dept_id) REFERENCES department(id);

ALTER TABLE employee：在从表employee中进行操作；

ADD FOREIGN KEY(dept_id)：将从表的字段dept_id添加为外键；

REFERENCES department(id)：映射到主表department当中为id的字段。

第二种：利用设计表

检测外键是否添加成功：:利用数据逆向模型

选中表employee，单击红框部分的“设计表”按钮，界面如下：
RESTRICT(限制)：如果你想删除的那个主表，它的下面有对应从表的记录，此主表将无法删除。

CASCADE（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删掉。

删除外键：

利用navicat中可以通过图形界面的方式删除外键，

1.从表中获取外键名：

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(10) COLLATE utf8_swedish_ci NOT NULL,
  `gender` varchar(2) COLLATE utf8_swedish_ci NOT NULL,
  `salary` float(10,2) DEFAULT NULL,
  `age` int(2) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_dept_id` (`dept_id`),
  CONSTRAINT `FK_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

通过sql语句来删除：

alter table emp drop foreign key 外键名; 

---

内连接（重点）

使用多个 inner join 可实现多表之间的联表查询

1、内连接：只列出匹配的记录

语法：
SELECT … FROM join_table1
INNER JOIN join_table2
[ON join_condition]
WHERE where_definition

解释：只列出这些连接表中与连接条件相匹配的数据行。INNER可以不写，则默认为内连接。[ON join_condition]里面写的是连接的条件。

举例：

select e.name,d.name from employee e inner join department d on e.dept_id=d.id; 

---

inner join 内连接写法：

1.查询所有id>2的男同学的部门、姓名、薪资、部门描述、

SELECT
	d.id,d.description,d.`name`，e.salary
FROM
	department d
INNER JOIN employee e ON (d.id = e.dept_id)
WHERE
	d.id > '2'
AND e.gender = '男'

---

join 内连接写法：

2.查询姓名为曹操员工所在的部门id、部门描述、薪资

SELECT
	d.description,
	d.id,
	e.salary,

FROM
	employee e
JOIN department d ON (e.dept_id = d.id)
WHERE
	e.`name` = "曹操"

---

查询薪资在 1000——8000区间，员工的姓名、性别、部门、薪资、性别

SELECT
	d.id,d.description,d.`name`,e.gender
FROM
	department d
INNER JOIN employee e ON (d.id = e.id)
WHERE
	e.salary BETWEEN 1000
AND 8000

在写多表查询的时候，尽量的为每张表取别名，避免字段冲突，使用别名可以区别不同表中的字段

外连接分类：

左外连接(LEFT JOIN) – LEFT JOIN

左外连接：左表列出全部，右表只列出匹配的记录

右外连接(RIGHT JOIN) – RIGHT LOIN

右外连接：右表列出全部，左表只列出匹配的记录

语法：

SELECT … FROM join_table1
(LEFT | RIGHT ) JOIN join_table2
ON join_condition
WHERE where_definition