Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Learning MySQL

  1. Nov 24, 2016 #1

    I would like to learn the concepts of databases and practice them. I found it hard to find a good tutorial that teaches you how to start. All seem to be advanced to me!!

    Is there a book (or a good tutorial) that teaches you both; the concepts and the applicationsof databases at the same time?

    Also, I noted that some tutorials use the MySQL workbench (which I have, and I'll be using) and others use the command prompt, does it matter how I MySQL as a beginner?

  2. jcsd
  3. Nov 24, 2016 #2


    Staff: Mentor

    In many production environments, you may be limited in what you have. Hence, its good to know how to use the command line tool to do select queries, use describe and explain to get details on tables and how queries will be evaluated. Also how to adjust tables and move data around to archive or restore it.

    One of my coworkers was so adept that he could use sql to write sql that did what he wanted. This was especially useful for recursive type queries that could be constructed directly without detailed knowledge of the database scheme.

    I've seen a couple of books on sql hacks that have some cool recipes. You might check amazon searching with sql hacks or sql cookbook.
  4. Nov 24, 2016 #3
    Makes sense. Do you have a suggestion on how to get started in this path?
  5. Nov 24, 2016 #4


    Staff: Mentor

  6. Nov 24, 2016 #5
    Interesting. They have their own SQL Script. I guess the commands will be the same. By I noticed that many jobs ask skills with MySQL, that's why I wanted to learn SQL using MySQL.

    I still need a good tutorial (or book) that explains the concepts and the terminology about databases, and if it includes practical examples, that would be even better.
  7. Nov 25, 2016 #6

    jack action

    User Avatar
    Science Advisor
    Gold Member

    I really like the MySQL documentation.

    The problem with other sources is that they show you some very basic stuff. But SQL is full of very useful things that are needed, especially with very large tables. So the documentation is quite heavy, but in the end you'll find that it is necessary to understand it fully to do good table structures and efficient data manipulations.

    I would suggest you start with the following chapters (in order), such that you don't get lost or bored too much. For most of the important stuff elsewhere, there is probably a link inside those sections to reach it. I would also focus on the innoDB engine first, as it is probably the one you'll use (although, I'm not bashing other engines).

    Chapter 10 Language Structure

    This chapter explains the basic language structure. Pretty simple stuff if you are used to programming.

    Chapter 12 Data Types

    This chapter is essential to help you choose the proper data type (i.e. the one that takes the less storage space). Focus mostly on numeric and string types at first.

    Chapter 14 SQL Statement Syntax

    This is what I consider the tutorial part of the documentation, especially the first two sections:
    The section 14.8.2 is also essential to understand to learn how to build good queries.

    Chapter 13 Functions and Operators

    For when you'll want to do more complex comparisons or string manipulations.

    Finally, I also suggest Rick's Rules of Thumb that helped me a lot understand the art and importance of proper SQL.
    Last edited: Nov 28, 2016
  8. Nov 25, 2016 #7
    I stumbled at the documentation before, but I was lost in it. Thanks for structuring what I need to read from it first.
  9. Nov 27, 2016 #8
    You can take a 6 week online sql certification course through ed2go.com. For entry level SQL, it doesn't really matter which one you learn. Once you get to mid-level or advanced SQL, you will likely need to know SQL platforms.
  10. Nov 28, 2016 #9
    As always learn from the ground up. Once you learn SQL and general data structures from top to bottom you can utilize different GUIs and helpers. Don't go the other way around.
  11. Nov 28, 2016 #10
    Right, but I want to practice as I learn the theory. That's how I learn programming languages in general.
  12. Nov 28, 2016 #11


    User Avatar

    Staff: Mentor

    You can do that with the MySQL command-line client. You don't need a fancy GUI or IDE.

    Code (Text):

    Jons-Mac-Pro:~ jtbell$ mysql -p music
    Enter password:
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 1
    Server version: 5.1.50 MySQL Community Server (GPL)

    Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL v2 license

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> select * from composer where lastname='strauss';
    | ID   | lastname | firstname | nationality | birthyear | deathyear |
    | 1432 | Strauss  | Eduard    | Austria     |      1835 |      1916 |
    | 1018 | Strauss  | Johann I  | Austria     |      1804 |      1849 |
    | 1019 | Strauss  | Johann II | Austria     |      1825 |      1899 |
    | 1020 | Strauss  | Josef     | Austria     |      1827 |      1870 |
    | 1021 | Strauss  | Richard   | Germany     |      1864 |      1949 |
    5 rows in set (0.02 sec)

    Yes, this is an ancient version of MySQL. :-p I plan to upgrade sometime after the end of the semester.
  13. Feb 13, 2017 #12
    I found this book very helpful when I was learning database programming. You should be able to buy a used copy on Amazon very cheaply.

    Jan. l. Harrington, Relational Database Design Clearly Explained. (Now in Second Edition at least).

    What I like about her book is that she combines theory with some interesting practical examples. If you go through her book, including coding all the examples yourself and making sure they work, then you will have an excellent foundation in database programming.

    BTW if you are ever looking for a lightweight alternative to mySQL which is easy to install on your PC, consider SQLite.
  14. Feb 13, 2017 #13


    Staff: Mentor

    Another one is H2 database. A pure Java database engine packaged as a single jar file with web server access to the database. Can be used as an application specific database or as a standalone database server. It supports most sql statements and is very fast.

  15. Feb 13, 2017 #14


    User Avatar

    Staff: Mentor

    For what it's worth, these are the books I used when I learned about MySQL, supplemented by online references and Google searches:

    MySQL Tutorial, by Luke Welling and Laura Thomson
    MySQL, by Paul DuBois
    MySQL Administrator's Guide, by MySQL AB

    These are all several years old, but probably have newer editions now, for newer versions of MySQL.
  16. Feb 14, 2017 #15


    User Avatar
    Gold Member

    A database is just a list of information.
    Writing down a shopping list is a simple database.
    The dictionary is another.
    A phonebook another.
    You, as a person know, intuitively how to manipulate those databases to find the information you seek, such as the third item on the shopping list, or the page to flip to in the phonebook to find the telephone number of John Henry ( you could flip A's to B's to C's etc, but most people do a somewhat quicker search assume that "H" is somewhere around the third part of the book, flip there, and move back or forth as need be. Another search would be " How many people have "Henry" as the last name.

    A computer isn't so smart, so you have to tell it each and every time you want to search for information, how to do it with a query.
    You can have a database organized as one of the two major groups - a flat database or as a relational database, such as:
    By the way, flat databases, can be quite large, so the "simple" comment doesn't really come across correctly I think.

    You can have database with the query language understanding built right in, so it stands alone, and your program queries and receives the information to and from for the user to see. Or you can build the database into your program, so that the program and database become inseparable.

    Things to know might be sequential files, random access files, sorting data, arrays, etc and all that fun stuff for the manipulation of data. B-trees, binary trees, linked lists, hashing, indexing, etc.

    With a relational database you have to look at how to organize your tables, so that information isn't duplicated needlessly.

    Your comment about wanting to learn databases is what drove me to write a little bit about the "behind" the screen".
  17. Feb 14, 2017 #16


    Staff: Mentor

    There's a set of rules that database designers try to follow to properly organize their database without duplication and for database vendors to offer products that are truly relational:


    For me the rule that affects folks most is probably rule 3 on the meaning of null values:

    This rule often affects how SQL statements return your data when trying to match things up during table/view joins.


    and three valued logic:


    As an example, in a book owners table you might ask how many people own zero books so of course you'd count those with a zero book count but what about the folks that have a null value meaning we don't know how many books they have.

    Should they be included in the count? The answer is it depends on the situation but you have to be aware and decide how you'll handle nulls to get the answer you want.

    If you want a definitive count then you might exclude the null owners. However if you want a count of people you plan to sell books to for the purpose of estimating the number of book catalogs to print then perhaps you want to include them.
    Last edited: Feb 14, 2017
  18. Feb 14, 2017 #17


    Staff: Mentor

Share this great discussion with others via Reddit, Google+, Twitter, or Facebook

Have something to add?
Draft saved Draft deleted