date类型_Chapter 01. 常见数据类型概述 Overview of Common Data Types

课程：DataCamp_Skill Track_SQL fundamentals【笔记】

---

Chapter 01. 常见数据类型概述 Overview of Common Data Types

了解常见数据类型（包括字符串，数字和数组）的属性和特征，以及如何检索有关数据库的信息。

1.1 欢迎！Welcome!

当你面对一个关系型数据库时，熟悉常见的数据类型以及属性、特性和相关操作是非常重要的，这门课将实现这个目标

本课用到的是一个经典的DVD租赁数据库，高度归一化，有各种典型的数据类型和自定义函数

具体来讲，本课会介绍常见的数据类型，时间和日期相关函数和操作，解析和处理文本以及用拓展来进行全文搜索

• 常见的文本数据类型有 CHAR、VARCHAR 和 TEXT，前两个是固定和可变字符长度（如电影标题 title），最后一个可认为是不限长度的（如电影描述 description）
• 常见的数字数据类型有 INT 和 DECIMAL，分别记录整数（如 id）和特定精度的浮点数（如付款额）
• 时间和日期数据类型包括 DATE、TIME、TIMESTAMP、INTERVAL
• 还有数组类型

很多时候我们执行操作前都要先确认字段的数据类型，PostgreSQL 中所有数据库对象的信息都储存在一个叫 INFROMATION_SCHEMA 的系统数据库中，所以，如果要查看 film 表中某三列的数据类型：

SELECT
    column_name,
    data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name in ('title','description','special_features')
    AND table_name ='film';

可以看到三个字段的类型分别为 character varying、text、ARRAY ，即前两个是文本而最后一个是数组

1.2 文本数据类型 Text data types

注意 PostgreSQL 里没有 STRING 这个数据类型

1.3 获取有关您的数据库的信息 Getting information about your database

如前所述，PostgreSQL 里有一个叫 INFROMATION_SCHEMA 的系统数据库保存了关于各种数据库对象的信息，其中有个 TABLES 表包含了数据库中各个表的属性信息，另有一个 COLUMNS 表包含了数据库中各表里各字段的属性信息

查询 public 数据库中表的属性信息

-- Select all columns from the TABLES system database
 SELECT * 
 FROM INFORMATION_SCHEMA.TABLES
 -- Filter by schema
 WHERE table_schema = 'public';

查询 actor 表中列的属性信息

-- Select all columns from the COLUMNS system database
 SELECT * 
 FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE table_name = 'actor';

1.4 确定数据类型 Determining data types

INFROMATION_SCHEMA.COLUMNS 表中包含了字段的各种属性信息，包括数据类型，浮点数类型的精度和字符串类型的长度，是否可为空等等

查询 customer 表的字段及字段数据类型

-- Get the column name and data type
SELECT
    column_name,
    data_type
-- From the system database information schema
FROM INFORMATION_SCHEMA.COLUMNS 
-- For the customer table
WHERE table_name = 'customer';

现在你有了确定现有数据库表中字段的数据类型的工具

1.5 日期和时间数据类型 Date and time data types

时间和日期数据类型包括 时间戳 TIMESTAMP、日期 DATE、时间 TIME、间隔 INTERVAL

• 时间戳 TIMESTAMP 是精确到微秒（1^-6s）的，可以记录非常精确的时间，也是最常用的一种时间日期格式，其中日期部分按照 ISO 8601 标准用短横线分割，例如：2019-03-26 01:05:17.93027+00(注意最后的 +00 代表时区，这是一个附带时区的时间戳，后面会讲) 又如 payment 表的 payment_date 就是 TIMESTAMP 类型，不过是精确到秒的：2005-05-25 11:30:37
• DATE 和 TIME 本质上可以看作 TIMESTAMP 的日期部分和时间，当只需要知道日期或只需要知道一天中的时间时会比较有用，如 customer 表的 create_date 就是 DATE 类型：2006-02-14
• 还有 INTERVAL 类型记录时间间隔，单位从 year 到 second 都有，常常用在时间日期的算数运算中，如，以租借日加三天作为预期归还日

SELECT rental_date + INTERVAL '3 days' as expected_return
FROM rental;

结果：
+---------------------+
| expected_return     |
|---------------------|
| 2005-05-27 22:53:30 |
+---------------------+

我们可以用之前讲过的方法查看某个时间日期列的具体时间日期类型，如，查看 rental 表的 rental_date 列的数据类型：

SELECT
    column_name,
    data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name in ('rental_date')
    AND table_name ='rental';

结果：
+-------------+-----------------------------+
| column_name | data_type                   |
|-------------|-----------------------------|
| rental_date | timestamp without time zone |
+-------------+-----------------------------+

可以看到是 TIMESTAMP 类型的，另外可以看到 PostgreSQL 可以在储存 TIMESTAMP 时选择附带或不附带时区（with/ without time zone），这个有时候会有用，但大部分时候，我们都用默认的不附带时区的时间戳。

1.6 日期和时间数据类型的属性 Properties of date and time data types

……

1.7 间隔数据类型 Interval data types

在 rental 表中，获得租借日 rental_date，（最晚）预期归还日 expected_return_date 和 实际归还日 return_date

通过比较后两者可以确定是否逾期

SELECT
    -- Select the rental and return dates
    rental_date,
    return_date,
    -- Calculate the expected_return_date
    rental_date + INTERVAL '3 days' AS expected_return_date
FROM rental;

1.8 使用数组 Working with ARRAY

PostgreSQL 中的数组和其它语言中的数组很相似，多维度、可变长度，其中的元素可以是各种原始数据类型对象

要用 SELECT 查询数据库，首先至少有一张表，且表里有若干行记录，所以我们先学习一下创建表和插入记录的语句：

CREATE TABLE my_first_table (
    first_column text,
    -- 创建表时要指明字段的数据类型
    second_column integer
);

INSERT INTO my_first_table
    (first_column, second_column) VALUES ('text value', 12);

例如，创建一个成绩表，后面两个字段均为数组

CREATE TABLE grades (
    student_id int,
    email text[][],
    /* 
    在数据类型后加上中括号表示数组，
    两个中括号表示二维数组，
    text[][]表示储存text值的二维数组
    */
    test_scores int[]
);  

INSERT INTO grades
    VALUES (1,
    '{{"work","work1@datacamp.com"},{"other","other1@datacamp.com"}}',
    -- 数组用单引号和大括号包裹
    -- 内部字符串用双引号
    '{92,85,96,88}' );

查询数组时内元素时索引的方法和其他语言类似，但注意 PostgreSQL 是从1而非0开始的

SELECT
    email[1][1] AS type,
    email[1][2] AS address,
    test_scores[1],
FROM grades;

也可以在 WHERE 中队数组内元素进行筛选

SELECT
    email[1][1] as type,
    email[1][2] as address,
    test_scores[1]
FROM grades
WHERE email[1][1] = 'work';

想通过检索整个数组是否包含某字段来筛选，可以用 ANY 函数或 @>（像鸟，哈哈）操作符

如，筛选 email 中含 ‘other’ 的记录：

SELECT
    email[2][1] as type,
    email[2][2] as address,
    test_scores[1]
FROM grades
WHERE 'other' = ANY (email);
/WHERE email @> ARRAY['other'];

1.9 访问数组中的数据 Accessing data in an ARRAY

选择 special_features 的第一个元素是 ‘Trailers’ 的电影记录

-- Select the title and special features column 
SELECT 
  title, 
  special_features 
FROM film
-- Use the array index of the special_features column
WHERE special_features[1] = 'Trailers';

1.10 用ANY搜索数组 Searching an ARRAY with ANY

通过使用 ANY 函数，可以检索数组中所有的值（而不是像前面一样检验数组中特定的某个位置的元素），结构如下：

WHERE 'search text' = ANY(array_name)
-- 等式，左边是检索的文本，要检索的列作为右边ANY函数内的参数

找出 special_features 里包含 ‘Trailers’ 的电影记录

SELECT
  title, 
  special_features 
FROM film 
-- Modify the query to use the ANY function 
WHERE 'Trailers' = ANY (special_features);

ANY 函数是一个你在检索数组类型数据时非常常用的灵活工具

1.11 用@>搜索数组 Searching an ARRAY with @>

可以用包含操作符 @> 来替代 ANY 函数来实现类似的功能

语法结构如下

WHERE array_name @> ARRAY['search text'] :: type[]
-- 最后那个强制格式转行是什么意思？

​ 找出 special_features 里包含 ‘Deleted Scenes’ 的电影记录

SELECT 
  title, 
  special_features 
FROM film 
-- Filter where special_features contains 'Deleted Scenes'
WHERE special_features @> ARRAY['Deleted Scenes'];

做得好！既然您已经了解了常见PostgreSQL数据类型的属性和特性，我们将开始深入研究内置函数以在您的查询中操纵和转换这些数据类型，我们从日期和时间函数以及运算符开始。

---

课程：DataCamp_Skill Track_SQL fundamentals【笔记】