Oracle-SQL基础

Oracle-SQL基础

1.Oracle的数据类型

2.SQL语句的分类

SQL语句分类	关键字
数据定义语言（DDL）	creat · alter· drop
数据管理语言（DML）	insert· select ·delete· update
数据控制语言（DCL）	grant· revoke
事务管理语言（TCL）	commit ·savepoint· rollback

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进行数据查询时，进场会用到运算符的运算结果作为条件。

查询条件	运算符
比较	=,>,<,>=,<=,<>,!=,NOT
范围	between and,not between and
集合	In,not in
字符匹配	like,not like
空值	is null,is not null
多重条件	and,or

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;

集合函数（常用）

常用函数名	作用
Avg	计算平均值
Min	返回最小值
Max	返回最大值
Sum	计算总和
Count	计算查询结果个数

查询所有员工的平均工资，最高工资和最低工资

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

集合操作

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

集合操作符	作用
INTERSECT	只返回两个查询的公共行(取交集)
MINUS	返回从第一个查询结果中排除第二个查询中出现的行(减去)
UNION	合并，去除重复(加all表示不去除重复)

连接操作符
连接操作符用于将多个字符串或数据值合并成一个字符串

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

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