Introduction of SQL：Organize data into one or more tables, and each table has columns and rows. A unique key identifies each rows
Column: vertical row: horizontal
Primary key: a field in a table which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields.
Surrogate key: A surrogate key is an artificially produced value, most often a system-managed, incrementing counter whose values can range from 1 to n, where n represents a table's maximum number of rows
Natural key: A natural key is a naturally occurring descriptor of the data and one of a table's attributes that has no duplicate values. When you use a natural key as a table's primary key, each of the table's rows is uniquely identified.
Sometimes, one can use combination of several attributes to form as the primary key: Such a key is called a concatenated primary key
Foreign key: A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
Some principles of the relational database model: 1.entity integrity 2.referential integrity 3.one fact one place
Syntax SQL的语法
# sql不区分大小写-select is the same as SELECT 以分号结束查询语句
SELECT syntax - extracts data from a database
例： select colomn 1, column 2,… from table name
If you want to select all the fields available in the table, use select* from table name.
Select distinct clause
The SELECT DISTINCT statement is used to return only distinct (different) values去掉重复值
Select distinct syntax: select distinct column 1, column 2, from table name;
例：Select all the different values from the Country column in the Customers table.
Select distinct country from customers;
例：The following SQL statement lists the number of different (distinct) customer countries:
Select count( distinct country) from customers;
Where Clause
The WHERE clause is used to extract only those records that fulfill a specified condition:SELECT column1, column2, ...FROM table_name WHERE condition;
举例：select*from customers where country='Mexico';
文本值周围加上单引号（大多数数据库系统也允许使用双引号）。 但是, 数字字段不应用引号引起来
WHERE clause + AND, OR, and NOT operators
The AND operator displays a record if all the conditions separated by AND are TRUE.
The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE
And Syntax
SELECT column1, column2, … FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
Or Syntax
SELECT column1, column2, … FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
Not Syntax
SELECT column1, column2, ...FROM table_name WHERE NOT condition;
例：The following SQL statement selects all fields from "Customers" where country is NOT "Germany" and NOT "USA"
Select*from customers where not country='Germany' and not country= 'USA';
ORDER BY Syntax
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword（自动按升序）
升序：select*from customers order by country;
降序：select*from customers order by country desc;
例：selects all customers from the "Customers" table, sorted by the "Country" and the "CustomerName" column. （This means that it orders by Country, but if some rows have the same Country, it orders them by CustomerName）
select*from customers order by country, customername;
例：selects all customers from the "Customers" table, sorted ascending by the "Country" and descending by the "CustomerName" column:
select*from customers order by country ASC, customername DESC;
例：Query a list of CITY names from STATION with even ID numbers only. You may print the results in any order, but must exclude duplicates from your answer.
select distinct city from station where(id%2)=0;
Insert into syntax（insert new records in a table）
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);——给some column 插入新数据
例：INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway');
如果是给所有column添加数据，则 insert into table name values(value1,value2,...) 注意values的顺序和table中的排列顺序一样
例：Insert into customers values ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
NULL Values
A field with a NULL value is a field with no value 经常用来检验null value的存在
A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation
Is null syntax: SELECT column_names FROM table_name WHERE column_name IS NULL;
举例：SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL;
Is not null syntax: SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
举例：SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;
Select customername, contactname, address from customers where address is not null
Update statement
modify the existing records in a table
UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;
The WHERE clause specifies which record(s) that should be updated. If you omit the WHERE clause, all records in the table will be updated.
(1) The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and a new city:
UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;
(2) The following SQL statement will update the contactname to "Juan" for all records where country is "Mexico":
UPDATE Customers SET ContactName='Juan'WHERE Country='Mexico';
在执行update命令时，一定不要忘记 where clause， 要不然会update整个table
Delete statement
delete existing records in a table
DELETE FROM table_name WHERE condition;
The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all records in the table will be deleted!
(1) The following SQL statement deletes the customer "Alfreds Futterkiste" from the "Customers" table:
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste'; 是把整个row删掉了
Delete all records:
It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact: DELETE FROM table_name(customers);
The SELECT TOP clause
specify the number of records to return 指定要返回的记录数，数据量很大时用得多
SQL Server / MS Access Syntax:
SELECT TOP number/percent column_name(s) FROM table_name WHERE condition;
举例：select top 3 * from customers;
MySQL Syntax:
SELECT column_name(s)FROM table_name WHERE condition LIMIT number;
select*from Customers limit 3;
Oracle Syntax:
SELECT column_name(s) FROM table_name WHERE ROWNUM（行数） <= number;
SELECT * FROM Customers WHERE ROWNUM <= 3;
SQL TOP PERCENT Example
The following SQL statement selects the first 50% of the records from the "Customers" table:
select top 50 percent *from customers;
Adding where clause
SELECT TOP 3 * FROM Customers WHERE Country='Germany';
SELECT*from Customers where country='Germany' limit 3;
SELECT * FROM Customers WHERE Country='Germany' AND ROWNUM <= 3;
SQL MIN() and MAX() Functions
Min syntax
SELECT MIN(column_name) FROM table_name WHERE condition;
举例：从products table中找到最低的价格
Select Min(price) as smallestprice from products;
Max syntax
SELECT MAX(column_name) FROM table_name WHERE condition;
举例：从products table中找到最高的价格
select Max(price) as largestprice from products;
SQL COUNT(), AVG() and SUM() Functions
COUNT() Syntax
The COUNT() function returns the number of rows that matches a specified criteria
SELECT COUNT(column_name) FROM table_name WHERE condition;
举例：finds the number of products---------select count(productid) from products;
举例2：Use the correct function to return the number of records that have the Price value set to 18
Select count*from products where price=18;
Null values are not counted
AVG() Syntax
The AVG() function
returns the average value of a numeric column
SELECT AVG(column_name) FROM table_name WHERE condition;
举例：finds the average price of all products---------select AVG(price) from products;
SUM() Syntax
SELECT SUM(column_name) FROM table_name WHERE condition;
举例： finds the sum of the "Quantity" fields in the "OrderDetails" table---------select SUM(quantity) from orderdetails;
SQL LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
SELECT column1, column2, … FROM table_name WHERE columnN LIKE pattern;
通常与like一起使用的：% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character
MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_)
1.selects all customers with a CustomerName starting with "a":
SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
2.selects all customers with a CustomerName ending with "a":
SELECT * FROM Customers WHERE CustomerName LIKE '%a';
3.selects all customers with a CustomerName that have "or" in any position:
SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
4.selects all customers with a CustomerName that have "r" in the second position:
SELECT * FROM Customers WHERE CustomerName LIKE '_r%';
5.selects all customers with a CustomerName that starts with "a" and are at least 3 characters in length:
SELECT * FROM Customers WHERE CustomerName LIKE 'a__%';
6.selects all customers with a ContactName that starts with "a" and ends with "o":
SELECT * FROM Customers WHERE ContactName LIKE 'a%o';
7.selects all customers with a CustomerName that does NOT start with "a":
SELECT * FROM Customers WHERE CustomerName NOT LIKE 'a%';
Wildcard character
[ ] Represents any single character within the brackets：h[oa]t finds hot and hat, but not hit
The following SQL statement selects all customers with a City starting with "b", "s", or "p":
SELECT * FROM Customers WHERE City LIKE '[bsp]%';
-Represents a range of characters: c[a-b]t finds cat and cbt
SELECT * FROM Customers WHERE City LIKE '[a-c]%';
! Represents any character not in the brackets: h[!oa]t finds hit, but not hot and hat
The two following SQL statements select all customers with a City NOT starting with "b", "s", or "p":
SELECT * FROM Customers WHERE City LIKE '[!bsp]%';
SELECT * FROM Customers WHERE City NOT LIKE '[bsp]%';
# Represents any single numeric character :2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295
In Operator
The IN operator allows you to specify multiple values in a WHERE clause, is a shorthand for multiple OR conditions
The following SQL statement selects all customers that are located in "Germany", "France" or "UK":
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK');
The following SQL statement selects all customers that are from the same countries as the suppliers:
SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
Between Operator (select value in a given range, the range can be number, text, date)
Syntax: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Example: The following SQL statement selects all products with a price BETWEEN 10 and 20:
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
The following SQL statement selects all products with a price BETWEEN 10 and 20. In addition; do not show products with a CategoryID of 1,2, or 3:
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20 AND CategoryID NOT IN (1,2,3);
Between text values example:
The following SQL statement selects all products with a ProductName BETWEEN Carnarvon Tigers and Mozzarella di Giovanni:
SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
Between dates example:
The following SQL statement selects all orders with an OrderDate BETWEEN '01-July-1996' and '31-July-1996':
SELECT * FROM Orders WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;
Aliases别名 give a table, or a column in a table, a temporary name, only exists for the duration of the query
Alias column syntax: SELECT column_name AS alias_name FROM table_name;
Alias table syntax: SELECT column_name(s) FROM table_name AS alias_name;
Alias column example:
The following SQL statement creates two aliases, one for the CustomerID column and one for the CustomerName column:
SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
Note: It requires double quotation marks or square brackets if the alias name contains spaces:
SELECT CustomerName AS Customer, ContactName AS [Contact Person] / 'contact person' FROM Customers;
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country):
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country AS Address
FROM Customers;
Alias for table example
The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter):
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;
Joins 多张表的组合
用于根据两个或多个表中的列之间的关系，从这些表中查询数据
有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM OrdersINNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
Different Types of SQL JOINs
Here are the different types of joins in SQL:
(inner) join: return records that have matching values in both tables 多个表中返回满足 JOIN 条件的所有行
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name条件;
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM ((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
Left(outer)join: return all records from the left table, and the matched records from the right table. The result is NULL from the right side, if there is no match
The following SQL statement will select all customers, and any orders they might have:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Right (outer) join: return all records from the right table, and the matched records from the left table
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ORDER BY Orders.OrderID;
Full (outer)join: returns all records when there is a match in either left or right table
只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行. FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果
SELECT Customers.CustomerName, Orders.OrderID FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
例题：
• Choose the correct JOIN clause to select all records from the two tables where there is a match in both tables.
Select*from orders inner join customers on orders. Customerid=customers.customerid;
• Choose the correct JOIN clause to select all the records from the Customers table plus all the matches in the Orders table.
Select* from orders right join customers on orders.customerid=customers.customerid;
Self join 自连接
Syntax:SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition; (T1 and T2 are different table aliases for the same table)
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID AND A.City = B.City ORDER BY A.City;
SQL UNION Operator(combine the result-set of two or more select statements) 组合两个或更多SELECT语句的结果集，而不返回任何重复的行
• UNION中的每个SELECT语句必须具有相同的列数
• 这些列也必须具有相似的数据类型
• 每个SELECT语句中的列也必须以相同的顺序排列
• 每个SELECT语句必须有相同数目的列表达式
• 但是每个SELECT语句的长度不必相同
Union Syntax: 不允许有重复值
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
例题：
The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values.
Union all syntax:允许有重复值
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
The column names in the result-set are usually equal to the column names in the first SELECT statement in the UNION
UNION结果集中的列名总是等于UNION中第一个SELECT语句中的列名
The following SQL statement returns the cities (only distinct values) from both the "Customers" and the "Suppliers" table:
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
SQL union with where
SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers
WHERE Country='Germany' ORDER BY City;
SQL union all with where
SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers
WHERE Country='Germany' ORDER BY City;
The following SQL statement lists all customers and suppliers:
SELECT 'Customer' As Type, ContactName, City, Country FROM Customers UNION SELECT 'Supplier'As Type, ContactName, City, Country FROM Suppliers;
Group by statement 用于结合合计函数，根据一个或多个列对结果集进行分组
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.
Group by syntax:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
The following SQL statement lists the number of customers in each country:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
The following SQL statement lists the number of customers in each country, sorted high to low:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;
Having clause
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
Having syntax:
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s);
The following SQL statement lists the number of customers in each country. Only include countries with more than 5 customers:
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5 ORDER BY COUNT(CustomerID) DESC; order by 语句放在最后面
Exist operator (used to test for the existence of any record in a subquery)
指定一个子查询，检测行的存在
Exists syntax:
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
Select column name from table name where exists (select column name from table name where condition)
例题1：
1.查询所有选修了1号课程的学生姓名
解析：首先取Student表中的一个元组，然后在SC表中依次找SC.Sno=该元组的Sno，并且对应的Cno='1'，如果存在，则外层查询的where子句返回为真，则Student表中的该元组可以输出。然后依次遍历Student表中的其他元组。
Select sname from students where exists (select*from sc where sno=students.sno and cno=1);
例题2：
1.The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT* FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
2.The following SQL statement returns TRUE and lists the suppliers with a product price equal to 22:
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT*FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);
Any and all operators
The ANY and ALL operators are used with a WHERE or HAVING clause,
The ANY operator returns true if any of the subquery values meet the condition.
The ALL operator returns true if all of the subquery values meet the condition.
Any syntax:
SELECT column_name(s) FROM table_name WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);
The following SQL statement returns TRUE and lists the product names if it finds ANY records in the OrderDetails table that quantity = 10:
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
All syntax:
SELECT column_name(s) FROM table_name WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);
运用access查询某一时间段的A/R：
Sum of sales, sum of cash receipt， sum of sales return
