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.”