Tutorials > Setting Up a Development Environment > Using MySQL - The Basics

5. Using MySQL - The Basics

  • This article assumes you have completed the previous tutorials up to: Installing MySQL
  • Goto Start > Run (Win+r) and type in 'cmd' (If you didn't restart your computer after MySQL installation the next part won't work)
  • In the command prompt type 'mysql' and hit enter.
  • You should get an access deinied error.
  • This time type 'mysql -u root -p' and it should prompt you to enter a password, type in the password you used in MySQL setup.

    mysql -u root -p
  • You are now logged into MySQL via command prompt.
  • Type 'show databases;' and hit enter. This will show all the databases on your server (these are the databases MySQL has installed by default, you shouldn't touch the schema or mysql ones unless you know what you're doing).

    show databases;
  • We can use the test database. First, lets delete it by typing 'drop database test;' and hitting enter.

    drop database test;

  • If you type 'show databases;' you will see it is no longer there.
  • Type 'create database test;' to recreate the test database.

    create database test;
  • Now that we have our test database, lets start working with tables.
  • We don't have a database selected, so if we type 'show tables;' we'll get an error.

    show tables;

  • Type 'use test;' to select the test database.
  • Type 'show tables;' again and you will see the test database is empty.

    use test;
  • Create a table called groceries using CREATE TABLE and then use DESCRIBE to show it's structure.

    CREATE TABLE groceries (item_name VARCHAR(255), in_cart BOOLEAN);
    DESCRIBE groceries;
  • Add some data using INSERT and then view it using SELECT.

    INSERT INTO groceries (item_name, in_cart) VALUES ('Cereal', 0), ('Milk', 0), ('Bread', 0);
    SELECT * FROM groceries;
  • Change the data using UPDATE.

    UPDATE groceries SET in_cart = 1 WHERE item_name = 'Milk';
    SELECT * FROM groceries;
  • Lastly, remove rows using DELETE.

    DELETE FROM groceries WHERE item_name = 'Bread';
    SELECT * FROM groceries;

Return to TutorialsNext Tutorial: Connecting PHP and MySQL