MySQL Advanced ---- Filter conditions

select * from ... where ...;

Usually we don't need to look at all the rows of a table , What we need to look at is a line with certain conditions . front MySQL In the basic study of using , I used simple conditional filtering where age > 20 , This article will focus on where The conditions are more detailed ! I haven't learned the basic content 【MySQL Getting started 】

Simple screening

Symbol meaning
> Greater than
< Less than
= be equal to
>= Greater than or equal to
<= Less than or equal to
!= It's not equal to
<> It's not equal to

Compound condition --and 、or and not

To learn MySQL My friend, I acquiesce that you have learned at least one programming language .

A condition is a logical expression , The result of the expression is only true and false. Multiple expressions can use and and or To connect , respectively And and or , You can also use not To express Not . Here are some examples of specific uses .

--  Older than 10 And less than 20
select s_name from students where age > 10 and age < 20;
-- Age is less than 10 Or greater than 20
select s_name from students where age < 10 or age > 20;
-- Not ( Age is less than 10 Or greater than 20), Equivalent to age not less than 10 And not more than 20
select s_name from students where not (age < 10 or age > 20);
  • In the use of and and or We need to pay attention to their priority , Let's look at the following requirements
--  Search out all age groups for 20 or 21 year , Good grades (90 More than ) Information about our students 
select * from students where age = 20 or age = 21 and score >= 90;
-- The above sentence seems to be the result we want , In fact, the result is not what we want .
-- The actual meaning of the above condition is :20 I'm a 12-year-old student or 21 I'm a 20-year-old student with excellent grades
-- Why is that? , The reason is that MySQL in and The priority ratio or Higher , This leads to the combination of the latter two conditions , That is to say
select * from students where age = 20 or (age = 21 and score >= 90);
-- So in order to achieve our original intention , It should be written as follows
select * from students where (age = 20 or age = 21) and score >= 90;

When you don't know how priorities are set , Using brackets is always a safe choice , And it makes the meaning of the condition clearer . I believe you have the same experience in other languages .

String filter

= This filter is obviously not enough for Strings , Let's introduce like and regexp


--  The results of the following two statements are exactly the same 
select * from students where s_name = ' Zhang San ';
select * from students where s_name like ' Zhang San '; -- like Only collocation % and _ Only then and = Dissimilarity
-- The following sentence filters out students surnamed Zhang , such as ' Zhang San ',' zhang wuji ' etc. , It can also match ' Zhang '.
select * from students where s_name = ' Zhang %';
-- Can match ' Zhang San '、' Zhang Fei ' etc. , But it doesn't match ' zhang wuji '.
select * from students where s_name = ' Zhang _';

% and _ It's all wildcards , The former matches any character of any length , Include 0 length , The latter matches a single arbitrary character .


except like This simple screening condition , We can still do that MySQL Regular expressions are used as filters in

select prod_name from products where regexp 'ipad air[12]';
-- You can find the trade name :'ipad air1 64g'、'ipad air2 128g'、'ipad air1 128g' etc.

The knowledge of regular expressions is not detailed here , Please refer to the relevant materials for study .

What needs to be explained :

  • MySQL Regular expressions in Escape character by \\.
  • Be careful like and regexp The important difference between , The former is a perfect match for Strings , The latter is partial matching , Regular is a perfect match only if the first locator is added .
  • MySQL Of 3.23.4 Later versions are case insensitive in regular expressions ! If you need to be case sensitive, use regexp binary

Text processing function

Text processing functions can be used in filtering conditions

function explain
Left() Returns the character to the left of the string
Right() Returns the character to the right of the string
Locate() Find a substring of a string
SubString() Return the character of the substring
LTrim() Remove the space to the left of the string
RTrim() Returns the character to the right of the string
Length() Returns the length of the string
Soundex() Return string SOUNDEX value
Lower() Convert string to lowercase
Upper() Convert string to uppercase
--  Example : find 'tom','Tom','TOM' etc. 
select name from students where Lower(name) = 'tom';

Numerical processing function

function explain
Abs() Returns the absolute value of a number
Mod() Returns the remainder of the division operation
Exp() Returns the index value of a number
Mod() Returns the remainder of the division operation
Sin() Returns the sine of an angle
Cos() Returns the cosine of an angle
Tan() Returns the tangent of an angle
Sqrt() Returns the square root of a number
Pi() Return the PI
Rand() Returns a random number
--  Example : Find out if all the scores are in single digits 5 Student information , such as 75、85、65 etc. 
select * from students where Mod(score,10) = 5;

Date processing function

function explain
AddDate() Add a date ( God 、 Zhou et al )
AddTime() Add a time ( when 、 Grading )
CurDate() Return current date
CurTime() Return current time
Date() Return date part of date time
DateDiff() Calculate the difference between the two dates
Date_Add() Highly flexible date operation function
Date_Format() Returns a formatted date or time string
Day() Returns the days part of a date
DayOfWeek() For a date , Returns the corresponding day of the week
Hour() Returns the hour part of a time
Minute() Returns the minute part of a time
Month() Returns the month part of a date
Now() Returns the current date and time
Second() Returns the second part of a time
Time() Returns the time part of a date time
Year() Returns the year part of a date
--  Example : Find out all 1999 Information about students born in 
select * from students where Year(birthday) = 1999;

<!> Be careful stay MySQL The assignment format for the date type in is yyyy-mm-dd,datetime The type contains not only the date but also the time , So if you want to filter on 1999-07-24 When using birthday = '1999-07-42' It's not accurate , This only matches 00:00:00 Time . belong birthday Put it on the outside Date() function . When dealing with dates, we should make good use of date processing functions !


be used for Numerical range or Time interval

--  Example : Find out all 90 After the information of students 
select * from students where Date(birthday) between '1990-01-01' and '1990-12-31';


in I can represent the range , A collection, to be exact

  • Use a : parentheses
--  Example : Find out all ages for 15、18 or 24 Information about our students 
select * from students where age in (15,18,24);
  • Use two : Subquery
--  Suppose there is a table items Store product information , A table orders Store order information ( Suppose an order has only one product )
-- Example : Find out all the purchases ipad air Orders for a range of products
select * from orders where orders.item_id in (
select item_id from items where item_name regexp 'ipad air'

Null check

null Values in MySQL The Chinese price is special , It represents an unspecified or uncertain value . Whether they have null You need to use is null To judge

--  Example : Find out all the unfilled profile information ( The value must be null, Empty strings don't count , Just spaces are not ) My book book_id
select book_id from books where info is null;

<!> Be careful NULL Does not match

When you filter out rows that do not have a specific value , You may want to return with NULL Row of values . however , no way . Because the unknown has a special meaning , The database doesn't know if they match , So don't return them when they match or don't match filtering . therefore , When filtering data , Be sure to verify that the returned data does give that the filtered column has NULL The line of .


  • Comparative relationship : Greater than 、 Less than 、 be equal to ...
  • and、or、not
  • String filtering :like、 Regular 、 Text processing function
  • function : Numerical processing 、 Date processing 、 Text processing
  • between、in、 Control checks

MySQL--WHERE More articles on the topic

  1. 「mysql Optimize the topic 」90% The teaching of stored procedures and stored functions that programmers have never heard of (7)

    One .MYSQL Introduction to storage process ( Technical papers ): A stored procedure is a programmable function , It creates and saves... In the database . It can have SQL Statement and some special control structures . When you want to execute the same function on different applications or platforms , Or when encapsulating specific functions ...

  2. 「mysql Optimize the topic 」 High availability 、 Load balanced mysql Clustering solutions (12)

    One . Why mysql colony ? In the performance bottleneck of a huge distributed system , The most vulnerable is the connection . There are two connections , One is the connection between the client and the back end , The other is the connection between the back end and the database . It's as simple as the two blue frames in the picture ( Actually , This picture is my question and answer in Wukong ...

  3. 「mysql Optimize the topic 」90% Programmer interview can use the index optimization manual (5)

    Catalog ( Technical papers ) More about index , Divided into the following points to explain : One . An overview of the index ( What is index , Advantages and disadvantages of index ) Two . Basic use of index ( Create index ) 3、 ... and . The fundamentals of indexing ( Interview focus ) Four . The data structure of the index (B Trees ,hash) 5、 ... and . ...

  4. mysql Optimize the topic 」90% Programmers will ignore the addition, deletion, modification and optimization (2)

    Supplementary information : Operational data statement optimization understanding Usually , When accessing a table , The reader must first acquire the lock of the table , If a write arrives , So the writer is waiting for the reader to complete the operation ( The query cannot be interrupted after it has started , So the reader is allowed to complete the operation ). ...

  5. 「mysql Optimize the topic 」 This is probably the best one mysql Optimization introductory article (1)

    Optimize , It's always the most frequently asked question in an interview . Because from the perspective of optimization , Optimization ideas , You can see a person's technology accumulation . that , About system optimization , Suppose this scenario , Users reflect the system is too laggy ( It's actually high concurrency ), So how do we optimize ? If there are too many requests , ...

  6. 【 turn 】[Mysql] Linux Mysql Journal topics

    Link to the original text : 1, Set the directory to store : [root@Linux etc]# more /etc ...

  7. 「mysql Optimize the topic 」 Master and slave copy interview Dictionary ! The interviewer doesn't know as much as you !(11)

    The content is more , Can be collected first , Directory as follows : One . What is master-slave replication Two . The role of master-slave replication ( a key ) 3、 ... and . The principle of master-slave replication ( A top priority ) Four . Three steps to easily build master-slave 5、 ... and . Must ask the interview question dry goods analysis ( The most important point ) One . What is master-slave replication ( Technical papers ...

  8. 「mysql Optimize the topic 」 What is slow query ? How to optimize log by slow query ?(10)

    Diaries are just like people's diaries , Recording the past . But people's diaries are subjective ( Remember what you want to remember ), And the database log is objective , According to the record content, it can be divided into the following several kinds of logs ( Technical papers ): a. Error log : Record start . To run or stop mysqld ...

  9. 「mysql Optimize the topic 」 Explain the engine (InnoDB,MyISAM) Memory optimization strategy ?(9)

    Be careful : All of the following are in MySQL In the catalog my.ini In the file ( Technical papers ). One .InnoDB Memory optimization InnoDB Use a memory area to do I/O Buffer pool , The cache pool is not just for caching InnoDB The index block of , And it's also used to cache Inn ...

  10. 「mysql Optimize the topic 」 The road to optimization is advanced —— Design and optimization of tables (6)

    Text : Design and optimization of tables ( It's really technical ) Optimize ①: Create normalized tables , Eliminate data redundancy The database paradigm is to ensure that the database structure is reasonable , Meet all kinds of query needs . Database design to avoid abnormal database operation . Tables that meet the requirements of the paradigm , It's called normalization table , Paradigms arise from 2 ...

Random recommendation

  1. 【Java A daily topic 】20161201

    20161130 For problem analysis, please click below today's questions "[Java A daily topic ]20161201" see package Dec2016; public class Ques1201 { publ ...

  2. gulp Perform tasks in sequence

    gulp The execution of the task is asynchronous .  therefore , When I finish a series of tasks , Be ready to do it all at once . # gulp.task('prod', ['clean', 'compass', 'image', 'style', ' ...

  3. Linux System (X64) install Oracle11g Complete installation of graphics and text tutorial, additional basic operation

    One : View local ssh service Linux Install and start under the system ssh service , Let's say CentOS edition Linux System as an example : 1. Check to see if SSH package rpm -qa |grep ssh 2. No installation SSH direct YUM install ...

  4. java Judge whether it is Chinese character or not , part ,. Chinese symbols cannot be recognized

    public static void main(String[] args) { int i = 0; for (char c : ",. To determine whether a string has Chinese characters, we usually use Unicode ...

  5. jasper A blank page appears in the printed file

    EG: Print the file and the result prints out a blank reason : Used null Instead of JREmptyDataSource Here is the correct code public <T> List<JasperPrint> ...

  6. Java8 The new features Collectors

    Reference resources :Java8 The new features Collectors On the next day , You have learned Stream API It allows you to help you deal with collections in a declarative way . We see collect It's a way to put the results of pipe flow into a list End operation in .c ...

  7. Itellj Idea Using skills

    Template settings : Class template :File–>Settings–>Editor–>File and Code Templates–>Includes–>File Header Enter abbreviation ...

  8. python And redis

    1. What is? redis Redis It's a key-value The storage system . and Memcached similar , It supports storage value There are more types , Include string( character string ).list( Linked list ).set( aggregate ).zset ...

  9. T4 Study - 3、 Create a runtime template

    Use Visual Studio Preprocessed text template , You can generate text strings in your application at run time . The computer executing the application does not have to have Visual Studio. Preprocessed templates are sometimes called " Runtime text ...

  10. Compensating-Transaction Pattern

    In the application , A series of related operations will be defined as a continuous operation , When one or more of the steps fail ,Compensating-Transaction The mode will reset ( Roll back ) This continuous operation . In Cloud Applications , These need to be consistent ...