Overview of select statement

There are three common functions of keyword select ion in query statements: single-table query, multi-table join query and sub-query.
All the following introductions are about manipulating this table

        create  table tml  //Main table, two fields, ID (primary key self-increasing) and name (non-empty)
        (
            tml_id int auto_increament,
            tml_name varchar(255) not null,
             primary key(tml_id)
        );
        creat table student //Slave table, three fields, ID (primary key self-increasing) and name, and java_tml (refer to foreign key of main table name)
        (
            stu_id int auto_increment primary key,
            stu_name varchar(255),
            #Specify java_tml reference to the tml_id column
            java_tml int ,
            constraint   student_tml_fk   foreign   key   (java_tml)   references   tml_table  (tml_id) //Specify a name for a foreign key constraint

);  Operate on this table

* Single table query statement

Basic Query Form

   #Execute form queries
    select student_name     //Select columns. If you select all columns, you can replace them with *.
    from  student_table     //Selected data table
    where  java_tml>3       //Conditions for rows

    #You can use arithmetic operators in select statements (in a sense, data columns are also variables)
    select  *
    from  student_table
    where  student_id*3>4;

    #String join operation using concat function
    select  concat(tml_name,'xyz')
    from tml_table;

    #For mysql, whether null is used in arithmetic expressions or in join operations, the result is null
    select concat(tml_name,null)
    from tml_table;                  //Returns null

    #Individual names for data columns or expressions
    select  tml_id+5 as  MY_ID       
    from tml_table；
    *****************************
    select  tml_id+5  "MY'ID"
    from tml_table;                                                    //Select a new column (6, 7, 8... And take an alias MY_ID for it.
    #Aliases for multiple columns and tables
    select  tml_id+5  MY_ID,tml_name  Teacher's Name      //Aliases for multiple lists
    from tml_table  t;                                                 //Aliases for tables

    #Removing duplicate values of multi-column combinations with distinct
    select  distinct  tml_id,tml_name
    from  tml_table;                                 //Only when all the values of their combinations are the same (1,tianmaolin), (1,tianmaolin), (1,tianmaolin), here is the relationship between them.

Operators in SQL for queries

Assignment Operators

Not an equal sign, but a colon equal sign (:=)

Comparison operator

Keyword between

                select * from student_table
                where student_id between 2 and 4; // (The two values can't be equal, and both values are the relationship between big equals and small equals)
                **************It can also be between two columns.**********
                select * from student_table
                where 2 between student_id and java_tml; //Remove all records of student_id >= 2 and java_tml<== 2

Keyword in

    select  *  from  student_table
    where  2  in(student_id,java_tml);               //Choose all records in parentheses that have a value of 2. Here is the relationship between yes and No.

Keyword like

I think it's important to use like for fuzzy queries. There are two common wildcards in mysql: underscore () and percentage (%). Underline represents one character and wildcards represent multiple characters. Usually, there are the following grammatical applications

#Query all classmates surnamed Sun
select *from student_table
where student_name  like 'Grandchildren%'；
#Query all students whose names are not two characters
select * from student_table
where   not  student_name like '__';
#The escape keyword is usually used to query data with underscores and percentage wildcards
select  *  from student_table
where  student_name like '\_%' escape  '\';
#Use a combination of like keywords and (and) or (or) to specify a search for multiple conditions
select *  from  student_table
where student_name like '__' and student_id>3 ; //Select all records whose student_name is two characters and student_id>3

The keyword is null

is null keyword judgement empty

select *from student_table
where student_name  is  null;      //All empty records in the table will be selected

Logical Operator

not"and"or"are sorted by priority, and all comparison operators take precedence over logical operators

Sort the query results

Key words: order by, desc (descending), ASC (ascending) by default in ascending order

select *from student_table
order by  java_teacher desc , student_name;    //Force descending order according to java_teacher, and ascending order according to student_name when java_teacher is the same

Database functions (single-line functions)

Functions are used to perform complex operations on data, which are divided into single-line functions (calculated separately for each input value, one input to one output) and multi-line functions (multi-line simultaneous calculation, multi-line input to one output, also known as aggregation function or grouping function).

Characteristic of one-line function

1. The parameters of a one-line function can be variables, constants or data columns. Each row can accept more than one parameter, but only return one value.
2. Using one-line functions can change the data type of parameters.
3. One-line functions support nested use, that is, the return value of inner function is the parameter of outer function.

Classification of one-line functions (corresponding to mysql data types)

1. Date-time function, numerical function, character function.
2. Conversion function, mainly used to complete the conversion of data types.
3. Bit function, process control function, encryption and decryption function, information function

Example of one-line function

Select the character length of the tml_name column in the tml_table table table
select  char_length  (tml_name)
from  tml_table;
# Calculate the sin value of the character length of the tml_name column
select sin  (char_length  (tml_name))
from  tml_table;
# Calculate the sin value of 1.57
select sin  (1.57);

select  curtime();      Get the current time
select  curdate();    Get the current date
select  now();          Get the current date and time
select  date_add ( now(), interval 1  day/hour/minute/second/mirsecond/week/month/quarter/year)  //Add time to the current time

select MD5('testing')     //MD5 is an encryption function

Functions dealing with null
 # If student_name (expr1) is listed as null, then "no name" (expr2) is returned.
Select ifnull (student_name,'No name')
from   student_table;
# If expr1=expr2, return null or expr1
 Select nullif (student_name,'Zhang San')
From student_table;//If student_name equals Zhang San, return null or expr1
 # If student_name is listed as null, no name is returned, otherwise a name is returned.
Select if (isnull (student_name),'No name','name')
From student_table;//isnull (expr1) If expr1 is null, it is true, and if true, it returns no name.

Process Control Function
select    student_name, case  java_teacher  //Return the list of students according to the teacher's id number
case    value
when   compare_value1   then   result1
when   compare_value2   then   result2
.......
else   result
end
from   student_table
//IfvalueThe value is equal to the following value1 Then return result1 The result is the same later.
select     student_name, case
when  condition1   then   result1
when  student_id<=3   then  'Junior class'
.......
else   result
end
from   table

Grouping and group functions

A group function is a multiline function.

#Calculate the number of records in the student_table table table table
select   count(*)                 //* Represents the number of rows recorded in the table
from   student_table;    //Note that * and distinct (not counting duplicate bars) cannot be used simultaneously
#Calculate how many values are in the java_teacher column
select   count(distinct  java_teacher)
from   student_table; 
#Statistics the sum of all student_id s
select   sum(student_id)
from   student_table; 
#Select the maximum student_id in the table
select   max(student_id)
from   student_table; 
#Select the minimum value of student_id in the table
select   min(student_id)
from   student_table; 
#In avg calculation, the following steps are used to avoid null value calculation
select   avg(ifnull(java_teacher,0))
from   student_table; 

Grouping: keyword: group by

select *                                                   
from  student_table                           //Select all data
group  by  java_teacher                     //Grouping according to the same java_teacher value
havaing   count(*)>2;                          //Groups with more than 2 records are selected

* Multi-table join query

sql92 Specification (Equivalent Connection, Non-Equivalent Connection, External Connection, Generalized Cartesian Product)

********Equivalent connection*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s,teacher_table  t      //Identify the source table and alias it
where  s.java_teacher=t.teacher_id  and student_name is not null                                   //Specify the connection condition
*****Non-Equijoin*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s,teacher_table  t       //Identify the source table and alias it
where  s.java_teacher>t.teacher_id               //Specify the connection condition

***Extended cartesian product*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s,teacher_table  t   //Identify the source table and alias it

******External connection*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s,teacher_table  t   //Identify the source table and alias it
where  s.java_teacher=t.teacher_id  (*)     //The right outer link returns the qualified values, the right outer link returns the data rows of the query that do not meet the join conditions in the right table, and the null value is returned if there is no corresponding on the left.

* sql99 specification (cross-links, natural links, using clause links, on Clause links, all-out links or left-right external links)

****Cross-linking*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s                  //from followed by only one table name
cross join  teacher_table t             //Equivalent to the generalized Cartesian product
*****Natural Connection*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s                  //from followed by only one table name
nature  join  teacher_table  t          //All the same columns in the two tables will be selected.     

***using Clause conjunction*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s                  //from followed by only one table name
join  teacher_table  t          //You can also use nature join instead of join to select all the same columns in both tables.     
using (The same column in two tables)        //Specifying conditions  
******on Clause links*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s                  //from followed by only one table name
join  teacher_table  t          //You can also use nature join instead of join to select all the same columns in both tables.     
on s.java_teacher=t.teacher_id      //on clause links can completely replace equivalence and non-equivalence links under arbitrary conditions.

*****Right External Connection*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s                  //from followed by only one table name
right   join  teacher_table  t         //The right outer link returns the qualified values, the right outer link returns the data rows of the query that do not meet the join conditions in the right table, and the null value is returned if there is no corresponding on the left.
on s.java_teacher=t.teacher_id              
******External connection*********************************************************
select s.*，teacher_name              //Query all the students'information and the names of the corresponding teachers
from  student_table   s                  //from followed by only one table name
full  join  teacher_table  t         //Total External Connection will list all eligible records in both tables
on s.java_teacher=t.teacher_id              

* Subquery statement

Think of subqueries as data tables (temporary views)

select *
from (select * from  student_table)  t
where  t.java_teacher>1

Think of subqueries as filtering conditions

select *
from student_table
where   java_teacher>(select  teacher_id   from  teacher_table  where  teacher_name='yeeku') ;//The returned subquery values are used as scalars
*******************************
select *
from student_table
where  studetn_id  in  (select  teacher_id   from  teacher_table) ;//A set of values will be returned, and then compared with studetn_id, the same is selected.
*******************************
select *
from student_table
where  studetn_id  >
any  (select  teacher_id   from  teacher_table) ;//Require greater than the minimum in the list of values
*******************************
select *
from student_table
where  studetn_id  >all (select  teacher_id   from  teacher_table) ;//Require greater than the maximum in the list of values

Set operation

In fact, it is a combination of multiple select s. The number and type of data columns contained in the two result sets must be the same.

Union operation (union)

select  *  from  teacher_table
union
select   student_id,student_name  from  student_table;

minus operation (difference) (substituted by sub-query)

select  student_id  ,student_name   from student_table
where ( student_id  ,student_name)
not  in 
(select   student_id,student_name  from  student_table);     //Find out all the records with the same id and name subtracted from the teacher's table. in denotes the equality of scalar values and using denotes columns with the same name  

intersect operation (intersection) (replaced by multi-table query)

select  student_id  ,student_name   from student_table
join
teacher_table
on(student_id=teacher_id  and  student_name=teacher_name)
(select   student_id,student_name  from  student_table);    //Find out the same items in all student and teacher lists
where  teacher_name  like 'plum%' and  student_id<4;     //The subdivision conditions on two tables represent the constraints between different tables. where represents the respective constraints of each table.