达梦数据库（DM8）常用SQL学习

达梦产品手册
1.检查数据库版本及服务状态

1.1 检察达梦数据库运行状态

1. SELECT status$ as 状态 FROM v$instance;

复制代码

1.2 检察达梦数据库版本

1. SELECT banner as 版本信息 FROM v$version;

复制代码

2.创建用户并授权

2.1 创建用户

1. -- 使用 CREATE USER 语句创建 DM 用户，登录密码为 "dameng123"。
2. CREATE USER DM IDENTIFIED BY "dameng123";

复制代码

2.2 授予用户根本权限

1. -- 使用GRANT语句给DM用户授予RESOURCE角色
2. GRANT RESOURCE TO DM;
3. -- 给 DM 用户授予 dmhr 用户下 employee 表的 select 权限；
4. GRANT SELECT ON dmhr.employee TO DM;
5. -- 给 DM 用户授予 dmhr 用户下 department 表的 select 权限；
6. GRANT SELECT ON dmhr.department TO DM;
7. -- 给 DM 用户授予 创建表的权限
8. GRANT CREATE TABLE to DM;

复制代码

2.3 检察用户信息

1. -- 通过字典表 dba_users 查看基本信息
2. SELECT username,account_status,created FROM dba_users WHERE username='DM';

复制代码

从输出效果看出，用户状态 account_status 为 open，输出的时间为用户的创建时间。

2.4 切换用户

1. -- 使用 conn 命令切换用户。
2. conn DM/dameng123;

复制代码

留意：windows体系在DM管理工具中使用该命令无法完成用户切换，直接断开登录后重新连接，更换登录用户即可。

1. -- 使用 user 关键字返回当前登录用户。
2. SELECT user FROM DUAL;

复制代码

3. 表相关

3.1创建表并添加约束

1. -- 创建employee表
2. CREATE TABLE employee
3. (
4.   employee_id INTEGER,
5.   employee_name VARCHAR2(20) NOT NULL,
6.   hire_date DATE,
7.   salary INTEGER,
8.   department_id INTEGER NOT NULL
9. );
10. -- 使用 CREATE TABLE 语句创建 department 表
11. CREATE TABLE department
12. (
13.   department_id INTEGER PRIMARY KEY,
14.   department_name VARCHAR(30) NOT NULL
15. );
16. -- 非空约束
17. ALTER TABLE employee MODIFY( hire_date not null);
18. -- 主键约束
19. ALTER TABLE employee ADD constraint pk_empid PRIMARY KEY(employee_id);
20. -- 外键约束
21. ALTER TABLE employee ADD constraint fk_dept FOREIGN KEY (department_id) REFERENCES department (department_id);

复制代码

3.2 检察表主键外键

1. SELECT table_name, constraint_name, constraint_type FROM all_constraints WHERE owner='DM' AND table_name='EMPLOYEE';

复制代码

3.3 表字段增长

1. ALTER TABLE "DM"."EMPLOYEE" add "IS_REPEAT" VARCHAR(255);
2. COMMENT ON COLUMN "DM"."EMPLOYEE"."IS_REPEAT" IS '是否重复，1、是，0、否';

复制代码

3.4 表字段删除

1. ALTER TABLE "DM"."EMPLOYEE" DROP COLUMN IS_REPEAT;

复制代码

4. 验证数据表 CRUD 功能

1. -- 插入数据
2. INSERT INTO department VALUES(666, '数据库产品中心');
3. INSERT INTO employee VALUES (9999, '王达梦','2008-05-30 00:00:00', 30000, 666);
4. commit;
5. -- 因为 employee 员工表和 department 部门表存在主外键约束，所以此示例中须按顺序执行插入语句，即先在 department 表中插入数据。
7. -- 更新数据
8. UPDATE employee SET salary='35000' WHERE employee_id=9999;
9. commit;
11. -- 验证数据
12. SELECT salary,employee_id FROM employee;
14. -- 删除数据
15. DELETE FROM employee;
16. DELETE FROM department WHERE department_id=666;
17. commit;
19. -- 验证数据
20. SELECT * FROM employee;

复制代码

5. 批量插入及选择排序

1. -- 在 t1 表中批量插入 100000 条数据记录。
2. CREATE TABLE t1 AS
3.      SELECT rownum AS id,
4.        trunc(dbms_random.value(0, 100)) AS random_id,
5.        dbms_random.string('x', 20) AS random_string
6.      FROM dual
7.      connect BY level <= 100000;
8. -- 使用 COUNT(*) 聚集函数统计 t1 表中的总数据记录。
9. SELECT COUNT(*) FROM t1;
10. -- 使用 ORDER BY 语句实现选择排序。
11. SELECT * FROM t1 where rownum<5 ORDER BY id DESC;

复制代码

6.验证分组查询

1. -- 插入准备数据
2. INSERT INTO department (department_id, department_name)
3.      SELECT department_id, department_name FROM dmhr.department;
4. INSERT INTO employee
5.      (employee_id, employee_name, hire_date, salary, department_id)
6.      SELECT employee_id, employee_name, hire_date, salary,
7.      department_id FROM dmhr.employee;
8. commit;
9. SELECT dept.department_name as 部门, count(*) as 人数
10.      FROM employee emp, department dept
11.      where emp.department_id=dept.department_id
12.      GROUP BY dept.department_name
13.      HAVING count(*) > 20;

复制代码

7.创建视图

1. -- 定义视图
2. CREATE OR replace VIEW v1 AS
3.      SELECT dept.department_name, emp.employee_name,
4.      emp.salary,emp.hire_date
5.      FROM employee emp, department dept
6.      WHERE salary > 10000
7.      AND hire_date >= '2013-08-01'
8.      AND emp.department_id = dept.department_id;
9. -- 通过视图简化查询
10. SELECT * FROM v1 WHERE hire_date > '2014-09-01';

复制代码

8.索引

1. -- 创建普通索引
2. CREATE INDEX ind_emp_salary ON employee(salary);
3. SELECT table_name, index_name, index_type
4.      from user_indexes WHERE index_name='IND_EMP_SALARY';
5. -- 删除索引
6. DROP INDEX IND_EMP_SALARY;

复制代码

9.创建表

1. -- 若该表存在则删除
2. DROP TABLE IF EXISTS TEST_TEMP;
3. -- 创建对应的表：
4. -- 主键自增配置  IDENTITY(1, 1)，CLUSTER PRIMARY KEY("ID")
5. -- 自动创建时间  DEFAULT (CURDATE)
6. CREATE TABLE "TEST_TEMP"
7. (
8. "ID" INT IDENTITY(1, 1) NOT NULL,
9. "PROJECT_KEY"         VARCHAR(100),
10. "CREAT_TIME"         DATE                         DEFAULT (CURDATE),
11. CLUSTER PRIMARY KEY("ID"))
12. STORAGE(ON "TEST", CLUSTERBTR) ;

复制代码

9.1 批量插入数据

1. -- 语法
2. SET IDENTITY_INSERT [<模式名>.]<表名> ON | OFF;
3. -- 初始化数据，自增id赋值。需要指定列且IDENTITY_INSERT设置ON
4. SET IDENTITY_INSERT SYSDBA.TEST_TEMP ON;
5. INSERT INTO TEST_TEMP(ID,PROJECT_KEY,CREAT_TIME) VALUES (1, '测试项目', '2022-12-15 14:15:30');
6. -- 记得提交，否则无法真正的落库。
7. commit;

复制代码

9.2 数据库主动新增日期

1. -- 自动生成日期，不带时分秒
2. "CREAT_TIME"         DATE                         DEFAULT (CURDATE),
3. -- 自动生成日期，带时分秒
4. "CREAT_TIME"         TIMESTAMP                 DEFAULT (SYSDATE),

复制代码

9.3 数据库主动更新时间

通过触发器脚本实现

1. create trigger UPDATE_TIME_CUSTOM_NAME
2. before update on "DEV"."CUSTOM_NAME" for each row
3. begin
4. new.UPDATE_TIME:=sysdate;
5. end;

复制代码

分析
UPDATE_TIME_CUSTOM_NAME：触发名称
DEV： 模式名
CUSTOM_NAME：表名称
UPDATE_TIME：字段名称
– 字段配置，主动天生日期，带时分秒
“UPDATE_TIME” TIMESTAMP DEFAULT (SYSDATE),
10. 查询表中的字段信息

1. -- SYSDBA为模式，TEST_TEMP 为表名称
2. -- 查询表字段情况，但不包含注释信息
3. select * from all_tab_columns where owner='SYSDBA' and Table_Name='TEST_TEMP'

复制代码

1. -- 查询表字段的注释信息
2. select * from dba_col_comments  where owner='SYSDBA' and Table_Name='TEST_TEMP'

复制代码

11.修改字段

11.1 修改字段名称及解释

1. -- 将SYSDBA模式的TEST_TEMP表中的UPDATE_TIME字段名称修改为CREATE_TIME
2. ALTER TABLE  "SYSDBA"."TEST_TEMP"  RENAME COLUMN "UPDATE_TIME" TO "CREATE_TIME";
3. COMMENT ON COLUMN "SYSDBA"."TEST_TEMP"."CREATE_TIME" IS '数据创建时间';

复制代码

11.2 修改字段范例

1. -- 将SYSDBA模式的TEST_TEMP表中的CREATE_TIME 字段类型修改为TIMESTAMP DEFAULT SYSDATE
2. ALTER TABLE "SYSDBA"."TEST_TEMP" MODIFY CREATE_TIME TIMESTAMP DEFAULT SYSDATE

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。