Database Design Summary

It can be divided into three parts: basic concepts of databases, practical skills of SQL language, database design skills and instances.

--------------------------------------------------------------------------------

Basic concepts of databases:

Database, table, column, primary key, foreign key, data type.

Transaction. Http://www.cnblogs.com/samwu/archive/2011/09/07/2169851.html

Lock Mechanism. Http://www.cnblogs.com/samwu/archive/2011/09/19/2181506.html, the transaction isolation mechanism will affect the lock mechanism for addition, deletion, query, and modification.

Integrity constraints, including primary key constraints, foreign key constraints, unique constraints, data type constraints, non-empty constraints, and custom constraints.

Paradigm. Http://www.cnblogs.com/samwu/archive/2011/09/07/2169629.html

E-R diagram. Http://www.cnblogs.com/samwu/archive/2011/09/07/2169842.html

Database lifecycle. Http://www.cnblogs.com/samwu/archive/2011/09/07/2169829.html

Data flowchart and Data Dictionary (not familiar ).

Skills in 14 Database Design (transfer ). Http://www.cnblogs.com/samwu/archive/2011/09/07/2169631.html

 

Recommended books: Database principles and design

--------------------------------------------------------------------------------

SQL language practical skills:

Paging stored procedure.

Trigger usage.

Database User-defined functions.

Use the cursor.

Query consecutive dates. Http://www.cnblogs.com/samwu/archive/2011/09/15/2177176.html

Generate a continuous date. Http://www.cnblogs.com/samwu/archive/2011/09/27/2192953.html

Integrated SQL query: An SQL pen question that employees and departments query. Http://www.cnblogs.com/samwu/archive/2011/09/27/2192934.html

Developing SQL is a fortune. Http://kb.cnblogs.com/page/103705/

 

 

Insert, update, delete,

Query:

SQL Aggregate functions: max (maximum), min (minimum), AVG (average), sum (and), and count (Quantity ).

Data Sorting: the order by clause is at the end of the SELECT statement. It allows you to specify one or more columns for sorting. You can also specify that the sorting method is ascending (from small to large, ASC) or descending order (ascending to small, DESC ).

Wildcard filtering. A single-character matching wildcard is a half-width underline "_", and a multi-character matching wildcard is a half-width percent sign "%", which matches any number of times (zero or multiple) any character that appears.

Null Value processing. If no value is specified for a column in the database, the value is null. null in this column and null in C # indicate "unknown" in the database, rather than "NONE. SQL uses is null and is not null for null determination.

Multi-value matching, select Fage, fnumber, fname from t_employee where Fage in (, 28 ).

Data group: the Group by clause must be placed after the where statement. columns that do not appear in the group by clause cannot be placed in the column name list after the SELECT statement (except in aggregate functions ).

Having statement. You cannot use aggregate functions in where. Having must be used. Having must be placed after group by. Note that having cannot use columns not involved in grouping. Having cannot replace where. Having filters groups.

Limit the number of rows in the result set, that is, the top keyword. After sqlserver2005, The row_number function is added for simplified implementation, which is usually used for paging.

Remove data duplication. distinct processes the data of the entire result set repeatedly, instead of targeting each column. (Forgot)

Union result set, Union keyword, basic principle: each result set must have the same number of columns, and each result set column must be type compatible. The Union keyword is used to merge two query result sets and merge completely duplicated data rows into one. The Union keyword is inefficient because it requires repeated value scanning. If you are not sure to merge duplicate rows, use the Union all keyword.

 

Various query functions:

Numeric functions,

ABS (), returns the absolute value. Ceiling (), rounded to the maximum integer. 3.33 is rounded to 4, 2.89 is rounded to 3, and-3.61 is rounded to-3.

Floor (): round to the smallest integer. 3.33 is rounded to 3, 2.89 is rounded to 2, and-3.61 is rounded to-4.

Round (): rounding. For example, select round (3.14666, 2) returns 3.15000, and select round (3.14666, 3) returns 3.14700.

String function, date function, type conversion function, null value processing function, and caes function.

 

Index,

Full table scan: full table scan is the most efficient way to retrieve data (select. Using indexes can improve query efficiency, but indexes occupy space, and indexes must be updated synchronously when adding, updating, and deleting data. Therefore, the speed of insert, update, and delete operations will be reduced. You can only create an index on a field that is frequently searched (where. Even if an index is created, it is still possible to perform full table profiling, such as like, function, and type conversion. Http://www.cnblogs.com/xioxu/archive/2010/10/20/1857119.html

Table join, join is connected to which table, and on is connected. Inner join, left join, right join,

Http://www.cnblogs.com/doucontorl/archive/2010/09/06/1819419.html

Subquery: http://www.cnblogs.com/edisonfeng/archive/2011/07/02/2096403.html

 

 

 

 

 

 

 

 

 

Recommended books: SQL cookbook

 

------------------------------------------------------------------------------ Database design instance:

Table Design for category menus and Table Design for adding additional item properties: http://blog.csdn.net/luyysea/article/details/5068157