Oracle简单数据库操作

create table student(
stuid char(10),
name varchar2(20),
stuname varchar2(20),
stuage number);
-- 1、创建用户
create user shaoxin identified by a123456;
-- 为用户授权
-- 2、链接权限
grant connect to shaoxin;
-- 操作权限
grant create to shaoxin;
grant alter to shaoxin;
-- 3、总的操作权限
grant resource to shaoxin;
-- 4、登录数据库
conn shaoxin/a123456@orcl;
-- 数据库实例(一个数据库就是一个数据库实例)
-- 数据库方案(一个用户就是一个数据库方案)
-- 5、删除用户
drop user xiaozhang;
-- 管理员创建指定普通用户下的表
create table shaoxin.utab(
username varchar2(20),
pwd varchar2(20)
);
-- 插入表数据
insert into utab values('sx','123');
-- 练习
-- 创建迷你版图书馆里系统的图书表
create table bookuser(
id number(3);
username varchar2(20),
password varchar2（20）
);
create table book(
bookid char(10),
bookname varchar2(20),
statu number(1),
takebook date,
takecount number(2)
);
-- 显示表信息 desc tablename
-- 日期类型 date 关键字to_date('实际参数','数据格式')；其中分钟的关键字变成了mi
-- 增加字段 alter table tablename add (columnname 字段类型)
-- 修改字段的长度 alter table tablename modify(columnname 新的字段长度)
-- 修改字段类型的时候（要保证不能有数据）alter table tablename modify(column 新的字段类型);
-- 删除一个字段 alter tabel tablename drop column 要删除的字段名;
-- 修改表名 rename oldtablename to newtablename;
-- 删除表 drop table tablename;

-- 约束
-- not null（不能为空） ; unique （唯一，可以为空）; primary key （主键）;check ; foreign;
-- 1、建表的同时添加约束，
-- not null
-- check (column in ('',''))枚举类型，只能插入举例出的数据
-- check (column in (number <= column and number>=column))第二种检查方式，数据必须在指定范围内
-- 默认值 default value;
-- alter table customer modify address default '南昌';

-- 双主键primay key (column1,column2)

-- 外键约束 foreign
-- 方式一： references tablename(column)
-- 方式二： alter table student add constraint s_t_fk foreign key(stuid) references teacher(tid);

-- 主键约束 alter table student add constraint s primary key (sid)
-- 唯一性约束 alter table student add constraint sn unique(stuname)
-- 检查约束 alter table student add constraint ss check(stusalary >= 3000)

-- 非空约束 alter table student modify age not null;

--test
-- 商品
create table goods(
goodsId number primary key,
goodsName varchar2(20),
unitprice number check(unitprice > 0),
gcategory number(2),
provider varchar2(20)
);
-- 客户
create table customer(
custId number primary key,
name varchar2(20) not null,
address varchar2(20),
email varchar2(20) unique,
sex varchar2(20) default '男' check(sex='男' or sex='女'),
cardId number(12)
);
--购买
create table purchase(
custId number references customer(custId),
goodsId number references goods(goodsId),
nums number check(nums>=1 and nums<=30),
primary key(custId,goodsId)
);
//换种添加主键及外键约束的方式
create table purchase(
custId number ,
goodsId number ,
nums number check(nums>=1 and nums<=30)
);
alter table purchase add constraint pk primary key(custId,goodsId);
alter table purchase add constraint purchase_customer_fk foreign key(custId) references customer(custId);
alter table purchase add constraint purchase_goods_fk foreign key(goodsId) references goods(goodsId);

-- homework
-- 宠物表
create table category(
catId varchar2(10) primary key,
name varchar2(80) not null,
descn varchar2(255) not null
);
insert into category(catId,name,descn) values('1','小猫','山地野猫');
insert into category(catId,name,descn) values('2','中华田园犬','中国特有的土狗');
insert into category(catId,name,descn) values('3','猪','专门用于生产精华火腿的猪');
-- 产品表
create table product(
productId varchar2(10) primary key,
category varchar2(10) not null,
name varchar2(80) not null,
descn varchar2(255) not null
);
alter table product add constraint product_category_fk foreign key(category) references category(catId);
insert into product(productId,category,name,descn) values('1','1','猫类','山地野猫，生命长度大概十年');
insert into product(productId,category,name,descn) values('2','2','狗类','中华田园犬，中国特有的土狗');
insert into product(productId,category,name,descn) values('3','3','猪类','猪，专门用于生产精华火腿的猪');
insert into product(productId,category,name,descn) values('4','2','猪类','猪，专门用于生产精华火腿的猪');
insert into product(productId,category,name,descn) values('5','1','猫类','山地野猫，生命长度大概十年');
insert into product(productId,category,name,descn) values('6','2','狗类','中华田园犬，中国特有的土狗');
insert into product(productId,category,name,descn) values('7','3','猪类','猪，专门用于生产精华火腿的猪');
insert into product(productId,category,name,descn) values('8','2','猪类','猪，专门用于生产精华火腿的猪');
insert into product(productId,category,name,descn) values('9','2','猪类','猪，专门用于生产精华火腿的猪');
insert into product(productId,category,name,descn) values('10','1','猫类','山地野猫，生命长度大概十年');
insert into product(productId,category,name,descn) values('11','2','狗类','中华田园犬，中国特有的土狗');
insert into product(productId,category,name,descn) values('12','3','猪类','猪，专门用于生产精华火腿的猪');
insert into product(productId,category,name,descn) values('13','2','猪类','猪，专门用于生产精华火腿的猪');
-- 产品明细表
create table item(
itemId varchar2(10) primary key,
productId varchar2(10) not null,
listprice number(10,2) not null,
unitcost number(10,2) not null,
supplier integer not null,
status varchar2(2) not null,
attr1 varchar2(80) not null,
attr2 varchar2(80) not null,
attr3 varchar2(80) not null,
attr4 varchar2(80) not null,
attr5 varchar2(80) not null
);
alter table item add constraint item_product_fk foreign key(productId) references product(productId);

alter table item add constraint item_supplier_fk foreign key(supplier) references supplier(suppid);
alter table item add constraint item_inventory_fk foreign key(itemId) references inventory(itemid);
alter table item add constraint uniq unique(supplier);

insert into item(itemId,productid,listprice,unitcost,supplier,status,attr1,attr2,attr3,attr4,attr5) values(
'1','1','1200.67','1200',0001,'0','0','0','0','0','0');
insert into item(itemId,productid,listprice,unitcost,supplier,status,attr1,attr2,attr3,attr4,attr5) values(
'2','2','1524.33','1524',0002,'0','0','0','0','0','0');
insert into item(itemId,productid,listprice,unitcost,supplier,status,attr1,attr2,attr3,attr4,attr5) values(
'3','3','2131.67','2131',0003,'0','0','0','0','0','0');
-- 供应商信息表
create table supplier(
suppid integer primary key,
name varchar2(80) not null,
status varchar2(2) not null,
addr1 varchar2(80) not null,
addr2 varchar2(80) not null,
city varchar2(80) not null,
state varchar2(80) not null,
zip varchar2(5) not null,
phone varchar2(80) not null
);
insert into supplier values(1,'长城',1,'中国','江西','南昌',1,1,'18679654629');
insert into supplier values(2,'长城',1,'中国','江西','南昌',1,1,'18679654629');
insert into supplier values(3,'长城',1,'中国','江西','南昌',1,1,'18679654629');
-- 库存表
create table inventory(
itemid varchar2(10) not null,
qty integer not null
);
insert into inventory values(1,0001);
insert into inventory values(2,0002);
insert into inventory values(3,0003);
alter table inventory add constraint ipk primary key(itemid);

-- 查询
select * from category;
select * from product;
select * from item;
select * from supplier;
select * from inventory;
-- 列车车次查询
create table lieche(
startcity varchar2(20),
endcity varchar2(20),
checi varchar2(20)
);
insert into lieche values('上海','北京','K001');
insert into lieche values('上海','南昌','G001');
insert into lieche values('上海','昆明','S001');
insert into lieche values('上海','南京','K032');
insert into lieche values('上海','甘肃','SE01');
insert into lieche values('南昌','北京','G201');
insert into lieche values('昆山','北京','G401');
insert into lieche values('杭州','北京','SG01');
insert into lieche values('深圳','北京','F013');
insert into lieche values('赣州','北京','G021');

-- 对于****模糊查询单****的时候like是第一选择，而当要插入字段的时候因为存在null要么选择回滚要么选择拼接字符串
select * from lieche where startcity like '%%' and endcity like'%%' and checi like'%%';
select li2.* from (select rownum rn,li.* from lieche li where rownum<=3*pageNo) li2 where rn>(pageNo-1)*3;
select li2.* from (select rownum rn,li.* from lieche li where rownum<=3*2) li2 where rn>(2-1)*3;

-- 常用函数：
-- 字符串处理函数lower转为小写upper转为大写
select lower(checi) from lieche;
-- 数字函数round(value,p) 四舍五入
-- trunc(value,p)首位p位数字
-- 日期函数to_date(sysdate),to_date(日期型数据，格式)其中sysdate是系统的当前时间可直接插入数据类型为date
-- to_char(sysdate，格式)将日期型转换为字符串,这个可以显示完整数据,定义的时候数据类型为char(20)类型
-- to_timestamp(日期型数据，格式)时间戳

-- 连接查询
-- 内连接查询
-- 1、等值连接
select item.listprice 产品价格,product.name 产品名称,item.unitcost 标准价格 from
item,product,category where item.productid=product.productid and product.productid=category.catid;
--改成等值连接：
select item.listprice 产品价格,product.name 产品名称,item.unitcost 标准价格 from
item join product on item.productid=product.productid join category on product.productid=category.catid;
-- 2、非等值连接
-- 3、自连接
select i1.listprice 产品价格 from item i1 join item i2 on i1.listprice > i2.listprice and i2.itemid =1s
-- 4、左连接
select item.listprice 产品价格,product.name 产品名称,item.unitcost 标准价格 from
item left join product on item.productid=product.productid ;
-- 5、右连接
select item.listprice 产品价格,product.name 产品名称,item.unitcost 标准价格 from
item right join product on item.productid=product.productid ;

-- 分组查询
-- count
select count(*) from item;
-- max
select max(listprice) from item;
-- min
select min(listprice) from item;
-- avg
select avg(listprice) from item;
-- sum
select sum(listprice) from item;
-- group by
-- 带有group by的查询中select 列表中指定的列要么是group by 子句中指定的列，要么包含聚合函数
-- 前十条数据中分组后的商品数量的在3条以上的分组情况；
-- 分组之前要用where 分组之后要用having 而最后排序
select name,count(*) from product where rownum<=10 group by name having count(*)>=3 order by count(*) ;
-- select pro2.* from (select pro.name,count(*) from product pro where rownum<=10) pro2 group by name having count(*)>=3 order by count(*);

-- 课堂练习
-- 主表myproduct
create table myproduct (
productid number primary key,
productname varchar2(20) unique,
price number not null
);
insert into myproduct values(1,'HP1200打印机',2000);
insert into myproduct values(2,'LX360兼容机',4800);
insert into myproduct values(3,'IBM 350笔记本',11000);
insert into myproduct values(4,'BM 360笔记本',12000);
-- 字表 sales
create table sales(
productid number references myproduct(productid),
clientname varchar2(20),
productnumber number,
salesprice number
);
--1、 为表添加主键
alter table sales add sid number primary key;
--2、 添加数据
insert into sales values(2,'华亚咨询',10,4500,1);
insert into sales values(1,'华亚咨询',25,1800,2);
insert into sales values(3,'联想集团',10,11000,3);
insert into sales values(2,'联想集团',30,4500,4);
insert into sales values(1,'联想集团',20,1800,5);
insert into sales values(3,'北大方正',40,10000,6);
insert into sales values(3,'诺基亚',20,10500,7);
-- 3、查询单笔数量大于15的客户信息
select clientname 客户名称,productnumber 购买数量,salesprice 销售价格信息 from sales where productnumber>15;
-- 4、查询所有商品的全部销售金额
select productname,sum(salesprice * productnumber) from myproduct mpro,sales sa where mpro.productid=sa.productid
group by mpro.productname;
-- 只关注总金额就不需要关联
select sum(salesprice * productnumber) from sales sa group by sa.productid;
--5、查询客户姓名、对应的客户销售金额
select clientname,sum(salesprice*productnumber) from sales group by clientname ;
--6、查询产品价格大于"LX360兼容机"的所有产品信息
select * from myproduct mpro where mpro.price > (select price from myproduct where productname='LX360兼容机' );
--7、 查询购买过商品"IBM 350笔记本"的商品名称、客户名称、购买数量
select productname,clientname,productnumber from myproduct mpro,sales sa where
mpro.productid = (select productid from myproduct where productname='IBM 350笔记本') and mpro.productid=sa.productid ;
-- 8、把客户“华亚咨询”名称更改为新致软件
update sales set clientname='新致软件' where clientname='华亚咨询';