oracle练习

oracle 练习一:

CREATE TABLE ORDERS (
    "ID" VARCHAR2(10) NOT NULL,
    "PRODUCTID" VARCHAR(10) NOT NULL,
    "QUANTITY" NUMBER(10),
    "PRICE"  NUMBER(10),
    "USERID" VARCHAR(10),
    "ORDERDATE" DATE DEFAULT sysdate,
    PRIMARY KEY("ID")
) ;

CREATE TABLE PRODUCT
(
  "ID" VARCHAR(10) NOT NULL,
  "NAME" VARCHAR(10) NOT NULL,
  "PRICE" NUMBER(10),
  PRIMARY KEY("ID")
);

INSERT INTO PRODUCT(ID,NAME,PRICE) VALUES('1','CAT',50);
INSERT INTO PRODUCT(ID,NAME,PRICE) VALUES('2','DOG',25);
INSERT INTO PRODUCT(ID,NAME,PRICE) VALUES('3','FISH',42);
INSERT INTO PRODUCT(ID,NAME,PRICE) VALUES('4','PIGEON',34);

INSERT INTO ORDERS(ID,PRODUCTID,QUANTITY,PRICE,USERID) VALUES('1','1',2,50,2);
INSERT INTO ORDERS(ID,PRODUCTID,QUANTITY,PRICE,USERID,ORDERDATE) VALUES('1','4',2,34,2,TO_DATE('2008-04-16','yyyy-mm-dd'));
INSERT INTO ORDERS(ID,PRODUCTID,QUANTITY,PRICE,USERID,ORDERDATE) VALUES('4','1',20,150,3,TO_DATE('2008-02-18','yyyy-mm-dd'));

INSERT INTO ORDERS(ID,PRODUCTID,QUANTITY,PRICE,USERID) VALUES('4','1',2,50,1);
INSERT INTO ORDERS(ID,PRODUCTID,QUANTITY,PRICE,USERID) VALUES('5','4',1,34,1);
INSERT INTO ORDERS(ID,PRODUCTID,QUANTITY,PRICE,USERID) VALUES('6','3',3,42,1);


SELECT id, TRUNC((SYSDATE - orderdate)/365) FROM orders;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select userid, sum(price*quantity) from orders group by userid;

select a.id,a.username,b.id,b.userid from users a,orders b where a.id=b.userid

select o.id as 订单号, u.username as 用户名, o.price as 产品价格 from orders o,users u where o.userid=u.id;

--查询订单号，订单产品名称，用户名，订单价格，每种商品购买总额
select o.id,p.name,p.price,p.price*o.quantity   from orders o, users u, product p
where o.userid=u.id and o.productid=p.id

select * from product where id = (select productid from orders where userid=1 and productid=4);
--查询  查询任意一个用户，订单行内小计超过100元，

--检索订单总额最大的用户
select * from users where id =
(
   select userid from
   (
     select userid, sum(price*quantity) as total
     from orders group by userid order by total desc
   )
   where rownum=1
);

select * from product where id in (select productid from orders);

select * from users where id in(select userid from orders);


oracle练习二

--批量执行
start d:\*.sql;

--查看大字短的大小
SELECT DBMS_LOB.GETLENGTH(MYCLOB) FROM ATTACHMENT;

SELECT DBMS_LOB.GETLENGTH(MYBLOB) FROM ATTACHMENT;

--设置控制台尺寸
set linesize 150;

--创建新表
CREATE TABLE ORDER1
(
  "ID" VARCHAR(10) NOT NULL,
  "PRODUCTID" VARCHAR(10) NOT NULL,
  "QUANTITY" VARCHAR(10) NOT NULL,
  "PRICE" NUMBER(10),
  PRIMARY KEY("ID")
);


--插入多行记录
insert into orders5 (id,productid,quantity)
(
   select id,productid,quantity from orders where quantity>1
);
select * from orders5;


--复制表
create table orders2 as
(
select id,userid,quantity,price from orders
);


--根据多个表中的某些指定列建立一个新表，并写入符合条件的数据
create table orders3
as
(
select u.id,o.price,o.quantity from orders o,users u where u.id=o.userid
);


--撤消
rollback;

--创建保存点
savepoint abc35;
savepoint abc36;
savepoint abc37;

--回退到指定点
rollback to abc36

--更新多行
update orders3 set price=45 where quantity between 2 and 3;

--嵌套更新<将原表的一空列更新成此表的一指定列计算结果更新至空列>
update order1 set price=
(
   select quantity+100 from order1 where id=1
)
where id=1;

--两张表的嵌套更新
update order1 set price=
(
   select o.price+200 from orders3 o,order1 r where o.id=2 and r.id=2
)
where id in(3,5);

--删除记录
delete from orders where id=1;

--删除表快速删除表数据
advantage tabelname;

--删除后不能恢复
disadvantage tablename;

truncate table tablename;

--创建序列
create sequence mysequence start with 1;
查看序列表
select * from user_sequences;

--为列
select user from dual;
--访问序列
select mysequence.nextval from dual;
--插入记录
insert into product(id,name,price) values(mysequence,nextuval,'aaa',45);
--查看当前使用的最大的序列值
select mysequence.currval from dual;

insert into products(pid,name,price) values(mysequence.nextval,'bb',145);

insert into orders(id,productid,quantity,price,userid) values(mysequence.nextval,mysequence.nextval,45,100,1);

--查看当前使用的最大序列值
select mysequence.currval from products;

select mysequence.nextval from products;


--查看当前时间和下单时间
select id,orderdate,sysdate from orders;
--删除一行记录
delete from orders5 where id=1;
--删除整表的内容
truncate table orders5;

//创建用户
CREATE USER "CM" PROFILE "DEFAULT"
  IDENTIFIED BY "1234" DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "USERS"
ACCOUNT UNLOCK;

--绑定角色<授权>
GRANT "CONNECT" TO "CM";

--创建视图
create view viewname as sql_command;

create or replace view viewtest as select * from

--创建视图
create or replace view myview as select o.id,p.price from orders o,products p where p.pid=o.productid;



--从多张表中取出用户名，用户所购买的产品名称，购买产品的价格，产品的数量，用户的email地址，用户的下单时间（日：时：分），用户的注册日期

--并把上述结构创建为视图。

create or replace view myview as
select o.id,u.name,p.names,p.price,o.quantity,u.email,o.orderdate,u."regdate" from orders o,products p,users u where o.id=p.pid and o.id=u.id;

update users set "regdate"=to_date('2008-10-10 21:21') where id in(1,2,3,4,5,6,7,8,9,10);


--使SQLPLUS控制台可以输出
set serveroutput on
set serveroutput off

--控制台输出
declare
begin
dbms_output.put_line('this is ');
end;
/

declare
aaa varchar2(100);
begin
dbms_output.put_line((2+3)*6);
end;
/



--存储过程
create or replace procedure
  proname
   (id in number) as names varchar(10);
begin
insert into products(pid,names,price) values(mysequence.nextval,'xiaoliu',123);

end;
/

--运行
execute proname(23);

create or replace procedure
  pro
   (mynum in number) as str varchar(10);
begin
select count(*) into str from products;
dbms_output.put_line(str);
end;
/


--查询products表大于输入price的总个数
create or replace procedure mypro1(prprice in number)
as
total real;
begin
select count(*) into total from products where price>prprice;
dbms_output.put_line(total);
end;
/



--插入一行记录
insert into products(pid,names,price) values(mysequence.nextval,'aaa',45);

create or replace procedure mypro2(userid in number)
as
buffer varchar2(100);
status INTEGER;
usernames char(100);
begin
select name into usernames from users where id='1';
dbms_output.enable(20000);
dbms_output.put_line('user:' || usernames);
end;

--赋值语句< := >
create or replace procedure mypro4(w in integer,h in integer)
as
width integer :=w;
hight integer :=h;
area real;
begin
area :=(width+hight)*2;
dbms_output.put_line('area=' || area);
end;
/

--条件语句<if .. then ... end if>
create or replace procedure myro5(num in integer) as
n integer :=num;
begin
if n=5 then
dbms_output.put_line('ok');
else
dbms_output.put_line('not equals');
end if;
end;
/

--循环<while ** loop *** end loop; for * in ***loop end loop;>
create or replace procedure mypro6(n in integer) as
con integer :=0;
begin
dbms_output.put_line('while loop.=========');
while con<n loop
dbms_output.put_line(con);
con :=con+1;
end loop;

dbms_output.put_line('for loop ==========');
con :=0;
for con in 1..n loop
for con in n..1 loop
dbms_output.put_line('*');
end loop;
end loop;
end;
/

--游标cursor

create or replace procedure mypro7(w in integer) as
id products.pid%type;
name products.names%type;
price products.price%type;
cursor cursors is
select
pid,names,price
from products where price>w order by pid;
begin
open cursors;
loop
fetch cursors into id,name,price;
exit when cursors%notfound;
dbms_output.put_line('id:' || id || 'name:' || name || 'price:' || price);
end loop;
close cursors;
end;
/


--函数function
create or replace function prices
(
proprice in number
)
return integer
is
pricecount integer;
begin
select count(price) into pricecount from product where price>proprice;
if(pricecount >0) then
return pricecount;
else
return 0;
end if;
end prices;

declare
counter number := 0;
begin
counter := prices(30);
if counter >0 then
dbms_output.put_line('counts:'||counter );
else
dbms_output.put_line('counts:'||counter );
end if;
end;
/


create table journal(
id integer not null,
username varchar(20),
operation varchar(30),
operatetime date,
primary key(id)
);

create sequence sequ start with 1 maxvalue 999;

create or replace trigger sparks
after insert or update or delete
on orders2
declare
operation journal.operation%type;
begin
if inserting then
  operation :='insert';
elsif updateing then
  operation :='update';
elsif deleteing then
  operation :='delete';
else
  operation :='unknown';
end if;
dbms_output.put_line(operation);
insert into journal values(sque.nextval,user,operation,sysdate);
dbms_output.put_line('success');
end;
/




select o.id,p.name||'的数量是:'||o.quantity,p.productid,p. price from order1  o,product  p where  o.productid=p.productid

select * from order1 o,product p where o.productid(+)=p.productid

select o.id,o.quantity,p.name,p.price from order1 o natural join product p

select o.id,o.quantity,p.name,p.price from order1 o join product p using(productid) where productid=2


select o.id,p.name||'的数量是:'||o.quantity,p.productid,p. price from order1  o cross join product  p where  o.productid=p.productid


natural join natural join natural join
select * from order1 o left join product p on o.productid=p.productid

select * from order1 o right join product p on o.productid=p.productid


load data local infile 'd:/sql.txt' into table person;

按时间查询
select * from site_gameplayer where createTime BETWEEN '2009-07-23 00:00:00' and '2009-07-23 23:59:59'