一.aliases(别名)
1.对列进行重命名
SELECT column_name AS alias_name
FROM table_name;
eg.把customername列更名为customer,contactname列更名为contact person
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
注:如果列的名称中含有空格,则可用双引号或方括号注明
eg.把四列合并为一列,并重新命名为address
SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address
FROM Customers;
运行结果为
2.对表格进行重命名
SELECT column_name(s)
FROM table_name AS alias_name;
eg.把customers表格命名为c,orders表格命名为o,然后显示customerID为4号的客户的订单
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
如果不使用表格的重命名,则代码会很冗长,如下所示
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;
3.使用别名的情况
查询过程中需要使用多个表
查询中使用到功能
列的名称很长或是不容易读懂
两个或多个列需要合并
二.join clause(子句)
1.join用来整合两个或多个表格的行数据
inner join:返回的所有行的时候有两个表中至少有一个匹配
left join:左表中返回所有行和右表匹配的行
right join:右表中返回所有行和左表的匹配的行
full join:返回所有行的时候没有在表中的一个匹配
2.inner join keyword(关键字)
显示两个或多个表格中,对指定的列都有匹配的行数据
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
或
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
eg.因为orders表和customers表的customerID是相同的,所以结合两个表,显示出三个列的所有数据orderID,customername和orderdate
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
eg.因为orders表和customers表的customerID是相同的,所以结合两个表,显示出两个列的所有数据customername和orderID,并且按照customername的字母顺序排列显示
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
3.left join keyword(关键字)
左边表格返回所有行,右边表格返回有匹配项的行,左边表格中数据没有对应于右边表格的,就显示null
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
或
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
eg.显示customers表格的所有行,如果有和orders表格中customerID一样的数据,就把对应的orderID显示,如果没有和orders表格中customerID一样的数据,orderID就显示null
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
4.right join keyword(关键字)
左边表格返回所有行,右边表格返回有匹配项的行,左边表格中数据没有对应于右边表格的,就显示null
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
或
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
eg.显示employees表格的所有行,如果有和orders表格中employeeID一样的数据,就把对应的orderID显示,如果没有和orders表格中employeeID一样的数据,orderID就显示null
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;
5.full outer join keyword(关键字)
显示所有表格的所有行,如果没有对应的值,就显示null
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
eg.显示customers表和orders表的所有行,潜在的连接规则是customerID相同,如果没有orderID或customername,就显示null
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
6.self join keyword(关键字)
三.union operator(操作符)
1.union
用来把两个或多个被选中的列全部合并显示出来,要合并的数据必须是同种类型的
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
注:默认情况下,自动去除重复的行
eg.把customers和suppliers两个表格中的city数据,全部显示出来,自动删除重复的city数据
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
2.union all
显示所有数据,不删除重复的行,一般显示的列名称会默认是第一个表格的列名称
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
eg.把customers和suppliers两个表格中的city数据,全部显示出来,即使有重复的数据也显示
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
3.union all ... where
eg.把customers和suppliers两个表格中的city和country两列的数据中country在germany的数据全部显示出来,并按city顺序排列
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;