ORACLE中基本语句笔记

为什么80%的码农都做不了架构师？>>>   

1、创建表CUSTOMERS 和ORDERS

create table CUSTOMERS (
   ID number(8) not null,
   NAME varchar2(15),
   AGE number(4),
   primary key (ID)
);
create table ORDERS (
   ID number(8) not null,
   ORDER_NUMBER varchar2(15),
   PRICE number(10,2),
   CUSTOMER_ID number(8),
   primary key (ID)
);

2、创建ORDERS 的外键CUSTOMER_ID与CUSTOMERS 的ID关联

alter table ORDERS add constraint FK_CUSTOMER foreign key 
(CUSTOMER_ID) references CUSTOMERS (ID);

3、向表中插入数据

insert into CUSTOMERS(ID,NAME,AGE) values(1,'Tom',21);
insert into CUSTOMERS(ID,NAME,AGE) values(2,'Mike',24);
insert into CUSTOMERS(ID,NAME,AGE) values(3,'Jack',30);
insert into CUSTOMERS(ID,NAME,AGE) values(4,'Linda',25);
insert into CUSTOMERS(ID,NAME,AGE) values(5,'Tom',null);

insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(1,'Tom_Order001',100,1);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(2,'Tom_Order002',200,1);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(3,'Tom_Order003',300,1);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(4,'Mike_Order001',100,2);

4、查询年龄在18到25之间的客户，查询结果年龄降序排列，再按名字升序排列

select *from CUSTOMERS where age between 15 and 25 order by AGE desc,NAME asc

5、交叉连接查询CUSTOMERS表和ORDERS

select *from CUSTOMERS,ORDERS

6、显式内连接查询，使用inner join关键字，在on子句中设定连接条件

select c.ID ,o.CUSTOMER_ID,c.NAME,o.ID ORDER_ID,ORDER_NUMBER
from CUSTOMERS c
inner join  ORDERS o
on c.ID=o.CUSTOMER_ID

7、隐式内连接查询，不包含inner join和on关键字,在where子句中设定连接条件

select c.ID ,o.CUSTOMER_ID,c.NAME,o.ID ORDER_ID,ORDER_NUMBER
from CUSTOMERS c,ORDERS o
where c.ID=o.CUSTOMER_ID

8、左外连接查询，使用left outer join关键字，在on子句中设定连接条件

select c.ID ,o.CUSTOMER_ID,c.NAME,o.ID ORDER_ID,ORDER_NUMBER
from CUSTOMERS c
left outer join ORDERS o
on c.ID=o.CUSTOMER_ID

9、带查询条件的左外连接查询，在where子句中设定查询条件

select c.ID ,o.CUSTOMER_ID,c.NAME,o.ID ORDER_ID,ORDER_NUMBER
from CUSTOMERS c
right outer join ORDERS o
on c.ID=o.CUSTOMER_ID

10、右外连接查询，使用right outer join关键字，在on子句中设定连接条件

select c.ID ,o.CUSTOMER_ID,c.NAME,o.ID ORDER_ID,ORDER_NUMBER
from CUSTOMERS c
left outer join ORDERS o
on c.ID=o.CUSTOMER_ID
where o.ID>4 and c.ID>2

11、带查询条件的右外连接查询，在where子句中设定查询条件

select c.ID ,o.CUSTOMER_ID,c.NAME,o.ID ORDER_ID,ORDER_NUMBER
from CUSTOMERS c
right outer join ORDERS o
on c.ID=o.CUSTOMER_ID
where o.ID>5 and c.ID=4

12、查询具有三个订单以上的用户

select * 
from CUSTOMERS c 
where 3<=(select count(*) from ORDERS o where c.ID=o.CUSTOMER_ID)

14、查询名为"Mike"用户的所有订单

select * 
from  ORDERS o
where o.CUSTOMER_ID in(select c.ID from CUSTOMERS c where c.NAME like 'Mike')

15、查询没有订单的客户

select * 
from  CUSTOMERS c
where 0=(select count(*) from ORDERS o where c.ID=o.CUSTOMER_ID)

或者

select *
from CUSTOMERS c
where not exists(select * from ORDERS o where c.ID=o.CUSTOMER_ID)

16、

select c.NAME,c.AGE,(select sum(PRICE) from ORDERS where  CUSTOMER_ID=1)  Total_Price  
from  CUSTOMERS c
where c.ID=1

--或者通过左外连接查询：
 
select c.NAME,c.AGE,sum(o.PRICE) Total_Price
from CUSTOMERS c
left outer join ORDERS o
on c.ID=o.CUSTOMER_ID
where c.ID=1
group by c.NAME,c.AGE

17、按客户分组，查询每个客户的所有订单的总价格

select c.ID,c.NAME,sum(o.PRICE)Total_Price
from CUSTOMERS c
left join ORDERS o
on c.ID=o.CUSTOMER_ID
group by c.ID,c.NAME

18、按客户分组，查询每个客户的所有订单的总价格，并且要求订单的总价格大于100

select c.ID,c.NAME,sum(o.PRICE)Total_Price
from CUSTOMERS c
left join ORDERS o
on c.ID=o.CUSTOMER_ID
group by c.ID,c.NAME
having(sum(o.PRICE)>100)

转载于:https://my.oschina.net/Boston/blog/76791