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

Recommended Today

Understanding and deepening of relative path and absolute path

What is relative path and absolute path Last week’s report solved some problems, but also exposed many problems, one of which is the relative path and absolute path. For PHP using xampp to build a server, the relative path refers to the current file relative to the user’s access, and the absolute path refers to […]