4. 常用SQL

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

uPDaTE my_TabLE SeT a = 5;

# 切换到Linux的postgre用户
su postgres 
# 登陆: 主机，端口，用户名，密码，使用的数据库
psql  -h localhost -p 54321 -U postgres -w password -d postgres
# 修改超管用户的密码
ALTER USER postgres WITH PASSWORD 'Password@123';

CREATE ROLE david;　　/*默认不带LOGIN属性*/
CREATE USER sandy;　　/*默认具有LOGIN属性*/
ALTER USER sandy WITH PASSWORD 'password'; /*设置密码*/

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

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

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

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

-- 建库指定owner，指定表空间，这时默认的模板就是template1
create database 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 public AUTHORIZATION username;
comment on schema public is 'test';
alter schema public owner to fengzhao;


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

-- 选择schema
SET schema 'temp';


-- 建表

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;

create table test(col integer);

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

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