Meet the MySQL Database

1
Meet the MySQL Database

• The Ikea of Databases!

2
About Me

• Imran Chaudhry ichaudhry_at_gmail.com
• SmoothWall perl/database guy
• MySQL Core Pro Certified
• South by South West MySQL user group organizer
• http//mysql.meetup.com/123/

3
So, what is MySQL?

• A Relational Database (RDBMS) and language
• Made by Swedes
• v5.0, not a toy database anymore
• Open Source and Dual Licence
• Multi-platform
• Essential Part of the L.A.M.P stack
• Download from www.mysql.com/downloads
• Who is using it?

4
Anatomy of MySQL

• Birds-eye view of a typical installation

5

MySQL Server
bookshop
Client
mysql, Query Browser, Administrator, phpMyAdmin
foo
Connectors (Perl DBD, ODBC, JDBC)
Perl, PHP, Python, Java, VB, OpenOffice Base
6
SQL

• Structured Query Language
• Two categories of statements
• Data Definition Language
• Statements that create objects (tables, columns
  etc)
• Data Manipulation Language
• Statements that manipulate data (SELECT, INSERT
  etc)

7
Simple example

• Create a database
• Create a table
• Fill table with data
• Read the table
• and more

8

• MySQL admin_at_laptop_(none)gt create database
  bookshop
• MySQL admin_at_laptop_(none)gt use bookshop
• Database changed
• MySQL admin_at_laptop_bookshopgt
• create table book (
• id int auto_increment primary key,
• name varchar(255)
• )
• MySQL admin_at_laptop_bookshopgt desc book
• --------------------------------------------
  -------------
• Field Type Null Key Default
  Extra
• --------------------------------------------
  -------------
• id int(11) NO PRI
  auto_increment
• name varchar(255) YES
  
• --------------------------------------------
  -------------

9

• MySQL admin_at_laptop_bookshopgt
• alter table book add publisher varchar(255)
• MySQL admin_at_laptop_bookshopgt desc book
• ---------------------------------------------
  ----------------
• Field Type Null Key Default
  Extra
• ---------------------------------------------
  ----------------
• id int(11) NO PRI
  auto_increment
• name varchar(255) YES
  
• publisher varchar(255) YES
  
• ---------------------------------------------
  ----------------

10

• MySQL admin_at_laptop_bookshopgt
• insert into book values (0, 'MySQL in a
  Nutshell', 'Oreilly')
• insert into book values (0, 'Perl Cookbook',
  'Oreilly')
• insert into book values (0, 'Mastering MySQL',
  NULL)
• MySQL admin_at_laptop_bookshopgt select from book
• ------------------------------------
• id name publisher
• ------------------------------------
• 1 MySQL in a Nutshell Oreilly
• 2 Perl Cookbook Oreilly
• 3 Mastering MySQL
• ------------------------------------
• MySQL admin_at_laptop_bookshopgt
• select from book where publisher 'Oreilly'
  and name like 'perl'
• ------------------------------
• id name publisher
• ------------------------------

11

• MySQL admin_at_laptop_bookshopgt select from
  copies_sold
• ----------------
• book_id sales
• ----------------
• 1 1000
• 2 750
• 3 1
• ----------------
• select
• book.name as 'Name of Book', copies.sold as
  'Copies Sold'
• from
• book inner join copies_sold
• on book.id copies_sold.book_id
• order by
• sales desc
• ----------------------------------
• Name of Book Copies Sold
• ----------------------------------

12
Tips

• The option file my.cnf
• \G to display results vertically
• mysql_secure_installation
• GUI interfaces
• Online docs www.mysql.com/foo

13

• MySQL Server Instance Configuration File
• ----------------------------------------
• client
• useradmin
• passwordF00b4r
• port3306
• mysql
• default-character-setlatin1
• to stop me being a dummy...
• safe-updates
• promptMySQL\_\u_at_\h_\dgt\_
• databaseworld
• mysqldump
• commentsFALSE
• compactTRUE
• no-dataTRUE

14

• MySQL admin_at_laptop_worldgt select from country
  limit 3
• ----------------------------------------------
  -----------------------------
• -----------------------------------------------
  ---------------------------
• -------------------------------------------------
  -----------------------------
• -----------------
• Code Name Continent
  Region Surf
• aceArea IndepYear Population LifeExpectancy
  GNP GNPOld Loca
• lName GovernmentForm
  HeadOfState
• Capital Code2
• ----------------------------------------------
  -----------------------------
• -----------------------------------------------
  ---------------------------
• -------------------------------------------------
  -----------------------------
• -----------------
• AFG Afghanistan Asia
  Southern and Central Asia 6520
• 90.00 1919 22720000 45.9
  5976.00 NULL Afga
• nistan/Afqanestan Islamic Emirate
  Mohammad Omar
• 1 AF
• NLD Netherlands Europe
  Western Europe 4152
• 6.00 1581 15864000 78.3
  371362.00 360478.00 Nede

15

• MySQL admin_at_laptop_worldgt select from country
  limit 3 \G
• 1. row
  
• Code AFG
• Name Afghanistan
• Continent Asia
• Region Southern and Central Asia
• SurfaceArea 652090.00
• IndepYear 1919
• Population 22720000
• LifeExpectancy 45.9
• GNP 5976.00
• GNPOld NULL
• LocalName Afganistan/Afqanestan
• GovernmentForm Islamic Emirate
• HeadOfState Mohammad Omar
• Capital 1
• Code2 AF
• 2. row
  
• Code NLD

16
Query Browser
17
Administrator
18
Resources

• MySQL Forums
• http//forums.mysql.com
• Mailing Lists
• http//lists.mysql.com/mysql
• MySQL user group needs You!
• http//mysql.meetup.com/123/

19
And Finally

• http//www.atomdatabase.org
• Slides will be at http//www.atomdatabase.org
• Further talks?
• Any Questions?
• Ask now or find me later!