Continuing with our SQL proficiency test topic, today's topic is as follows:
SQL two: user table (including fields: user ID[self added), name, gender, nationality, date of birth, ID number.
Use an SQL statement to query:
The total number of users, the number of males, the number of women, the number of Han nationality, the number of ethnic minorities (non Han nationality), the number of people born in 1995, and the number of people without ID number.
First of all, it's not difficult to look at the topic, but there are still many details. First of all, we consider using that. WHEN we see that the total is calculated according to a certain condition, we think of using the form of CASE... WHEN... So we first use this method:
Before we start, we should first understand the simple search method of case... when... And the function search method:
SELECT COUNT(*) AS person_all_sum , CASE user_sex_code WHEN 1 THEN 'male' ELSE 'female' END CASE; FROM `t_user_info` -------------------------------------------------------------- SELECT CASE user_sex_code WHEN 1 THEN 'male' ELSE 'female' END FROM `t_user_info` -------------------------------------------------------------- SELECT CASE WHEN user_sex_code = 1 THEN 'male' WHEN user_sex_code = 2 THEN 'female' ELSE 'other' END FROM `t_user_info`
Link: case when then else end usage in SQL
.
The SQL written in combination with the above problems is as follows:
SELECT SUM( CASE WHEN user_sex_code = 1 THEN 1 ELSE 0 END ) AS man_sum, SUM( CASE WHEN user_sex_code = 2 THEN 1 ELSE 0 END ) AS woman_sum, SUM( CASE WHEN user_nation = 1 THEN 1 ELSE 0 END ) AS hannation_sum, SUM( CASE WHEN user_nation <> 1 THEN 1 ELSE 0 END ) AS unhannation_sum, SUM( CASE WHEN DATE_FORMAT( user_birthday, '%Y%' ) = 1995 THEN 1 ELSE 0 END ) AS birthday_sum, SUM( CASE WHEN user_idcard IS NULL OR user_idcard = '' THEN 1 ELSE 0 END ) AS unuser_idcard_sum, SUM( CASE WHEN user_idcard IS NOT NULL AND user_idcard <> '' THEN 1 ELSE 0 END ) AS user_idcard_sum FROM `t_user_info`
Here we will analyze the details:
1. First of all, we must use < > to make judgment, although it is used= Yes, but after testing, it is found that < > and are used in mysql= Yes, but not recognized in sqlserver! =, Therefore, it is recommended to use < >
Only < > is the standard sql syntax and can be transplanted. Others are the characteristics of the oracle platform and have poor portability. Therefore, in development, using < > as much as possible does not mean that only < > is the standard sql syntax and can be transplanted. Others are the characteristics of the oracle platform and have poor portability. Therefore, in development, using < > as much as possible does not mean that.
2. Use isnull to judge whether it is empty: only when name is null, the return value of ISNULL(exp) function is 1, and the empty string and existing data are 0;
sql statements filtered to is null can also be used as follows (null and empty strings are excluded at the same time):
SUM( CASE WHEN ( ISNULL( user_idcard )= 1 ) || ( LENGTH( trim( user_idcard ))= 0 ) THEN 1 ELSE 0 END ) AS emptyidcord_sum,-- Number of people obtaining empty ID cards
3. As long as the logical conditions representing the true value are given in SUM, we can change the above SQL statement to the following:
select 100=100;
select 100=200;
select DATE(NOW())='2021-08-13';
select DATE(NOW())='2021-10-30';
SELECT SUM( user_sex_code = 1 ) AS man_sum, SUM( user_sex_code = 2 ) AS woman_sum, SUM( user_nation = 1 ) AS hannation_sum, SUM( user_nation <> 1) AS unhannation_sum, SUM( DATE_FORMAT( user_birthday, '%Y%' ) = 1995 ) AS birthday_sum, SUM( user_idcard IS NULL OR user_idcard = '' ) AS unuser_idcard_sum, SUM( user_idcard IS NOT NULL AND user_idcard <> '' ) AS user_idcard_sum FROM `t_user_info`
In fact, we also need to optimize here. We add a common index to birthday, take a look, and then execute different sql for comparison. For the knowledge of MySql index, see the following: MySql index: MySql index knowledge learning
Insert picture description here
We perform the following:
EXPLAIN SELECT * FROM t_user_info
And
EXPLAIN SELECT * FROM t_user_info WHERE user_birthday LIKE "1995%"
Two SQL statements are generally not encouraged to use like. If not, it should be noted that like"%aaa%" will not use indexes; But like "aaa%" uses indexes. The following two statements can be used for comparison:
EXPLAIN SELECT * FROM t_user_info WHERE user_birthday LIKE "1995%" EXPLAIN SELECT * FROM t_user_info WHERE user_birthday >= '1995-01-01 00:00:00'AND user_birthday <= '1995-12-31 23:59:59'
Note: the function will not be used when it can not be used, because the function will invalidate the index
-- God level thin mode EXPLAIN SELECT count(*) AS user_all_num,-- Get total number of people SUM( user_sex = '2') AS woman_sum,-- Number of girls obtained SUM( user_sex = '1') AS man_sum,-- Number of boys obtained SUM( user_nation = '1' ) AS hannation_sum,-- Obtain the number of Han nationality SUM( user_nation <> '1' ) AS unhannation_sum,-- Obtain non Han population SUM( user_birthday >= '1995-01-01 00:00:00' AND user_birthday <= '1995-12-31 23:59:59' ) AS birt_sum,-- Obtain the number of people whose birth date is 1995 SUM( user_idcard is null OR user_idcard = '' ) AS emptyidcord_sum,-- Number of people obtaining empty ID cards SUM( user_idcard <> '' ) AS notemptyidcord_sum-- Number of people obtaining non empty ID cards FROM `t_user_info`
Welcome interested partners to discuss and learn knowledge together. The above is some personal summary and sharing. If there are mistakes, please leave a message and point them out. Thank you very much.
If you find it useful, don't forget to praise, collect, pay attention and leave fragrance in your hand! 😗 😗 😗