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 admins and developers interact with RDBMS by running statements, which are referred to as queries in database management. In this tutorial, you will learn how you can run queries on your Linux terminal with MySQL.
Read: Relational Database Management Systems (RDBMS) MSSQL vs MySQL
How to Create a Database in the MySQL Shell
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 this background knowledge, it is now time to start your MySQL server. If you do not have MySQL installed yet, you can do so using the following commands:
$ sudo apt-get install mysql-server $ sudo mysql_secure_installation # follow the prompts
If you have just installed MySQL, then your server should already be started. You can check the status of your server using:
$ sudo service mysql status
If you already had MySQL installed, then you can start the server using:
$ sudo service mysql start
After starting the service, you can now get into the interactive MySQL shell on your terminal:
$ sudo mysql
To create a database, use the keywords CREATE DATABASE, followed by the database name:
> CREATE DATABASE school;
It’s important to note that statements in MySQL are case insensitive. Therefore, the above statement is similar to:
> create database school;
Note that the first style – ALL CAPS – is preferred because of convention. For that reason, the rest of this tutorial will be using that format.
Notice the semicolon ; at the end of the statement. It signifies the completion of a statement. If you press the Enter key without it, the statement will not run until the symbol is encountered.
After creating your database, it would be good to know that your database has been successfully saved. You can show the currently available databases using:
> show databases;
Next, you will need to choose the database that you are going to interact with. In this case, the school database:
> use school;
You now need to create some tables in your database. The syntax to create tables in a MySQL database is:
CREATE TABLE table_name (column1 datatype1, column2 datatype2, …);
Therefore, to create the student table containing the columns ID, fname, lname, and age, as shown below:
> create table student( -> ID int, -> fname varchar(255), -> lname varchar(255), -> age int);
MySQL supports a number of data types, which can be referenced on their official page. For our purposes, the ID column has the data type int, representing an integer value.
The type varchar represents a string of variable length. It takes in the maximum number of expected characters as its argument. For the example above, fname and lname can not have more than 255 characters. The maximum number of characters that varchar can accept is ((2^16) -1).
Notice the indentation in the above MySQL statement. It helps fragment the code into a more readable format. As mentioned earlier, the statement will not run until the ; is encountered.
To view the table we created, use the DESCRIBE keyword:
> DESCRIBE student;
The table created currently lacks any entries and has NULL values. To populate the table, use the keyword INSERT. The syntax is as follows:
INSERT INTO table_name(column1,column2,...) VALUES (value1,value2,...);
Here is an example of how to insert values into a table with MySQL:
> INSERT INTO ->student(ID,fname,lname,age) ->VALUES(2501,"Jack","Andrews",16);
Read: PHP Database Options: More Than Just MySQL
How to Update and Delete From a MySQL Database
Suppose you had entered a wrong entry and wanted to modify it. This is where the UPDATE statement would come in. The syntax for making 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 ="Anders" -> WHERE fname = "Jack";
Now, say you want to delete a certain student’s details. For this, you would use the DELETE FROM statement with the following syntax:
DELETE FROM table_name WHERE condition; > DELETE FROM student WHERE fname = "Jack";
It is important for you to note that you must include the WHERE clause. Not including it will delete the whole table.
You may also want to know which data has been stored in your table. Use the SELECT statement with the syntax below to do view 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
Looking 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 is transferable (with minimal changes) to other database software . MySQL ranks among the most powerful RDBMS software, therefore it would be well worth your effort to take some time to closely learn it.
