MySQL is an open source cross-platform relational database management system (RDBMS). MySQL uses the Standard Query Language (SQL) to communicate with the MySQL software.
Database administrators and developers interact with the RDBMS by executing statements called queries in database administration. This tutorial shows you how to use MySQL to run queries on your Linux terminal.
Read: Relational Database Management Systems (RDBMS) MSSQL vs MySQL
How to create a database in the MySQL shell
Example of a Linux terminal window
Databases are used to store organized data and information. In a database, related items are stored in a table. These tables, in turn, contain columns that store data entries of a particular type, each of which has a corresponding row.
With that in mind, now is the time to start your MySQL server. If you haven’t already installed MySQL, you can do so using the following commands:
$ sudo apt-get install mysql-server $ sudo mysql_secure_installation # Follow the instructions
If you just installed MySQL, your server should already be started. You can check the status of your server with:
$ sudo service mysql-status
If you have already installed MySQL, you can start the server with the following command:
$ sudo service mysql start
After starting the service, you will now get to the interactive MySQL shell on your terminal:
$ sudo mysql
To create a database, use the CREATE DATABASE keywords followed by the database name:
> CREATE DATABASE School;
Note that MySQL statements are case-insensitive. Hence, the above statement is similar to:
> Create database school;
Note that the first style – ALL UPPER CASE – is preferred out of convention. It is for this reason that the rest of this tutorial will use this format.
Note the semicolon; at the end of the statement. It means completing an instruction. If you press Enter without it, the instruction will not run until the symbol is found.
After you’ve created your database, it is good to know that your database has been saved successfully. You can display the currently available databases with:
> Show databases;
Next, you need to choose the database that you want to interact with. In this case the school database:
> Use school;
You now need to create some tables in your database. The syntax for creating tables in a MySQL database is:
CREATE TABLE table name (column1 data type1, column2 data type2, …);
To create the student table with ID, fname, lname and age columns as shown below:
> Create table student (-> ID int, -> fname varchar (255), -> lname varchar (255), -> alter int);
MySQL supports a number of data types which can be referenced on their official page. For our purposes, the ID column is of the data type int, which represents an integer value.
The type varchar represents a string of variable length. It takes the maximum number of expected characters as an argument. For the above example, fname and lname cannot have more than 255 characters. The maximum number of characters that varchar can accept is ((2 ^ 16) -1).
Notice the indentation in the MySQL statement above. It helps fragment the code into a more readable format. As already mentioned, the instruction is only executed when the; is encountered.
To see the table we created, use the DESCRIBE keyword:
> DESCRIBE the student;
The table created does not currently contain any entries and has NULL values. Use the INSERT keyword to populate the table. The syntax is as follows:
INSERT INTO table name (column1, column2, …) VALUES (value1, value2, …);
Here is an example of inserting values into a table using MySQL:
> INSERT IN -> student (ID, fname, lname, age) -> VALUES (2501, “Jack”, “Andrews”, 16);
Read: PHP Database Options: More Than Just MySQL
How to update and delete a MySQL database
Suppose you entered the wrong entry and want to change it. This is where the UPDATE statement comes into play. The syntax for this database query is:
UPDATE table name SET column1 = value1, column2 = value2,… columnN = valueN WHERE condition;
Here is an example showing how to update a database entry using the Update keyword in MySQL:
> UPDATE student -> SET lname = “Different” -> WHERE fname = “Jack”;
Suppose you want to clear a specific student’s data. To do this, you would use the DELETE FROM statement with the following syntax:
DELETE FROM table-name WHERE-condition; > DELETE FROM student WHERE fname = “Jack”;
Note that you must include the WHERE clause. If you don’t include it, the entire table will be deleted.
You may also want to know what data has been stored in your spreadsheet. Use the SELECT statement with the following syntax to view the data stored in your tables:
SELECT column1, column2, .. columnN FROM table-name;
To select all columns, use an asterisk * or wildcard character instead:
SELECT * FROM table-name;
Read: MySQL INSERT or REPLACE commands
Beyond MySQL
There are a number of database management systems that use SQL, including SQL Server, Oracle, and Postgres. This means that the knowledge you have acquired here (with minimal changes) can be transferred to other database software. MySQL is one of the most powerful RDBMS software, so it pays to take some time to learn it thoroughly.
