数据库教学课件课第4天

1、4 ORACLE1 4 ORACLE1 1NetManager 2三、oraclealteruser identifiedby alteruserstudsysidentifiedbyORACLE312system 3五、关于本机登录后修改ORACLE1Oracle2五、关于本机登录后修改ORACLE1Oracle2cmd输入as ORACLENetManagerORACLENetManager # listener.ora Network Configuration File: # Generated by Oracle configuration tools.SID_LIST_LISTEN

2、ER= (SID_LIST =(SID_DESC(SID_NAME=(ORACLE_HOME=C:oracleproduct10.2.0db_1) (PROGRAM = extproc)LISTENER=(DESCRIPTION_LIST= (DESCRIPTION =(ADDRESS=(PROTOCOL=IPC)(KEY=)(DESCRIPTION(ADDRESS=(PROTOCOL=TCP)(HOST=pc000)(PORT=)31alter31alteruser 用户名称 accountunlock; 2alteruserscottaccount1alteruser 用户名称 accou

3、ntlock; 2alteruserscottaccount2限revoke 1,2，角色nfrom2限revoke 1,2，角色nfrom 3SQLconnectConnected to Oracle Database 10g EntriseEditionRelease10.2.0.1.0 Connected as testSQLconnectsys/orclasConnected to Oracle Database 10g EntriseEditionRelease10.2.0.1.0 Connected as SYSSQLrevokedba,connect,resourcefromte

4、st; Revoke succeededSQLconnecttest/orcl; Not logged on2drop user 用户ID2drop user 用户IDdrop user test cascade;4十一、 关于 ORACLLE 1cmdsqlplus/as2createuserstudsysidentifiedbyorcl1234; 3grant dba,connect,resource to studsys;4revokedba,connect,resourcefromstudsys; 5alter user studsys identified by orcl;6alte

5、ruserstudsysaccount7alteruserstudsysaccountunlock; 8drop user studsys cascade;9plsqldevd connect sys/orclpc520 as sysdba; connectsys/orclpc000assysoper;connectsys/orclasconnectstudsys/orclasconnectstudsys/orclpc000asconnect 10NetManager4SQLconnectsys/orclasConnected to Oracle Database 10g EntriseEdi

6、tionRelease10.2.0.1.0 Connected as SYS SQLdropusertestcascade;User dropped 十二、 关于独立的约束设计约束名称 约束条件and(or) 约束条件2十二、 关于独立的约束设计约束名称 约束条件and(or) 约束条件25createtable 用户 primary key, namevarchar2(20)not U_Aage check(age=20 and age =20 and age =20 and age =20andage=0andAGEADDRPHONE RUXUE_TIMEDATEnotXUE_QICHAR

7、(1)notnullcheck(XUE_QIEGERnot-下面一数据有o STUDENT_2 values(20000,QH10001,o STUDENT_2 values(20000,QH10001,createtableSTUDENT identity(10000,1)primaryCLASS_ID char(7) not null foreign key referenNAME nvarchar(10) not null,SEXnchar(1)check(SEXin(男,女AGE tinycheck(AGE=0andAGE=0andAGEADDRPHONE RUXUE_TIMEDATE

8、notXUE_QICHAR(1)notnullcheck(XUE_QIEGERnotforeignkey(CLASS_ID) CLASS_REG(CLASS_ID),foreignkey(ZIXUNSHI_ID) EMPLOYEE(EMPLOYEE_ID)o STUDENT values o STUDENT values oSTUDENT12Oracle 42字段12Oracle 42字段数据类型字段B数据类型B, primarykey(字段A,字段B),foreignkey(字段3createtableSTUDY EGERSCORENUMBER(5,2)check(SCORE=0andSCO

9、REforeign STUDENT(STUD_ID),foreignkey(COURSE_ID) COURSE(COURSE_ID), PRIMARYselect*fromstudent select * from studyo study values(10020,1,102.456)o study values(10000,3,102.456) ostudyvalues(10000,3,106.456) -有错，主键 ostudyvalues(10000,2,106.456) -有错，主键 ostudyselect sq_student_c.currval from dualselects

10、q_student_c.nextvalfromdual -序列号下一个-关于虚拟数据select345 学号 from dual;select 348 学号,张红红主楼 办公地址,22 fromdual; select sysdate from dual;-Oracle系统日期中没有dual 虚拟数据表概念，直接用，也就是不需要from select 345select主楼 办公地址4create4createtableCLASS_REG_D CLASS_IDCHAR(7)primarySTART_TIMEDATEcheck(END_TIMESTART_TIME), -设计END_TIME c

11、reatetableCLASS_REG_D CLASS_IDCHAR(7)primarySTART_TIME END_TIMEDATEcheck(START_TIMEto_date(2010-10-1,yyyy-mm- -有andEND_TIMESTART_TIME) createtableCLASS_REG_D CLASS_IDCHAR(7)primarySTART_TIME END_TIMEDATEcheck(END_TIMEto_date(2010-10-1,yyyy-mm- andEND_TIME=to_date(2011-5-1,yyyy-mm-dd) -OK，独立的约束行，可以为多个字段的联合