PSQL Doc ...
来源:互联网 发布:手机数据备份怎么弄 编辑:程序博客网 时间:2024/05/24 05:39
内容结构
1 新用戶
2 sql查询语言环境,数据类型和函数,用户角色,性能调优
3 安装和服务端的管理
4 客户端程序的接口
5 服务端大可扩展性,包含用户自定义数据类型和函数.
6 SQL语句的参考信息,客户端和服务端的程序.
7 PostgreSQL开发者需要的信息
www.postgresql.org/docs/9.4/interactive/tutorial-advanced.html
1.新用户
PostgreSQL 所对象关系数据库.
有些特性:复杂查询,外键,触发器,可更新的视图,事物的完整性,多并发控制
PostgreSQL可以被任何没有特权的用户安装,非root帐号也能访问.
切换到 posgres 用户(未进入数据库中),创建数据库:
createdb db_name
createdb(默认创建 postgres 数据库)
dropdb db_name
访问数据库
psql db_name
dbname=# select version(); (查看版本号)
\h help
\q quit
2. SQL基本的使用
创建表
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);
可以使用大数据类型有 int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, and interval 等.
'--' 表示后面的内容被注释掉了.
删除表
DROP TABLE tablename;
插入数据
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
更易读的语句
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
插入的数据不需要使用特定的序列:
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
从文本框读取数据:
COPY weather FROM '/home/user/weather.txt';
查询表
SELECT * FROM weather;
查询精准的数据列:
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
查询使用简单的数学表达式:
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
筛选查询:
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
排序查询:
SELECT * FROM weather ORDER BY city;
多排序查询:
SELECT * FROM weather ORDER BY city, temp_lo;
排重性查询:
SELECT DISTINCT city FROM weather;
排重与排序查询:
SELECT DISTINCT city FROM weather ORDER BY city;
多表查询
SELECT * FROM weather, cities WHERE city = name;
精准而正确的查询语句:
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
#大致等于 SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
INNER JOIN 使用了内查询,但有时候我们更需要外查询 如 LEFT OUTER JOIN.
SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
#以 wether 表作为准则表,以 cities 作为辅助表,把符合条件的辅助表内容匹配到 wether 表中导出为结果
自连接,比如查询一个表中较小的两个数据和最大的两个数据:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;
使用别名查询:
SELECT * FROM weather w, cities c WHERE w.city = c.name;
函数查询
SELECT max(temp_lo) FROM weather;
还有其它的函数,如 avg (average), max (maximum) , min (minimum)
查询最大值的数据:
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
或者使用 group by : SELECT city, max(temp_lo) FROM weather GROUP BY city;
过滤输出结果,用have函数:
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;
使用 like 匹配内容:
SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%'(1) GROUP BY city HAVING max(temp_lo) < 40;
更新
UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';
删除
DELETE FROM weather WHERE city = 'Hayward';
1 新用戶
2 sql查询语言环境,数据类型和函数,用户角色,性能调优
3 安装和服务端的管理
4 客户端程序的接口
5 服务端大可扩展性,包含用户自定义数据类型和函数.
6 SQL语句的参考信息,客户端和服务端的程序.
7 PostgreSQL开发者需要的信息
www.postgresql.org/docs/9.4/interactive/tutorial-advanced.html
1.新用户
PostgreSQL 所对象关系数据库.
有些特性:复杂查询,外键,触发器,可更新的视图,事物的完整性,多并发控制
PostgreSQL可以被任何没有特权的用户安装,非root帐号也能访问.
切换到 posgres 用户(未进入数据库中),创建数据库:
createdb db_name
createdb(默认创建 postgres 数据库)
dropdb db_name
访问数据库
psql db_name
dbname=# select version(); (查看版本号)
\h help
\q quit
\l show databases
\c dbname #change current database
2. SQL基本的使用
创建表
CREATE TABLE weather (
city varchar(80),
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date
);
CREATE TABLE cities (
name varchar(80),
location point
);
可以使用大数据类型有 int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, and interval 等.
'--' 表示后面的内容被注释掉了.
删除表
DROP TABLE tablename;
插入数据
INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
更易读的语句
INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
插入的数据不需要使用特定的序列:
INSERT INTO weather (date, city, temp_hi, temp_lo)
VALUES ('1994-11-29', 'Hayward', 54, 37);
从文本框读取数据:
COPY weather FROM '/home/user/weather.txt';
查询表
SELECT * FROM weather;
查询精准的数据列:
SELECT city, temp_lo, temp_hi, prcp, date FROM weather;
查询使用简单的数学表达式:
SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
筛选查询:
SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0;
排序查询:
SELECT * FROM weather ORDER BY city;
多排序查询:
SELECT * FROM weather ORDER BY city, temp_lo;
排重性查询:
SELECT DISTINCT city FROM weather;
排重与排序查询:
SELECT DISTINCT city FROM weather ORDER BY city;
多表查询
SELECT * FROM weather, cities WHERE city = name;
精准而正确的查询语句:
SELECT weather.city, weather.temp_lo, weather.temp_hi,
weather.prcp, weather.date, cities.location
FROM weather, cities
WHERE cities.name = weather.city;
#大致等于 SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name);
INNER JOIN 使用了内查询,但有时候我们更需要外查询 如 LEFT OUTER JOIN.
SELECT * FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);
#以 wether 表作为准则表,以 cities 作为辅助表,把符合条件的辅助表内容匹配到 wether 表中导出为结果
自连接,比如查询一个表中较小的两个数据和最大的两个数据:
SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
W2.city, W2.temp_lo AS low, W2.temp_hi AS high
FROM weather W1, weather W2
WHERE W1.temp_lo < W2.temp_lo
AND W1.temp_hi > W2.temp_hi;
使用别名查询:
SELECT * FROM weather w, cities c WHERE w.city = c.name;
函数查询
SELECT max(temp_lo) FROM weather;
还有其它的函数,如 avg (average), max (maximum) , min (minimum)
查询最大值的数据:
SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
或者使用 group by : SELECT city, max(temp_lo) FROM weather GROUP BY city;
过滤输出结果,用have函数:
SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;
使用 like 匹配内容:
SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%'(1) GROUP BY city HAVING max(temp_lo) < 40;
更新
UPDATE weather SET temp_hi = temp_hi - 2, temp_lo = temp_lo - 2 WHERE date > '1994-11-28';
删除
DELETE FROM weather WHERE city = 'Hayward';
0 0
- PSQL Doc ...
- Psql 常用命令
- psql常用命令
- PSQL常用命令
- psql常用命令
- psql 常用命令
- psql 小结
- psql使用
- psql Linux
- psql语法
- psql常用命令
- PSQL RETURNING
- psql命令
- psql -W
- psql 常用命令
- doc
- doc
- DOC
- tp执行流程
- Ant打可执行jar包指南
- 【leetcode】Sort Colors
- 蛋疼的Layout 和 分辨率适配
- 解决"要执行请求的操作,WordPress需要访问您网页服务器的权限"
- PSQL Doc ...
- linux网络配置
- 2.29
- 通过httpClient获取重定向后的页面链接地址(应用与百度链接跳转获取原网页的链接地址)
- 【leetcode】Swap Nodes in Pairs
- #!/bin/sh與#!/bin/bash的區別
- 2.30
- 解决无法安装这个包PCLZIP_ERR_BAD_FORMAT (-10) : Unable to find End of Central Dir Record signature
- 作业3.22