SQL语法——细节问题（UNION、INTERSECT、MINUS、INSERT、UPDATE JOIN、ON DELETE CASCADE、DELETE JOIN）

MySQL UNION和ORDER BY

如果要对union的结果进行排序，请ORDER BY在最后一个SELECT语句中使用一个子句， 如以下示例所示：

SELECT 
    concat(firstName,' ',lastName) fullname
FROM
    employees 
UNION SELECT 
    concat(contactFirstName,' ',contactLastName)
FROM
    customers
ORDER BY fullname;

注意：如果将ORDER BY子句放在每个SELECT语句中，则不会影响最终结果集中行的顺序。

模拟INTERSECT运算符

CREATE TABLE t1 (
    id INT PRIMARY KEY
);
 
CREATE TABLE t2 LIKE t1;
 
INSERT INTO t1(id) VALUES(1),(2),(3);
 
INSERT INTO t2(id) VALUES(2),(3),(4); 

使用DISTINCT运算符和INNER JOIN子句模拟MySQL INTERSECT运算符:

SELECT DISTINCT 
   id 
FROM t1
   INNER JOIN t2 USING(id); 

使用IN运算符和子查询模拟MySQL INTERSECT运算符:

SELECT DISTINCT
    id
FROM
    t1
WHERE
    id IN (SELECT 
            id
        FROM
            t2); 

模拟MINUS运算符

使用join模拟MINUS：

SELECT 
    id
FROM
    t1
        LEFT JOIN
    t2 USING (id)
WHERE
    t2.id IS NULL; 

MySQL INSERT IGNORE

使用INSERT语句向表中添加多行时，如果在处理过程中发生错误，MySQL将终止语句并返回错误。

但是，如果使用INSERT IGNORE语句，将忽略导致错误的包含无效数据的行，并将具有有效数据的行插入到表中。

INSERT IGNORE语句的语法如下：

INSERT IGNORE INTO table(column_list)
VALUES( value_list),
      ( value_list),
      ... 

MySQL UPDATE JOIN语法

MySQL的语法UPDATE JOIN 如下：

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition 

-- 或

UPDATE T1, T2
SET T1.c2 = T2.c2,
      T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition

1. 首先，在子句后面指定主表（T1）和主表要加入的表（T2）UPDATE。请注意，您必须在UPDATE 子句后指定至少一个表。表中未在UPDATE 子句后指定的数据将不会更新。
2. 接下来，指定一种加入你想用即无论是INNER JOIN 或LEFT JOIN 和联接谓词。JOIN必须在UPDATE之后出现。
3. 然后，将新值分配给要更新的T1和/或T2表中的列。
   之后，在WHERE子句中指定条件以将行限制为行以进行更新。

示例：

UPDATE employees
INNER JOIN merits ON employees.performance = merits.performance 
SET salary = salary + salary * percentage; 

UPDATE employees
LEFT JOIN merits ON employees.performance = merits.performance 
SET salary = salary + salary * 0.015
WHERE merits.percentage IS NULL; 

MySQL ON DELETE CASCADE

假设我们有两个表：buildings和rooms。在此数据库模型中，每个建筑物都有一个或多个房间。但是，每个房间只属于一栋建筑。没有建筑物就不会有房间。

buildings 和rooms表之间的关系是一对多（1：N），如下面的数据库图所示：

CREATE TABLE buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
); 

CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
); 

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
      ('ACME Sales','5000 North 1st Street CA 95134'); 
			
INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Showroom',2); 

级联删除2号建筑物及其房间：

DELETE FROM buildings 
WHERE
    building_no = 2; 

MySQL DELETE JOIN与INNER JOIN

MySQL还允许使用INNER JOIN语句中的子句DELETE删除表中的行和另一个表中的匹配行。

例如，要删除两行T1，并T2满足指定条件表，使用下面的语句：

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition; 

示例：

DROP TABLE IF EXISTS t1, t2;
 
CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT
);
 
CREATE TABLE t2 (
    id VARCHAR(20) PRIMARY KEY,
    ref INT NOT NULL
);
 
INSERT INTO t1 VALUES (1),(2),(3);
 
INSERT INTO t2(id,ref) VALUES('A',1),('B',2),('C',3);

以下语句删除t1表中id为1的行，并使用DELETE…INNER JOIN语句删除在t2表中ref = 1的行 ：

DELETE t1,t2 FROM t1
        INNER JOIN
    t2 ON t2.ref = t1.id 
WHERE
    t1.id = 1; 

我们经常使用SELECT语句中的 LEFT JOIN子句来查找左表中的行，这些行在右表中有或没有匹配的行。

我们还可以使用DELETE语句中的LEFT JOIN子句删除表（左表）中的行，这些行在另一个表（右表）中没有匹配的行。

以下语法说明如何使用DELETE语句中 LEFT JOIN子句从T1表中删除T2表中没有相应行的行：

DELETE T1 
FROM T1
        LEFT JOIN
    T2 ON T1.key = T2.key 
WHERE
    T2.key IS NULL;