oracle数据库的关于建表的sql语句练习
- 创建用户表shop_user
create table shop_user(
User_name varchar2(20) primary key,
Password varchar2(20) not null,
Name varchar2(20) not null,
zip number(6) check(length(zip)=6),
address varchar2(50)
)
commit
select * from shop_user
insert into shop_user
values(
‘Luxw’,
‘123456’,
‘Luxiaowei’,
100000,
‘西二旗’
)
select * from shop_user
- 创建商品表 shop_product
create table shop_product(
product_id number(7) primary key,
product_name varchar2(20) not null,
price number not null ,
picture_path varchar2(30),
discription varchar2(50)
)
insert into shop_product
values (
60001,
'Ipad3',
3500,
'D:/picture/a.gif',
'aaaaa'
)
commit
select * from shop_product
- 创建订单表 shop_order
create table shop_order(
order_id number(7) primary key,
ordertime date not null,
totalprice number not null,
username varchar2(20)
references shop_user(user_name)
)
insert into shop_order
values (
10001,
to_date('05-06-1998','dd-MM-yyyy'),
5000,
'Luxw'
)
commit
select * from shop_order
- 创建订单项表 shop_orderitem
create table shop_orderitem(
orderitem_id number(7) primary key,
order_id number(7) references shop_order(order_id) not null,
product_id number(7) references shop_product(product_id) not null,
count number(3)
)
insert into shop_orderitem
values (
1,
10001,
60001,
5
)
commit
select * from shop_orderitem
二、DML练习
1、 给商品表 shop_product创建序列,从60001开始。
create sequence shop_seq start with 60001
commit
drop sequence shop_seq
select shop_seq.nextval from dual
2、 使用序列往商品表中,插入4条数据:
insert into shop_product
values (
shop_seq.nextval,
'Ipad2',
2200,
'D/picture/b.gif',
'Pad2'
)
insert into shop_product
values (
shop_seq.nextval,
'Ipad4',
5500,
'D/picture/c.gif',
'Pad4'
)
insert into shop_product
values (
shop_seq.nextval,
'dell',
6500,
'D:/picture/d.gif',
'Pad5'
)
commit
select * from shop_product
3、 修改第3数据,名称改为IpadMini,价格2800。
update shop_product set product_name=‘IpadMini’ where product_id = 60003
commit
4、 删除所有的pad商品。
create index shop_name on shop_product(product_name)
delete from shop_product where product_name like ‘%pad%’
delete from shop_product
commit
select * from shop_product
5、 根据给的测试数据,给订单表 shop_order创建序列,并插入如下数据:
create sequence shop_ord start with 10001
commit
insert into shop_order
values (
shop_ord.nextval,
to_date('1988-08-10','yyyy-MM-dd'),
8000,
'huxz'
)