【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;

将两张表提取的东西上下合并,同时会去掉重复的记录 。

注意事项:

  1. 作为运算对象的记录的列数必须相同;
  2. 作为运算对象的记录中列的数据类型必须一致;
  3. 可以使用任何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基础教程》
药企,独角兽,苏州。团队长期招人,感兴趣的都可以发邮件聊聊:tiehan@sina.cn
个人公众号,比较懒,很少更新,可以在上面提问题,如果回复不及时,可发邮件给我: tiehan@sina.cn