Go: How to learn SQL (Part IV: DBMS extension features and SQL advanced topics)

Transferred from: Http://blog.163.com/[email protected]/blog/static/285720652010950102575/

9. Extended functionality provided by the DBMS

Master the basic relational model principle and DBMS data type, but also need to understand the extension function provided by DBMS, in order to make full use of DBMS for database development.

9.1. Control Flow

SQL is a descriptive language, but the DBMS, in the face of actual development requirements, usually provides procedural extensions in the SQL dialect, including (in T-SQL, for example):
1. Variable Definition and Assignment
[Code=sql]
DECLARE @var <datetype>--Variable definition statement
Set @var = <value>--Assign a value by a set statement
Select @var = MAX (column_value) from [table]--Assignment by SELECT statement
[/code]
2. Code block
BEGIN ... End defines a block of code.
For the following if/else and while, if Begin is omitted ... The end code block, conditions, and loops will only take effect for the first statement thereafter.
3. Conditional branching statements
IF ... ELSE ...
Note the difference between the IF statement and the case-when expression.
4. Circular control Statements
The while can be looped. Break/continue can jump out or make the next loop.
5. Exception Handling Statements
SQL Server 2005 supports exception handling with try-catch statements, but only a subset of exceptions. See Books Online.

9.2. Dynamic statements

SQL dynamic statements are powerful, but difficult to debug and maintain (string concatenation, no syntax highlighting), inefficient (hard to reuse execution plans), and poor security (SQL injection). Avoid using dynamic SQL unless it is functionally necessary.

If you really need to use dynamic SQL, the way to use sp_executesql is better than exec (). Because there are times when you can reuse execution plans to improve performance, and allow parameters to be passed, the data type is more secure.

9.3. DBMS-supported database objects

SQL Server also supports database objects such as temporal tables, views, stored procedures, custom functions (scalar and table values), triggers, cursors, and so on, which is a necessary knowledge to exploit SQL Server for development. See the section on Microsoft SQL Server 2005 Technology Insider: T-SQL programming.

9.4. System functions, System views, and system stored procedures provided by the DBMS

-System Functions: Provides specific expression arithmetic functions, such as date-time functions, String functions, mathematical functions, aggregate functions, etc., which are required for T-SQL programming.
-System Views: Contains database metadata, internal system running data, etc., such as catalog view (used instead of system tables in SQL Server 2000), Information Schema View, dynamic management view, and so on.
-System procedures: To view systems information, modify system configuration, etc., such as directory stored procedures, database engine stored procedures, and so on.

9.5. Tools provided by the DBMS

-Database Server Configuration tool: Configuration Manager, surface area Configurator, etc.
-Database client utilities: SSMS, sqlcmd, bcp, etc.
-Database performance tools: SQL Server Profiler, etc.


10. Advanced Topics

The following is a database-related high-level topic, each piece deserves to be discussed separately, this post is no longer detailed.

1. Advanced Technical Topics
-Database design
-Server architecture
-Indexing and performance optimization
-Transactions, locking and concurrency
-Backup and restore

Trackback:http://topic.csdn.net/u/20100826/18/21e54f19-b203-4dcb-bc40-6d39554779f7.html

Go: How to learn SQL (Part IV: DBMS extension features and SQL advanced topics)