PostgreSQL和mysql语法区别详解通俗易懂

朴灿烈づ我的快乐病毒、 2024-03-16 21:01 20阅读 0赞

**目录**

0.PostgreSQL的数据类型:

0.1.数值数据类型

0.2.字符串数据类型

0.3.日期/时间数据类型

0.4.布尔类型

0.5.货币类型

0.6.几何类型

1.PostgreSQL创建表

1.1.建表之前，创建自增序列

1.2.创建表，设置id为自增序列

2.夸表数据复制

3.分页处理

3.1.PostgreSQL两种分页方法查询

3.2.mysql 的分页

4.函数使用

5.数据类型转化（针对于PostgreSQL）

6.字段拼接

7.获取当前时间

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

### 0.PostgreSQL的数据类型: ###

#### 0.1.**数值数据类型** ####

<table style="width:600px;"> 
 <tbody> 
  <tr> 
   <th style="width:82px;">名称</th> 
   <th style="width:166px;">描述</th> 
   <th>存储大小</th> 
   <th>范围</th> 
  </tr> 
  <tr> 
   <td style="width:82px;"> <p>smallint</p> </td> 
   <td style="width:166px;">存储整数，小范围</td> 
   <td>2字节</td> 
   <td>-32768 至 +32767</td> 
  </tr> 
  <tr> 
   <td style="width:82px;">integer</td> 
   <td style="width:166px;">存储整数。使用这个类型可存储典型的整数</td> 
   <td>4字节</td> 
   <td>-2147483648 至 +2147483647</td> 
  </tr> 
  <tr> 
   <td style="width:82px;">bigint</td> 
   <td style="width:166px;">存储整数，大范围。</td> 
   <td>8字节</td> 
   <td>-9223372036854775808 至 9223372036854775807</td> 
  </tr> 
  <tr> 
   <td style="width:82px;"> <p>decimal</p> <p>[desɪml]</p> </td> 
   <td style="width:166px;">用户指定的精度，精确</td> 
   <td>变量</td> 
   <td>小数点前最多为131072个数字; 小数点后最多为16383个数字</td> 
  </tr> 
  <tr> 
   <td style="width:82px;"> <p>numeric</p> <p>[nuˈmɛrɪk]</p> </td> 
   <td style="width:166px;">用户指定的精度，精确</td> 
   <td>变量</td> 
   <td>小数点前最多为131072个数字; 小数点后最多为16383个数字。</td> 
  </tr> 
  <tr> 
   <td style="width:82px;">real</td> 
   <td style="width:166px;">可变精度，不精确</td> 
   <td>4字节</td> 
   <td>6位数字精度</td> 
  </tr> 
  <tr> 
   <td style="width:82px;">double</td> 
   <td style="width:166px;">可变精度，不精确</td> 
   <td>8字节</td> 
   <td>15位数字精度</td> 
  </tr> 
  <tr> 
   <td style="width:82px;"> <p>serial</p> <p>[ˈsɪəriəl]</p> </td> 
   <td style="width:166px;">自动递增整数</td> 
   <td>4字节</td> 
   <td>1 至 2147483647</td> 
  </tr> 
  <tr> 
   <td style="width:82px;"> <p>big serial</p> </td> 
   <td style="width:166px;">大的自动递增整数</td> 
   <td>8字节</td> 
   <td>1 至 9223372036854775807</td> 
  </tr> 
 </tbody> 
</table>

#### 0.2.字符串数据类型 ####

<table style="width:600px;"> 
 <tbody> 
  <tr> 
   <th>数据类型</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>char(size)</td> 
   <td>这里<code>size</code>是要存储的字符数。固定长度字符串，右边的空格填充到相等大小的字符。</td> 
  </tr> 
  <tr> 
   <td> <p>character(size)</p> <p>[ˈkærəktər]</p> </td> 
   <td>这里<code>size</code>是要存储的字符数。 固定长度字符串。 右边的空格填充到相等大小的字符</td> 
  </tr> 
  <tr> 
   <td>varchar(size)</td> 
   <td>这里<code>size</code>是要存储的字符数。 可变长度字符串。</td> 
  </tr> 
  <tr> 
   <td>character varying(size)</td> 
   <td>这里<code>size</code>是要存储的字符数。 可变长度字符串。</td> 
  </tr> 
  <tr> 
   <td>text</td> 
   <td>可变长度字符串</td> 
  </tr> 
 </tbody> 
</table>

#### 0.3.日期/时间数据类型 ####

<table style="width:600px;"> 
 <tbody> 
  <tr> 
   <th>名称</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>timestamp [ (p) ] [不带时区 ]</td> 
   <td>日期和时间(无时区)</td> 
  </tr> 
  <tr> 
   <td>timestamp [ (p) ]带时区</td> 
   <td>包括日期和时间，带时区</td> 
  </tr> 
  <tr> 
   <td>date</td> 
   <td>日期(没有时间)</td> 
  </tr> 
  <tr> 
   <td>time [ (p) ] [ 不带时区 ]</td> 
   <td>时间(无日期)</td> 
  </tr> 
  <tr> 
   <td>time [ (p) ] 带时区</td> 
   <td>仅限时间，带时区</td> 
  </tr> 
 </tbody> 
</table>

#### 0.4.布尔类型 ####

<table style="width:600px;"> 
 <tbody> 
  <tr> 
   <th>名称</th> 
   <th>描述</th> 
   <th>存储大小</th> 
  </tr> 
  <tr> 
   <td>boolean</td> 
   <td>它指定<code>true</code>或<code>false</code>的状态。</td> 
   <td>1字节</td> 
  </tr> 
 </tbody> 
</table>

#### 0.5.货币类型 ####

<table style="width:600px;"> 
 <tbody> 
  <tr> 
   <th>名称</th> 
   <th>描述</th> 
   <th>存储大小</th> 
   <th>范围</th> 
  </tr> 
  <tr> 
   <td>money</td> 
   <td>货币金额</td> 
   <td>8字节</td> 
   <td>-92233720368547758.08 至 +92233720368547758.07</td> 
  </tr> 
 </tbody> 
</table>

#### 0.6.几何类型 ####

<table style="width:600px;"> 
 <tbody> 
  <tr> 
   <th>名称</th> 
   <th>存储大小</th> 
   <th>表示</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>point</td> 
   <td>16字节</td> 
   <td>在一个平面上的点</td> 
   <td>(x,y)</td> 
  </tr> 
  <tr> 
   <td>line</td> 
   <td>32字节</td> 
   <td>无限线(未完全实现)</td> 
   <td>((x1,y1),(x2,y2))</td> 
  </tr> 
  <tr> 
   <td>lseg</td> 
   <td>32字节</td> 
   <td>有限线段</td> 
   <td>((x1,y1),(x2,y2))</td> 
  </tr> 
  <tr> 
   <td>box</td> 
   <td>32字节</td> 
   <td>矩形框</td> 
   <td>((x1,y1),(x2,y2))</td> 
  </tr> 
  <tr> 
   <td>path</td> 
   <td>16+16n字节</td> 
   <td>封闭路径(类似于多边形)</td> 
   <td>((x1,y1),…)</td> 
  </tr> 
  <tr> 
   <td>polygon</td> 
   <td>40+16n字节</td> 
   <td>多边形(类似于封闭路径)</td> 
   <td>((x1,y1),…)</td> 
  </tr> 
  <tr> 
   <td>circle</td> 
   <td>24字节</td> 
   <td>圆</td> 
   <td><code>&lt;(x，y)，r&gt;</code>(中心点和半径)</td> 
  </tr> 
 </tbody> 
</table>

### 1.PostgreSQL创建表 ###

#### 1.1.建表之前，创建自增序列 ####

创建自增序列

--创建序列
    CREATE SEQUENCE "xiang_base"."cloud_new_id_seq" 
    INCREMENT 1
    MINVALUE  1
    MAXVALUE 9223372036854775807
    START 1
    CACHE 1;
    
    --设置丛多少开始自增
    SELECT setval('"xiang_base"."cloud_new_id_seq"', 1, true);
    
    --给序列指定字段
    ALTER SEQUENCE "xiang_base"."cloud_new_id_seq" OWNED BY "xiang_base"."cloud_news"."id";
    
    --给序列指定所有人
    ALTER SEQUENCE "xiang_base"."cloud_new_id_seq" OWNER TO "cloud_admin";

#### 1.2.创建表，设置id为自增序列 ####

设置自增序列：nextval('xiang\_base.cloud\_new\_id\_seq'::regclass)

CREATE TABLE "xiang_base"."cloud_news" (
        "id" int8 NOT NULL DEFAULT nextval('xiang_base.cloud_new_id_seq'::regclass),
        "title" TEXT COLLATE "pg_catalog"."default",
        "news_content" TEXT COLLATE "pg_catalog"."default",
        "news_content_m" TEXT COLLATE "pg_catalog"."default",
        "pic_path" VARCHAR ( 256 ) COLLATE "pg_catalog"."default",
        "intro" TEXT COLLATE "pg_catalog"."default",
        "is_deleted" bool NOT NULL DEFAULT FALSE,
        "creator" VARCHAR ( 32 ) COLLATE "pg_catalog"."default",
        "updater" VARCHAR ( 32 ) COLLATE "pg_catalog"."default",
        "create_time" TIMESTAMP ( 3 ) DEFAULT now( ),
        "update_time" TIMESTAMP ( 3 ) DEFAULT now( ),
        "description" TEXT COLLATE "pg_catalog"."default",
        "keywords" TEXT COLLATE "pg_catalog"."default",
        CONSTRAINT "cloud_news_pkey1" PRIMARY KEY ( "id" ) 
    );
    ALTER TABLE "xiang_base"."cloud_news" OWNER TO "cloud_admin";
    COMMENT ON COLUMN "xiang_base"."cloud_news"."title" IS '新闻标题';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."news_content" IS '新闻内容';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."news_content_m" IS '新闻移动端内容';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."pic_path" IS '新闻图片';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."intro" IS '介绍';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."is_deleted" IS '删除标志:FALSE:未删除,TRUE:已删除';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."creator" IS '创建人';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."updater" IS '更新人';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."create_time" IS '创建时间';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."update_time" IS '更新时间';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."description" IS '描述';
    COMMENT ON COLUMN "xiang_base"."cloud_news"."keywords" IS '关键词';

### 2.夸表数据复制 ###

--从一张表复制数据到另一张表
    INSERT INTO xiang_base.cloud_news ( ID, title, news_content, news_content_m, pic_path, intro, is_deleted, creator, updater, create_time, update_time ) SELECT ID
    :: int8,
    title,
    news_content,
    news_content_m,
    pic_path,
    intro,
    is_deleted,
    creator,
    updater,
    create_time,
    update_time 
    FROM
        xiang_base.cloud_news_bak_20221214_1;

### 3.分页处理 ###

#### 3.1.PostgreSQL两种分页方法查询 ####

在3000W数据的时候,建议使用第一种

--第一种
    SELECT * FROM test_table WHERE i_id>0 limit 100;

--第二种 offset从0开始，offset 0表示从第一行开始获取。
    SELECT * FROM test_table  limit 100 OFFSET 0;

#### 3.2.mysql 的分页 ####

SELECT * FROM test_table  limit 100, 10;

### 4.函数使用 ###

--时间转字符串
    
    --MySQL: 
    date_format(a.tag_create_date,‘%Y-%m-%d %H:%i:%s’)
    
    --PostgreSQL: 
    to_char(a.tag_create_date,‘yyyy-mm-dd HH:MM:SS’)

--字符串转时间：
    
    --MySQL: 
    date_format(a.tag_create_date,‘%Y-%m-%d %H:%i:%s’)
    
    --PostgreSQL: 
    to_date(a.tag_create_date,‘yyyy-mm-dd HH:MM:SS’)

--IFNULL()函数
    
    --MySQL: 
    IFNULL(a.idm,‘’)
    
    --PostgreSQL: 
    COALESCE(a.id,‘’)

-- sysdate()函数
    
    MySQL: SELECT sysdate()
    
    PostgreSQL: SELECT now()

-- find_in_set()函数（允许在逗号分隔的字符串列表中查找指定字符串的位置）
    
    MySQL: SELECT t.dept_id FROM sys_dept t WHERE find_in_set(‘100’, ancestors)
    
    PostgreSQL: SELECT t.dept_id FROM sys_dept t WHERE ‘100’ = ANY (string_to_array(ancestors, ‘,’))

-- group_concat()函数
    
    MySQL: select a.name,group_concat(distinct city)from user_city a group by a.name;
    
    PostgreSQL: select a.name,array_to_string(array_agg(distinct a.city),‘,’)from user_city a group by a.name;

-- LIMIT
    
    MySQL: select id,name from hospital.ods_user_basic limit 10,2;
    
    PostgreSQL: select id,name from hospital.ods_user_basic limit 10 offset 2;

-- DISTINCT
    
    MYSQL: select DISTINCT b.id from hospital.ods_user_basic as b
    
    PostgreSQL: select DISTINCT ON (b.id) b.* from hospital.ods_user_basic as b

-- MyBatis-Plus整合MySQL、PostgreSQL，LIKE使用
    -- LIKE是一般用法，ILIKE匹配时则不区分字符串的大小写
    
    -- MySQL：
    	<select id="checkReportPage" resultMap="reportResultMap">
           SELECT
           *
           FROM
           infisa_medical_report
           WHERE is_deleted=0 AND status = 1 AND name LIKE concat('%',#{handleTask.name},'%')
        </select>
    
    -- PostgreSQL:
    	<select id="checkReportPage" resultMap="reportResultMap">
           SELECT
           *
           FROM
           infisa_medical_report
           WHERE is_deleted=0 AND status = 1 AND name like concat('%',#{handleTask.name}::varchar,'%')
        </select>

### 5.**数据类型转化（针对于PostgreSQL）** ###

-- 强转（在查询字段上指定数据类型）
    
    a.a1 = b.b1::int8 
    -- 或者 
    a.a1::varchar = b.b1 
    -- 或者
    CAST(a.a1 AS varchar)
    
    MySQL：CAST(a.a1 AS varchar)

### 6.字段拼接 ###

如果需要对拼接的值去重，可以这样处理：

SELECT string_agg(address,',') FROM student GROUP BY grade;
    --未去重的情况下：
    string_agg|
    ----------|
    天河一路,天河一路 |
    天河一路,天河一路 |
    SELECT string_agg(distinct address,',') FROM student GROUP BY grade;
    string_agg|
    ----------|
    天河一路      |
    天河一路      |

如果想对拼接的值做排序，可以在拼接符号后面加`order by`

SELECT string_agg(name,',' order by name desc) FROM student GROUP BY grade;
    --结果：
    string_agg|
    ----------|
    小B,小A     |
    小D,小C     |

### 7.获取当前时间 ###

MySQL

>  *  CURDATE(), CURTIME(), NOW()：可以获取客户端所在时区的当前时间；
>  *  UTC\_DATE(), UTC\_TIME(), UTC\_TIMESTAMP()：可以获得当前的UTC时间；
>  *  CURRENT\_DATE(), CURRENT\_TIME(), CURRENT\_TIMESTAMP()：就是CURDATE(), CURTIME(), NOW()的别名

mysql> SELECT CURDATE(), CURTIME(), NOW();
    +------------+-----------+---------------------+
    | CURDATE()  | CURTIME() | NOW()               |
    +------------+-----------+---------------------+
    | 2022-08-30 | 11:45:47  | 2022-08-30 11:45:47 |
    +------------+-----------+---------------------+
    
    mysql> SELECT UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP();
    +------------+------------+---------------------+
    | UTC_DATE() | UTC_TIME() | UTC_TIMESTAMP()     |
    +------------+------------+---------------------+
    | 2022-08-30 | 07:46:10   | 2022-08-30 07:46:10 |
    +------------+------------+---------------------+
    
    mysql> SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();
    +----------------+----------------+---------------------+
    | CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() |
    +----------------+----------------+---------------------+
    | 2022-08-30     | 11:46:31       | 2022-08-30 11:46:31 |
    +----------------+----------------+---------------------+

PGSql

select now();
    select CURRENT_DATE;
    select CURRENT_TIME;
    select CURRENT_TIMESTAMP;
    select CURRENT_TIME(precision);
    select CURRENT_TIMESTAMP(precision);
    select LOCALTIME;
    select LOCALTIMESTAMP;
    select LOCALTIME(precision);
    select LOCALTIMESTAMP(precision);
     
    --示例
    SELECT CURRENT_TIME;
    结果：14:39:53.662522-05
     
    SELECT CURRENT_DATE;
    结果：2001-12-23
     
    SELECT CURRENT_TIMESTAMP;
    结果：2001-12-23 14:39:53.662522-05
     
    SELECT CURRENT_TIMESTAMP(2);
    结果：2001-12-23 14:39:53.66-05
     
    SELECT LOCALTIMESTAMP;
    结果：2001-12-23 14:39:53.662522