Oracle基本建表语句.docx
《Oracle基本建表语句.docx》由会员分享,可在线阅读,更多相关《Oracle基本建表语句.docx(12页珍藏版)》请在冰豆网上搜索。
![Oracle基本建表语句.docx](https://file1.bdocx.com/fileroot1/2023-6/9/58d53c3e-8484-48f3-bb09-13bdda363257/58d53c3e-8484-48f3-bb09-13bdda3632571.gif)
Oracle基本建表语句
--创建用户createuserhanidentifiedbyhandefaulttablespaceusersTemporaryTABLESPACETemp;
grantconnect,resource,dbatohan;//授予用户han开发人员的权利
对表的操作
创建表格语法:
createtable表名(
字段名1字段类型(长度)是否为空,
字段名2字段类型是否为空
);
-增加主键
altertable表名addconstraint主键名primarykey(字段名1);
-增加外键:
altertable表名
addconstraint外键名foreignkey(字段名1)references关联表(字段名2);
在建立表格时就指定主键和外键
createtableT_STU(
STU_ID
char(5)
notnull,
STU_NAME
varchar2(8)
notnull,
constraintPK_T_STUprimarykey(STU_ID)
);
主键和外键一起建立:
createtableT_SCORE(
EXAM_SCORE
EXAM_DATE
AUTOID
STU_ID
SUB_ID
number(5,2),
date,number(10)notnull,
char(5),
char(3),
constraintPK_T_SCOREprimarykey(AUTOID),constraintFK_T_SCORE_REFEforeignkey(STU_ID)referencesT_STU(STU_ID)
--创建表createtableclasses(
idnumber(9)notnullprimarykey,classnamevarchar2(40)notnull)
--查询表select*fromclasses;
--删除表droptablestudents;
--修改表的名称renamealist_table_copytoalist_table;
--显示表结构describetest--不对没查到
对字段的操作
--增加列altertabletestaddaddressvarchar2(40);
--删除列altertabletestdropcolumnaddress;
--修改列的名称
altertabletestmodifyaddressaddressesvarchar(40;
--修改列的属性altertabletestmodicreatetabletest1(
idnumber(9)primarykeynotnull,namevarchar2(34)
)
renametest2totest;
--创建自增的序列
createsequenceclass_seqincrementby1startwith1MAXVALUE999999NOCYCLENOCACHE;
selectclass_seq.currvalfromdual
--插入数据
insertintoclassesvalues(class_seq.nextval,'软件一班')
commit;
--更新数据
updatestu_accountsetusername='aaa'wherecount_id=2;commit;
--创建唯一索引
createuniqueindexusernameonstu_account(username);--唯一索引
不能插入相同的数据
--行锁在新打开的对话中不能对此行进行操作
select*fromstu_accounttwheret.count_id=2forupdate;--行锁
--altertablestuinfomodifysty_idtostu_id;
altertablestudentsdropconstraintclass_fk;
altertablestudentsaddconstraintclass_fkforeignkey(class_id)referencesclasses(id);--外键约束
altertablestuinfoaddconstraintstu_fkforeignkey(stu_id)referencesstudents(id)ONDELETECASCADE;--外键约束,级联删除
altertablestuinfodropconstantstu_fk;
insertintostudentsvalues(stu_seq.nextval,'张三',1,sysdate);
威海');
insertintostuinfovalues(stu_seq.currval,'select*fromstuinfo;
createtablezhuce(
zc_idnumber(9)notnullprimarykey,stu_idnumber(9)notnull,zhucetimedatedefaultsysdate
)
createtablefeiyong(
fy_idnumber(9)notnullprimarykey,stu_idnumber(9)notnull,mx_idnumber(9)notnull,yijiaonumber(7,2)notnulldefault0,qianfeinumber(7,2)notnull
)
createtalbefymingxi(
mx_idnumber(9)notnullprimarykey,
feiyongnumber(7,2)notnull,//共7位数字,小数后有两位
class_idnumber(9)notnull}
createtablecard(
card_idnumber(9)primarykey,
stu_idnumber(9)notnull,
moneynumber(7,2)notnulldefault0,
statusnumber
(1)notnulldefault0--0表可用,1表挂
失
)
--链表查询
selectc.classname||'_'||s.stu_nameas班级—姓名,si.addressfrom
classesc,studentss,stuinfosiwherec.id=s.class_idands.id=si.stu_id;
insertintostudentsvalues(stu_seq.nextval,'李四:
1,sysdate);
insertintostuinfovalues(stu_seq.currval,'南京');
--函数
selectrownum,id,stu_namefromstudentstorderbyidasc;
--中间表实现多对多关联
--(11,1
n,n1,nn)
--1n
的描述
1的表不作处理
--11
的描述
主外键关联
--nn
的描述
中间表实现多对多关联
n的表有1表的字段
createtablecourse(course_idnumber(9)notnull,couser_namevarchar2(40)notnull
)
altertablecoursetocouse;
createtablestu_couse(stu_couse_idnumber(9)primarykey,stu_idnumber(9)notnull,couse_idnumber(9)notnull
)
createuniqueindexstu_couse_unqonstu_couse(stu_id,couse_id);--唯
一学生
createsequencestu_couse_seqincrementby1startwith1MAXVALU9E99999NOCYCLENOCACHE;
createsequencecouses_seqincrementby1startwith1MAXVALUE999999
计算机原理');编译原理');
数据库原理');数据结构');
计算机基础');语言初步');
NOCYCLENOCACHE;
insertintocoursevalues(couses_seq.nextval,'insertintocoursevalues(couses_seq.nextval,'insertintocoursevalues(couses_seq.nextval,'insertintocoursevalues(couses_seq.nextval,'insertintocoursevalues(couses_seq.nextval,'insertintocoursevalues(couses_seq.nextval,'Ccommit;
insertintostu_cousevalues(stu_couse_seq.nextval,1,1);
insertintostu_cousevalues(stu_couse_seq.nextval,1,3);
insertintostu_cousevalues(stu_couse_seq.nextval,1,5);
insertintostu_cousevalues(stu_couse_seq.nextval,1,5);
insertintostu_cousevalues(stu_couse_seq.nextval,2,1);
commit;
select*fromstu_couse;
select*fromcourse;
--selects.stu_name,sc.couse_id,c.couser_namefromstudentss,coursec,stu_cousescwherestu_id=1
--selectcouse_idfromstu_cousewherestu_id=1
selectcl.classname,s.stu_name,c.couser_namefromstu_cousesc,studentss,coursec,classesclwheres.id=sc.stu_idandsc.couse_id=c.course_idands.class_id=cl.idands.id=1;
--班级——姓名
selectc.classname,s.stu_namefromstudentss,classescwheres.class_id=c.idands.id=2;
select*fromstudentsswheres.id=2
--班级——姓名——课程
and
写出关
sql的
group
selectcl.classname,s.stu_name,c.couse_namefromstu_cousesc,studentss,classescl,cousecwheresc.stu_id=s.idandsc.couse_id=c.couse_is.id=26;
--sql语句的写法,现写出关联到的表,然后写出要查找的字段,第三联条件,记住在写关联到的表时先写数据多的表,这样有助于提高效率
selectc.couser_name,s.stu_namefromstu_cousesc,studentss,coursecwherec.course_id=1andc.course_id=sc.couse_idandsc.stu_id=s.id;
selects.stu_namefromstudentss,stu_cousescwheres.id=sc.stu_idbys.id,s.stu_name;
selectc.classname,count(sc.couse_id)fromstu_cousesc,studentss,classescwheres.class_id=c.idands.id=sc.stu_idgroupbyc.classname;
selects.stu_name,count(sc.couse_id)fromstu_cousesc,students
s,classesclwheres.id=sc.stu_idgroupbys.id,s.stu_namehavingcount(sc.stu_couse_id)>3;
班级学生选课数量
selectcl.classname,count(sc.stu_couse_id)fromstu_cousesc,students
s,classesclwheres.id=sc.stu_idands.class_id=cl.idgroupbycl.classname;
--班级学生选课数量
selectcl.classname,s.stu_name,count(sc.stu_couse_id)fromstu_cousesc,studentss,classesclwheres.id=sc.stu_idands.class_id=cl.idgroupbys.stu_name;
selectcl.classname,s.stu_name,count(sc.stu_couse_id)fromstu_couse
sc,studentss,classesclwheresc.stu_id=s.idands.class_id=cl.idgroup
bys.id;
selectcl.classname,s.stu_name,count(sc.stu_couse_id)fromstu_cousesc,studentss,classesclwheresc.stu_id=s.idands.class_id=cl.idgroupbys.stu_name;
--班级学生所选课程id所选课程名称
--创建试图目的把表联合起来然后看成一个表,在与其他的联合进行查询createviewxsxkasselectcl.classname,s.stu_name,c.couse_id,c.couse_namefromstu_cousesc,studentss,classescl,cousecwheresc.stu_id=s.idandsc.couse_id=c.couse_idands.class_id=cl.id;
select*fromxsxk
createviewclassstuasselects.id,c.classname,s.stu_namefromstudentss,classescwherec.id=s.class_id;
dropviewclassstu;--删除视图
select*fromclassstu;
createviewstu_couse_viewasselects.id,c.couse_namefromstu_cousesc,studentss,cousecwheres.id=sc.stu_idandsc.couse_id=c.couse_id;select*fromstu_couse_view;
createviewcscasselectcs.classname,cs.stu_name,scv.couse_namefromclassstucs,stu_couse_viewscvwherecs.id=scv.id;
select*fromcsc;
select*fromclassescrossjoinstudents;--全连接,相当于select*fromclasses,students;
select*fromclassesclleftjoinstudentssoncl.id=s.class_id;--左连接不管左表有没有都显示出来
select*fromclassesclrightjoinstudentssoncl.id=s.class_id;--右连接
软件四班');
select*fromclassesclfulljoinstudentssoncl.id=s.class_id;--全连接
insertintoclassesvalues(class_seq.nextval,'createtablesales(
nianvarchar2(4),
yejinumber(5)
);
insertintosalesvalues('2001',200);
insertintosalesvalues('2002',300);
insertintosalesvalues('2003',400);
insertintosalesvalues('2004',500);
commit;
select*fromsales;
droptablesale;
selects1.nian,sum(s2.yeji)fromsaless1,saless2wheres1.nian>=s2.niangroupbys1.nianorderbys1.niandesc;
selects1.nian,sum(s2.yeji)fromsaless1,saless2wheres1.nian>=s2.niangroupbys1.nian;
s
年年业绩总和2001200
2002500
2003900
20041400
createtabletest1(
t_idnumber(4)
);
createtableorg(
org_idnumber(9)notnullprimarykey,org_namevarchar2(40)notnull,parent_idnumber(9)
);
dropsequenceorg_seq;insertintoorgvalues(1,'insertintoorgvalues(2,'insertintoorgvalues(3,'insertintoorgvalues(4,'insertintoorgvalues(5,'insertintoorgvalues(6,'insertintoorgvalues(7,'
createsequenceorg_seqincrementby1startwith1MAXVALUE999999NOCYCLENOCACHE;
华建集团',0);华建集团一分公司',1);华建集团二分公司',1);华建集团财务部',1);华建集团工程部',1);华建集团一分公司财务处',2)华建集团一分公司工程处',2)
select*fromorg;
--不正确不能实现循环
selectb.org_id,b.org_name,b.parent_idfromorga,orgbwherea.org_id=7anda.parent_id=b.org_id;
select*fromorgconnectbypriorparent_id=org_idstartwithorg_id=7orderbyorg_id;
select*fromorgconnectbypriororg_id=parent_idstartwithorg_id=1orderbyorg_id;
createtablechengji(
cj_idnumber(9)notnullprimarykey,stu_cou_idnumber(9)notnull,fennumber(4,1)
);
insertintochengjivalues(1,1,62);
insertintochengjivalues(2,2,90);
insertintochengjivalues(3,3,85);
insertintochengjivalues(4,4,45);
insertintochengjivalues(5,5,68);
insertintochengjivalues(6,6,87);commit;
select*fromchengji;
select*fromstu_couse;
--在oracle中好像不适用altertablechengjichangestu_cou_idstu_couse_id;altertableshop_jbchangeprice1pricedouble;
学生姓名平均分
selects.stu_name,avg(cj.fen)fromstu_cousesc,chengjicj,studentsswheres.id=sc.stu_idandsc.stu_couse_id=cj.stu_couse_idgroupbys.id,s.stu_name;
selects.stu_namefromstudentss,stu_cousesc,chengjicjwheres.id=sc.stu_idandsc.stu_couse_id=cj.stu_couse_idgroupbys.id,s.stu_name;
selects.stu_name,cj.fenfromstudentss,stu_cousesc,chengjicjwheres.id=sc.stu_idandsc.stu_couse_id=cj