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

# 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' ;``````

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

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

## Front end interview 3 + 1 Daily – 159th day

Today’s knowledge (September 22, 2019) – day 159 [HTML] for example, how to use pure HTML to realize the function of drop-down prompt? How to use CSS to achieve alignment? [JS] what is the difference between attribute and property in JS? [soft skills] did your last company write a daily, weekly or monthly newspaper? Talk […]