常用sql语句

/*表操作*/

drop table order;

create table products(
product_no integer primary key default 1,
name text,
price numeric default 9.99
);

create table orders (
order_id integer primary key default 1,
product_no int,
quantity integer
);

create table order_items(
product_no int references products(product_no) on update set default 9,
order_id int references orders(order_id) on update set default 9,
quantity int,
primary key(product_no,order_id)
);

insert into products values(2,'aaa',10.00),(3,'bbb',18.00);
insert into orders values(2,2,50),(3,3,80);
insert into order_items values(2,3,70),(3,2,80);

update products set product_no=4 where product_no =2;
delete from orders where order_id =2;

alter table products drop description cascade;
alter table orders alter column order_id set default 004;

create table circles(
c circle,
exclude using gist(c with &&)
);

alter table products add check(name <>'');
alter table products add constraint some_name unique(name);
alter table orders add constraint test foreign key (quantity) references products(product_no);
alter table products drop constraint some_name;
alter table products alter column name set not null;
alter table products alter column name drop not null;
alter table products alter column product_no set default 100;
alter table products alter column product_no drop default;
alter table products alter column price type numeric(10,3);
alter table products rename column product_no to p_no;
alter table order_items rename to items;
alter table items owner to joe;
grant update on items to wang;
revoke delete on items from wang;
grant select,update,delete,insert on items to admin;
grant select(col1),update(col2) on items to joe;

alter table items enable row level security;
create policy items_manager on items to wang using(quantity<100);

drop table circles;
drop table people;
drop table items;
drop table products cascade;
drop table orders;

/*模式*/
create schema schema1;
create table schema1.test1(name text);
drop schema schema1;
drop schema schema1 cascade;
/*显示当前搜索路径*/
show search_path;
set search_path to schema1,public;
/*schema1是搜索路径中第一个元素，新对象会默认创建在schema1模式下*/
create table test1(id smallint);
create role wang5;
revoke create on schema schema1 from wang5;

/*继承*/
create table cities(
name text,
pop float,
altitude int
);
create table capitals(
state char(2)
)inherits(cities);
insert into cities values('Las Vegas',10000,56),('Mari',2100,45),('Madanana',4587.56,12);
insert into capitals values('Las Vegas',10000,56,'hh'),('Mari',2100,45,'dd'),('Las hdhdh',10780,56,'lo');
select name,pop from cities where altitude > 10;
select name,pop from only cities where altitude > 10;

/*分区表*/
create table mea(
cityid int not null,
logdate date not null,
peaktmp int,
unitsales int
)partition by range(logdate);
create table mea_y2022m01 partition of mea for values from('2022-01-01') to ('2022-01-31');
create table mea_y2022m02 partition of mea for values from('2022-02-01') to ('2022-02-28');
create table mea_y2022m03 partition of mea for values from('2022-03-01') to ('2022-03-31');
create table mea_y2022m04 partition of mea for values from('2022-04-01') to ('2022-04-30') partition by range(peaktmp);
create index on mea(logdate);
alter table mea detach partition mea_y2022m04;
insert into mea values(111,'2022-01-20',120,9),(222,'2022-01-22',130,8),(333,'2022-02-15',150,74),(444,'2022-03-01',145,14),(555,'2022-03-05',162,85);
CREATE TABLE mea_y2022m05
(LIKE mea INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
TABLESPACE fasttablespace;
alter table mea attach partition mea_y2022m05 for values from('2022-05-01') to ('2022-05-31');
create index index_m01 on mea_y2022m01(unitsales);
alter index index_m01 attach partition mea_y2022m01;
drop index index_m01;
/*使用继承实现分区*/
create table mea1(
cityid int not null,
logdate date not null,
peaktmp int,
unitsales int
);
create table mea_y2022m05() inherits(mea1);
create table mea_y2022m06() inherits(mea1);
create table mea_y2022m07(check(logdate>=date'2022-07-01' and logdate<=date'2022-07-31'))inherits(mea1);
create index in_m05 on mea_y2022m05(logdate);
create index in_m06 on mea_y2022m06(logdate);
create index in_m07 on mea_y2022m07(logdate);
alter table mea_y2022m06 no inherit mea1;
drop table mea_y2022m07;

update cities set pop=10000.25 where pop=10000;
update cities set altitude=altitude +1;
update cities set pop=10000,altitude =55 where pop=10000.25;

insert into cities values('hdhdh',56985,76) returning name;
delete from cities where name='Las hdhdh' returning pop;
delete from cities where name='hdhdh' returning *;
select 10+5;

/*查询*/
create table test1(
   num int,
   name varchar(10)
);
create table test2(
   num int,
   value varchar(10)
);
insert into test1 values(1,'a'),(2,'b'),(3,'c');
insert into test2 values(1,'xxx'),(3,'yyy'),(5,'zzz');
select * from test1 cross join test2;
select * from test1 inner join test2 on test1.num=test2.num;/*num不会聚合成一列*/
select * from test1 inner join test2 using(num);/*num聚合成一列*/
select * from test1 natural inner join test2;/*num聚合成一列,natural根据在两个表里都出现的列去聚合*/
select * from test1 left join test2 on test1.num=test2.num;/*num不会聚合成一列*/
select * from test1 right join test2 using(num);
select * from test1 full join test2 on test1.num=test2.num;
select * from test1 left join test2 on test1.num=test2.num and test2.value='xxx';/*返回3行*/
select * from test1 left join test2 on test1.num=test2.num where test2.value='xxx';/*返回1行*/

select * from dblink('dbname=mydb','select proname,prosrc from ux_proc') as t1(proname name,prosrc text) where proname like'bytea%';
/*如果get_product_names()返回一个制造商制造的产品的名字，但是某些制造商在我们的表中目前没有制造产品，我们可以找出哪些制造商是这样：*/
select m.name from manufactures m left join lateral get_product_names(m.id) pname on true where pname is null;
select * from test1 where num in (1,2,3);
select * from test1 where num in (select num from test2);
select * from test1 where num between 1 and 3;
select * from test1 where exists(select num from test1 where num>3);/*exists条件为真，则执行前面的查询，否则不执行；查询结果为空*/
select * from test1 where exists(select num from test1 where num>2);/*查询结果为test1表*/

create table test3(x char,y int);
insert into test3 values('a',3),('c',2),('b',5),('a',1);
select x from test3 group by x;
select x,sum(y) from test3 group by x;
select x,sum(y) from test3 group by x having sum(y)>3;
select brand,size,sum(sales) from items_sold group by grouping sets((brand),(size),());
rollup(e1,e2,e3)等效于grouping sets((e1,e2,e3),(e1,e2),(e1),())
cube(e1,e2,e3)等效于grouping sets((e1,e2,e3),(e1,e2),(e1,e3),(e1),(e2,e3),(e2),(e3),())
/*CUBE或ROLLUP子句中的元素可以是表达式或者圆括号中的元素子列表。在后一种情况中，对于生成分组集的目的来说，子列表被当做单一单元来对待*/
cube((a,b),(c,d))等效于grouping sets((a,b,c,d),(a,b),(c,d),())
rollup(a,(b,c),d)等效于grouping sets((a,b,c,d),(a,b,c),(a),())
query1 union query2;
query1 intersect query2;
query1 except query2;
with regional_sales as(select region,sum(amount) as total_sales from orders group by region),
top_region as(select region from regional_sales where total_sales>(select sum(total_sales)/10 from regional_sales)
select region,product,sum(quantity) as product_units,sum(amount) as product_sales from orders where region in (select region from top_region) group by region,product;
/*通过使用recursive,一个with 查询可以引用它自己的输出*/
with recursive t(n) as (values(1) union all select n+1 from t where n<100) select sum(n) from t;
with moved_rows as(delete from products where "date">='2010-10-01' and "date"<'2010-11-01' returning *) insert into products_log select * from moved_rows;

/*兼容性语法*/
/*ux*/create synonym table synt1 on test1; /*创建同义词失败*/
/*uxdb*/create synonym table synt1 on test1;
/*ora*/create synonym table synt1 on test1;/*创建同义词成功*/
/*oracle*/create synonym table synt1 on test1;
set ora_grammar=on;/*打开兼容语法开关*/
show ora_grammar;

/*同义词(synonym)是表、索引、视图等模式对象的一个别名*/
create synonym synt2 for test2;
select * from ux_synonym;
select * from synt2;
show running_mode;
create view vw1 as select name from test1 where num<=3;
create synonym synv1 for vw1;
create function add(int,int,int) returns int as 'select $1+$2+$3;' language sql;
create synonym s1 for add(int,int,int);
select * from s1(1,2,3);
drop synonym synt2;
drop synoym vw1;

/*+语法*/
select * from test1 left join test2 on test1.num=test2.num;
select * from test1,test2 where test1.num=test2.num(+);
select * from test1 right join test2 on test1.num=test2.num;
select * from test1,test2 where test1.num(+)=test2.num;

/*子查询支持无别名*/
select * from(select name from test1 where num<=3) as a;
select * from(select name from test1 where num<=3);
select name,age from(select * from(select * from parent where id>1)) where age>40;
select name,age from(select * from(select * from parent where id>1) a1)where gae>40;
select name,age from(select * from(select * from parent where id>1) a1) a2 where gae>40;
select name,age from(select * from(select * from parent where id>1)) a2 where gae>40;

/*分页查询,limit x，y等价于limit y offset x 或者offset x limit y*/
select id,name,score from students order by score desc limit 3 offset 2;
select id,name,score from students order by score desc limit 2,3;
select id,name,score from students order by score desc offset 2 limit 3;
select id,name,score from students order by score desc limit 2+1 offset 2;
select id,name,score from students order by score desc offset 2;

/*约束名省略*/
alter table test1 add constraint check(num>0);
alter table test1 add constraint primary key(num);
alter table circles add constraint exclude using gist(c with &&);

/*非聚簇索引：以not cluster primary key约束条件，实现建立非聚簇索引的表*/
create table cluster_table1(id int not cluster primary key,name varchar(20));
/*默认不加not cluster语法所建立的同样是非聚簇索引的表*/
create table cluster_table1(id int primary key,name varchar(20));

/*序列，就是用create sequence创建的特殊的单行表*/
create sequence test_seq;
create table t4(id int);
insert into t4 values(test_seq.nextval);
select test_seq.nextval;
select nextval(test_seq);

/*在兼容模式下支持distinct与order by子查询逆序排序*/
select distinct * from(select id from test4 order by id desc);

/*case when语法*/
select case id when 1 then 'abc' when 2 then '123' else 'a1b2c3' end from t4;
select case when id=1 then 'abc' when id=2 then '123' else 'a1b2c3' end from t4;

/*delete不加from*/
delete test1 where num=3;
delete test1;

/*group by支持出现语义不明确的列*/
set sql_mode='';
set sql_mode='only_full_group_by';/*需要定义语义明确的列*/
select a,b from tab order by c;
select a,b from tab group by c;
select a,b from tab having d<5;
select a,b from tab group by a,e having e>5 order by b;
select distinct a,b from tab having c>4;
select distinct a,b from tab group by a having d>5 order by c;
select 1 as one from tab having 2<3;
select avg(1) from tab having 2<1;

/*insert all/first*/
create table sample(id varchar,data real,no int);
create table sampl1(id varchar,data real,no int);
create table sampl2(id varchar,data real,no int);
create table sampl3(id varchar,data real,no int);
insert into sample(id,data,no) values('111111', 11.11, 1);
insert into sample(id,data,no) values('222222', 22.22, 2);
insert all into sample1(id,data,no) values(id,data,no) select id,data,no from sample;/*无when条件*/
insert all when no<5 then into sampl1 values(id,data,no) when data<70 then into sample2 values(id,data,no) else into sample3 values(id,data,no) select * from sample;/*有when条件*/
/*insert first是考虑先后关系的，如果有数据满足第一个when条件又满足第二个when条件，则执行第一个then插入语句，第二个then就不插入第一个then已经插入过的数据了*/
insert frist when no<5 then into sample1 values(id,data,no) when data<70 then into sample2 values(id,data,no) else into sample3 values(id,data,no) select * from sample;

/*merge into:从一个或多个源中选择行以进行更新或插入到表或视图中，同时对多个需要更新的表进行更新、插入操作*/
merge into ta using(select aid,name,year from tb)tc on ta.id=tc.aid
when matched then update set ta.year=tc.year
when not matched then insert(id,name,year) values(tc.aid,tc.name,tc.year);
merge into ta using(select tb.aid,tb.name,tb.year from tb)tc on ta.id=tc.aid
when matched then update set ta.year=tc.year where aid=2;

/*q`转义字符：可以在输入特殊符合时不需要增加转义符号，q/Q+两个单引号+两个界定符*/
select q'!name like '%dbms_%'!';
select Q'*!名称类似'%dbms%%'!*';
insert into tab1 values(1,q'[!name like ''%dbms_%%!]');

/*rownum<3等价于limit2*/
select rownum,* from t4;
select rownum,* from t4 where rownum>1;
select rm from (select rownum as rm,id form t4 order by id desc)t;

/*storage,指定创建表的表空间*/
create tablesapce tblspc owner uxdb location'/home/uxdb/uxdbinstall/dbsql/bin/mysapce';
create table t_stroage(id int) storage(on tblspc,clusterbtr);
drop table t_storage;
drop tablespace tblspc;

/*top子句：top n 表示选择结果集的前n条记录，top n,m 表示选择第n条记录之后的m条记录*/
select top 1,3 * from test1;
select top 2,5 id from test1;
select top 3 id,name form test1;

/*update语句支持使用别名*/
update test1 as t1 set test1.col1=3,t1.col2='wangwu' where col1=3;
update test1 as t1 set test1.col1=3,t1.col2='wangwu' where t1.col1=3;
update test1 as t1 set test1.col1=3,t1.col2='wangwu' where test1.col1=3;/*failed，在提供了一个别名时，它会完全隐藏表的真实名称*/

/*约束管理：enable、disable、disable validate、disable novalidate、enable validate、enable novalidate*/
alter table test1 enable constraint constraint_name;
alter table test1 enable validate constraint cinstraint_name;

/*minus关键字使两个同类型结果集之间做差运算，返回位于第一个结果集但不在第二个结果集中的行*/
select * from test1 minus select * from test;
select * from test1 except select * from test;

/*systimestamp:返回timestamptz类型，该时间包含时区信息，精确到微秒*/
select systimestamp;
select systimestamp-systimestamp;

/*层次查询 with query：START WITH condition、CONNECT BY condition、ORDER SIBLINGS BY expression ...*/
create table player(keyid int,parent_keyid int,name varchar(16),salary int,sex varchar(4));
insert into player values(1,0,'zhangsan','1000000','f');
insert into player values(2,1,'lisi','50500','m');
insert into player values(3,1,'wangwu','60000','m');
insert into player values(4,1,'houzi','65000','m');
insert into player values(5,2,'maliu','30000','f');
insert into player values(6,2,'liuqi','25000','m');
insert into player values(7,4,'gouba','23000','m');
insert into player values(8,4,'dujiu','21000','f');
/*自上而下遍历*/
select keyid,parent_keyid,name,prior name,salary from player start with keyid=1 connect by prior keyid=parent_keyid;
/*自下而上遍历*/
select keyid,parent_keyid,name,salary from player start with keyid=6 connect by prior parent_keyid=keyid;

/*将数据库sql测试结果输出到文件里*/

vim test，将测试步骤粘贴进去并保存

./uxsql -p 5647 -W -f test > jj 2>&1

cat jj

/*dblink，数据库A去连接B*/

在A端进入集群目录，vim uxsinodb.conf,修改shared_preload_libraries='orafce,database_link'；重启集群，创建插件

create extension if not exists uxdb_fdw;

create extension if not exists database_link;

create schema if not exists uxdbc_oracle_syntax_placeholder_0058_sch01;

set search_path to uxdbc_oracle_syntax_placeholder_0058_sch01;

drop database link if exists uxdbc_oracle_syntax_placeholder_0058_link01;

B数据库所在虚拟机192.71.0.181、集群端口6503、密码1qaz!QAZ，B数据库集群存在UXDB用户,密码是1qaz!QAZ,并创建表xdual(id int,name varchar(10)),插入1条数据。

CREATE DATABASE link uxdbc_oracle_syntax_placeholder_0058_link01 CONNECT TO 'UXDB' IDENTIFIED BY '1qaz!QAZ' USING (connstr='192.71.0.181:6503/UXDB',dbtype='uxdb');

select name from xdual@uxdbc_oracle_syntax_placeholder_0058_link01 where id=1;