oracle 数据库

ORACLE;

dml 数据操作  

ddl 对象操作

dcl 权限控制

tcl事务控制

sql 结构化查询语句

distinct 去除重复

语法；

insert 插入语法

insert into 表名set [（列名...）]values（列值）

update 修该语法

update 表名 set 列名 =列值【....】【where条件】

delete 删除语法

delete 【from】表名【where 条件】

表连接的种类

内连；inner join

外连分为；

左外连；left join 右外连；right join 全外连；full join

交叉连；cross join

自连接；natural join

销售数据库Sales中存在这样的三张表如最下表

select *from emp;drop table proout;drop table Customer;drop table Product;create table  Customer(       CusNo varchar2(10) primary key ,       CusName varchar2(10),        Address varchar2(20),       Tel varchar2(100));create table Product(       ProNo varchar2(10) primary key ,       ProName varchar2(30),       price number(20),       Stocks varchar2(30));create table proout(       SaleDate date,       CusNo varchar2(10),       ProNo varchar2(10),        Quantity int,       foreign key (CusNo)  references Customer(CusNo),       foreign key (ProNo)  references Product(ProNo));insert into Customer values ( 'C001','杨婷',  '北京'  ,'010-5328953');insert into Customer values ('C002',    '李和平',  '上海', '021-6235965');insert into Customer values ('C003',  '叶新', '成都', '024-3222781');insert into Customer values ( 'C004', '冯辰诚' ,'上海' ,'021-8723596');insert into Customer values ( 'C005', '张展', '郑州', '0371-8907654');insert into Product values ('P0002', '空调',  2390.00 ,460);insert into Product values ('P0003', '洗衣机', 3700.00 ,600);insert into Product values ( 'P0004', '电热水器',  890.00 , 120);insert into Product values ( 'P0001' ,'液晶电视',  5600.00, 800);insert into proout values(to_date('2007-10-27','yyyy-mm-dd'), 'C001','P0001',3);insert into proout values(to_date('2007-11-06','yyyy-mm-dd'), 'C004','P0003',40);insert into proout values(to_date('2007-12-27' ,'yyyy-mm-dd'), 'C001','P0003', 5);insert into proout values(to_date('2008-3-15','yyyy-mm-dd'), 'C002','P0002',12);insert into proout values(to_date('2008-05-02' ,'yyyy-mm-dd'), 'C003','P0002', 21);insert into proout values(to_date('2008-05-02' ,'yyyy-mm-dd'), 'C003','P0001', 9);insert into proout values(to_date('2008-09-21','yyyy-mm-dd'), 'C004','P0001',30);insert into proout values(to_date('2008-11-21' ,'yyyy-mm-dd'), 'C004','P0001',73);select *from Customer;select *from Product;select *from proout;--3)将产品“洗衣机”的单价加1000元update Product set price =price+1000 where proname ='洗衣机';--4)编号“P0005”的产品“电冰箱”，单价3000，库存1000，录入时遗漏，请编写SQL语句插入该记录。insert into Product values ('P0005','电冰箱','3000.00',1000);--5)  查询购买了产品编号“P0002”的客户编号、客户名和电话，查询结果按客户名降序排列。select *from proout p inner join Customer c on c.cusno=p.cusno where p.prono='P0002' order by c.cusname desc;--6)  查询所有的客户编号和客户名以及它们所购买的产品编号和产品名。（包括没买产品的顾客）。select c.cusno,c.cusname,pr.prono,pr.proname from Customer c left  join  proout p on c.cusno =p.cusno left join Product pr on p.prono=pr.prono ;--7)  查询客户表中“姓张”的客户的姓名，地址，电话。select c.cusname,c.address,c.tel from Customer c where c.cusname like'张%';--8)  查询产品表中产品名称中带有“电”字的产品编号、产品名称、单价。select  p.prono,p.proname,p.price from Product p where p.proname like '%电%';--9)  查询产品表中库存数量大于产品表中库存量的平均值的产品编号、产品名称、库存量。select p.prono,p.proname,p.stocks from Product p where p.stocks<(select avg(stocks)from Product );--10) 查询2008年液晶电视的销售总数量和销售总额（总额=销售总数量*单价）。select sum(p.quantity),sum(p.quantity*pr.price)  from Product pr inner join proout p on pr.prono=p.prono;--11) 查询出2008年销售给上海客户的客户们，商品名称，销售数量。select c.cusname,p.proname,pr.quantity from Product p inner join proout pr on p.prono=pr.prono inner join Customer c on pr.cusno=c.cusno where to_char(pr.saledate,'yyyy')=2008 and c.address ='上海'; --12) 更新液晶电视的价格为8800元，库存数量为888。update Product   set price=8800.00,stocks=888 where proname like'液晶电视' ;select *from Product;--13) 查询电话号码以021开头且最后一位不是5的客户姓名、客户电话、客户地址。select c.cusname,c.tel,c.address from Customer c where c.tel like '021%' and not  c.tel like'%5'; --14) 查询在2007年内购订购过产品的客户编号，客户名以及产品名和单价。select c.cusno,c.cusname,p.proname,p.price from proout pr  inner join Product p on pr.prono=p.prono inner join customer c on  c.cusno=pr.cusno where to_char(pr.saledate,'yyyy')=2007;--15) 查询定购过产品的客户编号、客户名和电话。查询结果按客户编号升序排列。select c.cusno,c.cusname,c.tel from proout pr  inner join Customer c on c.cusno=pr.cusno order by c.cusno ;--16) 统计销售数量超过100的产品名。select p.stocks,sum(pr.quantity) from proout pr inner join Product p on pr.prono=pr.prono group by p.stocks having  sum(pr.quantity)>100;--17) 删除销售表中销售数量低于6的销售记录 。delete proout p where p.quantity<6;--18) 更改地址为北京的用户姓名为杨婷婷。update Customer   set address ='北京' where cusno='C001';update Customer   set cusname ='杨婷婷' where address='北京';select *from Customer;--19) 删除销售记录表。--drop  table proout;--20) 删除产品表中所有数据。--delete from Product;

 

客户表（Customer）

客户编号(CusNo)

姓名(CusName)

地址(Address)

电话(Tel)

C001

杨婷

北京

010-5328953

C002

李和平

上海

021-6235965

C003

叶新

成都

024-3222781

C004

冯辰诚

上海

021-8723596

C005

张展

郑州

0371-8907654

产品表(Product)

产品编号(ProNo)

产品(ProName)

单价(price)

库存数量(Stocks)

P0001

液晶电视

5600.00

800

P0002

空调

2390.00

460

P0003

洗衣机

3700.00

600

P0004

电热水器

890.00

120

 

销售表(ProOut)

销售日期(SaleDate)

客户编号(CusNo)

产品编号(ProNo)

销售数量(Quantity)

2007-10-27

C001

P0001

3

2007-11-06

C004

P0003

40

2007-12-27

C001

P0003

5

2008-3-15

C002

P0002

12

2008-05-02

C003

P0002

21

2008-05-02

C003

P0001

9

2008-09-21

C004

P0001

30

2008-11-21

C004

P0001

73