3 ページ
Mark Simon Ivanhoe VIC, VIC, Australia ISBN 978-1-4842-9492-5 e-ISBN 978-1-4842-9493-2 https://doi.org/10.1007/978-1-4842-9493-2 © Mark Simon 2023 This work is subject to copyright. All rights are solely and exclusively licensed by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. The use of general descriptive names, registered names, trademarks, service marks, etc. in this publication does not imply, even in the absence of a specific statement, that such names are exempt from the relevant protective laws and regulations and therefore free for general use. The publisher, the authors, and the editors are safe to assume that the advice and information in this book are believed to be true and accurate at the date of publication. Neither the publisher nor the authors or the editors give a warranty, expressed or implied, with respect to the material contained herein or for any errors or omissions that may have been made. The publisher remains neutral with regard to jurisdictional claims in published maps and institutional affiliations. This Apress imprint is published by the registered company APress Media, LLC, part of Springer Nature. The registered company address is: 1 New York Plaza, New York, NY 10004, U.S.A.
5 ページ
Introduction In the distant past, data was managed by computers in all sorts of ways, and there was no one way to do this. There still isn’t, which isn’t a bad thing, because not all data can be handled the same way. There is, however, a large body of data which can be handled in a common way, and the early 1970s saw the development of a set of mathematical principles in the relational model. Starting in a lab at IBM, software was developed to handle relational data, and a language was developed to manage it. The language eventually became the Structured Query Language. In the early days, different vendors had their own idea of how the language should work, but this was eventually rolled into a standard. The standard has grown over the decades, which means that the language is also growing. Not all database vendors follow the standard to the same degree. Some standards were late in coming, and database vendors filled in some gaps with their imagination. Other standards are harder to implement than it looks, and some vendors are “still working on it.” And sometimes, the vendor just wants to do something differently. This book looks at using SQL for basic tasks. Mostly that means fetching data and possibly processing it. There are many database packages available, all with their own quirks, and all with their own variations of the SQL standard. This book covers a few of the most popular packages and makes a point of showing not only what the standard says but also how the individual packages do things. We’ll also look at how databases are designed, but not because we’ll be designing any. One of the big mysteries to any new SQL user is why do we do things this way or that, and why is the data the way it is. By understanding some design principles, you’ll be in a better position to know why. In this book, we make no assumptions about your prior knowledge, except that you have an idea what a table is. You have probably had some experience with spreadsheets as well. As to what you might do afterward, that depends. It might be your job to fetch data to feed it into some sort of analysis or reporting software. You might do all of your analysis in SQL directly, or you might write queries in
6 ページ
a specialized database application. Or you might be a web developer looking to manage a blog or an ecommerce site. The Sample Data We’ll talk more about the sample data later in the book, but the book is based on a sample database. You can sit in an armchair and read the book as it is if you like, but you’ll probably want to work through the exercises. To work through the exercises, you’ll need the following: A database server and a suitable database client. Permissions to do anything you like on the database. If you’ve installed the software locally, you probably have all the permissions you need, but if you’re doing this on somebody else’s system, you need to check. The script which produces the sample database. You can get a fresh copy of the script from https://sample-db.net and choose your options. If that’s too much like hard work, you can use the following links to download a script: PostgreSQL: https://sample-db.net/?dbmss[]=pgsql- 10&db=prints&br=crlf&refresh&download SQLite: https://sample-db.net?dbmss[]=sqlite- script&db=prints&br=crlf&refresh&download MySQL/MariaDB: https://sample-db.net/? dbmss[]=mysql- ansi&db=prints&br=crlf&refresh&download Microsoft SQL: https://sample-db.net/?dbmss[]=mssql- 16&db=prints&br=crlf&refresh&download Oracle: https://sample-db.net/?dbmss[]=oracle- 12&db=prints&br=crlf&refresh&download These links are for current versions of the software. If you want older versions, visit the preceding site. Notes
7 ページ
Throughout the book, you’ll come across a few terms and a few expectations: MySQL and MariaDB are essentially the same; MariaDB is an increasingly popular spin-off. With very few exceptions (as noted), the code is interchangeable. The book makes a great deal of using MySQL/MariaDB in so-called ANSI mode. This is easily done, as you’ll see in the book, and makes working with standard SQL much easier. The Microsoft product will sometimes be referred to as Microsoft SQL Server and sometimes as MSSQL for short. If you’re looking for T-SQL (Transact SQL), it’s also the same thing.
8 ページ
Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub. For more detailed information, please visit http://www.apress.com/source-code.
9 ページ
Acknowledgments The sample data includes information about classical paintings and their artists. This information is an extract of the hard work that went into the WebMuseum (www.ibiblio.org/wm/).
10 ページ
Table of Contents Chapter 1: Starting with SQL Basic SELECT Statement Case Sensitivity Spacing Clause Ordering The Semicolon (;) Selecting Specific Columns Column Order Layout Using SELECT * Calculated Columns Aliases Comments Block Comments Uses of Comments Filtering Rows Clause Ordering Placing the Semicolon Ordering the Results Clause Order Distinct Rows Summary Writing SQL Columns Comments
11 ページ
Filtering Data Row Order Clause Order Coming Up Chapter 2: Database About the Sample Database Database Tables Normalized Tables Multiple Values Summary Coming Up Chapter 3: Filtering Data The WHERE Clause Unrelated Assertions All and Nothing Dealing with NULL Deliberately Ignoring NULLs Finding NULLs Numbers Discrete vs. Continuous Values Strings Quotes More on MySQL/ MariaDB Modes More on Double and Single Quotes Case Sensitivity
12 ページ
Trailing Spaces Filtering with String Functions Handling Quotes and Apostrophes Before and After Strings Dates Dates Are Not Strings Alternative Date Formats Date Comparisons Filtering with a Date Calculation Multiple Assertions AND and OR The IN Operator Derived Lists Wildcard Matches Case Sensitivity and Patterns Pattern Characters Wildcards with Non-strings Extensions to Wildcards A Simple Pattern Match Example Summary NULL Numbers Strings Dates Multiple Assertions The IN Operator
13 ページ
Wildcard Matches Coming Up Chapter 4: Ordering Results Using the ORDER BY Clause Sort Direction Missing Data (NULL) Data Types Case Sensitivity and Collation Multiple Columns Interdependence of Columns Sort Direction on Multiple Columns Sorting by Calculated Columns Limiting the Number of Results Paging Using LIMIT … OFFSET … (MySQL/MariaDB, SQLite, and PostgreSQL) Using TOP (MSSQL) Fetching a Random Row Nonalphabetical String Order Special Strings Summary Sorting with ORDER BY Limiting Results Sorting Strings Coming Up Chapter 5: Calculating Column Values
14 ページ
Testing Calculations Emulating Variables Some Basic Calculations Basic Number Calculations Basic String Calculations Basic Date Calculations Working with NULL Using Aliases Aliases Without AS Awkward Aliases Calculating with Numbers Arithmetic Operators Integers Remainder Extra Decimals Mathematical Functions Approximation Functions Formatting Functions Calculating with Dates Simple Calculations Age Calculations Extracting Parts of a Date Formatting a Date Strings Character Functions Subqueries
15 ページ
The CASE Expression Casting to Different Data Types The cast( ) Function Casting to a String Casting Date Literals Creating a View Using Views in Microsoft SQL Summary Data Types NULLs Aliases Subqueries The CASE Expression Casting a Value Views Coming Up Chapter 6: Joining Tables How a Join Works Joining the Tables Alternative Syntax Selecting the Results Table Aliases Developing a Price List Join Types The INNER JOIN The LEFT OUTER JOIN and RIGHT OUTER JOIN
16 ページ
The “Preferred” Outer Join Some Recommendations on JOINS Finishing the Price List Joining Many Tables Building a Larger JOIN Revisiting Some Subqueries A More Complex Join Using a Self-Join Summary Syntax Table Aliases The ON Clause Join Types Coming Up Chapter 7: Aggregating Data Counting Data Counting Values How Aggregates Work Counting Selectively Distinct Values Summarizing Numbers Bad Examples Scales of Measurement Aggregating Calculated Data Other Aggregate Functions Using Aggregates As Filters
17 ページ
Grouping Using the GROUP BY Clause GROUP BY vs. DISTINCT Grouping with Multiple Tables Redundant Groups Preparing Data for Aggregating Using CASE in a CTE Using a Join in the CTE Summarizing Strings Filtering Grouped Results with HAVING Using Results in a CTE Finding Duplicates Using Aggregates on Aggregates Summary Coming Up Chapter 8: Working with Tables How Tables Are Created Creating a Table Column Names Data Type Primary Keys Constraints NOT NULL UNIQUE DEFAULT CHECK
18 ページ
Foreign Keys Indexes Adding Rows to a Table Deleting Rows from a Table Adding More Rows Updating Rows Altering the Table DML in Real Life Security Front-End Software Summary Data Types Constraints Foreign Keys Indexes Manipulating Data Chapter 9: Set Operations Unions Selective Unions SELECT Clauses Must Be Compatible Only Column Names from the First SELECT Statement Are Used Sorting Results Intersections Differences Some Tricks with Set Operations
19 ページ
Comparing Results Virtual Tables Mixing Aggregates Summary Appendix 1: Differences Between SQL Dialects Appendix 2: A Crash Course in PDO Appendix 3: Additional Notes Index
20 ページ
About the Author Mark Simon has been involved in training and education since the beginning of his career. He started as a teacher of mathematics but soon moved into IT consultancy and training because computers are much easier to work with than high school students. He has worked with and trained in several programming and coding languages and currently focuses mainly on web development and database languages. When not involved in work, you will generally find him listening to or playing music, reading, or just wandering about.
コメント (0)
まだコメントはありません。最初のコメントを書きましょう！