MySQL基础知识四——表连接
目录
- SQL项目
- SQL表连接
第一部分 SQL项目
项目五:组合两张表,在数据库中创建表1和表2,并各插入三行数据。要求:编写一个SQL查询,满足条件:无论person是否有地址信息,都要基于上述两表提供person的以下信息:FirstName,LastName,City,State
解题过程:
USE test1;
CREATE TABLE Person_1 (
Personld INT NOT NULL PRIMARY KEY,
FirstName VARCHAR (255),
LastName VARCHAR (255)
);
INSERT INTO Person_1
VALUES
(1, 'owfjij', 'uhvgnu');
INSERT INTO Person_1
VALUES
(2, 'jdfnvi', 'ijvioj');
INSERT INTO Person_1
VALUES
(3, 'sigi', 'isojvi');
SELECT
*
FROM
Person_1;
CREATE TABLE Address (
Addressld INT NOT NULL,
Personld INT NOT NULL,
City VARCHAR (255),
State VARCHAR (255)
);
INSERT INTO Address
VALUES
(12, 1, 'JNVJ', 'JNVJK');
INSERT INTO Address
VALUES
(45, 2, 'IFIDSFV', 'KHDFIH');
INSERT INTO Address
VALUES
(46, 3, 'SOJFDI', 'SIJFI');
SELECT
*
FROM
Address;
SELECT FirstName,LastName,City,State FROM Person_1 a JOIN Address b ON a.Personld=b.Personld;
结果如下:
项目六:删除重复的邮箱,编写一个SQL查询,来删除email表中所有重复的电子邮件,重复的邮件只保留ld最小的那个。
解题过程:
USE test1;
CREATE TABLE Emailinfo (
ID INT NOT NULL PRIMARY KEY,
Email VARCHAR (255) NOT NULL
);
INSERT INTO Emailinfo
VALUES
('1', '[email protected]');
INSERT INTO Emailinfo
VALUES
('2', '[email protected]');
INSERT INTO Emailinfo
VALUES
('3', '[email protected]');
DELETE P1
FROM
Emailinfo P1
JOIN Emailinfo P2 ON P1.Email = P2.Email
WHERE
P1.ID > P2.ID;
SELECT
*
FROM
Emailinfo;
结果如下:
第二部分 SQL表连接
知识点
- MySQL别名
- INNER JOIN
- LEFT JOIN
- CROSS JOIN
- 自连接
- UNION