MySQL foundation advancement

Common functions:
Character functions:
Length field character length
Concat connection substring
Substr intercept Substring
Instr Insert substring
Remove both ends of trim
Upper to uppercase
Lower to lowercase

< div> lpad left fill

rpad right fill
replace
Numerical function:
round
ceil round up
floor and round down
mod mod
truncate truncated
rand Return a random decimal between 0-1
Date function:
Now return the current date + time
Curdate Return the current date< /div>

Curtime Return the current time
Year
Month
Monthname Return the month in English
Day
hour
minute
second
date_format convert date into characters
other functions :
version current data server version
database The currently open database
user current user
md5(‘character’) returns the md5 encrypted form of the character
control Function:
1)if
If (conditional expression, expression 1, expression 2) If the conditional expression is true, return expression 1, otherwise return 2
select dept_id,
sum(if (sex=’男’,1,0)) male count,
sum(if (sex=’女’,1 ,0)) female count,
from emp_sex
group by dept_id;
2)case
Select dept_id,
sum(case sex when’male’ then 1 else 0 end) male count,
sum(case sex when’女’ then 1 else 0 end) female count,
from emp_sex
group by dept_id;
grouping function:
sum,avg, max,min,count
1. sum, avg are generally used to handle numeric types, max min count can handle any type
2. The above grouping functions ignore null values
3. Can be combined with distinct to achieve deduplication operation
group by group query:
1. group query The filtering conditions in the inquiry are divided into two categories:
Filter before grouping: Operation data source
Filter after grouping: Result set after grouping operation
1) The grouping function must be placed in the having clause as a condition
2). If filtering before grouping can be used, priority is given to filtering before grouping.
2. group by can write multiple fields Grouping, there is no order relationship before and after
3. If there is a sort, it will be placed at the end of the entire grouping query
SELECT * FROM sc GROUP BY sid; after grouping Specify the aggregation function, and the system selects to display the first data of sid by default
mysql basic syntax:
select the grouping function, the grouped field step5
div>

from Table 1 Alias ​​1, Table 2 Alias ​​2 step1
where filter conditions step2
group by grouped field step3
having grouped Filtering step4
order by sorting list step6
sql92 syntax:
(non-)equivalent connection:
1. The result of multi-table equivalence connection is the intersection of multiple tables
2. n-table connection, at least n-1 connection conditions are required
3. multi-table There is no requirement for the order of the table 4. Generally, it is necessary to create an alias for the table (the original name of the table cannot be written in the select after the alias is created)
5. It can be combined with all the clauses introduced above
/div>

Self-connection:
1. Consider a table as two tables, and use different aliases to query the data in the same table
Syntax:
select alias 1. field, alias 2. field
from table A alias 1, table A alias 2
where alias 1. field=alias 2. field
sql99 syntax:
syntax:
select query list
from Table 1 alias [connection type]

< div> join table 2 alias

on connection conditions
[where filter conditions]
[group by grouping]
[having filter Condition]
[order by [sort list]
Connection type classification:
Inner connection: inner

< div> Outer connection:

Left outer: left
Right outer: right
All outer: full
Cross connection: cross < /div>

Common function:

Character function:

Length field character length

Concat connection substring

< p> substr intercept substring

instr insert substring

trim both ends to empty

upper to uppercase

lower to lowercase< /p>

Lpad left padding

Rpad right padding

Replace

Numerical function:

< p> round to the nearest integer

ceil to round up

floor to round down

mod modulus

truncate truncated

rand returns 0 Random decimal between -1

Date function:

now return current date+time

curdate return current date

curtime returns the current time

year

month

monthname returns the month in English

day

< p> hour

minute

second

date_format converts the date into characters

other functions:< /p>

version current data server version

database currently open database

user current user

md5(‘character’) Return the character md5 encryption form

Control function:

1)if

if (conditional expression, expression 1, expression 2 ) If the conditional expression is true, return expression 1, otherwise return 2

select dept_id,

sum(if (sex=’男’,1,0)) male count,

sum(if (sex=’女’,1,0)) female count,

from emp_sex

group by dept_id;

< p>

2)case

select dept_id,

sum(case sex when’male’ then 1 else 0 end) male count,

sum(case sex when’女’ then 1 else 0 end) female count,

from emp_sex

group by dept_id;

Grouping function:

sum,avg,max,min,count

1.sum,avg are generally used to process numeric types, max min count can handle any Type

2. The above grouping functions ignore null values

3. Can be combined with distinct to achieve deduplication operation

group by Group query:

1. The filter conditions in the group query are divided into two categories:

Filter before grouping: Operation data source

Filter after grouping: Operation group After the result set

1). The grouping function must be placed in the having clause as a condition

2). The filtering before the grouping can be used, and the filtering before the grouping is preferred.

2. group by can write multiple field groupings, there is no order relationship between the front and back

3. If there is a sort, it will be placed at the end of the entire group query

< p> SELECT * FROM sc GROUP BY sid; After grouping, no aggregation function is specified, and the system selects to display the first data of sid by default

mysql basic syntax:

< p> Select the grouping function, the field after grouping step5

From Table 1 Alias ​​1, Table 2 Alias ​​2 step1

Where filter conditions step2

Group by group Field step3

having filtering after grouping step4

order by sorting list step6

sql92 syntax:

< p>(Non) equivalence connection:

1. The result of multi-table equivalence connection is the intersection of multiple tables

2. n-table connection, at least n-1 Connection conditions

3. The order of multiple tables is not required 4. Generally, it is necessary to alias the table (the original name of the table cannot be written in the select after the alias is created)

5. It can be combined with the front All the clauses introduced

Self-connection:

1. Look at a table as two tables, and use different aliases to query the same table. Data

Syntax:

select alias 1. field, alias 2. field

from table A alias 1, table A alias 2

Where alias 1. field= alias 2. field

sql99 syntax:

Syntax:

select query list

from table 1 alias [connection type]

join table 2 alias

on connection Conditions

[where filter conditions]

[group by grouping]

[having filter conditions]

[order by [order list] ]

Connection type classification:

Inner connection: inner

Outer connection:

Left outer: left

Right outer: right

All outer: full

Cross connection: cross

WordPress database error: [Table 'yf99682.wp_s6mz6tyggq_comments' doesn't exist]
SELECT SQL_CALC_FOUND_ROWS wp_s6mz6tyggq_comments.comment_ID FROM wp_s6mz6tyggq_comments WHERE ( comment_approved = '1' ) AND comment_post_ID = 2965 ORDER BY wp_s6mz6tyggq_comments.comment_date_gmt ASC, wp_s6mz6tyggq_comments.comment_ID ASC

Leave a Comment

Your email address will not be published.