MySQL – Query Operations

Time:2019-9-11

Simple query

Select Select Query List

select [listname] from [tablename];
select [listname1] [listname2] from [tablename];

[*] indicates that all columns are displayed in the same order as when the table is created

Avoid duplication of data

/* Remove duplicate data from columns*/
select distinct [listname] from [tablename];
/* Remove column names 1 and 2 from each data*/
select distinct [listname1] [listname2] from [tablename];

MySQL - Query Operations
MySQL - Query Operations

Implementing Mathematical Operational Query

YesInteger, decimalType data can use (+ – */) to create expressions
YesdateType data can use (+-) to create expressions

/* Search for id, name and wholesale price of all goods (sale price * discount)*/
select id,productname,saleprice*cutoff from tablename;

/* Inquire all product id, name and purchase cost of 50 items*/
select id,productname,costprice*50 from tablename;

Set aliases for columns

Meaning used to represent the results of calculation
Single quotation marks are required for aliases that use special characters, force case sensitivity, and spaces.

/* Give costprice * 50 the nickname XXX, in fact as can be omitted*/
select id,productname,costprice*50 as XXX from tablename;

Setting Display Format Query

/* Format: XXX Retail Price: XXX*/
Select concat (product name,'retail price of goods:', saleprice) from tablename;

Filtering queries

Replace the from clause with the where clause

/* Data on age 23 in the table*/
select * from t_students where age = 23 ;

/* Data named Yang Minhao in the table should be enclosed in single quotation marks for strings and dates*/
Select * from t_students where name ='Yang Minhao';

/* Aliases for column names cannot be used in where clauses*/
select name as mingzi from t_students where name != 'Mh' ;
  • Strings and dates are enclosed in [‘] single quotes
  • To make MySQL queries case-sensitive:
/* Case-insensitive by default*/
select * from t_students where name = 'Mh' ;

/* After where, add binary to distinguish case from case*/
select * from t_students where binary name = 'Mh' ;

MySQL - Query Operations

The execution order of each clause in SQL is as follows:

  1. From: Determine which table to use for queries
  2. Where: Select eligible data from tables
  3. Select: Display the results of the screening in a centralized manner
  4. Order by: sort
/* AND */
select * from product where saleprice>=300 AND saleprice<=400;
/* OR */
select * from product where saleprice=300 OR saleprice=400;
/* NOT */
select * from product where NOT saleprice=300 ;

If there are multiple query conditions, try to put the most filtered conditions nearest where to improve performance.

priority operator
1 + – * /
2 NOT
3 AND
4 OR

Range query

Between-and, commonly used in numeric/date type data, is also available for character types.

/* Selecting Range with Between-and Statement*/
select * from product where saleprice between 300 and 400;

Select * from product where NOT saleprice between 300 and 400; /* reverse*/

Aggregate query

In, does the column value exist in the collection for bulk deletion

select * from product where id in (2,4);

Null value query

Is null, to determine whether the column’s value is empty (NULL, not an empty string)

select * from product where id is null;

MySQL - Query Operations

Fuzzy query

Like, query conditions can contain text or numbers
[%]: Can represent zero or any number of characters
[_]: Represents a character

/* Value to end with Logitech M*/
Select * from product where name like'% Logitech M';

/* Value begins with Logitech M*/
Select * from product where name like'Logitech M%';

Paging query

  • False Paging/Logical Paging/Memory Paging:

Query out all the data at onceIt is stored in memory, and every page turn, the specified number of entries is taken out of memory.

Features: Page turning is faster, but when the amount of data is too large, it may cause memory overflow.

  • True Paging/Physical Paging/Database Paging (Recommendation):

Each page turn is to intercept the specified number of entries from the database, assuming 10 entries per page, the first page is to query 0-9 entries, and the second page is to query 10-19 entries.

Characteristic: Page turning is slow and does not cause memory overflow

Paging design of MySQL:

Int PageSize = n; /* means that up to N data are displayed per page*/

Paging query result set SQL:

select * from tablename limit ?,?; 

The first [?]: (current page – 1)* displays n data per page
The second [?]: n pieces of data are displayed on each page

Page 1: SELECT * FROM `test1'. `t_students `LIMIT 0,2
Page 2: SELECT * FROM `test1'. `t_students `LIMIT 2,2
Page 3: SELECT * FROM `test1'. `t_students `LIMIT 4,2