一.group by statement(语句)
1.group by通常和聚合函数(count,max,min,sum,avg)一起使用,显示一列或多列
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
eg.显示每一个国家的客户数量
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
eg.显示每一个国家的客户数量,并且按照由高到低排列
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
eg.显示每一个投递员的订单数量
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
二.having
1.
三.select into statement(语句)
1.把表格中的所有数据复制到一个新的表格中
SELECT *
INTO newtable [ IN externaldb ]FROM table1;
eg.创建数据备份
SELECT *
INTO CustomersBackup2013
FROM Customers;
eg.把一个表格复制到另一个数据库里
SELECT *
INTO CustomersBackup2013 IN 'Backup.mdb'
FROM Customers;
2.把表格中的部分列复制到一个新的表格中
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;
注:新复制来的表格中会默认用之前的列名称和类型,也可以用as子句来更换名称
eg.把一个表格中的部分列复制到另一个新的表格里
SELECT CustomerName, ContactName
INTO CustomersBackup2013
FROM Customers;
3.select...into...where
eg.把customers表格中的country列中的Germany的数据复制到另一个新的表格里
SELECT *
INTO CustomersBackup2013
FROM Customers
WHERE Country='Germany';
4.把多个表格中的数据复制到另一个新的表格里
SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2013
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;
5.创建一个新的空的表格
SELECT *
INTO newtable
FROM table1
WHERE 1=0;
四.insert into select statement(语句)
1.把一个表格中的所有数据全部复制进另一个已经存在的表格里
INSERT INTO table2
SELECT * FROM table1;
2.复制一个表格中的部分列到另一个已经存在的表格里
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
eg.把suppliers表格中的suppliername列和country列,复制进customers表格中
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;
eg.把suppliers表格中的suppliername列和country列,且country列中是germany的数据,复制进customers表格中
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';
五.comments
1.