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: http://securitronlinux.com/bejiitaswrath/creating-a-simple-database-with-mysql-on-either-linux-or-windows-this-will-get-you-started/

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