1 / 78

数据库基础知识培训胶片

数据库基础知识培训胶片. 集成产品部. 数据库原理. 目标. 通过这部分课程的学习 , 您将了解以下内容 : 关系数据库基本概念 常用数据库对象 常用数据类型. Informix Server. SMP. IN Database. SMAP. SMAP. SMAP. SCP. What is a database?. The database plays the role of the base, saving and processing the data that should be easily accessed by the user.

mayda
Télécharger la présentation

数据库基础知识培训胶片

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 数据库基础知识培训胶片 集成产品部

  2. 数据库原理

  3. 目标 通过这部分课程的学习,您将了解以下内容 : • 关系数据库基本概念 • 常用数据库对象 • 常用数据类型

  4. Informix Server SMP IN Database SMAP SMAP SMAP SCP What is a database? The database plays the role of the base, saving and processing the data that should be easily accessed by the user. Usually, a database runs in the server part to serve access requests from clients.

  5. DB Server Users use DML to operate data in a database DBClient (User) DBClient (DBA) DB Users use DDL to define a database Users use programs to manage database DBClient (User) DDL, DML & Programs

  6. 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

  7. IN Service Database Subscriber Data Table PPS Service Data Table FPH Service Data Table Tables are relational

  8. 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

  9. Columns 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. Stock

  10. Operations on Tables

  11. Database Objects Object Description Table Basic unit of storage; composed of rows and columns View Logically represents subsets of data from one or more tables Sequence Numeric value generator Index Improves the performance of some queries Synonym Gives alternative names to objects

  12. What is a View? EMPLOYEES Table:

  13. Why Use Views? • To restrict data access • To make complex queries easy • To provide data independence • To present different views of the same data

  14. 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

  15. 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

  16. 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.

  17. Data Types Data Type Description 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.

  18. 总结 通过这部分课程的学习,您需要掌握以下内容 : • 关系数据库基本概念 • 常用数据库对象 • 表 • 索引 • 约束 • 常用数据类型

  19. SQL语言基础

  20. 目标 通过这部分课程的学习,您将了解以下内容 : • SQL语句的种类 • 执行基本的Select语句 • 在表中插入数据行 • 在表中修改数据行 • 从表中删除数据行 • 控制事务处理

  21. SQL Statements Data retrieval SELECT INSERT UPDATE DELETE CREATE ALTER DROP TRUNCATE COMMIT ROLLBACK SAVEPOINT GRANT REVOKE Data manipulation language (DML) Data definition language (DDL) Data control language (DCL)

  22. 基础SELECT语句语法 SELECT *|{[DISTINCT] column|expression [alias],...} FROM table;

  23. 使用算术操作符 SELECT last_name, salary, salary + 300 FROM employees; …

  24. 使用列的别名 SELECT last_name AS name, commission_pct comm FROM employees; … SELECT last_name "Name", salary*12 "Annual Salary" FROM employees; …

  25. 使用连接符和字符串 SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees; …

  26. 排除重复的行 SELECT DISTINCT department_id FROM employees;

  27. 使用 WHERE条件 SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;

  28. 字符串和日期值 • 都使用单引号. • 字符串值区分大小写，日期值需注意格式. SELECT last_name, job_id, department_id FROM employees WHERE last_name = 'Whalen';

  29. 比较条件 操作符 = > >= < <= <> 含义 等于 大于 大于或等于 小于 小于或等于 不等于

  30. 其它的比较条件 操作符 BETWEEN...AND... IN(set) LIKE IS NULL 含义 两个值之间 匹配值列表中值 匹配一个字符样式 空值

  31. 使用 BETWEEN条件 可以使用该条件显示某范围内的值. SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; Lower limit Upper limit

  32. 使用 IN条件 满足成员列表中的条件. SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);

  33. 使用 LIKE条件 • 搜索条件能包括两种模式匹配类型: • %表示许多字符. • _表示一个字符. SELECT first_name FROM employees WHERE first_name LIKE 'S%';

  34. 使用 NULL条件 测验空值使用 IS NULL 条件. SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;

  35. 逻辑条件 Operator ANDOR NOT 含义 两部分条件都返回真值则结果返回真值 两部分条件中有任意一部分返回真值则结果返回真值 如果条件返回假则结果返回真值

  36. 使用 AND操作符 要求条件都为真. SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%';

  37. Using the NOT Operator SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');

  38. ORDER BY条件 • 带 ORDER BY条件的排序 • ASC: 升序，缺省 • DESC: 降序 • ORDER BY条件在Select语句最后. SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY hire_date ; …

  39. 数据操作语句 • 一条 DML 语句在下列情况下被执行: • 向表中增加新的数据行 • 修改表中存在的数据 • 从表中删除数据 • 一个事务由DML语句集合形成的逻辑工作单元构成.

  40. INSERT语句语法 . INSERT INTO table [(column [, column...])] VALUES (value [, value...]);

  41. 向表中插入新的数据行 • 插入的新行应包括所有的列值. • 插入的值应按照表中列缺省的顺序排列. • 如插入所有的列, INSERT条件中可随意选择是否列出所有列. INSERT INTO departments(department_id, department_name, manager_id, location_id) VALUES (70, 'Public Relations', 100, 1700); 1 row created.

  42. 插入特殊的日期型值 • Verify your addition. 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.

  43. 从另外一个表中复制行 • 带子查询的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.

  44. UPDATE语句语法 • 可一次性修改多行 UPDATE table SET column = value [, column = value, ...] [WHERE condition];

  45. 修改表中数据 • 在WHERE条件中指定需要修改的行. • 修改表中所有的行. UPDATE employees SET department_id = 70 WHERE employee_id = 113; 1 row updated. UPDATE copy_emp SET department_id = 110; 22 rows updated.

  46. 基于另外表SELECT得到的值修改数据 使用子查询实现： 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.

  47. DELETE 语句语法 DELETE [FROM] table [WHERE condition];

  48. 从表中删除多行 • 通过WHERE条件指定删除的行. • 省略 WHERE条件，所有的行都将被删除. DELETE FROM departments WHERE department_name = 'Finance'; 1 row deleted. DELETE FROM copy_emp; 22 rows deleted.

  49. 基于另外表SELECT得到的值删除数据 通过子查询语句从另外表中得到值作为删除条件. DELETE FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%'); 1 row deleted.

  50. 数据库事务 一个数据库完整事务包含以下情况: • 构成对数据的一次一致性改变的DML语句（一条或多条） • 一条DDL 语句

More Related