深入浅出MySQL(五)

多表查询

MySQL中的多表联查

MySQL中多表查询分为三种形式：

1. 笛卡尔积的形式
2. 内连接的形式
3. 外连接的形式

测试如下：

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET 'UTF8';
USE test;
-- 员工表
CREATE TABLE emp(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    age TINYINT UNSIGNED NOT NULL DEFAULT 18,
    sex ENUM('男','女','保密') NOT NULL DEFAULT '保密',
    addr VARCHAR(20) NOT NULL DEFAULT '北京',
    depId TINYINT UNSIGNED NOT NULL COMMENT '部门对应的编号'
)ENGINE = INNODB CHARSET = UTF8;

CREATE TABLE dep(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    depName VARCHAR(50) NOT NULL UNIQUE,
    depDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE = INNODB CHARSET = UTF8;

INSERT dep(depName,depDesc) VALUES('PHP部','研发PHP程序'),
('JAVA部','研发JAVA程序'),
('IOS部','研发IOS程序'),
('C++部','研发C++程序');

INSERT emp(username,age,depId) VALUES('kim',26,2),
('small',25,2), 
('lily',27,1),
('rose',30,4),
('jack',31,3),
('frank',26,3);

笛卡尔积形式的查询
测试如下：

-- 查询员工表的id username age 以及对应的部门名称 
-- 会产生笛卡尔积
SELECT emp.id,emp.username,emp.age,dep.depName FROM emp,dep;  

笛卡尔积形式的查询会查询出重复的记录，所以一般情况下不会使用。

内连接形式的查询
内连接形式的查询表达式如下：

SELECT 字段名称,... FROM tbl_table1
INNER JOIN tbl_name2
ON 连接条件

内连接形式的查询会查询两个表中符合连接条件的记录
测试如下：

-- 测试内连接的形式
-- 内连接查询的是数据的交集部分
SELECT e.id,e.username,e.age,d.depName 
FROM emp AS e
INNER JOIN dep AS d
ON e.depId = d.id;

左外连接形式的查询
左外连接形式的查询表达式如下：

SELECT 字段名称,... FROM tbl_name1
LEFT JOIN tbl_name2
ON 连接条件

左外连接形式的查询会先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以NULL代替，为了有更明显的效果，我们先向表中插入一条非法记录：

INSERT emp(username,age,depId) VALUES('test',1,10);

测试左外连接如下：

-- 测试左外连接
-- 以左表为基础进行查询
SELECT e.id,e.username,e.age,d.depName,d.depDesc 
FROM emp AS e
LEFT JOIN dep AS d
ON e.depId = d.id;

+----+----------+-----+---------+--------------+
| id | username | age | depName | depDesc      |
+----+----------+-----+---------+--------------+
|  3 | lily     |  27 | PHP部   | 研发PHP程序  |
|  1 | kim      |  26 | JAVA部  | 研发JAVA程序 |
|  2 | small    |  25 | JAVA部  | 研发JAVA程序 |
|  5 | jack     |  31 | IOS部   | 研发IOS程序  |
|  6 | frank    |  26 | IOS部   | 研发IOS程序  |
|  4 | rose     |  30 | C++部   | 研发C++程序  |
|  7 | test     |   1 | NULL    | NULL         |
+----+----------+-----+---------+--------------+

右外连接形式的查询
右外连接形式的查询表达式如下：

SELECT 字段名称,... FROM tbl_name1
RIGHT OUTER JOIN tbl_name2
ON 连接条件

右外连接会先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以NULL代替
同样的，为了更好地看懂右外连接查询，先向dep表中插入一条数据

INSERT dep(depName,depDesc) VALUES('C#部','研发C#程序');

测试如下：

SELECT e.id,e.username,e.age,d.depName,d.depDesc 
FROM emp AS e
RIGHT JOIN dep AS d
ON e.depId = d.id; 

+------+----------+------+---------+--------------+
| id   | username | age  | depName | depDesc      |
+------+----------+------+---------+--------------+
|    1 | kim      |   26 | JAVA部  | 研发JAVA程序 |
|    2 | small    |   25 | JAVA部  | 研发JAVA程序 |
|    3 | lily     |   27 | PHP部   | 研发PHP程序  |
|    4 | rose     |   30 | C++部   | 研发C++程序  |
|    5 | jack     |   31 | IOS部   | 研发IOS程序  |
|    6 | frank    |   26 | IOS部   | 研发IOS程序  |
| NULL | NULL     | NULL | C#部    | 研发C#程序   |
+------+----------+------+---------+--------------+

多表联查的操作

要想进行多表联查，最终要的是搞清楚表和表之间的关系。
如示例：

-- 创建省份表
CREATE TABLE provinces(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    proName VARCHAR(10) NOT NULL UNIQUE
);

INSERT provinces(proName) VALUES('北京'),
('上海'),
('深圳');


-- 创建管理员表
CREATE TABLE admin(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(50) NOT NULL DEFAULT '1@qq.com',
    proId TINYINT UNSIGNED NOT NULL
);

INSERT admin(username,proId) VALUES('kim',1);
INSERT admin(username,proId) VALUES('small',2);

-- 创建商品分类表
CREATE TABLE cate(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    cateName VARCHAR(50) NOT NULL UNIQUE,
    cateDesc VARCHAR(50) NOT NULL DEFAULT ''
);
INSERT cate(cateName) VALUES('母婴');
INSERT cate(cateName) VALUES('服装');
INSERT cate(cateName) VALUES('电子');

-- 创建商品表 
CREATE TABLE products(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    productName VARCHAR(50) NOT NULL UNIQUE,
    price FLOAT(8,2) NOT NULL DEFAULT 0,
    cateId TINYINT UNSIGNED NOT NULL,
    adminId TINYINT UNSIGNED NOT NULL
);
INSERT products(productName,price,cateId,adminId)
VALUES('iphone9',9888,3,1),
('adidas',388,2,2),
('nike',888,2,2),
('奶瓶',288,1,1);  

进行两个表以上的查询操作:

-- 三表联查
-- products id productName price
-- cate cateName
-- admin username email

SELECT p.id,p.productName,p.price,c.cateName,a.username,a.email
FROM products AS p
JOIN admin AS a
ON p.adminId = a.id
JOIN cate AS c
ON p.cateId = c.id
WHERE p.price < 1000
ORDER BY p.price DESC
LIMIT 0,2;

-- 查询结果
+----+-------------+--------+----------+----------+----------+
| id | productName | price  | cateName | username | email    |
+----+-------------+--------+----------+----------+----------+
|  3 | nike        | 888.00 | 服装     | small    | 1@qq.com |
|  2 | adidas      | 388.00 | 服装     | small    | 1@qq.com |
+----+-------------+--------+----------+----------+----------+

外键约束的使用

首先只有InnoDB存储引擎支持外键，外键可以保证数据的一致性和完整性。
创建外键有两种方式第一种是建表的时候可以指定外键，第二种为动态添加外键。
建表时指定外键
建表时指定外键的语句为：

[CONSTRAINT 外键名称] FOREINGN KEY(字段名称) REFERENCES 主表(字段名称)

子表的外键关联必须是父表的主键。子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同。如果外键字段没有创建索引，MySQL会自动帮我们添加索引.
测试如下：

-- 创建新闻分类表
CREATE TABLE news_cate(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    cateName VARCHAR(50) NOT NULL UNIQUE,
    cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE = INNODB CHARSET = UTF8;

-- 创建新闻表
CREATE TABLE news(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    content VARCHAR(100) NOT NULL,
    cateId TINYINT UNSIGNED NOT NULL,
    FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE = INNODB CHARSET = UTF8;

-- 建表时添加外键测试
-- 主表和从表
-- 先有新闻分类表再有新闻表
-- news_cate.id = news.cateId
-- 所以 新闻分类表为主表 新闻表为从表


-- 删除表的时候，先删除子表
DROP TABLE news;
DROP TABLE news_cate;

也可以再建表的时候用CONSTRAINT关键字指定外键的名称：

-- 添加外键名称
-- 创建新闻分类表
CREATE TABLE news_cate(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    cateName VARCHAR(50) NOT NULL UNIQUE,
    cateDesc VARCHAR(100) NOT NULL DEFAULT ''
)ENGINE = INNODB CHARSET = UTF8;

-- 创建新闻表
CREATE TABLE news(
    id INT UNSIGNED AUTO_INCREMENT KEY,
    title VARCHAR(100) NOT NULL UNIQUE,
    content VARCHAR(100) NOT NULL,
    cateId TINYINT UNSIGNED NOT NULL,
    CONSTRAINT cateId_fk_newsCate FOREIGN KEY(cateId) REFERENCES news_cate(id)
)ENGINE = INNODB CHARSET = UTF8;

-- 通过show create table news; 查看news表的详细信息可以看到 外键被指定成了我们命名的`cateId_fk_newsCate`
-- CONSTRAINT `cateId_fk_newsCate` FOREIGN KEY (`cateId`) REFERENCES `news_cate` (`id`)

动态添加删除外键
首先动态删除外键的操作为

ALTER TABLE tbl_name
DROP FOREIGN KEY fk_name

如程序示例,在指定了外键为cateId_fk_newsCate,可以对其进行删除操作

-- 删除外键
ALTER TABLE news
DROP FOREIGN KEY cateId_fk_newsCate;

删除之后，我们还可以对news表动态添加外键，也可以指定外键的名称
动态添加外键的操作为：

ALTER TABLE tbl_name
ADD[CONSTRAINT 外键名称] 
FOREIGN KEY(外键字段) REFERENCES 主表(主键字段)

如示例程序

-- 动态添加外键
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id);

-- 或者指定外键名称进行动态添加
ALTER TABLE news
ADD CONSTRAINT cateId_fk_newsCate
FOREIGN KEY(cateId) REFERENCES news_cate(id);

动态添加外键之前我们需要确保表中的记录一定是合法的，没有脏值，否则添加外键将不成功。
外键约束的参照操作

1. CASCADE
   从父表删除或更新，子表也跟着删除或者更新，级联操作
2. SET NULL
   从父表删除或者更新记录，并且设置子表的外键列为NULL。需要确保没有设置NOT NULL
3. NO ACTION | RESTRICT
   拒绝对父表做更新或者删除操作

拿CASCADE级联操作举例：

-- 指定级联操作，DELETE CASCADE UPDATE CASCADE
ALTER TABLE news
ADD FOREIGN KEY(cateId) REFERENCES news_cate(id)
ON DELETE CASCADE ON UPDATE CASCADE;

对news表添加了外键并且指定了更新操作和删除操作父表和子表为级联关系。
将如下数据插入到news表与news_cate表中

INSERT news_cate(cateName) VALUES('国内新闻'),
('国际新闻'),
('娱乐新闻'),
('体育新闻');

INSERT news(title,content,cateId) VALUES('a1','aaaa1',1),
('a2','aaaa2',1),
('a3','aaaa3',4),
('a4','aaaa4',2),
('a5','aaaa5',3);

执行更新语句：

UPDATE news_cate SET id = 11 WHERE id = 1;

执行这条sql语句成功，我们查看news表中，对应地更新了cateId

+----+-------+---------+--------+
| id | title | content | cateId |
+----+-------+---------+--------+
|  1 | a1    | aaaa1   |     11 |
|  2 | a2    | aaaa2   |     11 |
|  3 | a3    | aaaa3   |      4 |
|  4 | a4    | aaaa4   |      2 |
|  5 | a5    | aaaa5   |      3 |
+----+-------+---------+--------+