一，数据库

(1)数据库

数据库：是一个以某种有组织的方法存储的数据集合

数据库软件：数据库管理系统(DBMS)，一般用DBMS创建和操作数据库

(2)数据类型

(3)主键

主键：表中每一行都应该有一列唯一标识自己

特点：a，任意两行不应该有相同的主键值；

b，每一行必须有一个主键值(主键列不许有空值)；

c，主键列中的值不允许有更新或更改；

d，主键值不能重复使用(若某行值被删除，它的主键值不可以赋给新的行)

(4)什么是SQL

SQL：是一种专门用来与数据库沟通的语言(Structured Query Language)

优点：简单易学；灵活，可以进行非常复杂和高级的数据库操作

注意：不区分大小写

二，SELECT语句

(1)检索单列

select ‘字段名’ from ‘表名’；(一般用；表示一条语句结束)

(2)检索多列

1，select ‘字段名1’，‘字段名2’，‘字段名3’ from ‘表名’；(字段用，隔开)

2，select * from ‘表名’；(*表示所有字段名)

3，select distinct ‘字段名’ from ‘表名’；(distinct表示去重)

4，限制结果

select top 5 ‘字段名’ from ‘表名’；(SQL Server 和 Access，用top5限制前5行)

select ‘字段名’ from ‘表名’ fetch first 5 rows only；(DB2，限制前5行)

select ‘字段名’ from ‘表名’ where rownum <= 5；(Oracle需要rownum行计数器计算行)

select ‘字段名’ from ‘表名’ limit 5；(MySQL，MariaDB，PostgreSQL，SQLLite)

select ‘字段名’ from ‘表名’ limit 5 offset 5；(第一个数字指从哪开始，第二个指检索几行)

5，使用注释

-- (两个连字符--加空格)

#(一行注释)

/*   */(多行注释)

(3)排序检索

select ‘字段名’ from ‘表名’ order by ‘字段名1’；(按字段名1升序排列)

select ‘字段名’ from ‘表名’ order by ‘字段名1’ desc；(按字段名1降序排列)

select ‘字段名1’ from ‘表名’ order by ‘字段名1’，‘字段名2’；(在按1排的基础上按2排)

select ‘字段名1’，‘字段名2’，‘字段名3’ from ‘表名’ order by 1；(按‘字段名1’排序)

若需对a和A进行排序，则请求数据库管理员帮助

三，WHERE语句

where位于from之后

(1)操作符

=        !=    >=    <=

(2)范围匹配(and操作符优先级高于or)

1，满足其中一个值即可

in操作符比or操作符执行更快

where ‘字段’ in (‘值1’，‘值2’)；(in后也可以包含其他select语句)

where 条件1 or 条件2；

2，同时满足

where ‘字段’ between ‘值1’ and ‘值2’ ；(包含值1和值2)

where 条件1 and 条件2；

(3)模糊匹配 like

1，通配符%

where ‘字段’ like ‘值%’；(字段包含值，且值在第一位)

where ‘字段’ like ‘%数值’；(字段包含数值，且数值处于最后)

%表示多个字符，可位于前面，中间，后面

若字母后面有空格则不会被筛选出来，需要用函数去掉空格

不会匹配null

2，通配符_(DB2不支持通配符_)

_只匹配单个字符，不能多也不能少

3，通配符技巧

a，不要过度使用，如其他操作符可达到目的，应使用操作符；

b，使用时，不要放在开始处，搜索起来最慢；

c，仔细通配符放置的位置；

(4)找到空值

where ‘字段’ is not null；(筛选出字段为不为空的值)

not操作符用来表示否定，一般和其他操作符一起使用

四，创建计算字段

计算字段：

我们需要直接从数据库中检索出转化，计算，格式化过的数据，然后在客户端重新格式化。

操作符，+，-，*，/

拼接字段：

创建由两列组成的标题，vendors表中包含供应商和地址信息。

假如，要生成一个供应商报表，需要在格式化的名称中列出供应商的位置。此报表需要一个值，而表中的数据存储在两个列vend_name和vend_country中。需要将vend_country用括号括起来，这些东西没有存储在数据库表中。

解决办法：把两个列撇接起来，在SQL中select语句中，可用一个特殊的操作符来拼接两个列。可用(+)和(||)表示。

Access和SQL Server使用+

DB2，Oracle，PostgreSQL，SQLite，Open Office Base使用||

select vend_name + ‘(’ + vend_country +‘)’ from Vendors order by vend_name；

select concat(vend_name，‘(’，vend_country，‘)’)from Vendors order by vend_name

concat()拼接函数

分析：

a，存储在vend_name列中的名字；

b，包含一个空格和一个(的字符串；

c，存储在vend_country列中的国家；

d，包含一个)的字符串

select rtrim(vend_name)+‘(’+rtrim(vend_country)+‘)’ from Vendors

rtrim()函数：去掉右边的所有空格

ltrim()函数：去掉左边的所有空格

trim()函数：去掉两边的空格

创建的没有表名，需要对它进行命名

select rtrim(vend_name)+‘(’+rtrim(vend_country)+‘)’  as vend_title from Vendors

五，函数

1，文本处理函数

rtrim()：去掉右边的所有空格

upper()：将所有小写字母转换成大写字母

lower()：将字母全部转换成小写

left(‘hello’，‘Word’)：返回字符串左边的字符       ‘hello’

left(‘hello’，4)：返回字符串从左边的几个字符     ‘hell’

right(‘hello’，3)：返回字符串右边的几个字符       ‘llo’

replace(‘vessel’，‘e’，‘a’)：将字母e转换成a         ‘vassel’

substring(‘Angola’，2，4)：从第2位开始取4个字符     ‘ngol’

position(‘ll’ in ‘hello’)：看第一个字符串是否包含在第二个字符串，输出位置       3

position(‘an’ in ‘camper’)： 若不存在，返回0                0

locate(x，y，z)：在y中找x，从z位开始查找，

locate(‘k’，‘12k-15k’)：k在第3位，返回3

locate(‘k’，‘8k-15k’，3)：返回6，若从第2位开始查找，则返回2

soundex()：返回类似发音字符和音节

where soundex(cust_countact)= soundex(‘Micheal Green’)        ‘Michelle Green’

2，日期和时间处理函数

from_unixtime()：将时间戳转化成日期时间格式

datepart()：含有两个参数，返回成分和从中返回的日期

datepart(‘yyyy’，‘字段’)：从字段中返回年份

SQL Server，Sybase，Access版本使用datepart函数

date_part()：返回检索日期     date_part(‘year’，order_date)

PostgreSQL版本使用date_part

ro_number(to_char(order_date，‘YYYY’))

Oracle版本使用to_number()用来捋提出的成分转化成数值，to_char()用来提取日期成分

to_date(‘01-01-2010’)：将字符串转化成日期

Oracle版本

convert()：将字符串转化成日期

SQL Server版本

year(order_date)：提取字段年份

MySQL和MariaDB版本

interval  n  day：返回n天后的日期

date ‘2016-05-20’ + interval 5 day ：5天后‘2016-05-25’

date ‘2016-05-20’ + 5 ：5天后‘2016-05-25’

date ‘2016-05-20’ +interval 5 month ：5月后‘2016-10-20’

date ‘2016-05-20’ + interval 5 year：5年后 ‘2021-05-20’

extract()：检索日期组成部分

year    month     day     hour     minute     second

extract(year  from  ‘2006-05-20’)： ‘2006’

3，数值处理函数

一般用于代数，三角，几何运算

abs()：返回绝对值

nvl(x，y)：返回不是空值的第一个

coalesce(x，y，z)：返回不为空的第一个值，否则返回null

coalesce(‘null’，‘none’)：    ‘none’

nullif(x，y)：x=y，返回null，否则返回第一个

nullif(‘snp’，‘lab’)：‘snp’

floor(32.9)：返回整数值32

ceil(3.2)：四入五入，返回 4

concat()：合并

mod(a，b)：取余a%b

round()：将小数保留几位

round(7.86,1)：7.9

round(7253.86，-3)：7000

sin()：返回一个角度的正弦

cos()：返回一个角度的余弦

tan()：返回一个角度的正切

exp()：返回一个数的指数值

sqrt()：返回一个数的平方根

pi()：返回圆周率

4，聚集函数

sum()：求和(忽略值为null的行)

avg()：求平均值(忽略列值为null的行)

count()：计数(指定列名会忽略空值)

max()：最大值(忽略值为null的行)

min()：最小值(忽略值为null的行)

5，聚集函数

可以使用多个函数，记得取别名

六，分组数据

select ‘字段’，count(*) from ‘表名’ group by ‘字段1’

表示对字段进行分组后计算

在 group by 后不可以跟别名

在 where 之后 order by 之前

1，过滤分组(having)

select ‘字段’，count(*) from ‘表名’ group by ‘字段1’ having count(*)>2；

where 和 having 语句区别：

where 不可以筛选分组，先运行 where 筛选，后 having 筛选

having 和 where 使用语法相同，要结合 group by 语句使用

2，分组和排序

group by 和 order by一般同时使用

七，使用子查询

子查询：嵌套在其他查询中的查询

order表中存储订单日期，顾客ID

orderItems表存储各订单的物品

customers表存储顾客的有关信息

(1)利用子查询进行过滤

需列出订购物品RGAN01的所有顾客

1，筛选出购买RGAN01的订单编号

select order_num from orderItems where prod_id = ‘RGAN01’；

2，有订单编号的顾客ID

select cust_id from orders where order_num in(20007,20008)；

3，返回顾客信息

select cust_name，cust_contact from customers where cust_id in (‘1000000004’，‘1000000005’)；

用子查询语句：

select cust_name，cust_contact from customer where cust_id

in(select order_num from orders where order_num

in(select order_num from orderItems where prod_id = ‘RGAN01’))；

从内向外处理，只能是单列

(2)计算字段使用子查询

需要显示customers表中每个顾客的订单总数

1，从customers表中检索顾客列表

2，检索出的每个顾客，统计在orders表中的订单数目

select cust_name，cust_state，

(select count(*) from orders where orders.cust_id = customers.cust_id) as 购买顾客ID

from customers order by cust_name；

完全限定列名

八，表连接

1，关系表

把信息分解为多个表，一类数据一个表，各表通过某些共同的值相互关联

2，等值连接

select vend_name，prod_price from Vendors v，products p where v.vend_id=p.vend_id；

完全限定

第一个表中第一行和第二个表中每一行配对

3，笛卡尔积：由没有联结条件的表关系返回的结果

4，内连接：基于两个表之间的相等测试

select * from Vendors v inner joinproducts p onv.vend_id=p.vend_id

三个表链接：

select cust_name，cust_contact from customer where cust_id

in(select order_num from orders where order_num

in(select order_num from orderItems where prod_id = ‘RGAN01’))；

等价于

select cust_name，cust_contact from customer c，orders o，orderItems d

where c.cust_id=o.cust_id and d.order_num=o.order_num and prod_id = ‘RGAN01’；

5，自连接

要与Jim Jones同一公司的所有顾客发送一封信

1，找出与Jim Jones的公司

2，找出公司内的顾客信息

子查询

select cust_id，cust_name，cust_contact from customers where

cust_name =(select cust_name from customers where cust_contact = ‘Jim Jones’)；

等价于

select cust_id，cust_name，cust_contact from customers c1，customers c2 where

c1.cust_name=c2.cust_name and c2.cust_contact = ‘Jim Iones’；

6，自然连接

排除多次出现的列，是每列只返回一次

select c.*，o.order_num，o.order_date from customers c，orders o where

c.cust_id = o.cust_id

通配符只对customers表进行全部取，对orders表只取一部分，以保证没有重复列

7，外连接

包含了在相关表中没有关联行的行

select c.cust_id，o.order_num from customers c left (outer) join orders o

on  c.cust_id = o.cust_id；

left outer join ：左链接，左边的表为主表链接右边的表(包含左边所有的值)

right outer join ： 右链接，右边的表为主表链接左边的表(包含右边所有的值)

可以通过改变 left outer join 两边的表，转换为右链接

full outer join ：全外连接(包含左右两边所有的值)

Access，MariaDB，MySQL，Open Office Base，SQLite不支持full outer join

也可以在 select 后添加聚集函数

8，注意事项

a，一般使用内连接，使用外连接也有效；

b，看相应的DBMs支持何种语法；

c，保证使用正确的连接条件；

d，提供连接条件，否则会出现笛卡尔积；

e，在一个连接中包含多个表，可以对每个连接采用不同的连接类型

今天客户提了一个小需求，希望我能提供一条sql语句，帮助他对数据中 _field 这个字段的值去重，并且保留其他字段的数据。第一反应是select distinct，但这种语句在对某个字段去重时，无法保留其他字段，所以select distinct不成立。因为用户对去重没有要求，字段值重复时保留任意一行就行，所以我想到当字段值重复时，选出对应主键最大的那条数据作为保留数据，这样可以实现用户的去重需求。但是用户的表中又没有主键，没办法，我们只好先使用窗口函数创建主键了。

因为平时喜欢用hive on spark写sql，所以sql语句使用中间表的形式来写，_field为去重字段，other_fields为原表table中_field外的其他字段

1.创建主键(存在主键则无需创建，窗口函数需要遍历所有行数据，数据量大时会很慢)

TEMP table1 = select row_number() over (order by _field)  as id,  _field, other_fields from table

2.选出每个_field对应的最大主键

TEMP table2 = select max(id) as max_id from table1 group by _field

3.找出选中的主键对应的原表数据

TEMP table3 = select _field, other_fields from table2 left join table on table2.max_id = table1.id

OUTPUT  table3

中间表写法看起来可能有些乱，对于mysql这种支持嵌套查询的数据库来说，写起来更好理解

id为主键ÿ