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

Posted: August 16, 2012. At: 5:34 PM. This was 5 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.

This 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.
 
mysql> use fun;
Database changed
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)
 
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)
Microsoft SQL Server 2008.
Microsoft SQL Server 2008.
1 responses to “Creating a simple database with MySQL on either Linux or Windows. This will get you started.

Leave a Reply