达梦数据库select insert update delete merge

简单查询

选择全列
select * from dmhr.department;
操作符 乘除的优先级高于加减，同一优先级运算符从左向右执行，括号内的运算先执行
select employee_name,salary,salary+1000 from dmhr.employee;
列的别名 重命名一个列 ；便于计算；紧跟列名，也可以在列名和别名之间加入关键字 AS ，以便在别名中包含空格或特殊的字符并区分大小写
select employee_name,salary,salary+1000 as TOL from dmhr.employee;
去重  distinct
select distinct department_id from dmhr.employee;
使用连接符
把列与列，列与字符连接在一起。用 ||表示，可以用来合成列。
select department_id, employee_name||'工资是：'||salary from dmhr.employee;

过滤排序

使用比较运算 > < =
select employee_id,employee_name,salary from dmhr.employee where salary >=25000 and salary <=30000;
IN
select employee_id,employee_name,salary from dmhr.employee where salary in (15000,30000);
使用 LIKE 运算选择类似的值选择条件可以包含字符或数字:%代表零个或多个字符_代表一个字符
select employee_id,employee_name,department_id from dmhr.employee where employee_name like '%马%' ;
NULL 
select employee_id,employee_name,department_id from dmhr.employee 
where department_id is not null;
逻辑运算符 and or not 
select employee_id, employee_name, salary from dmhr.employee
where (employee_id=1001 or employee_id=1002)
and salary >20000;

排序和分组

排序ORDERBY 子句 ASC: 升序 DESC: 降序
select employee_id,employee_name,salary from dmhr.employee order by salary desc, employee_id asc;
分组函数作用于一组数据，并对一组数据返回一个值。HAVING用于过滤分组结果集。常见的分组函数：AVG 、COUNT 、MAX 、MIN 、SUM
select department_id, max(salary)as max_sal, min(salary) as min_sal, avg(salary) as avg_sal
from dmhr.employee group by department_id;
HAVING 子句
select department_id, avg(salary) as avg_sal from dmhr.employee 
group by department_id having avg(salary)>10000;

子查询

当一个查询是另一个查询的条件时，称之为子查询。子查询最常用于SELECT-SQL命令的WHERE子句中。
单行子查询
select employee_name, salary from dmhr.employee 
where department_id =
(select department_id from dmhr.employee where employee_name='马学铭');
多行子查询 运算符	
IN	等于列表中的任意一个
ANY	将值与子查询返回的任意一个值进行比较
ALL	将值与子查询返回的每个值进行比较
select employee_name, salary from dmhr.employee 
where salary > all (select salary	from dmhr.employee where department_id=108);
select employee_name, salary from dmhr.employee where salary > any (select min(salary)	from dmhr.employee );

连接查询

自然连接中是以具有相同名字的列为连接条件的可以使用 ON 子句指定额外的连接条件
这个连接条件是与其它条件分开的
ON 子句使语句具有更高的易读性
select c.city_name, l.street_address from dmhr.city c
join dmhr.location l
on c.city_id=l.city_id;
左外连接
select c.city_name, l.street_address from dmhr.city c
left join dmhr.location l on c.city_id=l.city_id;
select c.city_name, l.street_address from dmhr.city c, dmhr.location l
where c.city_id=l.city_id(+);
右外连接
select c.city_name, l.street_address from dmhr.city c
right join dmhr.location l on c.city_id=l.city_id;
select c.city_name, l.street_address from dmhr.city c, dmhr.location l
where c.city_id(+)=l.city_id;
全外连接
select c.city_name,l.street_address from dmhr.city c
inner join dmhr.location l on c.city_id=l.city_id;
select c.city_name, l.street_address from dmhr.city c, dmhr.location l
where c.city_id=l.city_id;

insert

向DMHR.EMPLOYEE增加新员工信息：
INSERT INTO DMHR.EMPLOYEE VALUES
(11146,'吴健','210102196202303000','wujian@dameng.com','15312348223','2016-05- 30','11',30000.00,0,1001,101);
插入多行
INSERT INTO DMHR.EMPLOYEE VALUES 
(11150,'吴军','210102196302303003','wujun@dameng.com','15312348225','2016-05- 30','11',30000.00,0,1001,101),
(11151,'曹阳','110102196202303004','caoyang@dameng.com','15234348226','2016-05- 31','11',30000.00,0,1001,101);

update

修改马学铭的工资：
update dmhr.employee set salary=20000
where employee_name='马学铭';
commit;
用子查询更新
修改马学铭的工资和金纬的工资相同：
update dmhr.employee
set salary=(select salary
from dmhr.employee
where employee_name='金纬') where employee_name='马学铭';

delete

表中删除数据
如果指定了WHERE子句，则可以删除特定的行，假
设马学铭离职，要把他在DMHR.EMPLOYEE表删除：
delete from dmhr.employee where employee_name='吴健';
删除表所有行
删除员工历史表的所有数据：
delete from dmhr.job_history;

merge

把T1表中C1值为2的记录行中的C2列，更新为表T2中C3值为2的记录中
C4列的值，同时把T2中C3列为4的记录行插入到了T1中
MERGE INTO T1 USING T2 ON (T1.C1=T2.C3) WHEN MATCHED THEN UPDATE SET T1.C2=T2.C4
WHEN NOT MATCHED THEN INSERT (C1,C2) VALUES(T2.C3,T2.C4);