MySQL Basic Tutorial

MySQL Basics#

Today's Goals:

Complete the installation of MySQL and basic login operations

Be able to perform CRUD operations on the database using SQL

Be able to perform CRUD operations on tables using SQL

Be able to perform CRUD operations on data using SQL

Logging into MySQL#

mysql -uroot -p 123456

In the past, when we built systems, data persistence was achieved through file storage. Storing data in files ensures that data is not lost when the system shuts down, but file storage also has its drawbacks.

Suppose we store the following data in a file:

Name Age Gender Address
Zhang San 23 Male Beijing Xisanqi
Li Si 24 Female Beijing Xierqi
Wang Wu 25 Male Xi'an Software New City

Now, if we want to change Li Si's gender to male, the I/O technology we are learning can read all the data into memory, modify it, and then write it back to the file. This method has significant issues; currently, there are only three records, but if the file stores 1TB of data, we would find that memory cannot hold it.

We need a technology that can both persistently store data and avoid the aforementioned issues. A database is such a technology.

1.1 Database#

==A warehouse for storing and managing data, where data is stored in an organized manner.==
The English name for a database is DataBase, abbreviated as DB.

A database stores data on a hard disk, achieving persistent storage. So how does it solve the above problems? By using a database management system.

1.2 Database Management System#

==A large software that manages databases==
English: DataBase Management System, abbreviated as DBMS

After installing a database management system on a computer, you can create databases to store data and perform CRUD operations on the data within the database using the system. The MySQL database we commonly refer to is actually the MySQL Database Management System.

From the above description, you should already understand the relationship between Database Management System and Database. So what are some common database management systems?

1.3 Common Database Management Systems#

Next, a brief introduction to the database management systems listed above:

Oracle: A large paid database, a product of Oracle Corporation
==MySQL==: An open-source and free medium-sized database. Later, Sun Corporation acquired MySQL, and Sun Corporation was then acquired by Oracle.
SQL Server: A medium-sized paid database from Microsoft. Commonly used with C#, .NET, etc.
PostgreSQL: An open-source free medium-sized database
DB2: A large paid database product from IBM
SQLite: An embedded micro-database. For example, as the built-in database for Android
MariaDB: An open-source free medium-sized database

We are learning the MySQL database management system in our course. PostgreSQL is also used in some companies, and you may wonder what to do if you encounter a PostgreSQL database management system that we haven't studied in the future. You need not worry, as shown in the image below:

We can operate the database through the database management system to perform CRUD operations on the data within the database. How do we let users interact with the database management system? This can be achieved through a programming language (SQL).

1.4 SQL#

English: Structured Query Language, abbreviated as SQL
A programming language for operating relational databases
Defines a unified standard for operating all relational databases, allowing SQL to be used to operate all relational database management systems. If you use other database management systems in the future, you will also use SQL to operate them.

MySQL

2.1-2.4 MySQL Installation#

MySQL Installation Documentation

2.5 MySQL Data Model#

Relational Database:

A relational database is a database built on the relational model. In simple terms, a relational database consists of multiple two-dimensional tables that can connect with each other.

As shown in the image below, both the Order Information Table and the Customer Information Table are two-dimensional tables with rows and columns, which we call a relational database.

Next, let's look at the advantages of relational databases:

They all use a table structure, have a consistent format, and are easy to maintain.
They use a common SQL language for operations, which is convenient and can be used for complex queries.
Relational databases can all be operated using SQL, making them user-friendly.
Complex queries. Now we need to query the data for order number 001. We can see that this order belongs to customer number 1, and order number 1 is for customer Li Cong. We can also perform statistical analysis and other operations on a single table in the future.
Data is stored on disk, ensuring safety.

Data Model:

As shown in the image above, we can create databases through the database management system using a client, create tables within the database, and add data to the tables. Each created database corresponds to a folder on the disk. For example, we can create a database using SQL statements (the database name is db1), as shown in the statement below. We will learn this statement later.

We can see that a new folder named db1 has been added in the data directory under the database installation directory. Therefore, in MySQL, a database corresponds to a folder on the disk.

A database can have multiple tables. Let's check the folder directory of the built-in MySQL database:

In the image above, the right side shows db.frm as the table file, and db.MYD as the data file. These two files can be used to query data and display it in a two-dimensional table format.

Summary:

Multiple databases can be created in MySQL, each corresponding to a folder on the disk.
Each database can have multiple tables, each corresponding to a frm file on the disk.
Each table can store multiple records, and the data will be stored in the MYD file on the disk.

3. Overview of SQL#

After understanding the data model, we will learn SQL statements to perform CRUD operations on databases, tables, and data.

3.1 Introduction to SQL#

English: Structured Query Language, abbreviated as SQL
A structured query language, a programming language for operating relational databases
Defines a unified standard for operating all relational databases
For the same requirement, there may be some differences in the way each database operates, which we call "dialects."

3.2 General Syntax#

SQL statements can be written in single or multiple lines and must end with a semicolon.

show databases;

As shown above, a complete SQL statement must end with a semicolon.

SQL statements in MySQL are case-insensitive, but it is recommended to use uppercase for keywords.

The same SQL statement can also be written as shown in the image below and will produce the same result.

Show DataBases;

Comments
Single-line comments: -- comment content or #comment content (specific to MySQL)

Note: When using -- to add a single-line comment, make sure to add a space after --, while # has no such requirement.

Multi-line comments: /* comment */

-- comment content
#comment content (specific to MySQL)
/* comment */

3.3 SQL Classification#

·DDL (Data Definition Language) is used to define database objects: databases, tables, columns, etc.

·DML (Data Manipulation Language) is used to perform CRUD operations on data in tables within the database.

·DQL (Data Query Language) is used to query records (data) in tables within the database.

·DCL (Data Control Language) is used to define access permissions and security levels for the database and to create users.

DDL (Data Definition Language): Used to define database objects: databases, tables, columns, etc.

In simple terms, DDL is used to operate databases, tables, etc.

DML (Data Manipulation Language): Used to perform CRUD operations on data in tables within the database.

In simple terms, DML is used to add, delete, and modify data in tables.

DQL (Data Query Language): Used to query records (data) in tables within the database.

In simple terms, DQL is used to perform query operations to retrieve the data we want from database tables.

DCL (Data Control Language): Used to define access permissions and security levels for the database and to create users.

In simple terms, DCL is used for permission control in the database. For example, I may restrict certain database tables to only allow specific users to operate on them.

Note: In the future, we will most often operate with DML and DQL, as the most common operations in development involve data.

4. DDL: Operating Databases#

We will first learn DDL to operate databases. The main operations on databases are adding, deleting, and querying.

4.1 Querying#

Query all databases

SHOW DATABASES;

Running the above statement will yield the following result:

The databases queried above are the built-in databases that come with MySQL installation, and we should not operate on these databases in the future.

4.2 Creating a Database#

Create Database:

CREATE DATABASE database_name;

Running the statement will yield the following result:

When creating a database, I may not know whether the db1 database has already been created. If I directly attempt to create a database named db1 again, an error will occur.

To avoid the above error, we should first check if the database exists before creating it.

Create Database (Check if it exists, if not, create it)

CREATE DATABASE IF NOT EXISTS database_name;

Running the statement will yield the following result:

From the above result, we can see that although the db1 database already exists, attempting to create db1 again does not throw an error, while creating the db2 database succeeds.

4.3 Deleting a Database#

Delete Database

DROP DATABASE database_name;

Delete Database (Check if it exists, if so, delete it)

DROP DATABASE IF EXISTS database_name;

Running the statement will yield the following result:

4.4 Using a Database#

Once the database is created, we need to specify which database to operate in before creating tables, so we need to use the database.

Use Database

USE database_name;

View the Current Database in Use

SELECT DATABASE();

Running the statement will yield the following result:

5. DDL: Operating Tables#

Operating tables involves performing Create (C), Read (R), Update (U), and Delete (D) operations.

5.1 Querying Tables#

Query All Table Names in the Current Database

SHOW TABLES;

Since there are no tables in the database we created, we will enter the built-in MySQL database and execute the above statement to check.

Query Table Structure

DESC table_name;

To view the structure of the func table in the MySQL database, run the statement as follows:

5.2 Creating a Table#

Create Table

CREATE TABLE table_name (
    column_name1 data_type1,
    column_name2 data_type2,
    ...
    column_nameN data_typeN
);

Note: Do not add a comma at the end of the last line.

Knowing the statement to create a table, we will create a table with the following structure:

CREATE TABLE tb_user (
    id INT,
    username VARCHAR(20),  -- VARCHAR represents string type, with length limit in parentheses
    password VARCHAR(32)
);

Running the statement will yield the following result:

5.3 Data Types#

MySQL supports various data types, which can be divided into three categories:

Numeric

TINYINT: Small integer type, occupies one byte
INT: Large integer type, occupies four bytes
eg: age INT
DOUBLE: Floating-point type
Usage format: column_name DOUBLE(total_length, decimal_places)
eg: score DOUBLE(5,2)

Date

DATE: Date value. Contains only year, month, and day
eg: birthday DATE
DATETIME: Mixed date and time value. Contains year, month, day, hour, minute, and second

String

CHAR: Fixed-length string.
Advantages: High storage performance
Disadvantages: Wastes space
eg: name CHAR(10) If the stored data has fewer than 10 characters, it will still occupy 10 spaces
VARCHAR: Variable-length string.
Advantages: Saves space
Disadvantages: Lower storage performance
eg: name VARCHAR(10) If the stored data has fewer than 10 characters, it will occupy only the number of characters stored

Note: For other types, refer to the document "MySQL Data Types.xlsx."

Case Study:

Requirement: Design a student table, paying attention to the rationality of data types and lengths.
1. ID
2. Name, with a maximum length of 10 Chinese characters
3. Gender, since there are only two possible values, at most one character
4. Birthday, with values for year, month, and day
5. Entrance score, with two decimal places
6. Email address, with a maximum length of 64
7. Family contact number, which may not necessarily be a mobile number and may contain characters like -
8. Student status (represented by numbers, such as normal, on leave, graduated, etc.)

The statement design is as follows:

CREATE TABLE student (
    id INT,
    name VARCHAR(10),
    gender CHAR(1),
    birthday DATE,
    score DOUBLE(5,2),
    email VARCHAR(15),
    tel VARCHAR(15),
    status TINYINT
);

5.4 Deleting a Table#

Delete Table

DROP TABLE table_name;

Clear Data in the Table: TRUNCATE

Format: TRUNCATE TABLE table_name

SELECT * FROM ADDRESS;

Clear the address table: TRUNCATE TABLE ADDRESS;

Query all records in the address table: SELECT * FROM ADDRESS;

Delete Table with Existence Check

DROP TABLE IF EXISTS table_name;

Running the statement will yield the following result:

5.5 Modifying a Table#

Rename Table

ALTER TABLE table_name RENAME TO new_table_name;

-- Rename the table student to stu
ALTER TABLE student RENAME TO stu;

Add a Column

ALTER TABLE table_name ADD column_name data_type;

-- Add a column address to the stu table, with the field type being VARCHAR(50)
ALTER TABLE stu ADD address VARCHAR(50);

Modify Data Type

ALTER TABLE table_name MODIFY column_name new_data_type;

-- Change the type of the address field in the stu table to CHAR(50)
ALTER TABLE stu MODIFY address CHAR(50);

Modify Column Name and Data Type

ALTER TABLE table_name CHANGE column_name new_column_name new_data_type;

-- Change the field name address in the stu table to addr, and change the type to VARCHAR(50)
ALTER TABLE stu CHANGE address addr VARCHAR(50);

Delete Column

ALTER TABLE table_name DROP column_name;

-- Delete the addr field from the stu table
ALTER TABLE stu DROP addr;

6. Using Navicat#

From the above learning, we find that writing SQL statements in the command line is particularly inconvenient, especially when writing create table statements. We can only write them in Notepad and then copy them directly to the command line for execution. So, is there a suitable tool available for us to use? Yes.

6.1 Overview of Navicat#

Navicat for MySQL is an ideal solution for managing and developing MySQL or MariaDB.
This comprehensive front-end tool provides an intuitive and powerful graphical interface for database management, development, and maintenance.
Official website: http://www.navicat.com.cn

6.2 Navicat Installation#

Reference: Documentation\navicat installation package\navicat_mysql_x86\navicat installation steps.md

6.3 Using Navicat#

6.3.1 Establishing a Connection to MySQL Service#

Step 1: Click on Connect and select MySQL.

Step 2: Fill in the necessary information to connect to the database.

If the above operations are correct, the following interface will appear:

6.3.2 Operations#

Once connected successfully, you will see the following interface:

Modify Table Structure

Modify the table structure through the operations shown in the image below:

After clicking on Design Table, the following interface will appear, where you can directly modify field names, types, and other information in the red box:

Write and Execute SQL Statements

You can write SQL statements and execute them as shown in the image below.

7. DML#

DML mainly involves performing Insert (add), Delete (remove), and Update (modify) operations on data.

7.1 Adding Data#

Add Data to Specified Columns

INSERT INTO table_name(column_name1, column_name2,…) VALUES(value1, value2,…);

Add Data to All Columns

INSERT INTO table_name VALUES(value1, value2,…);

Batch Add Data

INSERT INTO table_name(column_name1, column_name2,…) VALUES(value1, value2,…),(value1, value2,…),(value1, value2,…);
INSERT INTO table_name VALUES(value1, value2,…),(value1, value2,…),(value1, value2,…);

Exercise

To demonstrate whether the add, delete, and modify operations are successful, we will first introduce the statement to query all data:

SELECT * FROM stu;

-- Add data to specified columns
INSERT INTO stu (id, NAME) VALUES (1, 'Zhang San');

-- Add data to all columns; the column name list can be omitted
INSERT INTO stu (id, NAME, sex, birthday, score, email, tel, STATUS) VALUES (2, 'Li Si', 'Male', '1999-11-11', 88.88, '[email protected]', '13888888888', 1);

INSERT INTO stu VALUES (2, 'Li Si', 'Male', '1999-11-11', 88.88, '[email protected]', '13888888888', 1);

-- Batch add data
INSERT INTO stu VALUES
(2, 'Li Si', 'Male', '1999-11-11', 88.88, '[email protected]', '13888888888', 1),
(2, 'Li Si', 'Male', '1999-11-11', 88.88, '[email protected]', '13888888888', 1),
(2, 'Li Si', 'Male', '1999-11-11', 88.88, '[email protected]', '13888888888', 1);

7.2 Modifying Data#

Modify Table Data

UPDATE table_name SET column_name1=value1, column_name2=value2,… [WHERE condition];

Note:

If no condition is added to the update statement, all data will be modified!

The square brackets in the above statement indicate that this part can be omitted when writing SQL statements.

Exercise
Change Zhang San's gender to female.

UPDATE stu SET sex = 'Female' WHERE name = 'Zhang San';

Change Zhang San's birthday to 1999-12-12 and score to 99.99.

UPDATE stu SET birthday = '1999-12-12', score = 99.99 WHERE name = 'Zhang San';

Note: If the update statement does not include a where condition, it will modify all data in the table!

UPDATE stu SET sex = 'Female';

After executing the above statement, the result queried is:

7.3 Deleting Data#

Delete Data

DELETE FROM table_name [WHERE condition];

Exercise

-- Delete Zhang San's record
DELETE FROM stu WHERE name = 'Zhang San';

-- Delete all data from the stu table
DELETE FROM stu;

8. DQL#

Below is the page displaying the question bank data from the Heima Programmer.

The data displayed on the page is certainly stored in the question bank table in the database, and we need to query the data from the database and display it on the page for users to see. The image above shows the most basic query effect, but since there are many databases, it is not possible to display all data on one page; instead, there will be pagination effects, as shown below:

Of course, the difficulty field in the image above can also be sorted and queried when clicked. From this example, we can see that querying a database is flexible and variable, depending on specific requirements, and database query operations are also the most important operations, so this part needs to be mastered.

Next, we will introduce the complete syntax for querying:

SELECT
    column_list
FROM
    table_list
WHERE
    condition_list
GROUP BY
    group_column
HAVING
    group_condition
ORDER BY
    order_column
LIMIT
    pagination_limit

To demonstrate the query statements, we need to prepare tables and some data first:

-- Delete the stu table
DROP TABLE IF EXISTS stu;

-- Create the stu table
CREATE TABLE stu (
    id INT, -- ID
    name VARCHAR(20), -- Name
    age INT, -- Age
    sex VARCHAR(5), -- Gender
    address VARCHAR(100), -- Address
    math DOUBLE(5,2), -- Math score
    english DOUBLE(5,2), -- English score
    hire_date DATE -- Enrollment date
);

-- Add data
INSERT INTO stu(id, NAME, age, sex, address, math, english, hire_date)
VALUES
(1, 'Ma Yun', 55, 'Male', 'Hangzhou', 66, 78, '1995-09-01'),
(2, 'Ma Hua Teng', 45, 'Female', 'Shenzhen', 98, 87, '1998-09-01'),
(3, 'Ma Si Ke', 55, 'Male', 'Hong Kong', 56, 77, '1999-09-02'),
(4, 'Liu Bai', 20, 'Female', 'Hunan', 76, 65, '1997-09-05'),
(5, 'Liu Qing', 20, 'Male', 'Hunan', 86, NULL, '1998-09-01'),
(6, 'Liu De Hua', 57, 'Male', 'Hong Kong', 99, 99, '1998-09-01'),
(7, 'Zhang Xue You', 22, 'Female', 'Hong Kong', 99, 99, '1998-09-01'),
(8, 'De Ma Xiya', 18, 'Male', 'Nanjing', 56, 65, '1994-09-02');

Next, we will start learning from the most basic query statements.

8.1 Basic Queries#

8.1.1 Syntax#

Query Multiple Columns

SELECT column_list FROM table_name;
SELECT * FROM table_name; -- Query all data

Remove Duplicate Records

SELECT DISTINCT column_list FROM table_name;

Alias

AS: AS can also be omitted

8.1.2 Exercise#

Query the name and age columns.

SELECT name, age FROM stu;

Query all columns of data; the column name list can be replaced with *.

SELECT * FROM stu;

In the above statement, it is not recommended to use *, as it makes the SQL statement less readable. When writing the column list, you can add comments to explain each field.

During class, to save time, the teacher often uses * in many places.

Query address information.

SELECT address FROM stu;

The result of executing the above statement is as follows:

From the above result, we can see that there are duplicate data entries, and we can also use the DISTINCT keyword to remove duplicate data.

Remove Duplicate Records.

SELECT DISTINCT address FROM stu; -- This will remove duplicates from Hunan and Hong Kong

Query name, math score, and English score. Use AS to give math and English aliases (AS can be omitted).

SELECT name, math AS MathScore, english AS EnglishScore FROM stu;

-- AS can be omitted, but at least one space must be left between the original name and the alias
SELECT name, math MathScore, english EnglishScore FROM stu;

8.2 Conditional Queries#

8.2.1 Syntax#

SELECT column_list FROM table_name WHERE condition_list; -- WHERE is similar to IF

WHERE >= xx && < bb; -- AND

WHERE >= xx AND < bb; -- AND

WHERE BETWEEN xx AND bb; -- Between xx and bb, equivalent to >= xx <= bb

# In MySQL, date data can be filtered directly using the above methods

# In MySQL, to check for equality, use = instead of ==

<> !=; # Not equal

|| OR; # OR

-- Example of OR
SELECT * FROM stu WHERE age = 18 OR age = 20 OR age = 22; -- Verbose

SELECT * FROM stu WHERE age IN (18, 20, 22); -- Concise

# Query NULL data

-- To query NULL, use IS or IS NOT instead of = or !=

-- Example
SELECT * FROM stu WHERE english IS NULL; # Query data with empty English scores

SELECT * FROM stu WHERE english IS NOT NULL; # Query all data with non-empty English scores

-- LIKE for fuzzy queries: _ for a single character, % for multiple arbitrary characters

# Query students whose name starts with 'Ma'
SELECT * FROM stu WHERE name LIKE 'Ma%'; # % represents any character, unlimited quantity

# Query students whose second character is 'Hua'
SELECT * FROM stu WHERE name LIKE '_Hua%'; # _ represents any single character

# Query students whose name contains 'De'
SELECT * FROM stu WHERE name LIKE '%De%'; # Any characters before and after, containing 'De'

Conditions

The condition list can use the following operators:

8.2.2 Conditional Query Exercises#

Query student information for those older than 20.

SELECT * FROM stu WHERE age > 20;

Query student information for those older than or equal to 20.

SELECT * FROM stu WHERE age >= 20;

Query student information for those older than or equal to 20 and younger than or equal to 30.

SELECT * FROM stu WHERE age >= 20 && age <= 30;

SELECT * FROM stu WHERE age >= 20 AND age <= 30;

In the above statements, both && and AND mean "and." It is recommended to use AND.

You can also use BETWEEN ... AND to achieve the above requirement.

SELECT * FROM stu WHERE age BETWEEN 20 AND 30;

Query student information for those whose enrollment date is between '1998-09-01' and '1999-09-01'.

SELECT * FROM stu WHERE hire_date BETWEEN '1998-09-01' AND '1999-09-01';

Query student information for those whose age is equal to 18.

SELECT * FROM stu WHERE age = 18;

Query student information for those whose age is not equal to 18.

SELECT * FROM stu WHERE age != 18;

SELECT * FROM stu WHERE age <> 18;

Query student information for those whose age is equal to 18, 20, or 22.

SELECT * FROM stu WHERE age = 18 OR age = 20 OR age = 22;

SELECT * FROM stu WHERE age IN (18, 20, 22);

Query student information for those whose English score is NULL.

NULL values cannot be compared using = or !=. Use IS or IS NOT instead.

SELECT * FROM stu WHERE english = NULL; -- This statement will not work

SELECT * FROM stu WHERE english IS NULL;

SELECT * FROM stu WHERE english IS NOT NULL;

8.2.3 Fuzzy Query Exercises#

Fuzzy queries use the LIKE keyword and can use wildcards for placeholders:

(1) _ : Represents a single arbitrary character

(2) % : Represents any number of characters

Query student information for those whose name starts with 'Ma'.

SELECT * FROM stu WHERE name LIKE 'Ma%';

Query student information for those whose second character is 'Hua'.

SELECT * FROM stu WHERE name LIKE '_Hua%';

Query student information for those whose name contains 'De'.

SELECT * FROM stu WHERE name LIKE '%De%';

8.3 Sorting Queries#

8.3.1 Syntax#

SELECT column_list FROM table_name ORDER BY sort_column1 [sort_order1], sort_column2 [sort_order2] …;

The sorting methods in the above statement are:

ASC: Ascending order (default value)
DESC: Descending order

Note: If there are multiple sorting conditions, the second condition will only be used to sort when the values of the first condition are the same.

8.3.2 Exercises#

Query student information, sorted by age in ascending order.

SELECT * FROM stu ORDER BY age;

Query student information, sorted by math score in descending order.

SELECT * FROM stu ORDER BY math DESC;

Query student information, sorted by math score in descending order, and by English score in ascending order if the math scores are the same.

SELECT * FROM stu ORDER BY math DESC, english ASC;

8.4 Aggregate Functions#

8.4.1 Concept#

==To treat a column of data as a whole and perform vertical calculations.==

How to understand this? Suppose we have the following table:

Now we have a requirement to calculate the total of all math scores in the table. This is a vertical summation of the math field.

8.4.2 Categories of Aggregate Functions#

Function Name	Functionality
COUNT(column_name)	Count quantity (generally choose a column that is not NULL)
MAX(column_name)	Maximum value
MIN(column_name)	Minimum value
SUM(column_name)	Summation
AVG(column_name)	Average value

8.4.3 Syntax of Aggregate Functions#

SELECT aggregate_function_name(column_name) FROM table;

Note: NULL values do not participate in any aggregate function calculations.

8.4.4 Exercises#

Count how many students are in the class.

SELECT COUNT(id) FROM stu;

SELECT COUNT(english) FROM stu;

The above statements count based on a specific field. If the value of that field in a certain row is NULL, it will not be counted. Therefore, you can use COUNT() to achieve this. * indicates all field data, and it is impossible for all data in a row to be NULL, so it is recommended to use COUNT().

SELECT COUNT(*) FROM stu; # Count all data

Query the highest math score.

SELECT MAX(math) FROM stu; # This will only return the highest score, one data point

Query the lowest math score.

SELECT MIN(math) FROM stu; # This will only return the lowest score, one data point

Query the total math score.

SELECT SUM(math) FROM stu; # This will return the total score, one data point

Query the average math score.

SELECT AVG(math) FROM stu; # This will return the average score, one data point

Query the lowest English score.

SELECT MIN(english) FROM stu; # Cannot query NULL values

8.5 Group Queries#

8.5.1 Syntax#

SELECT column_list FROM table_name [WHERE pre-grouping condition] GROUP BY group_column [HAVING post-grouping condition];

Note: After grouping, the fields queried must be aggregate functions and grouping fields; querying other fields is meaningless.

8.5.2 Exercises#

Query the average math score for male and female students.

SELECT sex, AVG(math) FROM stu GROUP BY sex; # Group by gender

# Gender will be displayed vertically, and field data will be displayed horizontally

Note: After grouping, the fields queried must be aggregate functions and grouping fields; querying other fields is meaningless.

SELECT name, sex, AVG(math) FROM stu GROUP BY sex; -- Querying the name field here is meaningless

name	sex	avg(math)
Ma Huateng	Female	91
Ma Yun	Male	72.6

Query the average math score and the number of male and female students.

SELECT sex, AVG(math), COUNT(*) FROM stu GROUP BY sex;

sex	avg(math)	count(*)
Female	91	3
Male	72.6	5

Query the average math score and the number of male and female students, requiring that scores below 70 do not participate in the grouping.

SELECT sex, AVG(math), COUNT(*) FROM stu WHERE math > 70 GROUP BY sex;

Query the average math score and the number of male and female students, requiring that scores below 70 do not participate in the grouping, and that the number of students in each group is greater than 2.

SELECT sex, AVG(math), COUNT(*) FROM stu WHERE math > 70 GROUP BY sex HAVING COUNT(*) > 2; # HAVING is used in grouping queries, similar to WHERE

Difference between WHERE and HAVING:

Different execution timing: WHERE filters before grouping; if it does not meet the WHERE condition, it will not participate in grouping, while HAVING filters the results after grouping.
Different conditions that can be judged: WHERE cannot judge aggregate functions, while HAVING can.
Execution order: WHERE > Aggregate Functions > HAVING

8.6 Pagination Queries#

As shown in the image below, you have seen similar effects on many websites, such as JD, Baidu, Taobao, etc. Pagination queries display data to users one page at a time, and users can also click to view data on the next page.

Next, we will discuss the syntax for pagination queries.

8.6.1 Syntax#

SELECT column_list FROM table_name LIMIT starting_index, number_of_records_to_query;

Note: The starting index in the above statement starts from 0.

Calculation formula: starting_index = (current_page_number - 1) * number_of_records_per_page

Tips:

The LIMIT pagination query is a MySQL database dialect.
Oracle uses ROWNUM for pagination queries.
SQL Server uses TOP for pagination queries.

8.6.2 Exercises#

Query from the beginning, retrieving 3 records.

SELECT * FROM stu LIMIT 0, 3; # Index counting rules are similar to arrays

Query the first page of data, displaying 3 records per page.

SELECT * FROM stu LIMIT 0, 3; -- 0 1 2

Query the second page of data, displaying 3 records per page.

SELECT * FROM stu LIMIT 3, 3; -- 3 4 5

Query the third page of data, displaying 3 records per page.

SELECT * FROM stu LIMIT 6, 3; -- 6 7 8

From the above exercises, we can derive the calculation formula for the starting index:

starting_index = (current_page_number - 1) * number_of_records_per_page