forked from netkiller/netkiller.github.io
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsql.html
More file actions
161 lines (138 loc) · 12.7 KB
/
Copy pathsql.html
File metadata and controls
161 lines (138 loc) · 12.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>第 6 章 SQL</title><link rel="stylesheet" type="text/css" href="/docbook.css" /><meta name="generator" content="DocBook XSL Stylesheets V1.78.1" /><link rel="home" href="index.html" title="Netkiller PostgreSQL 手札" /><link rel="up" href="index.html" title="Netkiller PostgreSQL 手札" /><link rel="prev" href="copy.html" title="5.2. copy" /><link rel="next" href="orderby.html" title="6.2. ORDER BY 排序" /></head><body><a xmlns="" href="http://netkiller.github.io/">Home</a> |
<a xmlns="" href="http://netkiller.github.io/">简体中文</a> |
<a xmlns="" href="http://netkiller.sourceforge.net/">繁体中文</a> |
<a xmlns="" href="/journal/index.html">杂文</a> |
<a xmlns="" href="/search.html">Search</a> |
<a xmlns="" href="http://netkiller-github-com.iteye.com/">ITEYE 博客</a> |
<a xmlns="" href="http://my.oschina.net/neochen/">OSChina 博客</a> |
<a xmlns="" href="https://www.facebook.com/bg7nyt">Facebook</a> |
<a xmlns="" href="http://cn.linkedin.com/in/netkiller/">Linkedin</a> |
<a xmlns="" href="mailto:[email protected]">Email</a><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">第 6 章 SQL</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="copy.html">上一页</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="orderby.html">下一页</a></td></tr></table><hr /><table width="100%" border="0"><tr><td align="left"><a href="http://netkiller.sourceforge.net/">Home</a> |
<a href="http://netkiller.github.com/">Mirror</a> |
<a href="/search.html">Search</a></td><td align="right"></td></tr></table></div><table xmlns=""><tr><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&repo=netkiller.github.com&type=watch&count=true&size=large" height="30" width="170" frameborder="0" scrolling="0" style="width:170px; height: 30px;" allowTransparency="true"></iframe></td><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&repo=netkiller.github.com&type=fork&count=true&size=large" height="30" width="170" frameborder="0" scrolling="0" style="width:170px; height: 30px;" allowTransparency="true"></iframe></td><td><iframe src="http://ghbtns.com/github-btn.html?user=netkiller&type=follow&count=true&size=large" height="30" width="240" frameborder="0" scrolling="0" style="width:240px; height: 30px;" allowTransparency="true"></iframe></td></tr></table><div class="chapter"><div class="titlepage"><div><div><h1 class="title"><a id="sql"></a>第 6 章 SQL</h1></div></div></div><div class="toc"><p><strong>目录</strong></p><dl class="toc"><dt><span class="section"><a href="sql.html#idp56132480">6.1. "::"数据转换</a></span></dt><dt><span class="section"><a href="orderby.html">6.2. ORDER BY 排序</a></span></dt><dt><span class="section"><a href="sql.recursive.html">6.3. 递归查询</a></span></dt><dt><span class="section"><a href="returning.html">6.4. returning</a></span></dt><dt><span class="section"><a href="sql.function.html">6.5. Function</a></span></dt><dd><dl><dt><span class="section"><a href="sql.function.html#generate_series">6.5.1. generate_series</a></span></dt><dt><span class="section"><a href="sql.function.html#datetime">6.5.2. 日期/时间</a></span></dt><dd><dl><dt><span class="section"><a href="sql.function.html#idp56170928">6.5.2.1. Date/Time Operators</a></span></dt><dt><span class="section"><a href="sql.function.html#idp56173088">6.5.2.2. 当前日期/时间</a></span></dt><dt><span class="section"><a href="sql.function.html#idp56176144">6.5.2.3. 时间计算</a></span></dt><dt><span class="section"><a href="sql.function.html#idp56177936">6.5.2.4. to_char() / to_date()</a></span></dt><dt><span class="section"><a href="sql.function.html#idp56180000">6.5.2.5. EXTRACT, date_part</a></span></dt><dt><span class="section"><a href="sql.function.html#idp56182576">6.5.2.6. date_trunc</a></span></dt><dt><span class="section"><a href="sql.function.html#idp56184208">6.5.2.7. 延迟执行</a></span></dt><dt><span class="section"><a href="sql.function.html#idp56185968">6.5.2.8. 时区</a></span></dt></dl></dd><dt><span class="section"><a href="sql.function.html#idp56187216">6.5.3. uuid</a></span></dt><dt><span class="section"><a href="sql.function.html#tablefunc">6.5.4. tablefunc</a></span></dt></dl></dd></dl></div>
<div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="idp56132480"></a>6.1. "::"数据转换</h2></div></div></div>
<pre class="screen">
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=#
</pre>
</div>
</div><div xmlns="" id="disqus_thread"></div><script xmlns="" type="text/javascript">
/* * * CONFIGURATION VARIABLES: EDIT BEFORE PASTING INTO YOUR WEBPAGE * * */
//if(document.domain == 'netkiller.github.com'){
var disqus_shortname = 'netkiller'; // required: replace example with your forum shortname
//}else{
//var disqus_shortname = 'neochan';
//}
/* * * DON'T EDIT BELOW THIS LINE * * */
(function() {
var dsq = document.createElement('script'); dsq.type = 'text/javascript'; dsq.async = true;
dsq.src = 'http://' + disqus_shortname + '.disqus.com/embed.js';
(document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
})();
</script><noscript xmlns="">Please enable JavaScript to view the <a href="http://disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript><a xmlns="" href="http://disqus.com" class="dsq-brlink">comments powered by <span class="logo-disqus">Disqus</span></a><br xmlns="" /><div xmlns="" id="clustrmaps-widget"></div><script xmlns="" type="text/javascript">var _clustrmaps = {'url' : 'http://netkiller.github.io', 'user' : 1107643, 'server' : '2', 'id' : 'clustrmaps-widget', 'version' : 1, 'date' : '2013-08-14', 'lang' : 'en', 'corners' : 'square' };(function (){ var s = document.createElement('script'); s.type = 'text/javascript'; s.async = true; s.src = 'http://www2.clustrmaps.com/counter/map.js'; var x = document.getElementsByTagName('script')[0]; x.parentNode.insertBefore(s, x);})();</script><noscript xmlns=""><a href="http://www2.clustrmaps.com/user/87410e6bb"><img src="http://www2.clustrmaps.com/stats/maps-no_clusters/netkiller.github.io-thumb.jpg" alt="Locations of visitors to this page" /></a></noscript><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="copy.html">上一页</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="orderby.html">下一页</a></td></tr><tr><td width="40%" align="left" valign="top">5.2. copy </td><td width="20%" align="center"><a accesskey="h" href="index.html">起始页</a></td><td width="40%" align="right" valign="top"> 6.2. ORDER BY 排序</td></tr></table></div><script xmlns="">
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','//www.google-analytics.com/analytics.js','ga');
ga('create', 'UA-11694057-1', 'auto');
ga('send', 'pageview');
</script><script xmlns="" type="text/javascript">
var _bdhmProtocol = (("https:" == document.location.protocol) ? " https://" : " http://");
document.write(unescape("%3Cscript src='" + _bdhmProtocol + "hm.baidu.com/h.js%3F997cd4a7320a82d72cb74d179118f697' type='text/javascript'%3E%3C/script%3E"));
</script><script xmlns="" type="text/javascript" src="/js/q.js"></script></body></html>