Chapter SQL language (b)

2010-12-29  来源:本站原创  分类:Database  人气:122 

5.3.5 Improved Method of nested queries

1. Exporting table

SQL2 FROM clause allows the use of subqueries. If used in the FROM clause subquery, then give the results of the subquery from a table name and the corresponding column name.

2.WITH clause and temporary view

SQL3 allows the user to use the WITH clause defines a temporary view (ie sub-queries), as the start of the SELECT statement. The provisional view itself is defined SELECT statement.

5.3.6 base table join operations

The following are connected with the explanation and description of operation:

(1) connection type is divided into Inner and outer connecting the two. The connection (INNER JOIN) is equivalent connections, divided into left outer joins (LEFT OUTER JOIN), Right (RIGHT OUTER JOIN), full connectivity (FULL OUTER JOIN) of three. Connection type in the INNER, OUTER words do not write.

(2) join condition into three types:

① NATURAl: that the implementation of the natural connection between the two operations, namely, the relationship between the two as equivalent connections on public property, public property in the operation result only once.

② ON connection equivalent conditions: specifically listed in the corresponding property in which the two relations do the equivalent connection.

③ USING (A1, A2 ,..., An): similar to the NATURAL form, where A1, A2 ,..., An is the relationship between the two public properties, but not all public properties. The result of the connection, public properties A1, A2 ,..., An only once.

(3) If the connection operation is "INNER JOIN", not mentioned in the join condition, then this operation is equivalent to the Cartesian product, SQL2 to this operation is defined as "CROSS JOIN".

(4) If the connection operation is "FULL OUTER JOIN ON false", where the join condition is always false, then the operation of class more than the "external and" operation, but there are differences. This operation results between the two properties should all be included. This operation is defined as SQL2 the "UNION JOIN".

5.4 Data Update

5.4.1 Insert Data

Base table to insert data into SQL statement is INSERT statement in SQL3, there are the following four ways:

(1) unit into the group

INSERT INTO <base table name> [(<column name sequence>)]

VALUES (<tuple value>)

(2) into multiple groups

INSERT INTO <base table name> [(<column name sequence>)]

VALUES (<value >),(< tuple tuple tuple value >),(< value>) ... ..., (<tuple value>)

(3) insert the query results

INSERT INTO <base table name> [(<column name sequence>)]

<SELECT Query statement >

This statement can be a SELECT statement to query results into the one of the basic table.

(4) Insert a table

INSERT INTO <base table name> [(<column name sequence>)]

TABLE <base table name 2>

The above insert statement, insert the value of the property if the number, order and basic table structure exactly the same, then after the basic table (<column name sequence>) can be omitted, or to be listed in detail.

5.4.2 Data Deletion

SQL to delete the base table is deleted from the tuple, the syntax is as follows:

DELETE FROM <base table name>

[WHERE <conditional expression>]

DELETE statement can only be removed from a base table tuple. If you want to delete from multiple base table tuple to be written for each base table of a DELETE statement. WHERE clause can add WHERE clause of SELECT statement as complex conditions, can be nested, it can be the basic form of the compound from several conditions.

5.4.3 Data Modification

When the need to modify some tuples in base table column values, you can achieve with the UPDATE statement, the syntax is as follows:

UPDATE <base table name>

SET <column name> = <value expression> [, "the column name> = <value expression> ...] | ROW = (<tuple>)

[WHERE <conditional expression>]

The semantics is: modify the base table that satisfy the conditional expression of those tuples column value, modify the column values in the SET clause said. SET clause in the first format tuple is eligible to modify column values in the second format is an element of the group eligible to modify the value of each column.

5.5 view

5.5.1 the creation and revocation of view

In SQL, a data structure outside the model is the basic unit view (view), the view from a number of basic tables and (or) tables constructed from other views.

1. View to create

Create views available "CREATE VIEW" statement implementation. The syntax is as follows:

CREATE VIEW <view name "(<list sequence>)

AS <SELECT Query statement >

2. View of the revocation

Not in the view, you can use the "DROP VIEW" statement to remove it from the system. The syntax is as follows:

DROP VIEW <view name>

5.5.2 the operation of the view

Definition 5.1 If the view is only from a single base table selection, export projection operations, and the basic table contains the primary key, then such a view is called "the ranks of a subset of View", and update operations can be executed. Allows the user to update the view definition must be added in the "WITH CHECK OPTION" phrase.

5.6 Embedded SQL

In the main language used in the structure called the Embedded SQL SQL.

5.6.1 Embedded SQL implementations

SQL language used in two ways: one is to use the terminal interactive mode, known as interactive SQL; the other is embedded in the main language used in the program, known as embedded SQL.

Storage device is to use SQL database access, database, and the main language of information transfer between programs is achieved through the shared variable. These shared variables that use SQL's DECLARE statement, then SQL statements can refer to these variables. SQL and the shared variable will become the main language interface.

SQL2 regulations, SQL_STATE is a special shared variable, plays an SQL statement to explain the role of the state of implementation, it is a character from the 5 character array. When the same SQL statement is executed, the system automatically to SQL_STATE assigned zero value on the whole (the "00000"), indicates that no error has occurred; otherwise its value is nonzero, that statement is executed when the various error conditions.

5.6.2 The Use of embedded SQL

Program in the main language used in SQL statement has the following provisions:

1. In the program to distinguish between SQL statements and host language statements

All money must be added the prefix SQL statement identifies "EXEC SQL", and to "END_EXEC" as the statement marks the end. Embedded SQL statement format is as follows:

EXEC SQL <SQL Statement > END_EXEC

Identifies the end of the main languages in different is different.

2. To allow the embedded SQL statements reference the primary language of the program variables (called shared variables)

Allow embedded SQL statements reference the primary language of program variables. But there are two requirements:

(1) reference, these variables must be added before the colon ":" as a prefix logo, to show the variables in the database are different.

(2) these variables in the DECLARE statement to use SQL instructions.

3.SQL set approach with the main language of a single record of coordination between the treatment

With the cursor (cursor) on the SQL statement has the following four:

(1) cursor definition statement (DECLARE). The results of a query cursor is associated with a symbolic name, the cursor DECLARE statement with a SQL-defined syntax is as follows:

EXEC SQL DECLARE <cursor name> CURSOR FOR

<SELECT Statement >

END_EXEC

Definition statement is a cursor that statement, the definition of the SELECT statement is not executed immediately.

(2) the cursor open statement (OPEN). The statement executes the SELECT statement in a cursor definition, colleagues, the cursor is active. Cursor is a pointer to point at this time before the first row of the query results. OPEN syntax is as follows:

EXEC SQL OPEN <cursor name> END_EXEC

(3) the cursor forward statement (FETCH). At this point the cursor forward one line and the line pointer (called the current row) value out, to the shared variable. The syntax is as follows:

EXEC SQL FETCH FROM <cursor name> INTO <variable table> END_EXEC

Comma separated variable table is shared by the variables registered. FETCH statement placed in the primary language programs often results in the cycle, and deal with the main language of each statement, process the query results one by one per group.

(4) cursor is closed statements (CLOSE). Close the cursor so that it no longer linked to the query results. Close the cursor, you can open again, with the new results linked. The statement syntax is as follows:

EXEC SQL CLOSE <cursor name> END_EXEC

When the cursor is active, you can modify and delete the tuple pointer.

5.6.3 Embedded SQL, use of technology

SQL DDL statements, as long as the prefix identifies the "EXEC SQL" and end mark "END_EXEC", can be embedded in the main language used in the program. Used in embedded SQL DML statements, pay attention to whether a cursor mechanism.

1. Does not involve the cursor SQL DML statements

Use SELECT statement

As INSERT, DELETE, and UPDATE statements do not go back on the data results, but the database operations, hidden as long as the prefix identifies the "EXEC SQL" and end mark "END_EXEC", can be embedded in the main language used in the program. For SELECT statements, if the query result is definitely known cell group, in cooperation with the prefix after the end of the flag can also be used directly embedded in the main program, this time should be in the SELECT INTO statement to add a clause to that found The value should be sent to the appropriate shared variables.

2. Involving cursor SQL DML statements

(1) SELECT statements use

(2) tuple pointer to modify or delete

Status of the cursor is active, you can modify or delete the tuple pointer.

3. The definition of volume and advance cursor

SQL2 provides volume cursor (scroll cursor) technology to solve this problem, in advancing the cursor to control them.

(1) the definition of volume cursor syntax is as follows:

EXEC SQL DECLARE <cursor name> SCROLL CURSOR FOR

<SELECT Statement >

END_EXEC

(2) volume of the cursor forward the following syntax:

EXEC SQL FETCH

[NEXT | PRIOR | FIRST | LAST | RELATIVE <integer> | ABSOLUTE <integer>]

FROM <cursor name> INTO <variable table> END_EXEC

Here, NEXT that advance from the current position the cursor line;

PRIOR said to return the cursor position from the current row;

FIRST move the cursor query results that the first line;

LAST move the cursor query results that last line;

The latter two syntax examples:

RELATIVE 3 that advance the cursor 3 lines from the current location;

RELATIVE -5 that the cursor returns 5 rows from the current location;

ABSOLUTE 4 results that move the cursor to the line 4;

ABSOLUTE -6 query results that move the cursor to the bottom line 6.

5.6.4 Dynamic SQL statements

Dynamic SQL technology has two main SQL statement:

1. Dynamic SQL prepared statement

EXEC SQL PREPARE <dynamic SQL statement name> FROM <shared variables or string>

Here the value of shared variables or string should be a complete SQL statement. This statement can be run in the program together based on user input. At this point, this statement is not executed.

2. Dynamic SQL statement is executed

ESEC SQL EXECUTE <dynamic statement name>

Dynamic SQL statement is used, can also have two improvements:

(1) When a combination of prepared statements in the SQL statement only once, then the statement preparation and implementation of statements can be combined into one statement:

EXEC SQL EXECUTE IMMEDIATE <shared variables or string>

(2) When a combination of prepared statements in the SQL statement, the condition value is still missing, the statement can be used in the implementation of fill USING phrase:

EXEC SQL EXECUTE <dynamic SQL statement name> USING <shared variable>

5.7 stored procedures and SQL / PSM

Stored procedure (Stored Procedure) and the SQL / PSM (Persistent Stored Module, persistent storage module)

5.7.1 database stored procedures and functions

Definition 5.2 stored procedure is to use SQL statements and control flow control module prepared statements, stored procedures, compiled and optimized by the database server is stored in the database, you can use when calling.

Using stored procedures has the following advantages:

(1) improve the speed.

(2) enhances the functionality and flexibility of SQL.

(3) can reduce network traffic.

(4) reducing the workload of programming.

(5) indirectly to achieve a safe control.

Because the stored procedure is used to complete the data query and data processing operations, and therefore can not use stored procedures to create a database object statement.

The general form of the stored procedure declared as follows:

CREATE PROCEDURE <process name "(<parameters>)

<Local declarations>

<Procedure body>;

This statement and the parameters are optional, and only specified when needed.

Function declaration in the form:

CREATE FUNCTION <function name "(<parameters>)

RETURN <return type>

<Local declarations>

<Function body>;

If the process (or function) with common programming language, and stored procedures will have to specify the language code file name.

CREATE PROCEDURE <function name "(<parameters>)

LANGUAGE <programming language name>

EXTERNAL NAME <file path name>;

Typically, each parameter should have a parameter type, the parameter type should be a SQL data type. Each parameter should have a parameter model, can be IN, OUT, or INOUT. These models is the only input, only output (return) value can be output to the input value.

Can be used in the SQL standard stored procedure CALL statement to call, you can call from the interactive interface can also be called by the embedded SQL. The call statement of the form:

CALL <process name "(<parameter list>);

5.7.2 SQL / PSM

SQL / PSM is part of the SQL standard, which specifies how to write persistent storage module, providing process flow control statements that dealt with the application logic.

1. Sequence

2. Conditional branch statement

Conditional branch is in storage during the process of one of the fundamental basis for process control. It is based on the results of the implementation of the conditional expression to determine the subsequent execution of the statement that branch. The form:

IF <condition> THEN <statement list>

ELSEIF <condition> THEN <statement list>

... ...

ELSEIF <condition> THEN <statement list>

ELSE <statement list>

END IF;

3. Loop statement

SQL / PSM in a variety of loop structures, here are three forms

(1) standard WHILE loop structure:

WHILE <condition> DO

<Statement list>

END WHILE;

(2) standard REPEAT loop structure:

REPEAT

<Statement list>

UNTIL <condition>

END REPEAT;

(3) Based on the cursor loop structure:

FOR <loop name> AS <cursor name> CURSOR FOR <query> DO

<Statement list>

END FOR;

The statements in this loop in the query results will list each tuple once. Cycle can also be a name.

In addition, if the conditions are met, you can also use a "LEAVE <loop name>" statement to break the cycle.

<! - EndFragment ->

相关文章
  • Chapter SQL language (a) 2010-12-29

    5.1 SQL Overview Structured Query Language SQL (Structured Query Language) was between relational algebra and tuple calculus language between. 5.1.1 SQL History 5.1.2 SQL database architecture SQL database architecture is basically the tertiary struc

  • Chapter SQL language (b) 2010-12-29

    5.3.5 Improved Method of nested queries 1. Exporting table SQL2 FROM clause allows the use of subqueries. If used in the FROM clause subquery, then give the results of the subquery from a table name and the corresponding column name. 2.WITH clause an

  • Chapter SQL language (II) 2010-12-29

    5.3.5 improved method of nested queries 1. Exporting table SQL2 allowed to use subqueries in the FROM clause. If the FROM clause of the subquery, then give the results of the subquery from a table name and the corresponding column names. 2.WITH claus

  • SQL Language Arts (4) flexible: Reflections SQL Statement 2010-03-18

    This chapter depth SQL double feature, SQL and database engine, relationships and the impact on the optimizer The emergence of a relational database, thanks to EF Codd relationship between the theoretical ground-breaking research results. Codd resear

  • sql language in the case when 2010-03-10

    sql language in the case when sql language has no such C language switch case statement? ? No, with the case when instead of on the line. For example, the following statement shows the Chinese years select getdate () as date, case month (getdate ())

  • SQL language description 2010-03-26

    SQL (Structure Query Language) language is the core of the database language. SQL development is from 1974, and its development process is as follows: ----- 1974, made by the Boyce and Chamberlin, then known as SEQUEL. 1976 ----- IBM's Institute for

  • sql language has no such C language switch case statement?? case 2010-06-22

    sql language has no such C language switch case statement? ? No, with the case when instead of on the line. For example, the following statement shows the Chinese years select getdate () as date, case month (getdate ()) when 11 then '11' when 12 then

  • SQLite database, sql language of 2010-10-13

    wo-06 10-12 dfeixtay original sql language of Yesterday, the first use of the database, completed a 3 million line of how to specify the data collection and preservation. The whole process is the SQL structured query language, structured query langua

  • SQL language: statistical database daily volume of data growth 2010-12-21

    SQL language: statistical database daily volume of data growth Requirements: The system sometimes statistical database a total of how much space, how much space is left, the use of space, space utilization, space free rate Implementation: select t.*

  • Example of a SQL language arts (rownum) 2010-12-29

    These days I'm reading "SQL language arts," the electronic version. There is such a simple example: the staff is not a manager among the highest income which five people. "Find the employees is not a manager" is one part of the relatio

  • ORACLE database structured query language SQL language (a) 2010-12-30

    ORACLE database structured query language SQL language (a) First, the concept description: Database DATABASE, table TABLE, column COLUMN, line ROW, keyword PRIMARY KEY, Index INDEX Second, the column type: CHAR character, and VARCHAR2, value NUMBER,

  • SQL Language Arts Learning Summary (a) 2011-01-01

    Read the past few days "SQL Language Arts," a book of which referred to the process of database development issues should be concerned about summed up: 1. Query identification: While many products provide a good monitoring tool, but a short S QL

  • Classification DQL.DML.DDL.DCL SQL language 2011-05-26

    SQL language is divided into four categories: data query language DQL, data manipulation language DML, Data definition language DDL, Data Control Language DCL. 1. DQL Data Query Language Basic structure of the data query language DQL SELECT clause is

  • SQLite database, sql language Study 2010-10-13

    wo-06 10-12 dfeixtay original sql Language Study Yesterday, the first use of the database, completed a 30,000 line specifies how much data collection and preservation. The full SQL is structured query language, structured query language. This is to u

  • SQL language presented in a way the type of transaction 2010-12-16

    SQL language foundation SQL (structured query language, Structured Query Language) is a standard database language, the following features: 1 All data are stored in relational tables. Table consists of rows and columns, each row contains a set of dat

  • Classification from the SQL language to talk about the use of COMMIT and ROLLBACK 2010-12-17

    From the division of functions, SQL language can be divided into DDL, DML and DCL three categories. 1. DDL (Data Definition Language) Data definition language for defining and managing all objects in the SQL database language; CREATE --- create table

  • SQL language: Statistics database daily volume of data growth 2010-12-21

    SQL language: Statistics database daily volume of data growth Requirements: The system sometimes statistical database a total of how much space, how much space is left, use of space, space utilization, space free rate Achieve: select t.* from (SELECT

  • ORACLE database query language SQL language structure (a) 2010-12-30

    ORACLE database query language SQL language structure (a) First, the concept description: Database DATABASE, table TABLE, column COLUMN, line ROW, keyword PRIMARY KEY, index INDEX Second, the type of the column: the character CHAR and VARCHAR2, value

  • SQL language arts study concluded (a) 2011-01-01

    These days read "SQL Language Arts," a book of which refers to the process of database development issues of concern should be to sum up: 1 identification query: While many products provide a good monitoring tool, but to determine the short S QL

  • SQL Language Reference 2011-02-18

    SQL Language Reference