基础知识
1、select distinct 语句(用于返回唯一 不同的值)
使用场景说明:有时候表中的某些列的值不是唯一的(重复数据过多,但恰恰我们不需要那些重复的数据)时,便可使用DISTINCT 语句过滤掉重复的值。
使用语法:SELECT DISTINCT 列名称 FROM 表名称;
2、AND & OR 运算
使用场景说明:一般与where 子句 配合使用 表示多个条件联合查询,可以把多个条件结合起来,如果第一个和第二个条件都成立,那么使用 and 结合,如果第一个条件和第二个条件只需要一个成立则使用 or 将条件连接。
使用实例:
1、使用 AND 来显示所有姓为 "Carter" 并且名为 "Thomas" 的人;
SELECT * FROM Persons WHERE FirstName='Thomas' AND
LastName='Carter'
查出结果集就是满足两个条件的数据。
2、使用 OR 来显示所有姓为 "Carter" 或者名为 "Thomas" 的人:
SELECT * FROM Persons WHERE firstname='Thomas'
OR
lastname='Carter'
查出的结果集就是两个条件 只要满足任何一个的结果集。
3、Order by 语句
根据指定的列队结果集进行排序,默认按照升序排列。
排列方式分为 Order by asc (升序) 和 Order by desc (降序)。这里需要进一步说明的是如果排序的条件相同的数据不止一条的话,结果集还是会根据升序优先的原则展示数据(null 为优先显示数据);
基础语法: select * from tableName where tableOrder> '10000' and tableOrder<'20000' Order by tableOrder desc
所查结果集为 tableName 表中 tableOrder 的值介于 10000---20000之间的数据,并按照降序排列。
4、TOP 语句
使用场景:数据量过大的话,使用top 语句查询表头多少笔数据。
不同的数据库系统 TOP 语句略有差异,这里不做展示。
5、LIKE 操作符(模糊查询)
like 操作符用于where 子句中搜索列中的制定模式。
基本语法:(实例描述 查找居住在以N开头的名字的城市的人)
SELECT * FROM Persons WHERE City LIKE 'N%'
注意 like ‘%N’(以N开头) 与 like '%N%' (名称中含N) 和 like 'N%'(以 N字符结尾的)
6、SQL 的通配符
sql 通配符必须配合like操作符同时使用,可以在数据库查询的时候可以替代一个或多个字符,通配符有四种;
通配符 | 描述 |
---|---|
% | 替代一个或多个字符 |
_ | 仅替代一个字符 |
[charlist] | 字符列中的任何单一字符 |
[^charlist] 或者 [!charlist] | 不在字符列 |
依据下张Person表 对以上几种操作符详细说明;
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
1、%操作符
现在,我们希望从上面的 "Persons" 表中选取居住在以 "Ne" 开始的城市里的人:
SELECT * FROM Persons WHERE City LIKE 'Ne%'
结果集:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
2 | Bush | George | Fifth Avenue | New York |
2、_操作符
现在,我们希望从上面的 "Persons" 表中选取名字的第一个字符之后是 "eorge" 的人:
SELECT * FROM Persons WHERE FirstName LIKE '_eorge'
结果集:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
2 | Bush | George | Fifth Avenue | New York |
接下来,我们希望从 "Persons" 表中选取的这条记录的姓氏以 "C" 开头,然后是一个任意字符,然后是 "r",然后是任意 字 符,然后是 "er":
SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'
结果集:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
3 | Carter | Thomas | Changan Street | Beijing |
3、[ ]操作符
现在,我们希望从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人:
SELECT * FROM Persons WHERE City LIKE '[ALN]%'
结果集:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
4、[! ] 操作符
现在,我们希望从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人:
LECT * FROM Persons WHERE City LIKE '[!ALN]%'
结果集:
Id | LastName | FirstName | Address | City |
---|---|---|---|---|
3 | Carter | Thomas | Changan Street | Beijing |
7、between 操作符
between ... and 操作符在where子句中使用,介于选取两个值之间范围的数据。
基本语法:select * from tableName where columName between value1 and value2
重要事项:不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。某些数据库会列出介于 "value1" 和 "value2" 之间的人,但不包括 "value1" 和 "value2" ;某些数据库会列出介于 "value1" 和 "value2" 之间并包括 "value1" 和 "value2" 的数据;而另一些数据库会列出介于 "value1" 和 "value2" 之间的数据,包括 "value1" ,但不包括 "value2" 。所以,请检查你的数据库是如何处理 BETWEEN....AND 操作符的!
8、JOIN 语法
join 语法用于根据两张或者多张表之间的关联关系,查询这些表中的相关数据。
数据库中的表可通过主键将表与表关联起来, 主键时表中的一个列,主键的值在一个表中是唯一的,这样就可以把表与表通 过主键关联起来做捆绑查询。
请看 "Persons" 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
请注意,"Id_P" 列是 Persons 表中的的主键。这意味着没有两行能够拥有相同的 Id_P。即使两个人的姓名完全相同,Id_P 也可以区分他们。
接下来请看 "Orders" 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
请注意,"Id_O" 列是 Orders 表中的的主键,同时,"Orders" 表中的 "Id_P" 列用于引用 "Persons" 表中的人,而无需使用他 们的确切姓名。请留意,"Id_P" 列把上面的两个表联系了起来
下边引用两个表,查询查询谁订购了产品,并且订购了多少。
方式1:普通SQL
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE
Persons.Id_P = Orders.Id_P
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
方式2: inner join
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons
INNER JOIN Orders ON Persons.Id_P = Orders.Id_P ORDER BY Persons.LastName
astName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
注:inner join 在表中存在至少有一个匹配(即用来关联的这个一个字段是有值)的时候返回该行,否则不出现该条记录。
同样的查询还有:
- JOIN: 如果表中有至少一个匹配,则返回行
- LEFT JOIN: 即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN: 即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN: 只要其中一个表中存在匹配,就返回行
9、LEFT Join On(左连接,某些数据库也将其称为左外连接)
Left Join 会从左表哪里返回所有的行,即使在右表中没有匹配的行。
语法:SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
实例:下表
"Persons" 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
"Orders" 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
业务需求:列出Persons 表中所有欧订单的人,并根据LastName进行降序排列。
sql 语句:Select Persons.lastName Persons.firstName,Order.OrderNo from Persons left Join Orders On Persons.ID_p=Orders.ID_p Order By Persons.lastName 。
查询结果集:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
Bush | George |
10、Right Join (右连接 也称右外连接)
Right Join 会根据右表返回所有的行,即使在左表中没有匹配的行。
语法:SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
实例:下表
Persons 表
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
Orders 表
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
业务需求:列出所有的订单 和他的所有人 并根据Persons表中的LastName 字段进行降序排列 。
sql 语句:Select Persons.LastName,Persons.FristName,Orders.OrderNo from Persons Right Join Orders On Persons.Id_p = Orders.Id_p ORDER BY Persons.LastName;
查询结果集:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
34764 |
11、Full Join On关键字
只要其中某个表存在匹配,FULL JOIN 关键字就会返回行。
基础语法:SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
实体:下表
"Persons" 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
"Orders" 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
业务需求:列出所有的人,以及他们的定单,以及所有的定单,以及定购它们的人。
SQL 语句:SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.Id_P=Orders.Id_P ORDER BY Persons.LastName
查询结果集:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
Bush | George | |
34764 |
full join on 关键字会从左表(Persons)和右表(Orders)那里返回所有的行。如果 "Persons" 中的行在表 "Orders" 中没有 匹配,或者如果 "Orders" 中的行在表 "Persons" 中没有匹配,这些行同样会列出。
12、平均数函数 AVG()
AVG()函数返回数值列。null 值不包括计算中。
基础语法: Select SVG(column_name) from table_name;
SVG()实例 :下表Orders表
O_Id | OrderDate | OrderPrice | Customer |
---|---|---|---|
1 | 2008/12/29 | 1000 | Bush |
2 | 2008/11/23 | 1600 | Carter |
3 | 2008/10/05 | 700 | Bush |
4 | 2008/09/28 | 300 | Bush |
5 | 2008/08/06 | 2000 | Adams |
6 | 2008/07/21 | 100 | Carter |
需求:计算 "OrderPrice" 字段的平均值。
SQL 语句 :Select AVG(OrderPrice) AS orderAvgrage from orders;
查询结果集:
OrderAverage |
---|
950 |
拓展实例:找到 OrderPrice 值高于 OrderPrice 平均值的客户。
SQL 语句 :SELECT Customer FROM Orders WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
查询结果集:
Customer |
---|
Bush |
Carter |
Adams |
13、查询总数 Count();
函数返回指定列的值的数目(null 值不计入在内)
说明:COUNT(column_name) 与COUNT(*)
COUNT(column_name) 返回列 column_name (值为null 的不计算在内)。
COUNT(*) 返回数据库表中的总资料数目。
基础语法:SELECT COUNT(column_name) FROM table_name;
COUNT()语法实例一:
COUNT(*) 函数返回表中的记录数
SQL 语句:SELECT COUNT(*) FROM table_name
COUNT()语法实例二:
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目:
SQL 语句:SELECT COUNT(DISTINCT column_name) FROM table_name
14、First() 函数 与Last() 函数
FIRST() 函数返回指定的字段中第一个记录的值。
Last() 函数返回指定的字段中最后一个记录的值。
基础语法:Select first(column_name) from tableName;
基础语法:Select last(column_name) from tableName;
实例:如下表Orders
O_Id | OrderDate | OrderPrice | Customer |
---|---|---|---|
1 | 2008/12/29 | 1000 | Bush |
2 | 2008/11/23 | 1600 | Carter |
3 | 2008/10/05 | 700 | Bush |
4 | 2008/09/28 | 300 | Bush |
5 | 2008/08/06 | 2000 | Adams |
6 | 2008/07/21 | 100 | Carter |
需求一:查找 "OrderPrice" 列的第一个值。
SQL 语句:SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
查询结果集:
FirstOrderPrice |
---|
1000 |
需求二:查找“OrderPrice” 列的最后一个值。
SQL 语句:SELECT Last(OrderPrice) AS FirstOrderPrice FROM Orders
查询结果集:
LastOrderPrice |
---|
100 |
15、Max()函数与Min()函数、sum ()函数
MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
MIN 函数返回一列中的最小值。NULL 值不包括在计算中。
SUM 函数返回数值列的总数(总额) 通常与Gourp By 配合使用。
基础语法:SELECT MAX(column_name) /MIN(column_name) FROM table_name;
实例:下表Orders
O_Id | OrderDate | OrderPrice | Customer |
---|---|---|---|
1 | 2008/12/29 | 1000 | Bush |
2 | 2008/11/23 | 1600 | Carter |
3 | 2008/10/05 | 700 | Bush |
4 | 2008/09/28 | 300 | Bush |
5 | 2008/08/06 | 2000 | Adams |
6 | 2008/07/21 | 100 | Carter |
需求一:查找OrderPrice 列下最大值;
SQL 语句:SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders;
LargestOrderPrice |
---|
2000 |
需求二:查找OrderPrice 列下最小值;
SQL 语句:SELECT MIN(OrderPrice) AS LargestOrderPrice FROM Orders;
SmallestOrderPrice |
---|
100 |
需求三:查找 "OrderPrice" 字段的总数。
SQL 语句:SELECT SUM(OrderPrice) AS OrderTotal FROM Orders;
OrderTotal |
---|
5700 |
16、SQL 快速参考
语句 | 语法 |
---|---|
AND / OR | SELECT column_name(s) FROM table_name WHERE condition AND|OR condition |
ALTER TABLE (add column) | ALTER TABLE table_name ADD column_name datatype |
ALTER TABLE (drop column) | ALTER TABLE table_name DROP COLUMN column_name |
AS (alias for column) | SELECT column_name AS column_alias FROM table_name |
AS (alias for table) | SELECT column_name FROM table_name AS table_alias |
BETWEEN | SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2 |
CREATE DATABASE | CREATE DATABASE database_name |
CREATE INDEX | CREATE INDEX index_name ON table_name (column_name) |
CREATE TABLE | CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... ) |
CREATE UNIQUE INDEX | CREATE UNIQUE INDEX index_name ON table_name (column_name) |
CREATE VIEW | CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition |
DELETE FROM | DELETE FROM table_name (Note: Deletes the entire table!!) or DELETE FROM table_name |
DROP DATABASE | DROP DATABASE database_name |
DROP INDEX | DROP INDEX table_name.index_name |
DROP TABLE | DROP TABLE table_name |
GROUP BY | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 |
HAVING | SELECT column_name1,SUM(column_name2) FROM table_name GROUP BY column_name1 HAVING SUM(column_name2) condition value |
IN | SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..) |
INSERT INTO | INSERT INTO table_name VALUES (value1, value2,....) or INSERT INTO table_name |
LIKE | SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern |
ORDER BY | SELECT column_name(s) FROM table_name ORDER BY column_name [ASC|DESC] |
SELECT | SELECT column_name(s) FROM table_name |
SELECT * | SELECT * FROM table_name |
SELECT DISTINCT | SELECT DISTINCT column_name(s) FROM table_name |
SELECT INTO (used to create backup copies of tables) | SELECT * INTO new_table_name FROM original_table_name or SELECT column_name(s) |
TRUNCATE TABLE (deletes only the data inside the table) | TRUNCATE TABLE table_name |
UPDATE | UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value |
WHERE | SELECT column_name(s) FROM table_name WHERE condition |