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'
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'