oracle基本操作

--create tablespace [表空间的名字]
--datafile '[数据文件的路径]'
--size nM
--autoextend on next nM

--创建用户 密码 对应表空间
create user web_order identified by 123456 default   tablespace user_order_details
grant dba to web_order

--数字
number --代表整型和浮点型
number(p,s) p代表整体长度,s代表小数的精度
number(5,2) 整数部分为3,小数部分为2

int 实际上是number的子集

number(10,0) 这样就代表的是一个整数

--字符

--定长字符
char
char(10) 代表创建一个定长字符,最多只能存放10个字符,如果不到10个,那么整个定长字符所占空间还是10个

--非定长字符

--unicode非定长字符
nvarchar
nvarchar2

--非unicode非定长字符
varchar
varchar2

--日期
date 2017-08-10

datetime 2017-08-10 09:41:13

Timestamp 时间戳 20170810094113


text 大文本

clob char large object

blob binary

--创建表
create table student
(
stu_id int,
stu_name varchar(20),
stu_age number(3,0),
stu_gender char(2),
stu_birthday date,
clazz_id int
)


ddl 数据库定义语言

--添加主键
alter table student add constraint pk_stu_id primary key(stu_id)

索引 聚集索引

insert into student(stu_id,stu_name,stu_age,stu_gender,stu_birthday,clazz_id)
values(1,'魏洋',22,'男',to_date('1995-08-10','yyyy-MM-dd'),1)

drop table student

drop table clazz

create table clazz
(
clazz_id int,
clazz_name varchar(20)
)
alter table clazz add constraint pk_clazz_id primary key(clazz_id)

insert into clazz(clazz_id,clazz_name) values(1,'J0626')

--外键约束
alter table student add constraint fk_clazz_id foreign key(clazz_id) references clazz(clazz_id)

--级联置空
alter table student add constraint fk_clazz_id foreign key(clazz_id) references clazz(clazz_id) on delete set null
--级联删除
alter table student add constraint fk_clazz_id foreign key(clazz_id) references clazz(clazz_id) on delete cascade

delete from student where stu_id=2
--oracle 从删库到跑路

truncate --彻底删除

delete from clazz where clazz_id=1


update student set stu_name='阳跻',stu_age=24 where stu_id=1

--check约束
insert into student(stu_id,stu_name,stu_age,stu_gender,clazz_id)
values(4,'阳跻1',22,'无',1)

alter table student add constraint ck_stu_gender check (stu_gender in('男','女'))

alter table student add constraint ck_stu_age check (stu_age <120)

--unique约束 唯一

alter table student add constraint uk_stu_name unique(stu_name)

--非空约束 not null

alter table student modify stu_birthday constraint nt_stu_birthday not null

--空 null

--修改表名
rename students to student

--删除约束
--删除非空约束
alter table student drop constraint nt_stu_birthday
--删除unique约束
alter table student drop constraint uk_stu_name
--删除check约束
alter table student drop constraint ck_stu_gender
--删除主键约束
alter table student drop constraint pk_stu_id

--添加字段
alter table student add parentName varchar(20)
--删除字段
alter table student drop column parentName

--修改字段名称
alter table student add parentName varchar(20)

alter table student rename column parentName to pname


--修改字段的类型
alter table student modify pname number

--添加字段默认值
alter table student modify parentName varchar(20) default '他爹他娘'

insert into student(stu_id,stu_name) values(6,'小王')



--查询所有字段
--*值通配所有字段名
select * from products


--指定字段名
--如果有多个字段,用,隔开
select productID,productName,quantityPerUnit,UnitPrice,supplierid,categoryID,unitsInStock,Unitsonorder,reorderlevel,discontinued from products


--起别名
select productName 商品名称,unitPrice 商品单价 from products

select productName "商品 名称",unitPrice "商品 单价" from products

--去重
select distinct(categoryID) from products

--字符串的拼接
select '商品的名称是:'||productName 商品 from products


--查询语句展示出来的结构不是表实际的物理结构,而是视图(view)

--判断条件
--比较运算符

-- > 大于
select * from products where unitPrice>50
-- >= 大于等于
select * from products where unitPrice>=55
-- < 小于
select * from products where unitPrice<10
-- <= 小于等于
select * from products where unitPrice<=6
-- != 不等于
select * from products where unitPrice!=6
-- <> 不等于
select * from products where unitPrice<>6
-- = 等于
select * from products where unitPrice=6

--and 代表的是与,意思是左右两边的判断需要同时满足
select * from products where unitPrice > 20 and categoryID=1

--or
select * from products where unitPrice>50 or categoryID=8

--not
select * from products where not unitPrice=97

insert into products (productid,productName) values(78,'老干妈')

--is null代表是空的,不能写成=null
select * from products where categoryID is null


select * from products where categoryID is not null


--between
select * from products where unitPrice between 10 and 20

--in 不连续的情况
select * from products where categoryID in (1,2,8)
select * from products where categoryID =1 or categoryID=2 or categoryID=8

--模糊查询
-- _指匹配任意一个字符
select * from products where productName like '_niseed Syrup'
-- %指匹配零个或多个字符
select * from products where productName like 'Chai%'

select * from products where productName like '%ob%'


--聚合函数

--统计 count
select count(*) 总数量 from products
select count(productID) from products
--如果字段为null,则不计入统计
select count(categoryID) from products

--求和 sum
select sum(unitPrice) 所有商品的总价格 from products
select sum(unitsinstock) from products

--求平均数
select avg(unitPrice) from products
select sum(unitPrice)/count(productID) from products


--分组 group by
select count(productID),categoryID from products group by categoryID
--having 指对于group by之后结果进行判断 where 指对于原来的数据结果进行判断
select count(productID),categoryID from products group by categoryID having count(productID) >=10

--max
select max(unitPrice) from products

--min
select min(unitPrice) from products


--order by
--desc降序
select * from products order by unitPrice desc
--asc
select * from products order by unitPrice asc

select count(productID) from products where unitPrice>10 group by categoryID having count(productID) >5 order by count(productID) asc


--(a+b)(c+d)=ac+ad+bc+bd

--交叉连接,会得到一个笛卡尔乘积
--78*8=624
select * from products cross join categories
select * from products,categories


--自然连接,如果连接的字段有空值,则不会对应
select * from products natural join categories
select a.productname,b.categoryname from products a natural join categories b

select * from products a,categories b where a.categoryid=b.categoryid

select count(productID),categoryID from products group by categoryID

select * from categories a,(select count(productID) 商品数量,categoryID from products group by categoryID) b where a.categoryID=b.categoryID

--自连接
create table department
(
did int,
dname varchar(20),
parentid int
)
alter table department add constraint pk_did primary key(did)

insert into department(did,dname,parentID) values(1,'董事会',null);
insert into department(did,dname,parentID) values(2,'财务部',1);
insert into department(did,dname,parentID) values(3,'技术部',1);
insert into department(did,dname,parentID) values(4,'财务一部',2);
insert into department(did,dname,parentID) values(5,'财务二部',2);
insert into department(did,dname,parentID) values(6,'技术一部',3);
insert into department(did,dname,parentID) values(7,'技术二部',3);


select * from department

select a.dname 上级部门,b.dname 部门名称 from department a,department b where a.did=b.parentID

--左连接,左外连接
--(+) 用来标记右表
select * from products a,categories b where a.categoryID=b.categoryID(+) order by productID
select * from products a left outer join categories b on a.categoryid=b.categoryid



--右连接
select * from products a,categories b where a.categoryID(+)=b.categoryID order by productID
select * from products a right outer join categories b on a.categoryid=b.categoryid


--全连接
select * from products a full outer join categories b on a.categoryID=b.categoryID
insert into categories(categoryid,categoryName) values(9,'饮料')

-- > any 比最小的值要高

select *
from products
where unitprice > any
(select unitprice from products where categoryid = 2)
order by unitprice asc
select min(unitprice) from products where categoryid = 2


-- < any 比最大的值要小
select *
from products
where unitprice < any
(select unitprice from products where categoryid = 2)
order by unitprice desc
select max(unitprice) from products where categoryid = 2

-- > all 比最大的值还要高
select *
from products
where unitprice > all
(select unitprice from products where categoryid = 2)
select max(unitprice) from products where categoryid = 2

-- < all 比最小的值还要小
select *
from products
where unitprice < all
(select unitprice from products where categoryid = 2)
select min(unitprice) from products where categoryid = 2


--case when

select productname,
case
when unitprice between 0 and 10 then
'便宜'
when unitprice between 10 and 50 then
'可以接受的'
when unitprice > 50 then
'太贵了'
else
'该商品没有正确价格'
end 商品接受程度,
unitprice
from products

create table person
(
pid int,
pname varchar(20),
gender int
)

insert into person(pid,pname,gender) values(1,'小明',1);
insert into person(pid,pname,gender) values(2,'小红',2);
insert into person(pid,pname,gender) values(3,'小王',1);

select pid,
pname,
case
when gender = 1 then
'雄'
when gender = 2 then
'雌'
end 性别
from person


--填充函数
--dual是一个虚拟表,用来匹配SQL语句
--第一个参数指要去填充的字符串
--第二个参数指填充后的长度
--第三个参数指用来填充的内容
select lpad('a',10,'*') from dual;
select rpad('a',10,'*') from dual;

--拼接函数
select concat('学生的名字是','朱一帆') from dual

--截取函数
--第一个参数指要截取的字符串
--第二个参数指从字符串的什么位置开始
--第三个参数指一共截取多少位
select substr('asdfqwer',3,3) from dual

-- 13247174155
select from dual
select substr('13247174155',8,4) from dual



select concat(rpad(substr('13247174155',1,3),7,'*'),substr('13247174155',8,4)) from dual



create table "user"(phone number(11,0))
insert into "user"(phone) values(13247174155);
insert into "user"(phone) values(13247174555);
insert into "user"(phone) values(18647174157);
insert into "user"(phone) values(15947174795);


create table "dictionary"(dcon varchar(5),dtype varchar(20))
insert into "dictionary"(dcon,dtype) values('1','lstart');
insert into "dictionary"(dcon,dtype) values('3','lend');
insert into "dictionary"(dcon,dtype) values('7','filllength');
insert into "dictionary"(dcon,dtype) values('*','fillcontent');
insert into "dictionary"(dcon,dtype) values('8','rstart');
insert into "dictionary"(dcon,dtype) values('4','rend');


select phone from "user"


select concat(rpad((substr(phone,
(select dcon
from "dictionary"
where dtype = 'lstart'),
(select dcon
from "dictionary"
where dtype = 'lend'))),
(select dcon from "dictionary" where dtype = 'filllength'),
(select dcon from "dictionary" where dtype = 'fillcontent')),
(substr(phone,
(select dcon from "dictionary" where dtype = 'rstart'),
(select dcon from "dictionary" where dtype = 'rend'))))
from "user"


update "dictionary" set lend='2',rend='2',rstart='10'
update "dictionary" set dcon='2' where dtype='lend';
update "dictionary" set dcon='2' where dtype='rend';
update "dictionary" set dcon='10' where dtype='rstart';




--length
select length('asdf') from dual
--instr
select instr('asdfss','s') from dual
--round 取整
select round(2.456,2) from dual
--trunc 截断
select trunc(2.456,2) from dual
--mod
select mod(10,3) from dual

select trunc(dbms_random.value*10,0) from dual

0<=dbms_random.value<1


--to_date 转换成日期
--to_number 转换成数字
--to_char 转换成字符串

select to_date('2017-06-09','yyyy-MM-dd') from dual

select to_number('1') from dual
select to_char('1') from dual


select sysdate from dual
--日期的加减以天位单位
select sysdate-1 from dual

select sysdate-1/24 from dual

select sysdate-1/24/60 from dual

--months_between

select months_between(sysdate,to_date('2017-06-01','yyyy-MM-dd')) from dual

--add_months
select add_months(sysdate,10) from dual

--next_day
select next_day(sysdate,2) from dual

--last_day
select last_day(sysdate) from dual


--nvl

select nvl(quantityperunit,'该字段没有值'),productname from products


--decode
--decode(要用来做判断的字段,该字段的值,该值对应展示的内容,该字段的值1,该值对应展示的内容1...默认值)
select decode(clazz_id,1,'这是一班的','这是不知道是哪个班的'),stu_name,clazz_id from student

select
case
when clazz_id=1 then '这是一班的'
when clazz_id=2 then '这是二班的'
end
from student



select a.productid,a.quantityperunit,a.productname,a.unitPrice,b.categoryid,b.categoryname from products a, categories b where a.categoryID=b.categoryID

--view视图
create view view_test
as
select a.productid,a.quantityperunit,a.productname,a.unitPrice,b.categoryid,b.categoryname from products a, categories b where a.categoryID=b.categoryID



select * from view_test
insert into view_test(productid,productname,quantityperunit,unitprice)
values(79,'雪碧','瓶',3)


delete from view_test2 where productid=78

update view_test1 set productname='可乐' where productid=79


create view view_test1
as
select * from products

--只读视图
create view view_test2
as
select * from products
with read only


--plsql编程
declare --声明变量关键字
--变量名称 变量类型;
--变量名称 变量类型 :=变量的值
v_test varchar(20) :='hello world';
--being end 相当于java中的花括号,要执行的代码放到begin和end之间,end后要加分号结束
begin
dbms_output.put_line(v_test);
end;


declare
v_test varchar(20);
begin
v_test:='hello world';
dbms_output.put_line(v_test);
end;


declare
v_test varchar(20);
begin
--从数据库中表中查出数据赋值给变量
select productname into v_test from products where productid=1;
dbms_output.put_line(v_test);
end;

declare
v_pname varchar(20) :='可口可乐';
v_pid int :=80;
begin
insert into products(productid,productname) values(v_pid,v_pname);
commit;
end;

declare
v_pname varchar(20) :='百事可乐';
v_pid int :=80;
begin
update products set productname=v_pname where productid=v_pid;
commit;
end;

declare
v_pid int := 80;
begin
delete from products where productid = v_pid;
commit;
end;



--if
declare
v_num number:=1;
begin
if v_num=1 then
dbms_output.put_line('v_num是等于1的');
end if;
end;
--else
declare
v_num number:=2;
begin
if v_num=1 then
dbms_output.put_line('v_num是等于1的');
else
dbms_output.put_line('v_num是不等于1的');
end if;
end;

--elsif
declare
v_num number := 2;
begin
if v_num = 1 then
dbms_output.put_line('v_num是等于1的');
elsif v_num = 2 then
dbms_output.put_line('v_num是等于2的');
else
dbms_output.put_line('v_num是既不等于1也不等2的');
end if;
dbms_output.put_line('over');
end;


--loop循环
declare
v_index int := 0;
begin
loop
if v_index < 10 then
dbms_output.put_line('当前v_index的值为' || v_index);
v_index := v_index + 1;
else
exit;
end if;
end loop;
end;

declare
v_id int := 5;
v_name varchar(20);
v_age int := 22;
v_cid int := 1;
begin
loop
if v_id < 15 then
insert into student
(stu_id, stu_name, stu_age, clazz_id)
values
(v_id, '学生' || v_id, v_age, v_cid);
commit;
v_id := v_id + 1;
else
exit;--退出循环
end if;
end loop;
end;


--触发器 类似于js中的dom事件监听器
--insert delete update 触发的动作
--before after 触发的时机
--表级触发器
create trigger tri_products
before insert
on products
begin
dbms_output.put_line('你现在正在插入数据');--当进行插入数据的时候,会执行这里的代码
end;
drop trigger tri_products;


insert into products(productid,productname) values(81,'可口可乐');

create trigger tri_products_delete
before delete
on products
begin
dbms_output.put_line('你现在正在删除数据');--当进行插入数据的时候,会执行这里的代码
end;

delete from products where productid=81;


create trigger tri_products_update
before update
on products
begin
dbms_output.put_line('你现在正在更新数据');--当进行插入数据的时候,会执行这里的代码
end;


update products set productname='asdf' where productid=79;


create table pro
(
pid int ,
pname varchar(20),
unitsinstock int
);
alter table pro add constraint pk_pid primary key(pid);

insert into pro(pid,pname,unitsinstock) values(1,'可口可乐',10);

create table od
(
odid int,
pid int,
quantity int
);
alter table od add constraint pk_odid primary key(odid);


create or replace trigger tri_add_pro
before insert
on od
for each row
begin
--dbms_output.put_line('新插入的商品的id为'||:new.pid);
--dbms_output.put_line('新插入的商品的数量为'||:new.quantity);
--dbms_output.put_line('这是表级触发器');
update pro set unitsinstock=unitsinstock-:new.quantity where pid=:new.pid;
end;


insert into od(odid,pid,quantity) values(1,1,2);
insert into od(odid,pid,quantity) values(2,1,2);
insert into od(odid,pid,quantity) values(3,1,2);

truncate table od;


create or replace trigger tri_pro_table
before update
on products
for each row
begin
dbms_output.put_line('这是表级触发器');
end;

update products set productname='sadf' where categoryid=1;


--sequence
create sequence seq_od
start with 1--从1开始增加
increment by 1--每次增加1
--maxvalue 能够增长到的最大值
--minvalue 最小值
--maxvalue 9
--cycle --是否循环
--20170817172105009764
insert into od(pid,quantity) values(1,2);

create trigger tri_od_insert
before insert
on od
for each row
begin
select seq_od.nextval into :new.odid from dual;
end;

select seq_od.currval from dual;

--存储过程 定义一个已经被编译过的sql语句
create or replace procedure pro_test as
begin
dbms_output.put_line('hello world');
end;

begin
pro_test();
end;


create or replace procedure pro_test1(v_in in varchar)
as
begin
dbms_output.put_line('hello '||v_in);
end;

declare
v_name varchar(20):='张蕲成';
begin
pro_test1(v_name);
end;

create or replace procedure pro_test2(v_in in int, v_out out varchar) as
begin
select productname into v_out from products where productid = v_in;
end;

declare v_pid int := 4; v_pname varchar(100);
begin
pro_test2(v_pid, v_pname); dbms_output.put_line(v_pname);
end;


create or replace procedure pro_test3(v_in in out varchar,v_out in out varchar)
as
v_temp varchar(20);
begin
v_temp:=v_in;
v_in:=v_out;
v_out:=v_temp;
end;

declare
v_a varchar(20) := '变量a';
v_b varchar(20) :='变量b';
begin
pro_test3(v_a, v_b);
dbms_output.put_line(v_a);
dbms_output.put_line(v_b);
end;



create or replace trigger tri_od_insert
before insert
on od
for each row
begin
pro_generate_id(:new.odid);
end;


select from dual;


insert into od(pid,quantity) values(1,1);




alter table od modify odid varchar(50);


create or replace procedure pro_generate_id(v_id out varchar) as
begin
select to_char(sysdate, 'yyyymmddhhmiss') ||
substr(to_char(trunc(dbms_random.value * 1000000, 0)), 2, 4)
into v_id
from dual;
end;


=======

--存储过程 定义一个已经被编译过的sql语句
create or replace procedure pro_test as
begin
dbms_output.put_line('hello world');
end;

begin
pro_test();
end;


create or replace procedure pro_test1(v_in in varchar)
as
begin
dbms_output.put_line('hello '||v_in);
end;

declare
v_name varchar(20):='张蕲成';
begin
pro_test1(v_name);
end;

create or replace procedure pro_test2(v_in in int, v_out out varchar) as
begin
select productname into v_out from products where productid = v_in;
end;

declare v_pid int := 4; v_pname varchar(100);
begin
pro_test2(v_pid, v_pname); dbms_output.put_line(v_pname);
end;


create or replace procedure pro_test3(v_in in out varchar,v_out in out varchar)
as
v_temp varchar(20);
begin
v_temp:=v_in;
v_in:=v_out;
v_out:=v_temp;
end;

declare
v_a varchar(20) := '变量a';
v_b varchar(20) :='变量b';
begin
pro_test3(v_a, v_b);
dbms_output.put_line(v_a);
dbms_output.put_line(v_b);
end;


create or replace trigger tri_od_insert
before insert
on od
for each row
begin
pro_generate_id(:new.odid);
end;


select from dual;


insert into od(pid,quantity) values(1,1);




alter table od modify odid varchar(50);


create or replace procedure pro_generate_id(v_id out varchar) as
begin
select to_char(sysdate, 'yyyymmddhhmiss') ||
substr(dbms_random.value,3,4)
into v_id
from dual;
end;

>>>>>>> .r42

---连表查询

案例一
SELECT products.CategoryID,ProductName,c.sl FROM products,
(
SELECT a.sl,a.ProductID FROM
(
SELECT sum(quantity)sl ,ProductID FROM `order details` group BY ProductID
)a
,
(
SELECT MAX(sl) sl FROM
(
SELECT sum(quantity)sl ,ProductID FROM `order details` group BY ProductID
)a
)b WHERE a.sl=b.sl
)c WHERE c.ProductID=products.ProductID

--案例二
SELECT * FROM suppliers,
(
SELECT a.count_category,a.supplierid  FROM
(
SELECT COUNT(DISTINCT(categoryid)) count_category ,supplierid FROM products GROUP BY supplierid
)a,
(
SELECT MAX(count_category) count_category FROM
(
SELECT COUNT(DISTINCT(categoryid)) count_category ,supplierid FROM products GROUP BY supplierid
)a)b
WHERE a.count_category=b.count_category
)c
WHERE suppliers.supplierid=c.supplierid

  
 

 

>>>>>>> .r42