在线PgSql语句转换为mysql语句

PGSQL学习

PGSQL数据类型

数值类型

类型名称	存储尺寸	描述	范围
smallint	2字节	小整形	-32768至+32767
integer	4字节	整形	-2147483648至+214783647
bigint	8字节	长整型	-9223372036854775808至+9223372036854775807
decimal	变长	用户指定精度，精确	小数点前131072位，到小数点后16383位
numeric	变长	用户指定精度，精确	小数点前131072位，到小数点后16383位
real	4字节	可变精度，不精确	6位十进制精度
double precision	8字节	可变精度，不精确	15位十进制精度
smallserial	2字节	小范围自增整数	1至32767
serial	4字节	自增整数	1至2147483647
bigserial	8字节	大范围自增整数	1至9223372036854775807

字符类型

类型	描述
character varying(n), varchar(n)	变长字符串，最大1GB
character(n), char(n)	定长字符串，不足位空格补齐
text	变长字符串，无长度限制

二进制类型

类型	存储空间	描述
bytea	1或4字节加实际长度	变成二进制值

时间类型

类型	存储空间	描述	最小值	最大值	间隔
timestamp[§] [without time zone]	8字节	不带时区的日期时间	4713 BC	294276 AD	1毫秒
timestamp[§] with time zone	8字节	带时区的日期时间	4713 BC	294276 AD	1毫秒
date	4字节	不带时间的日期	4713 BC	5874897 AD	1天
time[§] [without time zone]	8字节	不带时区的时间	00:00:00	24:00:00	1毫秒
time[§] with time zone	12字节	带时区的时间	00:00:00+1459	24:00:00-1459	1毫秒
interval[fields] [§]	16字节	时间间隔	-178000000 years	178000000 years	1毫秒

布尔值类型

类型	存储空间	描述
boolean	1字节	布尔值，真或假

可用值：

true	false
TRUE	FALSE
‘t’	‘f’
‘true’	‘false’
‘y’	‘n’
‘yes’	‘no’
‘on’	‘off’
‘1’	‘0’

金额类型

类型名称	存储尺寸	描述	范围
money	8 bytes	currency amount	-92233720368547758.08至+92233720368547758.07

网络地址类型

类型	存储空间	描述
cidr	7或19字节	IPv4/IPv6网络地址
inet	7或19字节	IPv4/IPv6网络地址、支持位数表示的掩码
macaddr	6字节	MAC物理地址
macaddr8	8字节	MAC物理地址（EUI-64格式）

几何类型

类型	存储空间	描述	数值表示方法
point	16 bytes	点	(x, y)
line	32 bytes	无限长的线	(A, B, C)或[(x1, y1), (x2, y2)]
lseg	32 bytes	有限长线段	((x1, y1), (x2, y2))
box	32 bytes	矩形	((x1, y1), (x2, y2))
path	16+16n bytes	封闭路径	((x1, y1), …)
path	16+16n bytes	开放路径	((x1, y1), …)
polygon	40+16n bytes	多边形	((x1, y1), …)
circle	24 bytes	圆	<(x, y), r>（圆心位置和半径）

文档类型

类型	描述
XML	XML文档
json	原文本存储的JSON文档
jsonb	经过分解的二进制方式存储的JSON文档

数组类型

• PostgreSQL允许在各种内置的或者用户自定义的数据类型基础上创建数组类型

• 数组类型的定义是通过在数组元素类型后面添加中括号“[ ]”来实现的

• 多维数组就是使用多对中括号“[ ]”，但实际上是否为多维是取决于数据而不取决于有多少对[]，所以[]与[][]的效果是一样的

范围类型

类型	描述
int4range	整形范围
int8range	长整型范围
numrange	数值范围
tsrange	时间戳范围
tstzrange	带时区时间戳范围
daterange	日期范围

PGSQL基本语法

表操作

CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL ,  
NAME TEXT NOT NULL,  
AGE INT NOT NULL,   
ADDRESS CHAR(50),   
SALARY REAL,   
LOCATION point
);

# 删除表
drop table if exists company;

drop table if exists weather;

CREATE TABLE cities (
    city_id          varchar(10),
    name            varchar(80),
    location        point
);

INSERT INTO cities VALUES ('xa','西安', '(-194.0, 53.0)');

UPDATE cities SET NAME='西安市', LOCATION='(-196.12,63.22)' WHERE CITY_ID ='xa';

DELTE FROM cities WHERE CITY_ID =’xa’;

模式(SCHEMA)

PostgreSQL 模式（SCHEMA）可以看着是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

相同的对象名称可以被用于不同的模式中而不会出现冲突，例如 schema1 和 myschema 都可以包含名为 mytable 的表。

CREATE SCHEMA myschema.mytable (
...
);

# 创建一个模式,并模式下创建一个表格
create schema myschema;
create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

# 查看表格
select * from myschema.company;

# 删除一个为空的模式（其中的所有对象已经被删除）
drop schema myschema;

# 删除一个模式以及其中包含的所有对象（cascade 层叠/大量）
drop schema myschema cascade;

CRUD

# 插入语句 JOIN_DATE 字段使用 DEFAULT 子句来设置默认值
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );

# 插入多行数据
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

# 查询
select * from company;
SELECT (17 + 6) AS ADDITION；
SELECT COUNT(*) AS "RECORDS" FROM COMPANY;
SELECT CURRENT_TIMESTAMP;

# 子查询
SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

# 更新
UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

# 删除
DELETE FROM COMPANY WHERE ID = 2;

聚合操作

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

# like  将整型数据类型转化为字符串数据类型,再模糊搜索
SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';

# limit 从第三位开始提取3个记录
SELECT * FROM COMPANY LIMIT 4;
SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

# GROUP BY 在一个 SELECT 语句中，放在 WHRER 子句的后面，ORDER BY 子句的前面。
SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

# HAVING 子句可以让我们筛选分组后的各组数据。
SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

# DISTINCT 关键字与 SELECT 语句一起使用，用于去除重复记录，只获取唯一的记录。
SELECT DISTINCT name FROM COMPANY;

WITH子句

在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用；

WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表；

WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它；WITH 子句在使用前必须先定义；

语法：name_for_summary_data 是 WITH 子句的名称，name_for_summary_data 可以与现有的表名相同，并且具有优先级。

可以在 WITH 中使用数据 INSERT, UPDATE 或 DELETE 语句，允许您在同一个查询中执行多个不同的操作。

# 1导入数据
DROP TABLE COMPANY;
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

# 2使用 WITH 子句在上表中查询数据：
With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

# 3使用 RECURSIVE 关键字和 WITH 子句编写一个查询，查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和
WITH RECURSIVE t(salary) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(salary) FROM t;

# 4我们建立一张和 COMPANY 表相似的 COMPANY1 表，使用 DELETE 语句和 WITH 子句删除 COMPANY 
#表中 SALARY(工资) 字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1 表，实现将 COMPANY 
#表数据转移到 COMPANY1 表中

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);


WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      salary >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

union, except, intersect

并集： union会移除所有重复的行，要保留重复的行，需要使用 union all
     select name ,age, address,salary from COMPANY  where (salary > 10000)
union
      select name ,age, address,salary from COMPANY1  where (salary > 10000)

差集：except返回在第一张表出现，但在第二张表不存在的记录，两张表查询有先后顺序之别
     select name ,age, address,salary from COMPANY  where (salary > 10000)
except
      select name ,age, address,salary from COMPANY  where (age > 30)

交集：intersect返回既,又两种条件
     select name ,age, address,salary from COMPANY  where (salary > 10000)
intersect
      select name ,age, address,salary from COMPANY  where (age > 20)

级联查询

内连接：inner Join

select co1.* from company co1 inner join  company1 co2 on  co1.id =  co2.id;

select co1.* from  company co1, company1 co2 where  co1.id =  co2.id;


外连接： left join , right join 
   
左连接：
          select co1.* from company co1 left join  company1 co2 on  co1.id =  co2.id;
          
          select co1.* from  company co1, company1 co2 where  co1.id =  co2.id(+);

右连接：
          select co2.* from company co1 right join  company1 co2 on  co1.id =  co2.id;
          
          select co2.* from  company co1, company1 co2 where  co1.id(+) =  co2.id;

PGSQL查看执行计划

1.使用explain命令查看执行计划;

2.客户端点击查看执行计划；

执行计划是从下往上读的

explain报告查询的操作，开启的消耗，查询总的消耗，访问的行数 访问的平均宽度

开启时间消耗是输出开始前的时间例如排序的时间

消耗包括磁盘检索页，cpu时间

注意，每一步的cost包括上一步的，重要的是，explain 不一定是真正的执行一次查询 只是得到查询执行的计划和估计的花费