Oracle基本建表语句.docx
《Oracle基本建表语句.docx》由会员分享，可在线阅读，更多相关《Oracle基本建表语句.docx（12页珍藏版）》请在冰点文库上搜索。
Oracle基本建表语句
--创建用户
createuserhanidentifiedbyhandefaulttablespace
usersTemporaryTABLESPACETemp;
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 number（5,2）,
EXAM_DATE date,
AUTOID number（10） notnull,
STU_ID char（5）,
SUB_ID 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;
--修改列的属性
altertabletestmodi
createtabletest1（
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.addressfromclassesc,studentss,stuinfosiwherec.id=s.class_idands.id=si.stu_id;
insertintostudentsvalues（stu_seq.nextval,'李四',1,sysdate）;
insertintostuinfovalues（stu_seq.currval,'南京'）;
--函数
selectrownum,id,stu_namefromstudentstorderbyidasc;
--中间表实现多对多关联
--（1 1，1 n，n1，nn）
--1n的描述 1的表不作处理 n的表有1表的字段
--11的描述 主外键关联
--nn的描述中间表实现多对多关联
create tablecourse（
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_seqincrementby1startwith1MAXVALUE999999NOCYCLENOCACHE;
createsequencecouses_seqincrementby1startwith1MAXVALUE999999NOCYCLENOCACHE;
insertintocoursevalues（couses_seq.nextval,'计算机原理'）;
insertintocoursevalues（couses_seq.nextval,'编译原理'）;
insertintocoursevalues（couses_seq.nextval,'数据库原理'）;
insertintocoursevalues（couses_seq.nextval,'数据结构'）;
insertintocoursevalues（couses_seq.nextval,'计算机基础'）;
insertintocoursevalues（couses_seq.nextval,'C语言初步'）;
commit;
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
--班级——姓名——课程
selectcl.classname,s.stu_name,c.couse_namefromstu_cousesc,studentss,classescl,cousecwheresc.stu_id=s.idandsc.couse_id=c.couse_idands.id=26;
--sql语句的写法，现写出关联到的表，然后写出要查找的字段，第三写出关联条件 ，记住在写关联到的表时先写数据多的表，这样有助于提高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_idgroupbys.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,studentss,classesclwheres.id=sc.stu_idgroupbys.id,s.stu_namehavingcount（sc.stu_couse_id）>3;
班级学生选课数量
selectcl.classname,count（sc.stu_couse_id）fromstu_cousesc,studentss,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_cousesc,studentss,classesclwheresc.stu_id=s.idands.class_id=cl.idgroupbys.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
年 年业绩总和
2001 200
2002 500
2003 900
2004 1400
createtabletest1（
t_idnumber（4）
）;
createtableorg（
org_idnumber（9）notnullprimarykey,
org_namevarchar2（40）notnull,
parent_idnumber（9）
）;
createsequenceorg_seqincrementby1startwith1MAXVALUE999999NOCYCLENOCACHE;
dropsequenceorg_seq;
insertintoorgvalues（1,'华建集团',0）;
insertintoorgvalues（2,'华建集团一分公司',1）;
insertintoorgvalues（3,'华建集团二分公司',1）;
insertintoorgvalues（4,'华建集团财务部',1）;
insertintoorgvalues（5,'华建集团工程部',1）;
insertintoorgvalues（6,'华建集团一分公司财务处',2）;
insertintoorgvalues（7,'华建集团一分公司工程处',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
升级会员 