DMSQL语法学习记录：以达梦数据库自带DMHR数据库为示例练习

DMSQL语法学习记录：以达梦数据库自带DMHR数据库为示例练习

DMSQL
SQL:结构化查询语言
DMSQL 基于 sql92 和 sql99 的标准

1.DMSQL分类

DML数据操纵(数据库查询和操纵数据表资料行)：select insert update delete
DDL 数据定义(建立、修改、删除数据库中数据表对象):create drop alter truncate
DCL数据控制（权限控制、用于执行行权限授予与收回操作）：grant revoke
TCL事务控制(事务控制、维护数据的一致性):commit rollback

2.SQL书写规范

A.关键字不能被拆分、缩写 ，也不能分行；
B.大小写不敏感，除了’ ’、” ”以外 ；
C.可以写在一行或者多行，可以换行；
D.采用缩进可提高语句的可读性。

3.单表查询

3.1 简单查询 （全表扫描，耗能）
A.SELECT 标识选择哪些列，FROM标识从哪个表中选择
B.省略模式名：set schema 模式名
C.查询结果出现乱码：客户端字符集和数据库冲突（查看是否做过conn操作，以disql标准执行操作）
语法 ：select () from ();

--单列查询 
eg:查询员工表的姓名 
SQL>select employee_name from dmhr.employee; 

--多列查询 
eg:查询员工表的姓名，部门编号，工资 
SQL> select employee_name,department_id,salary from dmhr.employee; 

--查询所有列 
SQL>Select * from dmhr.employee; 

--对列起别名 
eg:查询员工姓名和工资 
SQL>select employee_name,salary as gz from dmhr.employee; 
SQL>select employee_name,salary gz from dmhr.employee; 

连接 || 
eg:查询“***的工资是：” 
SQL>select employee_name|| '的工资是:'||salary from dmhr.employee; 

去重复 distinct (隐藏排序-升序) 
eg:在员工表查询部门编号 
SQL>select distinct department_id from dmhr.employee; 

--表达式 
eg:为所有人工资加 2000 
SQL>select employee_name,salary,salary+2000 from dmhr.employee; 

3.2 过滤查询（带条件查询）
比较运算符、逻辑运算、in、is null/is not null、between***and ***，like
语法：Select () from () where ();

--比较运算符：= > >= < <= <> != 
eg:查询名为“马学铭”的工资
SQL>select employee_name,salary from dmhr.employee where employee_name='马学铭'; 
SQL>select employee_name,salary,department_id from dmhr.employee where salary>=30000; 

--逻辑运算符：and or not
and:如果两个分量条件都为真，则返回true
or：如果其中一个分量条件为真，则返回true
not:如果下面的条件为假，则返回true
eg:查询部门编号为101且工资大于10000的员工
SQL>select employee_name,salary,department_id from dmhr.employee where department_id=101 and salary >=10000; 
eg:查询部门编号为103或者工资大于30000的员工
SQL>select employee_name,salary,department_id from dmhr.employee where department_id=103 or salary >=30000; 

--模糊查询：like % _ 
%匹配0个或多个字符，_匹配 1 个字符
eg:查询姓陈的员工姓名和部门编号
SQL>select employee_name,department_id from dmhr.employee where employee_name like '陈%';
eg:查询名字里带有“强”的员工姓名和部门编号 
SQL>select employee_name,department_id from dmhr.employee where employee_name like '%强'; 
eg:查询姓陈且姓名为两个字的员工姓名和部门编号
SQL>select employee_name,department_id from dmhr.employee where employee_name like '陈_'; 

--in 用来查询某列值属于指定集合的元组
eg:查询部门编号为101和102的员工
SQL>select employee_name,department_id from dmhr.employee where department_id in (101,102) 

--is null/is not null null 为空，不等于 0 
eg:查询部门编号为空/不为空的员工
SQL>select employee_name,job_id,department_id from dmhr.employee where department_id is null; 
SQL>select employee_name,job_id,department_id from dmhr.employee where department_id is not null; 

between*** and *** 表示范围
eg:工资在10000~20000之间的员工
SQL>select employee_name,department_id,salary from dmhr.employee where salary BETWEEN 10000 and 20000; 
等价于：
SQL>select employee_name,salary from dmhr.employee where salary>= 10000 and salary <=20000;

3.3 分组函数
COUNT：总个数函数
MAX：最大值函数
MIN：最小值函数
AVG：平均值集数
SUM：总和函数
语法：Select () from () where () group by () having ();
使用说明：
AVG、SUM 的参数必须为数值类型；
MAX、MIN 的结果数据类型与参数类型保持一致；
group by(先分组,再聚合)：给查询的结果进行分组，除了聚合函数不用跟在 group by 后面，其他 select 列，必须要跟在 group by 后面，having
是对 group by 的进一步过滤，having 不能单独使用。

eg:统计工资大于30000的员工数
SQL>select count(*)from dmhr.employee where salary>=30000;
eg:求部门工资和大于 10 万的部门 
SQL>select department_id,sum (salary) from dmhr.employee group by department_id having sum (salary) >100000; 
eg：求部门编号为101的员工工资最大值
SQL>select department_id,max(salary)from dmhr.employee group by department_id having department_id=101;
eg：求部门编号为103的员工工资最小值
SQL>select department_id,min(salary)from dmhr.employee group by department_id having department_id=103;
eg：求部门编号为104的员工工资平均值
SQL>select department_id,avg(salary)from dmhr.employee group by department_id having department_id=104;

3.4 排序

--升序 asc 
eg:对员工工资进行升序排序
SQL>select employee_name,department_id,salary from dmhr.employee order by salary asc; 
eg:查看最早入职的员工
SQL>select employee_name,department_id,hire_date from dmhr.employee order by hire_date asc; 

--降序 desc 
eg:对员工工资进行降序排序
SQL>select employee_name,department_id,salary from dmhr.employee order by salary desc;

4.连接查询

4.1 内连接（inner)

结果集只显示满足条件的记录 
--自然连接 natural join 
查询不需要指定连接列，列名自己过滤，两个表的连接列的列名和数据类型要一致。 
eg:连接列department_id
SQL>select employee_name,department_id,department_name from dmhr.employee natural join dmhr.department; 
--交叉连接cross join（笛卡集）---尽量避免，容易拖死数据库 
SQL>select count(*) from dmhr.employee; （emoployee表的行集）
SQL>select count(*) from dmhr.department; （departmen表的行集）
SQL>select count(*) from dmhr.employee cross join dmhr.department; （两表行集乘积）

--join ... using 子句 
在满足多个连接列时，可以指定一个列做连接，连接列的前面不能加表名或者是前缀；join 关键字指定连接的两张表，using 指明连接列；要求 using 中的列存在于两张连接表中。
eg:指定连接列 department_id
SQL>select employee_name,department_name,department_id 
from dmhr.employee 
join dmhr.department 
using (department_id); 

--join ... on子句 
join 关键字指定连接的两张表，on 子句指定连接条件表达式，其中不允许出现 rownum，可为表设置简单代称。
eg：查询员工入职时间
SQL>select e1.employee_name,j1.start_date 
from dmhr.employee e1 
join dmhr.job_history j1
on e1.employee_id=j1.employee_id; 

--hash join 
可以通过 hint 强制走 hash join 
优化器 CBO 根据小表的连接列生成 hash 值，连接大表，扫描大表 
eg：查询员工编号和所属部门
SQL>select e.EMPLOYEE_ID,e.EMPLOYEE_NAME,d.DEPARTMENT_NAME from dmhr.employee e inner 
hash join dmhr.department d 
on e.department_id = d.department_id;

4.2 外连接（outer)
结果集除了显示满足条件的记录，不满足的也显示，用 null 代替

左外连接 
把 left join 左边的全部显示出来，右边只显示满足条件的记录，不满足条件的用 null 代替 
eg：查询员工电话号码和所属部门
SQL>select e.employee_name,e.phone_num,d.department_name
from dmhr.employee e
left join dmhr.department d
on d.department_id=e.department_id;

右外连接 
把 right join 右边的全部显示出来，右边只显示满足条件的记录，不满足条件的用 null 代替 
SQL>select e.employee_name,e.phone_num,d.department_name
from dmhr.employee e
right join dmhr.department d
on d.department_id=e.department_id; 

全外连接 
左外连接+右外连接 
SQL>select e.employee_name,e.phone_num,d.department_name
from dmhr.employee e
full join dmhr.department d
on d.department_id=e.department_id;

5.子查询

当一个查询的结果是另一个查询的条件时，就叫子查询。子查询先运行，子查询的结果是主查询的条件。子查询可以嵌套子查询，子查询可以做临时表，字段。

单行子查询：一次只返回一条记录 
eg：查询马学铭的工资和部门编号
SQL>select employee_name,salary,department_id 
from dmhr.employee 
where employee_id = (select employee_id from dmhr.employee where employee_name='马学铭'); 

多行子查询：一次返回多条记录 
eg：查询比102部门所有人工资都高的员工
SQL>select employee_name,salary,department_id 
from dmhr.employee
where salary > all (select salary from dmhr.employee where department_id=102);