MySQL database-basic query, simple query, conditional query, sorting of query results, mysql query results

MySQL database-basic query, simple query, conditional query, sorting of query results, mysql query results

I. SELECT statements SELECT COL1, COL2,... COLn FROM TABLE1, TABLE2,... TABLEn

[Where conditions] -- Query CONDITIONS

[Group by GROUP_BY_LIST] -- Query Result GROUP

[Having conditions] -- Query condition-statistical result as condition

[Order by ORDER_LIST [ASC | DESC] -- Sort query results

 

Ii. Simple Query

1. query all rows and columns of a table

Eg: Query all rows and columns in the player table

Select user_qq, user_name, user_sex, user_birthday, user_mobile from users;

Select * from users;

2. query some columns of a table

Eg: query the player QQ and nickname from the player table

Select user_qq, user_name from users;

3. Alias usage

Eg: query the player QQ and nickname from the player table, and display them as 'Player qq' and 'Player nicknames'

Select user_qq as 'Player qq', user_name as 'Player nickname 'from users;

Select user_qq 'Player qq', user_name 'Player nickname 'from users;

4. DISTINCT keyword-eliminate duplicate rows in the result set

Eg: displays the QQ of players who have participated in the game. players who have participated in multiple games are required not to repeatedly display QQ

Select distinct user_qq from scores;

5. LIMIT keyword-specify the data display range in the result set

Eg: displays 3rd to 5th pieces of data in the player table

Select * from users limit 2, 3;

Select * from users limit 3 --- only display the first three data items

 

Iii. Conditional Query

1. Common condition Query

Syntax: SELECT COL_LIST FROM TABLE_NAME [WHERE CONDITION_EXPRESSION]

Eg1: Query Information about players whose QQ number is 12301.

Select * from users where user_qq = 12301;

Eg2: query data with scores greater than 2500

Select * from scores where score> 2500;

<> ----- Not equal to> = ----- greater than or equal to <= ----- less than or equal

Eg3: query the score information of a game with a game number of 1 and a score greater than 4000

Select * from scores where gno = 1 and score> 4000;

Logical OPERATOR: and -- and

Or -- or

Non -- not

Eg4: query the scores of games numbered 1 and 2.

Select * from scores where gno = 1 or gno = 2;

2. Fuzzy search

Eg1: Query scores between 2500 (inclusive) and 3000 (inclusive)

Select * from scores where score> = 2500 and score <= 3000;

Select * from scores where score between 2500 and 3000;

Eg2: query the scores between 2500 (inclusive) and 3000 (inclusive ).

Select * from scores where score not between 2500 and 3000;

Eg3: Query Players born from January 1-19, 1987 to July 31

Select * from users where user_birthday between '2017-01-01 'and '2017-100 ';

Wildcard: '_' one character Branch like 'l _'

% Route_Code Like 'ams-% 'of any length'

[] Airbusno Like 'ab0 [1-5] 'in the specified range

[^] Airbusno Like 'ab0 [^] 'is not in brackets

Eg4: Query Information of all players surnamed sun

Select * from users where user_name like 'Sun % ';

Eg5: Query Information of all players not surnamed sun

Select * from users where user_name not like 'Sun % ';

3. query null-worthy Operators

Eg: query the information of null players whose birthdays are empty.

Select * from users where use_birthday is null;

Eg: Query Information about players whose birthdays are not NULL

Select * from users where user_birthday is not null;

 

Sort query results

1. Sort specified columns (sort by and sort)

Syntax: SELECT CLO_LIST FROM TABLE_NAME order by ORDER_BY_LIST [ASC/DESC]

For example, query all the scores numbered 1 in the score table and sort the scores in ascending order.

Select * from scores where gno = 1 order by score asc.

For example, query all the scores numbered 1 in the score table and sort the scores in descending order.

Select * from score where gno = 1 order by score desc.

2. Sort multiple columns (sorting basis, sorting method, and priority)

For example, you can query all the information in the score table and sort the information by game number in ascending order and score in descending order.

Select * from scores order by gno asc, score desc