【4.2】postgresql基本命令
上一篇中已经介绍了postgresql的安装以及连接，这里我们将来更多的了解如何使用这个牛逼的工具。 （本版本的命令都是基于Postgresql语句）
一、表的创建
1.1 先建立数据库
linux命令行创建：
$ createdb database test; #创建一个数据库名为test的数据库
创建完成，可以查看创建是否成 功的数据库
$psql
postgres=# \l #查看所有数据库
1.2 然后来创建表格
create table <表名>
(列名1 数据类型 该列所需约束(Not NULL)
列名2 .. ..
..同理..
)
所有的列都必须指定数据类型；每一列不能存储跟这一列数据类型不一样的数据；
基本数据类型包括：
INTEGER:用来存储整数的列的数据类型，不能存储小数；
CHAR：存储字符串的列的数据类型；也可以通过CHAR(20)可以指定存储的字符串最大长 度；当达不到指定长度的时候，使用半角空格进行补足；注意表中存储的字符串是区分大小写 的
VARCHAR:指定字符串类型，他跟CHAR的 区别就在于及时没有达到指定的长度，也不会用
半角空格进行补足
DATA:存储时间的列
Not NULL表示该列必须有东西，如果没有的话，就是没有什么限制
创建好表格后，加上这一句：
primary key ( $列名 ) #这一列就是用来跟其他列区分的，值是唯一的
我们只能使用半角英文字母、数字、下划线作为数据库、表和列的名称；且开头必须以半角英文字母； 名称不能重复；
二、表的删除和更新(以创建的表shohin为例)
删除表
drop table 表名
添加列
alter table 表名 add column 列名 列的数据类型
eg: alter table shohin add column shohin_mei_kan VARCHAR(100);
删除列
alter table 表明 drop column 列名;
向表中插入数据
begin transaction;
insert into shohin values (‘’,’’,’’);
commit;
修改表的名字
alter table sohin rename to shohin;
三、表的查询
基本句式
select 列名1,列名2,列名3 from 表名;
#中间可以用逗号隔开，注意顺序
查询所有的列
select * from 表名
查询第一列，可以用来看到表的基本内容
select * from 表名 limit 1;
可以使用as来定义别
select 列名1 as 别名1，列名2 as 别字2 from 表名；
#别名可以使用汉语，但必须用双引号括起来 使用distinct删除重复行
select distinct 列名 from 表名;
#distinct 只放在第一个列名前面，即列名1
根据where来选择记录
select 列名 from 表名 where 条件表达式;
例如：select shohin_meri,shohin_bunrui from shoo-in where shoin_bunrui=‘衣服’;
ql中子句的顺序是固定的，不能随意更改
were紧跟在from后面
如果old_id中好几个元
select * from view_dbsnp where 'rs56636050'=any(old_id);
注释：
单行注释 “- -”
多行注释 用 “/*” “*/” 之间
三 算术运算符盒比较运算符
3.1 算术运算符
select shohin_mei ,hanbai_tanka,hanbai_tanka*2 as “hanhai_tanka_*2” from shohin;
运算符就是这样以行为单位执行的
包含NULL的运算结果肯定为NULL
3.2 较运算符
= 相等
<> 不相等
>= 大于等于
<= 小于等于
select shobin_mei,hanhai_tanka,shire_tanka from shoin where hanbai_tanka -shiire_tanka>=500;
字符串的数据原则是按照字典顺序来进行排序，不能用数字的大小顺序混淆
希望选取NULL记录时，需要在条件表达式中使用IS NULL 运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL。
select shohin_mei,shiire_tanka from shoo-in where shiire_tanka IS NOT NULL;
3.3 逻辑运算符
NOT的妙用
select shohin_meri,shohin_bunrui from shohin where NOT shoin_bunrui>1000’;
select shohin_meri,shohin_bunrui from shohin where shoin_bunrui<=1000’;
AND 和OR的妙用
select shohin_meri,shohin_bunrui from shohin where shoin_bunrui<=1000’ and shoin_bun=“衣服”;
select shohin_meri,shohin_bunrui from shohin where shoin_bunrui<=1000’ and （shoin_bun=“衣服” or shoin_bun=“裤子” )
AND优先级高于OR，所以适当时候必须用（）指定顺序
四、对表进行聚合查询
所谓的聚合就是将多行的结果合并为一行
计算表中数据的行数
select count(*) from 表名；
计算NULL以外的行数
select count(*),count(列名1) from 表名；
第一得到包含NULL的行数，第二个包含NULL之外的行数
select sum(hanbai_tanka) from shohin; #求和
select avg(hanbai_tanka) from shohin;
select max(hanbai_tanka) from shohin; #min是最小值
删除重复值后的行
select count(distinct hanbai_tanka) from shohin;
对表进行分组（group by）
select shohin_bunrui,count(*) from shohin group by shohin_bunrui;
根据shohin_bunrui分类后分别统计shohin_bunrui的行数
group by不能使用别名;
group by结果是随机的;
只用在select ,having,order by中使用聚合函数，不能在where中用。where只能指定记录（行）的条件，不能用于指定组的条件
为聚合结果指定条件
select shohin_bunrui,count(*) from shohin group by shohin_bunrui having count(*)=2;
having中使用的3种要素包括：常数、聚合函数、group by子句中指定的列名
where 指定行所对应的条件；
having 指定组所对应的条件
对查询结果进行排序
select shohin_bunrui from shohin order order by shohin_bunrui desc;
desc指定为降序，否则默认是升序
select shohin_meri,shohin_bunrui from shohin order order by shohin_meri,shohin_bunrui;
先以shohin_meri进行排序
NULL的排序
NULL会在结果额开头或结尾汇总显示
order 中容许用别名，这跟命令的执行顺序有关：
from where group by having select order by
order by子句中可以使用存在于表中，但不包含在select子句之中的列
order 中可以使用列的编号
select shohin_bunrui from shohin order order by 3 desc,1
#按照第3列降序，第一列升序的方式进行排序（不推荐用）
语句顺序
select from where group by having order by
先根据where进行过滤，然后再进行聚类处理，根据聚类的结果进行过滤
五、数据的更新
5.1 数据的插入
insert into 表名 （列1，列2，列3...）values (值1，值2，值3…);
插入多行值：
insert into 表名 （列1，列2，列3...）values (值1，值2，值3…), (值1，值2，值3…),(值1，值2，值3…),;
列清单可以省略
insert into 表名 values (值1，值2，值3…), (值1，值2，值3…),(值1，值2，值3…),;
插入NULL
insert into 表名 values (值1，NULL，值3…), (值1，值2，值3…),(值1，值2，值3…),;
如果表格的列在开头设置了约束条件默认值
create table <表名>
(列名1 数据类型 该列所需约束(Not NULL),
列名2 .. DEAFULT 10,
..同理..
)
在输入的时候
insert into 表名 values (值1，DEAFULT，值3…), (值1，值2，值3…),(值1，值2，值3…);
如果表格开头没有设置DEAFULT，就会出现NULL
从其他表中复制数据
create table copy_shohin
(列名1 数据类型 该列所需约束(Not NULL),
列名2 .. DEAFULT O,
..同理..
)
insert into copy_shohin (列名1，列名2) select 各种限定 from shohin
select后跟的语句跟常规的一样
5.2 数据的删除
drop table 将表完全删除
delete from 表名 … where..；
#跟select 不一样的地方在于不能使用group by,having by,order by三类子句
5.3 数据的更新
update 表名 set 列名=表达式 where 条件;
update shohin set hanbai_tanka=hanbai_tanka*10 where shohin_bunrui=“厨房用具”;
NULL的更新
update shohin set torokubi=NULL where shohin_id=‘008’;
可以将指定的值清空，但是仅仅限于未设置NOT NULL的约束条件的列
多行更新
update shohin set torokubi=NULL，shiire_tanka=shiire_tanka*10 where shohin_id=‘008’;
5.4 事务（transaction）
将一系列命令放在一起来执行
begin transaction:
语句1;
语句2;
commit;
ROLLBACK—取消处理
begin transaction:
语句1;
语句2;
rollback;
跟没处理一样，撤销了刚才的处理
ACID特性
- 原子性（atomicity）:事务结束时，其中包含的更新处理要么全部执行，要么完全不执行的特性
- 一致性（consistency）:事务中包含的处理要满足数据库提前设置的约束
- 隔离性（isolation）:如果没有提交，其他事务看不到新的记录
- 持久性：事务一旦结束，该点得数据状态将得已保存
六、复杂查询
6.1 视图
表中存储的实际数据，视图中保存的时从表中取出数据所使用的select语句。
create view 视图名称（列名1，列名2）as select 语句;
尽量避免在视图的基础上建视图
定义视图是不要使用order by 子句
视图满足如下条件：selcet语句未使用distinct, from子句中只有一张表， 未使用group by, 未使用having子句。这是因为视图盒表需要同时更新，通过聚合得到的视图没法进行更新。
create or replace rule insert_rule as on insert to shohinjim do instead insert into shohin values(new.shohin_id,…) #postergresql中视图初始化为只读，执行容许对视图的更新，同时视图的更新，表也会跟着一起变。
视图的删除
drop view 视图名字（列1，列2...）;
6.2 子查询
一次性的视图，在selec语句执行后就消失了
select ** from (select … as **) as **;
#本质上来说就内嵌了一个查询子查询不能返回多行结果
6.3 关联子查询
在组内进行比较时，使用关联子查询
select shohin_id,shohin_mei,hanbai_tanka
from shohin as s1
where hanhai_tanka>(select avg(hanbai_tanka)
from shohin as s2
where s1.shohin_bunrui=s2.shohin_bunrui
group by shohin_bunrui)
#感觉很吊的，子函数返回了多个值，后面用到再好好研究一下
七、函数、谓词、CASE表达式
7.1 函数
除了前面提到的聚合函数外。还有
abs() 绝对值
mod() 除法余数
round(对象数值，保留小数位数)
|| 字符串的拼接 #select str1,str2,str1||str2 as str_concat from samplestr;
length() 字符串的长度
lower() 小写转化
replace(对象字符串，需替换的部分，替换为) 字符串替换
substring(对象字符串 from 截取的起始位置 for 截取的字符数)
upper() 大小转化
日期函数
shop=# select current_TIME;
timetz
--------------------
23:33:08.272797+08
(1 row)
cast(转换前的值，转换后的值)
coalesce 将NULL转化为其他值
coalesce(数据1，数据2，数据3..)
7.2 谓词
前方一致
select * from shohin where srool LIKE ‘ddd%’;
%代表0字符以上的任意字符串，这个表示ddd开头的
中间一致查询
select * from shohin where srool LIKE ‘%ddd%’;
后方一致查询
select * from shohin where srool LIKE ‘%ddd’;
select * from shohin where srool LIKE ‘__ddd’;
_代表任意的一个字符串
范围查询（between）
select * from shohin where srool 100 between 1000’;
srool值在100和1000之间。
is NULL 、 is not NULL 判断是否为空 select * from shohin where srool in （100，200，300);
取出 srool值为100,200,300的；
反之select * from shohin where srool not in （100，200，300);
#in和 Not in 都不能提出NULL
7.3 CASE表达式
case when 判断表达式 then 表达式
when 判断表达式 then 表达式
when 判断表达式 then 表达式
.
.
else 表达式
end
例子：
select shohin_mei,
case when shohin_bunrui=“衣服” then ‘A:’||shohin_bunrui
when shohin_bunrui=“办公用品” then ‘B:’||shohin_bunrui
else NULL
end as abc_shohin_bunrui
from shohin;
#eles子句可以省略，会自动补齐;end不能省略，否则会报错。
case是一个表达式，可以书写在任意位置。
八、集合运算
8.1 表的加减法
select shohin_id,shohin_mei from shohin
union select shohin_id,shohin_mei from shohin2;
将两张表提取的东西上下合并，同时会去掉重复的记录 。
注意事项：
- 作为运算对象的记录的列数必须相同；
- 作为运算对象的记录中列的数据类型必须一致；
- 可以使用任何select 语句，但是order by子句只能在最后使用一次
案例：
select shohin_id,shohin_mei from shohin
union all select shohin_id,shohin_mei from shohin2; #不去掉重复的行
select shohin_id,shohin_mei from shohin
intersect select shohin_id,shohin_mei from shohin2; #选取公共部分
select shohin_id,shohin_mei from shohin
intersect all select shohin_id,shohin_mei from shohin2; #选取公共部分，保留重复的行
select shohin_id,shohin_mei from shohin
except select shohin_id,shohin_mei from shohin2; #出去shohin2提取的部分
8.2 联结（以列为单位对标进行联结）
内联结
select TS.tenpo_id,TS.tenpo_mei,TS.shohin_id,S.shohin_mei,S.hanhai_tanka from Tenposhohin as TS INNER JOIN shohin as S ON TS.shohin_id=S.shohin_id;
#注意对两个表重新的命名来建议相关索引。TS和S分别为两张表的名字。只显示共有的行
外联结
select TS.tenpo_id,TS.tenpo_mei,TS.shohin_id,S.shohin_mei,S.hanhai_tanka from Tenposhohin as TS RIGHT OUTER JOIN shohin as S ON TS.shohin_id=S.shohin_id;
以其中一张表作为主表，即使在副表中不存在的行也写入新的数据中，right指右边的为主表，left指的左边的为主表
三张表的联结
select TS.tenpo_id,TS.tenpo_mei,TS.shohin_id,S.shohin_mei,S.hanhai_tanka ZS.hanbai_tanka, ZS.zaiko_suryo from Tenposhohin as TS INNER JOIN shohin as S ON TS.shohin_id=S.shohin_id
INNER JOIN zaikoshohin as ZS ON TS.shohin_id =ZS.shohin_id where ZS.souko_id=‘S001’;
交叉联结
cross join(笛卡尔积) 这里不作过多解释
九 SQL高级处理
9.1 窗口函数（OLAP）
OLAP(online analytical processing )，对数据进行实时分析
窗口函数 over (partition by 列清单 order by 排序用得列清单)
#能够作为窗口函数的聚合函数（sum等）；rank,dense_rank,row_number等专用窗口函数。专用窗口函数无需参数，通常括号中都是空的。
select shohin_mei ,shohin_bunrui,hanbai_tanka, rank() over (partition by shohin_bunrui order by hanbai_tanka) as ranking from shohin;
有三条记录在并列第一时
rank 1位、1位、1位、4位
dense_rank 1位、1位、1位、2位
row_rank 1位、2位、3位、4位
窗口函数只能在select子句中
ps
部分例子没有以实际数据来展示，更多的理解参见 MICK著的《SQL基础教程》
参考资料
- MICK著的《SQL基础教程》
