《sql数据库入门》PPT课件.ppt

SQL Server 2000 数据库入门 2021/1/24 ERP十万个为什么 http:/ 2 SQL Server 2000 MS 数据库管理系统 组成： 程序： 用户界面，工具 数据结构：管理和存储数据 系统 database Master Pubs tempDB 2021/1/24 ERP十万个为什么 http:/ 3 SQL Server数据库 管理、存储和访问数 据 Database 对应文件： .mdf, .ldf 包含： Table, View, Index, Store Procedure, Trigger, etc. 2021/1/24 ERP十万个为什么 http:/ 4 SQL 2000管理工具 2021/1/24 ERP十万个为什么 http:/ 5 创建 database 右键 - 新建数据库 输入数据库名 : test 包含： 数据文件 : test_Data 事务日志文件 :test_Log 及对应物理文件： .mdf, .ldf 2021/1/24 ERP十万个为什么 http:/ 6 表 Table Table: 表是包含数据库中所有数据的数 据库对象。 表结构： Column 列（字段）的 集合 每行是一条记录。 表的定义： 列名 , 数据类型 , 初始值， 是否允 许空 约束规则，索引， 主键， 外键 2021/1/24 ERP十万个为什么 http:/ 7 建新表 右键 - 新建表 列名， 数据类型，长度，允许空 类型：整数： bigInt: 64 bits Int: 32bits， dec, Numeric， Money Datetime Char, VarChar, Nvarchar Text, Image 默认值 自动增长 ( + 1) RowGUID 2021/1/24 ERP十万个为什么 http:/ 8 主键、外键、 主键 (PK): 唯一标示数据， 数 据完整性，主键索引，查找 快速 关系 : 引用 外键（ FK）：表与表之间的 链接，数据完整性、一致性 索引： Index， 查找快速 例如：部门， 职员 2021/1/24 ERP十万个为什么 http:/ 9 视图 View 虚拟表， 由查询定义， 包含行、列， View数据并不真正存在在 View中， 通过 查询其它数据源（数据表、视图、其他数 据库、其他服务器）而来。 2021/1/24 ERP十万个为什么 http:/ 10 存储过程 Stored Procedure Stored Procedure 运行在 SQL Server服务器方 类似子程序模块 可以输入、输出参数 包含数据库操作语句（ or call SP ) Return value 编程语言： Transact-SQL 优点： 模块化设计、创建一次，重复使用， 后台运行，速度更快 网络流量减少， 安全 /* - Stored Procedure: sp_backupmessage - Get backup file message - by You jianshe 2003-2-14 - */ CREATE PROC sp_backupmessage as_filename VARCHAR(200) as Restore HEADERONLY FROM DISK = as_filename GO 2021/1/24 ERP十万个为什么 http:/ 11 触发器 Trigger 一种特殊 SP, 在指定表的数据发生 变化时自动生效，即被调用、被执 行。 (Insert, Update, Delete时） 目的：强制业务规则和数据完整性 Trigger: Transact-SQL语法， （简单或复杂）逻辑处理 Tables: Inserted, Deleted 2021/1/24 ERP十万个为什么 http:/ 12 自定义类型、函数 自定义类型 一般不需要 自定义函数 2021/1/24 ERP十万个为什么 http:/ 13 数据库设计 需求分析 逻辑模型 E-R图 - 实体关系 数据流图 - 业务处理 物理模型 数据库 分析、优化 2021/1/24 ERP十万个为什么 http:/ 14 数据库设计 Steps 需求分析阶段 需求收集和分析，结果得到数据字典描述的数据需求（和数据流图描述的处理需求）。 概念结构设计阶段 通过对用户需求进行综合、归纳与抽象，形成一个独立于具体 DBMS的概念模型，可以 用 E-R图表示。 逻辑结构设计阶段 将概念结构转换为某个 DBMS所支持的数据模型（例如关系模型），并对其进行优化。 数据库物理设计阶段 为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）。 数据库实施阶段 运用 DBMS提供的数据语言（例如 SQL）及其宿主语言（例如 C），根据逻辑设计和物理 设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。 数据库运行和维护阶段 数据库应用系统经过试运行后即可投入正式运行。在数据库系统运行过程中必须不断 地对其进行评价、调整与修改。 2021/1/24 ERP十万个为什么 http:/ 15 数据库设计实例 项目工作管理， 包含数据表： 职员花名册， 公司部门 系统功能 ， 权力角色 ， 职员角色 项目（ Project) 、 项目成员（项目 -职员） 项目任务分解（ WBS， 分级，包括任务包、 工作量估计，计划， 进度， 人员安排，权重， 任务包父亲 ) 项目任务执行跟踪 （工作日志） : 项目 - 职员 时间 对应任务包 实际工作量 2021/1/24 ERP十万个为什么 http:/ 16 成成 0 , n 0 , 1 成成成成 0 , n 0 , n 成成 0 , n 1 , 1 R e l a t i o n s h i p _ 4 0 , 1 0 , n W B S 0 , n 0 , 1 成成成成 0 , n 0 , 1 成成 d e p _ I D N a m e M a n a g e r 成成 e m p l _ I D e m p l _ N a m e e m p l _ t y p e d e p _ I D 成 成 I D 成成成成 成成成成 成成成成 成成 P r j _ I D 成成 成成成成成成 w o r k l o a d 成成成成 P r j _ I D e m p l _ I D 成成成成 成成成成 成 成 W B S P r j _ I D T a s k _ I D P a r e n t _ T a s k _ I D 成 成 成 成 e m p l _ I D 成成 成成 成成成成成 成成成成成成 成 成 _ I D P r j _ I D T a s k _ I D 成成成成成成 成成成成成 成成 C o n c e p t u a l D a t a M o d e l M o d e l : P M P a c k a g e : D i a g r a m : P M A u t h o r : A d m i n i s t r a t o r D a t e : 2 0 0 4 - 1 - 6 V e r s i o n : E-R图实例 2021/1/24 ERP十万个为什么 http:/ 17 编程接口 API ADO, OLE DB, ODBC ( JDBC), Embedded SQL, URL, XML (Internet) 使用数据库 1. 打开到数据库的连接。 2. 设置控制某些行为的选项 3. 执行 Transact-SQL 语句 4. 将返回代码的数据值、输出参数和结果集移入程序 变量中，并通过程序变量由应用程序逻辑使用。 5. 在完成数据库中的工作后断开连接。 2021/1/24 ERP十万个为什么 http:/ 18 连接数据库 - PB Transaction SQLCA / 事务对象 sqlca.DBMS = ProfileString (gs_inifile, DATABASE, dbms, MSS) sqlca.DATABASE = ProfileString (gs_inifile, DATABASE, DATABASE, KF) sqlca.userid = ProfileString (gs_inifile, DATABASE, userid, dbo) sqlca.dbpass = ProfileString (gs_inifile, DATABASE, dbpass, ) sqlca.logid = ProfileString (gs_inifile, DATABASE, logid, sa) sqlca.logpass = ProfileString (gs_inifile, DATABASE, LogPassWord, ) sqlca.servername = ProfileString (gs_inifile, DATABASE, ServerName, (local) sqlca.dbparm = ProfileString (gs_inifile, DATABASE, Dbparm, ) sqlca.AutoCommit = FALSE connect Using SQLCA; / 打开连接 Disconnect Using SQLCA ; / 切断连接 / Profile sss SQLCA.DBMS = ODBC SQLCA.AutoCommit = False SQLCA.DBParm = ConnectString=DSN=Excel Files;UID=;PWD= 连接 Access / Profile Access SQLCA.DBMS = ODBC SQLCA.AutoCommit = False SQLCA.DBParm = ConnectString=DSN=MS Access Database;UID=;PWD=“ / VB-1 cn.Open Driver=SQL Server;Server=Server1;Uid=SA;Pwd=;Database=northwind / VB 2 cn.Provider = sqloledb“ ProvStr = Server=MyServer;Database= northwind; Trusted_Connection=yes cn.Open provStr 2021/1/24 ERP十万个为什么 http:/ 19 获取数据 -SQL语句 String ls_empl_name / 变量定义 Select empl_name INTO :ls_empl_name FROM bse_employee Where empl_no = system Using SQLCA ; if sqlca.sqlcode 0 then / 失败 Else / . 成功 End if Long ld_id = 0 String as_table = “tableXXX” /update UPDATE sys_system_id SET id_counter = :ld_id WHERE id_table_name = :as_table Using SQLCA; 2021/1/24 ERP十万个为什么 http:/ 20 获取数据 -Datawindow / Dw_list Datawindow / d_employee_edit Datawindow Object dw_list.SetTransobject(SQLCA) dw_list.Retrieve( g_userinfo.is_dep_no, g_userinfo.is_empl_cls) . /保存数据 ll_ret = dw_list.update(TRUE ,TRUE) / 判断 If ll_ret = 1 then Commit ; / using SQLCA Else Rollback; / using SQLCA MessageBox(提示 ,保存时出错 !) End if 2021/1/24 ERP十万个为什么 http:/ 21 与数据库连接 Datawindow 2021/1/24 ERP十万个为什么 http:/ 22 Transact-SQL 语言简介 标示符： create Table employee ( empl_no char(10) PRIMARY KEY, empl_name varchar(20), age int default 0 , . ） 数据类型： 函数： 表达式： 运算符： 注释 关键字： 2021/1/24 ERP十万个为什么 http:/ 23 创建与删除表 Create Table CREATE TABLE dbo.bse_department ( dep_no varchar (10) not NULL , dep_tycode char (1) NULL , dep_name char (10) NULL , dep_no1 char (4) NULL , dep_name1 char (10) NULL , dep_bld_date datetime NULL , dep_vendor_shared smallint NULL , dep_telno char (20) NULL ) ON PRIMARY GO drop table drop table dbo.bse_department GO 2021/1/24 ERP十万个为什么 http:/ 24 更改数据 Insert INSERT INTO bse_department (dep_no, dep_name) VALUES ( KFB, 开发部 ) GO Update: UPDATE bse_department SET dep_name = development WHERE dep_no = KFB GO Delete Delete from bse_department WHERE dep_no = KFB 2021/1/24 ERP十万个为什么 http:/ 25 简单查询 Select from Where SELECT * FROM bse_department WHERE (dep_tycode = 1) 查询统计： SELECT dep_no AS 部门编号 , COUNT(*) AS 部门人数 FROM bse_employee GROUP BY dep_no 2021/1/24 ERP十万个为什么 http:/ 26 联合查询 联合查询 JOIN ( INNER, LEFT, RIGHT ) SELECT bse_department.dep_name, COUNT(*) AS 部门人数 FROM bse_employee INNER JOIN bse_department ON bse_employee.dep_no = bse_department.dep_no GROUP BY bse_department.dep_name, bse_employee.dep_no 2021/1/24 ERP十万个为什么 http:/ 27 聚合函数 SUM ( ) AVG( ) COUNT() MAX() MIN() Select COUNT( *) from table1 Select sum( column1) from table2 2021/1/24 ERP十万个为什么 http:/ 28 结果集组合 UNION Select * from tableA UNION Select * from tableB UNION Select * from tableC Note: 结果集结构必须相同。 2021/1/24 ERP十万个为什么 http:/ 29 子查询 WHERE exp NOT IN ( subquery) WHERE exp comparison_op ( subquery) WHERE NOT EXISTS ( subquery) 例： SELECT * FROM bse_employee WHERE (dep_no NOT IN (SELECT dep_no FROM bse_department WHERE dep_no 0000) 2021/1/24 ERP十万个为什么 http:/ 30 多维汇总数据 CUBE SELECT bse_department.dep_name, bse_employee.empl_cls, COUNT(*) AS 部门人数 FROM bse_employee INNER JOIN bse_department ON bse_employee.dep_no = bse_department.dep_no GROUP BY bse_department.dep_name, bse_employee.empl_cls WITH CUBE RollUp SELECT bse_department.dep_name, bse_employee.empl_cls, COUNT(*) AS 部门人数 FROM bse_employee INNER JOIN bse_department ON bse_employee.dep_no = bse_department.dep_no GROUP BY bse_department.dep_name, bse_employee.empl_cls WITH rollup 2021/1/24 ERP十万个为什么 http:/ 31 事务处理 Transaction: 作为单个逻辑工作单元执行 的一系列操作。一个逻辑工作单元 4个 属 性： 原子性，一致性，隔离性，持久性 BEGIN Transaction - 启 动事务 Commit -提交 ， 结束事务 RollBack - 回滚， 结束事务 必须一次提交或全部退回 要么成功，要么失败！ 确保数据完整性和一致性 2021/1/24 ERP十万个为什么 http:/ 32 事务处理编写原则 事务保持尽量短。在多用户下，必须事务保持 尽量短，减少资源锁定争夺 不要在事务处理期间要求用户输入 浏览数据时，尽量不要打开事务 必须修改数据时，启动事务，执行修改，然后 马上提交 (commit)或失败后回滚 (rollback)事务。 灵活使用游 (cursor)标并发选项，如乐观并发 (OPTIMISTIC ) 在事务中，尽量访问最小量数据，减少锁定数 据行数。 避免并发 （ Lock) 2021/1/24 ERP十万个为什么 http:/ 33 结束语 欢迎批评指正， 并提出宝贵意见！ 谢谢 !