Learning MySQL commands: creating a server database

Learning MySQL commands: creating a server database

Learn how to set up a Structured Query Language (SQL) database using SQL tools

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 open-source software, meaning it is 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 commands 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 MySQL with the root username. If you have already set up another account, use that log-in information.
2. Create a Database. Now the command line 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 database name.

   mysql> create database first_database;

3. Create a Table. A database is a collection of data organized into tables. The following code creates a table with 6 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).
   • Lines 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-mail 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 in timestamp(14) 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', '[email protected]', 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.
   • Lines 3 and 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 in 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 address 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 ([email protected]).

   mysql> UPDATE first_table SET email = '[email protected]'
-> 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 last_name = '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 is 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 perform some maintenance tasks manually.