4. 常用SQL

常用SQL¶

命名¶

标识符字符范围¶

SQL标识符和关键词必须以一个字母（a-z，也可以是带变音符的字母和非拉丁字母）或一个下划线（_）开始。后续字符可以是字母、下划线（_）、数字（0-9）或美元符号（$）。

注意根据SQL标准的字母规定，美元符号是不允许出现在标识符中的，因此它们的使用可能会降低应用的可移植性。SQL标准不会定义包含数字或者以下划线开头或结尾的关键词，因此这种形式的标识符不会与未来可能的标准扩展冲突。

标识符字符长度¶

系统中一个标识符的长度不能超过 NAMEDATALEN-1 字节，在命令中可以写超过此长度的标识符，但是它们会被截断。

默认情况下，NAMEDATALEN 的值为64，因此标识符的长度上限为63字节。如果这个限制有问题，可以在src/include/pg_config_manual.h中修改 NAMEDATALEN 常量。

标识符大小写¶

在PostgreSQL中，未引用的名称总是会折叠成小写。例如，标识符FOO、foo和"foo"在PostgreSQL中被认为是相同的，但是"Foo"和"FOO"与这三个不同，并且彼此不同。

(在PostgreSQL中，将不带引号的名称折叠为小写与SQL标准不兼容，SQL标准规定不带引号的名称应折叠为大写。因此根据标准，foo应等同于"FOO"而不是"foo"。如果您想编写可移植应用程序，建议您始终引用某个特定的名称，或者永远不要引用它。）

关键词和不被引号修饰的标识符是大小写不敏感的

--下面两条SQL是等价的
UPDATE MY_TABLE SET A = 5;

uPDaTE my_TabLE SeT a = 5;

用户管理¶

在UNIX平台中安装PostgreSQL之后，PostgreSQL会在UNIX系统中创建一个名为postgres的操作系统用户。PostgreSQL的默认用户名,密码和默认数据库也是postgres。

如果是包管理器安装，一般会自动创建这个用户，如果是源代码编译安装，一般也建议创建一个这样的用户。Linux中的postgresql进程通常是由操作系统的postgres用户来启动的。

当你连接到一个数据库时，你可以选择以何种PostgreSQL用户名进行连接的；如果你不选择，那么缺省就是你的当前操作系统的账号。

如果这样，那么总应该有一个与操作系统用户同名的PostgreSQL用户账号用于启动服务器，并且通常这个用户都有创建数据库的权限。

如果你不想以该用户身份登录，那么你也可以在任何地方声明一个-U选项以选择一个用于连接的PostgreSQL用户名。

PostgreSQL安装后默认用户名和数据库也是“postgres”，这个用户为超级管理员且默认没有密码。在安装PostgreSQL之后可以以默认用户登录，也可以创建新当用户名。

安装后，第一件事就是用命令行登陆后，并修改密码

# 在初始化数据库系统时有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。在Linux终端中，切换到操作系统的postgre用户
su postgres 

# 登陆到pg中: 主机，端口，用户名，密码，使用的数据库
psql  -h localhost -p 54321 -U postgres -w password -d postgres

# 修改超管用户的密码
ALTER USER postgres WITH PASSWORD 'Password@123';

官方建议是这样的：

在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将search_path设置为"$user"(即缺省模式为与用户名相同的模式)，这样，任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

不建议项目的表存放在public下：

- 一是数据安全；
- 二是表存放混乱；不利于后期维护等等

CREATE ROLE david;　　/*默认不带LOGIN属性*/
CREATE USER sandy WITH PASSWORD 'password';　　/*默认具有LOGIN属性*/
ALTER USER sandy WITH PASSWORD 'password';     /*修改用户，为其设置密码*/

-- 用户sandy可以登录，角色david不可以登录。

SELECT * from pg_roles;  -- 查看角色
SELECT * from pg_user;   -- 查看用户，角色david创建时没有分配login权限，所以没有创建用户


-- 当我们创建一个用户，用户默认具有什么权限？

在PostgreSQL里没有区分用户和角色的概念，"CREATE USER" 是 "CREATE ROLE"的别名

这两个命令几乎是完全相同的，唯一的区别是CREATE USER命令创建的用户默认带有LOGIN属性，而CREATE ROLE命令创建的用户默认不带LOGIN属性。

CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not

用户和角色在整个数据库实例中是全局的，在同一个实例中的不同数据库中，看到的用户都是相同的。

CREATE ROLE role_name;

PUBLIC是 PostgreSQL 数据库中一个特殊的角色组，在元数据表（pg_roles）中都查不到该角色，数据库中所创建的角色都可以理解为是PUBLIC角色组成员。

而且对PUBLIC权限的继承完全不受NOINHERIT的控制，一旦创建了一个拥有 login 权限的角色，它会立即继承 PUBLIC 角色组拥有的权限，此时如果想通过 revoke（比如 revoke connect on database）来回收的话不会成功，只能从 PUBLIC 组回收相关权限（比如 revoke connect on database from PUBLIC）。

数据库管理¶

数据库创建

CREATE DATABASE实际上是通过拷贝一个现有的数据库进行工作的。默认地它将拷贝名为template1的标准系统数据库。

所以该数据库是创建新数据库的"模板"。如果你给template1增加对象，这些对象将被拷贝到随后创建的用户数据库中。这样的行为允许节点对数据库中的标准套件进行修改。

比如，如果你把过程语言PL/Perl安装到template1里，那么你在创建用户数据库的时候它们就会自动可得，而不需要额外的动作。

PostgreSQL默认有template0、template1两个模板，创建数据库时不指定模板，则默认模板为template1。用户也可以指定其他的自定义模板创建数据库。

使用 template1 模板库建库时不可指定新的字符集和本地化collate属性
使用 template0 模板库建库时可指定字符集和本地化collate属性

-- 建库
CREATE DATABASE db_test  WITH OWNER fengzhao tablespace pg_default;

CREATE DATABASE db_test
 WITH  
    OWNER = stxz                     --属主，默认是default，表示当前执行sql的用户
    TEMPLATE = template0             --模板库，默认是template1
    ENCODING = UTF8                  --要在新数据库中使用的字符集编码。一般用utf8就够了
    LC_COLLATE = 'zh_CN.utf8'        --要在新数据库中使用的整理顺序 (LC_COLLATE)。这会影响应用于字符串的排序顺序。
    LC_CTYPE = 'zh_CN.utf8'          --要在新数据库中使用的字符分类 (LC_CTYPE)。这会影响字符的分类，例如小写、大写和数字。
    TABLESPACE = default             --表空间，默认是default
    ALLOW_CONNECTIONS = true         --是否允许连接到此数据库。如果为 false，则没有人可以连接到该数据库。默认值为 true，允许连接。
    CONNECTION LIMIT = -1            --连接数限制。-1（默认）表示没有限制。
    IS_TEMPLATE = 0                  --是否可为模板
;   

-- 如果报错 invalid LC_COLLATE locale name: "zh_CN.utf8"，那么表示要提前去Linux上设置一下locale-gen zh_CN.utf8，并重启pg生效

-- 查看所有数据库
SELECT *  FROM pg_database;

--

-----------------------------------------------------------------------------------------------------------------
-- 建库
-- CREATE DATABASE实际上是通过拷贝一个现有的数据库进行工作的。默认地它将拷贝名为template1的标准系统数据库。 
-- 所以该数据库是创建新数据库的"模板"。如果你给template1增加对象，这些对象将被拷贝到随后创建的用户数据库中。这样的行为允许节点对数据库中的标准套件进行修改。 
-- 比如，如果你把过程语言PL/Perl安装到template1里， 那么你在创建用户数据库的时候它们就会自动可得，而不需要额外的动作。

-- PostgreSQL默认有template0、template1两个模板，创建数据库时不指定模板，则默认模板为template1。用户也可以指定其他的自定义模板创建数据库。
-- 使用 template1 模板库建库时不可指定新的字符集和本地化collate属性
-- 使用 template0 模板库建库时可指定字符集和本地化collate属性

-- 建库指定owner，指定表空间，这时默认的模板就是template1
CREATE  db_test  WITH OWNER fengzhao tablespace pg_default;


-- LC_COLLATE（字符排序规则）默认en_US.utf8。不同的排序规则下，相同字符串的比较其结果可能是不同的。
-- LC_CTYPE（字符分类）字符集中的字符分类，用来区分字母、数字、及大小写等，支持的字符分类可以查询系统表 pg_collation。


-- 使用 initdb 初始化 PostgreSQL 数据库集群时选择了(默认继承自操作系统locale变量)。在创建数据库时可以重写它，因此可以使用多个数据库，每个数据库具有不同的字符集。
-- 在日常使用中，很少会用到指定数据库的字符集。因为PostgreSQL数据库服务端并不支持通常汉字字符集"GBK"、"GB18030",所以一般都用"UTF8"字符集来支持中文的。



--  查询字符集支持的LC_COLLATE和LC_CTYPE信息
SELECT pg_encoding_to_char(collencoding) AS encoding,collname,collcollate AS "LC_COLLATE",collctype AS "LC_CTYPE" FROM pg_collation;

-- 查询某个库的字符集
SELECT datname,datcollate,datctype FROM pg_database WHERE datname = 'test';

CREATE DATABASE db_test
 WITH  
    OWNER = stxz  
    TEMPLATE = template0 
    ENCODING = UTF8 
    LC_COLLATE = 'zh_CN.utf8'
    LC_CTYPE = 'zh_CN.utf8'
    TABLESPACE = default 
    ALLOW_CONNECTIONS = true 
    CONNECTION LIMIT = -1 
    IS_TEMPLATE = 0 
;            



GRANT CONNECT ON DATABASE  db_test TO fengzhao;



-- 建模式

CREATE SCHEMA  IF NOT EXISTS schema_name AUTHORIZATION username;
COMMENT ON SCHEMA schema_name is 'test';
ALTER SCHEMA schema_name OWNER TO fengzhao;


-----------------------------------------------------------------------------------------------------------------

-- 选择schema
SET schema 'temp';

-- 建表
CREATE TABLE db_test.dim_admin_area (
  rid SERIAL PRIMARY KEY,
  qh_base_id BIGINT,
  source_code TEXT,
  update_time TIMESTAMP,
  cur_code VARCHAR(50),
  cur_name VARCHAR(50),
  parent_code VARCHAR(50),
  country_code VARCHAR(50),
  country_name VARCHAR(50),
  province_code VARCHAR(50),
  province_name VARCHAR(50),
  city_code VARCHAR(50),
  city_name VARCHAR(50),
  area_code VARCHAR(50),
  area_name VARCHAR(50),
  street_code VARCHAR(50),
  street_name VARCHAR(50),
  community_code VARCHAR(50),
  community_name VARCHAR(50),
  cur_level INTEGER,
  cur_order INTEGER,
  uid TEXT,
  border_id VARCHAR(50),
  version VARCHAR(50),
  UNIQUE (cur_code),
  UNIQUE (qh_base_id)
);

COMMENT ON TABLE dim_admin_area IS '行政区划代码';
COMMENT ON COLUMN dim_admin_area.rid IS '行ID';
COMMENT ON COLUMN dim_admin_area.qh_base_id is  '对象id';
COMMENT ON COLUMN dim_admin_area.source_code is  '数据源代码';
COMMENT ON COLUMN dim_admin_area.cur_code is  '当前编码';
COMMENT ON COLUMN dim_admin_area.cur_name is  '当前名称';
COMMENT ON COLUMN dim_admin_area.parent_code is  '父级编码';
COMMENT ON COLUMN dim_admin_area.country_code is  '国家编码';
COMMENT ON COLUMN dim_admin_area.country_name is  '国家名称';
COMMENT ON COLUMN dim_admin_area.province_code is  '省(自治区/直辖市)编码';
COMMENT ON COLUMN dim_admin_area.province_name is  '省(自治区/直辖市)名称';
COMMENT ON COLUMN dim_admin_area.city_code is  '地(区/市/州/盟)编码';
COMMENT ON COLUMN dim_admin_area.city_name is  '地(区/市/州/盟)名称';
COMMENT ON COLUMN dim_admin_area.area_code is  '县(区/市/旗)编码';
COMMENT ON COLUMN dim_admin_area.area_name is  '县(区/市/旗)名称';
COMMENT ON COLUMN dim_admin_area.street_code is  '街道办事处编码';
COMMENT ON COLUMN dim_admin_area.street_name is  '街道办事处名称';
COMMENT ON COLUMN dim_admin_area.community_code is  '社区/社区居委会编码';
COMMENT ON COLUMN dim_admin_area.community_name is  '社区/社区居委会名称';
COMMENT ON COLUMN dim_admin_area.cur_level is  '编码等级';
COMMENT ON COLUMN dim_admin_area.cur_order is  '编码顺序';

-- 建表

CREATE TABLE IF NOT EXISTS "db_TEST"
(
  "id"               SERIAL       NOT NULL,
  "file_id"          varchar(255) NOT NULL,
  "file_name"        varchar(255) NOT NULL,
  "dr"               int2 DEFAULT 0,
    "PHONE"            int  DEFAULT 0,
    "is_admin"         boolean default false, 
   PRIMARY KEY ("id")
);


CREATE TABLE products (
    id SERIAL PRIMARY KEY,             -- ID    主键，默认值为自增数值类型，SERIAL 是一种简写方式，相当于 DEFAULT nextval('products_id_seq')
    no integer,                        -- 编号   未指定默认值，默认值将会是 null
    name text,                         -- 名称   未指定默认值，默认值将会是 null
    price numeric(6,2) DEFAULT 9.99 ,   -- 价格   默认值是 9.99
    created_at timestamp DEFAULT now() -- 创建时间   默认值为插入时间
);


CREATE TABLE products (
    no integer,
    name text,
    price numeric CHECK (price > 0)    --约束， 价格大于0 
    -- price numeric CONSTRAINT positive_price CHECK (price > 0) -- 可给约束起一个名字
    -- 检查约束是最通用的约束类型。可以使用其来指定某列满足一个布尔（真值）表达式。如：想指定产品价格必须是正数类型，
    -- 约束定义就像默认值定义一样紧跟数据类型之后。检查约束由CHECK关键字和一个括号表达式组成。约束定义与默认值定义的顺序没有要求。
);


CREATE TABLE products (
    no INTEGER,                                             -- 产品编号
    name TEXT,                                              -- 产品名称
    price NUMERIC CHECK (price > 0),                        -- 价格(约束:价格大于0)
    discounted_price numeric CHECK (discounted_price > 0),  -- 折扣价(约束:价格大于0)
    CHECK (price > discounted_price)                        -- 约束:(折扣价低于正常价)
    -- 列定义与这些约束定义可以按混合顺序出现。我们叫前两种约束为列约束，第三个为表约束。
);




-- 检查表达式计算为true或null均认为是满足条件的。所以下面的插入语句是可以通过的
INSERT INTO products (no, name, price, discounted_price)
    VALUES (1, 'apple', null, null);


-- 建表

CREATE TABLE employees( 
    employee_id INTEGER NOT NULL, 
    first_name CHARACTER VARYING(20),
    last_name CHARACTER VARYING(25) NOT NULL, 
    email CHARACTER VARYING(25) NOT NULL, 
    phone_number CHARACTER VARYING(20), 
    hire_date DATE NOT NULL, 
    salary NUMERIC(8,2), 
    commission_pct NUMERIC(2,2), 
    manager_id INTEGER,
    department_id INTEGER,
    CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id),
    CONSTRAINT emp_salary_min CHECK (salary > 0),
    CONSTRAINT emp_email_uk UNIQUE (email), 
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id)
    CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
) ;


-- employee_id：员工编号，整数类型，主键（通过表级约束为主键指定了名称emp_emp_id_pk）
-- first_name：员工名字，字符串
-- last_name：员工姓氏，字符串，不能为空
-- email：员工电子邮箱，字符串，不能为空，必须唯一（emp_email_uk）
-- phone_number：员工电话号码，字符串
-- hire_date：员工雇佣日期，日期类型，不能为空
-- salary：员工薪水，数字类型，必须大于零（emp_salary_min）
-- commission_pct：员工佣金百分比，数字类型
-- manager_id：经理编号，外键（通过外键 emp_manager_fk 引用员工表的员工编号）
-- department_id，部门编号，外键（通过外键 emp_dept_fk 引用部门表 departments 的编号department_id）


CREATE TABLE IF NOT EXISTS "DB_TEST"
(
  "id"               SERIAL       NOT NULL,
  "file_id"          varchar(255) NOT NULL,
  "file_name"        varchar(255) NOT NULL,
  "dr"               int2 DEFAULT 0,
    "phone"            int  DEFAULT 0,
    "PHONE"            int  DEFAULT 0,
    "is_admin"         boolean default false, 
   PRIMARY KEY ("id")
);


insert into "DB_TEST" values(1,'abc','xyz',1,1,1,true)
insert into "DB_TEST" values(2,'abc','xyz',1,1,2,true)

insert into "db_TEST" values(1,'关羽','张飞',1,1,true);
insert into "db_TEST" values(2,'刘备','赵云',1,1,true);





select * from "DB_TEST"
select * from "db_TEST"

select "phone", "PHONE" from "DB_TEST"



-----------------------------------------------------------------------------------------------------------------






select *  from pg_database;
select * from pg_user;
select * from pg_stat_activity;

系统字段¶

在 PostgreSQL 中，当我们创建一个数据表时，数据库会隐式增加几个系统字段。这些字段由系统进行维护，用户一般不会感知它们的存在。

create table test(col integer);

insert into test(col) values (1),(2),(3);

我们查询数据字典表 pg_attribute 来查看表结构。

select attname, attnum, atttypid::regtype 
    from pg_attribute
where attrelid = 'test'::regclass;

查询结果显示，表 test 中一共包含 7 个字段。PostgreSQL 为我们增加了 6 个额外的系统字段，它们的 attnum 属性都是负数。