数据库基础知识培训胶

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,*,72,数据库基础知识培训胶片,集成产品部,数据库原理,目标,通过这部分课程的学习,您将了解以下内容 :,关系数据库基本概念,常用数据库对象,常用数据类型,IN Database,Informix,Server,SMP,SCP,SMAP,SMAP,SMAP,The database plays the role of the base, saving and processing the data that should be easily accessed by the user.,What is a database?,Usually, a database runs in the server part to serve access requests from clients.,Definition of a Relational Database,A relational database is a collection of relations or,two-dimensional tables.,Oracle,server,Table Name:,EMPLOYEES,Table Name:,DEPARTMENTS,Relational Database Management System,User tables,Data,dictionary,Oracle,server,RDBMS: Relational Database Management System,A relational database consists of tables that are made up of rows and columns.,Tables,A database in a RDBMS is a collection of information that is grouped into one or more tables.,A table is an array of data,items,organized into rows and columns.,Stock,PPS Service Data Table,IN Service Database,FPH Service Data Table,Subscriber Data Table,Tables are relational,Sub ID,Balance,IDD Flag,100,1100.00,0,101,2200.00,1,MSISDN Num,Balance,IDD Flag,Sub ID,100,1100.00,0,100,101,2200.00,1,101,FPH Num,Balance,IDD Flag,Sub ID,8008101234,1100.00,0,100,8008104321,2200.00,1,101,Rows,Rows,Each row of a table stands for one,instance,of the subject of the table, which is one particular example of that entity.,Stock,Columns,Each column of a table stands for one,attribute, which is one characteristic, feature, or fact that is true of the subject of the table.,Columns,Stock,The Relational Model,A relational database consists of tables that are made up of rows and columns.,Name,ID,Address,Age,Tom,1,New York, U.S.A.,15,Mike,2,London, U.K.,17,Leon,3,Beijing, P.R.China,21,Jack,4,Tokyo, Japan,18,Lisa,5,Paris, France,15,column,= attribute,row,= instance,table = entity,Operations on Tables,Database Objects,ObjectDescription,TableBasic unit of storage; composed of rows and columns,View Logically represents subsets of data from one or more tables,Sequence Numeric value generator,IndexImproves the performance of some queries,Synonym Gives alternative names to objects,What is a View?,EMPLOYEES,Table:,Why Use Views?,To restrict data access,To make complex queries easy,To provide data independence,To present different views of the same data,What are Constraints?,Constraints enforce rules at the table level.,Constraints prevent the deletion of a table if there are dependencies.,The following constraint types are valid:,NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,What is an Index?,An index:,Is a schema object,Is used by the Oracle server to speed up the retrieval of rows by using a pointer,Can reduce disk I/O by using a rapid path access method to locate data quickly,Is independent of the table it indexes,Is used and maintained automatically by the Oracle server,How Are Indexes Created?,Automatically: A unique index is created automatically when you define a,PRIMARY KEY,or,UNIQUE,constraint in a table definition.,Manually: Users can create nonunique indexes on columns to speed up access to the rows.,Data Types,Data TypeDescription,VARCHAR2(,size,),Variable-length character data,CHAR(,size,),Fixed-length character data,NUMBER(,p,s),Variable-length numeric data,DATE,Date and time values,LONG,Variable-length character data up to 2 gigabytes,CLOB,Character data up to 4,gigabytes,RAW and LONG RAW,Raw binary data,BLOB,Binary data up to 4 gigabytes,BFILE,Binary data stored in an external file; up to 4 gigabytes,ROWID,A 64 base number system representing the unique address of a row in its table.,总结,通过这部分课程的学习,您需要掌握以下内容 :,关系数据库基本概念,常用数据库对象,表,索引,约束,常用数据类型,SQL语言基础,目标,通过这部分课程的学习,您将了解以下内容 :,SQL语句的种类,执行基本的Select语句,在表中插入数据行,在表中修改数据行,从表中删除数据行,控制事务处理,SQL Statements,SELECT,INSERT,UPDATE,DELETE,MERGE,CREATE,ALTER,DROP,RENAME,TRUNCATE,COMMIT,ROLLBACK,SAVEPOINT,GRANT,REVOKE,Data retrieval,Data manipulation language (DML),Data definition language (DDL),Transaction control,Data control language (DCL),基础,SELECT,语句语法,SELECT*|DISTINCT,column,|,expression,alias,.,FROM,table;,使用算术操作符,SELECT last_name, salary, salary + 300,FROM employees;,使用连接符和字符串,SELECT last_name| is a |job_id,AS Employee Details,FROM employees;,排除重复的行,SELECT DISTINCT department_id,FROM employees;,使用,WHERE,条件,SELECT employee_id, last_name, job_id, department_id,FROM employees,WHERE department_id = 90 ;,比较条件,操作符,=,=,=10000,AND job_id LIKE %MAN%;,SELECT last_name, job_id, department_id, hire_date,FROM employees,ORDER BY hire_date ;,ORDER BY,条件,带,ORDER BY,条件的排序,ASC: 升序，缺省,DESC: 降序,ORDER BY,条件在Select语句最后.,数据操作语句,一条 DML 语句在下列情况下被执行:,向表中增加新的数据行,修改表中存在的数据,从表中删除数据,一个事务由DML语句集合形成的逻辑工作单元构成.,INSERT,语句语法,.,INSERT INTO,table,(,column, column.,),VALUES,(value, value.,);,向表中插入新的数据行,插入的新行应包括所有的列值.,插入的值应按照表中列缺省的顺序排列.,如插入所有的列,INSERT,条件中可随意选择是否列出所有列.,INSERT INTO departments(department_id, department_name,manager_id, location_id),VALUES (70, Public Relations, 100, 1700);,1 row created.,INSERT INTO employees,VALUES (114,Den, Raphealy,DRAPHEAL, 515.127.4561,TO_DATE(FEB 3, 1999, MON DD, YYYY),AC_ACCOUNT, 11000, NULL, 100, 30);,1 row created.,插入特殊的日期型值,Verify your addition.,带子查询的INSERT 语句.,字段的数据类型必须匹配.,INSERT INTO sales_reps(id, name, salary, commission_pct),SELECT employee_id, last_name, salary, commission_pct,FROM employees,WHERE job_id LIKE %REP%;,4 rows created.,从另外一个表中复制行,UPDATE,语句语法,可一次性修改多行,UPDATE,table,SET,column,=,value,column,=,value, .,WHERE,condition,;,UPDATE employees,SET department_id = 70,WHERE employee_id = 113;,1 row updated.,在WHERE条件中指定需要修改的行.,修改表中所有的行.,修改表中数据,UPDATE copy_emp,SET department_id = 110;,22 rows updated.,UPDATE copy_emp,SET department_id = (SELECT department_id,FROM employees,WHERE employee_id = 100),WHERE job_id = (SELECT job_id,FROM employees,WHERE employee_id = 200);,1 row updated.,基于另外表SELECT得到的值修改数据,使用子查询实现：,DELETE 语句语法,DELETE FROM,table,WHERE,condition,;,通过,WHERE,条件指定删除的行.,省略,WHERE,条件，所有的行都将被删除.,从表中删除多行,DELETE FROM departments,WHERE department_name = Finance;,1 row deleted.,DELETE FROM copy_emp;,22 rows deleted.,DELETE FROM employees,WHERE department_id =,(SELECT department_id,FROM departments,WHERE department_name LIKE %Public%);,1 row deleted.,基于另外表SELECT得到的值删除数据,通过子查询语句从另外表中得到值作为删除条件.,数据库事务,一个数据库完整事务包含以下情况:,构成对数据的一次一致性改变的DML语句（一条或多条）,一条DDL 语句,数据库事务,当第一条 DML SQL 语句执行时开始,当下列任意事件发生时结束:,提交或回滚语句执行,一条DDL 执行(自动提交),系统崩溃,控制事务,SAVEPOINT B,SAVEPOINT A,DELETE,INSERT,UPDATE,INSERT,COMMIT,Time,Transaction,ROLLBACK,to SAVEPOINT B,ROLLBACK,to SAVEPOINT A,ROLLBACK,UPDATE.,SAVEPOINT update_done;,Savepoint created.,INSERT.,ROLLBACK TO update_done;,Rollback complete.,回滚改变到一个标记,在当前的事务中可通过,SAVEPOINT,语句创建标记.,ROLLBACK TO,SAVEPOINT,语句可回滚到标记.,自动的 commit 发生在 :,DDL 语句执行,隐性的事务处理,COMMIT;,Commit complete.,改变数据.,Commit 改变.,DELETE FROM employees,WHERE employee_id = 99999;,1 row deleted.,INSERT INTO departments,VALUES (290, Corporate Tax, NULL, 1700);,1 row inserted.,提交数据,回滚后数据状态,将丢弃所有未COMMIT的改变：,DELETE FROM copy_emp;,22 rows deleted.,ROLLBACK;,Rollback complete.,总结,通过这部分课程的学习,您将了解以下内容 :,SQL语句类型,写一个SELECT语句:,从一个表中返回所有的行和列,从一个表中返回指定的列,使用列别名描述列头,描述 DML 语句,在表中插入数据行,在表中修改数据行,从表中删除数据行,控制事务处理,练习,请见Oracle数据库维护上机任务书,课堂练习-SQL语言基础,数据库函数,目标,通过这部分课程的学习,您将了解以下内容 :,描述SQL中用到的不同类型的函数,在SELECT语句中使用字符，数值和日期等函数,转换函数的使用,SQL Functions,Function,Input,arg 1,arg 2,arg,n,Function performs action,Output,Result,value,字符型函数,字符型函数,LOWER,UPPER,INITCAP,CONCAT,SUBSTR,LENGTH,INSTR,LPAD | RPAD,TRIM,REPLACE,大小写处理函数,字符处理函数,Function,Result,大小写处理函数,能对字符串进行大小写转换.,LOWER(,SQL Course,),UPPER(,SQL Course,),INITCAP(,SQL Course,),sql course,SQL COURSE,Sql Course,使用大小写处理函数,SELECT employee_id, last_name, department_id,FROM employees,WHERE last_name = higgins;,no rows selected,SELECT employee_id, last_name, department_id,FROM employees,WHERE LOWER(last_name) = higgins;,CONCAT(Hello, World),SUBSTR(HelloWorld,1,5),LENGTH(HelloWorld),INSTR(HelloWorld, W),LPAD(salary,10,*,),RPAD(salary, 10, *),TRIM(H FROM HelloWorld),HelloWorld,Hello,10,6,*24000,24000*,elloWorld,Function,Result,字符处理函数,下列函数能对字符串进行处理:,SELECT employee_id, CONCAT(first_name, last_name) NAME,job_id, LENGTH (last_name),INSTR(last_name, a) Contains a?,FROM employees,WHERE SUBSTR(job_id, 4) = REP;,使用字符处理函数,1,2,3,1,2,3,日期函数,两个日期中的间隔月数,MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC,在日期中增加历法月数,指定日期中的下一天,这个月的最后一天,Round 日期,Truncate 日期,Function,Description,MONTHS_BETWEEN (01-SEP-95,11-JAN-94),使用日期函数,ADD_MONTHS (11-JAN-94,6),NEXT_DAY (01-SEP-95,FRIDAY),LAST_DAY(01-FEB-95),19.6774194,11-JUL-94,08-SEP-95,28-FEB-95,转换函数,隐性的数据类型转换,显性的数据类型转换,数据类型转换,使用,TO_CHAR,函数转换日期,SELECT last_name,TO_CHAR(hire_date, fmDD Month YYYY),AS HIREDATE,FROM employees;,YYYY,日期格式基本模型,YEAR,MM,MONTH,DY,DAY,Full year in numbers,Year spelled out,Two-digit value for month,Three-letter abbreviation of the day of the week,Full name of the day of the week,Full name of the month,MON,Three-letter abbreviation of the month,DD,Numeric day of the month,使用,TO_NUMBER,和,TO_DATE,函数,转换字符型到数值型:,转换字符型到日期型:,TO_NUMBER(,char,format_model,),TO_DATE(,char, ,format_model,),总结,通过这部分课程的学习，您应该已经了解:,描述SQL中用到的不同类型的函数,在SELECT语句中使用字符，数值和日期等函数,转换函数的使用,练习,请见Oracle数据库维护上机任务书,课堂练习-数据库函数,使用,TO_NUMBER,和,TO_DATE,函数,转换字符型到数值型:,转换字符型到日期型:,TO_NUMBER(,char,format_model,),TO_DATE(,char, ,format_model,),