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

达梦产品手册

1.检查数据库版本及服务状态

1.1 查看达梦数据库运行状态

SELECTstatus$ as 状态 FROM v$instance;

windows上DM8执行结果

1.2 查看达梦数据库版本

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

windows上DM8执行结果

2.创建用户并授权

2.1 创建用户

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

2.2 授予用户基本权限

-- 使用GRANT语句给DM用户授予RESOURCE角色GRANT RESOURCE TO DM;-- 给 DM 用户授予 dmhr 用户下 employee 表的 select 权限；GRANTSELECTON dmhr.employee TO DM;-- 给 DM 用户授予 dmhr 用户下 department 表的 select 权限；GRANTSELECTON dmhr.department TO DM;-- 给 DM 用户授予 创建表的权限GRANTCREATETABLEto DM;

2.3 查看用户信息

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

从输出结果看出，用户状态 account_status 为 open，输出的时间为用户的创建时间。
在这里插入图片描述

2.4 切换用户

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

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

-- 使用 user 关键字返回当前登录用户。SELECTuserFROM DUAL;

3. 表相关

3.1创建表并添加约束

-- 创建employee表CREATETABLE employee
(
  employee_id INTEGER,
  employee_name VARCHAR2(20)NOTNULL,
  hire_date DATE,
  salary INTEGER,
  department_id INTEGERNOTNULL);-- 使用 CREATE TABLE 语句创建 department 表CREATETABLE department
(
  department_id INTEGERPRIMARYKEY,
  department_name VARCHAR(30)NOTNULL);-- 非空约束ALTERTABLE employee MODIFY( hire_date notnull);-- 主键约束ALTERTABLE employee ADDconstraint pk_empid PRIMARYKEY(employee_id);-- 外键约束ALTERTABLE employee ADDconstraint fk_dept FOREIGNKEY(department_id)REFERENCES department (department_id);

3.2 查看表主键外键

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

3.3 表字段增加

ALTERTABLE"DM"."EMPLOYEE"add"IS_REPEAT"VARCHAR(255);COMMENTONCOLUMN"DM"."EMPLOYEE"."IS_REPEAT"IS'是否重复，1、是，0、否';

3.4 表字段删除

ALTERTABLE"DM"."EMPLOYEE"DROPCOLUMN IS_REPEAT;

4. 验证数据表 CRUD 功能

-- 插入数据INSERTINTO department VALUES(666,'数据库产品中心');INSERTINTO employee VALUES(9999,'王达梦','2008-05-30 00:00:00',30000,666);commit;-- 因为 employee 员工表和 department 部门表存在主外键约束，所以此示例中须按顺序执行插入语句，即先在 department 表中插入数据。-- 更新数据UPDATE employee SET salary='35000'WHERE employee_id=9999;commit;-- 验证数据SELECT salary,employee_id FROM employee;-- 删除数据DELETEFROM employee;DELETEFROM department WHERE department_id=666;commit;-- 验证数据SELECT*FROM employee;

5. 批量插入及选择排序

-- 在 t1 表中批量插入 100000 条数据记录。CREATETABLE t1 ASSELECT rownum AS id,
       trunc(dbms_random.value(0,100))AS random_id,
       dbms_random.string('x',20)AS random_string
     FROM dual
     connectBYlevel<=100000;-- 使用 COUNT(*) 聚集函数统计 t1 表中的总数据记录。SELECTCOUNT(*)FROM t1;-- 使用 ORDER BY 语句实现选择排序。SELECT*FROM t1 where rownum<5ORDERBY id DESC;

6.验证分组查询

-- 插入准备数据INSERTINTO department (department_id, department_name)SELECT department_id, department_name FROM dmhr.department;INSERTINTO employee
     (employee_id, employee_name, hire_date, salary, department_id)SELECT employee_id, employee_name, hire_date, salary,
     department_id FROM dmhr.employee;commit;SELECT dept.department_name as 部门,count(*)as 人数
     FROM employee emp, department dept
     where emp.department_id=dept.department_id
     GROUPBY dept.department_name
     HAVINGcount(*)>20;

7.创建视图

-- 定义视图CREATEORreplaceVIEW v1 ASSELECT dept.department_name, emp.employee_name,
     emp.salary,emp.hire_date
     FROM employee emp, department dept
     WHERE salary >10000AND hire_date >='2013-08-01'AND emp.department_id = dept.department_id;-- 通过视图简化查询SELECT*FROM v1 WHERE hire_date >'2014-09-01';

8.索引

-- 创建普通索引CREATEINDEX ind_emp_salary ON employee(salary);SELECT table_name, index_name, index_type
     from user_indexes WHERE index_name='IND_EMP_SALARY';-- 删除索引DROPINDEX IND_EMP_SALARY;

9.创建表

-- 若该表存在则删除DROPTABLEIFEXISTS TEST_TEMP;-- 创建对应的表：-- 主键自增配置  IDENTITY(1, 1)，CLUSTER PRIMARY KEY("ID")-- 自动创建时间  DEFAULT (CURDATE)CREATETABLE"TEST_TEMP"("ID"INTIDENTITY(1,1)NOTNULL,"PROJECT_KEY"VARCHAR(100),"CREAT_TIME"DATEDEFAULT(CURDATE),
CLUSTER PRIMARYKEY("ID"))
STORAGE(ON"TEST", CLUSTERBTR);

9.1 批量插入数据

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

9.2 数据库自动新增日期

-- 自动生成日期，不带时分秒"CREAT_TIME"DATEDEFAULT(CURDATE),-- 自动生成日期，带时分秒"CREAT_TIME"TIMESTAMPDEFAULT(SYSDATE),

9.3 数据库自动更新时间

通过触发器脚本实现

createtrigger UPDATE_TIME_CUSTOM_NAME
before updateon"DEV"."CUSTOM_NAME"for each rowbegin
new.UPDATE_TIME:=sysdate;end;

解析
UPDATE_TIME_CUSTOM_NAME：触发名称
DEV：模式名
CUSTOM_NAME：表名称
UPDATE_TIME：字段名称
– 字段配置，自动生成日期，带时分秒
“UPDATE_TIME” TIMESTAMP DEFAULT (SYSDATE),

10. 查询表中的字段信息

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

在这里插入图片描述

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