Joe Celko's SQL programming style
Are you an SQL programmer that, like many, came to SQL after learning and writing procedural or object-oriented code? Or have switched jobs to where a different brand of SQL is being used, or maybe even been told to learn SQL yourself? If even one answer is yes, then you need this book. A "Manu...
Uloženo v:
| Hlavní autor: | |
|---|---|
| Médium: | E-kniha Kniha |
| Jazyk: | angličtina |
| Vydáno: |
San Francisco
Elsevier
2005
Amsterdam ; Boston ; Tokyo Morgan Kaufmann Publishers Elsevier Science & Technology Morgan Kaufmann Morgan Kaufmann/Elsevier |
| Vydání: | 1 |
| Edice: | The Morgan Kaufmann Series in Data Management Systems |
| Témata: | |
| ISBN: | 0120887975, 9780120887972 |
| On-line přístup: | Získat plný text |
| Tagy: |
Přidat tag
Žádné tagy, Buďte první, kdo vytvoří štítek k tomuto záznamu!
|
Obsah:
- 4.7 General Guidelines for Using Scales in a Database -- 5. Data Encoding Schemes -- 5.1 Bad Encoding Schemes -- 5.2 Encoding Scheme Types -- 5.2.1 Enumeration Encoding -- 5.2.2 Measurement Encoding -- 5.2.3 Abbreviation Encoding -- 5.2.4 Algorithmic Encoding -- 5.2.5 Hierarchical Encoding Schemes -- 5.2.6 Vector Encoding -- 5.2.7 Concatenation Encoding -- 5.3 General Guidelines for Designing Encoding Schemes -- 5.3.1 Existing Encoding Standards -- 5.3.2 Allow for Expansion -- 5.3.3 Use Explicit Missing Values to Avoid NULLs -- 5.3.4 Translate Codes for the End User -- 5.3.5 Keep the Codes in the Database -- 5.4 Multiple Character Sets -- 6. Coding Choices -- 6.1 Pick Standard Constructions over Proprietary Constructions -- 6.1.1 Use Standard OUTER JOIN Syntax -- 6.1.2 Infixed INNER JOIN and CROSS JOIN Syntax Is Optional, but Nice -- 6.1.3 Use ISO Temporal Syntax -- 6.1.4 Use Standard and Portable Functions -- 6.2 Pick Compact Constructions over Longer Equivalents -- 6.2.1 Avoid Extra Parentheses -- 6.2.2 Use CASE Family Expressions -- 6.2.3 Avoid Redundant Expressions -- 6.2.4 Seek a Compact Form -- 6.3 Use Comments -- 6.3.1 Stored Procedures -- 6.3.2 Control Statement Comments -- 6.3.3 Comments on Clause -- 6.4 Avoid Optimizer Hints -- 6.5 Avoid Triggers in Favor of DRI Actions -- 6.6 Use SQL Stored Procedures -- 6.7 Avoid User-Defined Functions and Extensions inside the Database -- 6.7.1 Multiple Language Problems -- 6.7.2 Portability Problems -- 6.7.3 Optimization Problems -- 6.8 Avoid Excessive Secondary Indexes -- 6.9 Avoid Correlated Subqueries -- 6.10 Avoid UNIONs -- 6.11 Testing SQL -- 6.11.1 Test All Possible Combinations of NULLs -- 6.11.2 Inspect and Test All CHECK() Constraints -- 6.11.3 Beware of Character Columns -- 6.11.4 Test for Size -- 7. How to Use VIEWS -- 7.1 VIEW Naming Conventions Are the Same as Tables
- 7.1.1 Always Specify Column Names -- 7.2 VIEWs Provide Row- and Column-Level Security -- 7.3 VIEWs Ensure Efficient Access Paths -- 7.4 VIEWs Mask Complexity from the User -- 7.5 VIEWs Ensure Proper Data Derivation -- 7.6 VIEWs Rename Tables and/or Columns -- 7.7 VIEWs Enforce Complicated Integrity Constraints -- 7.8 Updatable VIEWs -- 7.8.1 WITH CHECK OPTION clause -- 7.8.2 INSTEAD OF Triggers -- 7.9 Have a Reason for Each VIEW -- 7.10 Avoid VIEW Proliferation -- 7.11 Synchronize VIEWs with Base Tables -- 7.12 Improper Use of VIEWs -- 7.12.1 VIEWs for Domain Support -- 7.12.2 Single-Solution VIEWs -- 7.12.3 Do Not Create One VIEW Per Base Table -- 7.13 Learn about Materialized VIEWs -- 8. How to Write Stored Procedures -- 8.1 Most SQL 4GLs Are Not for Applications -- 8.2 Basic Software Engineering -- 8.2.1 Cohesion -- 8.2.2 Coupling -- 8.3 Use Classic Structured Programming -- 8.3.1 Cyclomatic Complexity -- 8.4 Avoid Portability Problems -- 8.4.1 Avoid Creating Temporary Tables -- 8.4.2 Avoid Using Cursors -- 8.4.3 Prefer Set-Oriented Constructs to Procedural Code -- 8.5 Scalar versus Structured Parameters -- 8.6 Avoid Dynamic SQL -- 8.6.1 Performance -- 8.6.2 SQL Injection -- 9. Heuristics -- 9.1 Put the Specification into a Clear Statement -- 9.2 Add the Words "Set of All..." in Front of the Nouns -- 9.3 Remove Active Verbs from the Problem Statement -- 9.4 You Can Still Use Stubs -- 9.5 Do Not Worry about Displaying the Data -- 9.6 Your First Attempts Need Special Handling -- 9.6.1 Do Not Be Afraid to Throw Away Your First Attempts at DDL -- 9.6.2 Save Your First Attempts at DML -- 9.7 Do Not Think with Boxes and Arrows -- 9.8 Draw Circles and Set Diagrams -- 9.9 Learn Your Dialect -- 9.10 Imagine That Your WHERE Clause Is ÏSuper AmebaÓ -- 9.11 Use the Newsgroups and Internet -- 10. Thinking in SQL
- 3.1 Put the Default in the Right Place -- 3.2 The Default Value Should Be the Same Data Type as the Column -- 3.3 Do Not Use Proprietary Data Types -- 3.4 Place the PRIMARY KEY Declaration at the Start of the CREATE TABLE Statement -- 3.5 Order the Columns in a Logical Sequence and Cluster Them in Logical Groups -- 3.6 Indent Referential Constraints and Actions under the Data Type -- 3.7 Give Constraints Names in the Production Code -- 3.8 Put CHECK() Constraint Near what they Check -- 3.8.1 Consider Range Constraints for Numeric Values -- 3.8.2 Consider LIKE and SIMILAR TO Constraints for Character Values -- 3.8.3 Remember That Temporal Values Have Duration -- 3.8.4 REAL and FLOAT Data Types Should Be Avoided -- 3.9 Put Multiple Column Constraints as Near to Both Columns as Possible -- 3.10 Put Table-Level CHECK() Constraints at the End of the Table Declaration -- 3.11 Use CREATE ASSERTION for Multi-table Constraints -- 3.12 Keep CHECK() Constraints Single Purposed -- 3.13 Every Table Must Have a Key to Be a Table -- 3.13.1 Auto-Numbers Are Not Relational Keys -- 3.13.2 Files Are Not Tables -- 3.13.3 Look for the Properties of a Good Key -- 3.14 Do Not Split Attributes -- 3.14.1 Split into Tables -- 3.14.2 Split into Columns -- 3.14.3 Split into Rows -- 3.15 Do Not Use Object-Oriented Design for an RDBMS -- 3.15.1 A Table Is Not an Object Instance -- 3.15.2 Do Not Use EAV Design for an RDBMS -- 4. Scales and Measurements -- 4.1 Measurement Theory -- 4.1.1 Range and Granularity -- 4.1.2 Range -- 4.1.3 Granularity, Accuracy, and Precision -- 4.2 Types of Scales -- 4.2.1 Nominal Scales -- 4.2.2 Categorical Scales -- 4.2.3 Absolute Scales -- 4.2.4 Ordinal Scales -- 4.2.5 Rank Scales -- 4.2.6 Interval Scales -- 4.2.7 Ratio Scales -- 4.3 Using Scales -- 4.4 Scale Conversion -- 4.5 Derived Units -- 4.6 Punctuation and Standard Units
- 10.1 Bad Programming in SQL and Procedural Languages -- 10.2 Thinking of Columns as Fields -- 10.3 Thinking in Processes, Not Declarations -- 10.4 Thinking the Schema Should Look Like the Input Forms -- back matter -- Appendix. Resources -- Military Standards -- Metadata Standards -- ANSI and ISO Standards -- U.S. Government Codes -- Retail Industry -- Code Formatting and Naming Conventions -- Appendix. Bibliography -- Reading Psychology -- Programming Considerations -- Index -- About the Author
- Front cover -- Title page -- Copyright page -- Table of contents -- front matter -- Introduction -- 1.1 Purpose of the Book -- 1.2 Acknowledgments -- 1.3 Corrections, Comments, and Future Editions -- First chapter -- 1. Names and Data Elements -- 1.1 Names -- 1.1.1 Watch the Length of Names -- 1.1.2 Avoid All Special Characters in Names -- 1.1.3 Avoid Quoted Identifiers -- 1.1.4 Enforce Capitalization Rules to Avoid Case- Sensitivity Problems -- 1.2 Follow the ISO-11179 Standards Naming Conventions -- 1.2.1 ISO-11179 for SQL -- 1.2.2 Levels of Abstraction -- 1.2.3 Avoid Descriptive Prefixes -- 1.2.4 Develop Standardized Postfixes -- 1.2.5 Table and View Names Should Be Industry Standards, Collective, Class, or Plural Nouns -- 1.2.6 Correlation Names Follow the Same Rules as Other Names . . . Almost -- 1.2.7 Relationship Table Names Should Be Common Descriptive Terms -- 1.2.8 Metadata Schema Access Objects Can Have Names That Include Structure Information -- 1.3 Problems in Naming Data Elements -- 1.3.1 Avoid Vague Names -- 1.3.2 Avoid Names That Change from Place to Place -- 1.3.3 Do Not Use Proprietary Exposed Physical Locators -- 2. Fonts, Punctuation, and Spacing -- 2.1 Typography and Code -- 2.1.1 Use Only Upper- and Lowercase Letters, Digits, and Underscores for Names -- 2.1.2 Lowercase Scalars Such as Column Names, Parameters, and Variables -- 2.1.3 Capitalize Schema Object Names -- 2.1.4 Uppercase the Reserved Words -- 2.1.5 Avoid the Use of CamelCase -- 2.2 Word Spacing -- 2.3 Follow Normal Punctuation Rules -- 2.4 Use Full Reserved Words -- 2.5 Avoid Proprietary Reserved Words if a Standard Keyword Is Available in Your SQL Product -- 2.6 Avoid Proprietary Statements if a Standard Statement Is Available -- 2.7 Rivers and Vertical Spacing -- 2.8 Indentation -- 2.9 Use Line Spacing to Group Statements -- 3. Data Declaration Language