according to the complete understanding of access, datamodule 4.adoquery2.sql.add ('select library card number, password FROM[user]WHERE (library card number =:tt)');

datamodule4.adoquery2.parameters[1].value:=username;

datamodule4.adoquery2.open;

it is always safe to call the Close method when setting SQL properties for TQuery or TADOquery parts. If TQuery or TADOquery parts have been Closed, calling the close method will have no effect. When setting a new SQL command statement for an SQL attribute in an application, you must call the Clear method to Clear the existing SQL command statement in the SQL attribute. If you don't call the clear method, you can call the Add method to set the SQL command statement in the SQL attribute. Then the newly set SQL command statement will be appended to the existing SQL command statement, and unexpected query results will often appear when the program runs, even the program can't run.

it is important to note here that in general, the SQL attribute of a TQuery or TADOquery component can only contain a complete SQL statement, and it is not allowed to be set as multiple SQL statements. Of course, some database servers also support setting multiple SQL statements in the SQL properties of TQuery or TADOquery components. As long as the database server allows this, we can set multiple SQL statements for the SQL properties when programming.

after setting the attribute values of SQL attributes for TQuery or TADOquery components, that is, after writing an appropriate SQL program, there are many ways to execute the SQL program.

in the design process, after setting the SQL attribute of TQuery or TADOquery component, set the value of its Active attribute to True, so that the SQL program in the SQL attribute can be executed. If there are data browsing components (such as TDDGridTDBEdit) connected to TQuery or TADOquery component in the application, the execution results of the SQL program will be displayed in these data browsing components.

during the running of the application, the SQL program in its SQL properties can be executed by calling the Open method or ExecSQL method of TQuery or TADOquery component. The Open method is different from the ExecSQL method. Open method can only be used to execute SQL query statement (Select command) and return a query result set, while ExecSQL method can also be used to execute other commonly used SQL statements (such as INSERT,UPDATE,DELETE and other commands), such as:

Query1.Open (which will return a query result set)

If the Open method is called and there is no query result, an error will occur. At this time, the ExecSQL method should be called instead of the Open method. For example:

Query1.ExecSQL (no result returned)

Of course, when designing an application, the programmer can't be sure whether the SQL statement in the TQuery or TADOquery component will return a query result. In this case, Try…Except module should be used to design the program. Call the Open method in the Try part and the ExceSQL method in the Except part, so as to ensure the correct operation of the program.

for example:

try

query1.open

except

query1.exec SQL

end

two types of data can be obtained through Tquery or TADOquery components:

u "active" data

This kind of data is just like the data obtained through TTable components. Users can edit and modify these data through the data browsing part, and when the Post method is called or the focus leaves the current data browsing part, the user's modification of the data is automatically written back to the database.

u inactive data (read-only data)

Users cannot modify the data in it through the data browsing component. By default, the query result data obtained through the TQuery component is read-only. In order to obtain the "active" data, the RequestLive attribute value of the Tquery or TADOquery component must be set to True in the application, but the "active" data can not be obtained under any circumstances (by setting the subordinate value of RequestLive to True). In order to obtain the "active" data, in addition to setting the RequestLive property of the TQuery part to True, the corresponding SQL command must meet the following conditions.

in the case of local SQL query, in order to get an updatable data set, the limitation of SQL statement is:

nQuery can only involve a single table

The NSQL statement can't contain the ORDERBY command

The NSQL statement can't contain the aggregation operator SUM or AVG

nThere can't be a calculated field in the Selected field list

nIn the WHERE part of the select statement, only the comparison operation of field values and constants can be included. ,< ,> =,< =。 There can be union AND intersection operations between the comparison operations: AND and OR

When querying the database tables in the database server through SQL statements:

nQuery can only involve a single table

nSQL statements can't contain the ORDERBY command

nSQL statements can't contain the aggregation operator SUM or AVG operations

In addition, if querying the tables in Sybase database, there can only be one index in the queried tables.

if TQuery or TADOquery components are required to return an "active" query result data set in an application, but the SQL command statement does not meet the above constraints, BDE can only return a read-only data set for the SQL query of the local database. For SQL queries in the database server, only the wrong code can be returned. When a Tquery or TADOquery component returns an "active" query result dataset, the value of its CanModIfy property is set to True. < p p>§3.4MSSQLServer Brief Introduction

SQLServer is a background database management system, which is powerful and easy to operate, and is increasingly loved by database users. More and more development tools provide interfaces with SQLServer. SQLServer is a relational database management system, which was originally developed by Microsoft, Sybase and Ashton-Tate. The first OS/2 version was launched in 1988. After the launch of WindowsNT, Microsoft and Sybase parted ways in the development of SQLServer. Microsoft transplanted SQLServer to WindowsNT system, focusing on the development and promotion of WindowsNT version of SQLServer.

SQLServer2111 is the latest version of SQLServer database management system introduced by Microsoft company. This version inherits the advantages of SQL server version 7.1, and at the same time, it adds many more advanced functions than it, and has the advantages of convenient use, good scalability and high integration with related software. It can be used across a variety of platforms, from laptops running MicrosoftWindows98 to large multiprocessor servers running MicrosoftWindows2111. MSSQLServer can not only be applied to the management of large and medium-sized databases, but also can develop desktop databases. In fact, the basic structure of SQLServer database processing adopts relational database mode. Nevertheless, I believe everyone can easily find that the database processing method in SQLServer uses object-oriented operation mode and spirit, that is to say, all the functions of SQLServer can be achieved based on some objects that have been established in the system, which is a rather OO (object-oriented) system structure.

SQLServer enterprise manager is the main management tool of SQLServer, which provides a user interface that complies with MMC standards, enabling users to:

define SQLServer instance groups.

register individual servers into a group.

configure all SQLServer options for each registered server.

create and manage all SQLServer databases, objects, logins, users and permissions in each registered server.

define and execute all SQLServer management tasks on each registered server.

interactively design and test SQL statements, batches and scripts by calling the SQL query analyzer.

wake up and call various wizards defined for SQLServer.

Chapter III Design Analysis of Library Management System

4.1 Analysis of Application Requirements

The library management system needs to meet the needs from three aspects, namely, book borrowers, librarians and librarians. The demand of book borrowers is to inquire about the books stored in the library, the personal borrowing situation and the modification of personal information; Library staff operate the borrowing and returning requirements of book borrowers, and at the same time form a borrowing or returning report for the borrowers to check and confirm; The functions of library administrators are the most complicated, including the management and maintenance of staff, book borrowers and books, the view and maintenance of system status, and the generation of books return reports.

The book borrower can directly check the books in the library. If the book borrower logs into the system according to his library card number and password, he can also inquire about his borrowing situation and maintain some personal information. Under normal circumstances, a book borrower should only inquire and maintain his own borrowing situation and personal information. If he inquires and maintains the borrowing situation and personal information of other book borrowers, he must know the library card number and password of other book borrowers. These are hard to get, especially passwords, so they not only meet the requirements of book borrowers, but also protect their personal privacy.

The library staff has the authority to modify the borrowing and returning records of books by book borrowers, so more consideration should be given to the staff logging in this module. In this module, library staff can add book borrowing records or book returning records for book borrowers, and print and generate corresponding reports for users to check and confirm.

The functions of library administrators are informative and require the highest data security and confidentiality. This function realizes the management and statistics of book information, borrower information and overall borrowing information, and the viewing and maintenance of staff and management information. Librarians can browse, query, add, delete, modify and count the basic information of books; Browse, query, count, add, delete and modify the basic information of book borrowers, browse, query and count the borrowing information of the library, but you can't add, delete and modify the borrowing information. This part of the function should be performed by the library staff. However, when deleting a basic information record of a book borrower, the borrowing record of the book borrower should be deleted in cascade. In addition, it should also have the function of generating and printing the book return report.

In this system, because there is no printer equipment for testing, the report printing is changed to report preview in advance.

design different users' operating rights and login methods

book query open to all users

borrowers maintain personal information of borrowers

borrowers check personal borrowing information

maintain personal passwords of borrowers

operate the database according to the borrowing situation and generate reports

operate the database according to the book return situation and generate reports

query and count all kinds of information

maintain. Information of personnel and administrators

Maintain information of borrowers

Handle the integrity of information

Generate reports for books with overdue loans

Figure 4-2 Summary of database application requirements of library management system

According to the above requirements analysis, some details are omitted (such as not considering user login; Maintenance of records), the following three-layer data flow diagram is obtained.

4.2 system function module division

the system function block diagram is shown in figure 4-11.

4.3 System Database Design

4.3.1 Conceptual Design

In the conceptual design stage, the designer looks at the data and processing requirements and constraints from the user's point of view, and produces a conceptual model that reflects the user's point of view. Then the conceptual model is transformed into the logical model. The concept design is separated from the design process, which makes the tasks of each stage relatively simple and greatly reduces the complexity of design, and is not limited by a specific DBMS.

conceptual design of database by ER method can be divided into three steps: first, design local ER patterns, then synthesize each local ER pattern into a global pattern, and finally optimize the global ER pattern to get the final pattern, namely the conceptual pattern.

(1) Design partial p>ER mode

Definition of entity and attribute:

Book (book number, book name, author, publishing house, publication date, remarks, price, quantity,)

Borrower (library card number, name, gender, ID card, contact number, password)

Identity (. A complete way is to investigate whether there is a connection between any two entity types in the local structure according to the results of demand analysis. If there is a connection, further determine whether it is 1:N, M:N, or 1:1, etc. It is also necessary to examine whether there is a connection within an entity type, whether there is a connection between two entity types, whether there is a connection between multiple entity types, and so on. Contact definition as shown in figure 4-5. The explanation is as follows:

u A borrower (user) can only have one identity, and one identity can be owned by multiple borrowers;

u A book can only belong to one book category (category), and a book category can contain multiple books;

u One user can borrow many different books, and a book can also be borrowed by many different users.

(2) design the global ER mode

institute.