Oracle命令.docx
- 文档编号:3903819
- 上传时间:2022-11-26
- 格式:DOCX
- 页数:7
- 大小:17.44KB
Oracle命令.docx
《Oracle命令.docx》由会员分享,可在线阅读,更多相关《Oracle命令.docx(7页珍藏版)》请在冰豆网上搜索。
Oracle命令
用户创建命令:
createuseraaaidentifiedbyaaa;
赋予权限命令:
grantdbatoaaa;
用户连接命令:
connaaa/aaa
创建表命令:
createtableguoshui(namevarchar(10),addrvarchar(20),numnumber);
插入表的命令:
Insertintoguoshui(name,addr,num)values('张三','云南','123456');
查询表的命令:
select*fromguoshui;
完成命令:
commit;
http:
//127.0.0.1:
8080/
createtablestudent
(
StudentNOvarchar(20)primarykey,/*学号即登录名*/
Passwordvarchar(20),/*密码*/
Namevarchar(20),/*学生姓名*/
Ageint,/*学生年龄*/
Gendervarchar
(2),/*学生性别*/
Classvarchar(50),/*所在班级*/
Departmentvarchar(50),/*所在系*/
Permittedint/*借书权限1:
有借书权限0:
没有权限*/
);
createtablebook
(
BookNOvarchar(10)primarykey,/*图书编号*/
BookNamevarchar(50),/*书名*/
Authorvarchar(50),/*作者*/
Publishvarchar(50),/*出版社*/
BookIsbnvarchar(20),/*ISBN号*/
BuyTimevarchar(50),/*购进时间*/
BookPricenumber(5,2),/*图书价格*/
BookStatusintdefault0/*Borrowed2:
丢失1:
借出0:
在库考虑2*/
);
createtableborrow_list
(
BorrowNOint,/*借书表主键,子增列*/
StudentNOvarchar(20),/*学号外键*/
BookNOvarchar(10),/*书号外键*/
StartTimevarchar(20)notnull,/*开始时间*/
Endvarchar(20)notnull,/*结束时间*/
XuJieintdefault0,/*是否续借标志*/
Dealintdefault0,/*0:
未归还1:
已归还2:
丢失*/
primarykey(BorrowNO),/*主键*/
Foreignkey(StudentNO)referencesstudent(StudentNO)ondeletecascade,
Foreignkey(BookNO)referencesbook(BookNO)ondeletecascade
);
CREATESEQUENCEborrow_pkSTARTWITH1INCREMENTBY1MAXVALUE1E30NOCYCLENOCACHE;
CREATETRIGGERborrow_list_pk
BEFOREINSERTONborrow_list
FOREACHROW
BEGIN
selectborrow_pk.nextval
into:
new.BorrowNO
fromdual;
END;
/
createtableamercement/*欠款表*/
(
AmerceNOnumber,/*罚款主键*/
BorrowNOnumber,/*罚款记录对应的借书记录*/
StudentNOvarchar(20),/*罚款记录对应的学生学号*/
Detailvarchar(20)notnull,/*罚款项目超期/丢书*/
Mulctnumber(5,2)notnull,/*罚款金额*/
Paynumber
(1)default0,/*是否缴纳默认为没有缴纳*/
PayTimevarchar(20),/*缴纳时间*/
primarykey(AmerceNO),
Foreignkey(BorrowNO)referencesborrow_list(BorrowNO)ondeletecascade,
Foreignkey(StudentNO)referencesstudent(StudentNO)ondeletecascade
);
CREATESEQUENCEamercement_pkSTARTWITH1INCREMENTBY1MAXVALUE1E30NOCYCLENOCACHE;
CREATETRIGGERamercement_pk
BEFOREINSERTONamercement
FOREACHROW
BEGIN
selectamercement_pk.nextval
into:
new.AmerceNO
fromdual;
END;
/
createtableorder_list
(
OrderNOnumber,
StudentNOvarchar(20),/*预约图书的学生学号*/
BookNOvarchar(10),/*所预约图书书号*/
EndTimevarchar(20),/*预约图书截止日期*/
primarykey(OrderNO),
Foreignkey(StudentNO)referencesstudent(StudentNO)ondeletecascade,
Foreignkey(BookNO)referencesbook(BookNO)ondeletecascade
);
CREATESEQUENCEorder_list_pkSTARTWITH1INCREMENTBY1MAXVALUE1E30NOCYCLENOCACHE;
CREATETRIGGERorder_list_pk
BEFOREINSERTONorder_list
FOREACHROW
BEGIN
selectorder_list_pk.nextval
into:
new.OrderNO
fromdual;
END;
/
createtablemanage
(
ManageIDvarchar(20),/*管理员登录名*/
ManagePwdvarchar(20)notnull,/*管理员密码*/
ManageLevelnumber
(1)default0,/*管理员级别1:
超级管理员*/
primarykey(ManageID)
);
--------------------------------------------------------------------------
insertintomanagevalues('wyf','123456',1);
insertintomanagevalues('wyy','123456',0);
insertintomanagevalues('zrk','123456',0);
insertintomanagevalues('cgq','123456',0);
insertintomanagevalues('hxl','123456',0);
insertintomanagevalues('zrx','123456',0);
insertintomanagevalues('xzh','123456',0);
insertintomanagevalues('syb','123456',0);
insertintostudentvalues('200501','200501','张三',20,'男','07级计算机3班','计控学院',1);
insertintostudentvalues('200502','200502','李四',22,'男','05级测绘1班','交通学院',1);
insertintostudentvalues('200503','200503','胡亮',21,'男','06级机械设计1班','机械学院',1);
insertintostudentvalues('200504','200504','曹飞',21,'女','06级土木工程2班','建工学院',1);
insertintostudentvalues('200505','200505','王霞',20,'女','07级物流管理1班','经管学院',1);
insertintostudentvalues('200506','200506','黄佳',22,'女','05级计算机1班','计控学院',1);
insertintostudentvalues('200507','200507','林珊',21,'女','06级仪表1班','计控学院',1);
insertintostudentvalues('200508','200508','高原',21,'男','07级造价1班','建工学院',1);
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('368779','使用Ant进行Java开发','[美]ErikHatcherSteveLougharn','电子工业出版社','7121016834','2008-5-26',69.00);
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('454908','精通CSS+DIV网页样式与布局','曾顺编著','人民邮电出版社','9787115163042','2008-2-13',49.00);
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('487923','VisualC++面向对象编程','王育坚编著','清华大学出版社','9787302069140','2008-2-13',35.00);
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('358134','JavaSE6.0编程指南','吴亚峰纪超编著','人民邮电出版社','9787115167408','2007-12-14','98.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('123456','NetBeans权威指南','刘斌费冬冬丁旋编著','电子工业出版社','9787121063206','2007-11-14','75.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('133456','Jsp2.0技术手册','林上杰林康司编著','电子工业出版社','9787505398245','2007-12-14','59.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('233456','大话设计模式','程杰著','清华大学出版社','9787302162063','2008-5-14','45.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('333456','红楼梦','曹雪芹高鄂','延边人民出版社','9787806484852','2008-5-14','26.80');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('351160','UNIX初级教程','[美]AmirAfzal著','电子工业出版社','9787121058981','2008-5-14','39.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('543235','Spring2.0技术手册','林信良著','电子工业出版社','9787121039850','2008-5-14','49.80');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('463235','Struts2权威指南','李刚编著','电子工业出版社','9787121048531','2008-5-14','79.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('643457','XML实践教程','张秋香等编著','清华大学出版社','9787302154884','2008-5-14','39.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('354770','精通JavaWeb开发','王俊标编著','人民邮电出版社','9787115155351','2008-6-14','59.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('967564','数据库系统概论','萨师煊王珊','高等教育出版社','9787040074949','2008-8-14','25.10');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('876462','JAVA面向对象编程','孙卫琴编著','电子工业出版社','9787121025389','2008-7-14','65.80');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('465127','Java程序员面试宝典','欧立奇朱梅段韬编著','电子工业出版社','9787121045523','2008-7-14','46.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('620928','Java与模式(含盘)','阎宏编著','电子工业出版社','9787505380004','2008-7-14','88.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('217690','EffectiveJava中文版','(美)布洛克著潘爱民译','机械工业出版社','9787111113850','2008-9-14','39.00');
insertintobook(BookNO,BookName,Author,Publish,BookIsbn,BuyTime,BookPrice)values('532870','Tomcat与JavaWeb开发技术详解(含盘)','孙卫琴,李洪成编著','电子工业出版社','9787505393929','2008-9-14','45.00');
insertintoborrow_list(StudentNO,BookNO,StartTime,End)values('200501','368779','2008-9-26','2008-11-15');
insertintoborrow_list(StudentNO,BookNO,StartTime,End)values('200501','454908','2008-9-1','2008-10-30');
insertintoborrow_list(StudentNO,BookNO,StartTime,End)values('200501','487923','2008-9-1','2008-10-30');
insertintoborrow_list(StudentNO,BookNO,StartTime,End)values('200501','333456','2008-7-1','2008-9-1');
insertintoborrow_list(StudentNO,BookNO,StartTime,End)values('200501','351160','2008-10-1','2008-11-30');
updatebooksetBookStatus=1whereBookNO='368779';
updatebooksetBookStatus=1whereBookNO='454908';
updatebooksetBookStatus=1whereBookNO='487923';
updatebooksetBookStatus=1whereBookNO='358134';
insertintoorder_list(StudentNO,BookNO,EndTime)values('200502','358134','2008-10-16');
insertintoorder_list(StudentNO,BookNO)values('200502','333456');
updatebooksetBookStatus=1whereBookName='红楼梦';
insertintoorder_list(StudentNO,BookNO,EndTime)values('200502','358134','2008-10-16');
insertintoorder_list(StudentNO,BookNO)values('200502','333456');
commit;
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 命令