发帖
雷达卡
个人笔记记录。
【postgresql】
[sql_base]
1\ curd
(1)
-- c
create
table
create table we_table(
city varchar(80),
temp_lo int,
date date
);
create table mytable(f1 int, f2 float, f3 text);
create table abc as
select * from abc;
not null
create table products (
product_no integer not null,
name text not null,
price numeric
);
unique
create table products (
product_no integer unique,
name text,
price numeric
);
create table example (
a integer,
b integer,
c integer,
unique (a, c)
);
default
create table products (
product_no integer,
name text,
price numeric default 9.99
);
check
create table products (
product_no integer,
name text,
price numeric check (price > 0)
);
create table products (
product_no integer,
name text,
price numeric check (price > 0),
discounted_price numeric check (discounted_price > 0),
check (price > discounted_price)
);
create table products (
product_no integer,
name text,
price numeric check (price > 0),
discounted_price numeric,
check (discounted_price > 0 and price > discounted_price)
);
primary key
create table city2(
city varchar(80) primary key,
location point
);
create table example (
a integer,
b integer,
c integer,
primary key (a, c)
);
create table t1 (
a integer primary key,
b integer,
c integer,
foreign key (b, c) references other_table (c1, c2)
);
foreign key
create table we2(
city varchar(80) references city2(city)
);
constraint
create table products (
product_no integer,
name text,
price numeric constraint positive_price check (price > 0)
);
(2)
-- u
update
update we_table
set temp_lo = temp_lo - 27
where date > '1994-11-27'
update mytable set a = 5, b = 3, c = 1 where a > 0;
insert
insert into we_table values('beijing',46,'1994-11-27');
insert into we_table(city) values('beijing');
insert into products (product_no, name, price) values
(1, 'cheese', 9.99),
(2, 'bread', 1.99),
(3, 'milk', 2.99);
add column
alter table products add column description text;
alter table products add column description text check (description <> '');
adding a constraint
alter table products add check (name <> '');
alter table products add constraint some_name unique (product_no);
alter table products add foreign key (product_group_id) references product_groups;
alter table products alter column product_no set not null;
alter table products alter column price set default 7.77;
alter table products alter column price type numeric(10,2);
renaming a column
alter table products rename column product_no to product_number;
renaming a table
alter table products rename to items;
(3)
-- r
functions
max
select max(temp_lo) from we_table
select city,max(temp_lo) from we_table group by city
select city,max(temp_lo) from we_table group by city having max(temp_lo) < 40
join
cross join
select * from t1 cross join t2;
inner join
select * from t1 inner join t2 on t1.num = t2.num;
select * from t1 inner join t2 using (num);
left join
right join
full join
select * from t1 full join t2 on t1.num = t2.num;
order by
select a, b from table1 order by a + b, c;
limit
... limit 10
offset
... offset 10
union
select 1 as column1, 'one' as column2
union all
select 2, 'two'
union all
select 3, 'three';
(4)
-- d
drop
table
drop table we_table;
delete
delete from we_table where city = 'hi'
drop column
alter table products drop column description;
alter table products drop column description cascade;
drop constraint
alter table products drop constraint some_name;
alter table products
alter column product_no drop not null;
alter table products
alter column price drop default;
2\ view
views
create view myview as
select city, temp_lo
from we_table
where date > '1994-11-27'
select * from myview
3\ transaction
beign;
update accounts set balance = balance - 100.00
where name = 'alice';
savepoint my_savepoint;
update accounts set balance = balance + 100.00
where name = 'bob';
-- oops ... forget that and use wally's account
rollback to my_savepoint;
update accounts set balance = balance + 100.00
where name = 'wally';
commit;
6\ partition
select depname, empno, salary,
avg(salary) over (partition by depname)
from empsalary;
select depname, empno, salary,
rank() over (parititon by depname order by salary desc)
from empsalary;
select salary,
sum(salary) over (order by salary)
from empsalary;
select depname, empno, salary, enroll_date
from
(select depname, empno, salary, enroll_date,
rank() over (partition by depname order by salary desc, empno) as pos
from empsalary
) as ss
where pos < 3;
**
select sum(salary) over w, avg(salary) over w
from empsalary
window w as (partition by depname order by salary desc);
**
7\ inherit
create table capitals (
state char(2)
) inherits (cities);
select name, altitude
from only cities
where altitude > 500;
8\ use code
select e'foo\ndd';
9\ filter
select
count(*) as unfiltered,
count(*) filter (where i < 5) as filtered
from generate_series(1,10) as s(i);
10\ array row --useful
select array[1,2,3+4];
select array[1,2,22.7]::integer[];
select array[array[1,2], array[3,4]];
select array[[1,2],[3,4]];
select array(select oid from pg_proc where proname like 'bytea%');
数据整理
select row(1,2.5,'this is a test');
数据整理
11\ case end
select case when min(employees) > 0
then avg(expenses / employees)
end
from departments;
12\ function
01-example
create function lgb_getf1(mytable) returns int as
'select $1.f1'
language sql;
select getf1(row(1,2.5,'this is a test'));
02-example
create function lgb_concat_lower_or_upper(a text, b text, uppercase boolean default false)
returns text
as
$$
select case
when $3 then upper($1 || ' ' || $2)
else lower($1 || ' ' || $2)
end;
$$
language sql immutable strict;
select lgb_concat_lower_or_upper('my name','is',true)
select concat_lower_or_upper(a := 'hello', uppercase := true, b := 'world');
13\ schema
create
create schema myschema;
table
create table myschema.mytable (
...
);
drop
drop schema myschema cascade;
提升卡
置顶卡
沉默卡
变色卡
抢沙发
千斤顶
显身卡
京公网安备 11010802022788号
