SQL MySQL SQL Server 合并查询(union) 复合主键 唯一约束(UNIQUE) 外键 CHECK约束

合并查询(union) UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SELECT ename, sal, job from emp where sal > 2500;
/*
ename	sal	job
SCOTT	3000.00	ANALYST
KING	5000.00	PRESIDENT
FORD	3000.00	ANALYST
JONES	2975.00	MANAGER
BLAKE	2850.00	MANAGER
*/

SELECT ename, sal, job from emp where job = 'MANAGER';
/*
ename	sal	job
JONES	2975.00	MANAGER
BLAKE	2850.00	MANAGER
*/

-- union all 将两个查询结果合并, 不会去重
SELECT ename, sal, job from emp where sal > 2500 
UNION ALL
SELECT ename, sal, job from emp where job = 'MANAGER';
/*
ename	sal	job
SCOTT	3000.00	ANALYST
KING	5000.00	PRESIDENT
FORD	3000.00	ANALYST
JONES	2975.00	MANAGER
BLAKE	2850.00	MANAGER
JONES	2975.00	MANAGER
BLAKE	2850.00	MANAGER
*/

-- union  将两个查询结果合并, 会去重
SELECT ename, sal, job from emp where sal > 2500 
UNION 
SELECT ename, sal, job from emp where job = 'MANAGER';
/*
ename	sal	job
BLAKE	2850.00	MANAGER
FORD	3000.00	ANALYST
JONES	2975.00	MANAGER
KING	5000.00	PRESIDENT
SCOTT	3000.00	ANALYST
*/

复合主键

---------------------------复合主键---------------------------
-- 普通演示
CREATE TABLE t17 (
		id INT PRIMARY KEY, 
		name VARCHAR(32),
		email VARCHAR(32)
);

INSERT INTO t17 VALUES(1, 'jack', 'jack@ll.com');
INSERT INTO t17 VALUES(2, 'tom', 'tom@ll.com');
-- 错误,主键2不能重复
INSERT INTO t17 VALUES(2, 'tom',