【Databend】数据库和表操作

数据库操作

基本语法：

-- 创建数据库
create database if not exists database_name;
-- 查看创建数据库语句
show create database database_name;
-- 使用数据库
use database_name;
-- 重命名数据库
alter database if exists old_database_name rename to new_database_name;
-- 查看所有数据库
show databases [like '<pattern>' | where <expr>];
-- 删除数据库
drop database if exists database_name;

示例：

-- 创建数据库
create database if not exists test;
-- 查看创建数据库语句
show create database test;
-- 使用数据库
use test;
-- 重命名数据库
alter database if exists test rename to test_db;
-- 查看所有数据库
show databases [like '<pattern>' | where <expr>];
-- 删除数据库
drop database if exists test;

SQL标识符：未引用和双引号的标识符

• 未引用的标识符以字母（A-Z、a-z）或下划线（“_”）开头，可能由字母、下划线、数字（0-9）或美元符号（“$”）组成。
• 双引号标识符可以包括广泛的字符，如数字（0-9）、特殊字符（如句号（.）、单引号（'）、感叹号（！）、符号（@）、数字符号（#）、美元符号（$）、百分比符号（%）、插入符号（^）和记号名称（&）、扩展 ASCII 和非 ASCII 字符以及空格。

Databend 允许您控制标识符的外壳敏感性。

• unquoted_ident_case_sensitive：设置为1时，此选项保留未引用标识符的字符大小写，确保它们区分大小写。如果保留在默认值0，未引用的标识符将保持不区分大小写，转换为小写。
• quoted_ident_case_sensitive：通过将此选项设置为0，您可以指示双引号标识符不应保留字符的大小写，使其不区分大小写。

双反引号（``）或双引号（"）是等价的。

数据表操作

创建表

基础语法：

-- 常规创建
create [transient] table [if not exists] [db.]table_name
(
    <column_name> <data_type> [ not null | null] [ { default <expr> }] [as (<expr>) stored | virtual],
    <column_name> <data_type> [ not null | null] [ { default <expr> }] [as (<expr>) stored | virtual],
    ...
);
-- 创建一个与现有表具有相同列定义的表
create table [if not exists] [db.]table_name like [db.]origin_table_name;
-- 通过查询结果创建表
create table [if not exists] [db.]table_name as select query_sql;

其中，transient 表示临时即创建临时表，stored 表示存储计算列，占用存储空间，virtual 表示虚拟计算列，不占用存储空间，访问时实时计算，类似于可视化工具 Power BI 和 Tableau 的度量值。

创建存储计算列的示例：每当“价格”或“数量”列的值更新时，“total_price”列将自动重新计算并更新其存储值。

create table if not exists products (
  id int,
  price float64,
  quantity int,
  total_price float64 as (price * quantity) stored
);

创建虚拟计算列的示例：“full_name”列是根据“first_name”和“last_name”列的当前值动态计算的。它不会占用额外的存储空间。每当访问“first_name”或“last_name”值时，将计算并返回“full_name”列。

create table if not exists employees (
  id int,
  first_name varchar,
  last_name varchar,
  full_name varchar as (concat(first_name, ' ', last_name)) virtual
);

在存储计算列和虚拟计算列之间进行选择时，请考虑以下因素：

• 存储空间：存储的计算列在表中占用了额外的存储空间，因为它们的计算值是物理存储的。如果您的数据库空间有限或希望最大限度地减少存储使用量，虚拟计算列可能是更好的选择。
• 实时更新：存储的计算列在更新依赖列时立即更新其计算值。这可以确保您在查询时始终拥有最新的计算值。另一方面，虚拟计算列在查询期间动态计算其值，这可能会略微增加处理时间。
• 数据完整性和一致性：存储的计算列保持即时的数据一致性，因为它们的计算值在写入操作时会更新。然而，虚拟计算列在查询期间实时计算其值，这意味着写入操作和后续查询之间可能存在短暂的不一致。

创建复制表示例：根据已有表复制。

create table if not exists employees_bak1 like employees;
create table if not exists employees_bak2 as select * from employees where 1=2;-- 复制表结构
create table if not exists employees_bak2 as select * from employees where 1=1;-- 复制表结构，并且克隆数据

创建临时表示例：一定时间或断开连接后自动删除。

-- 创建临时表
create transient table visits (
  visitor_id bigint
);

-- 录入数据
insert into visits values(1);
insert into visits values(2);
insert into visits values(3);

-- 查看数据
select * from visits;
+-----------+
| visitor_id |
+-----------+
|         1 |
|         2 |
|         3 |
+-----------+

删除表和恢复表

清空表语法和示例：

truncate table [db.]table_name;
truncate table test;

删除表基本语法：

drop table [if exists] [<database_name>.]<table_name> [all]

可选的“all”参数决定是否删除表的底层数据。

• 如果省略“all”，则仅从元数据服务中删除表模式，使数据保持不变。在这种情况下，您可以使用 undrop table 命令恢复表。
• 包括“all”将导致模式和基础数据的删除。虽然 undrop table 命令可以恢复模式，但它无法恢复表的数据。
  恢复表使用示例：

-- 创建表
create table test(a int, b varchar);
-- 录入数据
insert into test (a, b) values (1, 'example');
-- 删除表
drop table if exists test;
-- 查看数据
select * from test;
+---+-------+
| a |   b   |
+---+-------+
| 1 |example|
+---+-------+
-- 恢复表
undrop table test;
-- 查看恢复的数据
select * from test;
+---+-------+
| a |   b   |
+---+-------+
| 1 |example|
+---+-------+

查看表

查看所有或当前指定条件数据库中的表基本语法：

show [full] tables [{from | in} <database_name>] [history] [like '<pattern>' | where <expr>];

示例：

-- 查看当前数据库下的表
show tables;
-- 查看所有表
show full tables;
-- 查看当前数据库下的表，结果将包括仍在保留期内（默认为24小时）的被删除的表。
show tables history;
-- 查看指定条件的表
show tables like '%time';
show tables where data_size > 1000;

查看创建表的语法和示例：

show create table [database.]table_name;
-- 示例
show create table test;

查看当前或指定数据库中被删除的表语法和示例：

show drop tables [from <database_name>] [like '<pattern>' | where <expr>];
show drop tables from default_db;

查看表中列的信息语法和示例：

show fields from [<database_name>.]<table_name>;
desc [<database_name>.]<table_name>;
show [full] columns {from | in} tbl_name [{from | in} db_name] [like '<pattern>' | where <expr>];
-- 示例
show columns from books from default;

修改表

常见修改表的语法：

-- 重命名表
alter table [if exists] <name> rename to <new_table_name>;

-- 添加列到指定位置
alter table [if exists] [database.]<table_name>
add column <column_name> <data_type> [not null | null] [default <constant_value>] [first | after <column_name>]

-- 添加计算列
alter table [if exists] [database.]<table_name>
add column <column_name> <data_type> as (<expr>) stored | virtual;

-- 将计算列转换为常规列
alter table [if exists] [database.]<table_name>
modify column <column_name> drop stored;

-- 重命名列
alter table [if exists] [database.]<table_name>
rename column <column_name> to <new_column_name>;

-- 修改一列或多列数据类型
alter table [if exists] [database.]<table_name>
modify column <column_name> <new_data_type> [default <constant_value>][, column <column_name> <new_data_type> [default <constant_value>], ...]

示例：

-- 选择数据库
use test_db;
-- 创建表
create table if not exists employees (
  first_name varchar,
  last_name varchar,
  full_name varchar as (concat(first_name, ' ', last_name)) virtual
);
-- 重命名表名
alter table if exists employees rename to dim_employees;
-- 添加列
alter table dim_employees add column id int not null first;-- 位置为起始
alter table dim_employees add column email varchar null after last_name;-- 位置在其它列之后
alter table dim_employees add column age int after last_name;
-- 重命名列
alter table dim_employees rename column email to new_email;
-- 修改列
alter table products modify column full_name drop virtual,column age varchar(10) default '0';
-- 删除列
alter table dim_employees drop column new_email;

总结

Databend 的数据库和数据表操作跟简单的英语句子一样，特别好理解，与 Mysql 中的语法类似，只是有一些差异。业务可能随时发生调整，表结构操作语法还是要会，需要多练。

参考资料：

• Databend DDL：https://databend.rs/sql/sql-commands/ddl/table/