基础篇-使用视图 Using Views

Creating Views 创建视图

CREATE VIEW bigCities AS
SELECT
country.Name,
city.Name AS cityName,
city.Population
FROM country
INNER JOIN city ON
country.Code = city.CountryCode AND
city.Population > 5000000;

Deleting Views 删除视图

DROP VIEW bigCities

Calculated Fields in The View 视图中的计算字段

CREATE VIEW countryPersonArea AS
SELECT
Name,
SurfaceArea/Population AS AreaPerPerson
FROM country
ORDER BY 2 DESC;

SELECT * 
FROM countryPersonArea
WHERE AreaPerPerson > 0.5

Filter Data in the View 过滤视图中的数据

CREATE VIEW bigCountries AS
SELECT
Name,
SurfaceArea
FROM country
WHERE SurfaceArea > 5000000
ORDER BY SurfaceArea DESC;


SELECT * 
FROM bigCountries

ReFormart Retrieved Data 重新格式化检索到的数据

# for MySQL
CREATE VIEW countryNames AS
SELECT
Name,
CONCAT(Name, ' (', RTRIM(LocalName), ')') AS FullName
FROM country;

SELECT FullName
FROM countryNames
WHERE Name = 'Russian Federation'