大数据之—基础SQL查询
一、了解SQL
数据库：保存有组织的数据的容器
注意：数据库软件成为数据库管理系统（DBMS）。数据库是通过DBMS创建和操作的容器。
表：某种特定类型数据的结构化的清单。
注意：表名唯一性，实际上是数据库名和表名的组合的形式命名。
列：表中的一个字段。所有的表都是由一个或多个列组成。
注意：每个列都有自己相应的数据类型，它限制（或允许）该列中存储的数据类型。
行：表中的一个记录。也叫数据库记录
主键：一列（一组列）其值能够唯一标识表中每一行。
主键条件：
1、任意两行都不具有相同的主键值；
2、每一行都必须有一个主键值（主键列不允许null值）；
3、主键列中的值不允许修改或更新；
4、主键值不能重复用（如果某行从表中删除后，他的主键不能赋给以后的新行）。
二、检索数据
A、检索单个列
select prod_name
from products;
输出：
prod_name
fish bean bag toy
bird bean bag toy
注意：
1、多条SQL语句必须以分号“；”分隔。
2、SQL语句不区分大小写，表名、列名、值有可能区分大小写。
3、SQL语句忽略所有空格
B、检索多个列
select prod_id，prod_name,prod_price
from products;
输出：
prod_id prod_name prod_price
BNB001 fish bean bag toy 3.0989
注意：
选择多列时，列名之间用逗号“，”隔开，最后一列不加，加上则报错。
C、检索所有列
select *
from products；
注意：
应用通配符“*”，检索出所有列
D、检索不同值（去掉重复值）
select distinct vend_id
from products；
输出：
vend_id
BRS001
DLL001
FNG001
注意：
1、去重关键字DISTINCT，放在列名前，只能用于列
2、DISTINCT作用于所有列，不能作用于部分列
3、DISTINCT不能用于计算函数或者表达式
E、限制结果
1、SQL server 和access用top关键字来限制最多返回多少行
select top 5 prod_name
from products；
2、DB2用fetch关键字来限制最多返回多少行
select prod_name
from products
fetch first 5 rows only；
3、Oracle用rownum来计算返回多少行
select prod_name
from products
where rownum<=5；
4、MySQL、MariaDB、psostgreSQL、SQLite使用limit子句限制最多返回多少行
select prod_name
from products
limit 5 offset 2；--从第2行起的5行
注意：第一个被检索的行是第0行，而不是第一行。
limit 5 offset 2可以缩写为limit 2，5；
F、使用注释
注释方式一：--
select prod_name --这是一条注释
from products；
注释方式二：#
这是一条注释
select prod_name
from products；
注释方式三：/* */
/* /select prod_name
from products*/
select prod_name
from products；
三、排序检索数据
A、排序数据：order by子句
select prod_name
from products
order by prod_name；--按那么进行排序
注意：order by 子句永远在select语句的最后面。
B、按多个列排序：简单的指定列名，中间用逗号“，”隔开即可。
select prod_name， prod_id, prod_price
from products
order by prod_name,prod_id, prod_price;
C、按列位置排序：order by 除了支持列名排序还支持按相对位置进行排序。
select prod_name， prod_id, prod_price
from products
order by 2,3;
注意：此方法可以省略重复列名的情况，但也因没有给出明确列名而容易错用；另外，容易对select清单更改时造成错误数据排序。
D、指定排序方向
select prod_name， prod_id, prod_price
from products
order by prod_price DESC;
注意：
1、ASC升序（默认）、DESC降序。
2、升降序关键字只应用到直接位于其前面的列名，如果需要对每一列排序，需对每一列进行指定关键字。
四、过滤数据
Q\指定搜索条件：where子句
select prod_name， prod_id, prod_price
from products
where prod_price=3.49；
注意：
1、where子句的位置位于order by 子句之前
B、where子句操作符
检查单个值
select prod_name， vend_id, prod_price
from products
where prod_price<3.49；
不匹配检查
select prod_name， vend_id, prod_price
from products
where vend_id<>'DLL001'；
范围值检查
select prod_name， vend_id, prod_price
from products
where prod_price between3.49 and 5.00；
空值检查
select prod_name， vend_id, prod_price
from products
where prod_price is null；
注意：
1、当搜索过滤条件为值不需要单引号，为字符串需要单引号引起来。
2、between与and搭配使用，两个值之间用and隔开，前为开始值，后为结束值。
五、高级过滤数据
A、组合where子句：使用and和or操作符。
and操作符:满足所有给定的条件
select prod_id, prod_price, prod_name
from products
where vend_id='DLL001' AND prod_price<=4
order by prod_id;
or操作符：匹配满足任一条件
select prod_id, prod_price, prod_name
from products
where vend_id='DLL001' or prod_price<=4
order by prod_id;
B、求值顺序
select prod_id, prod_price, prod_name
from products
where （vend_id='DLL001' or prod_price<=4）and prod_price>8
order by prod_id;
注意：当and和or一起使用的时候，要注意求值顺序，可用圆括号控制顺序。
C、IN操作符：取一组有逗号隔开的括在圆括号内的合法值。与or操作符功能相同。
select prod_id, prod_price, prod_name
from products
where vend_id IN （'DLL001','BSR001'）;
D、NOT操作符：否定其后所跟的任何条件。他总是与其他操作符一起使用。用于过滤的列名前面。
select prod_id, prod_price, prod_name
from products
where not vend_id IN （'DLL001','BSR001'）;
六、通配符进行过滤
通配符：用来匹配值的一部分特殊字符。通配符只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。
A、百分号%通配符
select prod_id, prod_name
from products
where prod_name like 'fish%';--access通配符是*而不是%
B、下划线_通配符：只能匹配单个字符，不能匹配多个字符
select prod_id, prod_name
from products
where prod_name like '_ inch teddy bear';--SB2不支持通配符，access需要用？而不是_。
注意：对应一个通配符，比如18需要两个 两个通配符才能匹配。总是刚好匹配一个字符，不能多也不能少。
C、方括号[ ]通配符：指定一个字符集，必须匹配指定位置的一个字符。
select cust_contact
from customers
where cust_contact like '[JM]%'
order by cust_contact;
注意：此通配符可以用前缀字符^来否定
七、创建计算字段
A、拼接操作符“+"和”||"
“+”拼接操作符：
select vend_name + ‘（‘+vend_country+’）’
from vendors
order by vend_name;
“||“拼接操作符：
select vend_name || ‘（‘||vend_country||’）’
from vendors
order by vend_name;
“，”拼接符：
select vend_name |，‘（‘，vend_country，’）’
from vendors
order by vend_name;
注意：access和SQLserver使用+拼接符。DB2、Oracle、postgreSQL、SQLite和open office base使用||拼接符，MySQL、MariaDB用，拼接符
B、使用别名：别名用as关键字赋予。
select vend_name |，‘（‘，vend_country，’）’
as vend_title
from vendors
order by vend_name;
C、执行算术计算
select prod_id,quantity,itemprice,quantity*itemprice
from orderitems
where order_num=2008;
注意：执行算术计算时，直接是列名进行加减乘除操作。
八、使用函数处理数据
A、函数
|
函数
|
语法
|
|
提取字符串的组成部分
|
access使用MID（），DB2、Oracle、postgreSQL、SQLite使用SUBSTR（）；MySQL、SQLserver使用SUBSTRING（）
|
|
数据类型转换
|
access和Oracle使用多个函数，每种类型的转换有一个函数，DB2和postgreSQL使用cast（）；MariaDB，MySQL，SQLserver使用convert（）
|
|
取当前日期
|
access使用now（），DB2和postgreSQL使用current_date（）MariaDB，MySQL使用currentdate（），Oracle使用sysdate，SQLserver使用getdate（），SQLite使用date（）
|
B、使用函数
文本处理函数
select vend_name, upper(vend_name) as vend_name_upcase
from vendors
order by vend_name;
常用文本处理函数
|
函数
|
说明
|
|
LEFT（）（或使用子字符串函数）
|
返回字符串左边的字符
|
|
LENGTH（）（也使用datelength（）或len()）
|
返回字符串长度
|
|
LOWER（）（access使用LCASE（））
|
将字符串转换为小写
|
|
LTRIM（）
|
去掉字符串左边空格
|
|
RIGHT（）或使用子字符串函数
|
返回字符串右边的字符
|
|
RTRIM（）
|
去掉字符串右边空格
|
|
SOUNDEX（）
|
返回字符串的SOUNDEX值
|
|
UPPER（）（access用UCASE（））
|
将字符串换成大写
|
access和postgreSQL不支持SOUNDEX
日期和时间处理函数
select order_num
from orders
where datepart（yy, order_date）=2012;
数值处理函数
|
函数
|
说明
|
|
ABS()
|
返回一个数的绝对值
|
|
COS()
|
返回一个角度的余弦
|
|
EXP（）
|
返回一个数的指数值
|
|
PI（）
|
返回圆周率
|
|
SIN（）
|
返回一个角度的正弦值
|
|
SQRT（）
|
返回一个数的平方根
|
|
TAN（）
|
返回一个数角度的正切
|
九、汇总数据
A、SQL聚集函数
|
函数
|
说明
|
|
AVG（）
|
返回某列平均值
|
|
COUNT（）
|
返回某列行数
|
|
MAX（）
|
返回某列最大值
|
|
MIN（）
|
返回某列最小值
|
|
SUM（）
|
返回某列值之和
|
AVG（）函数
select AVG（prod_price）as avg_price
from products;
注意：
1、AVG（）函数只用于单列个列，求多个列平均值，需使用多个AVG（）函数；
2、AVG（）函数忽略值为null的行；
count（）函数
使用count（*）对表中行的数据进行计数，包含null行。
select count（*）as num_cust
from customers;
对特定列count（column）进行具有值的统计，忽略null值。
select count(cust_email) as num_cust
from customers;
MAX()函数
select MAX（prod_price）as max_price
from products；
注意：1、MAX函数忽略null的行；2、非数值类的列使用MAX（）函数返回返回该列排序最后一行。
MIN（）函数
select MIN（prod_price）as min_price
from products；
注意：1、MAX函数忽略null的行；2、非数值类的列使用MAX（）函数返回返回该列排序最前一行。
SUM（）函数
select SUM（quantity*item_price）as items_orderd
from orderitems
where order_num=2005；
注意：sum（）函数忽略null的行。
组合聚集函数
select
count（*）as num_cust
MAX（prod_price）as max_price
MIN（prod_price）as min_price
SUM（quantity*item_price）as items_orderd
from products；
十、分组数据
A、创建分组：group by子句。
select vend_id，count（*）as num_prods
from products
group by vend_id；
B、过滤分组：HAVING子句
where过滤行，HAVING过滤分组
select cust_id，count（*）as orders
from orders
group by cust_id
HAVING count（*）>=2;
同时使用where和HAVING例子
select vend_id，count（*）as num_prods
from products
where prod_price>=4
group by vend_id
HAVING count(*)>=2;
C、分组和排序
select order_num，count（*）as items
from orderitems
group by order_num
HAVING count（*）>=3
order by items，order_num；
D、select子句顺序
|
子句
|
说明
|
是否必须使用
|
|
select
|
要返回的列或表达式
|
是
|
|
from
|
从中检索的数据的表
|
仅在从表中选择数据时使用
|
|
where
|
行级过滤
|
否
|
|
group by
|
分组说明
|
仅在安祖计算聚集时使用
|
|
having
|
组级过滤
|
否
|
|
order by
|
输出排序顺序
|
否
|
十一、使用子查询
A、子查询：嵌套在其他查询中的查询
B、利用子查询进行过滤。
select cust_name，cust_contact
from customers
where cust_id IN （select cust_id
from orders
where order_num IN (SELECT order_num
from orderitems
where prod_id=‘RGAN01’））；
注意：1、对于嵌套子查询数目没有限制；2、作为子查询的select子句只能查询单个列。
C、作为计算字段使用子查询。
select cust_name，cust_state，（select count（*）
from orders
where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name;
注意：子查询中的where子句与之前的where子句使用不同，子查询中的where子句需使用完全限定列名。
十二、联结表
A、创建联结（内联结、等值联结）
select vend_name，prod_name，prod_price
from vendors，products
where vendors.vend_id=products.vend_id;
也可以表达为
select vend_name，prod_name，prod_price
from vendors inner join products
on vendors.vend_id=products.vend_id;
注意：where子句的列为完全限定列名
笛卡尔积：由没有联结条件的表关系返回的结果。
叉联结：返回笛卡尔积的联结。
B、联结多个表。创建联结的基本规则相同，首先列出所有的表，然后定义表之间的关系。
select prod_name，vend_name，prod_price，quantity
from ordersitem，products，vendoers
where products.cend_id=vendor.vend_id
and orderitems.prod_id=products.prod_id
and order_num=2007；
十三、创建高级联结
A、使用表别名
select cust_name，cust_contact
from customers as C，orders as O，ordersitem as OI
where C.cust_id=O.cust_id
and OI.order_num=O.order_id
and prod_id='RGAN01';
注意：Oracle中不用as 直接是customers C
B、自联结：通过使用表别名代替
使用子查询select语句
select cust_id，cust_name，cust_contact
from customers
where cust_name=（select cust_name
from customers
where cust_name=‘jim Jones’）；
使用自联结可这样表达
select C1.cust_id，C1.cust_name，C1.cust_contact
from customers asC1，customers as C2
where C1.cust_name=C2.cust_name
and C2.cust_contact=‘jim jones’;
C、自然联结
select C.*，o.order_num，o.order_date，oi.prod_id，oi.quantity，oi.item_price
from customers as C，orders as o，ordersitem as oi
where C.cust_id=o.cust_id
and oi.order_num=o.order_num
and prod_id=‘RGAN01’;
D、外联结
select customers.cust_id，orders.order_num
from customers LEFT OUTER JOIN orders
ON customers.cust_id=orders.cust_id；
注意：使用outer join语法时，必须使用LEFT或RIGHT关键字指定包括其所有行的表。
E、全外联结：检索两个表中的所有航并关联那些可以关联的行
select customers.cust_id，orders.order_num
from customers FULL OUTER JOIN orders
ON customers.cust_id=orders.cust_id；
F、使用带有聚集函数的联结
select customers.cust_id，count（orders.order_num）as num_ord
from customers INNER JOIN orders
ON customers.cust_id=orders.cust_id --这里使用INNER JOIN将customers和orders表互相关联起来
group by customers.cust_id；
十四、组合查询：union操作符
创建组合查询：给出每条select语句，在各条select语句之间放上关键字UNION
select cust_name，cust_contact，cust_email
from customers
where cust_state IN（‘IL’,'IN','MI'）
union
select cust_name，cust_contact，cust_email
from customers
where cust_name='fun4a11';
注意：
1、union必须由两条及以上的select语句组成
2、union必须每个语句查询相同的列、表达式、或聚集函数
3、列数据类型必须兼容
4、使用union时，重复行会被自动取消过滤，如需返回重复行，需使用union all
5、使用union排序，只能在最后使用一条order by语句进行排序，不允许使用多条。
十五、插入数据：INSERT语法。
A、插入完整行
INSERT INTO 表名（列名1，列名2）
VALUES（值1，值2）；
注意：可以省去列名直接是表对应值，但是插入位置跟值的位置有关，一般不建议
B、插入部分行。直接省略不需要插入的行即可。
注意：省略的列必须满足，1、该列定义允许null值；2、在表定义中给出默认值。
C、插入检索出的数据：INSERT SELECT语句
INSERT 表名1（列名1，列名2）
SELECT 列名1，列名2
FROM 表名2
WHERE=过滤条件
D、从一个表复制到另一个表：select into
select 列名
INTO 新表名
from 旧表名；
十六、更新和删除数据
A、更新数据：update语句
update 表名
set 列名1=值，列名2=值
where 更新条件
如：update customers
set cust_email=‘dj@163.com’
where cust_id=‘1009’；
B、删除数据
delete 列名
from 表名
where 过滤条件
注意：1、更新和删除都最好带where语句，2、使用更新和删除语句前，先跑下select语句，检验结果是否正确。
十七、创建和操纵表
A、创建表
create table 表名
（列名1 数据类型 是否允许NULL,
列名2 数据类型 是否允许NULL）;
注意：创建新表时，表名必须不存在，否则会出错。
B、更新表
ALTER TABLE 表名
更新内容
如：alter table vendors
add vend_phone CHAR（20）；--更新表vendors，增加veng_phone列。
C、删除表
drop table 表名
