神通数据库常用sql和基本操作

创建schema

-- 创建schema

create schema test_schema;

查询所有schema

SELECT NSPNAME FROM sys_namespace

查询所有表

SELECT RELNAME FROM sys_class WHERE RELKIND='r'

-- 查询指定模式下的表

SELECT A.RELNAME FROM sys_class A,sys_namespace B WHERE A.RELNAMESPACE = B.OID AND A.RELKIND='r' AND B.NSPNAME='SYSDBA'

创建表

-- 创建表

CREATE TABLE TEST_USER

(

ID **integer** **NOT** **NULL** AUTO_INCREMENT,

USER_NAME **character** **varying**(510) ,

AGE **integer** ,

GENDER **integer** ,

DESCRIPTION **character** **varying**(510) ,

CREATE_DATE **timestamp** **without** **time** **zone** **DEFAULT** (**NOW**())::**timestamp**(6) **without** **time** **zone** ,

**CONSTRAINT** TEST_USER_PKEY **PRIMARY** **KEY** (ID)

) AUTO_INCREMENT=1

新增字段

-- 增加字段

alter table TEST_USER add column t_column2 varchar(255);

字段改名

ALTER TABLE TEST_USER rename column t_column2 TO t_column3;

删除字段

ALTER TABLE TEST_USER DROP t_column3 CASCADE;

查询字段信息

SELECT

A.*

FROM v_sys_columns A WHERE A.TABLE_SCHEM='SYSDBA' AND A.TABLE_NAME='TEST_USER' AND A.ORDINAL_POSITION>=0

插入数据

INSERT INTO TEST_USER

(ID, USER_NAME, AGE, GENDER, DESCRIPTION, CREATE_DATE)

VALUES(1, 'zhangsan', 18, 1, NULL, '2024-10-11 12:00:00.000');

INSERT INTO TEST_USER

(ID, USER_NAME, AGE, GENDER, DESCRIPTION, CREATE_DATE)

VALUES(2, 'lisi', 19, 2, NULL, '2024-10-18 12:53:00.000');

INSERT INTO TEST_USER

(ID, USER_NAME, AGE, GENDER, DESCRIPTION, CREATE_DATE)

VALUES(3, 'wangwu', 20, 1, NULL, '2024-10-18 13:00:00.000');

查询数据

修改数据

UPDATE TEST_user SET USER_NAME = 'zhangsan1' WHERE id = 1;

删除数据

DELETE FROM TEST_USER WHERE id = 1;

创建用户并授权

--创建用户

create user user_t1 with PASSWORD '123456';

--查询用户

select username from dba_users

--授予权限：

grant all privileges on schema test_schema to user_t1;

--修改密码

alter USER user_t1 with password '123456789'

--收回权限

REVOKE all privileges ON SCHEMA test_schema FROM user_t1;

--删除用户

DROP USER user_t1;