MySQL Basics

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 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 poses significant issues; currently, there are only three records, but if the file contains 1TB of data, we will find that memory cannot accommodate 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 organized for storage.==
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

Once a database management system is installed on a computer, you can create databases to store data and perform CRUD operations on the data in the database through the system. The MySQL database we commonly refer to is actually the MySQL database management system.

From the above description, you should now understand the relationship between database management systems and databases. So what are some common database management systems?

1.3 Common Database Management Systems#

Next, we will briefly introduce the database management systems listed above:

Oracle: A large paid database, a product of Oracle Corporation
==MySQL==: An open-source, free medium-sized database. Later, Sun Corporation acquired MySQL, and Sun Corporation was acquired by Oracle.
SQL Server: A medium-sized paid database from Microsoft. Commonly used with C#, .NET, and other languages.
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.

In our course, we are learning the MySQL database management system. PostgreSQL is also used in some companies, and you might 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 following image:

We can operate the database through the database management system to perform CRUD operations on the data in the database. But 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 structured query language
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. In future work, if you use other database management systems, you will similarly use SQL to operate.

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 figure below, both the Order Information Table and the Customer Information Table are two-dimensional tables with rows and columns, which we refer to as a relational database.

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

They all use a table structure, which is consistent and easy to maintain.
They use a universal SQL language for operations, which is convenient and can be used for complex queries.
Relational databases can all be operated through 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 belongs to customer Li Cong. In the future, we can also perform statistical analysis and other operations in a single table.
Data is stored on disk, ensuring safety.

Data Model:

As shown in the figure above, we can create a database through the database management system via the client, create tables in 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 contain multiple tables. Let's look at the folder directory of the built-in MySQL database:

In the figure above, the right db.frm is the table file, and db.MYD is the data file. Through these two files, we can query the data and display it as a two-dimensional table.

Summary:

In MySQL, multiple databases can be created, each corresponding to a folder on the disk.
In each database, multiple tables can be created, 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 refer to as "dialects."

3.2 General Syntax#

SQL statements can be written in single or multiple lines, ending with a semicolon.

show databases;

As shown above, a complete SQL statement ends 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 figure below and will yield 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) Data Definition Language, used to define database objects: databases, tables, columns, etc.

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

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

·DCL(Data Control Language) Data Control Language, used to define database access permissions and security levels, as well as to create users.

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

In simple terms, DDL is used to operate on 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 database access permissions and security levels, as well as to create users.

In simple terms, DCL is used for permission control in databases. For example, I may restrict a certain database table to be operated on by only a specific user.

Note: In the future, we will most commonly operate on DML and DQL, as the most frequent 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 yields the following result:

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

4.2 Creating a Database#

Create Database:

CREATE DATABASE database_name;

Running the statement yields the following result:

When creating a database, I do not know whether the db1 database has already been created; directly trying to create a database named db1 again will result in an error.

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

Create Database (Check if it does not exist before creating)

CREATE DATABASE IF NOT EXISTS database_name;

Running the statement yields the following result:

From the above result, we can see that although the db1 database already exists, creating db1 again does not produce an error, while creating the db2 database is successful.

4.3 Deleting a Database#

Delete Database

DROP DATABASE database_name;

Delete Database (Check if it exists before deleting)

DROP DATABASE IF EXISTS database_name;

Running the statement yields the following result:

4.4 Using a Database#

Once the database is created, to create tables within the database, we need to specify which database we are operating in, so we need to use the database.

Use Database

USE database_name;

View the Current Database in Use

SELECT DATABASE();

Running the statement yields the following result:

5. DDL: Operating Tables#

Operating tables involves performing CRUD operations (Create, Retrieve, Update, Delete) on tables.

5.1 Querying Tables#

Query All Table Names in the Current Database

SHOW TABLES;

Since we have not created any tables in our database, 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: The last line must not end with a comma.

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 yields the following result:

5.3 Data Types#

MySQL supports various 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, only contains 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, maximum of 10 Chinese characters
3. Gender, only two possible values, so at most one character
4. Birthday, value is year, month, day
5. Admission score, two decimal places
6. Email address, 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, normal, on leave, graduated...)

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 yields the following result:

5.5 Modifying a Table#

Rename Table

ALTER TABLE table_name RENAME TO new_table_name;

-- Rename 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 as 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 address field name 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#

Through the above learning, we find that writing SQL statements in the command line is particularly inconvenient, especially when writing statements to create tables. We can only write them in Notepad and then copy them directly to the command line for execution. So is there a tool that can provide us with a better experience? 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 Installing Navicat#

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

You can modify the table structure through the following operations:

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 following image.

7. DML#

DML mainly involves performing insert (add), delete, and update 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 Adding 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,…);

Practice

To demonstrate whether the add, delete, and update 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 list of column names 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 adding 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.

Practice
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, all data in the table will be modified!

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];

Practice

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

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

8. DQL#

Below is the page displaying the question bank data from the Black Horse 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 above image shows the most basic query effect. However, since there are many databases, it is impossible to display all data on one page; the page will have a pagination effect, as shown below:

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

Next, we will introduce the complete syntax for querying:

SELECT
    field_list
FROM
    table_list
WHERE
    condition_list
GROUP BY
    grouping_field
HAVING
    post-grouping condition
ORDER BY
    sorting_field
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 -- Admission 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 Fields

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

Remove Duplicate Records

SELECT DISTINCT field_list FROM table_name;

Alias

AS: AS can also be omitted

8.1.2 Practice#

Query the name and age columns.

SELECT name, age FROM stu;

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

SELECT * FROM stu;

The * in the above statement is not recommended for use, as it makes the SQL statement hard to read. When we write the field list, we can add comments to explain each field.

During the class, to save time, the teacher often writes * 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. 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, and give aliases to math and English using AS (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 field_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, for equality checks, 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 records where English is NULL.
SELECT * FROM stu WHERE english IS NOT NULL; # Query all records where English is not NULL.

-- 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, any number.
# 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 Practice#

Query students older than 20.

SELECT * FROM stu WHERE age > 20;

Query students older than or equal to 20.

SELECT * FROM stu WHERE age >= 20;

Query students 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 statement, 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 students whose admission 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 students whose age is equal to 18.

SELECT * FROM stu WHERE age = 18;

Query students whose age is not equal to 18.

SELECT * FROM stu WHERE age != 18;
SELECT * FROM stu WHERE age <> 18;

Query students 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 students whose English score is NULL.

NULL value comparisons cannot use = 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 Practice#

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

(1) _ : Represents a single arbitrary character.

(2) % : Represents any number of arbitrary characters.

Query students whose name starts with 'Ma'.

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

Query students whose second character is 'Hua'.

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

Query students whose name contains 'De'.

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

8.3 Sorting Queries#

8.3.1 Syntax#

SELECT field_list FROM table_name ORDER BY sorting_field1 [sorting_method1], sorting_field2 [sorting_method2] …;

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 Practice#

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 if the math scores are the same, then sorted by English score in ascending order.

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 math score of all data in the table. This is to perform a vertical sum on the math field.

8.4.2 Categories of Aggregate Functions#

Function Name	Functionality
COUNT(column_name)	Count the number (generally choose a column that is not NULL)
MAX(column_name)	Maximum value
MIN(column_name)	Minimum value
SUM(column_name)	Sum
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 Practice#

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 a certain row's value for that field is NULL, it will not be counted. Therefore, you can use COUNT() to achieve this. * represents 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; # Only retrieves the highest score, one data point.

Query the lowest math score.

SELECT MIN(math) FROM stu; # Only retrieves the lowest score, one data point.

Query the total math score.

SELECT SUM(math) FROM stu; # Retrieves the total score, one data point.

Query the average math score.

SELECT AVG(math) FROM stu; # Retrieves the average score, one data point.

Query the lowest English score.

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

8.5 Grouping Queries#

8.5.1 Syntax#

SELECT field_list FROM table_name [WHERE pre-grouping condition limit] GROUP BY grouping_field [HAVING post-grouping condition filter];

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

8.5.2 Practice#

Query the average math score of male and female students.

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

Note: After grouping, the queried fields 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 count 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 count of male and female students, with the condition that scores below 70 are not included in the grouping.

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

Query the average math score and count of male and female students, with the condition that scores below 70 are not included in the grouping, and the count after grouping is greater than 2.

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

Difference between WHERE and HAVING:

Different execution timing: WHERE is limited 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 checked: WHERE cannot check aggregate functions, while HAVING can.
Execution order: WHERE > Aggregate Function > 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 page by page, and users can also click to view the next page of data.

Next, we will discuss the syntax for pagination queries.

8.6.1 Syntax#

SELECT field_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:

Pagination queries using LIMIT are a MySQL dialect.
Oracle uses ROWNUM for pagination queries.
SQL Server uses TOP for pagination queries.

8.6.2 Practice#

Query 3 records starting from index 0.

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 practice, we can derive the calculation formula for the starting index:

starting_index = (current_page_number - 1) * number_of_records_per_page

MySQL Basics#

Logging into MySQL#

1. Database Related Concepts#

1.1 Database#

1.2 Database Management System#

1.3 Common Database Management Systems#

1.4 SQL#

2.1-2.4 MySQL Installation#

2.5 MySQL Data Model#

3. Overview of SQL#

3.1 Introduction to SQL#

3.2 General Syntax#

3.3 SQL Classification#

4. DDL: Operating Databases#

4.1 Querying#

4.2 Creating a Database#

4.3 Deleting a Database#

4.4 Using a Database#

5. DDL: Operating Tables#

5.1 Querying Tables#

5.2 Creating a Table#

5.3 Data Types#

5.4 Deleting a Table#

5.5 Modifying a Table#

6. Using Navicat#

6.1 Overview of Navicat#

6.2 Installing Navicat#

6.3 Using Navicat#

6.3.1 Establishing a Connection to MySQL Service#

6.3.2 Operations#

7. DML#

7.1 Adding Data#

7.2 Modifying Data#

7.3 Deleting Data#

8. DQL#

8.1 Basic Queries#

8.1.1 Syntax#

8.1.2 Practice#

8.2 Conditional Queries#

8.2.1 Syntax#

8.2.2 Conditional Query Practice#

8.2.3 Fuzzy Query Practice#

8.3 Sorting Queries#

8.3.1 Syntax#

8.3.2 Practice#

8.4 Aggregate Functions#

8.4.1 Concept#

8.4.2 Categories of Aggregate Functions#

8.4.3 Syntax of Aggregate Functions#

8.4.4 Practice#

8.5 Grouping Queries#

8.5.1 Syntax#

8.5.2 Practice#

8.6 Pagination Queries#

8.6.1 Syntax#

8.6.2 Practice#