第 7 章 SQL

简介:

目录

7.1. "::"数据转换
7.2. ORDER BY 排序
7.3. 递归查询
7.4. returning
7.5. Function
7.5.1. generate_series
7.5.2. 日期/时间
7.5.2.1. Date/Time Operators
7.5.2.2. 当前日期/时间
7.5.2.3. 时间计算
7.5.2.4. to_char() / to_date()
7.5.2.5. EXTRACT, date_part
7.5.2.6. date_trunc
7.5.2.7. 延迟执行
7.5.2.8. 时区
7.5.3. uuid
7.5.4. tablefunc

7.1. "::"数据转换

    	
3.3    “::”数据转换

PostgreSQL 数据之间的转换可以使用“::”操作符。
3.3.1   text to varchar

vperson 表gender字段为布尔型(boolean)在视图中要显示为true显示为“先生”,false显示为“女士”CASE WHEN 表达式应该是:
CASE WHEN p.gender = true THEN '先生' ELSE '女士' END as gender,
直接使用'先生', '女士' PostgreSQL认为' '中间的字符为text类型,请看下面:

postgres=# CREATE OR REPLACE VIEW vperson AS
postgres-#     SELECT p.uid,p.name,
postgres-#     CASE WHEN p.gender = true THEN '先生' ELSE '女士' END as gender,
postgres-#     p.nickname,p.mobile,p.tel,p.fax,p.email,p.province,p.city,p.addre
ss,p.postalcode
postgres-#     FROM "person" p
postgres-#     Order By p.uid;
CREATE VIEW
postgres=# \dv vperson
         List of relations
 Schema |  Name   | Type |  Owner
--------+---------+------+----------
 public | vperson | view | postgres
(1 row)

postgres=# \d person
                    Table "public.person"
   Column    |          Type          |      Modifiers
-------------+------------------------+----------------------
 uid         | integer                | not null default 0
 name        | character varying(20)  | not null
 gender      | boolean                | not null default 'F'
 nickname    | character varying(20)  |
 mobile      | character varying(13)  |
 tel         | character varying(20)  | not null
 fax         | character varying(20)  |
 email       | character varying(60)  |
 province    | character varying(10)  | not null
 city        | character varying(10)  | not null
 address     | character varying(255) | not null
 postalcode  | character varying(6)   | not null
 rate        | character varying(20)  | default '0'
 bank        | character varying(20)  | not null default ''
 bankaccount | character varying(20)  | not null default ''
Indexes: person_pkey primary key btree (uid)
Check constraints: "person_rate" ((((((rate = '0'::character varying) OR (rate = '1'::character varying)) OR (rate = '2'::character varying)) OR (rate = '3'::character varying)) OR (rate = '4'::character varying)) OR (rate = '5'::character varying))

postgres=#
postgres=# \d vperson
              View "public.vperson"
   Column   |          Type          | Modifiers
------------+------------------------+-----------
 uid        | integer                |
 name       | character varying(20)  |
 gender     | text                   |
 nickname   | character varying(20)  |
 mobile     | character varying(13)  |
 tel        | character varying(20)  |
 fax        | character varying(20)  |
 email      | character varying(60)  |
 province   | character varying(10)  |
 city       | character varying(10)  |
 address    | character varying(255) |
 postalcode | character varying(6)   |
View definition: SELECT p.uid, p.name, CASE WHEN (p.gender = true) THEN '先生'::
text ELSE '女士'::text END AS gender, p.nickname, p.mobile, p.tel, p.fax, p.emai
l, p.province, p.city, p.address, p.postalcode FROM person p ORDER BY p.uid;


使用“::”将test 转为varchar:
CASE WHEN p.gender = true THEN '先生'::varchar(2) ELSE '女士'::varchar(2) END as gender,
例:
CREATE OR REPLACE VIEW vperson AS
    SELECT p.uid,p.name,
           CASE WHEN p.gender = true THEN '先生'::varchar(2) ELSE '女士'::varchar(2) END as gender,
           p.nickname,p.mobile,p.tel,p.fax,p.email,p.province,p.city,p.address,p.postalcode
    FROM "person" p
    Order By p.uid;
postgres=# drop view vperson ;
DROP VIEW
postgres=# CREATE OR REPLACE VIEW vperson AS
postgres-#     SELECT p.uid,p.name,
postgres-#     CASE WHEN p.gender = true THEN '先生'::varchar(2) ELSE '女士'::varchar(2) END as gender,
postgres-#     p.nickname,p.mobile,p.tel,p.fax,p.email,p.province,p.city,p.address,p.postalcode
postgres-#     FROM "person" p
postgres-#     Order By p.uid;
CREATE VIEW
postgres=# \d vperson
              View "public.vperson"
   Column   |          Type          | Modifiers
------------+------------------------+-----------
 uid        | integer                |
 name       | character varying(20)  |
 gender     | character varying(2)   |
 nickname   | character varying(20)  |
 mobile     | character varying(13)  |
 tel        | character varying(20)  |
 fax        | character varying(20)  |
 email      | character varying(60)  |
 province   | character varying(10)  |
 city       | character varying(10)  |
 address    | character varying(255) |
 postalcode | character varying(6)   |
View definition: SELECT p.uid, p.name, CASE WHEN (p.gender = true) THEN ('先生'::character varying)::character varying(2) ELSE ('女士'::character varying)::character varying(2) END AS gender, p.nickname, p.mobile, p.tel, p.fax, p.email, p.province, p.city, p.address, p.postalcode FROM person p ORDER BY p.uid;

postgres=#

    	





原文出处:Netkiller 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。

目录
相关文章
|
4月前
|
SQL 存储 大数据
SQL技巧
【7月更文挑战第26天】SQL技巧
24 1
|
5月前
|
SQL Java 数据库连接
SQL中为什么不要使用1=1
本文探讨了在SQL查询中使用`1=1`的现象及其背后的原因与问题。开发人员有时使用`1=1`作为始终为真的条件来方便动态构建SQL语句,但这样做可能会带来性能问题,尽管现代数据库查询优化器可能能优化掉这种条件,但在复杂查询或特定系统中仍可能影响效率。此外,`1=1`还降低了代码的可读性和跨数据库的兼容性。建议使用更佳实践,如MyBatis的动态SQL标签或Entity Framework的函数式查询,以避免不必要的条件。代码质量的重要性在于每一行代码都应有其明确的目的,避免浪费计算资源。
|
6月前
|
SQL 数据库 索引
八、SQL-Limite
八、SQL-Limite
48 0
|
SQL 数据挖掘 Python
sql8&10&11&12,3+1
sql8&10&11&12,3+1
112 0
sql8&10&11&12,3+1
|
SQL 网络协议 Docker
sql审核
sql审核
387 0
|
存储 SQL NoSQL
SQL必知必会(一)
对于我们而言,数据库是一个以某种有组织的方式存储的数据集合。最简单的办法就是将数据库想象成一个文件柜。这个文件柜是一个存放数据的物理位置,不管数据是什么,也不管数据是如何组织的。 数据库(DataBase) 保存有组织数据的容器(通常为一个或一组文件)
|
SQL 存储 搜索推荐
几个SQL问题
几个SQL问题
134 0
|
关系型数据库