Summary of basic SQL statements

ArticleDirectory

• Preface
• I. Four Basic SQL statements
• Ii. statement execution sequence
• 4. dynamic SQL statements
• Conclusion
• Preface
• I. Four Basic SQL statements
• Ii. statement execution sequence
• 4. dynamic SQL statements
• Conclusion

This article from: http://www.cnblogs.com/tunwa/archive/2011/03/08/1977208.html

 

Summary of basic SQL statements

Preface

This section describes some basic and common SQL statements, rather than the basic knowledge of databases. The knowledge about databases remains to be discussed later. Now we will discuss some common SQL statements.

This section covers the basic syntax of SQL, the execution sequence of SQL, the combination of SQL statements, and dynamic SQL statements. There are no pre-and post-order differences between these four aspects. They complement each other, and there are still many links within them.

I. Four Basic SQL statements 1. Query

Select * from table

2. Update

Update table set field = Value

3. insert

Insert [into] Table (field) values (value)

4. Delete

Delete [from] Table

Ii. statement execution sequence 1. syntax analysis

Analyze whether the syntax in the statement complies with the specifications to measure the meaning of each expression in the statement.

2. Semantic Analysis

Check whether all database objects involved in the statement exist and the user has the corresponding permissions.

3. Select Optimizer

Different databases have differentAlgorithm(This involves the data structure). The database selects different optimizers for SQL statements based on its own understanding (the database itself), and different optimizers select different execution plans"

4. Run the "Execution Plan"

Execute the SQL statement according to the execution plan.

The above describes the general route of Data Execution.

5. execution sequence of select statements

I borrowed a paragraph from the SQL Server 2005 Technology Insider: T-SQL query by Itzik Ben-Gan, lubor Kollar, Dejan sarka to explain:

(8) Select (9) distinct (11) <top_specification> <select_list>

(1) From <lef t_table>

(3) <join_type> join <right_table>

(2) On <join _ condition>

(4) Where <where_condition>

(5) group by <group_by_list>

(6) with {cube | rollup}

(7) having (having_condition)

(10) order by <order_by_condition>

From this order, we can see that all the query statements are executed from. During execution, each step generates a virtual table for the next step, which serves as the basis for the next step.

Step 1: From

First, a Cartesian product is executed for the first two tables in the from clause. In this case, a virtual table vt1 is generated.

Step 2: On

The next step is to apply the on filter. The logical expression in on will apply to each row in vt1, filter the rows that meet the on logical expression, and generate the virtual table VT2.

Step 3: Join

For outer join, the external row is added in this step, and left outer jion adds the left table filtered in step 2, if right outer join is used, add the rows filtered out by the right table in step 2 to generate the virtual table vt3.

Step 4: Multi-table

If the number of tables in the from clause is more than two tables, connect vt3 to the third table to calculate the Cartesian Product and generate a virtual table. This process repeats steps 1-3, finally, a new virtual table vt3.

Step 5: Where

Apply the where filter and reference the where filter to the virtual table produced in the previous step to generate the virtual table vt4. In this case, we have to explain the important details. For queries that contain the Outer Join clause, there is a confusing question: In the on filter or use the where filter to specify the logical expression? The biggest difference between on and where is that if a logical expression is applied to on, you can add the removed row again in step 3 outer join, and the final result of Where is removed.

Step 6: group

Group to generate virtual table vt4

Step 7: having

Apply having filter to vt4 to generate virtual table vt5

Step 8: select

Process the select list and generate a virtual table vt6

Step 9: distinct

Remove duplicate rows in vt6 to generate virtual table vt7

Step 10: Order

Sort the rows in vt7 by column list in the order by clause to generate a cursor vc8

Step 2: Top

Select a specified number or proportion of rows from the beginning of vc8, generate the virtual table vt9, and return it to the caller

Iii. SQL statement Extension

1. select1.1 selective insert statement 1.1.1 insert into Table1 (field1) Select field2 from Table2

Table 1 must exist.

1.1.2 select field1 into Table1 from Table2

If table 1 does not exist, a table named Table1 and field named field1 will be automatically created at runtime.

1.2 open other data sources

/* Oraclesvr is the name of the linked server. In this example, an Oracle database alias named orcldb has been created. */

Exec sp_add1_server 'oraclesvr ', -- connection server name oraclesvr, sysname type

'Msdaora ', -- provide the provider_name Data SourceProgram, Which is Oracle

'Orcldb' -- Data Source Name

Go

Select * From openquery (oraclesvr, 'select name, ID from Joe. Titles ')

If there are multiple SQL Server instances:

Select * from [servername \ InstanceName.] pubs. DBO. authors.

Note: The complete name of an object includes four identifiers: Server Name, database name, owner name, and Object Name. The format is as follows:

[[[Server.] [database].] [owner_name].] object_name

The name in the middle can be omitted, but the name in the middle cannot be omitted. For example, server... Object_name

2. update2.1 multi-Table update

Update Table1 set table1.field 1 = table2.field2 from

Table1, Table2/* Guess that the join mode is fully connected. Full [outer] Join */

Where table1.field3 = Table2. filed3

Knowledge: The from statement in the SQL Server Update statement can be followed by multiple tables. Oracle does not support this usage.

In ORACLE: Update Table1 set table1.field1 =

(Select table2.field2 from Table2 where. field3 = Table2. filed3)

3. Specification of insert3.1 insert statement

In SQL Server 2000 and SQL Server 2005

Standard statement: insert into table (field) values (value)

Tip: the access statements are incorrect because the SQL statements are not standardized. Therefore, you must follow the regular syntax when writing SQL statements.

4. e4.1 standard Deletion

Standard statement: delete from table where Condition

Tip: Same as insert

4.2 Delete other 4.2.1 truncate

Syntax: truncate table table_name

Deleting all rows in the table does not record the deletion operation of a single row, but does not record logs. The speed is faster than that of Delete.

4.2.2 drop

Statement: Drop table table_name

Delete tables and related tables. If you have an FK constraint, You cannot delete the tables. The system tables cannot be used last year.

5. Order

Function: Sort

Tip: Order by newid () random sorting

Iv. Basic principles of dynamic SQL statement 4.1 4.1.1 pre-Compilation

Before executing execute, the database does not compile statements in the execute statement. dynamic SQL statements are stored in the stored procedure and are not pre-compiled.

4.1.2 when to use dynamic SQL statements

When field name, table name, and database name are used as variables, dynamic SQL statements must be used.

4.2.exec [ute] 4.2.1 syntax

Exec ('select * From table_name where name = ''' + @ name + ''') -- no fewer parentheses

4.2.2 passing Parameters

-- Assume that a parameter is required in the Stored Procedure test_sp: nvarchar (50) Name @ parm

Declare @ parms nvarchar (50)

Set @ parms = 'test variable'

Exec test_sp [@ parm =] @ parms-the values in square brackets can be omitted.

If it is the first sentence in the batch processing, you can omit Exec

4.2.3 output parameters

Declare @ num int, @ field int,

@ Sqls nvarchar (4000)

Set @ field = 1

Set @ sqls = 'select @ A = count (*) from table_name where field = @ field'

Exec sp_executesql @ sqls, n' @ A int output, @ field int ', @ num output, @ Field

Select @ num

4.3.sp _ executesql

Syntax: exec [ute] sp_executesql n' select * From table_name where field = @ field ', n' @ field int', @ field = 1

Using sp_exutesql is more efficient than using exec.

Conclusion

The preceding statements are commonly used in SQL and their execution sequence. The SQL Server Series, MySQL, access, and Oracle series are basically the same. If you want to do a deep research on the database, read more of its own help, more exercises, and read more about the principles of the database.

 

Preface

This section describes some basic and common SQL statements, rather than the basic knowledge of databases. The knowledge about databases remains to be discussed later. Now we will discuss some common SQL statements.

This section covers the basic syntax of SQL, the execution sequence of SQL, the combination of SQL statements, and dynamic SQL statements. There are no pre-and post-order differences between these four aspects. They complement each other, and there are still many links within them.

I. Four Basic SQL statements 1. Query

Select * from table

2. Update

Update table set field = Value

3. insert

Insert [into] Table (field) values (value)

4. Delete

Delete [from] Table

Ii. statement execution sequence 1. syntax analysis

Analyze whether the syntax in the statement complies with the specifications to measure the meaning of each expression in the statement.

2. Semantic Analysis

Check whether all database objects involved in the statement exist and the user has the corresponding permissions.

3. Select Optimizer

Different databases have different algorithms (this involves data structures). The database selects different optimizers for SQL statements based on its own understanding (the database itself, different optimizers select different execution plans"

4. Run the "Execution Plan"

Execute the SQL statement according to the execution plan.

The above describes the general route of Data Execution.

5. execution sequence of select statements

I borrowed a paragraph from the SQL Server 2005 Technology Insider: T-SQL query by Itzik Ben-Gan, lubor Kollar, Dejan sarka to explain:

(8) Select (9) distinct (11) <top_specification> <select_list>

(1) From <lef t_table>

(3) <join_type> join <right_table>

(2) On <join _ condition>

(4) Where <where_condition>

(5) group by <group_by_list>

(6) with {cube | rollup}

(7) having (having_condition)

(10) order by <order_by_condition>

From this order, we can see that all the query statements are executed from. During execution, each step generates a virtual table for the next step, which serves as the basis for the next step.

Step 1: From

First, a Cartesian product is executed for the first two tables in the from clause. In this case, a virtual table vt1 is generated.

Step 2: On

The next step is to apply the on filter. The logical expression in on will apply to each row in vt1, filter the rows that meet the on logical expression, and generate the virtual table VT2.

Step 3: Join

For outer join, the external row is added in this step, and left outer jion adds the left table filtered in step 2, if right outer join is used, add the rows filtered out by the right table in step 2 to generate the virtual table vt3.

Step 4: Multi-table

If the number of tables in the from clause is more than two tables, connect vt3 to the third table to calculate the Cartesian Product and generate a virtual table. This process repeats steps 1-3, finally, a new virtual table vt3.

Step 5: Where

Apply the where filter and reference the where filter to the virtual table produced in the previous step to generate the virtual table vt4. In this case, we have to explain the important details. For queries that contain the Outer Join clause, there is a confusing question: In the on filter or use the where filter to specify the logical expression? The biggest difference between on and where is that if a logical expression is applied to on, you can add the removed row again in step 3 outer join, and the final result of Where is removed.

Step 6: group

Group to generate virtual table vt4

Step 7: having

Apply having filter to vt4 to generate virtual table vt5

Step 8: select

Process the select list and generate a virtual table vt6

Step 9: distinct

Remove duplicate rows in vt6 to generate virtual table vt7

Step 10: Order

Sort the rows in vt7 by column list in the order by clause to generate a cursor vc8

Step 2: Top

Select a specified number or proportion of rows from the beginning of vc8, generate the virtual table vt9, and return it to the caller

Iii. SQL statement Extension

1. select1.1 selective insert statement 1.1.1 insert into Table1 (field1) Select field2 from Table2

Table 1 must exist.

1.1.2 select field1 into Table1 from Table2

If table 1 does not exist, a table named Table1 and field named field1 will be automatically created at runtime.

1.2 open other data sources

/* Oraclesvr is the name of the linked server. In this example, an Oracle database alias named orcldb has been created. */

Exec sp_add1_server 'oraclesvr ', -- connection server name oraclesvr, sysname type

'Msdaora ', -- provider_name data source provider, which is Oracle

'Orcldb' -- Data Source Name

Go

Select * From openquery (oraclesvr, 'select name, ID from Joe. Titles ')

If there are multiple SQL Server instances:

Select * from [servername \ InstanceName.] pubs. DBO. authors.

Note: The complete name of an object includes four identifiers: Server Name, database name, owner name, and Object Name. The format is as follows:

[[[Server.] [database].] [owner_name].] object_name

The name in the middle can be omitted, but the name in the middle cannot be omitted. For example, server... Object_name

2. update2.1 multi-Table update

Update Table1 set table1.field 1 = table2.field2 from

Table1, Table2/* Guess that the join mode is fully connected. Full [outer] Join */

Where table1.field3 = Table2. filed3

Knowledge: The from statement in the SQL Server Update statement can be followed by multiple tables. Oracle does not support this usage.

In ORACLE: Update Table1 set table1.field1 =

(Select table2.field2 from Table2 where. field3 = Table2. filed3)

3. Specification of insert3.1 insert statement

In SQL Server 2000 and SQL Server 2005

Standard statement: insert into table (field) values (value)

Tip: the access statements are incorrect because the SQL statements are not standardized. Therefore, you must follow the regular syntax when writing SQL statements.

4. e4.1 standard Deletion

Standard statement: delete from table where Condition

Tip: Same as insert

4.2 Delete other 4.2.1 truncate

Syntax: truncate table table_name

Deleting all rows in the table does not record the deletion operation of a single row, but does not record logs. The speed is faster than that of Delete.

4.2.2 drop

Statement: Drop table table_name

Delete tables and related tables. If you have an FK constraint, You cannot delete the tables. The system tables cannot be used last year.

5. Order

Function: Sort

Tip: Order by newid () random sorting

Iv. Basic principles of dynamic SQL statement 4.1 4.1.1 pre-Compilation

Before executing execute, the database does not compile statements in the execute statement. dynamic SQL statements are stored in the stored procedure and are not pre-compiled.

4.1.2 when to use dynamic SQL statements

When field name, table name, and database name are used as variables, dynamic SQL statements must be used.

4.2.exec [ute] 4.2.1 syntax

Exec ('select * From table_name where name = ''' + @ name + ''') -- no fewer parentheses

4.2.2 passing Parameters

-- Assume that a parameter is required in the Stored Procedure test_sp: nvarchar (50) Name @ parm

Declare @ parms nvarchar (50)

Set @ parms = 'test variable'

Exec test_sp [@ parm =] @ parms-the values in square brackets can be omitted.

If it is the first sentence in the batch processing, you can omit Exec

4.2.3 output parameters

Declare @ num int, @ field int,

@ Sqls nvarchar (4000)

Set @ field = 1

Set @ sqls = 'select @ A = count (*) from table_name where field = @ field'

Exec sp_executesql @ sqls, n' @ A int output, @ field int ', @ num output, @ Field

Select @ num

4.3.sp _ executesql

Syntax: exec [ute] sp_executesql n' select * From table_name where field = @ field ', n' @ field int', @ field = 1

Using sp_exutesql is more efficient than using exec.

Conclusion

the preceding statements are commonly used in SQL and their execution sequence. The SQL Server Series, MySQL, access, and Oracle series are basically the same. If you want to do a deep research on the database, read more of its own help, more exercises, and read more about the principles of the database.