trino常用语法和官方sql手册

trino常用语法
trino数据库造简单表的语法模板
=没有分区的sql 简单创建、查询、插入===========
create table hive.youyou030310.even(
id int,
event_type varchar);
insert into hive.youyou030310.even(id,event_type)
values (1,‘点击’),(1,‘查看商品’),(1,‘购物’),(2,‘点击’),(2,‘查看商品’),(3,‘点击’);
select * from hive.youyou030310.even
=带分区的sql的创建、查询、插入=
–创建有分区的sql
create table hive.youyou030310.name(
id int comment ‘ID’,
name varchar comment ‘姓名’,
ds varchar comment ‘分区’
)comment ‘姓名表’ with(format =‘ORC’,partitioned_by=array[‘ds’]);
–查询姓名表内容
select * from hive.youyou030310.name;
–查询姓名表格式
desc hive.youyou030310.name;
–insert插入姓名表有分区的值
insert into hive.youyou030310.name(id,name,ds)
values(1,‘张三’,‘20200407’)
,(2,‘李四’,‘20200407’)
,(3,‘王五’,‘20200407’),(4,‘王莉’,‘20200407’),(5,‘王凯’,‘20200407’),
(6,‘王德’,‘20200407’),(7,‘李东’,‘20200407’),(8,‘张梅’,‘20200407’),(9,‘李科’,‘20200407’),
(10,‘何辉’,‘20200407’);
insert into hive.youyou030310.name(id,name,ds)
values(1,‘张研’,‘20200406’);
insert into hive.youyou030310.name(id,name,ds)
values(11,‘张三’,‘20200406’)
,(2,‘李四’,‘20200406’)
,(3,‘王五’,‘20200406’),(4,‘王莉’,‘20200406’),(5,‘王凯’,‘20200406’),
(6,‘王德’,‘20200406’),(7,‘李东’,‘20200406’),(8,‘张梅’,‘20200406’),(9,‘李科’,‘20200406’),
(10,‘何辉’,‘20200406’);
–删除表–
drop table hive.youyou030310.name01;
–查看所有表
use hive.youyou030310;
show tables
—查看最大有分区的内容
select max(ds) from hive.youyou030310.name;
select max(ds) from hive.youyou030310.name;
–联查查看最大有分区的内容
select * from hive.youyou030310.name where ds in(select max(ds) from hive.youyou030310.name);
–创建event有分区
create table hive.youyou030310.even1(
id int comment ‘主键id’,
event_type varchar comment ‘event类型’,
ds varchar comment ‘分区’
)comment ‘event表’ with(format =‘ORC’,partitioned_by=array[‘ds’]);
–插入event有分区
insert into hive.youyou030310.even1(id,event_type,ds)
values (1,‘点击’,‘20200406’),(1,‘查看商品’,‘20200406’),(1,‘购物’,‘20200406’),(2,‘点击’,‘20200406’),(2,‘查看商品’,‘20200406’),(3,‘点击’,‘20200406’);
insert into hive.youyou030310.even1(id,event_type,ds)
values (1,‘点击’,‘20200407’),(1,‘查看商品’,‘20200407’),(1,‘购物’,‘20200407’),(2,‘点击’,‘20200407’),(2,‘查看商品’,‘20200407’),(3,‘点击’,‘20200407’);
– 查看event1表
select * from hive.youyou030310.even1;

sql的常用语法

1、时间怎么表示

select * from hive.youyou030310.ads_basicinfo_df a
where a.create_time >timestamp'2021-01-01 00:00:00'

时间点的大于、等于、小于sql展示
timestamp '2021-01-01 00:00:00

2、Trino官方sql手册

https://trino.io/docs/current/