17th Chapter-sql Programming (i) (1)

SQL language, as a general structure query language in relational database management system, has been adopted by many database management systems, such as Oracle, Sybase, Informix and other database management systems, which support SQL language. Delphi is compatible with the SQL Language database management system, when using Delphi to develop database applications, we can use SQL language programming, support SQL programming is an important feature of Delphi, This is also the embodiment of Delphi as a powerful tool for database application development is an important symbol.

17.1 SQL Language Introduction

The history of 17.1.1 SQL

In the early 70, E.e.codd first proposed a relational model. In the middle of the 70, IBM developed the SQL language in the development of system R relational database management systems, the earliest SQL language (called SEQUEL2) was published in the November 1976 IBM Journal of R&d. The 1979 Oracle company first provided commercial SQL,IBM company in the DB2 and SQL/DS database system also implemented SQL.

In October 1986, ANSI adopted SQL as the standard language for relational database management systems (ANSI X3.135-1986), followed by International Standards Organization (ISO). In 1989, ANSI adopted the SQL standard language of the relational database management system defined in the ANSI x3.135-1989 report, called ANSI SQL 89, which replaces the ANSI x3.135-1986 version. The standards are adopted by the following organizations:

International Organization for Standardization (ISO), report "Database Language SQL with Integrity enhancement" for ISO 9075-1989

US federal government, released at the information Processing Standard publication (FIPS PUB) 127

Currently, all major relational database management systems support some form of SQL language, and most databases intend to comply with ANSI SQL89 standards.

Advantages of 17.1.2 SQL

The wide adoption of SQL is an illustration of its merits. It benefits all users, including application staff, DBA administrators, and end users.

(1) Non-procedural language

SQL is a non procedural language because it processes one record at a time, providing automatic navigation to the data. SQL allows a user to work on a high-level data structure, instead of manipulating a single record to manipulate the recordset. All SQL statements accept the collection as input and return the collection as output. The SQL Collection attribute allows the result of one SQL statement to be entered as another SQL statement.

SQL does not require the user to specify how to store the data. This feature makes it easier for users to focus on the results they are getting. All SQL statements use the query optimizer, which is part of an RDBMS that determines the fastest way to access the specified data. The query optimizer knows what indexes exist, where they are appropriate, and the user never needs to know whether the table has an index or not, and what type of index the table has.

(2) A unified language

SQL can be used for all users of the DB activity model, including system administrators, database administrators, application programmers, decision support system personnel, and many other types of end users. Basic SQL commands can be learned in little time, and the most advanced commands can be mastered within a few days.

SQL provides commands for many tasks, including:

Querying data

Inserting, modifying, and deleting records in a table

Create, modify, and delete data objects

Control access to data and data objects

Ensure database consistency and integrity

The previous database management system provided a separate language for each of these operations, while SQL Unified all the tasks in one language.

(3) is the common language of all relational databases

Because all major relational database management systems support the SQL language, users can transfer the ability to use SQL from one RDBMS to another. All programs written in SQL can be ported.

Application of 17.2 tquery parts in SQL programming

In Delphi is through tquery parts to achieve the support of the SQL language, that is, using Delphi to develop database applications, the only way to use SQL language to manipulate the data in the database is through the tquery parts. The Tquery component occupies an absolutely important position in Delphi programming with SQL language. In the database application developed using Delphi, you can access the following three databases using SQL language:

Tables in a Paradox or dBASE database

When accessing data in these desktop database systems, you can use only a subset of the SQL statements in the ANSI standard SQL language, which includes: Select, Insert, UPDATE, and DELETE statements, or local SQL statements. For more information, see Appendix, "Use of local SQL statements."

A database in a local interbase database server

SQL statements that are supported in the InterBase database can be used in Delphi. For the syntax and limitations of SQL statements in InterBase, refer to the InterBase language Reference.

Databases in the remote database server

Of course this requires that the corresponding SQL Link must be installed in Delphi. As long as the SQL statements supported by the DBMS on the database server are available in Delphi. For syntax and restrictions, see the documentation for the relevant database management system.

It is worth mentioning that Delphi also supports heterogeneous queries, that is, you can query the same or different types of database tables in multiple database servers at the same time, for example, the query data can be from tables in the Oracle database and tables in the Sybase database, or tables in many other databases.

Use of 17.2.1 tquery parts

The Tquery part is a dataset widget that has a lot in common with ttable parts on the data access page on the Delphi parts palette, which we introduced in more detail in the 15th chapter, "Application and programming of data access parts". The Tquery component occupies a very important place in SQL programming. It realizes the Delphi to the SQL Language support, in Delphi Development's database application, the SQL statement is passes through the Tquery part to accesses the database system the database engine, by the Database engine concrete execution SQL statement, realizes to the data operation, Instead of passing it to the BDE in Delphi, the BDE enforces specific SQL actions.

We already know that the ttable widget already has a powerful capability to access the database. Ttable parts can access various database systems through Delphi's built-in BDE, while tquery parts provide features that are not available in ttable parts:

Multiple table join query

Complex nested query (select contains a select subquery)

Explicitly require actions in the SQL language

Because the Ttabel part cannot use the SQL language, and the tquery part can use the SQL language, the Tquery component also has powerful relational query capabilities. This, of course, makes the database application itself more complex.