当前位置：网站首页>Mysql database explanation (VII)

Many of the statements used in the production environment are not basic statements , It's an advanced statement 、 For example, filter query 、 Multi table query, etc . therefore MySQL High level statements are very important .

One . Common query

1.1 Sort by single field

select  Field 1, Field 2, Field 3 from  indicate  order by  Field  ;
asc Expressing ascending order （ Default ）
desc Representation of descending order

1.2 Conditions of the query

select  Field 1, Field 2, Field 3 from  Table name  where  filter ;

1.3 Multi field sorting

select  Field 1, Field 2, Field 3 from  indicate  order by  Field , Field  ;
 order by  The first parameter after that is only when the same value appears , The second field makes sense

Four . Interval judgment

AND/OR —— And / or

nesting / Multiple conditions

5、 ... and . Query does not duplicate records

select distinct  Field  from  Table name ﹔

1.distinct Must be placed at the beginning 
2.distinct You can only use fields that need to be de duplicated .
3.distinct When removing multiple fields , The meaning is : Only when several fields are repeated at the same time can they be filtered .

6、 ... and .. Group results

select  Field , Aggregate functions  from  Table name  (where  Field name ( matching )  The number ) group by  Field name ;




group by  Usually used in conjunction with aggregate functions , Common aggregation functions include ： Count （count）、 Sum up （sum）、 Average （avg）、 Maximum （max）、 minimum value （min）,group by  When grouping, the results can be grouped according to one or more fields

7、 ... and . Limit result entries

select  Field  from  Table name  limit [offset,] number


 limit  The first parameter is the position offset （ Optional parameters ）, It's settings  mysql  Which line to start with 
  If the first parameter is not set , It will start from the first record in the table .
  The first offset is 0, The second is 1
 offset  Subscript for index 
 number  Is the number of digits after the index subscript

8、 ... and . Set alias

 Alias of column 
select  Field  as  Field alias   Table name 

 The table alias 
select  Field  from  Table name  as  Alias  

as  It can be omitted 


 stay  mysql  When inquiring , When the name of the table is long or some fields in the table are long , To facilitate writing or use the same table multiple times , You can set aliases for fields, columns, or tables 
  Convenient operation , enhance readability 

 When querying complex tables , Aliases can shorten query statements 
  When querying multiple tables ( Easy to understand 、 Shorten  SQL  sentence )

Nine . Connection statement

AS Can be used as a join statement 
 for example ： create table info as select * from info1; 

 there AS What it does ：
1、 Create a new table lucien, Define table structure , Insert table data ( And working The table is the same )
2、 however “ constraint " Has not been “ Copy ” To come over , But if the primary key is set in the original table , Then the attached table : default The field will be set to one by default 0

Ten . wildcard

 Wildcards are mainly used to replace some characters in a string , Query the relevant results through the matching of some characters 
  Usually wildcards are and  like（ Fuzzy query ） Used together , And cooperate with  where Clause to complete the query task 
  There are two common wildcards 
%： The percent sign represents any number of characters 
_： The underline represents a single character

11、 ... and . Subquery

Definition ：

Subqueries are also called inner queries or nested queries , It means that another query statement is nested in one query statement
The sub query statement is executed before the main query statement , Seven results are returned to the main query as outer conditions for further query filtering
The sub statement can be the same as the table queried by the subject sentence , It can be different

 Example ：
select id,name from info where hobby in (select hobby from info1 where hobby>3);

in  Is the syntax that associates the main query with the sub query

Subquery ：

 example ：
select name,hobby from ljm
 The above command can query the of a table name and hobby Information 

 Take the result as “ surface ” When querying , We also need aliases 
 So let's say we type in ：select hobby from (select name,hobby from info);
 Will report a mistake , because  select name,hobby from info  What you get is a result set , Not a watch ,mysql  Is unrecognizable 
 So we need to set an alias for the result set , such mysql You can treat the result set as a table .

Subquery （exists）

select count(*) as num from info where exists(select id from info where id>3);

as num  take  count  The statistical results are taken as  num  return 
exists  Boolean judgment ,where The following statement holds , The Boolean value is 0, Then proceed to the previous operation , Otherwise, the return value is 0
 When using  sum Summation combination exists , If the query result is not valid , The return value is null

Twelve . View

A view is a table derived from one or more tables , It's a virtual existence table
Only the definition of view is stored in the database , Instead of storing the data in the view , The data is stored in the original table
When using views to query data , The database system will take the corresponding data from the original table
The data in the view depends on the data in the original table , Once the data in the table changes , The data displayed in the view will also change
Virtual tables in the database , This virtual table does not contain real data , Just mapping
deals / inverted reflection in water , Dynamically save the result set （ data ）

effect ：

1. Simplify the operation , You can define a view for frequently used queries , So that users do not have to specify conditions for the same query operation
2. Increase data security , Through view , Users can only query and modify the specified data
3. Improve the logical independence of tables , Views can mask the impact of changes in the original table structure

summary ：

In most cases of using views, in order to ensure data security , Improve query efficiency
Create a virtual table according to the filter criteria , When modifying data in a table , When the data in the original table no longer meets the filter criteria , The table in the view will no longer display this data

13、 ... and .null value

null The difference between a value and a null value ( Air and vacuum )

null, Does not occupy a space
Empty characters and 0, Occupancy space
is null Cannot determine null value
Null use "=“ perhaps ”<>" To deal with it （！=）
count（） When calculating ,NULL Will ignore , Null values are added to the calculation