【MySQL】 Data Search: Mastering SELECT Statements, WHERE Clauses, and Operators

Data search in MySQL is a fundamental aspect of database management and is essential for efficient operation.

In this article, we begin with the basics of the SELECT statement and then delve into the use of crucial operators like AND, OR, and NOT within the WHERE clause.

By mastering these operators, you will learn how to efficiently extract necessary information from complex data sets.

▼youtube

The Database and Tables We’ll Use in This Session

In this exploration of data search, we will create a database for managing school-related data and explain the process of constructing the necessary tables within it.

We will create two tables: the ‘students’ table, which contains information about the students, and the ‘departments’ table, detailing the departments to which students belong.

• ‘Students’ Table: This table stores various details about the students, including their names, grades, and department IDs.
• ‘Departments’ Table: This table records information related to each department, such as the department name and its quota.”

students Table

id	ID Number
name	Student Name
grade	Grade
department_id	ID of the Department the Student Belongs To
entry_exam	Entrance Exam Score
status	Enrollment Status
updated_at	Date Updated

departments

departments Table

id	ID Number
name	Department Name
quota	Capacity

SQL Script: Creating a Database and Adding Data

— Creating the Database
CREATE DATABASE myschool_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

— Verifying Database Creation
SHOW DATABASES;

— Selecting the Database to Use
USE myschool_db;

— Creating Tables
CREATE TABLE students (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  grade INT NOT NULL DEFAULT 1,
  department_id INT,
  entry_exam INT,
  status CHAR(3) NOT NULL DEFAULT ‘ACT’,
  updated_at TIMESTAMP NOT NULL
);

CREATE TABLE departments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL UNIQUE KEY,
  quota INT NOT NULL
);

— Verifying Tables
SHOW TABLES;
DESCRIBE students;
DESCRIBE departments;

— Adding Data
INSERT INTO departments
VALUES
(NULL, ‘International Economics’, 40),
(NULL, ‘International Regional Studies’, 30),
(NULL, ‘Japanese Literature and Culture’, 40),
(NULL, ‘Criminal Psychology’, 20),
(NULL, ‘Social Psychology’, 80);

INSERT INTO students
(name, grade, department_id, entry_exam, updated_at)
VALUES
(‘Yamanaka Shinji’, 4, 1, 82, NOW()),
(‘Sato Hanako’, 4, 2, 68, NOW()),
(‘Shimoda Tetsuo’, 3, 3, 77, NOW()),
(‘Morikawa Shinkichi’, 2, 1, 99, NOW()),
(‘Niiyama Saeko’, 1, 1, 84, NOW()),
(‘Oomura Koji’, 3, 3, 67, NOW()),
(‘Motomiya Ritsuko’, 1, 2, 75, NOW());

— Verifying Data
SELECT * FROM departments;
SELECT * FROM students;

Data Search with the SELECT Statement

• List the column names separated by commas.
• Use ‘*’ for column_name to select all columns.
• Specify the search target using the WHERE clause.”

Retrieve all data from the students table:

SELECT * FROM students;

Retrieve the student name and grade for the student with id 1:

SELECT name, grade FROM students
WHERE id=1;

Operators used in the WHERE clause:

=	Equals
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to
<>	Not equal to
!=

• Retrieve member data for the member named ‘Yamanaka Shinji’:

SELECT * FROM students
WHERE name=’Yamanaka Shinji’;

• Search for members in the 3rd grade or higher:

SELECT * FROM students
WHERE grade>=3;

• Retrieve student information updated on or after May 5th, 2005:

SELECT * FROM students
WHERE updated_at>=’2005-05-05′;

Operators for Connecting Conditional Expressions:

AND	And
OR	Or
NOT	Not

• Search for students in the 3rd grade or higher who have had their information updated on or after May 5th, 2005:

SELECT * FROM students
WHERE grade>=3
AND updated_at>=’2005-05-05′;

• Search for members who are in the 3rd grade or higher, or whose entrance exam score is not below 80:

SELECT * FROM students
WHERE grade>=3
OR NOT entry_exam<=80;

Summary: Essentials of Data Search in MySQL Using SELECT and WHERE Operators

In this article, we have thoroughly explained the basics of data search in MySQL using the SELECT statement and the operators (AND, OR, NOT) used in the WHERE clause. The SELECT statement is a fundamental tool for selecting necessary information from a database. On the other hand, the WHERE clause is used to filter data based on specific conditions.

The operators AND, OR, and NOT are essential for making these queries more flexible and for meeting complex data requirements. The AND operator is used to select data when all conditions are true. The OR operator is used when any of the conditions is true. And the NOT operator is used to negate a specific condition.

Such combinations of operators enable more precise data searches.