Postgresql基础sql

学习pg的链接
https://github.com/bkfish/Postgresql


#创建数据库
create database my_db1;
create database tpcc with template=tmplate0 encoding='UTF8' owner hh;

#查看已经存在的数据库
\l

#进入某数据库
\c+ 数据库名

#删除数据库
drop database my_db1; #系统目录项并且删除包含数据的文件目录,只有超级用户和数据库管理人员可以执行

#创建表格
create table company(
    id int primary key not null,
    name text not null,
    age int not null,
    address char(20));

create table department(
    id int primary not null,
    dept char(20) not null,
    emp_id int not null);

#查看表格是否创建成功\删除成功
\d

#删除表格
drop table company, depatment;


#pg的模式 schema
create schema myschema;
create table myschema.company(id int primary key not null, name varchar(20));
select * from myschema.company;
drop schema myschema; #其中的对象已经被删除
drop schema myshcema cascade; #删除一个模式,以及其中包含的所有对象


#插入数据
insert into company (id, name, age, address) values (1, 'Li', 24, 'nanjing');
insert into company (id, name, age, address) values (2, 'Zhang', 17, 'Beijing');
insert into company (id, name, age, address) values (3, 'Huang', 21, 'Dongjing');
insert into company (id, name, age, address) values (4, 'Thang', 24, 'Xijing');


#查询语句
select * from company;
select id, name, age, address from company;


#查询语句中的运算符
select * from company where id = 2;
select * from company where age > 20 and age < 30;
select * from company where age < 25 or id > 3;
select * from company where address is not null;

#pg的表达式
select count(*) as "RECORDS" from company;
select current_timestamp;

#pg的where子句
select * from company where id = 2;
select * from company where age > 20 and age < 30;
select * from company where age < 25 or id > 3;
select * from company where address is not null;
select * from company where age in (10, 30);
select * from company where age between 20 and 30;
select * from company where name like '%ing'
select * from company where age select age from company where id > 3; #子查询

#pg的AND/OR
select * from company where age > 10 and id > 3;
select * from company where age > 10 or id > 3;

#pg的Update语句
update company set age = 33 where id = 4;

#pg的Delete语句
delete from company where age = 33;

#pg的like语句
SELECT * FROM company where address LIKE '%ing';

#pg的limit语句, 限制 SELECT 语句中查询的数据的数量
select * from company limit 3 offset 2; # 从第(2+1)位读取3个记录
select * from  company limit 4; #读取4条数据


#pg的order by语句
select * from company order by age asc; #升序
select * from company order by name desc; #降序

#pg的group by语句, 和 SELECT 语句一起使用, 用来对相同的数据进行分组,放在 WHRER 子句的后面, ORDER BY 子句的前面
select name, sum(age) from company group by name order by id;

#pg的having子句,用来筛选分组后的数据, count(name) > 1名字的计数大于1.
select * from group by age having count(name) > 1 order by id ASC;


#pg的distinct关键字,去除重复记录, 只获取唯一的记录
select distinct name  from company;


#pg常用的内置函数（聚集函数）
#COUNT 用于计算数据库表中的行数
#MAX 用于查询某一特定列中最大值
#MIN 用于查询某一特定列中最小值
#AVG 用于计算某一特定列中平均值
#SUM 用于计算数字列所有值的总和
#聚集不能用在where子句中，
SELECT city FROM weather WHERE temp_lo = max(temp_lo);     错误
如果想实现类似想法，需要在where子句中用到子查询
select city from weather where templo = (select max(temp_lo) from weather);


#pg的权限  grant -->赋予权限
#语法 grant privileges [, ...] on object [, ...] to {public | group groupname | username}

create user hh with password '12345';
grant all privileges  on comnpany to hh;

​
#pg的子查询 （子查询必须用括号括起来; order by 语句不能用在子查询中; 子查询返回多于一行，只能与多值运算符一起使用,如 IN 运算符）
select * from company where id in (select id from company where salary > 12345);
insert into  company_bak select * from company where id in (select id from company);
update company set salary = salary * 0.50 where age in (select age from company_bak where age >= 27);
delete from comapny where age in (select age from company_bak where age > 27);

#pg的约束，(约束可以在创建表的时候，可以是在alter时候)
#not null某列不能存储NULL值
#unique 确保某列的值是唯一的
#primary key 主建，唯一标识，是not null和unique的结合
#foreign key: 外键约束，指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。
#一个表只能有一个主键，它可以由一个或多个字段组成，当多个字段作为主键，它们被称为复合键。

#pg的alter命令
#alter table 命令用于添加、修改、删除一张已经存在的表
#也可以用于alter table命令添加和修改约束
#添加
alter table table_name add column_name datatype;
#删除列
alter table table_name drop column column_name;
#修改表中的某列数据类型
alter table table_name alter column column_name type data_type;
#给表中的某列添加Not null的约束
alter table table_name alter column  column_name set not null;
#给表中的某列删除Not null的约束
alter table table_name alter column column_name drop not null;

#pg的视图
#视图是一张假表，只不过是通过相关的名称存储在数据库中的一个 PostgreSQL 语句
#可以包含一个表的所有行或从一个或多个表选定行
#PostgreSQL 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句
create view view_name as select * from table_name;
drop view view_name;



#pg的权限  revoke -->撤销权限
#语法 revoke privileges  [, ...] on object [, ...] from {public | group groupname | username}
revoke all privileges on company from hh;
#或者 drop  user hh;


#pg的join
#CROSS JOIN 交叉连接
#INNER JOIN 内连接
#LEFT OUTER JOIN 左外连接 (不是相同元素的话,置位null)
#RIGHT OUTER JOIN 右外连接
#FULL OUTER JOIN 全外连接

SELECT ... FROM table1 (INNER) JOIN table2 ON conditional_expression ...;
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...;

#pg的union操作符，用于合并多个select语句的结果集
select id, name from company inner join department on commpany.id = department.id 
union
select id, name from cpmpany left outer join department on company.id = department.id

#pg中的null值
#null值代表遗漏的未知数据，表的列可以存放NULL值
#这里，NOT NULL 表示强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。具有 NULL 值的字段表示在创建记录时可以留空。在查询数据时，NULL 值可能会导致一些问题，因为一个未知的值去与其他任何值比较，结果永远是未知的。另外无法比较 NULL 和 0，因为它们是不等价的。

#where子句中的is not null 和 is null， 用来查找非空字段和空字段。

#pg中的别名：用来重命名一张表或者一个字段。
select column1, column2... from table_name as alias_name where [condition]; #表的别名
select column_name as alias_name from table_name where [condition]; #字段的别名

#pg中的pg_xlog
记录pg中的WAL信息,也就是一些事务日志信息，默认日志大小为16M。这些日志会在回滚恢复（PITR）、流复制（Replication Stream）、日志归档时用到。
pg_clog: 记录pg中的元数据信息（metadata)，也是一种事务日志信息。

#pg如何生成测试数据
# 转载  https://www.jianshu.com/p/d465a4c748e8
drop table if exists t1;
begin;
select txid_current();
create table t1(id int, c1 varchar(20));
select txid_current();
insert into t1 select generate_series(1, 10000);
select count(*) from t1;
select * from t1;
end;

#pg中查找组合使用DISTINCT和ORDER BY来保证获取一致的结果
select distinct XXXX from table_name ordern by YYYYYY;

#pg中的一些特殊变量类型
date： '1994-11-29'  #时间类型
real： 0.25  #单精度浮点型
point： '(-194.0, 53.0)'  #坐标类型

#聚集函数是常见使用场景
1：用于和GROUP BY子句组合
SELECT city, max(temp_lo) FROM weather GROUP BY city;