数据库基本语法 ¶

DDL 操作 ¶

数据库 ¶

新建数据库

create database [if not exists] <db_name> [in dbs_name] [with [buffered] log | with log mode ansi] [nlscasesensitive | nlscase insensitive];

重命名数据库

rename database <old_db_name> to <new_db_name>;

删除数据库

drop database [if exists] <db_name>;

DEMO

create database demo_db1;

database gbasedb;

rename database demo_db1 to demo_db2; 

drop database demo_db2;

表 ¶

新建表

create [standard | raw] table [if not exists] <table_name> (colname1 data_type1, colname2 data_type2, ...);

重命名表

rename table <old_table_name> to <new_table_name>;

删除表

drop table [if exists] <table_name>;

DEMO

create table t_user1(f_userid int, f_username varchar(20));

rename table t_user1 to t_user2;

drop table t_user2;

列 ¶

新增列

alter table <table_name | synonym_name> 
add (new_column_name1 data_type1 [before old_column_name1] [, new_column_name2 data_type2 [before old_column_name2], ...]);

删除列

alter table <table_name | synonym_name> drop (old_column_name1[, old_column_name2, ...]);

重命名列

rename column <table_name>.<old_column_name> to <new_column_name>;

DEMO

drop table if exists t_user;

create table t_user(f_userid int, f_username varchar(20));

insert into t_user values(1, 'gbasedbt');

select * from t_user;

alter table t_user add (f_nickname varchar(20));

select * from t_user;

rename column t_user.f_nickname to f_showname;

select * from t_user;

alter table t_user drop (f_showname);

select * from t_user;

视图 ¶

新建视图

create view [if not exists] <view_name> as <query_define>;

删除视图

drop view if exists <view_name>;

DEMO

create view vw_user as select * from t_user;

select * from vw_user;

drop view vw_user;

索引 ¶

新建索引

create [unique | distinct | cluster] index [if not exists] <idx_name> 
on <table_name | synonym_name> (<column_name | func_name(column_name)> [asc | desc]);

重命名索引

rename index <old_index_name> to <new_index_name>;

删除索引

drop index [if exists] <idx_name>;

DEMO

create unique index idx_user on t_user (f_userid asc);

rename index idx_user to idx_user01;

drop index idx_user01;

存储过程 ¶

什么是存储过程？

答：存储过程本质上就是一堆 SQL 语句按照一定的逻辑组合在一起，然后给它们取个名字，放在数据库里存起来。就好像把一组做菜的步骤写成了一个详细的菜谱，并且给这个菜谱起了个名字一样。

它一般由这么几个部分构成：

名字：就像人有名字一样，存储过程也有自己的名字，方便你之后能准确地找到它并调用它。比如上面书店例子里的 “查找库存低于 10 本书的指南” 就是个名字，在数据库里可能就是 FindLowStockBooks 之类的（只是举个例子哦）。

参数（可选）：有些时候，你做的操作可能需要根据不同的情况来变化。比如说，上面统计库存少的书，可能这个月你想看看库存低于 10 本的，下个月想看看低于 5 本的，那这个具体的数量就是个可变的条件呀。在存储过程里，就可以把这个可变的条件设成参数，像这样（以 MySQL 语法为例简单示意）：

新建存储过程

create procedure [if not exists] <procedure_name>(param1 data_type1, param2 data_type2, ...)
<spl code>
end procedure;

删除存储过程

drop procedure [if exists] <procedure_name> [(data_type1, data_type2, ...)];

DEMO

drop table if exists t_city;

create table t_city(f_cityid int, f_cityname varchar(20));

create procedure up_city_add(cityid int, cityname varchar(20))

insert into t_city(f_cityid, f_cityname) values(cityid, cityname);
end procedure;

call up_city_add(1, 'Beijing');
call up_city_add(2, 'Shanghai');
call up_city_add(3, 'Tianjin');
call up_city_add(4, 'Chongqing');

select * from t_city;

delete from t_city;

execute procedure up_city_add(1, 'Beijing');
execute procedure up_city_add(2, 'Shanghai');
execute procedure up_city_add(3, 'Tianjin');
execute procedure up_city_add(4, 'Chongqing');

select * from t_city;

函数 ¶

新建函数

create function [if not exists] <function_name>(param1 data_type1, param2 data_type2, ...)
returning data_type1 [as var1] [, data_type2 [as var2], ...]
<spl code>
return var1[, var2, ...];
end function;

删除函数

drop function [if exists] <function_name> [(data_type1, data_type2, ...)];

DEMO

drop function if exists fn_get_ymd;

create function fn_get_ymd(dt date)
returning int as year, int as month, int as day

define y,m,d int;

let y = year(dt);
let m = month(dt);
let d = day(dt);

return y,m,d;
end function;

execute function fn_get_ymd(today);

drop function fn_get_ymd;

同义词 ¶

新建同义词

create [public | private] synonym [if not exists] <synonym_name> for <table_name | view_name | sequence_name>;

删除同义词

drop synonym [if exists] <synonym_name>;

DEMO

drop table if exists t_user;

create table t_user(f_userid int, f_username varchar(20));

drop synonym if exists syn_user;

create synonym syn_user for t_user;

insert into syn_user values(1, 'gbasedbt');

select * from t_user;

select * from syn_user;

drop synonym syn_user;

触发器 ¶

触发器三要素

事件(Event)
对数据库的插入、删除、修改操作。
当声明的事件发生时，触发器开始工作。
条件(Condition)
当触发器被事件激活时，不是立即执行，而是首先由触发器测试触发条件。如果条件成立，则触发器执行相应的动作，否则触发器不做任何事情。
动作规则(Action Role)

新增触发器

create [or replace] tirgger <trigger_name> <insert | update [of column_name] | delete | select> on <target_table_name>
<before | after | for each row>
when <condition>
<action>

DEMO

cat trigger.sql

drop table if exists t_log;

create table t_log(f_logid serial, f_message varchar(50), f_operatedate date);

drop table if exists t_sale;

create table t_sale(f_saleid serial, f_productname varchar(20), f_qty int);

create or replace trigger trg_sale_insert insert on t_sale
referencing new as new_item
for each row 
(
insert into t_log(f_message, f_operatedate) values(concat('insert:', new_item.f_productname), today)
);


create or replace trigger trg_sale_update update of f_qty on t_sale
referencing old as old_item
for each row
(
insert into t_log(f_message, f_operatedate) values(concat('update: f_qty->', to_char(old_item.f_qty)), today)
);

create or replace trigger trg_sale_delete delete on t_sale
referencing old as old_item
for each row 
(
insert into t_log(f_message, f_operatedate) values(concat('delete:f_saleid->', to_char(old_item.f_saleid)), today)
);

create or replace trigger trg_sale_select select on t_sale
referencing old as old_item
for each row
(
insert into t_log(f_message, f_operatedate) values(concat('select:', old_item.f_productname), today)
);

!echo "insert action"

insert into t_sale(f_productname, f_qty) values('tv', 10);
insert into t_sale(f_productname, f_qty) values('a/c', 20);

!echo "search log"

select * from t_log;


!echo "update action"

update t_sale set f_qty = 15 where f_productname = 'tv';

!echo "search log"

select * from t_log;

!echo "select action"

select * from t_sale;

!echo "search log"

select * from t_log;

!echo "delete action"

delete from t_sale where f_productname = 'tv';

!echo "search log"

select * from t_log;

!echo "select action"

select * from t_sale;

!echo "search log"

select * from t_log;

dbaccess mydb trigger.sql

删除触发器

drop trigger <trigger_name>;

DEMO

drop trigger if exists trg_sale_insert;
drop trigger if exists trg_sale_update;
drop trigger if exists trg_sale_delete;
drop trigger if exists trg_sale_select;

DML 操作 ¶

insert ¶

insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] values(value1, value2, ...);

insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] select col_name1, col_name2, ... ;

insert into <table_name | view_name | synonym_name> [column_name1, column_name2, ...] execute function <fn_name([param1, param2, ...])>;

DEMO

drop table if exists t_user1;

create table t_user1(f_userid int, f_username varchar(20));

insert into t_user1 values(1, 'gbasedbt');

select * from t_user1;

drop table if exists t_user2;

create table t_user2(f_userid int, f_username varchar(20));

insert into t_user2 select * from t_user1;

select * from t_user2;

drop table if exists t_user3;

create table t_user3(f_userid int, f_username varchar(20));

drop function if exists fn_user_add;

create function fn_user_add(user_num int)
returning int as userid, varchar(20) as username

define i int;
define userid int;
define username varchar(20);

for i = 1 to user_num
    let userid = i;
    let username = concat('user_', to_char(i));

    return userid, username with resume;
end for;

end function;

insert into t_user3 execute function fn_user_add(10);

select * from t_user3;

update ¶

update <table_name | view_name | synonym_name> set column_name1 = value1[, column_name2 = value2, ... ] [where condition];

DEMO

drop table if exists t_user;

create table t_user(f_userid int, f_username varchar(50), f_age int);

insert into t_user values(1, 'Mary', 18);
insert into t_user values(2, 'Jack', 21);

select * from t_user;

update t_user set f_age = 20 where f_userid = 1;

select * from t_user;

delete ¶

delete from <table_name | view_name | synonym_name> [where condition];

DEMO

select * from t_user;

delete from t_user where f_userid = 1;

select * from t_user;

merge ¶

merge into <target_table_name> as t
using <source_table_name | source_query> as s
on t.column_name1 = s.column_name2
when matched then update set t.col_1 = s.col_1, t.col_2 = s.col_2, ...
when not matched then insert (t.col_1, t.col_2, ...) values(s.col_1, s.col_2, ...);

DEMO

drop table if exists t_user1;

create table t_user1(f_userid int, f_username varchar(20), f_age int);

insert into t_user1 values(1, 'Tom', 28);
insert into t_user1 values(2, 'Jack', 26);
insert into t_user1 values(4, 'Rose', 18);

select * from t_user1;

drop table if exists t_user2;

create table t_user2(f_userid int, f_username varchar(20), f_age int);

insert into t_user2 values(3, 'Jim', 25);
insert into t_user2 values(4, 'Rose', 23);
insert into t_user2 values(5, 'Mike', 21);
insert into t_user2 values(6, 'Bill', 19);

select * from t_user2;

merge into t_user1 a
using t_user2 b
on a.f_userid = b.f_userid
when matched then update set a.f_age = b.f_age
when not matched then insert (a.f_userid, a.f_username, a.f_age) values(b.f_userid, b.f_username, b.f_age);

select * from t_user1;

DQL 操作 ¶

数据准备 ¶

drop table if exists t_dept;

create table t_dept(f_deptid int, f_deptname varchar(10), f_parentid int);

insert into t_dept values(0, 'MS', -1);
insert into t_dept values(1, 'Dev', 0);
insert into t_dept values(2, 'Test', 1);
insert into t_dept values(3, 'Market', 0);
insert into t_dept values(4, 'HR', 0);

drop table if exists t_employee;

create table t_employee(f_employeeid int, f_deptid int, f_employeename varchar(10), f_salary money);

insert into t_employee values(1, 1, 'Tom', 6000.00);
insert into t_employee values(2, 1, 'Jack', 8000.00);
insert into t_employee values(3, 1, 'Mary', 6600.00);

insert into t_employee values(4, 2, 'Henry', 5000.00);
insert into t_employee values(5, 2, 'Rose', 7500.00);
insert into t_employee values(6, 5, 'Bill', 6500.00);


insert into t_employee values(7, 3, 'Kate', 5000.00);
insert into t_employee values(8, 3, 'Bob', 9000.00);

单表查询 ¶

select [first n] <* | column_name1[, column_name2, ...]> from <table_name>;

select <column_name1[, column_name2, ...], aggr_func1(column_name_n1)[, aggr_func2(column_name_n2), ...]>
from <table_name>
group by column_name_n1[, column_name_n2, ...]
[order by column_name_m1 [asc | desc][, column_name_m2  [asc | desc], ...]];

DEMO

select first 3 * from t_employee;

select f_deptid, max(f_salary) as f_salary from t_employee group by f_deptid order by f_salary desc;

多表关联查询 ¶

自连接 ¶

select * from <table_name> a, <table_name> b where a.f_column_name1 = b.f_column_name2;

DEMO

select a.*, b.f_deptname as f_parentname from t_dept a, t_dept b where a.f_parentid = b.f_deptid;

内连接 ¶

selct table_name1.column_name1, table_name1.column_name2, ... ,
      table_name2.column_name1, table_name2.column_name2, ... 
from <table_name1> a
inner join <table_name2> b
on a.column_name1 = b.column_name2;

DEMO

select a.f_employeeid, a.f_employeename, b.f_deptname
from t_employee a
inner join t_dept b
on a.f_deptid = b.f_deptid;

功能：从 t_employee 表和 t_dept 表中查询员工编号、员工姓名和员工所在部门名称，通过内连接仅返回两表中满足连接条件（员工所在部门编号与部门表中部门编号相等）的记录。
特点：能准确获取员工与所属部门的对应关系，只展示有部门分配的员工信息，适用于需要精确匹配员工和部门数据的场景，如生成员工所属部门详细报表等。

左连接 ¶

selct table_name1.column_name1, table_name1.column_name2, ... ,
      table_name2.column_name1, table_name2.column_name2, ... 
from <table_name1> a
left outer join <table_name2> b
on a.column_name1 = b.column_name2;

DEMO

select a.f_employeeid, a.f_employeename, b.f_deptname
from t_employee a
left outer join t_dept b
on a.f_deptid = b.f_deptid;

功能：查询结果包含 t_employee 表中的所有员工记录以及对应的部门名称。若员工所在部门在 t_dept 表中存在匹配记录，则正常显示部门名称；若不存在匹配记录，部门名称列显示为 NULL。
特点：保证了员工信息的完整性，即使员工未分配部门或部门信息缺失，也能在结果中体现员工记录，可用于全面了解员工及其可能的部门归属情况，例如在统计员工分布时，不遗漏未明确部门的员工数据。

右连接 ¶

selct table_name1.column_name1, table_name1.column_name2, ... ,
      table_name2.column_name1, table_name2.column_name2, ... 
from <table_name1> a
right outer join <table_name2> b
on a.column_name1 = b.column_name2;

DEMO

select a.f_employeeid, a.f_employeename, b.f_deptname
from t_employee a
right outer join t_dept b
on a.f_deptid = b.f_deptid;

功能：查询结果包含 t_dept 表中的所有部门记录以及对应的员工信息（若有）。若部门有员工与之关联，则显示员工编号和姓名；若部门暂无员工，员工编号和姓名列显示为 NULL。
特点：突出部门信息的完整性，适用于关注部门整体情况及可能的员工配置情况，如分析部门人员配置现状或规划部门人员需求时，确保所有部门都在结果中展示，无论是否已有员工。

全连接 ¶

selct table_name1.column_name1, table_name1.column_name2, ... ,
      table_name2.column_name1, table_name2.column_name2, ... 
from <table_name1> a
full outer join <table_name2> b
on a.column_name1 = b.column_name2;

DEMO

select a.f_employeeid, a.f_employeename, b.f_deptname
from t_employee a
full outer join t_dept b
on a.f_deptid = b.f_deptid;

功能：返回 t_employee 表和 t_dept 表中所有记录的组合，无论记录在另一个表中是否有匹配项。若员工所在部门在 t_dept 表中无匹配，部门名称列显示为 NULL；若部门在 t_employee 表中无匹配员工，员工编号和姓名列显示为 NULL。
特点：全面展示员工与部门之间的所有关联情况，涵盖员工未分配部门、部门暂无员工以及正常匹配的各种情形，可用于进行全面的人力资源和组织架构数据分析，提供最完整的数据视角。

子查询 ¶

IN ¶

select *
from <table_name1>
where column_name1 in
(select column_name2 from <table_name2>;

select *
from <table_name1>
where column_name1 in <(val1, val2, ...)>;

DEMO

select * from t_employee where f_deptid in (select f_deptid from t_dept);

select * from t_employee where f_deptid in (3, 5);

功能：从 t_employee 表中筛选出所属部门编号在 t_dept 表中存在的员工记录，即确保员工所属部门在部门表中有定义。
特点：通过子查询动态获取部门编号集合来进行筛选，保证了员工数据与部门数据的一致性关联，常用于数据完整性检查和关联数据查询，避免出现员工所属部门无效的情况。

EISYTS ¶

select *
from <table_name1>
where exists (select 1 from <table_name2> where table_name1.column_name1 = table_name2.column_name2)

DEMO

 select *
from t_employee a
where exists (select 1 from t_dept b where b.f_deptid = a.f_deptid);

功能：查询 t_employee 表中所有在 t_dept 表中存在对应部门的员工记录，通过 EXISTS 子句判断子查询是否返回结果来确定员工记录是否满足条件。
特点：同样确保了员工与部门数据的有效关联，关注的是员工记录是否有对应的有效部门，常用于数据完整性校验和基于关联关系的查询，以获取准确的员工数据子集用于进一步分析或处理。

总结 ¶

存储过程（Stored Procedure）

定义：

存储过程是一组为了完成特定功能的 SQL 语句集合，它经过编译后存储在数据库中。就像是一个预先包装好的数据库操作 “程序”，可以接受输入参数，执行一系列复杂的数据库操作（如查询、插入、更新、删除等），并可能返回输出结果。

优点：

性能提升：存储过程在数据库中预编译，当被调用时，不需要像普通 SQL 语句那样每次都进行语法分析和编译，减少了执行时间，尤其对于复杂的多表联合查询和频繁执行的操作，性能优势明显。例如，一个包含多个子查询和连接操作的报表生成查询，写成存储过程后，每次运行可以更快地得到结果。
安全性增强：可以通过权限控制用户对存储过程的访问，用户不需要直接访问底层表结构和数据，而是通过执行存储过程来完成特定任务，从而防止用户对数据的误操作或恶意操作。例如，只允许特定用户通过存储过程来更新敏感的数据表。
代码复用和维护方便：业务逻辑集中在存储过程中，当业务规则发生变化时，只需要修改存储过程内部的代码，而不用在多个应用程序中查找和修改相关 SQL 语句。例如，公司的折扣计算规则改变，只需更新存储过程中的计算逻辑，所有调用该存储过程的应用程序都能适应新规则。
减少网络流量：当应用程序和数据库服务器不在同一位置时，应用程序可以通过调用存储过程，让数据库在服务器端执行复杂操作，仅将最终结果返回给客户端，减少了网络传输的数据量。比如，一个分布式系统中，远程客户端只需要请求执行存储过程获取汇总数据，而不是传输大量原始数据和复杂查询语句。

缺点：

可移植性差：不同数据库系统（如 MySQL、Oracle、SQL Server 等）的存储过程语法差异较大，在一个数据库中编写的存储过程很难直接移植到其他数据库系统中。例如，MySQL 的存储过程语法和 Oracle 的存储过程语法在变量定义、游标使用等方面有诸多不同。
调试困难：存储过程通常是在数据库服务器内部执行，相对于应用程序代码，其调试工具和调试过程相对复杂。如果存储过程出现错误，定位和修复问题可能比较耗时，尤其是在复杂的逻辑和大量代码的情况下。
增加数据库负担：存储过程存储在数据库服务器中，如果存储大量复杂的存储过程，可能会占用较多的数据库服务器资源，如存储内存、CPU 时间等，尤其在服务器性能有限的情况下，可能影响数据库的整体性能。
索引（Index）

定义：

索引是一种数据库对象，它是对数据库表中一列或多列的值进行排序的数据结构。就像是一本书的目录，通过索引可以快速定位和访问表中的数据，而不需要全表扫描。例如，在一个学生成绩表中，对学生学号建立索引后，当查询某个学生的成绩时，可以根据学号索引快速找到对应的记录，而不是遍历整个成绩表。

优点：

提高查询速度：在对大型表进行查询操作时，索引可以显著提高查询效率。例如，在一个包含百万条记录的用户信息表中，通过对用户姓名建立索引，在搜索特定用户时，可以快速定位到符合条件的记录，大大减少查询时间。
加速排序和分组操作：索引本身是按照一定顺序存储的数据结构，在进行排序（ORDER BY）和分组（GROUP BY）操作时，如果查询条件涉及到索引列，可以利用索引的顺序性，减少数据的排序和分组时间。例如，在按日期对销售订单进行分组统计时，对日期列建立索引可以加速分组操作。
唯一约束实现：唯一索引可以确保表中某一列或多列组合的值具有唯一性，用于实现数据的完整性约束。例如，在用户登录表中，对用户名建立唯一索引，可以保证用户名的唯一性，防止重复注册。

缺点：

增加存储开销：索引本身需要占用一定的存储空间，尤其是对于大型表和多列索引，存储索引数据可能会占用大量的磁盘空间。例如，一个包含大量文本内容的文档表，如果对多个字段建立索引，索引文件可能会变得非常庞大。
降低数据更新性能：在对表中的数据进行插入、更新、删除操作时，数据库需要同时维护索引的正确性，这会增加这些操作的时间成本。例如，频繁插入新记录到一个有多个索引的表中，每次插入都需要更新索引结构，导致插入操作变慢。
索引维护成本：随着数据的频繁更新，索引可能会变得碎片化，需要定期进行维护（如重建或重组索引），这会消耗数据库的资源和时间。例如，在一个高并发的在线交易系统中，随着交易数据的不断更新，索引的维护可能成为系统性能的瓶颈之一。
视图（View）

定义：

视图是从一个或多个表（或其他视图）中导出的虚拟表，它本身不存储数据，而是根据定义视图的查询语句动态地从基础表中获取数据。就像是一个窗口，通过这个窗口可以看到基础表中的部分数据，并且可以按照特定的方式进行组合和展示。例如，创建一个视图，它从员工表和部门表中获取员工姓名和所属部门名称，这样用户通过这个视图可以方便地查看员工和部门的关联信息。

优点：

简化复杂查询：可以将复杂的多表联合查询封装成一个简单的视图，用户只需要查询视图就可以获取想要的数据，而不需要了解复杂的底层表结构和关联关系。例如，对于一个涉及客户订单、产品、客户信息的复杂业务场景，创建一个视图来展示每个客户的订单总额，用户查询这个视图就能快速得到所需信息。
增强数据安全性：通过视图可以控制用户对数据的访问，只让用户看到视图中定义的部分数据，而隐藏其他敏感信息。例如，在员工工资表中，创建一个视图，只显示员工姓名和基本工资，隐藏奖金、津贴等敏感信息，这样不同权限的用户可以访问不同视图，保护了数据的安全性。
数据独立性：视图提供了一种抽象层，使得应用程序和数据库表结构之间具有一定的独立性。当基础表结构发生变化时，只要视图的定义能够适应这种变化，对使用视图的应用程序影响较小。例如，在数据库表进行字段添加或删除操作后，只要调整视图的定义，应用程序通过视图获取数据的部分可能不需要修改。

缺点：

性能问题：由于视图是基于基础表动态查询生成的，如果视图的定义涉及复杂的查询操作，每次查询视图时都要重新执行这些操作，可能会导致性能下降。特别是在大数据量和频繁查询的情况下，性能问题可能更加明显。例如，一个视图是通过多个大型表的嵌套子查询和连接操作定义的，查询这个视图可能会消耗大量的时间和资源。
更新限制：不是所有的视图都可以进行更新操作，只有满足一定条件（如简单视图，基于单表且不包含聚合函数、分组等操作）的视图才能进行更新。对于复杂视图，可能无法直接通过视图来更新基础表的数据，这在一定程度上限制了视图的使用。例如，一个视图是通过对多个表进行聚合和分组操作得到的统计信息视图，就无法直接通过这个视图更新基础表中的原始数据。

https://www.gbase.cn/community/post/1458

数据库 基本语法 ¶

DDL 操作 ¶

数据库 ¶

表 ¶

列 ¶

视图 ¶

索引 ¶

存储过程 ¶

函数 ¶

同义词 ¶

触发器 ¶

DML 操作 ¶

insert ¶

update ¶

delete ¶

merge ¶

DQL 操作 ¶

数据准备 ¶

单表查询 ¶

多表关联查询 ¶

自连接 ¶

内连接 ¶

左连接 ¶

右连接 ¶

全连接 ¶

子查询 ¶

IN ¶

EISYTS ¶

总结 ¶

数据库基本语法 ¶