学习日记6（数据库4-多表查询）

join

person表

person表.png

orders表

orders表.png
谁订购了产品，并且他们订购了什么产品？

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 

内链接

列出所有人的定购

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

union

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

插入新表

SELECT *
INTO Persons_backup
FROM Persons

创建视图

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No