编程知识 cdmana.com

MySQL basic introduction single table query

Single table query

select Header , Header as Alias , Header (+-*/ Arithmetic ) from table_a

1. Conditions of the query

where + Conditions
<> , != It's not equal to
= be equal to , It can also indicate that string values are equal
<,> Less than , Greater than
>=,<= Greater than or equal to , Less than or equal to
between... and.. Between two values ( Left small right big , Both sides are closed ), Equate to >=and <=
is null,is not null It's empty , Not empty ( Be careful null Out of commission = To measure )
and also ,and Statement priority is higher than or
or perhaps example :select name from table where name=‘ Zhang San ‘or ‘‘ Li Si ‘
not not It means taking the wrong , Can be in is ,in (in After that is the specific value , It's not an interval ) Use in
and ,or Priority comparison of

and Priority over or, If you want to execute first or have access to (), Uncertain priority , Pay attention to ()

example : Query salary greater than 5500, And it's numbered 10 or 20 The employees'

select  
     name
from  
    emp
where  sal>5500 and (deptno=10 or deptno=20)

--  here and  Priority over or , The use of parentheses can combine content , Thus, inner query is realized 
Fuzzy query

like Fuzzy query , Support % and _( Underline )

  • % Matches any character
  • _ Represents any character ( An underline takes a place )
  • If you want to like contain _ The content of , have access to \ Transference
-  Said to a All the characters at the beginning 
 Header   like ‘a%-  Said to a All the characters at the end 
 Header  like ‘%a‘

-  Indicates that the table contains _ All characters of characters 
 Header  like ‘%\_%-  The second letter is a All characters of 
  Header  like ‘_a%-  The last letter is a All characters of 
  Header  like ‘%a_‘

 

2、 Sort

order by Header

  • +desc( Specify descending order )/asc( Ascending , Default )
  • + Column number Such as 2
  • The order is always last
  • slect ... from ... where .....order by
--  Sorting multiple fields    When the fields are equal, they are sorted by the following 
select sal from  emp order by sal desc,ename asc;

--  understand   Column number sort 
select sal from emp order by 2

 

3. Data processing functions

  • Also known as single line processing function
  • The characteristics of a single line processing function : One input corresponds to one output
  • Multiline processing functions : Multiple lines of input correspond to one output
  • Common function select function( Header ) from table_a perhaps stay where In the condition
lower Convert lowercase
upper Convert a capital
substr Take the string (substr( Intercepted string , Start subscript ( Subscript from 1 Start ), Intercept length ))
length Take the length
trim Go to space
str_to_data Convert string to date
data_format Format date
format Set the thousandth
round rounding round( Header , Decimal places reserved )
rand() Generate random number
ifnull take null Convert to a specific value ; Because in all the data, as long as there is null The result of the mathematical operation involved is null; Such as :ifnull( Header ,0) If null As a value
case... when..then..when..then.. else.....end case Header when Conditions 1 then perform a when Conditions 2 then perform 2 else other end
-- substr application 
--  from  emp  To get to A At the beginning ename
select ename from emp where substr(ename,1,1)=‘A‘;

--  length application 
--  see emp in ename The character length of 
select length(ename) from emp ;


-- null  The final result of participating in data calculation must be null, To avoid this phenomenon , Need to use ifnull  function 
ifnull( Header ,0)  If the header is null  It is regarded as 0
select ename,sal+ifnull(comm,0)as salcomm from emp

--case ...when. then... when ... then..else... end  application 
--  When an employee's position is sd when , Pay rises 10%, When the job is ds when , Pay rises 20%, Other normal , Don't modify the database 
select ename,job,
(case job when ‘sd‘ then sal*1.1 when ‘ds‘ then sal*1.2 else sal end)
as newsal 
from 
 emp;

 

4. Group function ( Multiline processing functions )

  • You need to group first , Ability to use Multiple and group by Use together
  • Be careful
    • If there are no groups , By default, the whole table is a group
    • No need. null To deal with , Automatically ignore null
    • count(*) And count( Specific fields ) The difference between
      • count( Specific fields ) This field is counted ( This column ) All not for null The sum of the elements of
      • count(*) Here, press the line ( Because there is no such thing as null The line of ) Statistics , That is to count the number of head offices
    • __ You can't __ stay where Use in condition
    • All grouping functions can be used in combination
   
count Count
sum Sum up
avg The average
max Maximum
min minimum value
--  example 
select min(asl) from emp
select max(asl) from emp
select sum(asl) from emp
select avg(asl) from emp
select count(ename) from emp

--  count(*) 
select count(*) from emp;
--  How many lines are there , That is, the number of head office 

select count( Header ) from emp:
--  That is not to say null Number of rows for 

 

5. Group query

You need to start with Grouping , Then the data after grouping is operated

select  ...  from  ... group by  Header 

 

  • select Header a, Group function ( Header b) group by Header a;

    stay select In the sentence , If there is group by sentence ,select You can only follow : Fields that participate in grouping , And grouping functions ,

    Nothing else can follow (1. It makes no sense ,2. If in oracle Error reported in , By contrast mysql The grammar is loose )

  • group by Header 1, Header 2  Two fields are grouped together

--  application 
--  Find each department , The highest salary for different jobs 
--  skill   Two fields are grouped together 
select deptno,jon,max(sal) from emp group by deptno,job

 

  • Use having Filter the data after grouping

The efficiency is not high , Actually, you can start with where Then group them ,where and having priority of use having

select deptno,jon,max(sal) from emp group by deptno,job having sal>3000;
 perhaps 
select deptno,jon,max(sal) from emp where sal>3000 group by deptno,job ;

-- hvaing + Group function select deptno,jon,max(sal) from emp group by deptno,job having avg(sal)>3000

 

Add

Execution order

select ... from .. where .... group by ...order by..
 Execute first from , also  where , Next is group by  And then select  In the end I order by..

 Query data from a table ,
 Go first where  Conditions filter out valuable data ,
 Grouping these valuable data 
 After grouping, you can use having Keep screening 
select Find out 
 Finally sort the output 

 

mysql Basic introduction Single table query

Original address :https://www.cnblogs.com/yescarf/p/14092434.html

版权声明
本文为[TormJ]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201211122751197k.html

Scroll to Top