学习MySQL之多表操作（三）
时间：2021-07-01 10:21:17
帮助过：2人阅读
USE mytest;
##删除，并重新创建表 t_dept
DROP TABLE t_dept;
CREATE TABLE t_dept (
deptno INT(
11)
NOT NULL,
dname VARCHAR(
20)
NOT NULL,
loc VARCHAR(
40),
UNIQUE INDEX uk_deptno(deptno)
)
##创建测试表 t_a
CREATE TABLE t_A (
aId INT (
11) AUTO_INCREMENT, ##主键，自增
aName VARCHAR (
20)
NOT NULL,
loc VARCHAR (
40),
cId INT(
11),
PRIMARY KEY pk_aId (aId)
) ;
##创建测试表 t_b
CREATE TABLE t_B (
bId INT (
11) AUTO_INCREMENT, ##主键，自增
bStand VARCHAR (
40)
NOT NULL,
aId INT (
11)
NOT NULL,
PRIMARY KEY pk_bId (bId),
UNIQUE INDEX uk_aId (aId) ##唯一索引
) ;
##创建测试表 t_c
CREATE TABLE t_c (
cId INT (
11) AUTO_INCREMENT, ##主键，自增
cvalue VARCHAR (
20)
NOT NULL,
PRIMARY KEY pk_cId (cId)
);
##创建测试表 t_d
CREATE TABLE t_d(
xname VARCHAR(
11),
xsex VARCHAR(
11)
);
##创建测试表 t_e
CREATE TABLE t_e(
xname VARCHAR(
11),
xsex VARCHAR(
11)
);
##删除唯一索引
ALTER TABLE t_b
DROP KEY uk_aId;
##插入测试数据
INSERT INTO t_dept(deptno,dname,loc)
VALUES
(10,
‘生产‘,
‘生产部‘),
(20,
‘业务‘,
‘业务部‘),
(30,
‘品质‘,
‘品质部‘),
(40,
‘人事‘,
‘人事部‘),
(50,
‘工程‘,
‘工程部‘);
INSERT INTO t_a (aName, loc,cId)
VALUES
(‘Jay‘,
‘Jay Chou‘,
1),
(‘Join‘,
‘Join Chang‘,
3),
(‘Hebe‘,
‘Hebe Young‘,
2),
(‘Jack‘,
‘Jack Son‘,
2),
(‘Smile‘,
‘Smile Kiss‘,
1) ;
INSERT INTO t_b (bStand, aId)
VALUES
(‘Jay-Jay Chou‘,
1),
(‘Hebe-Hebe Young‘,
3),
(‘Jack-Jack Son‘,
4),
(‘Smile-Smile Kiss‘,
5) ;
INSERT INTO t_c (cvalue)
VALUES (
‘鸡‘),(
‘牛‘),(
‘马‘);
INSERT INTO t_d (xname,xsex)
VALUES (
‘章子怡‘,
‘女‘),(
‘张筱雨‘,
‘女‘),(
‘张雨涵‘,
‘男‘),(
‘张予曦‘,
‘女‘),(
‘张曦予‘,
‘女‘);
INSERT INTO t_e (xname,xsex)
VALUES (
‘张曦予‘,
‘女‘),(
‘汪峰‘,
‘男‘),(
‘张馨予‘,
‘女‘);
-- ----------- 内联、外联不推荐使用，根据笛卡尔积，执行效率低于子查询 ----------- --
##内联 □■□
##INNER JOIN ……
ON。显示主副表数据交集
SELECT a.aid,a.aName,a.loc,b.bStand,c.cvalue
FROM t_a
AS a
INNER JOIN t_b
AS b
ON a.aId
= b.aId
INNER JOIN t_c
AS c
ON a.cId
=c.cId;
##外联 ■■□ OR □■■
##外联显示的数据以主表为准，不管副表是否有对应数据都会显示，副表无数据的，显示null。■■□
##内联，副表无对应数据，则不会显示出。简而言之，显示数据为主副表的 交集。□■□
##左联：以本表为主表，左联表为副表。■■□
SELECT a.aid,a.aName,a.loc,b.bStand
FROM t_a
AS a
LEFT JOIN t_b
AS b
ON a.aid
=b.aid;
##右联：以本表为副表，右联表为主表。□■■
SELECT a.aid,a.aName,a.loc,b.bStand
FROM t_a
AS a
RIGHT JOIN t_b
AS b
ON a.aid
=b.aid;
##合并查询：
SELECT * FROM t_d
UNION SELECT * FROM t_e; ##
UNION，去掉两表的重复数据。 ■■■
SELECT * FROM t_d
UNION ALL SELECT * FROM t_e; ##
UNION ALL, 未去掉两表的重复数据。 ■■■■
-- ----------- 子查询 推荐使用，根据笛卡尔积，执行效率高 ----------- --
SELECT COUNT(
*)
FROM t_a
AS a,t_b
AS b; ##笛卡尔积
SELECT * FROM t_a
WHERE cId
=(
SELECT cId
FROM t_a
WHERE aName
= ‘Jay‘); ##结果集可以是多行
SELECT * FROM t_a
WHERE (aId,loc)
= (
SELECT aId,loc
FROM t_a
WHERE cId
=3); ##结果集只能是单行
SELECT * FROM t_employee
WHERE deptno
IN (
SELECT deptno
FROM t_dept);
SELECT * FROM t_employee
WHERE deptno
NOT IN (
SELECT deptno
FROM t_dept);
##使用ANY。在使用上，用 <= 和
>= 更有意义 ■■□
## <ANY (
<=ANY) ，结果集是比最小值(包含)大的结果。
## >ANY (
>=ANY) ，结果集是比最大值(包含)小的结果。
## =ANY，与使用
IN 一样。
SELECT sal
FROM t_employee
WHERE job
=‘SALESMAN‘;
SELECT * FROM t_employee
WHERE sal
>=ANY(
SELECT sal
FROM t_employee
WHERE job
=‘SALESMAN‘);
##使用ALL。在使用上，用 < 和
> 更有意义 ■□□
## <ALL (
<=ALL) ，结果集是比最大值(包含)更大的结果。
## >ALL (
>=ALL) ，结果集是比最小值(包含)更小的结果。
## =ANY，与使用
IN 一样。
SELECT sal
FROM t_employee
WHERE job
=‘SALESMAN‘;
SELECT * FROM t_employee
WHERE sal
>ALL(
SELECT sal
FROM t_employee
WHERE job
=‘SALESMAN‘);
##使用 EXISTS 与IN 的使用效果类似，以下两条SQL语句执行结果一致。
SELECT *
FROM t_dept
AS d
WHERE EXISTS(
SELECT * FROM t_employee
WHERE deptno
=d.deptno);
SELECT * FROM t_dept
AS d
WHERE deptno
IN(
SELECT deptno
FROM t_employee);
-- 综合测试 --
##计算笛卡尔积
SELECT COUNT(empno)
FROM t_employee;
SELECT COUNT(deptno)
FROM t_dept;
##使用内联，无字段为NULL，但效率低(笛卡尔积为 9X5=45)
SELECT d.deptno,d.dname,d.loc,
COUNT(e.empno)
AS number,
AVG(e.sal)
AS average
FROM t_dept
AS d
INNER JOIN t_employee
AS e
ON d.deptno
= e.deptno
GROUP BY d.deptno;
##使用左联，有字段为NULL,且效率低(笛卡尔积为 9X5=45)
SELECT d.deptno,d.dname,d.loc,
COUNT(e.empno)
AS number,
AVG(e.sal)
AS average
FROM t_dept
AS d
LEFT JOIN t_employee
AS e
ON d.deptno
=e.deptno
GROUP BY d.deptno;
##使用右联，虽无字段为NULL，但无法保证其他情况没NULL，且效率低(笛卡尔积为 9X5=45)
SELECT d.deptno,d.dname,d.loc,
COUNT(e.empno)
AS number,
AVG(e.sal)
AS average
FROM t_employee
AS e
LEFT JOIN t_dept
AS d
ON d.deptno
=e.deptno
GROUP BY d.deptno;
##使用子查询。最好为每个表派生别名，并指出每个字段是哪张表的。(防止字段之间重名)
##效率高，笛卡尔积为(4+9=13)
SELECT d.deptno,d.dname,d.loc,e.
number,e.average
FROM t_dept
AS d,
(SELECT deptno,
COUNT(empno)
AS number,
AVG(sal)
AS average
FROM t_employee
GROUP BY deptno)
AS e
WHERE d.deptno
=e.deptno;
所有代码，均为自学时用到的测试与注释，知识细节或知识点不会面面俱到，亦不会有任何讲解，只做为自己学习复习用。
学习MySQL之多表操作（三）
标签：
