oracle从入门到精通 之 一.sql基础笔记

-- 在sys用户下，操作类似如下： 
-- *1. 先创建表空间： 
create tablespace ivy datafile '/ivy_tbs.dbf' size 100M; 
  
-- *2. 再创建用户并且指定此用户的默认表空间为上面创建的表空间： 
create user ivy identified by admin default tablespace ivy; 
  
-- *3. 给上面新创建的用户授权访问 
grant create session, resource to ivy; 
  
-- *4. 给 第2步新创建的用户 划分 第1步创建的表空间 的 “空间限额” 
alter user ivy quota unlimited on ivy; 
-- 上面表示：用户user_name 可以无限占用tablespace_name表空间的所有空间 
  
alter user ivy quota 100M on ivy; 
-- 上面表示：用户user_name最多可以使用tablespace_name表空间100M的空间。 
 

---//产品表
create table productinfo
(
ProId varchar2(10),

ProName varchar2(20),

ProPrice  number(3,2),

Quantity number(10),

ProCategory varchar2(10),

Description varchar2(1000),

Origin varchar2(20)
)

--//修改表结构
alter table  productinfo
add remark varchar2(20);

alter table productinfo 
modify ProPrice number(8,2)

alter table productinfo
modify ProCategory varchar2(30)

alter table productinfo
drop column remark

/*grant create session,resource to liuw
*/

--//类型表
create  table  categoryinfo
(
CategoryName  varchar2(20),
CategoryId  varchar2(10),
primary key(CategoryId)
) 
--/添加主键约束
alter table productinfo
add constraints dwdw primary key(ProId)

alter table productinfo
drop constraint dwdw

--创建外键约束
alter table productinfo
add constraint waijian foreign key(ProCategory)
references categoryinfo(CategoryId)
on delete cascade
--创建顾客表
create table custominfo
(
customid varchar2(10),
Name varchar2(10),
age varchar2(2),
gender varchar2(2),
tel varchar2(11),
adress varchar2(50),
primary key(customid),
constraint checkno check(age>=18 and age<=30)
)

--添加检查约束
alter table custominfo 
add constraint checkno1 check(gender='男' or gender='女')

--创建订单表并添加主键外键及唯一约束
create table orderinfo
(
orderid varchar2(10),
produtid varchar2(10),
customid varchar2(10),
orderdate date,
orderquantity number(10),
senddate varchar2(10),
primary key(orderid),
constraint foreign1 foreign key(produtid)
references productinfo(ProId),
constraint foreign2 foreign key(customid)
references custominfo(customid),
constraint weiyi unique(produtid)
)

alter table orderinfo
modify  orderdate varchar2(10);

alter table orderinfo
add constraint order123 unique(customid)

--创建管理员信息表设置非空约束
create table manageinfo
(
managerid varchar2(10),
loginName varchar2(10) not null,
password varchar2(10) not null,
name     varchar2(10),
tel      varchar2(11),
primary key(managerid)
)

alter table manageinfo 
modify name not null;

--直接插入数据
insert into manageinfo(managerid,loginName,password,name) values(1,'admin','super','liuwei')
insert into manageinfo(managerid,loginName,password,name) values(2,'ivy','super','yanyan')
insert into manageinfo(managerid,loginName,password,name) values(3,'nate','super','weiwei')

<--从select结果创建并插入数据-->
create table logininfo as select loginname,password from manageinfo

select * from logininfo

delete from logininfo
--数据更新
update manageinfo set name='weiwei' where loginname='admin'

delete from manageinfo where managerid=1

select * from manageinfo

--更新插入
merge into logininfo
using manageinfo
on (logininfo.loginname=manageinfo.loginname)
when matched then update set logininfo.password=manageinfo.password
when not matched then insert values(manageinfo.loginname,manageinfo.password)

 

--Select语句
select managerid as ID,manageinfo.loginname as 登录名,manageinfo.password as 密码  from manageinfo

 

insert into productinfo(ProId,ProName,ProPrice,Quantity,Procategory,description,origin) values (1,'三星5830i',3000,50,01,'三星手机','anycall')

insert into productinfo(ProId,ProName,ProPrice,Quantity,Procategory,description,origin) values (2,'三星9100i',4000,30,01,'三星手机','anycall')

insert into productinfo(ProId,ProName,ProPrice,Quantity,Procategory,description,origin) values (3,'iphone4',3000,12,02,'苹果手机','apple')

insert into productinfo(ProId,ProName,ProPrice,Quantity,Procategory,description,origin) values (6,'iphone4',3000,12,02,'苹果手机','apple')

insert into productinfo(ProId,ProName,ProPrice,Quantity,Procategory,description,origin) values (4,'nokia5300',1000,50,03,'诺基亚手机','nokia')

insert into productinfo(ProId,ProName,ProPrice,Quantity,Procategory,description,origin) values (5,'iphone5',5000,50,02,'5s手机','apple')

 

insert into categoryinfo(categoryinfo.categoryid,categoryinfo.categoryname) values (01,'三星')
insert into categoryinfo(categoryinfo.categoryid,categoryinfo.categoryname) values (02,'苹果')
insert into categoryinfo(categoryinfo.categoryid,categoryinfo.categoryname) values (03,'nokia')
insert into categoryinfo(categoryinfo.categoryid,categoryinfo.categoryname) values (04,'摩托罗拉')

select * from productinfo

select * from categoryinfo

--表达式
select distinct ProId ID,
                ProName 名称,
                ProPrice || '*' || '1.25' || '=' || ProPrice * 1.25 as  价格,
                Quantity,
                Procategory,
                description,
                origin
  from productinfo
 order by 价格 desc Nulls first,quantity asc
--order by用法
--升序降序，使用别名，多个字段排序，对null值得处理

--使用表达式
select distinct ProId ID,
                ProName 名称,
                ProPrice,
                ProPrice || '*' || '1.25' || '=' || ProPrice * 1.25 as 价格,
                Quantity,
                Procategory,
                description,
                origin,
                ProPrice * quantity as ww
  from productinfo
 order by ProPrice * quantity desc Nulls first

--字段位置 3表示价格字段
select distinct ProId ID,
                ProName 名称,
                ProPrice,
                ProPrice || '*' || '1.25' || '=' || ProPrice * 1.25 as 价格,
                Quantity,
                Procategory,
                description,
                origin,
                ProPrice * quantity as ww
  from productinfo
 order by 3 desc Nulls first

--设置检索条件

--单一条件查询
select distinct ProId ID,
                ProName as 名称,
                ProPrice,
                ProPrice || '*' || '1.25' || '=' || ProPrice * 1.25 as 价格,
                Quantity,
                Procategory,
                description,
                origin,
                ProPrice * quantity as ww
  from productinfo
  where quantity between 10 and 50
  and ProName like '三星%'
 order by 3 desc Nulls first

--分组
select distinct ProId ID,
                ProName as 名称,
                ProPrice,
                AVG(ProPrice),
                Procategory
  from productinfo
 where quantity between 10 and 50
 group by Procategory, ProId, ProName, ProPrice
 having AVG(ProPrice)<4500
 order by 3 desc Nulls first
--having只对group by负责

--单值嵌套查询
select distinct ProId ID,
                ProName as 名称,
                ProPrice,
                AVG(ProPrice),
                Procategory
  from productinfo
 where ProPrice < (select Max(proprice) from productinfo)
 and ProPrice > (select Min(proprice) from productinfo)
 group by Procategory, ProId, ProName, ProPrice
having AVG(ProPrice) < 4500
 order by 3 desc Nulls first

--多值嵌套查询
select distinct ProId ID,
                ProName as 名称,
                ProPrice,
                AVG(ProPrice),
                Procategory
  from productinfo
 where ProPrice > some (select MiN(proprice) from productinfo)
 group by Procategory, ProId, ProName, ProPrice
having AVG(ProPrice) < 4500

--   +号外连接
select * from productinfo,categoryinfo where productinfo.procategory(+)=categoryinfo.categoryid

--内连接，与上句等价，inner可省略
select * from productinfo p inner join categoryinfo c on p.procategory = c.categoryid
select * from productinfo p  join categoryinfo c on p.procategory = c.categoryid

--自连接，获取数量想等的不同茶品
select *
  from productinfo p
  join productinfo pr
    on p.quantity = pr.quantity
   and p.proid != pr.proid
   and p.rowid > pr.rowid
   
   
   update productinfo set procategory = '' where  proid=6
   --外连接
select *
  from productinfo p
  left join categoryinfo c
    on p.procategory = c.categoryid

select *
  from productinfo p
 right join categoryinfo c
    on p.procategory = c.categoryid

select *
  from productinfo p
 full join categoryinfo c
    on p.procategory = c.categoryid

原文见：http://blog.csdn.net/dw379936691/article/details/9014021