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! 😗 😗 😗