Posted: . At: 1:00 PM. This was 2 years ago. Post ID: 4436
Page permalink. WordPress uses cookies, or tiny pieces of information stored on your computer, to verify who you are. There are cookies for logged in users and for commenters.
These cookies expire two weeks after they are set.

Creating a simple database with MySQL on either Linux or Windows. This will get you started.

The example below shows the database I am creating with MySQL. This is a list of the operating systems and Linux distributions I have used over the years. Not all but a good listing.

I use the mysql -uroot -p command to start MySQL, then I type in the password that I entered after installing the MySQL server software. The database I am using was created with the create database fun; command within MySQL.

C:\HOME\FLYNN> mysql -uroot -p
Enter password: 
Welcome TO the MySQL monitor.  Commands END WITH ; OR \g.
Your MySQL connection id IS 48
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)
 
Copyright (c) 2000, 2011, Oracle AND/OR its affiliates. ALL rights reserved.
 
Oracle IS a registered trademark OF Oracle Corporation AND/OR its
affiliates. Other names may be trademarks OF their respective
owners.
 
TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the CURRENT INPUT statement.

Now create a database named ‘fun’.

mysql> CREATE DATABASE fun;
Query OK, 1 ROW affected (0.29 sec)

Then we switch to the new database we wish to use.

mysql> USE fun;
DATABASE changed

Then we may begin creating database tables within that database. Create a table named ‘operatingsystems’.

mysql> CREATE TABLE operatingsystems (OsID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(64), TYPE VARCHAR(30), vendor VARCHAR(30));
Query OK, 0 ROWS affected (0.53 sec)

Once the table is done, we can begin creating records in the table.

mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Windows 3.0', 'Windows', 'Microsoft');
Query OK, 1 ROW affected (0.10 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Windows 3.1', 'Windows', 'Microsoft');
Query OK, 1 ROW affected (0.06 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Windows `95', 'Windows', 'Microsoft');
Query OK, 1 ROW affected (0.04 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Windows `98', 'Windows', 'Microsoft');
Query OK, 1 ROW affected (0.03 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Redhat 6.2', 'Linux', 'Redhat');
Query OK, 1 ROW affected (17.43 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'FreeBSD 6.2', 'UNIX', 'FreeBSD Developers');
Query OK, 1 ROW affected (0.04 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Mandrake Linux 9.0', 'Linux', 'Mandrake');
Query OK, 1 ROW affected (0.03 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Mandrake Linux 9.2', 'Linux', 'Mandrake');                                 
Query OK, 1 ROW affected (0.86 sec)                                                                                                                     
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'SUSE Linux 9.2', 'Linux', 'SUSE');                             
Query OK, 1 ROW affected (0.24 sec)                                                                                                                     
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'SUSE Linux 10', 'Linux', 'SUSE');                                         
Query OK, 1 ROW affected (0.04 sec)                                                                                                                     
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'SUSE Linux 10.2', 'Linux', 'SUSE');                                          
Query OK, 1 ROW affected (0.04 sec)                                                                                                                     
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Ubuntu 8.10', 'Linux', 'Canonical');                               
Query OK, 1 ROW affected (0.05 sec)                                                                                                                     
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Ubuntu 9.04', 'Linux', 'Canonical');                                                                                                 
Query OK, 1 ROW affected (0.04 sec)                                                                                                                                                                               
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Ubuntu 9.10', 'Linux', 'Canonical');
Query OK, 1 ROW affected (0.20 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Ubuntu 10.04', 'Linux', 'Canonical');
Query OK, 1 ROW affected (0.07 sec)
 
mysql> INSERT INTO operatingsystems(OsID, Name, TYPE, vendor) VALUES (NULL, 'Ubuntu 10.10', 'Linux', 'Canonical');
Query OK, 1 ROW affected (0.05 sec)

After I was finished entering the data I request a listing of the data.

mysql> SELECT * FROM operatingsystems;
+------+--------------------+---------+--------------------+
| OsID | Name               | TYPE    | vendor             |
+------+--------------------+---------+--------------------+
|    1 | Windows 3.0        | Windows | Microsoft          |
|    2 | Windows 3.1        | Windows | Microsoft          |
|    3 | Windows `95        | Windows | Microsoft          |
|    4 | Windows `98        | Windows | Microsoft          |
|    5 | Redhat 6.2         | Linux   | Redhat             |
|    6 | FreeBSD 6.2        | UNIX    | FreeBSD Developers |
|    7 | Mandrake Linux 9.0 | Linux   | Mandrake           |
|    8 | Mandrake Linux 9.2 | Linux   | Mandrake           |
|    9 | SUSE Linux 9.2     | Linux   | SUSE               |
|   10 | SUSE Linux 10      | Linux   | SUSE               |
|   11 | SUSE Linux 10.2    | Linux   | SUSE               |
|   12 | Ubuntu 8.10        | Linux   | Canonical          |
|   13 | Ubuntu 9.04        | Linux   | Canonical          |
|   14 | Ubuntu 9.10        | Linux   | Canonical          |
|   15 | Ubuntu 10.04       | Linux   | Canonical          |
|   16 | Ubuntu 10.10       | Linux   | Canonical          |
+------+--------------------+---------+--------------------+
16 ROWS IN SET (0.01 sec)

This really is that easy, I had a hard time mastering the SQL statements at first, but now it is starting to make sense. The OsID int not null primary key auto_increment value creates a primary key that will automatically increment to give each entry a unique identification for the database to function properly. The massive Microsoft SQL Server 2008 product I have looked at also lets you manage a database, that is quite a hefty program, but very powerful indeed. But using the MySQL command-line and SQL code to enter data is still a good way of managing a database unless it is connected to an external program or web application that is writing and reading from the database. But knowing how to manage a MySQL database with SQL code is an important skill.

To list all the databases in our MySQL installation use the show databases query.

mysql> SHOW DATABASES;
+--------------------+
| DATABASE           |
+--------------------+
| information_schema |
| fun                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 ROWS IN SET (0.00 sec)

To list all of the tables in a certain database use this command.

mysql> SHOW TABLES IN fun;
+------------------+
| Tables_in_fun    |
+------------------+
| operatingsystems |
+------------------+
1 ROW IN SET (0.00 sec)

And this is how to use plain text queries to search for certain text in a column.

mysql> SELECT * FROM operatingsystems WHERE Name RLIKE "server";
+------+---------------------+---------+-----------+
| OsID | Name                | TYPE    | vendor    |
+------+---------------------+---------+-----------+
|    6 | Windows 2003 Server | Windows | Microsoft |
|    7 | Windows 2008 Server | Windows | Microsoft |
+------+---------------------+---------+-----------+
2 ROWS IN SET (0.00 sec)

1 thought on “Creating a simple database with MySQL on either Linux or Windows. This will get you started.”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.