Database Skill Combat Advanced Common structured SQL statements (middle)

In the previous article we covered the query for order by sorting the results of the query, and then we'll cover some of the other operations.

10, Limit limits query result bar number

In the MySQL database we want to display the first 10 rows, or the line x to n rows and other formats display, then limit will be our best choice.

select  * from  user limit 5;  #显示前5行1     kailinux     javadocker2    LInuxmysql    NULL3     python    null4    linux    redis5     linux    0 if we want to see the last 3 lines, but limit does not support reverse, we can use the previous way to achieve select    * from  user limit 21,3;26    redispyhon     27    PYthon234    28    winner1234      showing 5 select   * from  user limit 3,5;4  starting from 3    linux    redis5    linux    06     redis    name 7    kailinux     javadocker8 &Nbsp;  linux    redis 

11. mysql Common function

Connection function concat ()

Syntax: CONCAT (STR1,STR2,...) can be used to stitch query results,

Select CONCAT (Username, ' * * * * *, password) ' from user #将user表的username和password列用4个 * * * * * *. Results: kailinux*****javadockerlinuxmysql*****nullpython*****nulllinux*****redislinux*****0 with underline or horizontal stitching kailinux_ Javadockerlinuxmysql_nullpython_nulllinux_rediskailinux-javadockerlinuxmysql-nullpython-nullselect CONCAT ( Username, ", password) from user #不指定间隔直接拼接两列kailinuxjavadockerLInuxmysqlNULLpythonNULLLInuxredis thus can be seen concat () Functions can be used to specify any connector when stitching, to achieve different results of data query display.

Random function rand ()

This random function, can be in the query results, such as random sort of processing

Select   *  from user  order by rand ();5     linux    08    LInux    redis15     python    javadocker16    linux    2     LInuxmysql    NULL7    kailinux     javadocker28    winner1234    20     centos    17    linux    9996     redis    name 3    python    null26     redispyhon    22    rediswinner     25    pythoredis    27    python234      This random number can beTo be used for sweepstakes and the like, we'll get a random sort of 3 more rows, Select   *  from user  order by rand ( )  limit 3;23    PYthonmysql    27     Python234    4    linux    redis

Number of Statistics columns count ()

Select COUNT (*) from user can query out how many data are contained in a table, etc., where the column selection is usually the primary key related columns.

Select COUNT (*) from user, 24select count (id) from user; But this is slower when the data is large.

summation function sum ()

Select SUM (ID) from user; #对id求和356

Max ()

Select Max (ID) from user; 28

Find Minimum function ()

Select min (id) from user; 1

Avg function avg ()

Select AVG (ID) from user; 14.8333

Grouping function GROUP BY

Select Username,count (*) from the user GROUP by username;    #按用户名分组, you can count the same user information CentOS 1kailinux 3LInux 5LInuxmysql 1python 2python234 1PYTHonmysql 2PYTHoREDIS 1redis 3redispyhon 1rediswinner 1ubuntu 1winner1234 1winnerredis 1

For more information on multi-table queries, see the previous article on MySQL.

Database Skill Combat Advanced Common structured SQL statements (middle)