Simple query in MYSQL

MYSQL simple query

Complete syntax:

select [distinct] , … [from [where ] [group by , … [having ] ] [order by asc| desc ] [limit [offset , ] rows ] ]

select Simple query

select  1 ;  -- Often used for Do Database heartbeat detection
select user() ;  -- Get Current logged in user information
select version() ;  -- Get数据库的版本号
select now() ;  -- Get Current system time 
select last_insert_id() ;  -- Get The last primary key inserted(Must be self-increasing)

Based on table query

select  &lt;columnName&gt; , ....  from  &lt;tableName&gt; ; 
select * from &lt;tableName&gt;  ;  -- Query All fields in the table ， In production environment Not recommended * Query所有字段

Condition-based query

select  &lt;columnName&gt; , ... from &lt;tableName&gt;  where &lt;condition&gt; ;
select * from tb_user where name = 'admin'  ;  --  When querying nameValue of Case insensitive 
select * from tb_user where binary name = 'admin' ;  -- Queryed name value case sensitive

Group-based query

count(): used to count the number

--  Query Students' table How many are there student 

select count(*)  from  student ;  -- By behavior unit ，统计How many are therestudent
select count(stuNo) from student ; -- According to student number Come to count How many are therestudent
select count(1) from student ;  -- According to constant value Conduct statistics、If there is a value，Number of + 1

From a performance perspective, count(1) > count( * ), count(column) only counts the number of rows in this column whose value is not null (null does not participate in statistics).

If the column in column in count(column) has an index, the performance will be higher than count(1) , and if there is no index, the performance will be lower than count(*)

sum() : Sum

-- Check the total score of students 
select sum(score) from student ;  -- sum function parameter Only incoming Field name，  The corresponding field column null Not participating Ask for sum

avg() : Find the average value

-- Inquiry of students Average grade 
select avg(score) from student ;  -- sum function parameter Only incoming Field name，  The corresponding field column null Not participating Find the average value
select  avg ( ifnull(score,  0) ) from student ;  -- The corresponding field column null, but Pick 0 ， Still participating beg average value

max() : Find the maximum value

-- Query Highest score 
select  max(score) from student ;

min() : Find the minimum value

-- Query Lowest score 
select min(score) from student ;  -- No null value is involved beg Minimum value

group by implement group query

-- Query different Gender Number of students 
select  sex,  count(1) from student group by sex ;

group by group has requirements for the query column, either the query column is an aggregate column or appears behind group by as the basis for grouping.

having grouping filtering

The results after grouping are filtered and filtered, and having is based on the existence of group by.

 -- Query In class The same name 、Same-sex of Student name and gender
 select name, gender from student group by name ,gender having count(1) &gt; 1 ;

The difference between where and having

where is to filter the data in the table, and having is to filter the results after grouping.
where and having if they exist at the same time, then where filter first, then group and then filter
The aggregate function cannot be used in the where condition, but the aggregate function can be used when having
You can use where to filter the filtered data, try not to use having filter

Data sorting order by

-- Query All student information 、according to Sorting results in descending order，When the grades are the same ，according to Date of birth Arrangement in descending order 
select * from student order by score desc , birth desc  ;

When multiple fields participate in the sorting, the first sorted field will be sorted first, and when the value of the first field is the same, the second field will be sorted according to the value of the second field, and so on.

Pagination query limit

select * from student  limit 3 ;  -- In the query table forward 3 Data 
select * from  student  limit  10 ,  3  ;  -- In the query table from The 11Data开始 3 Data 
select * from  student limit 10 offset 3 ;

When pagination query, if order by is included, it is recommended to sort according to the unique key. If there are a lot of repetitions according to the field value, it is recommended to sort multiple fields, otherwise the pagination data will occur.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.