create table jun.TB_BOOK(
book_id number not null primary key,
book_name varchar2(100) not null,
book_price float not null,
author_id number,
book_pagennumbers number,
constraints fk_book_author_001 foreign key (author_id)
references jun.tb_author(author_id) /*创建外键*/
);
--/*删除表*/
drop table jun.tb_book;
select*from jun.tb_book;
--插入一条记录
insert into jun.tb_book (book_id,book_name,book_price,book_pagennumbers)
values(1006,'loveqin',100.0,500);
--/*物理删除一条记录*/
delete from jun.tb_book where book_id=1001;
select*from jun.tb_book b
--/*查找id为1007的作者所写的书*/
inner join jun.tb_author a
on b.author_id=a.author_id
where b.author_id=(select author_id from jun.tb_book where book_id=1007)
;
alter table jun.tb_book add constraints fk_book_author_001
foreign key (author_id) references jun.tb_author(author_id);/*创建外键*/
alter table jun.tb_book drop constraints fk_book_author_001;/* 删除外键约束*/
alter table jun.tb_book add constraints check_001 check(book_price<200);/*创建check约束*/
alter table jun.tb_book drop constraints check_001;/*删除check外键*/
create table jun.tb_author(
author_id number not null primary key,
author_name varchar2(20)
);
alter table jun.tb_book drop (book_author);
alter table jun.tb_book add(author_id number);
alter table jun.tb_book rename to books; /*修改表名*/
alter table jun.tb_book drop(book_author);
alter table jun.tb_book add(book_author varchar2(30));
alter table jun.tb_book modify (book_author varchar2(40));/*修改字段的名称*/
update jun.tb_book set book_name='lovejava' where book_id=1001; /*更新一条记录*/
--创建序列递增+1
reate sequence jun.seq_user_id
start with 6
increment by 1
cache 20;
--数据分页
--页码1， 每页的数量2 （n-1）*2+1 and n*2
select *from
(select rownum rn, u.* from jun.blog_user u
where u.edu_background='本科') c
where c.rn between (1-1)*2+1 and 1*2
--改变字段的大小
alter table jun.blog_article
modify article_name varchar2(400)
--求和
select sum(
case when flag=1 then cash*(-1)
else cash
end) as "count"
from jun.account a
where a.card_no ='1001';
--按月份统计 按年份排列的查询语句
select to_char(create_date,'yyyy') as "year",
sum(case when to_char(create_date,'mm')='01' then 1 else 0 end) as "01",
sum(case when to_char(create_date,'mm')='02' then 1 else 0 end) as "02",
sum(case when to_char(create_date,'mm')='03' then 1 else 0 end) as "03",
sum(case when to_char(create_date,'mm')='04' then 1 else 0 end) as "04",
sum(case when to_char(create_date,'mm')='05' then 1 else 0 end) as "05",
sum(case when to_char(create_date,'mm')='06' then 1 else 0 end) as "06",
sum(case when to_char(create_date,'mm')='07' then 1 else 0 end) as "07",
sum(case when to_char(create_date,'mm')='08' then 1 else 0 end) as "08",
sum(case when to_char(create_date,'mm')='09' then 1 else 0 end) as "09",
sum(case when to_char(create_date,'mm')='10' then 1 else 0 end) as "10",
sum(case when to_char(create_date,'mm')='11' then 1 else 0 end) as "11",
sum(case when to_char(create_date,'mm')='12' then 1 else 0 end) as "12"
from jun.blog_article
group by to_char(create_date,'yyyy');
--创建存储过程
create or replace procedure jun.sp_article_insert
(user_id varchar2,
artilce_name varchar2,
article_content varchar2,
last_modify date,
create_date date,
click_number number ,
author_name varchar2)
as
begin
insert into jun.blog_article values(
jun.seq_blog_article_article_id.nextval,
user_id,artilce_name,article_content,
last_modify,create_date,click_number,author_name
);
end ;
drop procedure jun.sp_article_insert
--定义一个游标
declare
rowValue jun.blog_article%rowtype;--定义一个行类型
cursor myCursor is
select *from jun.blog_article where user_id='zhangsan';
begin
open myCursor;
loop
fetch myCursor into rowValue;--抓取一行数据
exit when myCursor%notfound;
dbms_output.put_line(rowValue.article_id);
end loop;
close myCursor;
end;
--创建包
create or replace package jun.pk_article is
procedure sp_article_insert(
user_id varchar2,
artilce_name varchar2,
article_content varchar2,
last_modify date,
create_date date,
click_number number ,
author_name varchar2,
article_id out number);
type type_ref_cursor is ref cursor;
procedure sp_article_delete
(v_article_id jun.blog_article.article_id%type);
procedure sp_article_findall_by_user_id
(v_user_id jun.blog_article.user_id%type,
mycursor out type_ref_cursor);
end;
--创建包 body
create or replace package body jun.pk_article is
procedure sp_article_insert
(user_id varchar2,
artilce_name varchar2,
article_content varchar2,
last_modify date,
create_date date,
click_number number ,
author_name varchar2,
article_id out number)
as
begin
insert into jun.blog_article values(
jun.seq_blog_article_article_id.nextval,
user_id,artilce_name,article_content,
last_modify,create_date,click_number,author_name
);
select jun.seq_blog_article_article_id.currval
into article_id from dual;
end;
procedure sp_article_delete
(v_article_id jun.blog_article.article_id%type)
is
begin
delete from jun.blog_article
where article_id =v_article_id;
end;
procedure sp_article_findall_by_user_id
(v_user_id jun.blog_article.user_id%type,
mycursor out type_ref_cursor) is
begin
open mycursor for select *from jun.blog_article where user_id=v_user_id;
close mycursor;
end;
end;
