Mysql For Beginners

MySQL for Beginners | MySQL AB 2008
1
MySQL for Beginners MySQL for Beginners
MySQL AB
Training Services
[email protected]
http://www.mysqI.com/
Revision 1.5
2
Course Objectives (1/2)
List the features and benefits of MySQL
Find information about products, support and training
nstall and start the MySQL server
Explain the basics of relational databases
Distinguish the SQL Language and MySQL
Extensions
Explain data/column types with regard to efficient
database design
View a database design structure/content
Create a database design using an efficient structure
Extract basic database information using the SELECT
statement
3
Course Objectives (2/2)
Troubleshoot syntax and understand typical warnings
and errors
Delete and modify a database
Delete or modify table row data
Query data with aggregation
Connect data from multiple tables using JON
Perform nested sub-queries
List simple functions (String, Date, Numerical)
Understand the primary methods for exporting and
importing data
Describe MySQL connectors, their major features and
differences
Explain MySQL storage engine concepts
4
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
4. DATABASE CREATION
5. BASIC QUERIES
6. DATABASE MAINTENANCE
7. DATA MANIPULATION
8. FUNCTIONS
9. JOINING TABLES
10. EXPORTING/IMPORTING DATA
11. SUBQUERIES
12. SUPPLEMENTARY INFORMATION
13. CONCLUSION
5
Chapter 1: Chapter 1:
NTRODUCTON NTRODUCTON
6
Time For IndividuaI Presentations!
What is your background?
Your name and your organization
How does your organization use
MySQL?
Why are you here?
What do you expect from the
course?
What do you want to learn?
Have experience using MySQL?
Have experience from other SQL
dialects?
Have installed MySQL yourself?
Use MySQL under Linux?
Windows? Solaris? MacOSX?
Other?
Use MySQL 3.21? 3.22? 3.23?
4.0? 4.1? 5.0?
Use MySQL with PHP? Perl?
Java? C? ODBC? Others?
Are you MySQL Certified? Which
certifications?
Introduction
7
PracticaI CheckIist
Participant list -- email addresses -- checkup email
Breaks -- timing, duration
Where are the toilets/restrooms?
Lunch arrangements
Time allotted for exercises -- Solutions
When does the day end? Can you stay longer?
When do the days begin? Can you come earlier?
Evaluation forms
Introduction
8
SIide Format (Topic)
Presentation sub-topics
More sub-topic information
mysql> mysql -h training.mysql.com -u wld world
1.1 Features and Benefits of MySQL Introduction
Topics and
Sub-Topics,
presented
in detaiI
by instructor,
FoIIowing
Student Guide
content
Course Name
and Version
Current
Chapter titIe
Current Chapter
Section and TitIe
Code
ExampIes
9
Learning Objectives
A brief history of MySQL AB and it's philosophy
The benefits of using MySQL
The MySQL Business Model
Products and professional services available from MySQL AB
Operating Systems currently supported
Requirements for interfacing with MySQL
How the Client-Server model works
Navigation of the MySQL website
How to find information about products, support and training
How to install MySQL
How to start MySQL using the command line client
Introduction
10
MySQL AB: The Big Picture
Relational Database
Management System Program
Suite
World's most popular Open
Source database
Fastest growing with more than
11 million installations
Based in Sweden
Strong open source supporter
MySQL is instaIIed on every
continent in the worId
(Yes, even Antarctica!)
Introduction
MySQL Mission:
To make superior database software available and affordable to all.
1.1 Features and Benefits of MySQL
11
Top 10 Reasons To Use MySQL (1/2)
1. Scalability and Flexibility
2. High Performance
3. High Availability
4. Robust Transactional Support
5. Web and Data Warehouse
Strengths
12
Top 10 Reasons To Use MySQL (2/2)
6. Strong Data Protection
7. Comprehensive Application
Development
8. Management Ease
9. Open Source Freedom and
24 x 7 Support
10. Lowest Total Cost of
Ownership
13
You Are in Good Company!
Just a few of our many awesome partners.
AUF (Agence Universitaire de la Francophonie)
Chicago Mercantile Exchange
HypoVereinsbank
Finnish National Public Health
nstitute
UNCEF
Yamaha
Epson
Apple
Motorola
BBC
The Weather Channel
Nokia
PHProjekt
Introduction
Viasuisse
Axfood AB
Macy's
Orbitz
1.1 Features and Benefits of MySQL
14
MySQL Database Products
Enterprise Server
Enterprise-grade database
Community Server
Database server for open source developers
Embedded Database
Database server for OEMs/SVs to bundle cost-effectively
Cluster
Fault tolerant database clustering architecture
1.2 MySQL Products Introduction
15
MySQL Enterprise (1/2)
Enterprise Server
Enterprise-grade database
Exclusive to Enterprise subscribers
MySQL Monitor
Automatic MySQL software update/issue notification
Notification of changes with associated software/systems
24/7 Product Support
Direct access to support team
Introduction 1.2 MySQL Products
16
MySQL Enterprise (2/2)
Network Enterprise Dashboard
17
MySQL GUI TooIs
MySQL Migration Toolkit
Migration GU Wizard
MySQL Administrator
Administration console
MySQL Query Browser
Create databases, execute and optimize SQL queries
18
MySQL Drivers
MySQL Connector/ODBC
ODBC database AP on Windows and UNX
MySQL Connector/J
Java Driver that converts JDBC
MySQL Connector/Net
ADO.NET access from the .NET
MySQL Connector/MXJ
MBean to J2EE
MySQL Connector/PHP
PHP on Windows
19
MySQL Services
MySQL Training
Comprehensive set of MySQL training courses
MySQL Certification
High quality certification for MySQL Developers and
Database Administrators
MySQL Consulting
Full range of consulting services from start-up to optimization
MySQL Support
Community
Enterprise (and other levels of purchased support)
Introduction 1.3 MySQL Services
20
How Does the MySQL Community Work?
Introduction 1.4 Community vs. Enterprise
MySQL AB MySQL Community
New Ideas
Bug Reports.
HeIp, Forums.
Spread MySQL GoodwiII.
Code Contributions.
Open Source
Database
Server
Minimum Two
Binaries/Year
Fixes/Features
Constant Source
Code Drops
Free Mgmt
TooIs/Connectors
HeIp, Forums.
21
How Does MySQL Enterprise Work?
Introduction
$"
Enterprise $erver
$"
Communit $erver
Communit /
Third Part
Development
Design/DeveIopment
Community
Recommendations
Community Server Code
Contributions - Direct
Community Code
Contributions MySQL
Forge
Third party contributions
Community Purpose Enterprise Grade
Delivery of Community
and Customer
Recommendations
Acceptance/Rejection of
Community Code
Contributions
Community Testing and
Validation
"As Needed Release
Schedule, including Bug
Fixes and New Features
Selected Feature Set for
Enterprise nstallations
Predictable Service Pack
Schedule
Strong nternal Quality
Assurance Testing
External Quality
Assurance Testing
Certified for Popular
Platforms and
Applications
Formal Technical
Support
Many Ideas /
Submissions
SeIected Submissions
Become Features
Mature Features
OnIy
1.4 Community vs. Enterprise
22
Certification Program Overview
1.5 MySQL Certification Program
MySQL certifications available
Certified MySQL Associate (CMA)
Certified MySQL 5.0 Developer (CMDEV)
Certified MySQL Database Administrator (CMDBA)
Certified MySQL Cluster DBA (CMCDBA)
Certification web page
Exam administration
Pearson VUE
Introduction
23
DeveIoper (DEV) Path
1.6 Training CurricuIum Paths Introduction
DeveIoping
AppIications
with MySQL
and PHP
DeveIoping
AppIications
with MySQL
and PHP
/,8
MySQL for
DeveIopers
MySQL for
DeveIopers
/,8
Certification
Exam:
CMDEV I-II
Certification
Certification
Exam:
CMDEV I-II
Certification
I
n
t
r
o
d
u
c
t
i
o
n
I
n
t
r
o
d
u
c
t
i
o
n
MySQL 5.0 Upgrading
and New Features
(Experienced MySQL
Users)
MySQL 5.0 Upgrading
and New Features
(Experienced MySQL
Users)
/,8
MySQL for
Beginners
MySQL for
Beginners
/,8
I
n
t
e
r
m
e
d
i
a
t
e
I
n
t
e
r
m
e
d
i
a
t
e
A
d
v
a
n
c
e
d
/
S
p
e
c
i
a
I

T
o
p
i
c
s
A
d
v
a
n
c
e
d
/
S
p
e
c
i
a
I

T
o
p
i
c
s
MySQL Advanced
Stored Procedures
MySQL Advanced
Stored Procedures
/,8
Certification
Exam:
CMA
Certification
Certification
Exam:
CMA
Certification
24
Database Administrator (DBA) Path
Introduction
MySQL for
Beginners
MySQL for
Beginners
/,8
MySQL for
DBAs
MySQL for
DBAs
/,8
MySQL
Performance
Tuning
MySQL
Performance
Tuning
/,8
Certification
Exam:
CMDBA I-II
Certification
Certification
Exam:
CMDBA I-II
Certification
I
n
t
r
o
d
u
c
t
i
o
n
I
n
t
r
o
d
u
c
t
i
o
n
MySQL High
AvaiIabiIity
MySQL High
AvaiIabiIity
/,8
MySQL
CIuster
MySQL
CIuster
/,8
I
n
t
e
r
m
e
d
i
a
t
e
I
n
t
e
r
m
e
d
i
a
t
e
A
d
v
a
n
c
e
d
/
S
p
e
c
i
a
I

T
o
p
i
c
s
A
d
v
a
n
c
e
d
/
S
p
e
c
i
a
I

T
o
p
i
c
s
MySQL 5.0 Upgrading
and New Features
(Experienced MySQL
Users)
MySQL 5.0 Upgrading
and New Features
(Experienced MySQL
Users)
/,8
Certification
Exam:
CMCDBA
Certification
Certification
Exam:
CMCDBA
Certification
Certification
Exam:
CMA
Certification
Certification
Exam:
CMA
Certification
1.6 Training CurricuIum Paths
25
MySQL Website
http://www.mysqI.com/
1.7 MySQL Website Introduction
26
MySQL Community Web Page (1/2)
http://dev.mysqI.com/
Introduction 1.7 MySQL Website
27
MySQL Community Web Page (2/2)
Product Downloads
Documentation
Articles
Forums
Mailing Lists
Bugs and Feature Requests
PlanetMySQL
nd Much More!
Introduction 1.8 MySQL Website
28
The CIient/Server ModeI
Server -- the central database management program
Client -- program(s) connect to the server to retrieve or modify data
1.8 MySQL Architecture Introduction
TCP/P
TCP/P
mysqld
MySQL Server Process
CIient
Process
TCP/P
mysql
CIient
Process
PHP
Socket
ocahost)
ODBC
Windows
PHP
Linux
,
9
,
-
,
8
0
29
Communication ProtocoIs
TCP/P
Transmission Control Protocol
nternet Protocol
Unix Socket
Shared Memory
NT Pipes
Introduction 1.8 MySQL Architecture
30
Connectors and MySQL
MySQL Provides Several AP's to the Server
Officially Supported
ODBC
J
NET
Third-Party Client nterfaces
C AP Based
31
The LAMP Stack
Linux Operating System
Apache Web Server
MySQL Relational Database Management System
PHP / Perl / Python Programming Languages
Offers Many Degrees of Freedom
Go from LAMP -> WAMP (Windows)
32
MySQL Supported Operating Systems
More than 20 platforms
Control and Flexibility for Users
Currently Available for MySQL Download:
Windows (multiple)
Linux (multiple)
Solaris
FreeBSD
Mac OS/X
HP-UX
Introduction
BM AX
QNX
Novell NetWare
SCO
Open BSD
SG rix
Source Code
Special Builds
1.8 MySQL Architecture
33
InstaII and Start MySQL
nstall from the MySQL website
1.9 InstaII and Start MySQL Introduction
34
MySQL Server and Command Line CIient
Server connects during installation
Command line client can ...
Send queries
Receive query results
Introduction 1.9 InstaII and Start MySQL
35
Starting the MySQL Command Line CIient
Standard command
shell> mysql -u root -p
Enter password: <password>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version:
5.0.16-log
Type `help;' or `\h' for help. Type `\c' to clear the
buffer.
mysql>
36
MySQL Start-Up Command Line Options
Get full list by using -help option
-h hostname or IP
-u username
-ppassword
Database
< input script
> Filename
-b
Example
mysql> mysql -h training.mysql.com -u wld world <
sqlcommands.sql
37
Keyboard Editing
Keyboard directional arrows
Command history preserved during session
nterrupt a query
Full readline capabilities under Linux
Close quotes
Copy/paste into window
38
Chapter Summary
A brief history of MySQL AB and it's philosophy
The benefits of using MySQL
The MySQL Business Model
Products and professional services available from MySQL AB
Operating Systems currently supported
Requirements for interfacing with MySQL
How the Client-Server model works
Navigation of the MySQL website
How to find information about products, support and training
How to install MySQL
How to start MySQL using the command line client
Introduction
39
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
40
DATABA8E BA8C8 DATABA8E BA8C8
41
Learning Objectives
What is a Relational Database (and RDBMS)?
The structure of an RDBMS database
The use of SQL and MySQL with Relational databases
Data Definition Language
Data Manipulation Language
Database Basics
42
What is a RDBMS?
Relational Database Management System
Manages data according to a relational model
Organizes and stores data in the form of tabes
Bank example: tables for currency data, customer info, account info
Database
A collection of data
Synonymous with 'Schema'
The reationa model was invented to solve problems
with the hierarchica database model
Data logically organized as a tree
Time consuming and difficult to develop applications and perform
required operations
2.1 Basics of ReIationaI Databases Database Basics
43
Entities and ReIationships (1/2)
Entities
Things in the real world that we will store information about in
the database
Relationships
Links between entities
Tables typically store data representing one type of
entity
For instance, a bank database has a customer table
specifically for customer information
44
Entities and ReIationships (2/2)
Relationship categories
One-to-One -- Many-to-One
One-to-Many -- Many-to-Many
Relationship examples
A bank customer can have multiple accounts
The relationship between customers and accounts is one-to-many
Students can take multiple courses, and courses can be attended
by multiple students
The relationship between students and courses is many-to-many
A bank has 'business' versus 'general' customers
The relationship between business and general is one-to-one
45
RDBMS Database Structure
Data storage
Two-dimensional, columns and rows
0atabase 1 0atabase 2 0atabase 4 0atabase 5
0ata 8torage
6ode Name 6ont|nent
A8w
Norlr Arer|ca
AF0 As|a
A00 Alr|ca
6o|umn
RDBM8
or|d
Tab|es
Country
#o
Aruoa
Algrar|slar
Argo|a
46
The Use of SQL with Databases
Structured
Query
Language
Standardized language
Based on the English language (Phrases)
Categories of phrase types
DDL
DML
2.2 SQL Language and MySQL Database Basics
47
Creation and deletion of database
CREATE DATABASE ...
DROP DATABASE ...
Creation and deletion of database table
CREATE TABLE ...
DROP TABLE ...
Modification of databases and tables
ALTER DATABASE ...
ALTER TABLE ...
48
Data ManipuIation Language
Retrieve data
SELECT ... FROM <table> ...
Add new data
INSERT INTO <table> ...
Modify data
UPDATE <table> ...
Remove data
DELETE FROM <table> ...
49
MySQL Added-VaIue
Powerful extension of SQL language
Runs on many different operating systems
Flexible and secure authorization system
Supports very large databases
Very customizable
Built for speed
Free or nexpensive
Open Source and Commercial licenses available
Exceptional support available
.and more!
50
Chapter Summary
What is a Relational Databases (and RDBMS)?
The structure of an RDBMS database
The use of SQL and MySQL with Relational databases
Data Manipulation Language
Database Basics
51
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
52
DATABA8E DE8GN DATABA8E DE8GN
53
Learning Objectives
Database modeling
Using keys
The definition and usage of normalization
MySQL accepted data types and their uses
How to view a database structure
How to evaluate the database design
Database Design
54
3.1 Database ModeIing Database Design
Database ModeIing
Prior to building a database some database
structure modeling should occur
Process specifies the structure of the tables based
upon specific use of data
Entity-relational model (ERM) is the most common
model for RDBMSs
55
Structure Diagram (ERD)
Used to better visualize the content and hierarchy
of a database
Organize data into logical groups
Example of a high-level diagram of the worId
database
Database Design 3.1 Database ModeIing
Continent
Country
CountryLanguage
'worId' database
contains
City
contains
contains
56
CardinaIity Diagram (ERD) (1/2)
ndicates relationships between tables
Uses lines and specific symbols:
Database Design
One
Zero
Many
Zero or One
Zero or Many
OnIy One
One or Many
1-to-1 (or many)
1 (or many)-to-1
Many-to-Many (or 0)
1-to-1
1-to-0 (or 1)
B
A B
B
B
B
A
A
A
A
3.1 Database ModeIing
57
CardinaIity
Diagram (ERD)
(2/2)
worId database example
Database Design
world database
ER Diagram
City
(FK)
char(35) Name
(PK)
int(11)
char(20)
char(3)
int(11)
Population
District
CountryCode
D
CountryLanguage
(PK) char(30) Language
(PK)
float(4,1)
enum('T','F')
char(3)
Percentage
sOfficial
CountryCode
char(45) GovernmentForm
int(11) Population
float(10,2) GNPOld
int(11) Capital
float(10,2) GNP
float(3,1) LifeExpectancy
char(2) Code2
char(60) HeadOfState
smallint(6) ndepYear
float(10,2) SurfaceArea
char(26) Region
char(45) LocalName
enum(") Continent
char(52) Name
(PK) char(3) Code
Country
3.1 Database ModeIing
58
Keys (1/2)
Row identification
Candidate key
Column(s) that uniquely identify a row
Primary key
A candidate key that best defines exactly one unique row
Ensures that duplicate values do not occur
Cannot contain NULL
Can decrease lookup time for large databases
Foreign key
References a primary key in another table
3.2 Keys Database Design
59
Keys (2/2)
Database Design
Customer TabIe
cust_id firstname astname
1
2
3
Abby
Thompson
Jack
Guerra
Thorild
Svenson
Account TabIe
acct_id equip_cd cust_id
100
101
102
TV1
1
ST1
ENT1
start_date
103
104
105
1
2
2
2
3 ENT1
ST2
TV1
01/22/2000
06/07/2000
12/26/2004
11/09/2004
11/09/2004
03/31/1998
Equipment TabIe
equip_id name category
TV1
ST1
ENT1
Television_36
Television
Stereo_100W
Stereo
Oak_Entrtnmnt
Entrtnmnt_Ctr
EIectronics Equipment RentaI Database
Primary Key
Foreign Key
Primary Key
Foreign Key
3.2 Keys
60
NormaIization
Widely used as a guide in designing relational
databases
Eliminate redundant data
Eliminate modifications that make the data
inconsistent
Often includes a process of 'decomposition' into a
set of smaller tables
Removes multi-valued attributes and repeating groups
3.3 NormaIization Database Design
61
Advantages and Disadvantages of
NormaIizing
Advantages
ER diagrams
Compact
Joins
Optimizer
Disadvantages
Numerous tables
Database Design 3.3 NormaIization
62
ExampIe of 'worId' NormaIization (1/2)
Why Are Languages Not Added to Country Table?
Large discrepancies between numbers of languages per country
Turkey versus
-----------------------
CountryCode Language
-----------------------
TUR Arabic
TUR Kurdish
TUR Turkish
-----------------------
rows in set (0.00 sec)
Canada
-------------------------------
CountryCode Language
-------------------------------
CAN Chinese
CAN Dutch
CAN English
CAN Eskimo Languages
CAN French
CAN German
CAN Italian
CAN Polish
CAN Portuguese
CAN Punjabi
CAN Spanish
CAN Ukrainian
-------------------------------
12 rows in set (0.00 sec)
63
ExampIe of 'worId' NormaIization (2/2)
Adding language to Country would result in many
empty spaces
Extra details for languages would be extraneous
-----------------------------------------------
CountryCode Language IsOfficial Percentage
-----------------------------------------------
TUR Arabic F 1.4
TUR Kurdish F 10.6
TUR Turkish T 87.6
-----------------------------------------------
Meets goal of normalization
64
NormaI Forms
Level modification in successive manner
Numerous levels
First three are most common
First Normal Form (1NF) -- contains no repeating groups within
rows
Second Normal Form (2NF) -- normalized at the first level and
every non-key (supporting) value is dependent on a candidate key
Third Normal Form (3NF) -- normalized at the first and second
level, dependent soey on the primary key and no other non-key
(supporting) value
65
NormaIization Process ExampIe
Cooking recipes database
Data does not lend itself
to placement in a single table
Recipe Name: Preparation Time: TooIs:
Chocoate
Cake
60 minutes Oven;
Baking Pan;
ngredients: Quantity: Unit: Unit
Abbreviation:
Sugar 200 grams gr.
Flour 135 grams gr.
Cacao 45 grams gr.
Salt 1 teaspoon tsp.
Baking Soda 1 teaspoon tsp.
Butter 115 grams gr.
Egg 1 pieces pcs.
Vanilla Extract 1 teaspoon tsp.
Instructions:
1) Heat oven to 350F (175C).
2) Grease and flour an 8 inch pan.
3) Sift together flour, cacao, baking soda and
salt.
4) n a medium bowl, cream butter and
sugar until light and fluffy.
5) Add egg and vanilla and beat well.
6) Add flour and mix.
7) Put in pan, bake for 35 minutes.
Recipe Name: Preparation
Time:
TooIs:
Bueberry
Cheesecake
80 minutes Oven; Baking
Pan; Hand Mixer
8
Ingredients: Quantity: Unit: Unit
Abbreviation:
Butter 170 grams gr.
Flour 250 grams gr.
Sugar 500 grams gr.
Cream Cheese 450 grams gr.
Egg 3 pieces pcs.
Sour Cream 450 grams gr.
Vanilla Extract 1 teaspoon tsp.
Blueberries 750 grams gr.
Cornstarch 25 grams gr.
Instructions:
1) Heat oven to 350F (175C).
2) Mix butter, flour,100gram sugar and salt.
3) Bake mixture for 20 minutes in baking
dish to a crust.
4) Reduce oven heat to 325F (165C).
5) Use hand mixer to beat cream cheese,
200gram sugar until soft and creamy.
Stir in eggs one at a time. Stir in sour
cream and vanilla.
6) Pour mixture over baked crust
7) Bake for 45-55 minutes until firm to the
touch.
8) Mix blueberries, 200 gram sugar and
cornstarch at medium heat; cook until
thickened. Allow to cool.
9) Pour blueberry topping over baked
cheesecake. Put into refrigerator and chill
overnight.
66
First NormaI Form (1/2)
Putting Recipe database into 1NF
Remove repeating
groups from initial
data by splitting into
tables
Recipes
Recipe Name (1) Cooking Time
Chocolate Cake 60 minutes
Blueberry Cheesecake 80 minutes
Recipe TooIs
Recipe Name (1) TooI (2)
Chocolate Cake Oven
Chocolate Cake Baking Pan
Blueberry Cheesecake Oven
Blueberry Cheesecake Baking Pan
Blueberry Cheesecake Hand Mixer
Recipe Ingredients
Recipe Name (1) Ingredient (2) Quantity Unit Unit_Abrv
Chocolate Cake Sugar 200 grams gr.
Chocolate Cake Flour 135 grams gr.
Chocolate Cake Cacao 45 grams gr.
Chocolate Cake Salt 1 teaspoon tsp.
Chocolate Cake Baking Soda 1 teaspoon tsp.
Chocolate Cake Butter 115 grams gr.
Chocolate Cake Egg 1 pieces pcs.
Chocolate Cake Vanilla Extract 1 teaspoon tsp.
Blueberry Cheesecake Butter 170 grams gr.
Blueberry Cheesecake Flour 250 grams gr.
Blueberry Cheesecake Sugar 500 grams gr.
Blueberry Cheesecake Cream Cheese 450 grams gr.
Blueberry Cheesecake Egg 3 pieces pcs.
Blueberry Cheesecake Sour Cream 450 grams gr.
Blueberry Cheesecake Vanilla Extract 1 teaspoon tsp.
Blueberry Cheesecake Blueberries 750 grams gr.
Blueberry Cheesecake Cornstarch 25 grams gr.
#ecipe Ingredients is in 1NF but not in 2NF, because both Unit and Unit_Abrv
are dependent upon Ingredient, which is part of the key.
67
First NormaI Form (2/2)
Putting Recipe database into 1NF continued)
Recipe Instructions
Recipe Name (1) Num (2) Instruction
Chocolate Cake 1 Heat oven to 350F (175C)
Chocolate Cake 2 Grease and flour an 8 inch pan.
Chocolate Cake 3 Sift together flour, cocoa, baking soda and salt.
Chocolate Cake
4
n a medium bowl, cream butter and sugar until light and fluffy.
Add egg and vanilla and beat well.
Chocolate Cake 5 Add flour mixture and coffee, mix.
Chocolate Cake 6 Put in pan, bake for 35 minutes
Blueberry Cheesecake 1 Heat oven to 350F (175C)
Blueberry Cheesecake 2 Mix butter, flour,100gram sugar and salt
Blueberry Cheesecake 3 Bake mixture for 20 minutes in baking dish to a crust
Blueberry Cheesecake 4 Reduce oven heat to 325F (165C)
Blueberry Cheesecake
5
Beat cream cheese, 200gram sugar until soft and creamy. Stir in
eggs one at a time. Stir in sour cream, vanilla and lemon zest
Blueberry Cheesecake 6 Pour mixture over baked crust
Blueberry Cheesecake 7 Bake for 45-55 minutes until firm to the touch.
8
Mix blueberries, 200 gram sugar and cornstarch at medium
heat; cook until thickened. Allow to cool.
9
Pour blueberry topping over baked cheesecake. Put into
refrigerator and chill overnight.
68
Second NormaI Form
W Putting the Recipe Ingredients table into 2NF
Recipe Ingredients
Recipe Name (1) Ingredient (2) Quantity
Chocolate Cake Sugar 200
Chocolate Cake Flour 135
Chocolate Cake Cacao 45
Chocolate Cake Salt 1
Chocolate Cake Baking Soda 1
Chocolate Cake Butter 115
Chocolate Cake Egg 1
Chocolate Cake Vanilla Extract 1
Blueberry Cheesecake Butter 170
Blueberry Cheesecake Flour 250
Blueberry Cheesecake Sugar 500
Blueberry Cheesecake Cream Cheese 450
Blueberry Cheesecake Egg 3
Blueberry Cheesecake Sour Cream 450
Blueberry Cheesecake Vanilla Extract 1
Blueberry Cheesecake Blueberries 750
Blueberry Cheesecake Cornstarch 25
Ingredient Units
Ingredient (1) Unit Unit_Abrv
Sugar grams gr.
Flour grams gr.
Cacao grams gr.
Salt teaspoon tsp.
Baking Soda teaspoon tsp.
Butter grams gr.
Egg pieces pcs.
Vanilla Extract teaspoon tsp.
Cream Cheese grams gr.
Sour Cream grams gr.
Blueberries grams gr.
Cornstarch grams gr.
2NF 2NF
Ingredient Units is in 2NF but not in 3NF because
Unit_Abrv is transitivey dependent on Unit.
69
Third NormaI Form
W Putting the Ingredient Units table into 3NF
Unit Abbreviations
Unit Unit_Abrv
grams gr.
grams gr.
grams gr.
teaspoon tsp.
teaspoon tsp.
grams gr.
pieces pcs.
teaspoon tsp.
grams gr.
grams gr.
grams gr.
grams gr.
3NF
Ingredient Units
Ingredient (1) Unit_Abrv
Sugar gr.
Flour gr.
Cacao gr.
Salt tsp.
Baking Soda tsp.
Butter gr.
Egg pcs.
Vanilla Extract tsp.
Cream Cheese gr.
Sour Cream gr.
Blueberries gr.
Cornstarch gr.
3NF
70
NormaIized 'Recipes' Database
W Six final tables depicted in an ERD
Recipes
Recipe Name (1)
Cooking Time
Recipe Tools
Recipe Name (1)
Tool (2)
Recipe nstructions
Recipe Name (1)
Num (2)
nstruction
Recipe ngredients
Recipe Name (1)
ngredient (2)
Quantity
ngredient Units
ngredient (PK)
Unit_Abrv
Unit Abbreviations
Unit (1)
Unit_Abrv
(1) Primary Key, (2) Second column in Primary Key (composite)
71
Making InteIIigent Choices
Normalization is just common sense
Put "pen to paper and document the process
Eliminate repeating groups
Make sure each column contains only one value
Use candidate keys
Sensible column naming
Create new tables to better organize (if needed)
72
Data Types
Table columns can be one of several different types
Depends on the form of the data
Example
Recipe instruction column values are all letters, as opposed to
the instruction numbers
Four major categories
Numeric -- Binary
Character -- Temporal
ABC's of data types
Apt
Brief
Complete
3.4 Data Types Database Design
PROPER DATATYPE
USAGE IS VERY IMPORTANT!
It can make a big
performance difference.
73
Numeric Data Types
Store numeric data
nteger
Floating-Point
Fixed-Point
BT
Factors to consider with Numeric data types
Range of values the data type represents
Amount of space column values require
Column precision and scale (floating-point and fixed-point)
Whole numbers
74
Integer Data Types (1/2)
Types
TINYINT -- INT
SMALLINT -- BIGINT
MEDIUMINT
Example
WorId database, City table, PopuIation entity
Population INT(11)
Largest value output (uses 8, 11 allowed)
10500000
UNSIGNED attribute allows only positive numbers
ZEROFILL attribute replaces padding spaces with
zeros
75
Integer Data Types (2/2)
nteger data type comparison
0 to
18,446,744,073,709,551,615
-9,223,372,036,854,775,808 to
9,223,372,036,854,775,807
8 bytes
BIGINT
0 to 4,294,967,295 -2,147,483,648 to
2,147,483,647
4 bytes
INTEGER
0 to 16,777,215 -8,388,608 to 8,388,607 3 bytes
MEDIUMINT
0 to 65,535 -32,768 to 32,767 2 bytes
SMALLINT
0 to 255 -128 to 127 1 byte
TINYINT
Unsigned Signed Storage CoIumn Type
76
FIoating-Point Types
Used for approximate-value numbers
nteger, Fractional or both
Types
FLOAT
DOUBLE
May declare with precision and scale
Example
WorId database, Country table, GNP entity
GNP FLOAT(10,2)
Largest value output (uses 7, 10 allowed; 2 to right of decimal)
8510700.00
Integer Syntax:
FLOAT(M,D)
M maximum decimal digits
D digits after the decimal
77
FIoating-Point Types Summary
Type Storage Signed Range Unsigned Range
FLOAT 4 bytes
-3.402823466E+38 to -
1.175494351E-38
0 and
1.175494351E-38 to
3.402823466E+38
DOUBLE 8 bytes
-1.7976931348623157E+308
to
-2.2250738585072014E-308
0 and
2.2250738585072014E-308
to
1.7976931348623157E+308
78
Fixed-Point Data Types
Exact-value numbers
nteger, fractional or both
Types
DECIMAL
NUMERIC
Example
To represent currency values such as dollars and cents
cost DECIMAL(10,2)
Example value output
650.88
Integer Syntax:
DECIMAL(M,D)
M maximum significant digits
D digits after the decimal
79
BIT Types
Bit-field values
Column Width (M) is number of bits per value
1 to 64 bits
Example
Storing 4 and 20 bits
bit_col1 BIT(4)
bit_col2 BIT(20)
Can assign values using Numeric
expressions
Syntax:
BIT(M)
RuIe of thumb:
n8 -> 1 Byte
80
TemporaI Data Types
TME
HH:MM:SS > 12:59:02
YEAR
Two or Four digit > 2006
DATE
YYYY-MM-DD > 2006-08-04
DATETME
YYYY-MM-DD HH:MM:SS > 2006-08-04 12:59:02
TMESTAMP > 2006-08-04 12:59:02
DEFAULT CURRENT_TMESTAMP
81
TemporaI Data Type Comparison
1970 to 2069 (for YEAR(2)) 1 byte
YEAR(2)
1901 to 2155 (for YEAR(4)) 1 byte
YEAR
YEAR(4)
1970-01-01 00:00:00
to mid-year 2037
4 bytes
TIMESTAMP
1000-01-01 00:00:00 to
9999-12-31 23:59:59
8 bytes
DATETIME
-838:59:59 to 838:59:59 3 bytes
TIME
1000-01-01 to 9999-12-31 3 bytes
DATE
Range Storage Type
82
Character String Data Types (1/2)
Used for storing character strings
Sequence of characters
A character is a position in a character set (or alphabet)
Available string data types
Category Type
Text CHAR
(comparison values) 'ARCHAR
TINYTEXT
TEXT
MEDIUMTEXT
LONGTEXT
Integer ENUM
(comparison values) SET
83
Character String Data Types (2/2)
W Character sets
Every character has an associated symbol, per character set
Positions in the character set dictate sorting order
Column definitions can use a CHARSET attribute
W Collation
mysql supports several alternative sorting orders (coations)
String operations are affected by collation
Column definitions can use a COLLATE attribute
W Example
CREATE TABLE t (
c1 'ARCHAR(20) CHARACTER SET utf8,
c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs)
84
Text Category Data Types (1/2)
CHAR
Fixed-length string, always right-padded with specified spaces
CHAR (M)
VARCHAR
Variable-length string, stores the string value and length together
'ARCHAR (M)
Example
WorId database, CountryLanguage table, Language entity
Language CHAR(0)
Largest value output (uses 25, 30 allowed)
Southern Slavic Languages
85
Text Category Data Types (2/2)
TINYTEXT
Variable-length
TINYTEXT
TEXT
Variable-length
TEXT
MEDIUMTEXT
Variable-length
MEDIUMTEXT
LONGTEXT
Variable-length
LONGTEXT
86
Text Category Comparison
Type Storage Maximum Length
CHAR() characters 255 characters
'ARCHAR()
characters plus 1 or 2
bytes
65,535 characters (subject to
limitations)
TINYTEXT characters + 1 byte 255 characters
TEXT characters + 2 bytes 65,535 characters
MEDIUMTEXT characters + 3 bytes 16,777,215 characters
LONGTEXT characters + 4 bytes 4,294,967,295 characters
87
Integer Category Data Types
ENUM
ENUM -- Enumeration
Example
WorId database, Country table, Continent entity
Continent ENUM('Asia', 'Europe', 'North America',
'Africa', 'Oceana', 'Antarctica', 'South America')
Largest value output
North America, Asia, Africa, Europe, South America
Oceania, Antarctica
SET
SET -- List of string values
Example
AIIergy table, Symptom column:
Symptom SET('sneezing', 'runny nose', 'stuffy head', 'red eyes')
88
Binary String Data Types
Sequence of bytes
Binary digits (Bits) grouped in eights (octets)
Example binary uses
Compiled computer programs/applications
mage and sound files
Binary types
BINARY(M)
'ARBINARY(M)
TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB
89
Binary String Type Comparison
Type Storage Maximum Length
BINARY() M bytes 255 bytes
'ARBINARY()
M bytes plus 1 or 2
bytes
65,535 bytes (subject to limitations)
TINYBLOB L + 1 bytes 255 bytes
BLOB L + 2 bytes 65,535 bytes
MEDIUMBLOB L + 3 bytes 16,777,215 bytes
LONGBLOB L + 4 bytes 4,294,967,295 bytes
Query Performance 16.3 Binary String Data Types
90
Data Type Considerations
Data storage and retrieval is a large issue
Use appropriate data type
Use appropriate integer length
Use appropriate character length
91
The Meaning of NULL
Can set data types to allow missing values
Can be an empty query result
Conceptually has several meanings
"no value
"unknown value
"missing value
"out of range
"not applicable
Two categories
Unknown
Not Applicable
3.5 What is NULL? Database Design
92
When to Use NULL
During database design
Cases where column information is not available
Determine whether NULL values can be allowed
Can also be changed in an existing table
Example from worId
Country table contains countries with no value for this column
+LifeExpectancy+ float(,1) DEFAULT NULL
Use NOT NULL if column will not hold nulls
Ensures integrity of the data
3.5 What is NULL? Database Design
93
Viewing a Database in 5 Easy Steps
SQL statements for viewing a database
1) SHOW DATABASES
2) USE database_name
) SHOW TABLES
4) DESCRIBE table_name
5) SELECT * FROM table_name
3.6 Viewing Database Structure Database Design
94
EvaIuating Design (1/3)
Use '5 Easy Steps' to view database structure
mysql> SHOW DATABASES;
--------------------
Database
--------------------
information_schema --->
mysql
world
--------------------
Database Design
mysql> USE world;
-----------------
Tables_in_world
-----------------
city
country
countrylanguage
-----------------
3.6 Viewing Database Structure
95
mysql> SHOW TABLES;
-----------------
Tables_in_world
-----------------
city
country --->
countrylanguage
-----------------
Database Design
mysql> DESCRIBE ity;
-----------------------------------------------------------
Field Type Null Key Default Extra
-----------------------------------------------------------
ID int(11) NO PRI NULL auto_increment
Name char(5) NO NULL
CountryCode char() NO NULL
District char(20) NO NULL
Population int(11) NO 0
-----------------------------------------------------------
3.6 Viewing Database Structure
96
mysql> SELECT * FROM ity;
--------------------------------------------------------------
ID Name CountryCode District Population
--------------------------------------------------------------
1 Kabul AFG Kabol 1780000
2 Qandahar AFG Qandahar 27500
Herat AFG Herat 186800
4 Mazar-e-Sharif AFG Balkh 127800
5 Amsterdam NLD Noord-Holland 71200
6 Rotterdam NLD Zuid-Holland 5921
...
4079 Rafah PSE Rafah 92020
--------------------------------------------------------------
Database Design 3.6 Viewing Database Structure
97
Chapter Summary
Database modeling
Using keys
The definition and usage of normalization
MySQL accepted data types and their uses
How to view a database structure
How to evaluate the database design
Database Design
98
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
99
DATABA8E CREATON DATABA8E CREATON
100
Learning Objectives
How to prepare a design plan for a new database
Defining columns for a new database
To use the CREATE DATABASE statement
Column and Table Options
To use the SHOW CREATE DATABASE statement
ndexing and Constraints
To use the SHOW NDEX statement
To use the CREATE TABLE statement to add tables to
a database
Database Creation
101
Database Design PIan
4.1 Create a New Database Structure Database Creation
Database
Design Plan
Build Database
Model
(establish relationships
and data types)
List
Entities
Retrieve? Store?
Modify?
List
Entities
List
Entities
Primary Purpose
Normalization
Process
Database
Creation
Yes
No
No
Yes
Yes
No
102
Creating a Database
Create database using SQL DDL statement
mysql> CREATE DATABASE mydatabase
. the response will look similar to the following.
Query OK, 0 rows affected (0.00 sec)
MySQL naming conventions
Generally not case-sensitive
Reserved characters cannot be used in names
Names cannot have more than 64 characters
Reserved words and special characters can be used in quotes
4.2 Create a Database Database Creation
103
Creating a TabIe
General syntax for creating a table
CREATE TABLE <table_name> (
<column name> <column type> [<column options>],
[<column name> <column type> [<column options>],.,]
[, <primary key definition>]
)
Example
CREATE TABLE CountryLanguage (
CountryCode CHAR() NOT NULL,
Language CHAR(0) NOT NULL,
IsOfficial ENUM(`True', `False') NOT NULL DEFAULT `False',
Percentage FLOAT(,1) NOT NULL,
PRIMARY KEY(CountryCode, Language)
);
4.3 Creating a TabIe Database Creation
104
SHOW CREATE TABLE
Viewing exact statement used to create a table
Example
mysql> SHOW CREATE TABLE City\G
********************** 1. row ***********************
Table: City
Create Table: CREATE TABLE +City+ (
+ID+ int(11) NOT NULL auto_increment,
+Name+ char(5) NOT NULL default '',
+CountryCode+ char() NOT NULL default '',
+District+ char(20) NOT NULL default '',
+Population+ int(11) NOT NULL default '0',
PRIMARY KEY (+ID+)
) ENGINEMyISAM DEFAULT CHARSETlatin1
1 row in set (0.00 sec)
Database Creation 4.3 Creating a TabIe
105
CoIumn Options
Table must have at least one column
Add options to CREATE TABLE statement
Several options available
NULL -- DEFAULT
NOT NULL -- AUTO_INCREMENT
Example
IsOfficial ENUM(T, F) NOT NULL DEFAULT F,
PRIMARY KEY(Country, Language)
)
106
TabIe Options
Add options to CREATE TABLE statement
Several options available
ENGINE
COMMENT
DEFAULT CHARACTER SET
Example
) ENGINE MyISAM COMMENT Lists Language Spoken
107
TabIe Indexing
Original data not put in any specific order/location
Server must scan a data to find relevant rows
ndexes assist in finding rows quickly and easily
Only contain column info for locating rows
Assign ndexes when creating a table, or add later
Composite indexes
Multiple columns
108
MySQL Indexes
Commonly used indexes
PRIMARY KEY
UNIQUE
ndexes are optional and must be created by user
Example
)ENGINE MyISAM COMMENT Lists Language Spoken
Most indexes are stored
in B-trees.
109
SHOW INDEX (1/2)
View status of indexes in a table
General syntax
SHOW INDEX FROM <table>
110
SHOW INDEX (2/2)
Example
mysql> SHOW INDEX FROM CountryLanguage\G
*************************** 1. row ***************************
Table: countrylanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: countrylanguage
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 2
Column_name: Language
Collation: A
Cardinality: 984
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
111
TabIe Constraints
A restriction placed on one or more column values
of a table
Actively enforces integrity rules
Types of constraints
PRIMARY KEY
FOREIGN KEY
UNIQUE
MySQL generates indexes to enforce above
constraints
112
Further Practice: Chapter 4
Comprehensive exercises
Database Creation
113
Chapter Summary
How to prepare a design plan for a new database
Defining columns for a new database
To use the CREATE DATABASE statement
Column and Table Options
To use the SHOW CREATE DATABASE statement
ndexing and Constraints
To use the SHOW NDEX statement
To use the CREATE TABLE statement to add tables to
a database
Database Creation
114
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
115
BA8C OUERE8 BA8C OUERE8
116
Learning Objectives
To retrieve database data using the SELECT query statement
Syntax and usage of SELECT clauses: FROM, WHERE,
LMT, DSTNCT, ORDER BY
Trouble-shooting using warning and error messages
The use of SQL Modes to change interpretation of SQL syntax
Basic Queries
117
The SELECT Statement (1/2)
5.1 The SELECT Statement Basic Queries
Most commonly used command for queries
Retrieves rows from tables in a database
Returns rows in a tabular form called a 'result set'
Simple syntax
SELECT [<clause options>] <column list>
[FROM] <table_name> [<other_clauses>]
The coumn_ist is a list of column names that make
up the result set
118
The SELECT Statement (2/2)
Basic Queries
Example
mysql> SELECT Name FROM Country;
--------------------------------------
Name
--------------------------------------
Afghanistan
Netherlands
Netherlands Antilles
Albania
...
French Southern Territories
Unites States Minor Outlying Islands
--------------------------------------
5.1 The SELECT Statement
119
Basic Uses of SELECT
Basic Queries
Clauses used to yield specific results
DISTINCT
FROM
WHERE
ORDER BY
LIMIT
Syntax example
SELECT DISTINCT ;alues_to_return
FROM table_name
WHERE condition
ORDER BY how_to_sort
LIMIT row_count;
SELECT Tips
W Best to treat tabIe/databases
names as case-sensitive
W Use \c to abort a statement
W Use \G in pIace of the ;) to
return resuIts by the row
W Use of * (aII row data) can
give random resuIts and
waste resources
120
SELECT with DISTINCT (1/2)
Basic Queries
Removes duplicate rows
Example
mysql> SELECT Continent FROM Country;
---------------
Continent
---------------
Asia
Europe
North America
Europe
Africa --->
Oceania
Europe
Africa
...
Antarctica
Oceania
---------------
mysql> SELECT DISTINCT Continent
FROM Country;
---------------
Continent
---------------
Asia
Europe
North America
Africa
Oceania
South America
Antarctica
---------------
121
SELECT with DISTINCT (2/2)
Basic Queries
Treats NULL as the same
Example
mysql> SELECT i, j FROM t;
-------------
i j
-------------
1 2
1 NULL --->
1 NULL
-------------
mysql> SELECT DISTINCT i, j FROM t;
-------------
i j
-------------
1 2
1 NULL
-------------
122
SELECT with WHERE (1/4)
Basic Queries
Filters out unwanted rows
Specify column values
Example
mysql> SELECT ID, Name, District FROM City
-> WHERE Name 'New York';
--------------------------
ID Name District
--------------------------
79 New York New York
--------------------------
123
Basic Queries
Operators used with WHERE
Arithmetic
Comparison
Logical
Arithmetic
+, -, *, /, DIV, %
Comparison
<, <=, =, <=>, <> or !=, >=, >, BETWEEN
Logical
AND, OR, XOR, NOT
Additional options
IN, IS NULL, LIKE etc.
124
Basic Queries
Example
mysql> SELECT last_name, first_name, birth, state
-> FROM president
-> WHERE birth < `1750-1-1'
-> AND (state''A' OR state'MA');
---------------------------------------------
last_name first_name birth state
---------------------------------------------
Washington George 172-02-22 'A
Adams John 175-10-0 MA
Jefferson Thomas 174-04-1 'A
---------------------------------------------
row in set (0.02 sec)
125
Basic Queries
Example
mysql> SELECT ID, Name, District FROM city
-> WHERE Name IN ('New York', 'Rochester', 'Syracuse');
---------------------------
ID Name District
---------------------------
79 New York New York
871 Rochester New York
95 Syracuse New York
---------------------------
126
SELECT with ORDER BY (1/3)
Basic Queries
Will return output rows in a specific order
Example
mysql> SELECT last_name, first_name FROM president
-> ORDER BY last_name;
--------------------------
last_name first_name
--------------------------
Adams John
Adams John Quincy
Arthur Chester A.
Buchanan James
...
127
Basic Queries
Ascending order is default
Specify order with ASC and DESC
Example
mysql> SELECT Name FROM Country ORDER BY Name DESC;
----------------------
Name
----------------------
Zimbabwe
Zambia
Yugoslavia
Yemen
Western Sahara
Wallis and Futuna
'irgin Islands, U.S.
...
128
Basic Queries
Sort multiple columns simultaneously
Example
mysql> SELECT Name, Continent FROM Country
-> ORDER BY Continent DESC, Name ASC;
---------------------------------
Name Continent
---------------------------------
Argentina South America
Bolivia South America
Brazil South America
Chile South America
: : :
Uzbekistan Asia
'ietnam Asia
Yemen Asia
---------------------------------
129
SELECT with LIMIT (1/3)
Basic Queries
Specify number of rows output
Example
mysql> SELECT Name FROM Country LIMIT 8;
----------------------
name
----------------------
Afghanistan
Netherlands
Albania
Algeria
American Samoa
Andorra
Angola
----------------------
130
Basic Queries
Specify skip rows
Example
mysql> SELECT name, population FROM country LIMIT 20,8;
------------------------------------
name population
------------------------------------
Belgium 1029000
Belize 241000
Benin 6097000
Bermuda 65000
Bhutan 2124000
Bolivia 829000
Bosnia and Herzegovina 972000
Botswana 1622000
------------------------------------
131
Basic Queries
Use with ORDER BY for ordered output
Example
mysql> SELECT last_name, first_name, birth FROM president
-> ORDER BY birth LIMIT 5;
--------------------------------------
last_name first_name birth
--------------------------------------
Washington George 172-02-22
Adams John 175-10-0
Jefferson Thomas 174-04-1
Madison James 1751-0-16
Monroe Thomas 1758-04-28
--------------------------------------
132
MySQL Query Browser
5.2 MySQL Query Browser Basic Queries
GU to the MySQL server for querying and
analyzing data
Download from
MySQL website
Free and available
for Windows,
Linux and source
133
Warnings and Errors (1/2)
5.3 TroubIeshooting Basic Queries
Many resources to assist with query problems
Example
mysql> SELECT Name FROM city LIMIT;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server
version for the right syntax to use near '' at line 1
Three components of error message
MySQL Reference Manual
http://mysqI.com/<keyword>
134
Warnings and Errors (2/2)
Statements for viewing warnings and errors
SHOW WARNINGS
SHOW ERRORS
Reference manual error documentation
Correct errors and run SQL statement again
135
SQL Modes for Syntax Checking
Customize server operating mode
SET [SESSIONGLOBAL] sql_mode'mode_;alue'
View current SQL mode value
SELECT @@global.sql_mode
SELECT @@session.sql_mode
SQL mode values
ANSI
STRICY_TRANS_TABLES, STRICT_ALL_TABLES
TRADITIONAL
136
Basic Queries
137
Chapter Summary
To retrieve database data using the SELECT query statement
Syntax and usage of SELECT clauses: FROM, WHERE,
LMT, DSTNCT, ORDER BY
Trouble-shooting using warning and error messages
The use of SQL Modes to change interpretation of SQL syntax
Basic Queries
138
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
139
DATABA8E MANTENANCE DATABA8E MANTENANCE
140
Learning Objectives
To use the DROP DATABASE statement to delete a database
The issues related to dropping a database
Adding a new table to a database using SELECT
How to delete an entire table
Adding and removing columns of a table
How to modify table columns
Adding and removing index constraints
Database Maintenance
141
The DROP DATABASE Statement
6.1 DeIete an Entire Database Database Maintenance
Removes a database
Full or empty database
Returns row count which is number of tables deleted
Examples
DROP DATABASE mydb
DROP DATABASE IF EXISTS mydb
DROP DATABASE has no
UNDO feature, so be cautious
when deIeting an entire
database!
142
Adding a TabIe From an Existing TabIe
(1/2)
6.2 Maintaining TabIes Database Maintenance
Creating from values of an existing table
View original table using SHOW CREATE TABLE
Use SELECT statement with CREATE TABLE
Examples
mysql> CREATE TABLE EU_Countries
-> SELECT Name, Population * 1.5 AS NewPopulation
-> FROM Country
-> WHERE Continent 'Europe';
Records: 46 Duplicates: 0 Warnings: 0
143
Adding a TabIe From an Existing TabIe
(2/2)
New table added
mysql> SHOW TABLES;
-----------------
Tables_in_world
-----------------
city
country
countrylanguage
eu_countries
-----------------
mysql> SELECT * FROM EU_Countries;
----------------------------------------------
Name NewPopulation
----------------------------------------------
Netherlands 2796000.0
Albania 5101800.0
Andorra 117000.0
Belgium 1558500.0
Bosnia and Herzegovina 5958000.0
United Kingdom 8945100.0
: : :
Russian Federation 220401000.0
Estonia 2158800.0
----------------------------------------------
mysql> DESCRIBE EU_Countries;
----------------------------------------------------
Field Type Null Default Extra
----------------------------------------------------
NewPopulation decimal(12,1) NO 0.0
----------------------------------------------------
144
Creating a Temporary TabIe
CREATE TEMPORARY TABLE
Table kept until the client disconnects
Great way to work with summary data
Only visible to the client that created it
Does not conflict with other hosts using same data
Overrides existing table until the connection dropped
Examples
mysql> CREATE TEMPORARY TABLE EU_Countries_TEMP
-> SELECT Name, Population * 1.5 AS NewPopulation
-> FROM Country
-> WHERE Continent 'Europe';
Records: 46 Duplicates: 0 Warnings: 0
145
Copying an Existing TabIe Structure
Copying a table and it's structure
ndexes
Column options
No row data
Example
CREATE TABLE new_table LIKE old_table
146
The DROP TABLE Statement
6.1 DeIete an Entire Database Database Maintenance
Remove a table
Full or empty table
IF EXISTS to avoid error
DROP TEMPORARY TABLE
Examples
DROP TABLE table1
DROP TABLE IF EXISTS table1
DROP TEMPORARY TABLE EU_Countries_TEMP
DROP TABLE has no
UNDO feature, so be cautious
when deIeting an entire
tabIe!
147
Adding a CoIumn (1/2)
6.3 Maintaining CoIumns Database Maintenance
Use an ALTER TABLE statement with ADD
Examples
ALTER TABLE EU_Countries ADD Id INT(6) NOT NULL
Structure change
mysql> DESCRIBE EU_Countries;
---------------------------------------------------------
---------------------------------------------------------
Id int(6) NO NULL
---------------------------------------------------------
148
Adding a CoIumn (2/2)
Review row contents
mysql> SELECT * FROM EU_Countries;
-------------------------------------------
Name NewPopulation Id
-------------------------------------------
Albania 5101800.0 0
Andorra 117000.0 0
Austria 1217700.0 0
Belgium 1558500.0 0
Bulgaria 1228650.0 0
Bosnia and Herzegovina 5958000.0 0
: : : :
-------------------------------------------
149
Removing a CoIumn
Use an ALTER TABLE statement with DROP
Examples
ALTER TABLE EU_Countries DROP Id
Structure change
---------------------------------------------------------
---------------------------------------------------------
---------------------------------------------------------
150
Modifying CoIumns (1/2)
Use an ALTER TABLE statement with MODIFY
Examples
ALTER TABLE EU_Countries MODIFY NewPopulation INT(12)
UNSIGNED NOT NULL
Structure change
------------------------------------------------------------
------------------------------------------------------------
Name char(52) NO
NewPopulation int(12) unsigned NO
------------------------------------------------------------
151
Modifying CoIumns (2/2)
Review rows
---------------------------------------
name NewPopulation
---------------------------------------
Albania 5101800
Andorra 117000
Austria 1217700
Belgium 1558500
Bulgaria 1228650
Bosnia and Herzegovina 5958000
: : :
---------------------------------------
152
Adding Index Constraints (1/2)
6.4 Indexes and Constraints Database Maintenance
Use an ALTER TABLE statement with ADD INDEX
General syntax
ALTER TABLE table_name ADD INDEX index_name (index_columns)
ALTER TABLE table_name ADD UNIQUE index_name (index_columns)
ALTER TABLE table_name ADD PRIMARY KEY index_name(index_columns)
Example
ALTER TABLE City ADD INDEX Pop (Population)
153
Adding Index Constraints (2/2)
Use an SHOW CREATE TABLE to confirm
********************* 1. row ***********************
Table: city
Create Table: CREATE TABLE +city+ (
PRIMARY KEY (ÌD`),
KEY +Pop+ (+Population+)
154
Dropping Index Constraints (1/2)
Use an ALTER TABLE Statement with DROP
ALTER TABLE City DROP PRIMARY KEY
Use an SHOW CREATE TABLE to Confirm
*********************** 1. row ***********************
Table: city
KEY +Pop+ (+Population+)
155
Dropping Index Constraints (2/2)
Use an ALTER TABLE statement with DROP
Example
ALTER TABLE City DROP INDEX Pop
Use an SHOW CREATE TABLE to confirm
*************************** 1. row ***************************
Table: city
156
157
Chapter Summary
To use the DROP DATABASE statement to delete a database
The issues related to dropping a database
Adding a new table to a database using SELECT
How to delete an entire table
Adding and removing columns of a table
How to modify table columns
Adding and removing index constraints
158
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
159
DATA MANPULATON DATA MANPULATON
160
Learning Objectives
Deleting and modifying table data
Using the NSERT and REPLACE statements to add new
data to a table
Using the UPDATE statement to modify data in a table
Using the DELETE statement to remove table rows
Data ManipuIation
161
DeIete/Modify TabIe Row Data
7.1 DeIete/Modify TabIe Row Data
Row data may require changes
SAFETY TIPS
Database manipuIation can be tricky!
W Administrators shouId restrict access to onIy
required users
W Keep up-to-date backups
W Make ad hoc back-ups as needed
W Set a "Safe Updates" option
W Test queries and updates on a temporary (copy) tabIe
W AIways think in terms of rows-- entire row associated
with data wiII be affected
W Verify that the WHERE criteria returns the right rows
Data ManipuIation
162
The INSERT Statement(1/3)
Populate tables with data
INTO clause
General syntax
INSERT INTO table_name (column_list) 'ALUES(;alue_list)
Single row example
INSERT INTO used_books (author, title, edition)
'ALUES('Charles Dickens', 'A Tale of Two Cities', 'First Edition')
Row contents
-------------------------------------------------------
author title edition
-------------------------------------------------------
Charles Dickens A Tale of Two Cities First Edition
-------------------------------------------------------
Data ManipuIation
163
The INSERT Statement (2/3)
Multi-row example
INSERT INTO people (id,name,age)
'ALUES(1,'Bruce',49), (04,'Avery',21), (11,'Mackenzie',17)
Row contents
---------------------
id name age
---------------------
1 Bruce 49
04 Avery 21
11 Mackenzie 17
---------------------
Remember:
$tring and Temporal
Data Types must be encIosed
in singIe quotes.
Data ManipuIation
164
The INSERT Statement (3/3)
NULL condition
f column accepts NULL, set to NULL
f not, set to implicit default
AUTO_INCREMENT can be NULL
Data ManipuIation
165
The REPLACE Statement (1/2)
Exactly the same as INSERT
Except when it is a PRIMARY KEY or UNIQUE constraint
MySQL extension
General syntax
REPLACE INTO table_name (column_list) 'ALUES(;alue_list)
Example
REPLACE INTO people (id,name,age) 'ALUES(1,'Bruce',25)
Only useful with PRIMARY KEY or UNIQUE constraint
Data ManipuIation
166
The REPLACE Statement (2/2)
Returns sum of rows deleted and inserted
Cannot REPLACE and SELECT simultaneously
REPLACE algorithm
Data ManipuIation
167
The UPDATE Statement (1/4)
Modifies contents of existing rows
Use with SET clause for column assignments
General syntax
UPDATE table_name SET column=expression
[,column=expression,...]
[WHERE condition] [other_clauses]
Example
mysql> UPDATE Country
-> SET Population Population * 2,
-> Region 'Dolphin Country'
-> WHERE Code 'SEI';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings:0
Data ManipuIation
168
Updates can have no effect
Matches no rows
No change to column values
Differences between UPDATE and REPLACE
UPDATE requires existing row, REPLACE does not
UPDATE can change existing row, REPLACE discards row
Data ManipuIation
169
nconsistent ordering by default
Use ORDER BY and LIMIT to control order/count
Pre-update rows
mysql> SELECT * FROM people;
-------------------
id name age
-------------------
2 'ictor 21
Susan 15
-------------------
Data ManipuIation
170
Examples
UPDATE people SET idid-1
UPDATE people SET idid-1 ORDER BY id
UPDATE people SET name`'ic' WHERE name`'ictor' LIMIT 1
-------------------
id name age
-------------------
2 Susan 15 -->
1 'ictor 21
-------------------
-------------------
id name age
-------------------
1 'ictor 21 -->
2 Susan 15
-------------------
-------------------
id name age
-------------------
1 'ic 21
-------------------
Data ManipuIation
171
The DELETE Statement
Removes existing rows
FROM, WHERE, ORDER BY and LIMIT clauses
General syntax
DELETE FROM table_name [WHERE where_condition][ORDER BY.
[LIMIT row_count]
Examples
DELETE FROM people WHERE nameÈmily' LIMIT 4
DELETE FROM people WHERE nameÈmily'
ORDER BY id DESC LIMIT 4
Data ManipuIation
172
Data ManipuIation
173
Chapter Summary
Deleting and modifying table data
Using the NSERT and REPLACE statements to add new
data to a table
Using the UPDATE statement to modify data to a table
Using the DELETE statement to remove data to table rows
Data ManipuIation
174
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
175
UNCTON8 UNCTON8
176
Learning Objectives
How to group query data using aggregate functions
and the SELECT statement
The use of simple functions as specifically applied
by MySQL
String functions
Temporal functions
Numeric functions
Flow Control functions
Functions
177
Functions in MySQL Expressions
8.1 SimpIe Functions Functions
Several categories of functions
nvoked within an expression
General function syntax
function_name(<argl> [, <arg2>, ., <argn>])
Examples
SELECT NOW()
SELECT 'ERSION()
Function Tips
W Functions can be used anywhere a vaIue expression
is accepted
W CoIumns can be used as arguments with the correct
data type
W A function output can be the input of another function
W An expression with NULL aIways produces a NULL
W MathematicaI functions wiII return NULL on error
(i.e., division by zero)
178
String Functions
Perform operations on strings
Two categories
Returning numbers
Returning strings
179
Returning Numbers
Examples
mysql> SELECT CHAR_LENGTH('MySQL');
----------------------
CHAR_LENGTH('MySQL')
----------------------
5
----------------------
mysql> SELECT STRCMP('abc','def'),
-> STRCMP('def','def'),
-> STRCMP('def','abc');
---------------------------------------------------------------
STRCMP('abc','def') STRCMP('def','def') STRCMP('def','abc')
---------------------------------------------------------------
-1 0 1
---------------------------------------------------------------
mysql> SELECT INSTR('MySQL', 'SQL');
-----------------------
INSTR('MySQL', 'SQL')
-----------------------

-----------------------
180
Returning Strings (1/5)
Examples
mysql> SELECT CONCAT('A', '-', 'Z');
-----------------------
CONCAT('A', '-', 'Z')
-----------------------
A-Z
-----------------------
mysql> SELECT RE'ERSE('MySQL');
------------------
RE'ERSE('MySQL')
------------------
LQSyM
------------------
mysql> SELECT RIGHT('MySQL', );
-------------------
RIGHT('MySQL', )
-------------------
SQL
-------------------
mysql> SELECT LEFT('MySQL', );
-------------------
LEFT ('MySQL', )
-------------------
MyS
-------------------
181
Examples continued)
mysql> SELECT LOWER('MySQL');
----------------
LOWER('MySQL')
----------------
mysql
----------------
mysql> SELECT UPPER('MySQL');
----------------
UPPER('MySQL')
----------------
MYSQL
----------------
mysql> SELECT LPAD('MySQL', 15, '.');
------------------------
LPAD('MySQL', 15, '.')
------------------------
..........MySQL
------------------------
mysql> SELECT RPAD('MySQL', 14, '_.');
-------------------------
RPAD('MySQL', 14, '_.')
-------------------------
MySQL_._._._._
-------------------------
182
Examples continued)
mysql> SELECT TRIM(' MySQL ') AS str;
-------
str
-------
MySQL
-------
mysql> SELECT TRIM(LEADING 'Q' FROM 'QQQMySQLQQQ');
--------------------------------------
TRIM(LEADING 'Q' FROM 'QQQMySQLQQQ')
--------------------------------------
MySQLQQQ
--------------------------------------
183
Examples continued)
mysql> SELECT SUBSTRING('MySQL', );
-----------------------
SUBSTRING('MySQL', )
-----------------------
SQL
-----------------------
mysql> SELECT SUBSTRING('MySQL', 2, 2);
--------------------------
SUBSTRING('MySQL', 2, 2)
--------------------------
yS
--------------------------
184
Examples continued)
mysql> SELECT SUBSTRING_INDEX('[email protected]', '@', 1);
-----------------------------------------------
SUBSTRING_INDEX('[email protected]', '@', 1)
-----------------------------------------------
training
-----------------------------------------------
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-------------------------------------------
SUBSTRING_INDEX('www.mysql.com', '.', -2)
-------------------------------------------
mysql.com
-------------------------------------------
185
TemporaI Functions
Time and date
Several forms of description
Generated in many ways
Copying existing
Execute built-in function
Build a string representation
Date components
Type: DefauIt Format:
DATE YYYY-MM-DD
TIME HH:MM:SS
DATETIME YYYY-MM-DD HH:M:SS
TIMESTAMP YYYY-MM-DD HH:M:SS
YEAR YYYY
186
TemporaI Function Types
NOW()
CURDATE()
CURTIME()
YEAR(date_expression>)
MONTH(date_expression> )
DAYOFMONTH(date_expression> ),
DAY(date_expression> )
DAYNAME(date_expression> )
HOUR(time_expression> )
MINUTE(time_expression> )
SECOND(time_expression> )
187
Extracting TemporaI Data (1/2)
Examples
mysql> SELECT CURDATE(), CURTIME(), DAYNAME(NOW());
---------------------------------------
CURDATE() CURTIME() DAYNAME(NOW())
---------------------------------------
2006-06-09 17:55:40 Friday
---------------------------------------
mysql> SELECT NOW(), NOW() INTER'AL 5 DAY;
---------------------------------------------
NOW() NOW() INTER'AL 5 DAY
---------------------------------------------
2006-06-09 18:02:5 2006-06-14 18:02:5
---------------------------------------------
188
Extracting TemporaI Data (2/2)
Example
mysql> SELECT NOW(), DATE_FORMAT(NOW(), '%W the %D of %M');
------------------------------------------------------------
NOW() DATE_FORMAT(NOW(), '%W the %D of %M')
------------------------------------------------------------
2004-04-0 11:59:15 Friday the 0th of April
------------------------------------------------------------
189
Numeric Functions (1/2)
Mathematical operations
Examples
mysql> SELECT ABS(-42), ABS(42);
-------------------
ABS(-42) ABS(42)
-------------------
42 42
-------------------
mysql> SELECT SIGN(-42), SIGN(-1), SIGN(0), SIGN(1), SIGN(42);
-------------------------------------------------
SIGN(-42) SIGN(-1) SIGN(0) SIGN(1) SIGN(42)
-------------------------------------------------
-1 -1 0 1 1
-------------------------------------------------
190
Numeric Functions (2/2)
More numeric functions
TRUNCATE(number>, decimals>)
FLOOR(number>)
CEILING(number>)
ROUND(number>)
Mathematical functions
Geometry
Trigonometry
Other
191
FIow ControI Functions (1/3)
Choose between different values based on
the result of an expression
mysql> SELECT IF(1 > 0, 'YES', 'NO');
------------------------
IF(1 > 0, 'YES', 'NO')
------------------------
YES
------------------------
True
FaIse
192
CASE/WHEN provides branching flow control
General syntax
CASE ;alue
WHEN <compare_;alue> THEN <result>
[WHEN <compare_;alue> THEN <result> ...]
[ELSE <result>]
END
...and...
CASE
WHEN <condition> THEN <result>
[WHEN <condition> THEN <result> ...]
[ELSE <result>]
END
193
Examples
mysql> SELECT CASE WHEN 1 THEN 'one'
-> WHEN 2 THEN 'two' ELSE 'more' END;
'more'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
'true'
mysql> SELECT CASE BINARY 'B`
-> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
NULL
SELECT pet_name, owner, pet_gender,
CASE
WHEN pet_gender 'm' THEN 'Boy'
WHEN pet_gender 'f' THEN 'Girl'
ELSE 'Unknown'
END
FROM pet_info
ORDER BY pet_gender;
194
Why Use Aggregate Functions? (1/2)
8.2 Grouping with Functions Functions
Summary functions
Perform summary operations on a set of values
Returns Single Value Based on Group of Values
Only NON NULL
195
Why Use Aggregate Functions? (2/2)
Aggregate function types
MIN()
MAX()
SUM()
AVG()
COUNT()
GROUP_CONCAT()
Examples
mysql> SELECT COUNT(*)
-> FROM Country;
----------
COUNT(*)
----------
29
----------
mysql> SELECT COUNT(Capital)
-> FROM Country;
----------------
COUNT(Capital)
----------------
22
----------------
AND
196
Grouping with SELECT...GROUP BY (1/2)
Use GROUP BY for sub-group
Based on values on one or more columns of rows
Example
mysql> SELECT Continent, A'G(Population)
-> FROM Country
-> GROUP BY Continent;
--------------------------------
Continent A'G(Population)
--------------------------------
Asia 72647562.7451
Europe 15871186.9565
North America 105864.8649
Africa 152541.045
Oceania 1085755.571
Antarctica 0.0000
South America 24698571.4286
--------------------------------
197
Grouping with SELECT...GROUP BY (2/2)
GROUP_CONCAT()
Example
mysql> SELECT GovernmentForm, GROUP_CONCAT(Name) AS Countries
-> FROM Country
-> WHERE Continent 'South America'
-> GROUP BY GovernmentForm\G
*************************** 1. row ***************************
GovernmentForm: Dependent Territory of the UK
Countries: Falkland Islands
*************************** 2. row ***************************
GovernmentForm: Federal Republic
Countries: Argentina,'enezuela,Brazil
*************************** . row ***************************
GovernmentForm: Overseas Department of France
Countries: French Guiana
*************************** 4. row ***************************
GovernmentForm: Republic
Countries:Chile,Uruguay,Suriname,Peru,Paraguay,Bolivia,Guyana,
Ecuador,Colombia
198
GROUP BY...HAVING
Eliminates rows based on aggregate values
Only for comparisons against aggregated values
Example
mysql> SELECT Continent, SUM(Population) AS pop
-> FROM Country
-> GROUP BY Continent
-> HA'ING SUM(Population) > 100000000;
---------------------------
Continent pop
---------------------------
Asia 705025700
Europe 70074600
North America 48299000
Africa 784475000
South America 45780000
---------------------------
199
GROUP BY...WITH ROLLUP (1/2)
Multiple levels of summary values
Example
mysql> SELECT Continent, SUM(Population) AS pop
-> FROM Country
-> GROUP BY Continent WITH ROLLUP;
---------------------------
Continent pop
---------------------------
Asia 705025700
Europe 70074600
North America 48299000
Africa 784475000
Oceania 0401150
Antarctica 0
South America 45780000
NULL 6078749450
---------------------------
200
GROUP BY...WITH ROLLUP (2/2)
Super aggregate operation
Example
mysql> SELECT Continent, A'G(Population) AS avg_pop
-> FROM Country
-> GROUP BY Continent WITH ROLLUP;
------------------------------
Continent avg_pop
------------------------------
Asia 72647562.7451
Europe 15871186.9565
North America 105864.8649
Africa 152541.045
Oceania 1085755.571
Antarctica 0.0000
South America 24698571.4286
NULL 2544098.1172
------------------------------
201
IGNORE SPACE
Example
mysql> SELECT PI ();
ERROR 105 (42000): FUNCTION world.PI does not exist
mysql> SET sql_mode 'IGNORE_SPACE';
mysql> SELECT PI ();
----------
PI()
----------
.14159
----------
202
Functions
203
Chapter Summary
How to group query data using aggregate functions and the
SELECT statement
The use of simple functions as specifically applied by MySQL
String functions
Temporal functions
Numeric functions
Flow Control functions
Functions
204
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
205
ONNG TABLE8 ONNG TABLE8
206
Learning Objectives
How to combine data from multiple tables, using the JON
keyword
To describe the concept of a JON
OUTER and NNER Joins
Joining TabIes
207
Combining MuItipIe TabIes (1/2)
9.1 Combining MuItipIe TabIes
A join is used to combine tables for a query
Joins data in one table with data in another table
Joining TabIes
208
Combining MuItipIe TabIes (2/2)
Tables to be joined
table1
--------
i1 c1
--------
1 a
2 b
c
--------
Joined tables
SELECT * FROM table1, table2;
----------------
i1 c1 i2 c2
----------------
1 a 2 c
2 b 2 c
c 2 c
1 a b
2 b b
c b
1 a 4 a
2 b 4 a
c 4 a
----------------
table2
--------
i2 c2
--------
2 c
b
4 a
--------
AND
Joining TabIes
209
Three Categories of Joins
Cross join
Combines a rows from one table with a rows of another table
nner join
matching rows from two tables
Outer join
matching and non-matching rows from two tables
Joining TabIes
210
Inner Joins
dentifies combinations of matching rows
Two different types of syntax
Comma separated
INNER JOIN Keywords
Joining TabIes 9.2 Inner Joins
211
Comma Joins
List tables to be joined with a comma separator
Two separate queries can be joined into one
mysql> SELECT Code, Name, Language
-> FROM Country, CountryLanguage
-> WHERE Continent'Africa'
-> AND Code CountryCode;
--------------------------------------
Code Name Language
--------------------------------------
DZA Algeria Arabic
DZA Algeria Berberi
AGO Angola Ambo
AGO Angola Chokwe
...
BEN Benin Adja
BEN Benin Aizo
...
BWA Botswana Khoekhoe
BWA Botswana Ndebele
...
212
TabIe Name AIiases
Table reference can be aliased
tb_name AS aias_name
tb_name aias_name
Examples
mysql> SELECT t1.Name, t2.CountryCode
-> FROM Country AS t1, City AS t2
-> WHERE t1.Name t2.Name;
-------------------------
Name CountryCode
-------------------------
Djibouti DJI
Mexico PHL
Gibraltar GIB
Armenia COL
Kuwait KWT
Macao MAC
San Marino SMR
Singapore SGP
-------------------------
-> FROM Country t1, City t2
-> WHERE t1.Name t2.Name;
#
213
INNER JOIN Keywords
INNER JOIN replaces the comma separator
FROM clause
Example
-> FROM Country AS t1 INNER JOIN City AS t2
-> ON t1.Name t2.Name;
-------------------------
Name CountryCode
-------------------------
Djibouti DJI
Mexico PHL
Gibraltar GIB
Armenia COL
Kuwait KWT
Macao MAC
San Marino SMR
Singapore SGP
-------------------------
-> FROM Country AS t1
-> INNER JOIN City AS t2
-> USING(Name);
#
214
JOIN Keyword
Equivalent to INNER JOIN
Example
mysql> SELECT COUNT(City.Name)
-> FROM City
-> JOIN Country
-> ON CountryCode Code
-> WHERE Continent 'South America';
------------------
COUNT(City.Name)
------------------
470
------------------
ON condition -> How
WHERE clause -> Which
215
OUTER JOIN Keywords
Finds tables with and without matching rows
LEFT JOIN
RIGHT JOIN
Example ->
mysql> SELECT Name, Language
-> FROM Country
-> LEFT JOIN CountryLanguage
-> ON Code CountryCode;
-----------------------------------------------
Name Language
-----------------------------------------------
Aruba Dutch
Aruba English
Aruba Papiamento
Aruba Spanish
Afghanistan Balochi
...
Antarctica NULL
French Southern territories NULL
Antigua and Barbuda Creole English
Antigua and Barbuda English
Australia Arabic
Australia Canton Chinese
Australia English
Australia German
...
Joining TabIes 9.3 Outer Joins
216
LEFT JOIN
Comparison for join based on the first (or eft) table
Use WHERE clause to find mismatches
LEFT JOIN example
-> FROM Country
-> LEFT JOIN CountryLanguage
-> ON Code CountryCode
-> WHERE CountryCode IS NULL;
--------------------------------------------------------
Name Language
--------------------------------------------------------
Antarctica NULL
Bouvet Island NULL
British Indian Ocean Territory NULL
South Georgia and the South Sandwich Islands NULL
Heard Island and McDonald Islands NULL
French Southern territories NULL
--------------------------------------------------------
217
RIGHT JOIN
Roles of tables reversed from LEFT JOIN
Example
-> FROM Country
-> RIGHT JOIN CountryLanguage
-> ON Code CountryCode
-> WHERE CountryCode IS NULL
Empty set (0.00 sec)
218
OUTER JOIN: USING and NULL
USING
Single/Multiple columns must exist in both tables
. a LEFT JOIN b USING (c1,c2,c)
NULL
No matching row for right table of LEFT JOIN gives NULL result
Used to find rows with no counterpart in other table
Example
SELECT table1.id * FROM table1
LEFT JOIN table2
ON table1.idtable2.id
WHERE table2.id IS NULL
219
Joining TabIes
220
Chapter Summary
How to combine data from multiple tables, using the JON
keyword
To describe the concept of a JON
OUTER and NNER Joins
Joining TabIes
221
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
222
EXPORTNG AND EXPORTNG AND
MPORTNG DATA MPORTNG DATA
223
Learning Objectives
How to export data using a query
How to export using the 'mysqldump' database backup client
How to import data with MySQL statement files
How to import using the 'mysqlimport' data
To import data with Non-MySQL command files
To import data using ODBC
Exporting/Importing Data
224
Exporting Data
10.1 Exporting Data Exporting/Importing Data
Back ups are important!
Back up principles
Perform regularly
Consistent/Meaningful naming scheme
Expire back up files
nclude with regular system backups
Copying databases
From one server to another
Between RDBMS's
225
Export with a Query (1/2)
SELECT with INTO OUTFILE
Writes result set directly into a file
Example
SELECT * INTO OUTFILE Country.txt FROM Country
INTO OUTFILE changes SELECT operation
File written to server host, not to the executing client
Data is written to a new file only
User that executes the statement must have FILE privilege
File is created with filesystem access permissions
File contains one line per row select by the statement
10.1 Exporting Data
226
Export with a Query (2/2)
Create CSV format text file
SELECT * INTO OUTFILE '/tmp/data-out.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r'
FROM t
10.1 Exporting Data
227
Export with mysqldump (1/2)
MySQL utility to export (dump) table contents
Full structure
Data only
Table structure only
n standard format
MySQL specifics for optimized speed
Compressed
Three ways to invoke mysqIdump
mysqldump [options] db_name [tables]
mysqldump [options] --databases db_namel
[db_name2 db_name3...]
mysqldump [options] --all-databases
10.1 Exporting Data
228
Export with mysqldump (2/2)
Export a database .
mysqldump world
Export multiple tables .
mysqldump world City Country
Export multiple databases .
mysqldump --all-databases (or -A)
mysqldump --databases world db2
If you do not specify a
tabIe name,
the entire database
wiII be dumped.
10.1 Exporting Data
229
mysqldump Options
W --help
W --opt
W -C (--compress)
W --tabdir_name
W -d (--no-data)
W -X (--xml )
W --compatiblename
Exporting/Importing Data 10.1 Exporting Data
230
Dumping to a Text FiIe (1/2)
W Exporting database file
mysqldump guestdb > guestdb.txt
W Exporting table file
mysqldump guestdb guestTbl > guestdb.txt
W Exporting and dropping existing table
mysqldump -p --userusername --add-drop-table
guestdb guestTbl
Enter password: ********
231
Dumping to a Text FiIe (2/2)
W Text file contents
guestdb.txt
# MySQL dump 8.11
#
# Host: localhost Database: guestdb
#--------------------------------------------------------
# Server version .2.28-gamma
#
# Table structure for table 'guestTbl'
#
DROP TABLE IF EXISTS guestTbl;
CREATE TABLE guestTbl (
msgID int(11) NOT NULL auto_increment,
name varchar(0),
email varchar(0),
msgFrom varchar(0),
msgDate timestamp(14),
msgBody text,
PRIMARY KEY (msgID)
);
#
# Dumping data for table 'guestTbl'
#
INSERT INTO guestTbl 'ALUES (1,'Test User1','[email protected]','Smallville USA'
,20010101182157,'This is the body for message 1.');
INSERT INTO guestTbl 'ALUES (2,'Test User2','[email protected]','Smallville USA
',2001010118252,'This is the body for message 2.');
INSERT INTO guestTbl 'ALUES (,'Test User','[email protected]','Smalltown USA'
,20010101182451,'This is the body for message .');
INSERT INTO guestTbl 'ALUES (4,'Test User4','[email protected]','Smallcity USA'
,20010101182519,'This is the body for message 4.');
232
Importing with SQL Statement FiIes
W mporting table data using mysql command
Example
mysql -u root -p world < world.sql
W Loading the data file in mysql
Example
SOURCE C:/files/world.sql
10.2 Importing Data Exporting/Importing Data
ShouId not import over
existing database fiIe
with same name.
233
Importing with LOAD DATA INFILE (1/3)
W Tab delimited or comma separated files
W Characteristics you need to know about input file
W Syntax
LOAD DATA [LOCAL] INFILE 'file_name'
[IGNORE REPLACE]
INTO TABLE table_name
format_specifiers
[IGNORE n LINES]
[(column_list)]
[SET (assignment_list)]
Exporting/Importing Data 10.2 Importing Data
234
W Simple syntax example
LOAD DATA INFILE 'file_name' INTO TABLE table_name
W CSV example
LOAD DATA INFILE '/tmp/data.txt' INTO TABLE t
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r'
Exporting/Importing Data 10.2 Importing Data
235
MySQL assumptions
LOAD DATA INFILE clauses control.
Skipping data file lines
Loading specific table columns
Skipping or transforming column values
Control of duplicate records
Tracking of records
Control privileges
Data file format specification
mporting NULL values
10.2 Importing Data
236
Import with mysqlimport
MySQL utility to load data files into tables
Command line interface to LOAD DATA INFILE
General syntax
mysqlimport options db_name input_file ...
Matches file name with table name
Tables must already exist
10.2 Importing Data
237
mysqlimport Options (1/2)
--help
--lines-terminated-bystring
--fields-terminated-bystring
--fields-enclosed-bychar
--ignore or -replace
--local
10.2 Importing Data
238
mysqlimport Options (2/2)
Examples
mysqlimport --lines-terminated-by"\r\n" world City.txt
mysqlimport --fields-terminated-by","
--lines-terminated-by"\n" world City.txt
mysqlimport --fields-enclosed-by'"' world City.txt
10.2 Importing Data
239
Import with ODBC
ODBC
Open
Database
Connectivity
MySQL Connector/ODBC
Bridge between MySQL server and ODBC clients
Microsoft Excel
Microsoft Access
Visual Basic
Protocols
Available for Windows and Linux
10.2 Importing Data
240
MyODBC Driver
ODBC functions library
Processes ODBC function calls
Submits SQL requests
Modifies syntax if needed
10.2 Importing Data
241
242
Chapter Summary
How to export data using a query
How to export using the 'mysqldump' database backup client
How to import data with MySQL statement files
How to import using the 'mysqlimport' data
To import data with Non-MySQL command files
To import data using ODBC
243
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
244
8UBOUERE8 8UBOUERE8
245
Learning Objectives
Methods for performing nested queries (subqueries)
Types of sub-queries
The process of converting subqueries to joins
Subqueries
246
What is a Subquery? (1/2)
11.1 What is a Subquery?
Query nested inside another query
An alternative to a join
Enclosed in parenthesis ()
Subquery table type determines usage
Discarded like a temporary table
Subqueries
247
What is a Subquery? (2/2)
Example
mysql> SELECT Language
-> FROM CountryLanguage
-> WHERE CountryCode (SELECT Code
-> FROM Country
-> WHERE Name'Finland');
----------
Language
----------
Estonian
Finnish
Russian
Saame
Swedish
----------
Subqueries 11.1 What is a Subquery?
248
Why use a Subquery?
11.1 What is a Subquery?
When a join will not work
Structured readability
Puts the "S in "SQL
Similar to English language
Read subordinate clause from inside -> out
Focus on subquery first, then final analysis
Subqueries
249
PIacement of Subqueries
Column designation/list
FROM clause
WHERE clause
Example
mysql> SELECT 1 AS a, (SELECT 11) AS b;
------
a b
------
1 2
------
Subqueries 11.1 What is a Subquery?
250
Categories of Subqueries
Correlated
References columns in the outer query
Cannot stand alone
Non-correlated
Does not reference outer query
Can stand alone
Subqueries 11.2 Categories of Subqueries
251
CorreIated Subqueries
Example
mysql> SELECT Country.Name,
-> (SELECT COUNT(*) FROM City
WHERE CountryCodeCountry.Code)
-> as CityCount FROM Country;
---------------------------------
Name CityCount
---------------------------------
Aruba 1
Afghanistan 4
Angola 5
Anguilla 2
...
The WHERE
expression in the
subquery
makes the subquery
correlated; vaIues for
the expression must be
suppIied by the outer
query for the
subquery to execute
252
Non-CorreIated Subqueries
Example
mysql> SELECT Name FROM City WHERE CountryCode IN
-> (SELECT Code FROM Country
-> WHERE Continent 'Oceania');
--------------------
Name
--------------------
Tafuna
Fagatogo
Sydney
...
AND THE $TAND ANE $UB"UE#
mysql> SELECT Code FROM Country
-> WHERE Continent 'Oceania';
------
Code
------
ASM
AUS
...
253
ResuIt TabIe Types
Scalar
Single row, single column
Row
Single row, one or more columns
Column
Single column, with one or more rows
Table
Multiple rows, multiple columns
Subqueries 11.3 Subquery Types
254
ScaIar Subqueries (1/2)
Go almost anywhere a Scalar value is allowed
Functions
Mathematical operators
nd more
Example of Scalar subquery using CONCAT
mysql> SELECT name FROM City
-> WHERE population >
-> (SELECT population FROM City WHERE name'Tokyo');
------------------
name
------------------
So Paulo
Jakarta
Mumbai (Bombay)
Shanghai
...
255
ScaIar Subqueries (2/2)
Running the subquery to confirm results
mysql> SELECT population FROM City WHERE name'Tokyo';
------------
population
------------
798020
------------
256
Subquery Type/PIacement Chart
W Cross-references types with placement
Subqueries
ScaIar Row CoIumn TabIe
CoIumn X
FROM X X X X
WHERE: X X
WHERE: <,> X
WHERE: IN, ALL,
ANY, SOME
X X X
WHERE: EXISTS X X X X
11.3 Subquery Types
257
CoIumn Designation Subquery
Runs once for each row of the subquery result
Correlated example
mysql> SELECT name,
-> (SELECT MAX(Population) FROM City
-> WHERE City.CountryCode Country.Code)
-> AS LargestCity
-> FROM Country LIMIT 8;
-----------------------------------
name LargestCity
-----------------------------------
Afghanistan 1780000
Netherlands 71200
Netherlands Antilles 245
Albania 270000
Algeria 2168000
American Samoa 5200
Andorra 21189
Angola 2022000
-----------------------------------
Subqueries 11.4 PIacing Subqueries
258
FROM Subquery
Creates a derived table
Must be given a table alias name
Example
mysql> SELECT A'G(cont_sum)
-> FROM (SELECT Continent, SUM(Population)
-> AS cont_sum
-> FROM Country
-> GROUP BY Continent)
-> AS t;
----------------
A'G(cont_sum)
----------------
86892778.5714
----------------
259
WHERE Subquery
Most common location for subqueries
Per type/placement chart
All table type results can be obtained
ScaIar Row CoIumn TabIe
CoIumn X
FROM X X X X
WHERE: X X
WHERE: <,> X
WHERE: IN, ALL,
ANY, SOME
X X X
WHERE: EXISTS X X X X
260
WHERE with Operators
Operators: =, <, >
Compares outer query with subquery results
Example
mysql> SELECT Continent, Name, Population
-> FROM Country c
-> WHERE Population (SELECT MAX(Population)
-> FROM Country c2
-> WHERE c.Continentc2.Continent
-> AND Population > 0
-> );
-----------------------------------------------
Continent Name Population
-----------------------------------------------
Oceania Australia 18886000
South America Brazil 170115000
Asia China 1277558000
Africa Nigeria 111506000
Europe Russian Federation 14694000
North America United States 27857000
-----------------------------------------------
261
WHERE Constructs
IN/NOT IN
ANY
ALL
SOME
EXISTS/NOT EXISTS
262
WHERE ... IN/NOT IN
Using IN/NOT IN
'IN' is functionally equivalent to '=ANY'
Example of IN
mysql> SELECT Name, Population FROM City
-> WHERE CountryCode IN(SELECT Code FROM Country
-> WHERE Continent 'Europe')
-> ORDER BY Name LIMIT 10;
----------------------------------
Name Population
----------------------------------
A Corua (La Corua) 24402
Aachen 24825
Aalborg 161161
Abakan 169200
Aberdeen 21070
Aix-en-Provence 14222
Albacete 147527
Alcal de Henares 16446
Alcorcn 142048
Alessandria 90289
----------------------------------
263
WHERE ... ALL, ANY and SOME (1/3)
Using ALL, ANY and SOME
Quantified comparison for column subquery
Example of ALL
mysql> SELECT Name FROM City
-> WHERE Population >
-> ALL(SELECT Population FROM City
-> WHERE CountryCode 'CHN');
-----------------
Name
-----------------
So Paulo
Mumbai (Bombay)
Seoul
-----------------
264
Comparison using ANY
ny qualifying subquery values
mysql> SELECT Name
-> FROM Country
-> WHERE Continent 'Europe'
-> AND Code ANY (SELECT CountryCode
-> FROM CountryLanguage
-> WHERE Language 'Spanish')
-> ORDER BY Name;
---------
Name
---------
Andorra
France
Spain
Sweden
---------
265
Run subquery to confirm results
mysql> SELECT CountryCode FROM CountryLanguage
-> WHERE Language 'Spanish';
-------------
CountryCode
-------------
ABW
AND
...
USA
'EN
'IR
-------------
266
WHERE ... EXISTS/NOT EXISTS
Using EXISTS/NOT EXISTS
Tests whether a subquery returns rows
Example of EXISTS
mysql> SELECT Continent FROM Country WHERE
-> EXISTS (SELECT * FROM City WHERE Code
-> CountryCode AND Population > 8000000)
-> GROUP BY Continent;
---------------
Continent
---------------
Asia
Europe
North America
South America
---------------
---------------
Continent
---------------
Asia
Europe
North America
Africa
Oceania
Antarctica
South America
---------------
NT EXI$T$ >>
267
Finding Mismatches
SELECT...NOT IN
SELECT Name FROM Country
WHERE Code NOT IN
(SELECT CountryCode FROM CountryLanguage);
SELECT...LEFT JOIN
SELECT Name FROM Country
LEFT JOIN CountryLanguage
ON Code CountryCode
WHERE CountryCode IS NULL;
Subqueries 11.5 Other Subquery Uses
268
Modify TabIes Using Subqueries
Subqueries not limited to SELECT
Can use DELETE and UPDATE
DELETE example
DELETE FROM NACities
WHERE CountryCode IN
(SELECT Code
FROM Country
WHERE LifeExpectancy < 70.0);
UPDATE example
UPDATE table1 SET table1field
(SELECT MAX(table2.table2field)
FROM table2
WHERE table1.table1field table2.table2field);
269
Converting Joins to Subqueries
More easily constructed query
mysql> SELECT DISTINCT Language
-> FROM Country
-> JOIN CountryLanguage
-> ON CountryCode Code
-> WHERE Continent'Africa';
-----------------------
Language
-----------------------
Ambo
Chokwe
...
Nsenga
Tongan
-----------------------
SELECT DISTINCT Language
FROM CountryLanguage
WHERE CountryCode IN
(SELECT Code FROM Country
WHERE Continent'Africa');
#
270
Subquery Tips
May need to run several times through the data in
order to achieve desired result
A temporary table alias may be necessary
Subqueries can be used in SELECT, UPDATE,
DELETE, and INSERT statements
Used in the SELECT, FROM, WHERE, HAVING,
and ORDER BY clauses of queries
Used in conditions that utilize comparison
operators and special-purpose operators
Arithmetic operators can be appear on either side
of a subquery; =, <>, <, >,<=, >=
271
Converting Subqueries to Joins (1/2)
mysql> SELECT Name FROM Country
-> WHERE Code IN
-> (SELE% ountryode --->
-> FRO ountryLanguage);
-----------------------------
Name
-----------------------------
Afghanistan
Netherlands
Albania
Algeria
American Samoa
...
mysql> SELECT Name
-> WHERE CodeCountryCode;
----------------------
Name
----------------------
Afghanistan
Afghanistan
Afghanistan
Afghanistan
Netherlands
Netherlands
...
Subqueries 11.6 Converting Subqueries to Joins
Joins can be more efficient than subqueries
Convert a subquery to a join, if subquery is running slow
Subquery versus Join.
272
Converting Subqueries to Joins (2/2)
List each name once
mysql> SELECT DISTINCT Name
-> WHERE CodeCountryCode;
-----------------------------
Name
-----------------------------
Afghanistan
Netherlands
Albania
Algeria
American Samoa
...
Subqueries 11.6 Converting Subqueries to Joins
273
Subqueries
274
Chapter Summary
Methods for performing nested queries (subqueries)
Types of sub-queries
The process of converting subqueries to joins
Subqueries
275
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
276
8UPPLEMENTARY 8UPPLEMENTARY
NORMATON NORMATON
277
Learning Objectives
Creating views of tables with specific data
The basics of transactions and transaction statements
The meaning and use of ACD transactions
Concepts and features of the MySQL storage engines
Storage engines used by MySQL
The definition and usage of the NFORMATON_SCHEMA
database
How to retrieve metadata using the
NFORMATON_SCHEMA database
SuppIementary Information
278
Creating Views
12.1 Creating VIEWs SuppIementary Information
A view is a database object defined by SELECT
Several benefits to views
Using the CREATE VIEW statement
Simple syntax
CREATE
[OR REPLACE]
'IEW ;iew_name [(column_list)]
AS select_expression
279
CREATE VIEW...SELECT
Example
CREATE 'IEW test.v AS SELECT * FROM t
Can use many types of SELECT statements
Example
mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t 'ALUES(, 50);
mysql> CREATE 'IEW v AS SELECT qty, price, qty*price
-> AS value FROM t;
mysql> SELECT * FROM v;
--------------------
qty price value
--------------------
50 150
--------------------
Views created in defauIt database.
To specify database use;
db_name.view_name
12.1 Creating VIEWs
280
DispIaying VIEW Information
Base versus View
Base table statements work for views
DESCRIBE
SHOW TABLES
SHOW TABLE STATUS
Showing CREATE VIEW
mysql> SHOW CREATE 'IEW v;
----------------------------------------------------------
'iew Create 'iew
----------------------------------------------------------
v CREATE 'IEW +test+.+v+ AS select 1 AS +a+,2 AS +b+
----------------------------------------------------------
12.1 Creating VIEWs
281
Showing the TabIe Types
Using SHOW FULL TABLES
Base and view types
Example
mysql> SHOW FULL TABLES FROM world;
------------------------------
Tables_in_world Table_type
------------------------------
City BASE TABLE
Country BASE TABLE
CountryLangCount 'IEW
CountryLanguage BASE TABLE
CountryPop BASE TABLE
EuropePop 'IEW
LargePop 'IEW
------------------------------
12.1 Creating VIEWs
282
Basics of Transactions (1/2)
12.2 Transactions SuppIementary Information
When concurrent statements are needed
Means for executing one or more statement as a
single unit
or none succeed
Execute if a good
Cancel if error or incomplete
283
Basics of Transactions (2/2)
Transaction example
START TRANSACTION
/ Check for sufficient balance, then withdraw money from
account #1 /
/ Deposit money into account #2 /
COMMIT
/ if everything worked then make the changes permanent /
ROLLBACK
/ if anything went wrong, undo all changes in transaction /
#
ROLLBACK
/ if insufficient funds, or error encountered during update
/
/ end of transaction /
12.2 Transactions
284
Transaction Statements
START TRANSACTION
COMMIT
ROLLBACK
SET AUTOCOMMIT
Autocommit mode is default
Disable for transactional engine with SET AUTOCOMMIT0
Disable with START TRANSACTION
Check autocommit setting with SELECT @@AUTOCOMMIT
12.2 Transactions
285
What is ACID?
Atomic
All statements execute successfully or are canceled as a unit
Consistent
Database that is in a consistent state when a transaction begins,
is left in a consistent state by the transaction
Isolated
One transaction does not affect another
Durable
All committed changes are stored persistently (not lost), and all
rolled back changes guaranteed to be gone
12.2 Transactions
286
Storage Engines and MySQL
12.3 Storage Engines SuppIementary Information
Several storage engines to choose from
Best fit for your application
What types of queries will you use?
Specify engine using CREATE TABLE
CREATE TABLE t (i INT) ENGINE InnoDB
Uses system default if not set
287
DispIaying Storage Engines (1/3)
View current engine setting
SHOW CREATE TABLE
SHOW TABLE STATUS
Example
********************** 1. row ***********************
Table: CityCreate Table:
CREATE TABLE +City+ (
PRIMARY KEY (+ID+)
12.3 Storage Engines
288
Example
mysql> SHOW TABLE STATUS LIKE 'CountryLanguage'\G
********************** 1. row **********************
Name: CountryLanguage
Engine: MyISAM
'ersion: 10
Row_format: Fixed
Rows: 984
Avg_row_length: 9
Data_length: 876
Max_data_length: 1675072454
Index_length: 22528
Data_free: 0
Auto_increment: NULL
Create_time: 2005-04-26 22:15:5
...
289
Table management is engine independent
Storage engine implementation "Lower Tier
Good to know which engine manages a table
Engine must be compiled and enabled
290
Storage Engines AvaiIabIe on Server
View available storage engines
mysql> SHOW ENGINES\G
************************ 1. row *************************
Engine: MyISAM
Support: YES
Comment: Default engine as of MySQL .2 with great
performance
...
************************* 2. row *************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for
temporary tables
...
************************* . row *************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and
foreign keys
...
291
Common Storage Engines
MySAM
Fast
Data stored in table.MYD file
Table-level locking
nnoDB
Transactional
Foreign keys
Row-level locking
Backups
Memory
Data is in memory ONLY
292
The MyISAM Storage Engine
Manages tables with specific characteristics
Represented by three files
Most Flexible AUTO_INCREMENT
Fast, compressed, read-only tables save space
Manages contention between queries
Portable storage format
Specify preference for number of rows for a table
Disable updating of non-unique indexes and enable the
indexes
Tables take up very little space
293
MyISAM Row Storage Formats
Fixed-row format
All rows have the same size
Rows stored as multiples of the row size, for easy to look up
Fixed-size rows take more space
Dynamic-row format
Rows take varying amounts of space
Rows cannot be looked up as efficiently
Take less space since rows are not padded to a fixed size
Fragmentation can occur more easily than for fixed-row tables
Compressed format
Packed to save space
Storage is optimized for quick retrieval
Read-only tables
294
The InnoDB Storage Engine (1/2)
Represented by .frm file in nnoDB tabespace
Supports COMMIT and ROLLBACK
Full ACID compliance
Auto-recovery after a crash
Multi-versioning and row-level locking
Supports foreign keys and referential integrity
Tablespace storage format is portable
295
The InnoDB Storage Engine (2/2)
Log files
nformation about ongoing transactions
Cached in memory
Log buffer written and flushed to log files at COMMIT (or as
specified)
Crash auto-recovery
Support is standard in binary distributions
296
The MEMORY Storage Engine
Uses tables stored in memory
Fixed-Length rows
Represented by .frm file and in memory
n-memory storage very fast
Table contents do not survive restart
Limited file size
Manages contention using table-level locking
Cannot contain TEXT or BLOB columns
Cannot use different character sets for different columns
Formerly HEAP Engine
297
Storage Engine Summary
MyISAM MEMORY InnoDB
Usage
Fastest for read
heavy apps
n-Memory
storage
Fully ACD
compliant
transactions
Locking
Large-grain table
locks, no non-
locking reads
Large grain
table locks
Multi-
versioning,
Row-level
locking
Indexing
B-tree / Full text /
R-tree
Hash / B-tree
indexes
B-tree indexes
DurabiIity
Table recovery No disk /O or
persistence
Durability
recovery
298
Other Engines
Optional storage engines
Select when configuring MySQL
Many more engines available
FEDERATED
NDB
EXAMPLE
ARCHVE
CSV
BLACKHOLE
Falcon
299
Using INFORMATION_SCHEMA (1/2)
12.4 Retrieving Metadata SuppIementary Information
Metadata
Tables of all tables
Tables of columns
nformation about all MySQL server databases
System views, not data
Database cannot be manipulated
300
Using INFORMATION_SCHEMA (2/2)
Two methods for obtaining metadata
SHOW statement
SELECT...FROM with INFORMATION_SCHEMA
Advantages of INFORMATION_SCHEMA
Advantages of SHOW
12.4 Retrieving Metadata
301
INFORMATION_SCHEMA Options (1/2)
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
COLUMN_PRIVILEGES
KEY_COLUMN_USAGE
ROUTINES
SCHEMATA
302
INFORMATION_SCHEMA Options (2/2)
SCHEMA_PRIVILEGES
STATISTICS
TABLES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS
Find an ER Diagram of the
INFORMATION_SCHEMA
database onIine.
303
Viewing INFORMATION_SCHEMA
Example
mysql> SELECT table_name, table_type, engine
-> FROM information_schema.tables
-> WHERE table_schema 'db5'
-> ORDER BY table_name DESC;
--------------------------------
table_name table_type engine
--------------------------------
v56 'IEW NULL
v 'IEW NULL
v2 'IEW NULL
v 'IEW NULL
tables BASE TABLE MyISAM
t7 BASE TABLE MyISAM
t BASE TABLE MyISAM
t2 BASE TABLE MyISAM
t BASE TABLE MyISAM
pk BASE TABLE InnoDB
loop BASE TABLE MyISAM
...
304
SCHEMATA
Table in INFORMATION_SCHEMA database
Example
mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
-> WHERE SCHEMA_NAME 'world'\G
************************* 1. row *************************
CATALOG_NAME: NULL
SCHEMA_NAME: world
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
SQL_PATH: NULL
305
Chapter Summary
Creating views of tables with specific data
The basics of transactions and transaction statements
The meaning and use of ACD transactions
Concepts and features of the MySQL storage engines
Storage engines used by MySQL
The definition and usage of the NFORMATON_SCHEMA
database
How to retrieve metadata using the
NFORMATON_SCHEMA database
306
Course Structure
1. INTRODUCTION
2. DATABASE BASICS
3. DATABASE DESIGN
5. BASIC QUERIES
8. FUNCTIONS
9. JOINING TABLES
11. SUBQUERIES
13. CONCLUSION
307
CONCLU8ON CONCLU8ON
308
Course Review (1/2)
13.1 Course Review ConcIusion
List the features and benefits of MySQL
Find information about products, support and training
nstall and start the MySQL server
Explain the basics of relational databases
Distinguish the SQL language from MySQL language
extensions
Explain data/column types with regard to efficient
database design
nspect the design, structure and content of databases
Design and Create an efficiently structured database
Retrieve data using the SELECT statement
309
Course Review (2/2)
ConcIusion
Troubleshoot and understand typical warnings and errors
Modify and delete a database
Modify and delete table row data
Use data aggregation in queries
Combine data from multiple tables using JON
Write subqueries
List simple functions (String, Date, Numerical)
Understand the primary methods for exporting and
importing data
Describe MySQL connectors, their major features and
differences
Explain MySQL storage engine concepts
13.1 Course Review
310
Training and Certification Website
Overview
Certification
Curriculum Paths
Catalog
Schedule
Delivery Options
Savings and Promotions
Training Bundles
Training Partners
Testimonials
FAQ
ConcIusion 13.2 Training and Certification Website
http://www.mysqI.com/training
311
We Need Your EvaIuation!
Please take time to give us your opinions
http://www.mysqI.com/training/evaIuation.php
Get course code from instructor
ConcIusion 13.3 Course EvaIuation
312
13.4 THANK YOU! ConcIusion
We appreciate your attendance and participation!
Contact us regarding training issues @...
Website: http://www.mysqI.com/training/
EmaiI: [email protected]
Phone: USA ToII Free: 1-866-697-7522
WorIdwide: 1-208-514-4780
THANK YOU!!!
313
13.5 Q&A Session ConcIusion
Question and Answers
More questions after class?
Get answers on our FAQ Reference Manual online
http://dev.mysqI.com/doc/refman/5.1/en/faqs.htmI
Want to do the labs on your own?
Download the 'worId' database from our website
http://dev.mysqI.com/doc/
Under "ExampIe Databases
Q&A Session

Mysql For Beginners

Uploaded by

Copyright:

Available Formats

Mysql For Beginners

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Mysql For Beginners

Uploaded by

Copyright:

Available Formats

What are the main topics discussed in the document?

How is the information organized across pages?

MySQL for Beginners | MySQL AB 2008

You might also like