Learning MySQL Commands: Creating a Server Database

Learn How To Setup a Structured Query Language (SQL) Database by Using SQL Tools

Enjoying home office

A database organizes data so that it can be easily accessed, altered, and managed. Like virtually all database software, MySQL uses Structured Query Language (SQL-pronounced sequel), the standard language for requesting information from a database. It is an open-source software, meaning that its free to use and modify for your personal use, and we will provide SQL help in this article to make you comfortable using it. Despite its low price, MySQL has increasingly found wide adoption in industry because of its fast performance, reliability, ease of use, and versatility in working with programming languages. Join the 5 million active users of MySQL including NASA, FedEx and the computer nerd down the block by learning to use the basic asdcommands of MySQL.

It is not difficult to learn MySQL if you follow this MySQL tutorial step-by-step. 

  1. Make sure you have installed MySQL correctly. Once you have done so, go to the command line application of your operating system and enter: 'mysql -u root'. This opens up MySQL in the root username. If you have already set another account, use that log-in information.
  2. Create a Database. Now the commandline will read 'mysql>' and everything you type after it will be processed by MySQL. We will use the "create database" command to create a database called first_database. Notice that there can be no space in the filename.

    mysql> create database first_database;

  3. Create a Table. A database is a collection of data organized into tables. The following code mysql> creates a table with 5 columns:

    mysql> create table first_table
    -> (ID int not null primary key auto_increment,
    -> last_name varchar(25),
    -> first_name varchar(25),
    -> email varchar(25),
    -> date_joined timestamp(14),
    -> about text);

    • Line 1: Creates a column labeled ID that generates a unique integer (int) number that increases by an increment (auto_increment) of one for every new entry. This is known as a primary key because every record (or row) has a unique id to differentiate the rows (useful when several people have the same last name).
    • Line 2 and 3: Creates columns labeled "last_name" and "first_name" that contain the last names and first names, respectively, of entries. The maximum number of characters is 25 ( varchar(25) ).
    • Line 4: Creates a column for the e-maillearn mysql addresses of members. Again the maximum number of characters is 25.
    • Line 5: Creates a column that records the date (timestamp) the member joined. The number 14 'timestamp()' refers to the most commonly used display format: YYYYMMDDHHMMSS.
    • Line 6: Creates a column with a short description of the member.
  4. Insert Data. Now we will manually add data to the table:

    mysql> insert into first_table
    -> (ID, last_name, first_name, email, date_joined, about) values
    -> (NULL, 'Doe', 'John', 'johndoe@emailaddress.com' ,now(),
    -> 'This is a short description of John Doe.');

    • Line 1: Tells MySQL that you want to add a record to the table.
    • Line 2: Lists the names of the columns in the order we will use to enter the values.
    • Line 3+4: Lists the values for the columns in the order listed previously. The value 'now()' makes the timestamp record the time and date when the row was created.

    Repeat this until there are a total of four records so that we can run a query.

  5. Running Queries. Now that there are a total of four records we can run a query, the database term for requesting data from a table. We use the 'select' command to query 'first_table'. Here we specifically want to see the last and first names and email addresses from the database.

    mysql> select last_name,first_name,email from first_table;

    The asterisk after the 'select' command is known as the universal selector meaning that you want to query every column.

    mysql> select * from first_table;

  6. Updating an Entry. The next step in learning SQL is updating. Let's say John Doe has changed his e-mail address because of too much spam. The following code uses the 'UPDATE' command and 'SET' subcommand to change the email addresses of a person whose last name is 'Doe'. The 'WHERE' subcommand is important because it determines which person's email address should be changed (where the last name is Doe). Without the 'WHERE' command all records in 'first_table' would have the same email address (johnnydee@emailaddress  .com).

    mysql> UPDATE first_table SET email = 'johnnydee@emailaddress .com'
    -> WHERE last_name = 'Doe';

  7. Deleting a Record. You may need to make changes in the MySQL server. Let's say John Doe dies (I couldn't resist), or maybe he just opted-out of an email subscription list. To delete a record, no surprise here, we use the 'DELETE' command. 'FROM' determines the table you are working with and 'WHERE' determines the specific record you want to remove.

    mysql> DELETE FROM first_table WHERE lastname = 'Doe';

Now you have learned the MySQL commands and MySQL tools to perform basic database tasks. The MySQL documentation website is a great resource for learning about new commands. While it's probably more practical to use a GUI based database administration program, knowing the basics of SQL will be very useful to you especially if you want to work with a programming language or want to perform some maintenance tasks manually.

 

Share this article!

Follow us!

Find more helpful articles: