Sybase basic knowledge First, Client / Server Architecture Sybase is built on a client / server architecture of the database management system. What is the client / server architecture? From the hardware point of view, client / server architecture refers to a particular task in the machine between two or more distribution, client (Client) adapter for operation and provide front-end user applications, the server machine (Server) customers to use the resources and services. From software point of view, client / server architecture is a logical and functional application or system software for server software and client software. The general said some client software for data and application processing user interface for the users to receive data processing requests to the server will convert to the request to the server for data storage and retrieval services; Client software for the server to receive the request and to offer software development services. Client / server functions and integration of Mainframe strong central control and the low-cost PC and better handling balance. Client / server tasks focus / local distribution to provide a new approach, which will enable users to the system data integrity, management and centralized control for security. In mitigation, and server load and network traffic to meet the needs of users, the client / server system provides a good solution. In short, the client / server model of the network is : agreements between clients and servers (such as TCP, IPX/SPX) connectivity and communications, a request issued by the client to server, server to respond to requests and the corresponding services. Database client / server model as in the figure below : Second, the basic process visit Sybase server 1. Creating a link between the client and server, network, customer process and the link between the server process; 2. Client sent through the network to the SQL server, for information or inquiries or operating server database objects; 3. After receipt of SQL Server, its syntax analysis, optimization and translation after implementation; 4. If the sentences have implemented a result, and the server through the network to return the results to clients Set; 5. Client corresponding to the results of the treatment received. In client / server system, there are two types of database engines, database engine and single-process, multi-process, multi-threaded engines. Multi-process structure that is more enforceable procedures for the same time. When users download the database system to actually start a database engine as a separate case. There is a coordinating role in the coordination process to assure that the communications between multi-user accessing the same data integrity of data. Large multi-process database engine used in the general database. Single-process, multi-thread, multi-process database engine and database engine in principle similar to that of the database engine itself is a multi-threaded application scheduling of the CPU time occupied, and not dependent on the operating system. This multithread database engine's capacity for self-protection stronger. Oracle Server is a truly multi-process database engine, Sybase database management system using the single-process, multi-thread structure of the engine. Third, Sybase products outlined 1. Sybase software component Sybase software can be divided into three parts : First, the on-line data management and maintenance of relational database management systems to support Sybase SQL Server Database Application System 2 is a group of front-end development tool for creating software and Sybase SQL Tools; Third heterogeneous environment may be other manufacturers of any type of data link software and the Open Software Client/Open Server Adapter. Programmable SQL Server is a database management system (DBMS), which is the core of Sybase software products plays a data management, high-speed buffer management, business management role. 2. The basic features of SQL Server SQL Server is a relational database management system, which has the following basic characteristics : A.SQL Server can be put on a number of disk equipment, required for the initial installation of at least 17MB of disk space. B.SQL Server supports multi-reservoir structure, which means there could be a number of Sybase database system. Sybase can manage multiple databases. Compiling and Running SQL Server can c.SQL words, customers may return required by the procedures. SQL is a standard language SQL expansion, which in addition to data definition language, data manipulation language and data control language, the main increase in the flow control statements. D.SQL Server can manage the high number of users and transaction processing services and lower response time. Application Server customers can visit one or a few data bank. Fourth, the main contents of SQL Server Sybase SQL Server RDBMS is a multi-reservoir structure, the structure is as follows : 1. Database Used in the database server itself, it can also be said that the database management server and user database. Sybase installation, automatic database created four : Master, model, tempdb, sybsystemprocs (1) master database It is the management and control of users and database servers to maintain the normal operation of the core database, it preserved a lot of information systems, such as server configuration, user and equipment. Putin allowed in the master database to create database objects in overall households, or master database will be made soon change log first. If the log exhausted, we will not be able to use dump transaction ordered the release of the master database space. (2) model database It is the template for the creation of database users. When creating a new database, SQL Server database automatically build a model to copy, and it expanded to the size of user requirements, as a new user database. Model database contains each user that the system database table. Model database can be modified to create a new customized. (3) tempdb database It is a temporary database server for the operation and treatment with a common storage area, and group by the middle order by the results stored in here. Tempdb database of all the space for server shared by all users. Each restart SQL Server, the server automatically processes a copy of the model database tempdb database, and removed tempdb original content. So tempdb Table of users are temporary. Provisional List can be divided into two categories : shared and non-shared. Not share the table will create the temporary table by the # symbol table were placed before the creation; Sharing can create a temporary table to table name specified in Table Prefix tempdb. . Created. Not sharing the temporary table for SQL Server automatically add digital suffix, and also it exists in the current conversation. (4) database master database sybsystemprocs It is specifically designed to preserve order system (stored) in the database, such as sp_help, sp_configure, sp_helpdevice other. When users running a database to store sp_ beginning of the process, in accordance with the following order to find out : the SQL Server database, sybsystemprocs database, master database. 2. User Database We use Sybase database server users is the real objective. Users to manage data in Sybase establish its own database, it is referring to create databases order to create the database. Users can visit the master database is not to create a new database. The main content of the database : -- Object Database Table, view, the Provisional List Index, primary key and foreign key Default values, rules Storage process, flip-flop, etc. 5, Sybase installation and configuration 1. Server installation Creation of SQL Server installation, the place to build the database, log and logical disk indexing equipment. Database, log index and the allocation should pay attention to the following principles; A. Do not install any user objects in the master database. B. Should be kept in a separate database and log disk. C. Can optimize distribution across I/O equipment performance. 2. Client Installation 3. The need for immediate change (1) change the sa login password; (2) name server; (3) amend the document (to ensure the normal start Sybase Central); (4) change the default equipment; (5) increase tempdb space. 4. Create user database Following the establishment of a database through the database script shows the process : 4003rd create a database of equipment, the equipment to page (2K) for Disk init Name="_ftn1" "test_dbdev" Physname= "c:\test\test_dbdev.dat" Vdevno=10. Size=10240 Go Disk init Name="_ftn1" "test_logdev" Phyname= "c:\test\test_logdev.dat" Vdevno=11. Size=5120 Go 4003rd TEST_DB creating a database, the size of 20M, 10M size of the log Create database TEST_DB On test_dbdev=20 Log on test_logdev=10 Go 4003rd open database Use TEST_DB Go 5. The main content management system A. Physical resource management B. Users and their management competence C. Database Backup and restore 6. SQL Server configuration parameters Server Configuration is a systems administrator duties, the proper allocation of a significant impact on system performance. There are two storage system configuration information : Table syscurconfigs;sysconfigures sysconfigures and is a permanent, once the system's operation sysconfigures syscurconfigs information on which to copy. Configuration and change configuration process using the system showed sp_configure. There are two values : a dynamic and static configuration, the dynamic changes take effect immediately once the static value to the role of the system after the re-start. 2 : database management facilities and storage space I. OVERVIEW 1. Installation Initialization Initial installation of SQL Server, the main equipment installation and initialization script, and the creation of master and model, and sybsystemprocs tempdb database. System databases, predefined equipment and acquiescence of the following methods : A.master, model, tempdb database installed in the main equipment master; B.sybsystemprocs database installation option when installing the equipment (sysprocsdev); C. Predefined database creation for each of the three : system, default and logsegment; D. Creating a database of all users of the equipment is acquiescence master equipment; E. If the audit database sybsecurity choose to install it at their own equipment. 2. The main problem with memory management facilities (1) restoration Physical disk collapsed, disk mirroring or log kept in a separate physical facilities provided for the restoration of the two mechanisms database. (2) Performance I/O disk read and write operation is the speed bottleneck correct to make the database available to the physical object is conducive to improving equipment performance; Log objects placed in a separate database and put the equipment can improve system performance; Put on the table and put a hard disk on another hard disk indexing, as the work was transferred to the two hard drives, reading and writing speed will ensure physics; Disk Mirroring reduce disk write speed. Second, equipment (Device) Sybase data stored in the database of all equipment. 1. The concept equipment Sybase advance deployment of specialized equipment is stored in the database for a piece of disk space, and it was mapped to a document or a primitive operating system on the disk area. It has two names : the logical name and physical counterparts were. NT only support equipment mapping of the document. Equipment and the relationship between the database : many-to-many relationships. A database can be created or expanded a number of facilities, the equipment can also be used to store more than one database. Parallel to its different equipment operating system can read and write, we can artificially to a database available to multiple databases equipment. Device and equipment categories : Database Dump Device. Database and the log database storage equipment, transfer and storage of equipment used in the database or log backup. 2. Building equipment Grammar order : DISK INIT Name= 'device_name' Physname= 'physical_name' Vdevno=virtual_device_number Size=number_of_pages […….] To illustrate : DISK INIT Name= 'My_Device' Physname= 'D:\database\My_device.dat' Vdevno=3 Size=5000 Notes : logical name, physical names, virtual equipment, the equipment Store building equipment : Sp_addumpdevice{ 'disk' | 'tape') Logical_Name. Physical_Name. TapeSize 3. Acquiescence equipment In the absence of the designated equipment, users automatically create data objects stored in any acquiescence equipment. After the initial installation, the system's main equipment has been pre-designated as a master acquiescence equipment, it will be as soon as possible to build up their acquiescence equipment. The following equipment : acquiescence to ensure that the equipment is not the main system equipment, designated only by the equipment used to log. Sp_diskdefault equipment were [DefaultOn|DefaultOff] 4. Disk Mirroring Disk Mirroring is based database security considerations, media failure, disk mirroring to provide uninterrupted restoration. Disk Mirroring is the absolute copy of the data disk. If a hard disk incident, a copy of which was damaged offline automatically become a state, thus not lead were all writable copy of damage. Sybase is the disk image on the level of equipment, equipment disk mirroring essentially mirror. When the equipment of a mirror operation, automatic creation of a Sybase additional equipment from the original image processing equipment to mirror copy of all data on the equipment. If into a mirror database, the database was assigned to each of equipment have become mirror. Master Server equipment in a special position, and if it is damaged, SQL Server will collapse. So if there may always Mirror Master equipment (another disk). Mirror order grammar : Disk Mirror Name= 'device_name' 4003rd equipment was Mirror Mirror= 'physical_name' [Writes=serial|noserial] 5. Remove equipment Sp_dropdevice logical_name[, delfile] The database contains not allowed to remove equipment. Third, the creation and use of (Segment) is the logical combination of database disk space equipment, which can be regarded as the labeling equipment at one or more databases. Object databases can be controlled use of the storage location can be stored to the database object classification of different sections. Of the equipment and many-to-many relationships : the relationship between. Creating a number of equipment, a number of equipment can be covered. 1. Use of the advantages A. Control of the use of space : one on the outside of the growth in the database would not object; B. Improve the performance of the equipment : different disk can read and write in parallel; D. Table : with the big, big can be a separate section on the physical equipment, such as a list of text or image of a data storage on the other. 2. Creation of Sp_addsegment name of the database, and equipment were Note : in certain designated equipment for the creation of a database of. Expanding the scope of Sp_exetendsegment name of the database, and equipment were Note : equipment must be available in the database, the need to expand the database to the new equipment; Specified above, databases, equipment must exist. ． narrowing of the scope : Sp_dropsegment name of the database, and equipment were Note : with a third parameter, the order is not deleted, only a narrowing of the scope. If any of the other contains a paragraph to monopolize the equipment, it is necessary to narrow the scope of the paragraph. 3. Use of ． Two of the different databases on the same equipment, they will not affect each other; Whenever the database with additional space, additional space will be automatically assigned to each of the last; For example : alter database my_db On data_dev=50 IEMG my_db increase in data_dev equipment for the space, which are automatically assigned to IEMG space of a database. Caution : If data_dev for a new database, and the default of the system will automatically extend to the equipment. Orders can alter database log log space on the additional allocation option. (1) of creating a new object Create table form (known data types) of people [on] Create [clusterd|non clusterd]index name on indexing table (shown) of the people [on] : By definition, gathered on the same section of the index table always. (2) in place of the existing targets Sp_placeobject of people, who object : The order is not an object from one database to another mobile equipment, equipment, it may affect the future of space allocation. The text can be a big field or fields planted to a separate image of the equipment there. Sp_placeobject of people, "Table name. Field " (3) the creation of gathering Index By definition, gathered on the same section of the index table always. If the creation of a table, and the creation of another gathering in the index, the index together with its mobile form, the entire table to table to the left of the building and moved to the creation of the index gathered. Through this method, a rapid and convenient manner designated to a specific form of mobile equipment. (4) the system of predefined When users create a database, Sybase, the automatic creation of three predefined : System : storage system (including the definition of the object of all users) Default : storage of the user to create various objects, unless they clearly designated to different sections. Logsegment : repository of the log. (5) the deletion of Is the deletion of the narrow scope of a special case : Sp_dropsegment name of the database name 4. Using a threshold management Threshold (Threshold) management is a mechanism to automatically monitor database free space, the threshold Sybase database management allows users to a certain threshold value of the free space for the storage process and the corresponding definition. When the home of the first free space below the threshold value, the automatic operation of the corresponding Sybase stored procedure. In a practical database, log data rate of growth than the growth of the log once the free space exhausted acquiescence in the case of SQL Server will link all data manipulation services, client applications to a stop. Separation of storage in each of its business on the log database is automatically set up a last chance threshold (Last Chance Threshold), the threshold is the backup log, the estimated value of free space. When the home of the free space below the threshold value, Sybase sp_thresholdaction memory process called automatic operation. Name system and the process by pre-defined parameters, as prepared by the user. The following is a simple example. CREATE PROCEDURE dbo.sp_thresholdaction LEAVES OF 13 SPECIES OF LAURACEAE location of the transmission parameters to the name change, but can not change its definition and order */ db_name Varchar (30), database name */ LEAVES OF 13 SPECIES OF LAURACEAE seg_name Varchar (30), who */ of LEAVES OF 13 SPECIES OF LAURACEAE space_lefe Int, LEAVES OF 13 SPECIES OF LAURACEAE remaining free space */ status Int/* last chance threshold, the value of 1, the other threshold, the value of 0*/ AS BEGIN LEAVES OF 13 SPECIES OF LAURACEAE users preparation process as */ Dump transactiondb_name With truncate_only Residents Database and log Lecture 3 First, users create database Create Database database name On _1=Size_1 equipment, 4003rd units : M _2=Size_2 Equipment. …… Log on log equipment =Log_Size [With Override]// in the same equipment used to create the database and log option [For Load] 4003rd prohibit users from visiting until the database load and restore operations are completed For example : Create Database test_db On data_dev=100, 4003rd units : M Index_dev=50 Log on log_dev=30 Note : (1) log on separate equipment, help improve the performance of the database; (2) Picture shows : Data_dev Index_dev log_dev (3) If the database and log on the same equipment, it will be impossible to realize incremental backup; (4) tend to reduce the scope of a Default System and equipment, such as the deletion of the System and Default Index_dev equipment, the creation of new, specialized database for storing objects. Second, change database 1. Changes in the database is the main Usually a system administrator to create user database, which is the main acquiescence is dbo. Sp_changeddbowner process is a system of the main database can be changed, it must be changed by the database administrator is in the main database implementation. Grammar as follows : Sp_changeddbowner login_name[, True] True parameters which will be used to transfer to the new authority is the main half. 2. Database expansion (1) spatial database expansion Alter database name database On the expansion of space equipment were 4003rd units : M = If the database is for the expansion of new equipment, and Default of System will be automatically extended to the equipment. (2) to expand, log on to the new equipment Sp_logdevice database, and equipment were For example : In addition to the expansion 5M database for the storage of log Alter database my_db On my_dev=5 Go Sp_logdevice my_db, my_dev Go 3. Delete database Drop database name database Delete all equipment to be removed from the database, delete equipment orders : sp_dropdevice 3 log Log files are used to record every amendment database of documents. SQL Server database every one has its own log files, chart syslogs, also known as the log. Log lift at the failure to restore services and the basis for the Panel. In some cases, the log is more important than the data itself. What is the matter? Database Services is for the amendment. Operating a business is a sequence of these operators are either all done or not all, it is an indivisible unit. No services have the following characteristics. (1) implementation of the atom (Atomic); (2) to maintain data consistency (Consistency); (3) mutual isolation (Isolation); (4) The persistent (Durability). Four features of the above issues as matters ACID criteria. Services in the process, begin to change the SQL Server and Database Services and the end of the insertion, deletion and updating of each operation as a log to log, which records are kept. Service in the first operation of the updated database buffer (memory), were used to buffer the data page record operating activities (data page) and log pages (log page). When tran commit to the operation, the first buffer will more log disk writes, and then writes the data pages from the disk buffer, followed "with the log (write_ahead log)" principle, thus ensuring the failure of circumstances, the log can be adopted to maximize the restoration. Must be lifted to restore failure has not yet been submitted to the Panel, the Panel has completed the write buffer from the database if there is still no equipment, it is also necessary to re-operation of the Panel. Report : creating a database loaded with examples Use master Int decl arevedvno Selectvdevno=max (convert (tinyint, substring (convert (binary (4), d.low) v.low,1))) +1 From master.dbo.sysdevices d, master.dbo.spt_values v Where v.type= 'E' and v.number=3 Declarev_str char (2) Selectv_str=convert (char (2),vdevno) Printv_str Disk init Name="_ftn1" "YDDATA" Physname= "D:\Syb_Data\YDDATA.dat" Vdevno=@vdevno+1. Size=153600 Disk init Name="_ftn1" "YDINDEX" Physname= "D:\Syb_Data\YDINDEX.dat" Vdevno=@vdevno+2. Size=102400 Disk init Name="_ftn1" "YDLOG" Physname= "D:\Syb_Data\YDLOG.dat" Vdevno=@vdevno+3. Size=76800 Create database YDMISDB On YDDATA=300, YDINDEX=200 Log on YDLOG=150 Use YDMISDB Execute sp_addsegment indexdev, YTMISDB, YTINDEX Execute sp_dropsegment "default" YTMISDB, YTINDEX Execute sp_dropsegment system, YTMISDB, YTINDEX Use master Load database YDMISDB from 'd:\yd_dump\ydmis_backup.dmp' Online database YDMISDB Fourth stresses safety and user management database First, security management outlined Database security is the protection of the database to prevent unlawful use of the data disclosure, modification or destruction. SQL Server role-based security management is a (role) methods of management at different levels of competence (or roles) of users with different user competence. Authority provides users with three factors : users and data objects and operations, in which users what data objects that can be implemented on any operation. Access to SQL Server data four barriers : (1) operating system -- OS download (2) -- Sybase server server logs (3) -- Sybase database users download (4) authorized database objects -- objects Second, understanding the role database The role of collective authority. 1. System predefined role Is a true database server and the database administrator should have, with all the authority and its database management server. In a large system, the database administrator often by more than one person, but functional management tasks, each mentioned when different responsibilities. Such is the role of justice system is scheduled to meet the design requirements. ． System Administrator (sa_role) Sa_role role has nothing to do with the implementation of the specific application and database management authority : With the installation and update SQL Server With the management of the physical storage server With the system installed configuration parameters; With the build-user database; With the authority conferred SQL Server users Recent ┅ ┅ ． System Security Administrator (sso_role) Sso_role used for security-sensitive operations, the implementation of security tasks : With the server download accounts; With the password management; With the exception granted to any outside sa_role role; Recent management audit system; ◇┅┅ ． Operator (oper_role) Oper_role implementation of the entire scope of the database server operations, such as backup and restore arbitrary database : With the database and log dump Recent reproduced with the log database Recent ┅ ┅ 2. Creating and self-defined role (1) establish a new role : create role (2) the role entrusted to grant authority : (3) the role of authority set up accounts recorded : sp_role Format : sp_role "grant" | "revoke" role, and account numbers were posted Here the "grant" | "revoke" refers to the role of authority is granted or withdrawn. Third, the account management server Sybase system in order to become a user, one must first have their own login account. Through this account password to the server, together with their registration before they can use the Sybase system resources, each of these accounts in the system are given on behalf of the logo SUID Server level. 1. Add download Sp_addlogin download, and password [database] default If not specified default database, the default database for the master account reservoir. 2. Attribute change recorded With the change password; With the lock accounts; With the deadline for setting; With the given role. Sybase Fourth, users of the database management Through accounts registered to a server, in order to use a database, the database must become a user. Recorded with the user through the creation of the link between management of the database. 1. The concept Group (group) is a set of database users. Belong to any group of users, he automatically has the authority to the group. Group members Sp_addgroup With the accession of a certain user groups : (1) To change the user attribute; (2) specified when creating new users. Recent public groups belonging to all users automatically, even if the user has other group in the fall. 2. Creating new users Sp_adduser download, and a user ID [group name] 3. Alias users Sybase mechanism can make the alias accounts corresponding to the number of users with a database, thus entered in the database have the same authority. This audit system is a responsibility as a means of control. Sp_addalias download, and the database user name Alias others -- equivalent to a certain allocation of the key users (download). 5, user management competence Object database users to control the visit, there are two groups : the order of authority and competence object authority. 1. Order authority Create Database Create Table Create View Create Procedure Create Rule Create default Powers and authority authorized orders : Grant ordered authority Group portfolio to customers who were | | role Revoke order authority portfolio from user groups who were | | role 2. Object authority Select Update Insert Delete Reference Execute Object authority empowered with the right of : Grant targets portfolio authority on database users who object to public| group who | | role [With Grant Option] Revoke Object Database on competence portfolio public| groups who object to users who | | role From public public| user group who were | | role [Cascade] For the powers and authority : Recent Grant Insert, Delete on Employee To user_1, Group_1 Recent Grant Execute on Pro_culculate To public Recent Select on Employee Grant (emp_id, emp_name) To user_3 All of America on Employee Grant To user_4 Recent Revoke update on Employee (emp_id, emp_name) From user_5 Recent Revoke Create Table, Create Rule From user_6 V database backup and restore Database Backup and restore SQL Server Data security is an important means to prevent accidents, the database administrator must produce regular and frequent database backup. Once the system failure, data can be timely recovery. First, the basic concept 1. Transaction Processing and log Services to use SQL Server database to track all changes. SQL Server module is the work of the Panel. Consist of one or more of a success or failure of T_SQL sentence as a whole. Each database has its own log, chart syslogs, automatically record each user's log of each branch, each catering services it enough information to ensure that data can be restored. 2. Checkpoint (Checkpoint) Server when updating data? -- At the check points. Send a check point on the server : (1) update the data; (2) recorded in the log under inspection point mark. All checkpoints can be "dirty pages," writes database equipment. "Dirty pages" refers to the last checkpoint, in memory changes, but no changes in the disk page. SQL Server automatically checkpoint mechanism to guarantee the completion of the Panel was revised to show a pattern of the data page from the buffer memory database wrote equipment. Second, database backup If the media hardware failure (such as disk damage), if and only if I had made a backup of the database and the log can resume database. : Do not use a copy of the database system equipment, and a copy to load the SQL Server database will lead to substantial damage. Backup types : Full backup () -- Backup incremental backup log business Note : (1) Only log on separate equipment required for incremental backup; (2) would cut back log, log, backup of the contents since the affair since the last backup. (3) prior to launch a backup server backup, and the best equipment to establish dumps. Grammar order : Dump database name database Store equipment to name / physical file name Dump transaction database name {with {truncate_only|no_log} Store equipment to name / physical file name [With No_truncate] No_log Truncate_only and delete option for business without making copies. Truncate_only disconnect log; Log in business use no_log completely full, it does not establish a database to check points. Log option will lose two. When using these two parameters, timely backup of the entire database. No_truncate copy log log but does not disconnect, in the event of the use of the media wrong choice. Graphical interface option with the order parameters of the relationship : (1) dump transaction (2) : : dump transaction with no_truncate (3) : : dump transaction with truncate_only (4) : : dump transaction with no_log Third, database recovery Loading load backup to the existing database using database can be used to create a database dumps, or may not. Grammar : Store database from the database who were loading equipment / physical file name Store name from the database transaction were loading equipment / physical file name ． Use : For database backup restoration And log data were stored in a database in two separate disks, the normal operation of the backup plans are implemented, the daily 17:00 implementation of the entire database backup, the daily 10:00, 12:00, 14:00, 16:00 incremental backup spots : 周一17:00磁带1（100M）周二10:00磁带2（30M）周二12:00磁带3（30M）周二14:00磁带4（30M）周二16:00磁带5（30M）周二17:00磁带6（30M） DumpdatabaseDumptransactionDumptransactionDumptransactionDumptransactionDumpdatabase 若数据磁盘在周二的下午六点损坏，可以采用如下步骤恢复数据库： （1）使用dump transaction with no_truncate获得当前的事务日志转储，磁带7； （2）使用load database转载最新的数据库转储，磁带6；（offline） （3）使用load transaction提交最新的事务日志转储，磁带7； （4）使用online database把数据库状态设置为online。 若数据磁盘在周二的下午4:50损坏，恢复过程如下： （1）使用dump transaction with no_truncate获得当前的事务日志转储，磁带7； （2）使用load database转载最新的数据库转储，磁带6；（offline） （3）使用load transaction依次装载磁带2、3、4、5上的事务日志； （4）使用load transaction提交最新的事务日志转储，磁带7； （5）使用online database把数据库状态设置为online。 四、制定备份与恢复的策略 由于事务日志在恢复数据库中的特殊作用，应定期备份数据库及其事务日志，而且事务日志的备份要更频繁一些。如：数据库每周备份一次，事务日志每天备份一次。 第六讲 数据库与T-SQL语言 一、关系模型的基本概念 关系数据库以关系模型为基础，它有以下三部分组成： ●数据结构——模型所操作的对象、类型的集合 ●完整性规则——保证数据有效、正确的约束条件 ●数据操作——对模型对象所允许执行的操作方式 关系（Relation）是一个由行和列组成的二维表格，表中的每一行是一条记录（Record），每一列是记录的一个字段（Field）。表中的每一条记录必须是互斥的，字段的值必须具有原子性。 二、SQL语言概述 SQL（结构化查询语言）是关系数据库语言的一种国际标准，它是一种非过程化的语言。通过编写SQL，我们可以实现对关系数据库的全部操作。 ●数据定义语言（DDL）——建立和管理数据库对象 ●数据操纵语言（DML）——用来查询与更新数据 ●数据控制语言（DCL）——控制数据的安全性 T-SQL语言是Sybase对SQL92标准的一种扩展，主要在它的基础上增加了三个方面的功能：自己的数据类型/特有的SQL函数/流程控制功能 T-SQL中的标识符使用说明： （1）标识符由1-30个字符或数字构成，但首字符必须为字母。临时表的表名以#开头，长度不能超过13个字符。 （2）数据库对象的标识方法举例 database.owner.tablename.columnname 执行远程存储过程： EXEC server.db.owner.proc_name 当执行语句在批处理的句首时，EXEC可以省略。 三、Sybase的数据类型 在创建表或声明局部变量时，必须使用Sybase系统预定义类型。 1．字符类型 Char(n) VarChar(n) 2．数值类型 整数类型——Integer SmallInt TinyInt 浮点类型——Real Float Number[P,S] Decimal[P,S] 货币类型——Money SmallMoney 3．日期/时间类型 Datetime SmallDatetime 两者时间部分的精度不同，前者精确到分，后者精确到1/30秒。 4．文本和图像类型 Text Image 5．二进制数据类型 Binary(n) VarBinary(n) 四、数据定义语言 用来定义数据库对象。数据库对象是Sybase用来存储数据的逻辑实体，主要有： 表（Table）、视图（View）、临时表（Temp Table）； 主键（Primary Key）、外键（Foreign Key）、索引（Index）、规则（Rule）、默认值（Default）； 存储过程（Stored Procedure）、触发器（Trigger） ●基本语法 下面给出创建主要数据库对象的语法： 1．表 创建表的基本语法是： Create table[database.[owner].]table_name (column_name datatype [default {constant_expression|user|null}] {[{identity|null|not null}]|[[constraint constraint_name] {{unique|primary key}[clustered|nonclustered] [with{fillfactor|max_rows_per_page}=x] [on segment_name] |references[[database.]owner.]ref_table [(ref_column)] |check(search_condition)}]}… 在建立大型的数据库时，可以考虑将创建表乃至其它数据库对象的过程写到一个文本里，当数据库系统出现问题时，在最坏的情况下，重建过程可以得到简化，也能比较好的对数据库的建设过程进行监视。 创建表的过程完成下列活动： ·定义表的每一列； ·定义列名和列的数据类型并指定列是否处理空值； ·指定列是否具有IDENTITY属性； ·定义列级的完整性约束和表级的完整性约束 上述过程可见，创建表的过程可以设定填充因子，将列置于段上，设计索引，外键等等。 2．索引 索引对查询性能的影响很大，要引起重视。 索引加速了数据检索，Adaptive Server有三类索引： ·复合索引——索引包含多列；当两列或多列由于它们的逻辑关系而作为整体被查询时可建立这种索引； ·唯一索引——索引列的值不允许重复； ·簇聚索引和非簇聚索引——簇聚索引强迫Server不断地对表中数据排序或重排序以保证表中数据的物理顺序和逻辑顺序的一致性，簇聚索引对范围查询性能影响极大；非簇索引没有这样的要求，非簇聚索引对修改操作有利。 何时建索引？ ·如果手动插入identity列，则创建唯一索引以保证不插入已经存在的值； ·经常被排序访问的列，即被列在order by子句中的列，最好对其建立索引以便Adaptive Server能充分利用索引顺序的优点； ·如果列经常用手连接，则可对列建立索引，这样系统能更快地执行连接； ·包含主键的列一般都有簇聚索引，尤其是当它频繁地和其它表的列相关联； ·经常被范围查询的列最好为其建立簇聚索引，一旦查询范围内的第一个值被发现，则随后的值在物理上一定相近。簇聚索引对单值查询并没有什么优点。 创建索引的基本语法： Create [unique][clustered|nonclustered]index index_name On [[database.]owner.]table_name (column_name[,column_name]…) [on segment_name][with consumers=x] 上述语法包含了这样的暗示：将簇聚索引和它的基表分离在不同的段上；段是逻辑概念，但段可以位于不同的物理设备上，也即将簇聚索引和基表物理上分开。 这是不允许的，我们将在后面讨论设备、数据库、段、表分区时作详细讨论。 3．键（key） 理解键是理解关联的关键。 键和索引往往是一回事。键的意义在概念上，键用于参照完整性约束。 主键是表的单值列的集合，主键通过在放置它们的表上创建一个单值索引来实现其单值性的。实际上主键是作为标志表的标志符而存在的，一旦主键确定，则由该主键就确定了的表也就确定了。 外键是和其它表中的主键相关的列，主键和外键的关系确定了外键的值域，该值域即为相应主键的取值范围。这样就从理论上强制实现了表与表之间的参照完整性。 前面创建表的语法里包含了创建键的成分。也可以通过其它途径创建主键和外键。 ◇Unique约束和Primary key约束的区别 Unique约束和Primary key约束用来保证同一表中指定的列上没有重复值，这两个约束都产生唯一索引确保数据一致性，默认情况下，Unique约束产生唯一的非聚集索引，Primary key约束产生唯一的聚集索引。Primary key约束比Unique约束严格：Primary key列不允许有空值，Unique列允许有空值。 4．视图 视图是查看多表中数据的方法，视图从基表派生，它并非物理存在，而是逻辑表；视图也系统提供管理表的一种安全机制。视图使得用户集中精力在感兴趣的数据集上。 创建视图的语法： create view [[database.]owner.]view_name [(column_name[,column_name]…)] as select [distinct] select_statement [with check option] 有distinct关键字的视图不能更新。当视图涉及关联时，定义视图要小心，这时是对多表操作，完整性显得很重要。 五、数据操纵语言 1．Select语句 基本语法： SELECT[all|distinct]字段列表 [into表名] [from表名] [where条件表达式] [group by [all]字段列表] [having筛选表达式] [order by 字段列表[asc|desc]] [compute聚集函数列表[by字段列表]] 注意：Select语句中的子句必须按照上述顺序使用。也就是说，若该语句包括一个group by子句和一个order by子句where，group by子句必须放在order by子句之前。 Having子句类似于where子句，不同之处有两点：（1）Having子句必须结合group by子句使用；（2）where子句不能用聚集函数，而Having子句可以。 下面通过实例来对Select的通常用法加以介绍。 例1：选择所有的列，语法为select * from table_list 如：select * from publishers 例2：选择指定的列，语法为 select column_name[,column_name]… from table_name 如：select pub_id,pub_name from publishers 例3：重命名查询结果中的列，语法为 select column_heading= column_name from table_name 如：select Publisher=pub_name,pub_id from publishers 例4：select列表中的计算值，可以对select列表中的数值数据进行计算，下面列出了算术运算符。 符号运算 +加 -减 /除 *乘 %取模 如select title_id,total_sales,total_sales*2 from titles 例5：使用distinct消除重复的查询结果 可选的关键词消除select语句的结果中的重复行。若不指定distinct，缺省值为all，将检索出包含重复行的所有行数据。 如：select distinct au_id from titleauthor 例6：选择行——where语句 select语句中的确切指定要检索哪些行的准则，其一般格式为： select select_list from table_list where search_conditions where子句中的搜索条件（或称限制）包括： ·比较运算符（＝，＜，＞，!=等＝ 如：where advance*2＞total_sales*price ·范围（between和not between） 如：where total_sales between 5000 and 10000 ·列表（in和not in） 如：where state in(“CA”,”IN”,”MD”) ·匹配字符（like和not like） 如：where phone like “0535%” ·未知值（is null和is not null） 如：where advance is null ·以上各项的组合（and, or） 如：where advance＜5000 or total_sales between 500 and 1000 例7：用集合函数小结查询结果 集合函数用特定列的数据来计算小结值。 集合函数结 果 Sum([all|distinct]expression)数值列中（不重复）值的总和 Avg([all|distinct]expression)数值列中（不重复）值的平均 count([all|distinct]expression)列中（不重复）非空值的数目 Count(*)选定的行数 Max(expression)Expression的最大值 Min(expression)Expression的最小值 如：select avg(advance),sum(total_sales) from titles where type=”as” select count(*) from titles select avg(distinct price) from titles select max(price) from books 例8：分组组织查询结果——group by 子句 group by 子句用在select语句中将一张表分成若干组。 如：select type, advance from titles group by type 例9：选择分组数据——having子句 having为group by 子句设置条件，与where为select语句设置条件一样。Having搜索条件与where相同，但having可包括集合函数，而where不能包括。 下列语句使用带集合函数having子句的例子。它把title表中的行按类型分组，但去掉了那只包含一本书的分组。 Select type from titles group by type having count(*)>;1 下面是一个不带集合函数的having子句的例子。它把title表中的行按类型分组，但去掉了那些不以字母“p”开头的类型。 Select type from titles group by type having type like “p%” 例10：查询结果排序——order by子句 Order by子句允许按一列或多列对查询结果排序。每个排序可以是升序的（asc）或降序的(desc)。若不特别指明，则按升序进行。下列查询返回按pub_id排序的结果： Select pub_id,type,title_id from titles order by pub_id 例11：连接——从多张表中检索数据 连接两张或两张以上的表是这样一个过程：比较指定字段中的数据，根据比较结果用符合条件的行组成一张新表。 举例： select publishers.pub_id,publishers.pub_name,authors.* from publishers,authors where publishers.city=authors.city 例12：分组计算子句 Compute是Sybase对SQL标准中Group子句的扩充，可以将其看作带聚集计算的Group子句。例如： Select type,price,advance From titles Order by type Compute sum(price),sum(advance) by type 2．Insert语句 用Insert命令向数据库中添加行有两种方法：使用关键词values或使用select语句。 Insert语句的基本语法为： Insert[into]表名[（字段列表）] {values（值列表）|select_statement} 举例：insert into publishers values(‘1622’,’Jardin,Inc.’,’Camden’,’NJ’) Insert into publishers(pub_id,pub_name) values(‘1756’,’The Health Center’) Insert authors select * from newauthors Insert authors(au_id,address,au_lname,au_fname) Select * from newauthors 3．Delect语句 Delect可以对一行或多行进行操作。 Delect语句的基本语法为： Delect 表名 [from 表名列表] [where条件表达式] 举例：Delect publishers where pub_name=”Jardin,Inc.” Delect titles From authors, titles Where titles.title_id=authors.title_id 4．Update语句 可以使用Update命令来改动表中的单个行、一组行或所有行。 Update语句的基本语法为： Update表名 Set column_name1={expression1|null|(select_statement)} [,column_name2={expression2|null|(select_statement)}] [……] [from 表名列表] [where 条件表达式] 举例： update authors set_au_lname=”Health”,aufname=”Goodbody” where au_lname=”Bloth” update titles set total_sales=total_sales + qty from titles,sales where titles.title_id=sales.title_id 六、Sybase预定义函数 1．聚集函数 sum([all|distinct]表达式) avg([all|distinct]表达式) count([all|distinct]表达式) count(*) max(表达式) min(表达式) 2．字符串函数 upper(字符表达式) lower(字符表达式) char(整型表达式) char_length(字符表达式) ltrim(字符表达式) rtrim(字符表达式) …… 3．数学函数 abs(精确小数型表达式) floor(精确小数型表达式)求小于或等于给定表达式值的最大整数（取底） rand([整数型] round(精确小数型表达式,整数) sign(精确小数型表达式) power(精确小数型表达式,整数幂) …… 4．日期函数 getdate() datepart(日期部分，日期) datediff(日期部分，日期1,日期2) dateadd(日期部分，数值表达式，日期) 5．类型转换函数 convert(数据类型，表达式[，格式]) 6．系统函数 db_name([数据库ID]) host_name() isnull(表达式1,表达式2) …… 七、数据控制语言 用来控制数据的安全性，如权限控制语句GRANT和REVOKE等。 第七讲 数据库编程基础 一、批处理 SQL Server可以处理作为一批而提交的多个SQL语句，既可以是交互式的，也可以是一个文件。批处理SQL语句由批结束标志终止，该标志指示SQL Server从前面开始执行该批处理语句，对于独立的SQL实用程序isql而言，其批结束标志为单独占一行的“go”。 举例：选择表title及表authors的行数 select count(*) from titles select count(*) from authors go 二、流程控制语言 1．变量声明与赋值 全局变量由系统预定义，以符号@@打头。 局部变量声明使用Declare语句，这个变量必须以符号@开头，后跟一个标识符。 Declare @变量名 数据类型[，@变量名 数据类型，……] 变量赋值使用Select语句，未赋值的变量其值为Null。 举例： Declare @msg char(50) Select @msg=’How are you?’ Select @msg=emp_name from employee Where emp_id=12345678 2．SQL语句块 Begin Statement Block/*多个顺序执行的SQL 语句*/ End 3．条件语句 If 条件表达式 语句（块） Else 语句（块） 举例： if(select max(id) from sysobjects)<50 print ‘数据库里没有用户创建的对象‘ else select name,type,id from sysobjects where id>;50 4．循环语句 While 条件表达式 语句（块） ●两个特殊的循环控制语句： Continue 执行下一次循环 Break 退出当前循环 举例： While(select avg(price) from titles)>;$20 Begin Update titles set price=price/2 If(select avg(price) from titles)<$40 Break Else Continue End 5．其它控制语句 ◇Return语句——无条件结束当前过程，并可返回给调用者的一个状态值：Return[整数表达式] ◇Print语句 ◇RaiseError语句 ◇Waitfor语句 三、存储过程 存储过程是存储在服务器端的一类数据库对象，它实质上是一段用SQL语言编写的程序，它在服务器端预先经过编译，并确定出执行计划，因此与同样功能的批处理语句相比，它的执行速度较快。 基本语法： Create Procedure[owner.]过程名 [@参数名 数据类型[=默认值][Output]] [，@参数名 数据类型[=默认值][Output]] [……] AS Begin SQL语句（块） End 存储过程是数据库对象，和表、索引是一个级别的；是SQL语句和控制流语言的集合，存储过程在首次运行时被编译，并驻留在过程高速缓存的内存中，所以存储过程的招待非常快。存储过程可以带参数，可以调用其他过程，返回状态值，返回参数值，并且可以在远程SQL Server执行。可以在远程SQL Server执行对数据库设计有特别重要的意义。SQL Server提供的存储过程称为系统过程。 存储过程大大增强了SQL的能力、效率和灵活性，经过编译的存储过程极大地改善SQL语句和批处理的性能。 存储过程有很多优点： ●存储过程在第一次执行时编译，并存储在过程高速缓存的内存中。编译时系统对其进行优化，以选择最佳的路径来访问数据集中的数据，这种优化考虑了数据集的实际数据结构。因此存储过程大大提高了系统的性能。 ●存储过程可以跨服务器运行。这一点是通过触发器来实现的，当然，首先存储过程要能登录到该远程服务器。 ●应用程序也能执行存储过程，从而实现服务器和客户之间的协同作业。 ●存储过程减少了网络的交通。这是因为存储过程的文本存储在数据库里，调用存储过程时通过网络的只是存储过程的过程名。 ●利用存储过程可以提供一个附加的安全层。 如（该例子取自pubs2数据库）： Create proc titleid_proc(@title_id varchar(80)) As Begin Select @title_id=lower(@title_id)+”%” Select title,title_id,price form titles Where lower(title_id) like @title_id Return @@rowcount End 注意例子中的黑体部分，这实际上是一条赋值语句。该存储过程有返回值。 存储过程可以变得非常复杂。我们认为，创建存储过程还是要遵循“最简单就是最好”的原则。建议在创建存储过程时采用缩进风格，否则创建的存储过程三天之后连自己都看不懂。 需要对存储过程作些说明： ●Create procedure 语句不能和其他语句在同一个批命令里。 ●Create procedure 语句不能包括下列语句： use Create View Create default Create rule Create trigger Create procedure 不能使用use语句好理解，存储过程是针对数据库的，不能在一个数据库里访问另外的数据库。如果在存储过程里访问另外的数据库，则数据库表的参照完整性难于得到保障。 从另外几条语句看，在存储过程里一般不能创建新的数据库对象。但可以创建表和索引，以及和表相关联的键，表是临时表，在存储过程结束后不能看见创建的临时表；否则的话每运行一次存储过程就创建一个表，结果可想而知。 存储过程里不能创建一个对象，删除它；然后又在同一存储过程里用相同的名字创建新的对象。实际上，SQL Server在存储过程运行时而不是在编译时创建对象的。 ●如果存储过程调用另外的存储过程，则第二个存储过程可以调用在第一个存储过程里创建的对象。 ●存储过程包含的最多参数为255个，对存储过程里的局部和全局变量没有限制。 最后讨论一下系统存储过程。系统存储过程以sp_开头，当然用户创建的存储过程也可以以sp_开头；系统过程保存在sybsystemprocs数据库里。系统过程的使用有权限，如果打入系统过程名但没有出现预期的结果，要么是命令名错，要么是使用者没有该过程的权限。一般可通过系统管理员或数据库所有者对系统过程的execute授权。 系统过程繁多，大致有几类： a. 用户标志和授权。这一类的过程主要由于：增加、删除或报告在SQL Server上的登录，增加、删除或报告某数据库的用户、分组或别名等。这类过程有sp_addlogin,sp_adduser,sp_helpgroup,sp_dropuser等。 b. 远程过程的调用。这类过程用于：增加、删除或报告能存取本SQL Server的远程服务器；增加能从远程服务器上存取本SQL Server的用户名。这类过程有：sp_addremotelogin,sp_addserver,sp_dropserver等。 c. 数据定义和数据库对象。这类存储过程用于：连接和定义规则和缺省值，增加、删除或报告主码、外码和公共码；增加、删除或报告用户定义的数据类型。这类存储过程有：sp_bindfault, sp_bindrule, sp_help, sp_helpdb, sp_foreignkey, sp_helptext等。 d. 系统管理。这类存储过程用于：增加、删除或报告数据库及转储设备；报告锁；设置的数据库选择及用户正进行的进程；修改及报告配置变量；监控SQL Server的活动。这类过程有：sp_addumpdevice，sp_dropdevice, sp_helpdevice等。 四、触发器 触发器是一种用来保障参照完整性的特殊的存储过程，它维护不同表中数据间关系的有关规则。当对指定的表进行某种特定操作（如：Insert,Delete或Update）时，触发器产生作用。触发器可以调用存储过程。 创建触发器的语法： Create Trigger[owner.]触发器名 On [owner.]表名 For {insert,update,delete} As Begin SQL语句（块） End 定义一个好的触发器对简化数据的管理，保证数据库的安全都有重要的影响。触发器是针对表一级的，这就意味着，只有表的所有者有权创建表的触发器。 举例： 插入一个新行，必须保证外键与主键相匹配，触发器应该首先检查被插入行与主键表的连接。 以下的触发器对inserted表和titles表的title_id进行比较，这里假设正在给外键输入数据，没有插入空值，若连接失败，事务被回退。insert,update,delete Create trigger forinsertrigl On salesdetail For insert As If(select count(*) From title,inserted Where titles.title_id=inserted.title_id)!=@@rowcount Begin Rollback transaction Print “No,some title_id does not exist in titles.” End Else Print “Added! All the title_id is exist in titles.” 在本例中，@@rowcount代表添加到salesdetail表的行数，这也是添加到inserted表中的行数。通过连接表titles和表inserted来检测所有添加到salesdetail的title_id是否在titles中存在。若所连接的行数（count(*)）与@@rowcount不同，由有一个或多个插入不正确，整个事务被取消。 触发器的限制： ●一个表最多只能有三个触发器，insert,update,delete ●每个触发器只能用于一个表 ●不能对视图、临时表创建触发器 ●Truncate table能删除表，但不能触发触发器 ●不能将触发器用于系统表 合理地使用触发器对性能的影响是正面的。在设计和使用触发器时，经常地用sp_depends命令了解对象所关联的触发器是有好处的，该命令能列出触发器影响的所有对象、表和视等。 在定义几类数据库对象的时候，对存储过程、索引和触发器要给予特别的注意，尤其存储过程，它设计的好坏对数据库性能的影响很大。 说明：Sybase触发器使用的两个测试表：Deleted表和Inserted表，它们都是临时表，其结构与触发器的基表结构相同，用来存放与修改相关的数据行。 五、游标 1．游标的概念 游标是指向查询结果集的一个指针，它是一个通过定义语句与一条Select语句相关联的一组SQL语句。游标包含两方面的内容： ●游标结果集：执行其中的Select语句所得到的结果集； ●游标位置：一个指向游标结果集内的某一条记录的指针 利用游标可以单独操纵结果集中的每一行。游标在定义以后存在两种状态：关闭和打开。当游标关闭时，其查询结果集不存在；只有当游标打开时，才能按行读取或修改结果集中的数据。 2．使用游标 一个应用程序可以使用两种类型的游标：前端（客户）游标和后端（服务器）游标，它们是两个不同的概念。 无论使用哪一种游标，都需要经过如下几个步骤： ●定义游标 ●打开游标 ●从游标中操作数据 ●关闭游标 下面讲述的是后端（服务器）游标。 （1）定义游标 在使用游标之前必须声明它。声明指定定义游标结果集的查询。通过使用for update或for read only关键词将游标显式定义成可更新的或只读的。 Declare cursor语法为： For select_statement [for{read only|update[of colum_name_list]}] 举例： declare pubs_crsr cursor for select pub_name,city,state from publishers for update of city,state （2）打开游标 open的语法为： open 游标名 在声明游标后，必须打开它以便用fetch,update,delete读取、修改、删除行。在打开一个游标后，它将被放在游标结果集的首行前，必须用fetch语句访问该首行。 （3）从游标中读取数据 在声明并打开一个游标后，可用fetch命令从游标结果集中获取数据行。 Fetch的语法为： Fetch 游标名[into 变量列表] 举例：fetch pub_crsr into @name,@city,@state SQL Server在每次读取后返回一个状态值。可用@@sqlstatus访问该值，下表给出了可能的@@sqlstatus值及其意义。 值意 义 0Fetch语句成功 1Fetch语句导致一错误 2结果集没有更多的数据，当前位置位于结果集最后一行，而客户对该游标仍发出Fetch语句时。 若游标是可更新的，可用update和delete语句来更新和删除行。 删除游标当前行的语法为： delete[from]表名 where current of 游标名 举例：delete from authors where current of authors_crsr 当游标删除一行后，SQL Server将游标置于被删除行的前一行上。 更新游标当前行的语法为： update 表名 set column_name1={expression1|NULL|(select_statement)} [,column_name2={expression2|NULL|(select_statement)} [……] where current of 游标名 举例： update publishers set city=”Pasadena”,state=”CA” where current of pubs_crsr （4）关闭游标 当结束一个游标结果集时，可用close关闭。该语法为： close 游标名 关闭游标并不改变其定义，可用open再次打开。若想放弃游标，必须使用deallocate释放它，deallocater的语法为： deallocater cursor 游标名 deallocater语句通知SQL Server释放Declare语句使用的共享内存，不再允许另一进程在其上执行Open操作。 六、事务——维持数据一致性和恢复 1．事务的定义 事务提供了一种将T-SQL语句分组的方法，从而使它们能够被当成一个单元来处理：组中所有语句或都执行，或都不执行。 事务是确保一个或多个SQL语句的集合不被当成单一工作单元处理的机制，SQL Server自动将所有数据修改命令，包括单步改变请求，作为事务处理，缺省时，每个insert、update、delete语句被当成一个事务处理。 2．事务的作用 事务使SQL Server能保证： ·一致性——同时发生的查询或改变请求彼此不冲突，用户不能对正处于改变过程的数据进行查看或操作。 ·恢复——在系统失效时，数据库的恢复是完全的和自动的。 3．使用事务 a. 开始和确认事务 begin transaction和commit transaction可将任意数目的SQL语句封装起来，这两名的简单语法为： begin transaction commit transaction b. 回退事务 在commit transaction命令提交前任何时候可取消或回退事务，该命令的简单语法为： rollback transaction 4．检查事务状态 全局变量@@transtate记录了事务当前的状态。在执行一个语句后SQL Server通过记录所有事务变化来确定返回何种状态。 @@transtate可包含下列值： 值意 义 0事务进行中：一个显式或隐式事务有效；上一语句执行成功 1事务完成：事务完成并提交其变化 2语句异常中止：上一语句异常终止；对事务无影响 3事务异常中止：事务异常中止并回退所有变化 举例：在事务中，可在一个语句（如insert）后使用@@transtate确定该语句成功或失败对事务的影响。commit transaction Begin transaction Insert into publishers(pub_id)values(‘9999’) (1 row affected) select @@transtate ………………… 0 commit transaction select @@transtate ………………… 0 (1 row affected) Sybase基础知识 温故而知新！ :em11: [ 关闭窗口 ]