第1页 / 共133页
第2页 / 共133页
第3页 / 共133页
第4页 / 共133页
第5页 / 共133页
第6页 / 共133页
第7页 / 共133页
第8页 / 共133页
Getting-Started-with-SQL-A-Hands-On-Approach-for-Beginners.pdf
Copyright
Table of Contents
Foreword
Preface
How to Use This Book
Conventions Used in This Book
Using Code Examples
Safari® Books Online
How to Contact Us
Acknowledgments
Chapter 1. Why Learn SQL?
What Is SQL and Why Is It Marketable?
Who Is SQL For?
Chapter 2. Databases
What Is a Database?
Exploring Relational Databases
Why Separate Tables?
Choosing a Database Solution
Chapter 3. SQLite
What Is SQLite?
SQLiteStudio
Importing and Navigating Databases
Chapter 4. SELECT
Retrieving Data with SQL
Expressions in SELECT Statements
Text Concatenation
Summary
Chapter 5. WHERE
Filtering Records
Using WHERE on Numbers
AND, OR, and IN Statements
Using WHERE on Text
Using WHERE on Booleans
Handling NULL
Grouping Conditions
Summary
Chapter 6. GROUP BY and ORDER BY
Grouping Records
Ordering Records
Aggregate Functions
The HAVING Statement
Getting Distinct Records
Summary
Chapter 7. CASE Statements
The CASE Statement
Grouping CASE Statements
The “Zero/Null” CASE Trick
Summary
Chapter 8. JOIN
Stitching Tables Together
INNER JOIN
LEFT JOIN
Other JOIN Types
Joining Multiple Tables
Grouping JOINs
Summary
Chapter 9. Database Design
Planning a Database
The SurgeTech Conference
ATTENDEE
COMPANY
PRESENTATION
ROOM
PRESENTATION_ATTENDANCE
Primary and Foreign Keys
The Schema
Creating a New Database
CREATE TABLE
Setting the Foreign Keys
Creating Views
Summary
Chapter 10. Managing Data
INSERT
Multiple INSERTs
Testing the Foreign Keys
DELETE
TRUNCATE TABLE
UPDATE
DROP TABLE
Summary
Chapter 11. Going Forward
Appendix A. Operators and Functions
Appendix A1 – Literal Expression Queries
Appendix A2 – Mathematical Operators
Appendix A3 – Comparison Operators
APPENDIX A4 – Logical Operators
APPENDIX A5 – Text Operators
APPENDIX A6 – Common Core Functions
APPENDIX A7 – Aggregate Functions
APPENDIX A8 – Date and Time Functions
Date Functions
Time Functions
Date/Time Functions
Appendix B. Supplementary Topics
APPENDIX B1 – Further Topics of Interest
APPENDIX B2 – Improving Performance with Indexes
Appendix B3 – Transactions
Index
About the Author
Getting Started with
SQLA HANDS-ON APPROACH
FOR BEGINNERS
Thomas Nield
Getting Started with SQL
A Hands-on Approach for Beginners
Thomas Nield
Boston
Getting Started with SQL
by Thomas Nield
Copyright © 2016 Thomas Nield. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472.
O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are
also available for most titles (http://safaribooksonline.com). For more information, contact our corporate/
institutional sales department: 800-998-9938 or corporate@oreilly.com.
Editor: Shannon Cutt
Production Editor: Shiny Kalapurakkel
Copyeditor: Jasmine Kwityn
Proofreader: Rachel Head
February 2016:
First Edition
Revision History for the First Edition
2016-02-08: First Release
Indexer: Ellen Troutman-Zaig
Interior Designer: David Futato
Cover Designer: Randy Comer
Illustrator: Rebecca Demarest
See http://oreilly.com/catalog/errata.csp?isbn=9781491938614 for release details.
The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. Getting Started with SQL, the cover
image, and related trade dress are trademarks of O’Reilly Media, Inc.
While the publisher and the author have used good faith efforts to ensure that the information and
instructions contained in this work are accurate, the publisher and the author disclaim all responsibility
for errors or omissions, including without limitation responsibility for damages resulting from the use of
or reliance on this work. Use of the information and instructions contained in this work is at your own
risk. If any code samples or other technology this work contains or describes is subject to open source
licenses or the intellectual property rights of others, it is your responsibility to ensure that your use
thereof complies with such licenses and/or rights.
978-1-491-93861-4
[LSI]
Table of Contents
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix
1. Why Learn SQL?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
What Is SQL and Why Is It Marketable? 1
Who Is SQL For? 2
2. Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
What Is a Database? 3
Exploring Relational Databases 3
Why Separate Tables? 4
Choosing a Database Solution 5
Lightweight Databases 5
Centralized Databases 6
3. SQLite. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9
What Is SQLite? 9
SQLiteStudio 9
Importing and Navigating Databases 10
4. SELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Retrieving Data with SQL 19
Expressions in SELECT Statements 23
Text Concatenation 27
Summary 28
iii
5. WHERE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Filtering Records 29
Using WHERE on Numbers 30
AND, OR, and IN Statements 31
Using WHERE on Text 32
Using WHERE on Booleans 34
Handling NULL 34
Grouping Conditions 36
Summary 37
6. GROUP BY and ORDER BY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
39
Grouping Records 39
Ordering Records 41
Aggregate Functions 42
The HAVING Statement 45
Getting Distinct Records 46
Summary 46
7. CASE Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
The CASE Statement 47
Grouping CASE Statements 48
The “Zero/Null” CASE Trick 49
Summary 52
8. JOIN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Stitching Tables Together 53
INNER JOIN 55
LEFT JOIN 58
Other JOIN Types 61
Joining Multiple Tables 61
Grouping JOINs 63
Summary 66
9. Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Planning a Database 67
The SurgeTech Conference 69
ATTENDEE 69
COMPANY 69
PRESENTATION 70
ROOM 70
PRESENTATION_ATTENDANCE 70
Primary and Foreign Keys 70
iv
|
Table of Contents
The Schema 71
Creating a New Database 73
CREATE TABLE 76
Setting the Foreign Keys 84
Creating Views 86
Summary 89
10. Managing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
91
INSERT 91
Multiple INSERTs 93
Testing the Foreign Keys 93
DELETE 94
TRUNCATE TABLE 94
UPDATE 95
DROP TABLE 95
Summary 95
11. Going Forward. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
A. Operators and Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
B. Supplementary Topics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Table of Contents
|
v
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
