《课程笔记 | PostgreSQL深入浅出》之开发基础 （八）

常见数据类型

• 参考官方文档：http://www.postgres.cn/docs/12/datatype.html
  
  

与Oracle 对应类型

DDL、DML、DQL常用语句

• 参考官方文档：http://www.postgres.cn/docs/12/sql.html

DDL: 是数据定义语言，用户定义和管理sql数据库中所有对象的语言。create alter drop

DML: 是数据操纵语言；update,delete,insert

DQL: 是数据查询语言； select from where

DCL: 是数据控制语言；grant,rollback,commit,savepoint

操作实例：

--PostgreSQL之SQL语句
--CTAS

SELECT *
FROM std;

 \d std
ALTER TABLE std
ALTER COLUMN sage
SET DATA TYPE int2;

 \d+ std
SELECT *
FROM std;

 --只有在保证数据完整性时才会需要使用主键(std score)
 --数据类型
--整型
--smallint

CREATE TABLE int_tab(col1 smallint, col2 integer,col3 bigint);

 \d int_tab
INSERT INTO int_tab
VALUES(32767,
       2147483647,
       9223372036854775807);


SELECT *
FROM int_tab;


INSERT INTO int_tab(col1)
VALUES(-32768);


SELECT *
FROM int_tab;


INSERT INTO int_tab(col3)
VALUES(-9223372036854775808);


SELECT *
FROM int_tab;

 --decimal
 --decimal

ALTER TABLE int_tab ADD COLUMN col4 decimal;


SELECT *
FROM int_tab;


SELECT *
FROM int_tab;


INSERT INTO int_tab(col4)
VALUES(10);


SELECT *
FROM int_tab;


INSERT INTO int_tab(col4)
VALUES(100.00);

 TRUNCATE TABLE int_tab;


ALTER TABLE int_tab
ALTER COLUMN col4
SET DATA TYPE numeric(4,3);


INSERT INTO int_tab(col4)
VALUES(1.211);


INSERT INTO int_tab(col4)
VALUES(1.2118);

 --numeric(精度,小数比例)
--精度:数值总长度
--小数比例:表示小数位数

SELECT *
FROM int_tab;

 --添加一行序列类型列

ALTER TABLE int_tab ADD COLUMN col5 serial;


INSERT INTO int_tab(col1)
VALUES(1);


SELECT *
FROM int_tab;


INSERT INTO int_tab(col5)
VALUES(3);


INSERT INTO int_tab(col2)
VALUES(2);

 --序列递增，不会因为用户更改列数据而变化
--货币类型

ALTER TABLE int_tab ADD COLUMN price money;

 \d int_tab;


INSERT INTO int_tab(price)
VALUES(12.04);


SELECT *
FROM int_tab;


SELECT replace(price::text,'$','￥')
FROM int_tab;

 --字符数据类型
--varchar(n) 长度可变
--char(n) 固定长度,无论插入的字符长度是多少，长度都已经分配
--text
--boolean类型

CREATE TABLE bool_tab(col1 boolean);


ALTER TABLE bool_tab ADD COLUMN col2 boolean;


INSERT INTO bool_tab
VALUES('1',
       '0'),('t',
             'f'),('true',
                   'false'),('yes',
                             'no'),('on',
                                    'off');


SELECT *
FROM bool_tab;

 --

SELECT '10.10.20.0'::inet AS ipaddr ;


SELECT '10.10.20.100'::cidr AS netmask;

 --bit 0 1
--tsquery
--tsvector
 --用来模糊查找(关联查找)
 --数据库:
 --	PostgreSQL数据库,Oracle数据库,MySQL数据库
--数组,相同数据类型的集合

SELECT '{1,2,3}'::int[] ;


SELECT '{1,2,3}'::varchar[] ;


SELECT '{"A","B","C"}' :: text[];


SELECT '{1,"b"}' :: text [];


SELECT '{{1,"b"},{2,"c"}}' :: text[] --SQL函数
 --数学函数
\d employees;


SELECT salary
FROM employees;

 --最大工资

SELECT max(salary)
FROM employees;

 --最小工资

SELECT min(salary)
FROM employees;

 --绝对值函数

SELECT abs(id)
FROM
  (SELECT -100::int2 AS id) AS x;

 --平方根函数

SELECT sqrt(id)
FROM
  (SELECT 3 AS id) AS x;

 --四舍五入函数

SELECT round(id)
FROM
  (SELECT 3.14159265 AS id) AS x
SELECT round(id)
FROM
  (SELECT 3.86 AS id) AS x --round保留小数位

SELECT round(10.20023,4) AS rd ;


SELECT round(10.23418,4) AS rd;

 --向上截取函数，向下截取函数
--ceil floor

SELECT ceil(10.20) AS id;


SELECT floor(10.20) AS id;


SELECT ceil(-10.20) AS id;


SELECT floor(-10.20) AS id;

 --符号函数

SELECT sign(8) AS id;


SELECT sign(-8) AS id;


SELECT sign(0) AS id;

 --幂运算函数

SELECT pow(2,3) AS id;

 --对数运算函数

SELECT log(10) AS id;


SELECT log(power(10,2)) AS id;

 --字符串函数
--计算字符串长度

SELECT char_length(txt)
FROM
  (SELECT 'https://www.baidu.com' AS txt) AS x;


SELECT length(txt)
FROM
  (SELECT 'https://www.baidu.com' AS txt) AS x;

 --合并字符串的函数
--concat concat_ws

SELECT concat(str1,str2,str3)
FROM
  (SELECT 'https://' AS str1,
          'www.baidu.com' AS str2,
          '/index.html' AS str3) AS x;

 --concat_ws用指定的分隔符分割字符串函数

SELECT concat_ws('	',str1,str2,str3)
FROM
  (SELECT 'https://' AS str1,
          'www.baidu.com' AS str2,
          '/index.html' AS str3) AS x;

 --获取指定长度的字符串

SELECT left(txt,5)
FROM
  (SELECT concat(str1,str2,str3) AS txt
   FROM
     (SELECT 'https://' AS str1,
             'www.baidu.com' AS str2,
             '/index.html' AS str3) AS x) AS y;


SELECT right(txt,24)
FROM
  (SELECT concat(str1,str2,str3) AS txt
   FROM
     (SELECT 'https://' AS str1,
             'www.baidu.com' AS str2,
             '/index.html' AS str3) AS x) AS y;

 --填充字符串
--lpad rpad

SELECT last_name,
       salary
FROM employees;


SELECT lpad(last_name,10,'-') AS last_name,
       rpad(salary::text,10,'#') AS salary
FROM employees;

 --删除空格的函数
--ltrim rtrim trim

SELECT ltrim(txt),
       replace(rtrim(txt),' ',''),
       trim(txt)
FROM
  (SELECT '  http://www.   baidu.com   ' AS txt) AS x;

 --删除指定的字符串

SELECT trim('ab'
            FROM 'abwwwabcomab') AS id --生成重复的字符串

SELECT repeat('www',2) AS txt;

 --如何将字符串中的字符使用指定的字符代替

SELECT last_name || salary AS txt
FROM employees;


SELECT rpad(replace(replace(translate(lower(e.txt),t.lett,repeat('-',26)),'-',''),' ',''),10,' ') AS salary,
       replace(replace(translate(e.txt,'0123456789',repeat('-',26)),'-',''),'.','') AS last_name
FROM
  (SELECT last_name || salary AS txt
   FROM employees) AS e,

  (SELECT 'abcdefghijklmnopqrstuvwxyz' AS lett) AS t --获取子串函数
--substring

SELECT substring('https://www.baidu.com',10) AS txt; --匹配字符串的起始位置
--position

SELECT position('w' IN 'https://www.baidu.com') AS txt;

 --反转字符串函数
--reverse

SELECT reverse('moc.udiab.www.//:sptth') AS txt;

 --日期函数

SELECT CURRENT_DATE;


SELECT CURRENT_TIME;


SELECT CURRENT_TIMESTAMP;


SELECT LOCALTIME;


SELECT LOCALTIMESTAMP;

 --提取指定日期的函数

SELECT extract(YEAR
               FROM now()) AS YEAR;


SELECT extract(DAY
               FROM now()) AS DAY;


SELECT extract(MONTH
               FROM now()) AS MONTH;


SELECT extract(quarter
               FROM now()) AS quarter;


SELECT now(),
       now() + interval '1 hour' AS int_hour;


SELECT now(),
       now() + time '1:00' AS time;

 --条件判断函数
--case
--  when
--    then
--  when
--    then
--  else
--end case

SELECT salary
FROM employees;


SELECT last_name,
       salary,
       CASE
           WHEN salary::int <6000 THEN 'low salary'
           WHEN salary > 6000
                AND salary <15000 THEN 'middle salary'
           WHEN salary >= 15000
                AND salary <= 24000 THEN 'high salary'
           ELSE 'other'
       END
FROM employees;

 --使用列别名进行条件筛选

SELECT e.员工姓名,
       e.员工工资
FROM
  (SELECT last_name 员工姓名,
                    salary 员工工资
   FROM employees) AS e
WHERE 员工工资 > 10000;


SELECT 'https://www.baidu.com' AS txt h t t p
SELECT substr(t.txt,g.id,1) AS col_char,
       g.id AS col_no,
       substr(t.txt,g.id) as decrement,
       substr(t.txt,length(t.txt)-g.id+1) as increment
FROM
  (SELECT 'https://www.baidu.com' AS txt) AS t,

  (SELECT id
   FROM generate_series(1, 21) AS id) AS g


  --统计字符串出现的次数
  SELECT (length(t.txt) - length(replace(t.txt,'www','')))/length('w') AS len
FROM
  ( SELECT 'https://www.baidu.com' AS txt) AS t