MySQL single table query example detailed explanation

MySQL single table query example detailed explanation

1. Prepare data

The following operations will be performed on this table

create table student (
 id int unsigned primary key auto_increment,
 name char(12) not null,
 gender enum("male","famale") default "male",
 age tinyint unsigned not null,
 hoc_group char(12) not null,
 html tinyint unsigned not null,
 css tinyint unsigned not null,
 js tinyint unsigned not null,
 sanction enum("major sanction","minor sanction","none")
);

insert into student(name,gender,age,hoc_group,html,css,js,sanction) values
 ("Yunya","male",18,"first",88,93,76,"none"),
 ("Jack","male",17,"second",92,81,88,"none"),
 ("Bella","famale",17,"first",72,68,91,"minor punishment"),
 ("Dairis","famale",18,"third",89,54,43,"Big punishment"),
 ("Kyle","famale",19,"fifth",31,24,60,"big punishment"),
 ("Alice","famale",16,"second",49,23,58,"none"),
 ("Ken","male",16,"third",33,62,17,"big punishment"),
 ("Jason","male",21,"fourth",91,92,90,"none"),
 ("Tom","male",20,"fifth",88,72,91,"none"),
 ("Fiona","famale",19,"fourth",60,71,45,"none");

2. Query syntax

SELECT DISTINCT(field name 1, field name 2...) FROM table name WHERE condition GROUP BY field name HAVING filter ORDER BY field name asc/desc
  LIMIT limits the number of entries;

3. Execution order

Although the query syntax is the same as above, the internal execution order is slightly different.

1. Find the table to be queried through from

2. where specifies the query conditions, queries the table records row by row and filters out the records that meet the rules

3. Group the found records by field. If no grouping is performed, the default is a group.

4. Perform having filter on the grouped results. You can use the aggregate function (aggregate function cannot be used when using where)

5. Execute select to prepare for printing

6. Execute distinct to remove duplicates from the printed results

7. Execute ordery by to sort the results

8. Execute limit to limit the number of printed results

4. select

select is mainly responsible for printing related work

4.1 All Query

Use select * from 表名to get all the data in the table

The following example will show how to use the full query to get all the records in the student table.

 select * from student;
 
+----+--------+--------+-----+-----------+------+-----+-----+----+-----------+
| id | name | gender | age | hoc_group | html | css | js | sanction |
+----+--------+--------+-----+-----------+------+-----+-----+----+-----------+
| 1 | Yunya | male | 18 | first | 88 | 93 | 76 | None |
| 2 | Jack | male | 17 | second | 92 | 81 | 88 | None |
| 3 | Bella | famale | 17 | first | 72 | 68 | 91 | minor punishment |
| 4 | Dairis | famale | 18 | third | 89 | 54 | 43 | major punishment |
| 5 | Kyle | famale | 19 | fifth | 31 | 24 | 60 | major punishment |
| 6 | Alice | famale | 16 | second | 49 | 23 | 58 | None |
| 7 | Ken | male | 16 | third | 33 | 62 | 17 | Major punishment |
| 8 | Jason | male | 21 | fourth | 91 | 92 | 90 | None |
| 9 | Tom | male | 20 | fifth | 88 | 72 | 91 | None |
| 10 | Fiona | famale | 19 | fourth | 60 | 71 | 45 | none |
+----+--------+--------+-----+-----------+------+-----+-----+----+-----------+

4.2 Field Query

Use select field name 1, field name 2 from table name to get the corresponding data under a specific field

The following example will show how to use field query to get the HTML\CSS\JS scores of each student

select name,html,css,js from student;

+--------+------+-----+----+
| name | html | css | js |
+--------+------+-----+----+
| Yunya | 88 | 93 | 76 |
| Jack | 92 | 81 | 88 |
| Bella | 72 | 68 | 91 |
| Dairis | 89 | 54 | 43 |
| Kyle | 31 | 24 | 60 |
| Alice | 49 | 23 | 58 |
| Ken | 33 | 62 | 17 |
| Jason | 91 | 92 | 90 |
| Tom | 88 | 72 | 91 |
| Fiona | 60 | 71 | 45 |
+--------+------+-----+----+

4.3 as alias

Use select field name 1 as alias 1, field name 2 as alias 2 from table name to modify the queried record field to an alias

The following example shows how to modify the name field to name, the gender field to gender, and the age field to age.

select name as "姓名", gender as "性别", age as "年龄" from student;

+--------+--------+--------+
| Name| Gender| Age|
+--------+--------+--------+
| Yunya | male | 18 |
| Jack | male | 17 |
| Bella | famale | 17 |
| Dairis | famale | 18 |
| Kyle | famale | 19 |
| Alice | famale | 16 |
| Ken | male | 16 |
| Jason | male | 21 |
| Tom | male | 20 |
| Fiona | famale | 19 |
+--------+--------+--------+

4.4 distinct

Use select distinct(field name 1, field name 2) from table name to perform a de-duplication operation on the queried records.

The following example will show how to use the deduplication function to see how many groups there are

select distinct(hoc_group) from student;

+-----------+
|hoc_group|
+-----------+
| first |
| second |
| third |
fifth |
| fourth |
+-----------+

4.5 Arithmetic operations

The query results can be used for four arithmetic operations. The following example will show the operation of getting the total score of three subjects for each student.

select name, html+css+js as total score from student;
+--------+-----------+
| name | Total score|
+--------+-----------+
| Yunya | 257 |
| Jack | 261 |
| Bella | 231 |
| Dairis | 186 |
| Kyle | 115 |
| Alice | 130 |
| Ken | 112 |
| Jason | 273 |
| Tom | 251 |
| Fiona | 176 |
+--------+-----------+

4.6 Display Format

Use concat() to concatenate the query result with any string.

Use concat_ws() to specify the connector for splicing. The first parameter is the connector.

select concat("姓名->",name," ","性别->",gender) from student; # Merge into a string, note that they are separated by spaces, otherwise they will stick together. +--------------------------------------------------+
| concat("姓名->",name," ","性别->",gender) |
+--------------------------------------------------+
| Name->Yunya Gender->male |
| Name->Jack Gender->male |
| Name->Bella Gender->famale |
| Name->Dairis Gender->famale |
| Name->Kyle Gender->famale |
| Name->Alice Gender->famale |
| Name->Ken Gender->male |
| Name->Jason Gender->male |
| Name->Tom Gender->male |
| Name->Fiona Gender->famale |
+--------------------------------------------------+

select concat_ws("|||",name,gender,age) from student; # Use ||| to split each field+----------------------------------+
| concat_ws("|||",name,gender,age) |
+----------------------------------+
| Yunya|||male|||18 |
| Jack|||male|||17 |
| Bella|||famale|||17 |
| Dairis|||famale|||18 |
| Kyle|||famale|||19 |
| Alice|||famale|||16 |
| Ken|||male|||16 |
| Jason|||male|||21 |
| Tom|||male|||20 |
| Fiona|||famale|||19 |
+----------------------------------+

5. where

The where condition is the first hurdle of the query, which can effectively filter out any data we want

5.1 Comparison Operations

Use comparison operators > < >= <= != to query

The following example will show how to use where to filter out students whose js scores are greater than 80 points.

select name, js from student where js > 80;

+-------+----+
| name | js |
+-------+----+
| Jack | 88 |
| Bella | 91 |
| Jason | 90 |
| Tom | 91 |
+-------+----+

5.2 Logical Operations

Use and or not to perform logical operations and multi-condition queries

The following example will show how to use the where multi-condition query to filter out students whose scores in all subjects are greater than 80 points.

select name, html, css, js from student where html > 80 and css > 80 and js > 80;

+-------+------+-----+----+
| name | html | css | js |
+-------+------+-----+----+
| Jack | 92 | 81 | 88 |
| Jason | 91 | 92 | 90 |
+-------+------+-----+----+

5.3 Membership Operation

in can be used to obtain specific values, such as in(80,90,100) means only taking the records with values ​​of 80, 90 or 100.

The following example will show the personal information of only the first group and the second group of students.

select name, gender, age, hoc_group from student where hoc_group in ("first","second");

+-------+--------+-----+-----------+
| name | gender | age | hoc_group |
+-------+--------+-----+-----------+
| Yunya | male | 18 | first |
| Jack | male | 17 | second |
| Bella | famale | 17 | first |
| Alice | famale | 16 | second |
+-------+--------+-----+-----------+

5.4, ​​between and

Between and also means to take the interval,

The following example will show how to use between and to filter out students whose Js scores are greater than or equal to 60 and less than 80.

select name, js from student where js between 60 and 80;

+-------+----+
| name | js |
+-------+----+
| Yunya | 76 |
| Kyle | 60 |
+-------+----+

5.5, like

Like is a fuzzy query, where % represents any number of characters (similar to the greedy wildcard .*), and _ represents any character (similar to the non-greedy wildcard .*?).

The following example will show how to use like/% to match all the students whose names begin with k.

select name from student where name like "k%";

+------+
| name |
+------+
| Kyle |
| Ken |
+------+

The following example will show how to use like/_ to match the names of students whose names start with k and whose total length is 3.

select name from student where name like "k__";

+------+
| name |
+------+
| Ken |
+------+

5.6. Regular Expression Matching

RegExp can be used for regular matching. The following example will show how to use regular matching to find all the names of students with k in their names.

select name from student where name REGEXP "k+";

+------+
| name |
+------+
| Jack |
| Kyle |
| Ken |
+------+

6. Group by

The grouping behavior occurs after the where condition. We can classify the queried records according to a certain common field. Generally, grouping is used in conjunction with aggregate functions.

It should be noted that the select statement is placed after the group by condition, so aggregate functions can also be used in the select statement.

6.1 Basic Usage

The following example will demonstrate grouping on the hoc_group field.

We group by the hoc_group field, so the field for the select query can only be the hoc_group field. If you want to get information about other fields in the group, you need to use a function to complete it.

select hoc_group from student group by hoc_group;

+-----------+
|hoc_group|
+-----------+
fifth |
| first |
| fourth |
| second |
| third |
+-----------+

Without grouping, duplicate information will be generated.

mysql> select hoc_group from student;
+-----------+
|hoc_group|
+-----------+
| first |
| second |
| first |
| third |
fifth |
| second |
| third |
| fourth |
fifth |
| fourth |
+-----------+

6.2, group_concat

Whatever field name is used for grouping, you can only query the field used for grouping during select query. Querying other fields will throw an exception and the sql_mode exception will be prompted.

We filter out students whose Js scores are greater than 80 and group them by the gender field. In addition, we also want to view the names of all students who meet the conditions.

The following operation will throw an exception.

mysql> select gender,name from student where js > 80 group by gender;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school.student.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

We must use the group_concat() function to perform the operation in order to meet our needs.

select gender, group_concat(name) from student where js > 80 group by gender;

+--------+--------------------+
| gender | group_concat(name) |
+--------+--------------------+
| male | Jack,Jason,Tom |
| famale | Bella |
+--------+--------------------+

6.3 Grouping Mode

ONLY_FULL_GROUP_BY requires that the fields in the select are the fields used in the group by

If the group by is the primary key or unique not null, you can list other fields in the select

#View the default sql_mode of MySQL 5.7 as follows:
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#Set sql_mole as follows (we can remove the ONLY_FULL_GROUP_BY mode):
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

6.4 Aggregation Functions

Aggregate functions can be used in all statements after the where clause is executed, such as having, select, etc.

Aggregation functions are generally used in conjunction with grouping. The following are commonly used aggregation functions.

Function Name effect
COUNT() Find the number of a certain field of members in the group
MAX() Find the maximum value of a field in the group
MIN() Find the minimum value of a field in the group
AVG() Calculate the average value of a field for members in a group
SUM() Sum a field of members in a group
Note: If grouping is not used, the default is one group.

The following example will show the sum of the scores of each group

select hoc_group, sum(js+html+css) from student group by hoc_group;

+-----------+------------------+
| hoc_group | sum(js+html+css) |
+-----------+------------------+
| fifth | 366 |
| first | 488 |
| fourth | 449 |
| second | 391 |
| third | 298 |
+-----------+------------------+

The following example will show the average score and total score of the entire class (round() is used for rounding operations)

select round(avg(html+js+css)) as average score, sum(html+js+css) as total score from student;

+-----------+--------+
| Average score| Total score|
+-----------+--------+
| 199 | 1992 |
+-----------+--------+

The following example will print out the highest score of the total subject

select max(js+css+html) from student;

+------------------+
| max(js+css+html) |
+------------------+
| 273 |
+------------------+

The following example will show how many boys and girls there are in the class

select gender, count(id) from student group by gender;

+--------+-----------+
| gender | count(id) |
+--------+-----------+
| male | 5 |
| famale | 5 |
+--------+-----------+

7. having

Having can also be used for filtering operations

7.1. Distinguishing differences

Execution priority from high to low: where> group by > having

where occurs before group by, so any field can be in where, but aggregate functions cannot be used.

Having occurs after grouping by, so the grouped fields can be used in having, and other fields cannot be directly obtained. Aggregate functions can be used

7.2. Example Demonstration

The following example will show how to use the having filter to extract the groups whose total score is greater than 400.

select hoc_group, sum(html+css+js) from student group by hoc_group having sum(html+css+js) > 400;

+-----------+------------------+
| hoc_group | sum(html+css+js) |
+-----------+------------------+
| first | 488 |
| fourth | 449 |
+-----------+------------------+

The following example shows how to use the having filter to retrieve students who have been punished. (You can use the grouped fields, but not other fields)

select sanction, group_concat(name) from student group by sanction having sanction != "None";

+-----------+--------------------+
| sanction | group_concat(name) |
+-----------+--------------------+
| Big Punishment | Dairis,Kyle,Ken |
| Minor punishment | Bella |
+-----------+--------------------+

8. Ordery by

ordery by is used to sort the query results

The default sorting is by primary key.

8.1 asc

asc is used for ascending sorting. The following example will show the ascending sorting according to the age of each student. If the ages are the same, they will be sorted in ascending order according to the total score.

select id, name, age, html+css+js as total score from student order by age, html+css+js asc;

+----+--------+-----+-----------+
| id | name | age | total score|
+----+--------+-----+-----------+
| 7 | Ken | 16 | 112 |
| 6 | Alice | 16 | 130 |
| 3 | Bella | 17 | 231 |
| 2 | Jack | 17 | 261 |
| 4 | Dairis | 18 | 186 |
| 1 | Yunya | 18 | 257 |
| 5 | Kyle | 19 | 115 |
| 10 | Fiona | 19 | 176 |
| 9 | Tom | 20 | 251 |
| 8 | Jason | 21 | 273 |
+----+--------+-----+-----------+

8.2、desc

desc is used to sort in descending order. The following example will show the descending order of each student's age.

select id, name, age, html+css+js as total score from student order by age desc;

+----+--------+-----+-----------+
| id | name | age | total score|
+----+--------+-----+-----------+
| 8 | Jason | 21 | 273 |
| 9 | Tom | 20 | 251 |
| 5 | Kyle | 19 | 115 |
| 10 | Fiona | 19 | 176 |
| 1 | Yunya | 18 | 257 |
| 4 | Dairis | 18 | 186 |
| 2 | Jack | 17 | 261 |
| 3 | Bella | 17 | 231 |
| 6 | Alice | 16 | 130 |
| 7 | Ken | 16 | 112 |
+----+--------+-----+-----------+

9. Limit

Limit is used to control the number of displayed items.

9.1、Demonstration

Sort in descending order by total score and only print 1-5.

 select id, name, age, html+css+js as total score from student order by html+css+js desc limit 5;
 
+----+-------+-----+-----------+
| id | name | age | total score|
+----+-------+-----+-----------+
| 8 | Jason | 21 | 273 |
| 2 | Jack | 17 | 261 |
| 1 | Yunya | 18 | 257 |
| 9 | Tom | 20 | 251 |
| 3 | Bella | 17 | 231 |
+----+-------+-----+-----------+

Sort in descending order by total score and only print 6-8th place.

select id, name, age, html+css+js as total score from student order by html+css+js desc limit 5,3; # Starting from the fifth place, print three results. 6, 7, 8

+----+--------+-----+-----------+
| id | name | age | total score|
+----+--------+-----+-----------+
| 4 | Dairis | 18 | 186 |
| 10 | Fiona | 19 | 176 |
| 6 | Alice | 16 | 130 |
+----+--------+-----+-----------+

Summarize

This is the end of this article about MySQL single table query. For more relevant MySQL single table query content, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • In-depth explanation of MongoDB slow log query (profile)
  • Implementation of MySQL select in subquery optimization
  • Detailed example of MySQL subquery
  • MySQL parameter related concepts and query change methods
  • MySQL paging query method for millions of data volumes and its optimization suggestions
  • How to optimize MySQL query speed
  • How to query date and time in mysql
  • MySQL database advanced query and multi-table query
  • Detailed explanation of MySQL slow log query

<<:  React's component collaborative use implementation

>>:  Summary of some points to note when registering Tomcat as a service

Recommend

JavaScript to display hidden form text

This article shares the specific code of JavaScri...

Quickly solve the problem that the mysql57 service suddenly disappeared

one, G:\MySQL\MySQL Server 5.7\bin> mysqld --i...

Four solutions for using setTimeout in JS for loop

Table of contents Overview Solution 1: Closures S...

What is BFC? How to clear floats using CSS pseudo elements

BFC Concept: The block formatting context is an i...

Detailed explanation of three methods of JS interception string

JS provides three methods for intercepting string...

Vue + OpenLayers Quick Start Tutorial

Openlayers is a modular, high-performance and fea...

Front-end JavaScript thoroughly understands function currying

Table of contents 1. What is currying 2. Uses of ...

Why can't the MP4 format video embedded in HTML be played?

The following code is in my test.html. The video c...

Summary of commonly used tags in HTML (must read)

Content Detail Tags: <h1>~<h6>Title T...

Vue encapsulates a TodoList example and implements the application of browser local cache

This article mainly introduces the case of Vue en...

Implementation code of html floating prompt box function

General form prompts always occupy the form space...

21 MySQL standardization and optimization best practices!

Preface Every good habit is a treasure. This arti...

Node.js+express+socket realizes online real-time multi-person chat room

This article shares the specific code of Node.js+...

A detailed introduction to the netstat command in Linux

Table of contents 1. Introduction 2. Output Infor...

Solution to the problem that MySQL in Windows system cannot input and display Chinese

Step 1: Use Notepad to open the "my.ini"...