|
如何在psql中查看帮助？
?查看以开头的命令的帮助
h查看sql语句的帮助，例如：
- book=#hCREATEINDEX
- Command:CREATEINDEX
- Description:defineanewindex
- Syntax:
- CREATE[UNIQUE]INDEX[CONCURRENTLY][name]ONtable[USINGmethod]
- ({column|(expression)}[opclass][ASC|DESC][NULLS{FIRST|...[WITH(storage_parameter=value[,...])]
- [TABLESPACEtablespace]
- [WHEREpredicate]
可查看某一sql语句的用法。
postgresql是一款关系型数据库，table对应relation，column对应attribute，row对应tuble。
postgresql是一个设计在前的数据库，首先应该设计模式（schema），然后插入符合模式定义的数据。
创建表（包含名称以及一系列具有类型的列和一些可选的约束信息）：
- CREATETABLEcountries(country_codechar(2)PRIMARYKEY,country_nametextUNIQUE
- );
- CREATETABLEevents(
- event_idSERIALPRIMARYKEY,
- titlevarchar(255),
- startstimestamp,
- endstimestamp,
- venue_idint,
- FOREIGNKEYvenue_id
- REFERENCESvenues(venue_id)
- );
插入数据：
- INSERTINTOvenues(name,postal_code,country_code)
- VALUES('VoodooDonuts','97205','us')RETURNINGvenue_id;
RETURNING:插入数据时返回一些列的值。
索引：一种数据结构，其目的是为了避免查询时做全表扫描
建索引的时候，最好是所建索引的列的值尽量不会有大量重复，这样如果我们建B树索引的时候，查询的效率就比较高
为表添加列：
- ALTERTABLEvenuesADDactivebooleandefaulttrue;
window函数：
与group by很类似，但是group by与集合函数结合使用，会按group by字段输出一行，而window 函数，则每一行都会输出一条记录，相当于把集合函数在某一列上执行，每一行都会拷贝集合函数的计算结果：
- ELECTtitle,count(*)OVER(PARTITIONBYvenue_id)FROMevents;
存储过程：一种过程式编程方式，通常是创建一些过程或函数，可以通过存储过程来大幅度的提高程序的执行效率，特别是有大量的统计的时候，因为这时候我们是通过存储过程直接返回结果，而不是返回大量的行。但使用需谨慎，因为可能会降低代码的可读性、增加开发的复杂度。
触发器：在某个事件（insert或update）发生时，自动触发存储过程。
视图：可以认为是查询的别名，是规则（RULE）的一种
- CREATEORREPLACEVIEWholidaysAS
- SELECTevent_idASholiday_id,titleASname,startsASdate,colorsFROMevents
- WHEREtitleLIKE'%Day%'ANDvenue_idISNULL;
规则（RULE）：如何修改查询解析树的一种描述（一个客户端的sql要依次经过解析器、重写器、规划器、执行器的过程才能返回结果，RULE正式在rewrite的阶段起作用，可以在这个阶段对query tree进行重写）。例如创建一个基于holidays这个 view的一个规则，让我们可以针对view进行写入：
- CREATERULEupdate_holidaysASONUPDATETOholidaysDOINSTEADUPDATEevents
- SETtitle=NEW.name,
- starts=NEW.date,
- colors=NEW.colorsWHEREtitle=OLD.name;
- SELECTextract(yearfromstarts)asyear,extract(monthfromstarts)asmonth,count(*)
- FROMevents
- GROUPBYyear,month;
extract函数：返回timestamp或date中的子域。
- CREATETEMPORARYTABLEmonth_count(monthINT);
- INSERTINTOmonth_countVALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
- SELECT*FROMcrosstab(
- 'SELECTextract(yearfromstarts)asyear,
- extract(monthfromstarts)asmonth,count(*)FROMevents
- GROUPBYyear,month',
- 'SELECT*FROMmonth_count'
- )AS(
- yearint,
- janint,febint,marint,aprint,mayint,junint,julint,augint,sepint,octint,novint,decint
- )ORDERBYYEAR;
crosstab函数：输入是两个集合，第一个集合包含三个域：rowid、category、value，第二个集合是我们的“轴”——即我们以哪些值作为统计的刻度，一般是category里面的所有可能的值。有一点需要注意的是：AS后面的一定要有，不然它不知道我们的数据类型，以及列名是什么
上面使用临时表的方式很不好，可以使用如下的方式代替：
- selectgenerate_series(1,12)
- ;
再看一个例子：
- SELECT*FROMcrosstab(
- 'SELECTextract(weekfromstarts)asweek,
- extract(dowfromstarts)asweekday,count(*)FROMevents
- GROUPBYweek,weekday',
- 'SELECTgenerate_series(0,6)'
- )AS(
- weekint,
- sunint,monint,tueint,wedint,thuint,friint,satint
- )ORDERBYweek;
模糊查询：
- SELECTtitleFROMmoviesWHEREtitleILIKE'stardust%';
注：ILIKE大小写不敏感
使用regex进行更加强大的模糊匹配查询：
- SELECTCOUNT(*)FROMmoviesWHEREtitle!~*'^the.*';
!(不匹配)，*（大小写不敏感），~（正则匹配）
全文检索：
- SELECTtitle
- FROMmovies
- WHEREtitle@@'night&day';
原理：postgresql会把查找的域按照分词的结果存储成vector，然后针对此vector做查询，例如：
- SELECTtitle
- FROMmovies
- WHEREto_tsvector(title)@@to_tsquery('english','night&day');
这与上面一致。
多维超立方体查询：此种查询一般可用于推荐用户可能喜爱的东西，如针对电影的搜索，事先对电影分类（一个电影可能属于多个类别，这个类别用一个多维向量表示），然后我们可以算出与这部电影类别相近的类别（其实就是多维空间中的距离计算问题），推荐给用户，例如：
- SELECT*,
- cube_distance(genre,'(0,7,10,0)')dist
- FROMmoviesORDERBYdist;
找出所有电影与某一部电影的距离，按照距离排序，这又一个缺点：需要针对全表扫描，性能可能会比较差。再看下面一个：
- SELECTtitle,cube_distance(genre,0)')distFROMmovies
- WHEREcube_enlarge('(0,0)'::cube,5,18)@>genreORDERBYdist;
找出与某部电影的距离为5个单位的所有电影，这就缩小了查询的范围，相当于我们在地图上查找与某地相近200KM一样，我们不需要扫描全球，这大大缩小了查询范围，提高了查询的性能。
什么时候考虑使用其它数据库（非关系型）：
水平扩展、对读写速度有更高的要求、数据库制作大数据的存储。 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
