A detailed explanation of the usage of MySQL common keywords

A detailed explanation of the usage of MySQL common keywords

In the development project, there are often different types of condition queries when operating the database. In addition to where, Mysql itself provides many common keywords. This article mainly introduces some common keywords, such as update, insert, delete and select, which are not explained.

distinct keyword

The purpose of the distinct keyword is to de duplicate the query. An example is as follows:

select distinct the selected field from table name conditional clause; / / distinct is placed in front of the de duplicated field

between keyword

between is the query criteria keyword, which is mainly used with and. The example is as follows:

select * from user where age between 25 and 30

limit keyword

The LIMIT keyword is mainly used in paging queries. The LIMIT clause is used to force the SELECT statement to return the specified number of records. LIMIT accepts one or two numeric parameters. Argument must be an integer constant. Given two parameters, the first specifies the offset of the first return record row and the second specifies the maximum number of return record rows. The offset of the initial record line is 0 (not 1), as follows:

SELECT * FROM table LIMIT 5,10; // Retrieving record lines 6-15  

//In order to retrieve all record lines from an offset to the end of the Recordset, you can specify the second parameter as - 1:    
SELECT * FROM table LIMIT 95,-1; // Retrieve record line 96 last   
  
//If only one parameter is given, it indicates that the maximum number of record rows is returned:    
SELECT * FROM table LIMIT 5; //Retrieve the first 5 record lines   
 
//In other words, LIMIT n is equivalent to LIMIT 0,n.  

count keyword

The main purpose of the count keyword is to return the number of all records. An example is as follows:

select COUNT(*) from user

sum, max, min, avg keywords

Sum, max, min and avg are four keywords, which respectively represent the sum, maximum, minimum and average of query results. Examples are as follows:

select sum(age) from user

select min(age) from user

select max(age) from user

select avg(age) from user

order by keyword

The order by keyword is mainly used for sorting. Sorting can be divided into positive order (asc) and reverse order (desc). Examples are as follows:

//Reverse query
SELECT * from runoob_tbl ORDER BY submission_date DESC
//Positive sequence query
SELECT * from runoob_tbl ORDER BY submission_date ASC;

group by keyword

The GROUP BY statement groups the result set based on one or more columns. We can use COUNT, SUM, AVG, and other functions on grouped columns. An example is as follows:
Create database table, insert data

DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Number of logins',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `employee_tbl`
-- ----------------------------
BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', 'Xiaoming', '2016-04-22 15:25:33', '1'), ('2', 'Xiao Wang', '2016-04-20 15:25:47', '3'), ('3', 'Xiaoli', '2016-04-19 15:26:02', '2'), ('4', 'Xiao Wang', '2016-04-07 15:26:14', '4'), ('5', 'Xiaoming', '2016-04-11 15:26:40', '4'), ('6', 'Xiaoming', '2016-04-04 15:26:54', '2');
COMMIT;

We use the GROUP BY statement to group data tables by name and count how many records each person has:

mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| Xiaoli |        1 |
| Xiaoming |        3 |
| Xiao Wang |        2 |
+--------+----------+
3 rows in set (0.01 sec)

WITH ROLLUP
WITH ROLLUP can realize the same statistics (SUM,AVG,COUNT...) based on grouped statistics.
For example, we group the above data tables by name, and then count the number of logins for each person:

SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| Xiaoli |            2 |
| Xiaoming |            7 |
| Xiao Wang |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.00 sec)

Tags: MySQL Database

Posted on Fri, 05 Jun 2020 03:52:12 -0400 by Maknis