## 编程知识 cdmana.com

### Single table query

#### 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

-  Said to a All the characters at the end

-  Indicates that the table contains _ All characters of characters

-  The second letter is a All characters of

-  The last letter is a All characters of

#### 2、 Sort

• +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;```

• ###### mysql Table mapping for

select ‘sda ‘/ 1000 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 `

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```

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