Oracle SQL Developer Lesson 2 DDL, DML,TCL,DCL-数据库

2/19/20131DAY2Session1DataDefinitionLanguage(DDL).DataModificationLanguage(DML).Session2TransactionControlLanguage(TCL).DataControlLanguage(DCL).Constraints.DATADEFINITIONLANGUAGE2/19/20132DataDefinitionLanguageNamingRulesDataDefinitionLanguage2/19/20133DataDefinitionLanguageDefaultOptionDataDefinitionLanguageCreatingTableConformationOfCreatedtable2/19/20134DataDefinitionLanguageKindsOfTablesInADataBaseDataDefinitionLanguageViewingInfofromDictionaryTables2/19/20135DataTypes:CreatingTableUsingSubquerySyntaxSQLCREATETABLEdept1ASSELECT*FROMdept;2/19/20136CreatingTableUsingSubquery(SelectiveColumns)SQLCREATETABLEemp1ASSELECTename,job,salFROMemp;UsingAlterTableStatement2/19/20137UsingAlterTableStatementAddingaNewColumnSyntax:SQLALTERTABLEEMP1ADDdnamevarchar2(15)default‘Accounts’;UsingAlterTableStatementModifyingaColumnSyntax:SQLALTERTABLEEMP1MODIFYdnamevarchar2(20)default‘Admin’;2/19/20138UsingAlterTableStatementModifyingaColumnSyntax:SQLALTERTABLEEMP1DROPCOLUMNdname;DROPINGATableSQLDROPTABLEemp1;2/19/20139ChangingNameOfAnObject:SQLTRUNCATINGATable:2/19/201310AddingCOMMENTSToATable:SQLCOMMENTONTABLEempIS‘Employeesinformation’;SQLCOMMENTONCOLUMNemp.mgrIS‘EmployeesManagerID’DataDictionaryViews:2/19/201311DataDefinitionLanguageViewingInfofromDictionaryTablesSQLSELECT*FROMUSER_COL_COMMENTSWHERETABLE_NAME=‘EMP1’;DATAMANIPULATIONLANGUAGE2/19/201312DMLDMLInsertingNewRecordsIntoATable2/19/201313DMLInsertingNewRecordsIntoATableSQLINSERTINTOdeptVALUES(50,’EXECUTIVE’,STERLING’);DMLInsertingNULLvaluesSQLINSERTINTOEMP(empno,ename,job,hiredate,comm)VALUES(1234,SYAM’,’MANAGER’,sysdate,NULL);2/19/201314DMLUsingScript:SQLINSERTINTOEMP(empno,ename,job)VALUES(&empno,’&ENAME’,’&JOb’);DMLCopyingrowsfromanothertable:SQLINSERTINTOEMP1(empno,ename,job,sal)SELECTempno,ename,job,salFROMempWHEREjob=‘MANAGER’;2/19/201315DMLChangingdatainaTable;DMLChangingdatainaTable;SQLUPDATEempSETename=‘STEVE’WHEREempno=7654;2/19/201316DMLChangingdatainaTable;SQLUPDATEempSETjob=(SELECTjobFROMempWHEREename=‘STEVE’),sal=(SELECTsalFROMempWHEREename=‘STEVE’),WHEREename=‘SCOTT’;DMLDeletingRecordsFromATable;2/19/201317DMLDeletingRecordsFromATable;SQLDELETEFROMEMPWHEREempno=7654;SQLDELETEDEPT;DATABASETRANSACTIONS2/19/201318TCLTransactionsTCLAdvantagesOfCOMMIT&ROLLBACK2/19/201319TCLControllingTransactionsTCLControllingTransactions2/19/201320TCLImplicitTransactionProcessing:TCLStateOfDataBeforeCommitOrRollback;2/19/201321TCLStateOfDataAfterCommit;TCLStateOfDataAfterRollback;2/19/201322TCLStatementLevelRollback;TCLReadConsistency:2/19/201323DATACONTROLLANGUAGEOBJECTPRIVILEGES:2/19/201324OBJECTPRIVILEGES:GrantingSelectPrivilegeonEmptoSCOTTuserSQLGRANTSELECTONemptoSCOTT;GrantingUpdatePrivilegeonEmptouserSCOTT&STEVESQLGRANTUPDATEONemptoSCOTT,STEVE;OBJECTPRIVILEGES:GrantingPrivilegestoUpdatespecificcolumnstoUsersSQLGRANTUPDATE(sal,job)ONempTOSCOTT,STEVE;GrantingAllPrivilegesonEmptouserSCOTTSQLGRANTALLONemptoSCOTT;2/19/201325UsingWithGrantandPublicKeywordsGrantingaUserAuthorityToPassOnPrivileges:SQLGRANTSELECT,INSERTONdeptTOSCOTTWITHGRANTOPTION;GrantingAllUsersintheDatabasetoaccessDatafromDeptTableSQLGRANTSELECTONdepttoPUBLIC;ConfirmingPrivilegesGranted2/19/201326RevokingPrivilegesGrantedOBJECTPRIVILEGES:RevokingSelectPrivilegeonEmpFromSCOTTuserSQLREVOKESELECTONempFORMSCOTT;RevokingUpdatePrivilegeonEmpFromSCOTT&STEVESQLREVOKEUPDATEONempFROMSCOTT,STEVE;2/19/201327CONSTRAINTSCONSTRAINTS2/19/201328CONSTRAINTSSyntaxCONSTRAINTSSQLCREATEtablestudent(snonumber(3),snamevarchar2(20)constraintstd_sname_uunique,smarksnumber(3))2/19/201329CONSTRAINTSAtColumn&TableLevel:CONSTRAINTSAtColumn&TableLevel:SQLCREATEtablestudent(snonumber(3)notnull,snamevarchar2(20)constraintstd_sname_uunique,smarksnumber(3),sagenumber(3)check(sage120),constraintstd_smarks_ccheck(smarks=600));2/19/201330UNIQUECONSTRAINTPRIMARYKEYCONSTRAINT2/19/201331FOREIGNKEYCONSTRAINTFOREIGNKEYCONSTRAINT2/19/201332ADDACONSTRAINTDROPINGACONSTRAINT2/19/201333DISABLINGACONSTRAINTENABLINGACONSTRAINT2/19/201334VIEWINGCONSTRAINTSVIEWINGCOLUMNSASSOCIATEDWITHCONSTRAINTS2/19/201335Session1Joins.SQL:1999CompliantJoinsSession2SubQueriesProducingReadableOutputWithSQL*Plus.JOINS2/19/201336TYPESOFJOINSTYPESOFJOINSSQLCOMPLIANT1999JOINS:2/19/201337JOINSCARTESIANPRODUCTJOINSCARTESIANPRODUCTSQLSELECTename,dnameFROMemp,dept;2/19/201338JOINSEQUIJOINSQLSELECTename,dnameFROMemp,deptWHEREemp.deptno=dept.deptno;JOINSEQUIJOINSQLSELECTename,dname,emp.deptnoFROMemp,deptWHEREemp.deptno=dept.deptno;2/19/201339JOINSEQUIJOINSQLSELECTemp.ename,dept.dname,emp.deptnoFROMemp,deptWHEREemp.deptno=dept.deptno;JOINSEQUIJOINusingtablealiases:SQLSELECTe.ename,d.dname,e.deptnoFROMempe,deptdWHEREe.deptno=d.deptno;2/19/201340JOINSNON-EQUIJOINSQLSELECTe.ename,e.sal,e.job,s.gradeFROMempe,salgradesWHEREsalbetweenlosalandhisal;JOINSJOININGMORETHANTWOTABLES:SQLSELECTe.ename,e.sal,d.dname,s.gradeFROMempe,s