Delving into the world of database management, MySQL stands out as a robust and versatile choice. At its core are MySQL database queries, the essential tools for interacting with the database. These queries allow users to retrieve, modify, and organize data within a MySQL database. In this article, we will explore the ins and outs of MySQL database queries, including their syntax, functions, and best practices. Whether you’re a seasoned developer or a newcomer, understanding MySQL database queries is fundamental for optimizing database operations and gaining valuable insights from your data.

Terms and definitions

Database: A structured collection of data organized in a way that it can be easily accessed, managed, and updated.
Database Management System (DBMS): Software that interacts with end-users, applications, and the database itself to capture and analyze data. MySQL is a popular DBMS.
MySQL: An open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) for managing and manipulating data.

Key Concepts

Relational Database:

1. Organize data into tables with rows and columns.

2. Relationships between data entities are maintained.
Table:

1. Basic storage unit in MySQL.

2. Consists of rows (records) and columns (fields).
Column (Field):

1. Represents a specific piece of data in a table.

2. Each column has a data type that defines what type of data it can hold (e.g., int, varchar, date).

Row (Record):
1. A single entry in a table, containing data for each column.
Primary Key:

1. A unique identifier for each row in a table.

2. Helps in uniquely identifying rows.
Foreign Key:

1. A field in a table that refers to the primary key in another table.

2. Establishes relationships between tables.

How to access MySQL server

Once the MySQL server is installed, you can access it on the command line, by running the following command:

mysql -u root      #for passwordless login
mysql -u root -p   #when you have the root password. Input the same password set when installing the service.
mysql -u myuser -p -h remote.example.com -P 3306 -D mydatabase  #To access the database from a remote server.

Fetch all databases

To get a list of the databases created on the server, run the following commands:

show databases;

create a database

To create a new database, run the following command replacing database_name with the preferred darabase name.

create database database_name;

Switch to a specific database

Once you identify the database you wish to work with, switch or activate it through the command:

use databasename;   #switches to the mentioned database 

Data Types used in Mysql

MySQL supports various data types for defining columns in a table. These data types determine the kind of data that can be stored in a column:

Numeric Types:

• INT or INTEGER: Integer type, can be signed or unsigned.
• TINYINT: Very small integer, typically used for boolean values.
• SMALLINT: Small integer.
• MEDIUMINT: Medium-sized integer.
• BIGINT: Large integer.
• FLOAT: Single-precision floating-point number.
• DOUBLE or REAL: Double-precision floating-point number.
• DECIMAL or NUMERIC: Fixed-point exact decimal value.

Date and Time Types:

• DATE: Date in the format YYYY-MM-DD.
• TIME: Time in the format HH:MM:SS.
• DATETIME: Combined date and time in the format YYYY-MM-DD HH:MM:SS.
• TIMESTAMP: Combined date and time, similar to DATETIME but automatically converts to the session’s time zone.
• YEAR: Year in 4-digit format (e.g., 2023).

String Types:

• CHAR(size): Fixed-length string, maximum size is 255 characters.
• VARCHAR(size): Variable-length string, maximum size is 65535 characters.
• TEXT: Variable-length string, that can hold a large amount of text.
• BLOB: Binary large object, for storing binary data.
• ENUM: Enumeration type, where each column value must be one of a predefined list of values.
• SET: Similar to ENUM, but a column can have multiple values from the predefined list

Create a table

To create a database table, ensure that you switch to the database where you want to create the table.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    location VARCHAR(255),
    income INT
);
#Here, the table is named users

Inserting data into the table

To insert data into a database table use the INSERT keywork.

INSERT INTO users (name, age, location, income) VALUES ('John', 30, 'New York', 80000);
INSERT INTO users (name, age, location, income) VALUES ('Emily', 35, 'San Francisco', 95000);

Describe a table

The describe keyword gives more information about a table. The information returns contain the table Fields, data types of each field, default values, Null value status, extra tags, and key tags.

describe table_name; 

Select data

To select data, you can opt to fetch all columns of a table or selected columns.

select * from users;  #select all table entries from the users table
select name,income from users; #Returns only the name and income columns of the users table 
select * from users limit 5;  #selects the first 5 rows of the users table.

Deleting a table

To delete a table, note that all table records are deleted. Run the following command:

drop table table_name;

Database querying

Besides running the database from the database command line interface, the commands can be added to a file and given extensions such as .txt, .sql etc. With the commands, on the terminal, run the following.

mysql -u db_username -p < data.txt   #where data.txt contains sql commands.

The same file can be run from the database command line. Having logged into the database, run the following, ensuring that the data is on the same path that you accessed the database on.

source data.txt

Sample data for the above examples

Copy and paste the code below into a file and use it for the above practice.

CREATE DATABASE IF NOT EXISTS mydatabase;
use mydatabase;
CREATE TABLE if not exists user (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    income DECIMAL(10, 2),
    location VARCHAR(255),
    gender ENUM('Male', 'Female', 'Other')
);

INSERT INTO user (name, income, location, gender)
VALUES
    ('John Doe', 60000.00, 'New York', 'Male'),
    ('Jane Smith', 75000.00, 'Los Angeles', 'Female'),
    ('Michael Johnson', 55000.00, 'Chicago', 'Male'),
    ('Emily Davis', 68000.00, 'San Francisco', 'Female'),
    ('Alex Thompson', 50000.00, 'Houston', 'Male'),
    ('Jessica Lee', 72000.00, 'Miami', 'Female'),
    ('David Kim', 63000.00, 'Seattle', 'Male'),
    ('Sarah Miller', 59000.00, 'Boston', 'Female'),
    ('Kevin Brown', 66000.00, 'Dallas', 'Male'),
    ('Laura Taylor', 71000.00, 'Phoenix', 'Female');

Executing the preceding command with the provided data will create a new database named “mydatabase” if it does not already exist. The process then involves switching to the newly created database, creating a table named “user,” and inserting the relevant data. Proceed and query the data.

WHERE clause

Where clause can be used to access the data by filtering records from the database that fulfills some conditions. eg.

select * from users where id=1   #returns all records whose id is 1.

AND in MySQL

The AND clause fetches data that satisfies two or more specified conditions. Usage example:

select * from users where age > 30 AND income > 50000; # returns all users who are above 30 years and whose income is above 50000.

OR in MySQL

The OR clause fetches data that satisfies any of the given conditions. For example:

select * from users where age > 30 OR income > 50000; # returns all users who are above 30 years OR whose income is above 50000.

Sorting Data

When querying data, you might want the output to follow a specific order as per your preference. Identify the column that you want to use to order your data.

ORDER BY clause

This is the clause used to return data in an ordered list. By default, the ORDER BY will return the data in reference to the order column in ascending order (from the smallest to the biggest). To reverse this, use DESC. Usage example;

select * from users ORDER BY income;  #returns the users and orders them from one with the least income to one with the highest income.
select * from users ORDER BY income DESC;   returns the reverse of the above; income from highest to the lowest

Working with Null values

The table data may have some missing data. To fetch data in reference to such records, we do not use greater, less than, or equal signs. Instead, we use IS NULL and NOT IS NULL to filter such records. Usage example;

select * from users where income IS NOT NULL;

Matching patterns

when querying large volumes of data, you can consider using matching patterns to return specific data entries. Check out the use cases below.

LIKE clause

select * from users where name Like 'D%'   #Returns all users whose names start with D.
select * from users where name Like '%om' #Returns all users whose names end with om
select * from users where name Like '%u%' # selects all users whose names have a u
select * from users where name Like 'D%' 
select * from users where name Like '___' #selects records whose name's length is the same as the number of dash used.

UPDATE Data

In MySQL, we use the update clause to update records on our table. Usage example:

update users set income=120000 where id=1  #users is the table, income=12000; here you define what update to be made; id=1 defines the data to be updated. 

NOTE: Failing to set the criteria for the update will update all records!

Deleting records from the table

We can delete some records from the table. See the example below.

delete from users where id=1  #deletes the record whose id=1;

NOTE: Failing to add the criteria will lead to the deletion of all the data.

Add a new column to a table

You may encounter a situation where you need to add a new database column long after designing the table. To add a new column, run the command:

ALTER TABLE user ADD COLUMN email VARCHAR(255); #alters the table user, and adds a new column named email

Drop a table column.

To drop a table column, use the command:

ALTER TABLE user DROP COLUMN email;
   #drops the column email.

---

---

Web Hosting and email hosting Packages

---

For web development services, SEO services, Digital marketing strategies, website set up services, web hosting and domain registration; contact Dynamic Technologies.

---

Related content:

• A Practical Tutorial for Dockerizing Software Applications
• How to Configure a Docker App to a Domain Name
• Getting Started with Docker | Docker commands
• How To Run Scripts in Linux
• Deploy a Django Application on EC2 Instance with Nginx
• How to configure a domain to a docker container and install an SSL certificate on AWS