神通数据库常用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;
原文地址：https://blog.csdn.net/u010479989/article/details/143058633
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！