Delphi perfect classic-Chapter 2

Chapter 2 Introduction to database concepts and SQL

A relational database is a data structure in the two-dimensional table format. It is accessed Based on associations between data tables.

I. Basic concepts of databases

1. database structure:

The organizational structure of a database is field, record, and table in ascending order ).

2. Open Database Connection protocol (ODBC)

The interface between the ODBC provider and the database. Using ODBC as an interface for accessing databases, you can easily access different databases. Of course, such access must be completed through the ODBC driver, that is, the database vendor must provide ODBC drivers that support OS.

ODBC architecture: it consists of four elements: application, driver manager, driver, and data source.

There are three tabs related to the data source in the ODBC window for you to set "data source name ". They are:

User DSN: it can only be used by the local computer and only for the current user.

System DSN: it can only be used by local computers. It can be used by users with access permissions on the system.

File DSN: the data source set on this tab can be used by all users with the same driver installed. This data source is not limited to a user or a computer. The file data source does not record the data source we set by the data source name, but is recorded by the file name.

Although these three objects are different, the setting method is almost the same.

3. SQL Explorer

Databaseexplorer of Delphi has some different functional features in different Delphi versions. In the Delphi Enterprise version, the name is "SQL Explorer": You can directly access non-relational databases (such as dbase and paradox), access the supported database through ODBC, or any relational database that supports SQL. In the Delphi Professional version, the name is "Database Explorer", which only supports access to non-relational databases and the supported databases through ODBC.

Ii. Structured Query Language (SQL)

1. SQL Syntax: It is case-insensitive, but it is recommended that the SQL keywords be expressed in uppercase and not in lowercase or in combination.

In SQL naming rules, data packets and fields are naming rules. The basic naming principles are as follows:

Data Table Name: do not name it Chinese or empty. When a data table name or field name contains spaces or keywords, you must use [] to extend the name. Otherwise, an SQL syntax error occurs.

Field naming: Do not use Chinese characters, spaces, or special characters. Common types:

Ø char (n): string type with fixed length. It means that no matter whether the length of the string reaches N, it will occupy n Bytes of space (fill the remaining space with spaces ).

Ø varchar (n): variable length. The occupied space is the actual size without spaces.

Because the char length is fixed, the processing speed is faster. However, it is troublesome to remove spaces using functions such as trim.

Integer: an integer in the unit of 4 bytes. The first bit is used to record positive and negative numbers.

2. SQL commands: Data Definition Language (DDL) and data operation language (DML ). First, describe the symbols in the Syntax:

[]: Indicates that it can be omitted.

|, {}: When multiple options can only be selected, they are separated by "|. If one or more items can be omitted, use [] to enclose them; if not, use {} to enclose them.

[,…] : In addition to indicating that the project can be omitted, it also indicates that if the project exists, its syntax is the same as that of the previous project.

3. SQL statement:

1) Create statement: Create an index for a data table or table.

Create Table Syntax:

Create Table table_name

{

Column_definition [null | not null] [primary key | unique] [,

Column_definition [null | not null] [primary key | unique] [,…] ]

}

Column_definition: Format: "field name type (size )".

[Primary key | unique]: the primary index is a unique value, but the unique value is not necessarily a primary index.

Create tablefriend_table

{

Id int not null primary key,

Name char (12) not null,

Phone char (15) not null,

Age int null

}

Create a data table index:

Create index can create secondary indexes (non-primary indexes) in an existing data table ). Syntax:

Create [unique] indexindex_name on table_name

{

Column_name

}

[Unique]: sets that the index creation field is unique.

2) alter table statement: provides the ability to modify data tables.

Alter tabletable_name

{Addcolumn_definition [,…] | Drop column_name [,…] }

Alter tablefriend_table add sex char (6), address char (50)

Alter tablefriend_table drop sex, address // Delete the sex and address Fields

3) Drop statement

You can delete the index of a data table or the entire data table.

Drop TABLE statement: Drop tabletable_name [,…]

Drop index statement: (access can only delete one index name at a time, while SQL server can delete multiple index names at a time ):

Drop indexindex_name on table_name

Drop indextable_name.index_name [,…]

4) select statement

Select [predicate] {* | column_list} from tableexpression [,…]

[Where clause]

[Group byclause]

[Having clause]

[Order byclause]

Predicate: A condition in all, distinct, and topn [percent] can be used to limit the query results to be returned. Where, "all" indicates that all data is to be displayed. "distinct" indicates that duplicate fields are not displayed if multiple records are identical (only query fields) in the query results; topn [percent] is the first N records or the first n percentage records that display field data.

{* | Column_list}: Query all fields or select a specified field. Column_list is "table_name. *" and "[table_name.] column_name ". If the field name is specified, the as keyword can be used together to set an "alias" for the query field to facilitate the display of the query results. For example, "select ID as number, name as name from table ".

Tableexpression: names of one or more data tables separated by commas.

Group by clause: You can group query results by clause.

Having clause: usually used with group by clauses. Having clauses can be used as Aggregate functions, while where clauses cannot be used as Aggregate functions.

Order by clause: sorts the result in ascending (ASC) or descending (DESC) Order.

SQL provides five standard Aggregate functions: AVG (), count (), max (), min (), and sum. AVG () takes the average value of all records in a certain field that meets the query conditions. Count () is used to obtain the number of records that meet the query conditions. Sum () is used to calculate the sum of values of a certain field that meet the condition.

Select into: adds query results including field names and field definitions to a new data table. Generally, this new data table does not exist. If it exists, it may overwrite or generate an error. Syntax:

Selectcolumn_name [,…] Into new_table_name from source_table_name [,…]

New_table_name: name of the new data table to be created to store the query results.

If two or more tables contain the same field name, the format of "table name. Field name" must be used.

5. Insert and update statements

Insert statement: adds one or more records to a data table.

Insert [into] table_name (column_name1 [, column_name2 [,…])

Value (value1 [, value2 [,…])

[Into]: some databases are allowed to be omitted, but access is not allowed.

When the field type is text, the field values must be enclosed in single quotes, and the Field Values are separated by commas. If no field is specified, the default value is automatically entered.

Insert into product source (product name, origin)

Value ('Grape milk ', 'heilongjiang ')

An Insert statement can only add one record at a time. To add more than one record at a time, you can use insert with select.

Insert [into] table_name [(column_name [,…])]

Select {* | columu_list} from tableexpression [,…]

Update is used to update existing records.

Updatetable_name

Set column_ref = new_value [, column_ref2 = new_value2 [,…]

[Where criteria]

Update product source

Set product name = 'premium _ '+ product name

Where origin = 'guangdong'

6. Delete statement

Delete [from] table_name

[Where criteria]

Iii. SQL advanced instructions

1. Union operation: in SQL commands, select statements can be used together with Union operations to query multiple data tables. That is, two or more data tables are obtained to connect each data record. The common condition indicates that the number of fields in each query result is the same.

Query1union [all] query2 [Union (all) queryn [,…]

Query1, query2 ...... Queryn: each query is a SELECT statement, and the number of fields in the query results must be the same.

ALL: If all is not set, only one duplicate data is listed in the query results. If all is added, the full number of duplicate data is displayed.

In select statements with union, there are special restrictions on the combination of orderby clauses: the order by clause can only be used in the last SELECT statement, in order to sort the entire query result.

Select contact name as guest name, 'suppliers' as relationship from supplier

Union

Select Manager, 'employees' from employee order by relation ASC

2. Join Operation

SQL commands can intersection two data tables to obtain matching records between two or more data tables for data merging between different data tables. Based on the data merging method, the intersection is divided into left join, right join, and inner join ).

Table1 [left | right | inner] Join Table2

On table1.field1compopr table2.field2

Compopr: Comparison operators, such as =, <>, <=,> =.

The left intersection is all records of Table 1 + records that meet the conditions of table 2.

The right intersection is all records in Table 2 + records in Table 1 that meet the conditions.

The internal intersection is the matching records in Table1 and Table2.

3. Special operators

In addition to general operators <, =, and so on, there are also special operators: Like, is, in, And, Or, ...... And.

① {Where | having} [not] column_name like match_string

[Not]: reverse query condition.

Match_string: Condition value. It must be enclosed in single quotes. Multiple characters: * is used in access and % is used in SQL Server. Characters or numbers in a specific range.

② Is: query which field values are null, or use not to query which field values are not null.

{Where | having} [not] column_name is [not] Null

[Not]: When both not are set, they are not set.

③ In: query the specified field value, whether it is one of the conditions in the condition column.

{Wher | having} [not] column_name in (value1 [, value2 [,…])

④... And: query whether the specified field value is within the configured condition range.

{Wher | having} [not] column_name between value1 and value2

⑤ And, or: And, or operation.

{Wher | having} [not] (bool_exp (and | or) bool_exp)

Bool_exp: A bool expression.

4. subquery: one query statement contains another SELECT statement (the SELECT statement is expanded in parentheses ). Subqueries can be found in the list of query fields in the SELECT statement or in the where and having clauses.

{Where | having} column_name compare (subquery)

Or {where | having} column_name [not] In (subquery)

Or {where | having} [not] exists (subquery)

Compare: common comparison operators, such as = and>.

Subquery: select statement in the subquery.

The first syntax is mainly used when the returned value of a subquery is a single data record of a single field, and we use the returned data as a comparison value in another query condition.