PostgreSQL 09 SCHEMA操作与几个常用函数-柚子小站

已经知道了Schema的含义, 现在就来看一下详细操作. 还有就是一些条件表达式和操作, 这些其实就是一种函数. 也是为后边函数来先行铺垫一下.

SCHEMA与Search_path
CREATE SCHEMA
ALTER SCHEMA
DROP SCHEMA
CASE 条件语句
COALESCE 函数
NULLIF 函数
CAST 函数

SCHEMA与Search_path

SCHEMA在PgSQL里, 是一个数据库里包含所有数据库对象的命名空间. PgSQL的每一个DATABASE之下, 第一层对象就是schema对象. 在标准的查询中, 给定一个数据库, 想要取得这个数据库中的对象, 标准的方法就是使用schema.object这种形式的引用. 如果不给出当前的schema, 而且也没有设置search path中默认的schema, 那么PgSQL将会使用每个数据库默认的schema, 名称叫做public. 在每次创建数据库的时候, PgSQL就会自动创建这个名叫public的schema.

search_path

如果经常操作一个数据库中的某个固定的schema, 可以设置search_path, 来看两个例子.

//进入一个默认的数据库后, 查找当前使用的schema, 由于到现在为止都还没有创建过schema, 所以就是默认的public.
SELECT current_schema();

current_schema
----------------
public
(1 row)

然后看一下search_path变量:

SHOW search_path;
-----------------
"$user", public
(1 row)

可以看到显示了两个结果, 解释如下:

这里显示出来的结果, 是PgSQL用于搜索数据库对象所用的所有schema的顺序, 也就是说先搜索$user, 再搜索public.
$user表示与当前同名的用户的变量, 是PgSQL首先搜索的名称, 比如现在都是用postgres用户登录的, 则PgSQL最先搜索的schema名称就是postgres
public就是指的每个数据库默认的public schema

首先是创建SCHEMA, 使用CREATE SCHEMA. 来创建一个schema叫做cony:

CREATE schema cony;

创建之后, 可以在PgAdmin和IDEA里都看到这个新的schema. 然后可以将search_path修改一下:

SET search_path TO cony,public;

show search_path;

之后可以看到修改成功. 注意现在cony排在了public的前边, 表明如果不显式使用schema名称, 则会优先操作cony. 尝试创建一个表:

CREATE TABLE staff(
    staff_id SERIAL PRIMARY KEY,
    first_name VARCHAR(45) NOT NULL,
    last_name VARCHAR(45) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE
);

这个表会创建在哪个schema下边了, 答案就是cony, 就是因为search_path的设置. 注意, 虽然public schema是默认创建的, 但并不是一个特殊的schema, 可以使用DROP来去掉. 另外schema也是有权限的, 我们对于自己创建的schema默认有权限, 如果要授权给别人:

//允许USAGE
GRANT USAGE ON SCHEMA schema_name TO user_name;
//允许在Schema中执行CREATE的权限, 即创建对象(并不一定是表)
GRANT CREATE ON SCHEMA schema_name TO user_name;

默认情况下, 所有用户对于public schema都有CREATE和USAGE权限, 这个要注意, 一般都会删除public schema或者收回对于public schema的权限.

CREATE SCHEMA

标准的创建语句是:

CREATE SCHEMA [IF NOT EXISTS] schema_name;

和创建很多其他对象都是一样的. 还可以针对某个用户创建schema:

CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION user_name;

PgSQL还允许一次性创建schema和schema下边所属的对象:

CREATE SCHEMA schema_name
    CREATE TABLE table_name1 (...)
    CREATE TABLE table_name2 (...)
    CREATE VIEW view_name1
        SELECT select_list FROM table_name1;

这可以一次性创建上述的两个表和一个视图. 创建之后如果想要查询所有schema的名称, 使用如下语句:

SELECT
    *
FROM
    pg_catalog.pg_namespace
ORDER BY
    nspname;

这条语句可以查找当前数据库中的所有命名空间, 其中就包括自己创建的schema.

ALTER SCHEMA

可以像修改其他对象名称一样来修改schema的名称:

ALTER SCHEMA schema_name RENAME TO new_name;

注意在修改了名称之后, 需要重新设置search_path, search_path不会自动跟着一起修改名称. 还可以修改所属的用户:

ALTER SCHEMA schema_name OWNER TO { new_owner | CURRENT_USER | SESSION_USER};

不过一般确定了schema后,不要轻易修改, 否则会导致显式使用schema名称的查询等全部失效.

DROP SCHEMA

删除一个schema, 会将其下所有的数据库对象一并删除. 标准的删除命令是:

DROP SCHEMA [IF EXISTS] schema_name [ CASCADE | RESTRICT ];

其中需要解释的就是CASCADE, 如果使用这个, 就会删除其下的所有对象. 如果仅仅想在schema为空才删除, 可以使用RESTRICT. 如果想一次性删除多个schema, 可以使用如下语句:

DROP SCHEMA [IF EXISTS] schema_name1 [,schema_name2,...] [CASCADE | RESTRICT];

SCHEMA的操作整体来说还是很简单的, 接下来是一些条件操作和一些函数.

CASE 条件语句

这个语句在之前的SQL语句中也看过, 相当于很多语言中的switch语句. 看到这里又想起了CSAPP, swtich是用跳转表来做的. PgSQL的switch语句语法是:

CASE
      WHEN condition_1  THEN result_1
      WHEN condition_2  THEN result_2
      [WHEN ...]
      [ELSE result_n]
END

其中的每个condition都返回一个布尔值, 如果所有的布尔值都是false, 则会执行ELSE后边的语句. 这个对一个值进行处理的时候, 可以很方便的把一些值进行分组, 比如:

SELECT (CASE
            WHEN
                film.length <= 60 THEN 1
            WHEN film.length <= 120 THEN 2
            ELSE 3
    END
           ) as length,
       film.title,
       film.length
FROM film

ORDER BY film.film_id;

当电影的长度不超过一个小时的时候, 这个值是1, 一个小时到两个小时之间, 是2, 再长就是3. 这个执行之后, 就将film.length这列按照条件变成了1,2,3三个值

COALESCE 函数

这里开始接触到一些简单的函数了. 这个函数的作用是返回第一个不是null的参数, 其接受的参数可以是无限多个. 如果所有的参数都是null, COALESCE返回null. 在SQL中, 记住不是二元操作true 和 false, 而是三元操作 true null false. 具体的关系之前说过了. 看几个例子:

//返回1
SELECT COALESCE(1, 2, 3);

//返回3
SELECT coalesce(null, null, 3);

NULLIF 函数

这个函数是PgSQL中非常常用的条件函数, 接受两个参数. 如果第一个参数和第二个参数相等, 返回null, 否则返回第一个参数.

SELECT
   NULLIF (1, 1); -- return NULL

SELECT
   NULLIF (1, 0); -- return 1

SELECT
   NULLIF ('A', 'B'); -- return A

NULLIF的作用有很大一部分是和COALESCE搭配使用. 比如我们有一个简单的表, 有三个属性id, title, excerpt. 其中title是一定有数据的, excerpt的情况就复杂一些, 可能是一个空串, 也可能是一个null. 如果直接选, 无法区别空串还是不是空串, 现在想无论是空串还是null, 都想生成一个值, 这样就表示是空白, 可以编写如下查询:

SELECT
    id,
    title,
    COALESCE (
            NULLIF (excerpt, ''),
            '$EMPTY

看这里的套路, 首先NULLIF判断excerpt字段和空串''是否相同, 如果相等, 返回null, 然后导致COALESCE返回特定字符串.

如果excerpt不等于空串, 就会返回excerpt. 此时COALESCE又会判断excerpt是不是null, 如果不是null, 则说明excerpt既不是空串也不是null ,是正常字符串, 就会返回excerpt. 如果excerpt是null, COALESCE就会返回特定的字符串.

这个套路果然够深.

CAST 函数


这个函数也非常常用, 是类型转换函数.

标准语法是:

CAST ( expression AS target_type );
其中的expression是一个表达式, 可以是一个常量, 一个关系的属性, 或者是一个生成一个值的任何表达式.

target_type是要转换成的PgSQL的数据类型.

针对CAST函数, PgSQL有特别的写法:
expression::type
也就是无需显式的写出CAST函数, 只需要将上边两部分用两个引号连接起来即可.
SELECT
    '100'::SMALLINT,
    '01-OCT-2015'::timestamp;

看几个常用的例子:

 	字符串转数字, 最常用, 比如SELECT CAST ('100' AS INTEGER), CAST ('10.2' AS DOUBLE);
 	字符串转日期, 可以直接转换ISO标准格式的日期, 也可以美式写法, 比如SELECT CAST ('2015-01-01' AS DATE), CAST ('01-OCT-2015' AS DATE);
 	字符串转TIMESTAMP, 也是ISO格式, 比如SELECT '2019-06-15 14:30:20'::timestamp;
 	字符串转INTERVAL, 这个转换可以用X 加时间单位表示, 比如SELECT '15 minute'::interval, '2 hour'::interval, '1 day'::interval, '2 week'::interval, '3 month'::interval;
 	字符串转布尔值, 这个有很多转换形式: SELECT CAST('true' AS BOOLEAN), CAST('false' as BOOLEAN), CAST('T' as BOOLEAN), CAST('F' as BOOLEAN);

可以结合很多其他的判断条件来进行类型转换, 非常方便, 比如很多时候可能存储了字符串形式的数字, 就可以用这个来转换, 时间转换也是非常方便的.

)
        )
FROM
    posts;

看这里的套路, 首先NULLIF判断excerpt字段和空串''是否相同, 如果相等, 返回null, 然后导致COALESCE返回特定字符串. 如果excerpt不等于空串, 就会返回excerpt. 此时COALESCE又会判断excerpt是不是null, 如果不是null, 则说明excerpt既不是空串也不是null ,是正常字符串, 就会返回excerpt. 如果excerpt是null, COALESCE就会返回特定的字符串. 这个套路果然够深.

CAST 函数

这个函数也非常常用, 是类型转换函数. 标准语法是:

其中的expression是一个表达式, 可以是一个常量, 一个关系的属性, 或者是一个生成一个值的任何表达式. target_type是要转换成的PgSQL的数据类型. 针对CAST函数, PgSQL有特别的写法:

也就是无需显式的写出CAST函数, 只需要将上边两部分用两个引号连接起来即可.

看几个常用的例子:

字符串转数字, 最常用, 比如SELECT CAST ('100' AS INTEGER), CAST ('10.2' AS DOUBLE);
字符串转日期, 可以直接转换ISO标准格式的日期, 也可以美式写法, 比如SELECT CAST ('2015-01-01' AS DATE), CAST ('01-OCT-2015' AS DATE);
字符串转TIMESTAMP, 也是ISO格式, 比如SELECT '2019-06-15 14:30:20'::timestamp;
字符串转INTERVAL, 这个转换可以用X 加时间单位表示, 比如SELECT '15 minute'::interval, '2 hour'::interval, '1 day'::interval, '2 week'::interval, '3 month'::interval;
字符串转布尔值, 这个有很多转换形式: SELECT CAST('true' AS BOOLEAN), CAST('false' as BOOLEAN), CAST('T' as BOOLEAN), CAST('F' as BOOLEAN);

可以结合很多其他的判断条件来进行类型转换, 非常方便, 比如很多时候可能存储了字符串形式的数字, 就可以用这个来转换, 时间转换也是非常方便的.