Where conditional data filtering for MySQL

Time:2021-7-26

Syntax:

--Select * from table name where condition;

Test data:

--Create table statement
create table `student` (
`sid` int(11) primary key auto_increment,
`sname` varchar(20) not null,
`sex` varchar(10) default null,
`isdelete` tinyint(1) default 0
)charset=utf8;

--Insert data
insert into `student`(`sid`, `sname`, `sex`) values
(0, 'Dharma', 'male'),
(0, 'Dianwei', 'male'),
(0, 'Cao Cao', 'male'),
(0, 'Zhong Wuyan', 'female'),
(0, 'Monkey King', 'male'),
(0, 'King of Lanling', 'male'),
(0, 'Cheng Yaojin', 'male'),
(0, 'Liu Bei', 'male'),
Liu Chan, male,
(0, 'sun Shangxiang', 'female'),
(0, 'Sun Bin', 'male');

Comparison operator

  • be equal to:=
  • Greater than:>
  • Less than:<
  • Greater than or equal to:>=
  • Up to:<=
  • Not equal to:!=perhaps<>
--Query student information with Sid less than 3
select * from student where sid<3;

Execution results:

Logical operator

  • And:and
  • Or:or
  • Non:not
--Students whose word sid is greater than 8 and sex is male
Select * from student where Sid > 8 and sex = 'male';

--Query students whose sid is greater than or equal to 10 or whose sid is less than 3;
select * from student where sid>=10 or sid<3;

--Query students whose sid is not greater than 3
select * from student where not sid>3;

Execution results:

Priority: not > and > or

Note: and takes precedence over or. If both occur and you want or to take precedence, you can use parentheses.

Fuzzy query – like

  • %Indicates that one or more arbitrary characters are matched

    Select * from student where sname like 'sun%';
    Select * from student where sname like '% none%';
    Select * from student where sname like 'grandson%' or sname like '% none%';

Execution results:

  • _ Indicates that an arbitrary character is matched

    Select * from student where sname like 'sun';
    Select * from student where sname like 'sun _'-- Two underscores

Range query

  • Discontinuous range query: in

    select * from student where sid in(1,2,3,8);

    Execution results:

    Note: if there is no data in Article 8, no error will be reported, but there is no return value

  • Continuous range query: between… And

    select * from student where sid between 3 and 8;
    Select * from student where Sid between 3 and 8 and sex = 'male';

    Execution results:

    Note 1: if there is no data in Article 8, no error will be reported, but there is no return value

    Note 2: if between… And… And are used at the same time, between… And

Empty and non empty

  • Null judgment:is null
  • Non empty judgment:is not null
--Query students without gender information
select * from student where sex is null;

Execution results:

be careful:nullAnd’ ’Different.nullIt is empty and does not occupy any memory in the computer;’ ’Null character
String, which needs to occupy a certain memory.

Aggregate function

Please checkMySQL functionsA section

Group by

Coming soon

Data filtering after grouping —– having

Coming soon

Order by

  • Syntax:Select * from table name order by column 1 ASC / DESC, column 2 ASC / DESC;
  • explain
    • Sort some of the data by 1, and if they are the same, column them into 2
      Sort rows.
    • By default, it is sorted in ascending order ASC.
    • ASC: ascending order; Desc: descending.

Example:

select * from student
Where sex = 'female'
order by sid desc;

Execution results:

Get some lines – limit

  • Syntax:Select * from table name limit atart, count;
  • Note: start with start, get count pieces of data, and start index starts with 0.

Example:

select * from student limit 1,5;

Execution results:

paging

Syntax:Select * from table name where query criteria limit (curpage - 1) * PageSize, PageSize;

Note: curpage is the current page; PageSize is the number of records on a page

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]