Oracle_基本建表语句

--创建用户createuserhanidentifiedbyhandefaulttablespaceusersTemporaryTABLESPACETemp;grantconnect,resource,dbatohan;//授予用户han开发人员的权利--------------------对表的操作--------------------------创建表格语法:createtable表名(字段名1字段类型(长度)是否为空,字段名2字段类型是否为空);-增加主键altertable表名addconstraint主键名primarykey(字段名1);-增加外键:altertable表名addconstraint外键名foreignkey(字段名1)references关联表(字段名2);在建立表格时就指定主键和外键createtableT_STU(STU_IDchar(5)notnull,STU_NAMEvarchar2(8)notnull,constraintPK_T_STUprimarykey(STU_ID));主键和外键一起建立：createtableT_SCORE(EXAM_SCOREnumber(5,2),EXAM_DATEdate,AUTOIDnumber(10)notnull,STU_IDchar(5),SUB_IDchar(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.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;--中间表实现多对多关联--（11，1n，n1，nn）--1n的描述1的表不作处理n的表有1表的字段--11的描述主外键关联--nn的描述中间表实现多对多关联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_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=1selectcl.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_n