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.