sql数据库入门

SQL Server 2000 数据库入门SQL Server 2000lMS 数据库管理系统l组成：程序： 用户界面，工具数据结构：管理和存储数据l系统databaseMasterPubstempDB12/14/20222ERP十万个为什么http:/www.erpwhy.comSQL Server数据库l管理、存储和访问数据lDatabase对应文件： .mdf, .ldfl包含：lTable, View, Index, Store Procedure, Trigger, etc.12/14/20223ERP十万个为什么http:/www.erpwhy.comSQL 2000管理工具12/14/20224ERP十万个为什么http:/www.erpwhy.com创建databasel右键-新建数据库l输入数据库名: testl包含：数据文件: test_Data事务日志文件:test_Log及对应物理文件：.mdf, .ldf12/14/20225ERP十万个为什么http:/www.erpwhy.com表 TablelTable: 表是包含数据库中所有数据的数据库对象。 表结构： Column 列（字段）的集合 每行是一条记录。表的定义：列名 , 数据类型, 初始值， 是否允许空约束规则，索引， 主键， 外键12/14/20226ERP十万个为什么http:/www.erpwhy.com建新表l右键-> 新建表l列名， 数据类型，长度，允许空l类型：整数：bigInt: 64 bits Int: 32bits，dec, Numeric，l MoneylDatetimelChar, VarChar, NvarcharlText, Imagel默认值l自动增长( + 1)lRowGUID12/14/20227ERP十万个为什么http:/www.erpwhy.com主键、外键、l主键(PK): 唯一标示数据， 数据完整性，主键索引，查找快速l关系: 引用l 外键（FK）：表与表之间的链接，数据完整性、一致性l索引：Index，查找快速 l例如：部门， 职员12/14/20228ERP十万个为什么http:/www.erpwhy.com视图 Viewl虚拟表， 由查询定义， 包含行、列，View数据并不真正存在在View中， 通过查询其它数据源（数据表、视图、其他数据库、其他服务器）而来。12/14/20229ERP十万个为什么http:/www.erpwhy.com存储过程 Stored ProcedurelStored Procedure运行在SQL Server服务器方类似子程序模块 可以输入、输出参数包含数据库操作语句（ or call SP )Return value编程语言：Transact-SQLl优点：模块化设计、创建一次，重复使用， 后台运行，速度更快网络流量减少， 安全/* - Stored Procedure: sp_backupmessage - Get backup file message - by You jianshe 2003-2-14 - */CREATE PROC sp_backupmessageas_filename VARCHAR(200)asRestore HEADERONLY FROM DISK = as_filenameGO12/14/202210ERP十万个为什么http:/www.erpwhy.com触发器Trigger l一种特殊SP, 在指定表的数据发生变化时自动生效，即被调用、被执行。(Insert, Update, Delete时）l目的：强制业务规则和数据完整性lTrigger: Transact-SQL语法， （简单或复杂）逻辑处理lTables: Inserted, Deleted 12/14/202211ERP十万个为什么http:/www.erpwhy.com自定义类型、函数l自定义类型一般不需要l自定义函数12/14/202212ERP十万个为什么http:/www.erpwhy.com数据库设计l需求分析l逻辑模型 E-R图 - 实体关系数据流图 - 业务处理l物理模型l数据库l分析、优化12/14/202213ERP十万个为什么http:/www.erpwhy.com数据库设计Steps需求分析阶段需求收集和分析，结果得到数据字典描述的数据需求（和数据流图描述的处理需求）。概念结构设计阶段通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型，可以用E-R图表示。逻辑结构设计阶段将概念结构转换为某个DBMS所支持的数据模型（例如关系模型），并对其进行优化。数据库物理设计阶段为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）。数据库实施阶段运用DBMS提供的数据语言（例如SQL）及其宿主语言（例如C），根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。数据库运行和维护阶段数据库应用系统经过试运行后即可投入正式运行。在数据库系统运行过程中必须不断地对其进行评价、调整与修改。12/14/202214ERP十万个为什么http:/www.erpwhy.com数据库设计实例项目工作管理， 包含数据表： 职员花名册， 公司部门 系统功能 ， 权力角色 ， 职员角色 项目（Project) 、 项目成员（项目-职员） 项目任务分解（WBS， 分级，包括任务包、 工作量估计，计划， 进度， 人员安排，权重， 任务包父亲 ) 项目任务执行跟踪 （工作日志）:项目 - 职员 时间 对应任务包 实际工作量 12/14/202215ERP十万个为什么http:/www.erpwhy.comE-R图实例12/14/202216ERP十万个为什么http:/www.erpwhy.com编程接口 APIlADO, OLE DB, ODBC ( JDBC), Embedded SQL, URL, XML (Internet)使用数据库1.打开到数据库的连接。2.设置控制某些行为的选项3.执行 Transact-SQL 语句4.将返回代码的数据值、输出参数和结果集移入程序变量中，并通过程序变量由应用程序逻辑使用。5.在完成数据库中的工作后断开连接。12/14/202217ERP十万个为什么http:/www.erpwhy.com连接数据库- PBTransaction 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 = FALSEconnect Using SQLCA; / 打开连接打开连接Disconnect Using SQLCA ; / 切断连接切断连接/ Profile sssSQLCA.DBMS = "ODBC"SQLCA.AutoCommit = FalseSQLCA.DBParm = "ConnectString='DSN=Excel Files;UID=;PWD='"连接Access/ Profile AccessSQLCA.DBMS = "ODBC"SQLCA.AutoCommit = FalseSQLCA.DBParm = "ConnectString='DSN=MS Access Database;UID=;PWD='“/ / VB-1/ VB-1 cncn.Open "Driver=SQL Server;Server=Server1;.Open "Driver=SQL Server;Server=Server1;UidUid=SA;=SA;PwdPwd=;Database=;Database=northwindnorthwind" " / VB 2 / VB 2 cncn.Provider = ".Provider = "sqloledbsqloledb“ “ ProvStrProvStr = "Server= = "Server=MyServerMyServer;Database=;Database= northwind northwind; Trusted_Connection=yes" ; Trusted_Connection=yes" cn cn.Open.Open provStr provStr 12/14/202218ERP十万个为什么http:/www.erpwhy.com获取数据-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 ifLong ld_id = 0 String as_table = “tableXXX”/updateUPDATE sys_system_id SET id_counter = :ld_id WHERE id_table_name = :as_table Using SQLCA;12/14/202219ERP十万个为什么http:/www.erpwhy.com获取数据-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 if12/14/202220ERP十万个为什么http:/www.erpwhy.com与数据库连接Datawindow12/14/202221数据库入门Transact-SQL 语言简介l标示符： create Table employee ( empl_no char(10) PRIMARY KEY, empl_name varchar(20), age int default 0 , .）l数据类型： l函数：l表达式： l运算符：l注释l 关键字： 12/14/202222ERP十万个为什么http:/www.erpwhy.com创建与删除表lCreate 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 PRIMARYGOldrop tabledrop table dbo.bse_departmentGO12/14/202223ERP十万个为什么http:/www.erpwhy.com更改数据lInsert INSERT INTO bse_department (dep_no, dep_name) VALUES ( KFB, 开发部 ) GOlUpdate:UPDATE bse_department SET dep_name = development WHERE dep_no = KFBGOlDelete Delete from bse_department WHERE dep_no = KFB12/14/202224ERP十万个为什么http:/www.erpwhy.com简单查询lSelect < 列名,> from <表>Where <条件>SELECT * FROM bse_department WHERE (dep_tycode = '1')l查询统计：SELECT dep_no AS 部门编号, COUNT(*) AS 部门人数FROM bse_employeeGROUP BY dep_no12/14/202225ERP十万个为什么http:/www.erpwhy.com联合查询l联合查询 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_noGROUP BY bse_department.dep_name, bse_employee.dep_no12/14/202226ERP十万个为什么http:/www.erpwhy.com聚合函数lSUM ( )lAVG( )lCOUNT()lMAX()lMIN()Select COUNT( *) from table1 Select sum( column1) from table212/14/202227ERP十万个为什么http:/www.erpwhy.com结果集组合lUNIONSelect * from tableAUNIONSelect * from tableBUNIONSelect * from tableC lNote: 结果集结构必须相同。12/14/202228ERP十万个为什么http:/www.erpwhy.com子查询lWHERE exp NOT IN ( subquery)lWHERE exp comparison_op ( subquery)lWHERE NOT EXISTS ( subquery)l例：SELECT * FROM bse_employeeWHERE (dep_no NOT IN (SELECT dep_no FROM bse_department WHERE dep_no <> '0000')12/14/202229ERP十万个为什么http:/www.erpwhy.com多维汇总数据lCUBESELECT 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_noGROUP BY bse_department.dep_name, bse_employee.empl_cls WITH CUBElRollUplSELECT bse_department.dep_name, bse_employee.empl_cls, COUNT(*) AS 部门人数lFROM bse_employee INNER JOINl bse_department ON bse_employee.dep_no = bse_department.dep_nolGROUP BY bse_department.dep_name, bse_employee.empl_cls WITH rollup12/14/202230ERP十万个为什么http:/www.erpwhy.com事务处理lTransaction: 作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元4个 属性： 原子性，一致性，隔离性，持久性 BEGIN Transaction - BEGIN Transaction - 启启启启动事务动事务动事务动事务 Commit -Commit -提交提交提交提交 ， 结束事务结束事务结束事务结束事务 RollBackRollBack - - 回滚，回滚，回滚，回滚， 结束事务结束事务结束事务结束事务必须一次提交或全部退回必须一次提交或全部退回 要么成功，要么失败！要么成功，要么失败！确保数据完整性和一致性确保数据完整性和一致性12/14/202231ERP十万个为什么http:/www.erpwhy.com事务处理编写原则l事务保持尽量短。在多用户下，必须事务保持尽量短，减少资源锁定争夺l不要在事务处理期间要求用户输入l浏览数据时，尽量不要打开事务l必须修改数据时，启动事务，执行修改，然后马上提交(commit)或失败后回滚(rollback)事务。l灵活使用游(cursor)标并发选项，如乐观并发(OPTIMISTIC )l在事务中，尽量访问最小量数据，减少锁定数据行数。l避免并发 （Lock)12/14/202232ERP十万个为什么http:/www.erpwhy.com结束语欢迎批评指正，并提出宝贵意见！谢谢 !12/14/202233ERP十万个为什么http:/www.erpwhy.com