Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

MySQL for Absolute Beginners

VIEWS: 37 PAGES: 37

Never used MySQL before? Start here! This tutorial walks you through the very basics of MySQL and SQL databases. Learn how to install MySQL, how to issue commands, and how to create and use databases.

More Info
									                                                                                                                       Search articles...


                                                                Articles    Web Templates   Stock Images   Photoshop Actions    Forums




     Home : Articles : MySQL for Absolute Beginners


     MySQL for Absolute Beginners


                                                                                                                                   Advertise Here




                                                                                                                      300,000+ Impressions
                                                                                                                      From Just $120!

       Tutorial by Matt Doyle | Level: Beginner | Published on 25 August 2011

       Categories: Web Development


                                                                                                    Current forum topics
                         Never used MySQL before? Start here! This tutorial walks you
                                                                                                       Kitchen Table: A Slick Photo Light Table Using
                          through the very basics of MySQL and SQL databases. Learn how
open in browser PRO version Are you a developer? Try out the HTML to PDF API                                                                  pdfcrowd.com
                              through the very basics of MySQL and SQL databases. Learn how            CSS3 and jQuery
                              to install MySQL, how to issue commands, and how to create and
                                                                                                       Build a CMS in an Afternoon with PHP and MySQL
                              use databases.
                                                                                                       How to Make a Slick Ajax Contact Form with jQuery
                                                                                                       and PHP

                                                                                                       Contact Form Help

                                                                                                       Writing a Simple Form Mailer

                                                                                                    Got a question about making a website? Ask it in the
                                                                                                    forums — we'd love to help you. All questions
                                                                                                    answered!




      A database is an essential part of most websites and blogs, but setting up and using a
      database can be a daunting task for the beginner web developer.

      In this tutorial you'll learn — in simple terms — how to install, set up, and use a MySQL
      database on your computer. This will give you a good grounding in MySQL that you'll find
      useful when you start developing database-driven websites using PHP, Ruby, or your language
      of choice.

      You'll explore the following areas in this tutorial:

         The concept of a database, and why databases are useful

         The advantages of using MySQL to manage your databases

         How to install MySQL on your computer                                                      Popular articles
         Using the MySQL Monitor program to send commands to your MySQL server                         jQuery Mobile: What Can It Do for You?

                                                                                                       JavaScript Tabs - Create Tabbed Web Pages Easily
         How to create a database and table
                                                                                                       How to Make a Slick Ajax Contact Form with jQuery
                                                                                                       and PHP
         SQL (Structured Query Language), and what it's used for
                                                                                                       Making CSS Rollover Buttons
         Adding records to a table, and retrieving records from a table
open in browser PRO version Are you a developer? Try out the HTML to PDF API                                                                    pdfcrowd.com
        Adding records to a table, and retrieving records from a table
                                                                                                          Object-Oriented PHP for Absolute Beginners

     Ready to start exploring MySQL? Let's go!

                                                                                                       Building a site? We're here to help!
                                                                                                       At Elated you'll find lots of free webmaster resources to
                                                                                                       improve your sites, including:
     What is a database?                                                                                  Web design and development articles and tutorials

     A database is a structured collection of data. Here are some typical examples of databases:          Free website templates

                                                                                                          Free images
        An online store database that stores products, customer details and orders
                                                                                                          Photoshop actions
        A database for a web forum that stores members, forums, topics and posts
                                                                                                          Helpful experts in our webmaster forums

        A database for a blog system, such as WordPress, that stores users, blog posts,                   A free newsletter packed with useful tips and advice

        categories, tags, and comments                                                                 Yes, this stuff is free! We just ask that you give us a link
                                                                                                       - find out how to link to us. (Note: all our content is
     The software that manages databases is known as a database management system, or                  copyrighted; please read our website's terms of use.)

     DBMS. MySQL is an example of a DBMS. Rather confusingly, DBMSs are often called                   Never made a website before? Read How to Make a
                                                                                                       Website.
     databases too. Strictly speaking though, the database is the data itself, while the DBMS is the
     software that works with the database.

     There are many different ways to organize data in a database, known as database models.
     One of the most popular is the relational model, which is what MySQL uses (along with
     PostgreSQL, Oracle, Microsoft SQL Server, and other common systems). Consequently,
     MySQL is known as a relational database management system, or RDBMS.                                      Elated is part of the Smashing Network

     The following diagram shows how a database, the DBMS, and your website's code interact with
     each other.




open in browser PRO version    Are you a developer? Try out the HTML to PDF API                                                                      pdfcrowd.com
       A database (left) is a collection of related data, managed by a DBMS such as MySQL
       (centre). Web scripts (right) communicate with the DBMS in order to insert, update,
                              delete and retrieve data in the database.




     Why use a database?
     If you haven't used a database for your website before, you may have stored data in text files
     such as comma-separated value (CSV) files. Here's an example of a CSV file:




       username,password,emailAddress,firstName,lastName
       "johnsmith","brickwall53","john@example.com","John","Smith"
       "maryjones","garden37","mary@example.com","Mary","Jones"




     While this technique is fine for simple data structures and small amounts of data, you quickly
     run into problems as your site grows. Searching and updating a very large text file is slow and
     prone to corruption. What's more, things start to get messy when you want to link records

open in browser PRO version    Are you a developer? Try out the HTML to PDF API                        pdfcrowd.com
     together, such as linking a customer record to the orders that the customer has made, and
     then linking each order record to the products that are in the order.

     Relational databases are designed to take care of all these problems. Here are some reasons
     to use a database instead of text files:

        They're fast. Databases use indexes, or keys, to find records of data extremely quickly. For
        example, once you add a key to an emailAddress field for member records in a database,
        you can retrieve a member record based on the member's email address almost instantly,
        no matter how many millions of members you may have in your database.

        They're reliable. A DBMS ensures that the data in the database is read and written
        reliably, without corrupting the data. Many DBMSs allow you to use techniques like locking
        and transactions to ensure that records are inserted and updated in a predictable way.

        They let you link records together. Relational databases let you store different types of
        data in groups known as tables. You can then link data together across tables. For example,
        you can create a customers table and an orders table, then link a customer record to all
        the order records associated with the customer. The ability to link records across tables lets
        you create complex databases with lots of different types of related data.




     Why use MySQL?
     You now know why databases are useful, and how they can help you build complex websites
     and web apps. But why use MySQL in particular?

     There are many great DBMSs out there, including MySQL, PostgreSQL, SQLite, Oracle, and
     SQL Server, and all of them can be used for most web development purposes.

     That said, MySQL does have a few advantages for web developers compared to some other
      systems:
open in browser PRO version     Are you a developer? Try out the HTML to PDF API                         pdfcrowd.com
     systems:

        It's open source, which means it's free for anyone to use and modify.

        It's widely available. MySQL can be installed on many different platforms, and it usually
        comes standard with most web hosting setups.

        It's easy to use. Setting up and working with MySQL databases is relatively straightforward.

        It works well with PHP. As of version 5.3, PHP has a native MySQL driver that is tightly
        coupled with the PHP engine, making it a good choice for PHP coders.




       Each DBMS has its own strengths and weaknesses. For example, PostgreSQL is also open
       source, is very stable, and has a great community behind it. SQLite is extremely fast and
       self-contained (and is also free), while Oracle and SQL Server have a lot of enterprise-level
       features that make it a good choice for large organizations.




     Installing MySQL
     As I mentioned above, most web hosting accounts come with MySQL pre-installed. However, if
     you're developing websites using MySQL, you also want to have the MySQL server running on
     your own computer, so that you can create and test your databases and code without needing
     to upload files to your live server all the time.

     There are two main components to MySQL:

        The MySQL database server, mysqld . This is the MySQL DBMS that does the actual
        work of managing your databases. It runs all the time in the background, accepting

open in browser PRO version       Are you a developer? Try out the HTML to PDF API                     pdfcrowd.com
        connections from client programs, web scripts and so on.

        Various client and utility programs. These include mysql , the command-line MySQL
        Monitor client that you'll use later in the tutorial to issue commands to the server. You'll also
        find programs like mysqladmin for administering MySQL databases, and mysqldump for
        exporting and backing up databases.




       In addition, many MySQL installs include documentation, header files for developers, and
       the MySQL test suite.



     There are many ways to install the MySQL server and associated programs. Here are three
     ways you can do it:

        Using an official MySQL installation package. There are prebuilt packages available for
        many different operating systems, including Windows, Mac OS X and Linux. The basic
        procedure is to download the package file, extract it, and run the installer. See the
        documentation for the exact steps.

        Using a Linux package manager. Many Linux distros come with a package manager —
        for example, Ubuntu includes the Ubuntu Software Centre — that makes it easy to install
        MySQL, along with PHP, Apache and other web development software. See your distro's
        documentation for details.

        Installing an entire LAMP/WAMP/MAMP package. This is arguably the easiest way to
        install a complete MySQL-based development setup on your computer. These packages
        contain all you need to start building database-driven sites, including the Apache web
        server, MySQL, and PHP/Perl, hence the acronym "AMP". (The L, W and M stand for Linux,
        Windows and Mac OS X respectively.) Since everything's installed in one go, you'll find that

open in browser PRO version     Are you a developer? Try out the HTML to PDF API                            pdfcrowd.com
        Apache, MySQL and PHP/Perl all play nicely together, with little or no further configuration
        required.

     If you want to get up and running as quickly and easily as possible, I'd personally recommend
     downloading and installing XAMPP. This LAMP/WAMP/MAMP package is available for Linux,
     Windows, Mac OS X and Solaris, and automatically installs and sets up Apache, MySQL, PHP
     and Perl on your computer. What's more, it's easy to uninstall everything later if you want to.

     To install XAMPP:

     1. Visit the XAMPP homepage and click the link for your operating system (Linux, Windows,
        Mac OS X or Solaris).

     2. Follow the steps on the page to download, install, start, and test the XAMPP system on your
        computer.




       Other popular packages similar to XAMPP include WampServer and EasyPHP for Windows,
       and MAMP for Mac OS X.




open in browser PRO version     Are you a developer? Try out the HTML to PDF API                       pdfcrowd.com
        XAMPP makes it easy to install a complete Apache, MySQL, PHP and Perl setup on
                                       Windows, Mac and Linux.




     Issuing commands to MySQL
     Assuming you've now installed and started your MySQL server using one of the above
     techniques, how do you interact with the server? When you installed the MySQL server, you
     also installed mysql , the MySQL Monitor. This is a command-line client program that you can
     use to connect to the server and issue commands.

     So let's try firing up the MySQL Monitor and see what it can do. Follow these two steps:

     1. Open a terminal window:


           Windows 7: Click the Windows logo, then choose All Programs > Accessories >
           Command Prompt.

           Mac OS X: Open a Finder window, then choose Applications > Utilities > Terminal.

           Ubuntu: Choose Applications > Accessories > Terminal, or if you're using the Unity
           desktop, click the Ubuntu logo and type terminal . (More info)

     2. Run the mysql program in the terminal window:


open in browser PRO version    Are you a developer? Try out the HTML to PDF API                     pdfcrowd.com
           Windows 7: Assuming you installed XAMPP, type cd c:\xampp\mysql\bin and press
           Enter , then type mysql -u root and press Enter .

           Mac OS X and Ubuntu: Just type mysql -u root and press Enter .




         The -u root parameter tells the MySQL Monitor to connect to the MySQL server using
         the root user, which is always available with MySQL. By default, MySQL's root user
         doesn't need a password. This is OK for a development setup on your computer, but a
         terrible idea for a live server! If you're installing MySQL on a live server, make sure you
         secure it properly. XAMPP also comes with some security scripts that can automatically
         make your XAMPP installation more secure.



     Once the MySQL Monitor runs, you'll see something like this in your terminal window:




       Welcome to the MySQL monitor.      Commands end with ; or \g.
       Your MySQL connection id is 3893
       Server version: 5.5.8 Source distribution


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


       mysql> _




     The last line, mysql> , is the MySQL prompt. This is where you type your commands to send to
     the MySQL server.


open in browser PRO version    Are you a developer? Try out the HTML to PDF API                        pdfcrowd.com
     Let's try out a couple of commands. Type the following at the mysql> prompt, then press
      Enter :


       select now();

     This tells MySQL to get the current date and time and display it. You'll see something like this
     appear:




       +---------------------+
       | now()                  |
       +---------------------+
       | 2011-08-24 11:36:40 |
       +---------------------+
       1 row in set (0.00 sec)




     Now try another command:


       show databases;

     This command lists all the MySQL databases on your computer. Since you've just installed
     MySQL, there will just be a few default databases, similar to the following:




       +--------------------+
       | Database             |
       +--------------------+
       | information_schema |

open in browser PRO version       Are you a developer? Try out the HTML to PDF API                      pdfcrowd.com
       | mysql                 |
       | performance_schema |
       | test                  |
       +--------------------+
       4 rows in set (0.00 sec)




     Now that you know how to send commands to your MySQL server, you're ready to create your
     own database and start adding data to it. You'll do this in the following sections.




       When you're finished with the MySQL Monitor, you can quit it by typing exit at the prompt
       and pressing Enter .




       If you're not comfortable with the command line, there are other ways to administer MySQL
       and issue commands. MySQL Workbench is a free graphical app that can connect to any
       MySQL server and administer it. There's also the web-based phpMyAdmin, which is included
       in many LAMP/WAMP/MAMP packages.




     Creating a database
     Let's create a simple database for an imaginary book store. At your mysql> prompt, type the
     following and press Enter :



open in browser PRO version        Are you a developer? Try out the HTML to PDF API                pdfcrowd.com
       create database bookstore;

     If all goes well, you'll see something like this:




       Query OK, 1 row affected (0.05 sec)




     MySQL has now created your database. You can check this by typing show databases again:




       mysql> show databases;
       +--------------------+
       | Database               |
       +--------------------+
       | information_schema |
       | bookstore             |
       | mysql                  |
       | performance_schema |
       | test                   |
       +--------------------+
       4 rows in set (0.00 sec)




     Congratulations — you've just created your first MySQL database!




     Some SQL basics
open in browser PRO version         Are you a developer? Try out the HTML to PDF API           pdfcrowd.com
     All of the commands you've issued so far — select now() , show databases , and create
     database bookstore — are SQL statements. SQL, or Structured Query Language, is the
     language you use to communicate with most DBMSs, including MySQL. Using SQL, you can
     create and delete databases and tables; insert new data into tables; update data; delete data;
     and retrieve data.

     Statements that retrieve data from a database are also commonly called queries, hence the
     name "Structured Query Language".

     You'll use SQL in the rest of this tutorial as you create a table in your new database, add a
     record, and retrieve a record.




     Creating a simple table




open in browser PRO version     Are you a developer? Try out the HTML to PDF API                      pdfcrowd.com
     As with all relational databases, MySQL organizes its data in tables. A table holds one or more
     records of related data, in a similar way to an associative array in JavaScript or PHP. A table
     consists of:

        One or more fields. Each field holds a specific type of information. For example, in a
           books table, you might have one field for the book title, another field for the book's author,
        and so on.

        One or more records. A record is a set of field values that stores all the information about
        a particular entity in the table. In a books table, a record would store all the field values for
        a specific book.

     The easiest way to understand fields and records is to see how they look when laid out in a
     table. Let's set up a books table to hold books in our book store:


       id        title                                               author                    price

       1         The Grapes of Wrath                                 John Steinbeck            12.99

       2         Nineteen Eighty-Four                                George Orwell             8.99

       3         The Wind-Up Bird Chronicle                          Haruki Murakami           7.99


     The top row of the table holds the field names: id , title , author , and price . The next
     three rows are the three book records in our table. Each record has its own field values: for
     example, the first record's title field contains "The Grapes of Wrath", while the second
     record's title field contains "Nineteen Eighty-Four".

     Since a single column in a table holds all the different record values for a specific field, fields
     are also commonly known as columns. Similarly, the records in a table are commonly called
     rows.
open in browser PRO version       Are you a developer? Try out the HTML to PDF API                          pdfcrowd.com
     So how do you actually create this table in MySQL? To do this, you need to create a schema
     for the table. This is a text file containing a series of SQL statements that create the table and
     define the table's fields.

     Here's the schema — save it as a file called books.sql somewhere on your computer:




       USE bookstore;


       DROP TABLE IF EXISTS books;
       CREATE TABLE books
       (
           id                   int unsigned NOT NULL auto_increment, # Unique ID for the record
           title                varchar(255) NOT NULL,                              # Full title of the book
           author               varchar(255) NOT NULL,                              # The author of the book
           price                decimal(10,2) NOT NULL,                             # The price of the book


           PRIMARY KEY          (id)
       );




     Let's take a look at the SQL statements in this file and see what they do:

           USE bookstore
           This tells MySQL to switch to the bookstore database that you created earlier. MySQL will
           then carry out all further operations on this database.

           DROP TABLE IF EXISTS books
           This deletes any previous books table from the database, since you can't redefine a table if
           it already exists.


open in browser PRO version            Are you a developer? Try out the HTML to PDF API                        pdfcrowd.com
         Be careful when using DROP TABLE . When you delete a table like this, any data in the
         table is gone forever!



        CREATE TABLE books ( ... )
        This statement creates a new table called books . The stuff in between the parentheses
        defines the table's fields and its primary key, as we'll see next.

        id int unsigned NOT NULL auto_increment
        The first field we define is id . This is a special type of field that assigns a unique numeric ID
        to each book record in the table. Most of the time, you'll want your table to have a unique
        field of some sort, so that you can easily identify a particular record. We give the field an
        int unsigned type, which can hold large, positive integer numbers. We also add the
        auto_increment attribute to the field — now, whenever we add a new record to the table,
        MySQL will automatically assign a new, unique value to the record's id field (starting with
        1).




         The NOT NULL constraint prevents the field containing NULL values. In MySQL, NULL is
         a special type of value that can be useful in some situations. However, it can also be
         quite confusing for beginners, so we won't use them in this tutorial.



        title varchar(255) NOT NULL
        Next we define the field to hold each book's title. We give it a varchar(255) type, which
        means it can hold a text string up to 255 characters long.


open in browser PRO version       Are you a developer? Try out the HTML to PDF API                           pdfcrowd.com
        author varchar(255) NOT NULL
        The next field is the book's author. As with the title field, we give it the varchar(255)
        type.

        price decimal(10,2) NOT NULL
        The last field is the book's price. We give this field a decimal(10,2) type, which means
        that the field can hold a 10-digit decimal number, with 2 of the digits sitting to the right of the
        decimal point.

        PRIMARY KEY (id)
        Finally, we create a primary key based on the table's id field. A primary key uniquely
        identifies records in the table; a table can have only one primary key. MySQL also creates
        an index using the primary key — this lets you retrieve a book record extremely quickly by
        referencing its id field, even if the table contains millions of rows.

     Now that we've created our schema statements, we need to run them through MySQL to create
     the actual table. To do this, switch back to the MySQL Monitor and type the following command
     at the mysql> prompt:


       source /path/to/books.sql

     ...where /path/to/books.sql is the full path to your books.sql file. (If you ran mysql in
     the same folder as your books.sql file then you can just type source books.sql .)

     You should see the following output in MySQL Monitor:




       Database changed
       Query OK, 0 rows affected, 1 warning (0.00 sec)



open in browser PRO version      Are you a developer? Try out the HTML to PDF API                             pdfcrowd.com
       Query OK, 0 rows affected (0.10 sec)




     To check that your books table was created, you can type show tables :




       mysql> show tables;
       +---------------------+
       | Tables_in_bookstore |
       +---------------------+
       | books                 |
       +---------------------+
       1 row in set (0.00 sec)




     You can even inspect the table schema to make sure it's correct. To do this, use the explain
     command, like this:




       mysql> explain books;
       +--------+------------------+------+-----+---------+----------------+
       | Field   | Type               | Null | Key | Default | Extra                 |
       +--------+------------------+------+-----+---------+----------------+
       | id      | int(10) unsigned | NO        | PRI | NULL         | auto_increment |
       | title   | varchar(255)       | NO      |       | NULL       |               |
       | author | varchar(255)        | NO      |       | NULL       |               |
       | price   | decimal(10,2)      | NO      |       | NULL       |               |
       +--------+------------------+------+-----+---------+----------------+
       4 rows in set (0.00 sec)




open in browser PRO version    Are you a developer? Try out the HTML to PDF API                     pdfcrowd.com
     Adding records
     You've now created a database called bookstore , and added a books table to it. Let's try
     adding a record to the books table.

     To add a record to a table, you use the SQL INSERT statement, passing in the record's field
     names and values. Type the following line in the MySQL Monitor to insert a book record into
     your table:


       INSERT INTO books ( title, author, price )
          VALUES ( "The Grapes of Wrath", "John Steinbeck", 12.99 );

     You should see the following output, indicating that MySQL has added the row to the table:




       Query OK, 1 row affected (0.06 sec)




     As you can see, we've used an INSERT statement to add the book "The Grapes of Wrath" to
     the table. We specified INTO books to tell MySQL which table to insert the record into, then
     listed the field names that we want to supply values for in parentheses, followed by the keyword
      VALUES , followed by the field values in the same order as the field names, again in
     parentheses.

     Notice that we haven't specified a value for the id field. Since it's an auto_increment field,
     MySQL generates the field value automatically.

     Let's add another couple of books to the table:

open in browser PRO version     Are you a developer? Try out the HTML to PDF API                        pdfcrowd.com
       mysql> INSERT INTO books ( title, author, price )
         VALUES ( "Nineteen Eighty-Four", "George Orwell", 8.99 ),
                ( "The Wind-Up Bird Chronicle", "Haruki Murakami", 7.99 );
       Query OK, 2 rows affected (0.00 sec)
       Records: 2   Duplicates: 0     Warnings: 0




     As you can see, you can insert multiple rows at once by supplying multiple sets of field values,
     enclosed in parentheses and separated by commas.




     Retrieving records




open in browser PRO version     Are you a developer? Try out the HTML to PDF API                        pdfcrowd.com
     Now that we've added some records to the table, how can we retrieve them? This is where SQL
     queries come in. The SQL SELECT statement lets you retrieve one or more records from a
     table — or even multiple tables at once — based on criteria that you supply. The basic syntax
     is:


       SELECT fieldNames FROM tableName [WHERE criteria]




       There's a lot more to the SELECT statement than this, but we'll keep things simple in this
       tutorial!



     Let's try a basic SELECT query on our books table using the MySQL Monitor:




       mysql> SELECT * FROM books;
       +----+----------------------------+-----------------+-------+
       | id | title                             | author                 | price |
       +----+----------------------------+-----------------+-------+
       |   1 | The Grapes of Wrath              | John Steinbeck         | 12.99 |
       |   2 | Nineteen Eighty-Four             | George Orwell          |   8.99 |
       |   3 | The Wind-Up Bird Chronicle | Haruki Murakami |                7.99 |
       +----+----------------------------+-----------------+-------+
       3 rows in set (0.00 sec)




     This SELECT query retrieves all fields ( * ) from the books table. Since we haven't supplied any
     additional criteria, the query retrieves all the records in the table, and displays the field values
      in the MySQL monitor.
open in browser PRO version      Are you a developer? Try out the HTML to PDF API                           pdfcrowd.com
     in the MySQL monitor.




       As you can see, MySQL has auto-generated the values for the id field, beginning with 1.



     What if we want to retrieve just one record from the table, such as the book "Nineteen Eighty-
     Four"? To narrow down the selection, we can add a WHERE clause, like this:




       mysql> SELECT * FROM books WHERE id = 2;
       +----+----------------------+---------------+-------+
       | id | title                    | author              | price |
       +----+----------------------+---------------+-------+
       |   2 | Nineteen Eighty-Four | George Orwell |            8.99 |
       +----+----------------------+---------------+-------+
       1 row in set (0.00 sec)




     As well as selecting by the id field, we can select by any other field we like:




       mysql> SELECT * FROM books WHERE title = "Nineteen Eighty-Four";
       +----+----------------------+---------------+-------+
       | id | title                    | author              | price |
       +----+----------------------+---------------+-------+
       |   2 | Nineteen Eighty-Four | George Orwell |            8.99 |
       +----+----------------------+---------------+-------+
       1 row in set (0.01 sec)

open in browser PRO version     Are you a developer? Try out the HTML to PDF API                      pdfcrowd.com
     We can also use other operators, such as < (less than), > (greater than), and the boolean
      AND operator, to retrieve a range of records:




       mysql> SELECT * FROM books WHERE price < 10 AND price > 5;
       +----+----------------------------+-----------------+-------+
       | id | title                             | author                 | price |
       +----+----------------------------+-----------------+-------+
       |   2 | Nineteen Eighty-Four             | George Orwell          |   8.99 |
       |   3 | The Wind-Up Bird Chronicle | Haruki Murakami |                7.99 |
       +----+----------------------------+-----------------+-------+
       2 rows in set (0.00 sec)




     Finally, instead of retrieving all fields using * , we can specify just the field or fields we want to
     retrieve. Here's an example:




       mysql> SELECT title, author FROM books;
       +----------------------------+-----------------+
       | title                            | author                |
       +----------------------------+-----------------+
       | The Grapes of Wrath              | John Steinbeck        |
       | Nineteen Eighty-Four             | George Orwell         |
       | The Wind-Up Bird Chronicle | Haruki Murakami |
       +----------------------------+-----------------+
       3 rows in set (0.00 sec)




open in browser PRO version      Are you a developer? Try out the HTML to PDF API                             pdfcrowd.com
     As you can see, SELECT queries make it easy to retrieve just the records and fields you want
     from your table.




     Summary
     This tutorial has introduced you to databases in general, and MySQL in particular. You've
     covered the following topics:

        The concept of a database, which lets you easily store large amounts of structured data
        for your websites and web apps.

        Why databases are a good idea compared to, say, flat text files of data.

        Some reasons to choose MySQL for your DBMS.

        How to install MySQL in three different ways: installing the official packages, using a Linux
        package manager, and installing a LAMP/WAMP/MAMP package such as XAMPP.

        How to use the MySQL Monitor program, mysql , to connect to your MySQL server and
        issue commands.

        How to create databases with the CREATE DATABASE command.

        How to create tables with the CREATE TABLE command.

        A few MySQL data types, including int , varchar , and decimal .

        The concepts of NULL values, auto-increment fields, unique fields, and primary keys.

        Adding records to a table using the INSERT statement, and

        Retrieving records from a table with the SELECT statement.

open in browser PRO version     Are you a developer? Try out the HTML to PDF API                        pdfcrowd.com
     MySQL databases are a big topic, and there are lot more important areas to explore, including
     updating and deleting records; table relationships; normalization; joining tables; and grouping
     results. However, I hope you've found this article useful as a general introduction to MySQL.

     If you want to learn more, the MySQL manual has a decent tutorial section, including more
     details on using the MySQL Monitor, creating databases, and adding tables and data. It also
     includes some common queries that you can take and adapt for your own uses. You might also
     like to read my article Build a CMS in an Afternoon with PHP and MySQL to learn how to use
     PHP and MySQL together in practice.

     Have fun!

     [Photo credit: koalazymonkey]




     Share This Page                                       Follow Elated



     Link to this page                                     Subscribe to the Elated Newsletter
                                                           Get tips, tricks and site updates once a month! Free
       Link HTML:   <a href="http://www.elated.com/articles/mysql-for-absolute-beginners/">MySQL for Absolute Beginners</a>
                                                           web template when you sign up! Privacy


        URL only:                                   Your Email Address...
                    http://www.elated.com/articles/mysql-for-absolute-beginners/                       Send




     Related articles
open in browser PRO version    Are you a developer? Try out the HTML to PDF API                                               pdfcrowd.com
        JSON Basics: What You Need to Know

        Giveaway: Win a Professional XHTML
        Template for Your Admin Pages

        Software Giveaway: Win a PHP Link
        Directory License

        25 Web Tech Buzzwords Demystified

        Top 10 Firefox Add-ons for Webmasters




     Responses to this article

     20 most recent responses (oldest first):

      jacknet 01-Sep-11 20:18

      ok I set up the database and tried the set table command: source books.sql and I get an error:
      failed to open books.sql error 2
      Books.sql being a notepad file where I copied the code in the tutorial and saved as a file in
      xampp/mysql/book.txt changing the .txt extension to .sql. What am I not doing right?



      matt 02-Sep-11 02:30

open in browser PRO version      Are you a developer? Try out the HTML to PDF API                      pdfcrowd.com
      @jacknet: Make sure you're running the mysql command from the folder that contains your
      books.sql file. Also make sure it's called books.sql not book.sql.



      jacknet 02-Sep-11 05:56

      @matt: Yes it's in the folder/path c:\xampp\mysql\books.sql. I'm sorry but I misspelled it in my
      email but it's correct in the folder. I type: mysql> source /books.sql And still get the error. Can't
      figure it out.



      jacknet 02-Sep-11 15:10

      This tutorial doesn't work and when you try to get help everyone ignores you.



      matt 02-Sep-11 18:08

      @jacknet: I was about to help you some more, but then I saw your latest comment and it kinda
      put me off.


      But I'll just add that you don't want that slash (/) before books.sql.



      jacknet 03-Sep-11 11:24

      Matt - I apologize for my remark yesterday. I'm just frustrated because I followed your tutorial
      to the letter (more than once) and just couldn't get it to work, and still can't. I saw you
      answering other folks and felt ignored even after I sent an email. But again I apologize and
      know in life that you don't start a relationship with remarks like that and it was totally
      inappropriate for me to say what I did and expect further help. I really want to learn this. I have
open in browser PRO version      Are you a developer? Try out the HTML to PDF API                             pdfcrowd.com
      a great opportunity to join a marketing firm if I can prove I can do this stuff. I programmed in
      RPG several years ago and got out of computers, as a business, for awhile, then got back in
      about a year ago as a hardware/network tech for the local Boys and Girls Club. Now I work at
      the local library doing the same but miss programming and want to get back into it. So if you
      can forgive me and start over I'd like to learn your book. Thanks for your consideration.
      Jack



      matt 07-Sep-11 02:44

      @jacknet: No problem - just bear in mind that I'm often busy doing other things and don't
      always have the time to check the forums every few hours. I do my best to offer my help for
      free on these forums and mostly answer every question, but it's difficult when you have other
      commitments.


      I usually answer all posts in the order they were posted, so if you see me answering other
      posts first then it's usually because they were posted before yours.


      Now, with your MySQL problem, I can see that you've put your books.sql file in the wrong
      directory:


      c:\xampp\mysql\books.sql


      It should be in the same directory as your mysql binary:


      c:\xampp\mysql\bin\books.sql


      Alternatively, simply provide the full path to your books.sql file:


      source c:/path/to/books.sql
open in browser PRO version      Are you a developer? Try out the HTML to PDF API                        pdfcrowd.com
      Does that fix your problem?



      grimmdanny 07-Sep-11 09:31

      Many thanks Matt, great tutorial. I have been looking into getting started with some MySQL
      and PHP, but didn't know where to start. Managed to get Beginning PHP 5.3 book, it's a great
      start to learning the basics and enjoying it so far.



      jacknet 08-Sep-11 10:14

      @Matt: Well....I know you are going to think I'm crazy or my 'puter is. I tried every path
      possible from the Mysql prompt. I put the books.sql file in bin like you suggested which means
      I should be able to just type mysql> source books.sql and get the table to set up but all I get
      still is: Failed to open file 'books.sql' error: 2. I try every combination of commands you
      suggested in your tutorial and your forum message to me above. It just won't work. I have the
      file in the c:\xampp\mysql folder and the c:\xampp\mysql\bin folder. I don't know if you can
      suggest anything else. I know it's probably frustrating for you as it is for me. Thanks, Jack



      jacknet 08-Sep-11 10:29

      @matt: Conversely when I just run the commands: mysql> USE bookstore; That works. When I
      run: mysql> DROP TABLE IF EXISTS books; I got: Query OK, 0 rows affected, 1 warning
      <0.00 sec>. So I thought I'd setup the table with the CREATE TABLE books; command and
      when I entered the long string you have in the file book.sql it doesn't work so I'm probably
      leaveing one little thing out or it's different at the command line than in the file. I'm also setting
      this all up in another box running ubuntu so maybe I'll have better luck there. Comments?


open in browser PRO version       Are you a developer? Try out the HTML to PDF API                             pdfcrowd.com
      jacknet 08-Sep-11 12:47

      Well Matt - You're gonna love this. I'm over on my linux box now and get the same results.
      Created the source file books.sql and put it in my c:\Home\username\Documents\books.sql
      folder because I can't find the bin folder in Mysql. So I ran the command mysql> source
      c:\home\jacknet\Documents\books.sql; and same results as before. So I must be doing
      something wrong on both computers. Just wish I could figure our what that something is.
      Peace...



      matt 08-Sep-11 18:23

      @jacknet: You can't use a path like c:\home\jacknet\Documents\books.sql on a Linux box.
      Unix has no concept of a drive letter. Also the Unix path separator is '/' not '\'.


      The error you're getting (assuming it's the same error in each case) is that MySQL can't find
      the file, so you're using the wrong path in each case.



      matt 08-Sep-11 18:56

      @grimmdanny: Thanks for the kind words. I'm glad you're enjoying the book!



      jacknet 10-Sep-11 11:37

      @Matt: I agree the program/command "source" can't find the file books.sql. So let me go
      through the whole setup: I loaded xampp in the c drive. C:\xampp. In this folder is the folder
      "mysql". I put a copy of the file books.sql in that folder. In the folder mysql is a folder: bin. I
      also copied the file books.sql into that folder. So whether I run the command as follows:

open in browser PRO version       Are you a developer? Try out the HTML to PDF API                          pdfcrowd.com
      Mysql> source c:\xampp\mysql\books.sql; or Mysql> source c:\xampp\mysql\bin books.sql; or
      Msql> source books.sql; forward slash, backward slash it doesn't matter. I get the same error
      everytime "ERROR:
      Failed to open file 'books.sql', error: 2
      mysql>" This conversation is talking about on my windows 7 machine only. Do you see
      anything wrong in this. Thanks, Jack



      jacknet 10-Sep-11 11:45

      @Matt: Just to clarify more I created the file books.sql by copy/paste the code from your
      tutorial. My books.sql file looks like this:
      USE bookstore;


      DROP TABLE IF EXISTS books;
      CREATE TABLE books
      (
      id int unsigned NOT NULL auto_increment,
      title varchar(255) NOT NULL,
      author varchar(255) NOT NULL,
      price decimal(10,2) NOT NULL,
      PRIMARY KEY (id)
      );
      Ok; so I just got the idea to run the commands in the file.
      This is what I got:


      Current database: bookstore


      +--------------------+
      | Database |

open in browser PRO version       Are you a developer? Try out the HTML to PDF API                    pdfcrowd.com
      +--------------------+
      | information_schema |
      | bookstore |
      | cdcol |
      | mysql |
      | performance_schema |
      | phpmyadmin |
      | test |
      | webauth |
      +--------------------+
      8 rows in set (0.21 sec)


      mysql> USE bookstore;
      Database changed
      mysql> DROP TABLE IF EXISTS books;
      Query OK, 0 rows affected, 1 warning (0.00 sec)


      mysql> CREATE TABLE books
      -> (
      -> id int unsigned NOT NULL auto_increment,
      -> title varchar(255) NOT NULL,
      -> author varchar(255) NOT NULL,
      -> price decimal(10,2) NOT NULL,
      -> PRIMARY KEY (id)
      -> );
      Query OK, 0 rows affected (0.33 sec)


      mysql>
      Does this mean I have finally set up the database books?

open in browser PRO version      Are you a developer? Try out the HTML to PDF API   pdfcrowd.com
      jacknet 10-Sep-11 11:50

      Yes it does, to wit:
      mysql> show tables;
      +---------------------+
      | Tables_in_bookstore |
      +---------------------+
      | books |
      +---------------------+
      1 row in set (0.30 sec)


      mysql> explain books;
      +--------+------------------+------+-----+---------+----------------+
      | Field | Type | Null | Key | Default | Extra |
      +--------+------------------+------+-----+---------+----------------+
      | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
      | title | varchar(255) | NO | | NULL | |
      | author | varchar(255) | NO | | NULL | |
      | price | decimal(10,2) | NO | | NULL | |
      +--------+------------------+------+-----+---------+----------------+
      4 rows in set (0.06 sec)
      but this does not explain the mystery of the source command finding the file books.sql.



      jacknet 10-Sep-11 14:16

      Well I certainly have used up this thread pretty good. Just to let you know I completed the
      tutorial on both boxes and as long as I leave that file books.sql alone I'm ok, but I sure wish I
      could solve that mystery because I'm sure I'll need it in the future. On to build a CMS in an

open in browser PRO version         Are you a developer? Try out the HTML to PDF API                      pdfcrowd.com
       afternoon. Please if you get a chance though Matt let me know your final thoughts on the
       books.sql mystery. Thanks, Jack



       matt 12-Sep-11 05:40

       @jacknet: "Does this mean I have finally set up the database books?"


       Yes. You can always just copy & paste SQL into the MySQL monitor.


       No idea why you're having so many problems with your paths. I don't usually use Windows, but
       on my Win7 VM I can get it working no problem. You must use '/' not '\' in the MySQL monitor.


       Feel free to start a new topic if you're still having path problems:


       http://www.elated.com/forums/authoring-and-programming/topic/new/



       srafferty 05-Nov-11 10:42

       Matt-


       Thanks for posting this tutorial, I am trying to learn the basics here and am looking forward to
       completing your walk-through. One problem though- when I get to the part after you install
       XAMPP and try to start mysql (I'm on a Mac), I go to terminal and enter "mysql -u root", it just
       returns "-bash: mysql: command not found".


       Any ideas? I've tried googling this problem and cannot find anyone else having the exact
       same issue. Lots of people having problems starting mysql but none with the same issue.


       Hoping that I'm screwing something simple up here...
open in browser PRO version Are you a developer? Try out the HTML to PDF API                              pdfcrowd.com
      Hoping that I'm screwing something simple up here...


      Any help is GREATLY appreciated!!!



      matt 07-Nov-11 00:33

      @srafferty: I'm guessing you're running Lion? Apple removed MySQL by default in Lion (it now
      uses PostgreSQL). Therefore the default Apple mysql command line program will no longer be
      in your path, so you'll need to explicitly use the XAMPP version instead.


      Try:


      /Applications/XAMPP/xamppfiles/bin/mysql -u root


      If that works, you can add /Applications/XAMPP/xamppfiles/bin/ to your path, then you can just
      type mysql next time!


      Cheers,
      Matt




     View all 25 responses »



     Post a response
     Want to add a comment, or ask a question about this article? Post a response.

     To post responses you need to be a member. Not a member yet? Signing up is free, easy and
     only takes a minute. Sign up now.

open in browser PRO version     Are you a developer? Try out the HTML to PDF API                       pdfcrowd.com
                                                Top of Page




                                                                             Advertise Here




    Home                      Articles                           This site © Elated Communications Ltd 1996-2011.

    About Elated              PageKits                           Unauthorised copying or redistribution prohibited. By

    Advertise                 ImageKits                          using this Website, you are indicating your

    Contact Us                ActionKits                         acceptance of our Terms of Use.

    FAQ                       Forums

    Spamwars                  Newsletter

    Link to Us

    RSS

    Twitter
    Facebook




open in browser PRO version   Are you a developer? Try out the HTML to PDF API                                           pdfcrowd.com

								
To top