雪月书韵茶香

Oracle-SQL 基础

1.Oracle 的数据类型

2.SQL 语句的分类

DDL 和 DCL 语句有执行后自动提交的功能
任何 DML 或 TCL 语句在执行时，Oracle 会自动为语句创建事务，如果在 SQLPlus 中使用 quit 命令退出时，也会自动提交当前事务

3.DDL 语言

DDL 语言用于改变数据库结构，包括创建、修改、和删除数据库对象,主要由 creat · alter·和 drop 语句构成。
根据提供的 sql 语句创建如下三张表

create table dept(
       did integer primary key,
       dname varchar2(20),
       manager integer,
       tel varchar(30),
       maseter integer
       );
create table emp(
       eid integer primary key,
       ename varchar2(20),
       sex varchar2(1),
       hire date,
       sar number(8,2),
       did integer
       );
create table history(
       hid integer primary key,
       startdata date,
       enddate date,
       place varchar(50),
       job varchar2(20),
       eid integer
       );

给刚刚创建的表中的 dept 和 emp 插入数据

insert into dept values(1,'董事会',5,'1111111',0);
insert into dept values(2,'市场部',6,'2222222',1);
insert into dept values(3,'财务部’,7,'3333333',1);
insert into dept values(4,'采购部',8,'4444444',1);

insert into emp values(5,'tom','m',sysdate-234,4500.50,1);
insert into emp values(6,'jack','m',sysdate-543,2982.12,2);
insert into emp values(7,'kelly','f',sysdate-445,3491.32,3);
insert into emp values(8,'red','f',sysdate-143,3700.00,4);
insert into emp values(9,'blue','m',sysdate-423,38700.00,2);
insert into emp values(10,'green','m',sysdate-413,5100.00,3);
insert into emp values(11,'peter','m',sysdate-243,6700.00,4);

5.select 查询

在进行 select 进行数据查询时，进场会用到运算符的运算结果作为条件。

order by

用于制定查询结果的排序，后面可以跟多个字段名，越在前面的排序优先权越高
默认为升序（asc）

select * from emp order by sar asc;

select * from emp order by did asc,sar desc;

between and，not between and

可以进行某一范围的查询 ,范围包含最大值和最小值 。

select * from emp where sar between 3000 and 6000;

select * from emp where sar not between 3000 and 6000;

in，not in

是集合运算符，只要查询的值等于集合中的一个元素，表达式为真。

select * from emp where did in(2,3);

like，not like

select * from emp where ename like 't%';

select * from emp where ename like ‘t_‘;

and

多个条件同时成立时条件表达式才为真

select * from emp where sex='f' and sar>5000;

or

多个条件中只要有一个条件为真时条件表达式就为真

select * from emp where sex='f' or sar>5000;

内连接

查询所有员工及其所在的部门信息

select e.eid,e.ename,e.sex,e.hire,e.sar,d.dname
	from emp e inner join dept d
	on e.did=d.did;

查询部门及其部门经理信息

select d.*, e.ename from dept d, emp e where d.manager = e.eid

左外连接

查询所有部门及其部门下的员工(没有员工的部门也要查询出来)

select  d.*, e.ename from dept d left join emp e on d.did=e.eid

查询所有部门编号大于20及其部门下的员工

select  d.*, e.ename from dept d left join emp e on d.did=e.did WHERE d.did> 20

全外连接

select e.eid,e.ename,d.dname 
from emp e full join dept d 
on e.did=d.did

交叉连接

select e.eid, e.ename,d.name 
from emp e cross join dept

对自身的连接查询

在dept表中，每个部门都对应一个上级部门，如何查询所有部门及其上级部门的名称

select  d.dname  as  dname,m.dname as  mname 
   from dept d,dept m
   where d.master = m.did;

集合函数（常用）

select avg(sar),max(sar),min(sar) from emp;

查询每月支出的工资数

select count(eid),sum(sar) from emp;

distinct

选择无重复的行
在 SELECT 子句，使用 DISTINCT 关键字

select distinct(did) from emp;

使用列别名
为列表达式提供不同的名称
该别名指定了列标题

select avg(sar) as asar from emp;

select avg(sar) “asar” from emp;

group by

查询各个部门的最低薪水、最高薪水和平均薪水
切记：在 select 字句中出现的所有非集合函数项必须出现在 group by 字句中

select d.dname,max(e.sar),min(e.sar),avg(e.sar)
from dept d,emp e
where d.did=e.did
group by d.dname;

having

查询每个部门的最高工资、最低工资和平均工资，只保留平均工资小于 5000 的部门

select d.dname,max(e.sar),min(e.sar),avg(e.sar)
from dept d,emp e
where d.did=e.did
group by d.dname
having avg(e.sar)<5000;

嵌套查询（子查询）

查询公司所有的非部门经理的员工的编号和名称

select eid,ename from emp
where eid not in 
(select distinct manager from dept)

查询工资大于 4000 的所有部门经理的信息

select * from (
   select eid,ename,sar
   from  emp
   where eid  in (select distinct  manager from dept)
) where sar>4000

集合操作

集合操作符将结构相同的两个查询的结果组合成一个结果

 select  (dname || ‘ 的电话是: ‘ || tel) as dtel from dept;

通过使用连接操作符可以将表中
的多个列合并成逻辑上的一行列