DM基础语法

DMSQL
SQL?结构化查询语言，提供了应用程序到数据库的一个接口。
1.1DMSQL 分类
DML(数据操作语言)：insert update delete select
DDL(数据定义语言):create table,drop table,alter table ,truncate
DCL（数据控制语言）：权限的授予或撤回，grant,revoke
TCL(事务控制语言)：commit, rollback ,save moint
1.2SQL 语句规范
SQL 不区分大小写，除了’’ “”除外
关键字是不能被拆分，不能缩写
可以换行，可以缩进，提高阅读性
1.3 简单查询
语法：
Select () from ();
单列查询
select employee_id from dmhr.employee;
查询多列
select * from dmhr.employee;
表达式：select employee_id,employee_name,salary,salary+500 from
dmhr.employee;
起别名：（方便识别，计算）
select employee_id,employee_name,salary,salary+500 tol from
dmhr.employee;
去重复：(隐含排序 升序)
select distinct department_id from dmhr.department;
连接||
“xxx 的工资是：xxx”
select employee_name || ‘的工资是:’ || salary from dmhr.employee;
1.4 过滤查询
语法：
Select () from () where ()
比较运算符、逻辑运算符、in、between*** and、is null/is not null、like
比较运算符：> >= < <= <> != =
select employee_name,salary from dmhr.employee where
salary>=30000;
逻辑运算符：and or not
And 同时满足：
select employee_name,department_id,salary from dmhr.employee
where department_id=101 and salary>15000;or 满足一个条件即可：
select employee_name,department_id,salary from dmhr.employee
where department_id=101 or salary>15000;
Is null/is not null null 为空但是不等于 0
select employee_name,job_id from dmhr.employee where job_id is null;
select employee_name,job_id from dmhr.employee where job_id is not
null;
IN
select employee_name,department_id from dmhr.employee
where department_id in (101,104);
Between*** and
select employee_name,department_id,salary from dmhr.employee
where salary between 20000 and 30000;
模糊查询：like % _
%匹配 0 个或多个字符
匹配 1 个字符
select employee_name,salary from dmhr.employee
where employee_name like ‘李%’;
select employee_name,salary from dmhr.employee
where employee_name like '李’;
1.5 排序
升序 asc
select employee_name,salary from dmhr.employee
order by salary asc;
降序 desc
select employee_name,salary from dmhr.employee
order by 2 desc;
1.6 分组函数
Count max min avg sum
语法：
Select () from () group by () having ();
除了聚合函数不用跟在 group by 后面，其他 select 列必须在 group by
后面，having 是对 group by 进一步过滤，having 不能单独使用。
求部门工资和小于 8 万的部门
select department_id,sum(salary) from dmhr.employee
group by department_id
having sum(salary) <80000;
1.7 多表连接
内连接
结果集只显示满足条件的记录交叉连接（迪卡集）
两个表记录数的乘积
select count() from dmhr.employee cross join
dmhr.department;–39376
select count() from dmhr.employee;
select count(*) from dmhr.department;
自然连接
根据列名自己过滤，两个表连接的列名和数据类型要一致
select employee_name,department_name from dmhr.employee natural
join dmhr.department;
USING
在满足多个连接列时，可以指定某一个列做连接，一般是和 NATURAL
JOIN 使用，连接的列前面不能加表名或者是前缀。
select employee_name,department_name from dmhr.employee join
dmhr.department
using (department_id);
ON 子句
select c.city_name,l.street_address from dmhr.city c join dmhr.location
l on c.city_id=l.city_id;
HASH JOIN
优化器 CBO 根据小表连接列做运算生成 hash 值，根据 hash 值去连接
大表，扫描大表select e.employee_name,d.department_name from dmhr.employee e
inner hash join dmhr.department d
on e.department_id=d.department_id;
外连接
结果集除了显示满足条件的记录，不满足的用 null 代替
左外连接
把 left join 左边的全部显示出来，右边只显示满足条件的，不满足条
件的用 null 补齐
select a.employee_name,b.department_name from dmhr.employee a
left join dmhr.department b
on a.department_id=b.department_id;
右外连接
把 right join 右边的全部显示出来，左边只显示满足条件的，不满足
条件的用 null 补齐
select a.employee_name,b.department_name from dmhr.employee a
right join dmhr.department b
on a.department_id=b.department_id;
全外连接
左外连接+右外连接
select a.employee_name,b.department_name from dmhr.employee a
full join dmhr.department bon a.department_id=b.department_id;
1.8 子查询
当一个查询是另一个查询条件时，就叫子查询。子查询先运行，子查
询的结果是外部查询（主查询）的条件
单行子查询
= > >= < <= <>
找出和金纬在一个部门的员工
select employee_name,department_id from dmhr.employee where
department_id=
(select department_id from dmhr.employee where employee_name=‘金
纬’)
多行子查询
In any all
查询比 104 部门工资都高的人
select department_id,employee_name,salary from dmhr.employee
where salary > all
(select salary from dmhr.employee where department_id=104);
Exists
一旦有记录满足条件，就立马返回值SELECT E.EMPLOYEE_ID , E.EMPLOYEE_NAME FROM DMHR.EMPLOYEE
E
WHERE EXISTS
( SELECT * FROM DMHR.JOB_HISTORY J
WHERE E.EMPLOYEE_ID = J.EMPLOYEE_ID );