Day2 Sorting Query & common function query and case explanation (unfinished)

preface

        I studied for three hours this afternoon and didn't finish reading the complete content of the next day, but I feel a lot of knowledge. Put it on the blog. It may increase or open a new post tomorrow.

text

1. Sort query

*Syntax:
    select                        (3)
        Query list
    from                        (1)
        surface
    where                         (2)
        condition
    order by sorted field | expression | function | alias [asc|desc]                ( 4)
       

         Execution sequence: as above.

*Features:

        1.asc represents ascending order and desc represents descending order.

        2. Single field, multiple fields, expressions, functions and aliases can be supported in the order by clause

        3. The word order by is usually placed at the end of the query statement, except the word limit.

*Note:
    * If there are multiple sorting conditions, the second condition will be judged only when the previous condition values are the same.

Case:

#Case 1: query employee information and require the salary to be sorted from high to low
SELECT * FROM employees  ORDER BY salary DESC;

#Case 2: query the employee information with department No. > = 90, and sort by employment time [add filter criteria]
SELECT * FROM employees WHERE department_id >=90 ORDER BY hiredate;

#Case 3: display employee information and annual salary by annual salary level [sort by expression] (can be by alias)

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) Annual salary FROM employees ORDER BY Annual salary;

#Case 4: display employee name and salary by name length [sort by function]
SELECT last_name ,salary FROM employees ORDER BY LENGTH(last_name) DESC;

#Case 5: when querying employee information, it is required to sort by salary in descending order and then by employee number in ascending order [sort by multiple fields]
SELECT * FROM employees ORDER BY salary DESC ,employee_id ASC;

2. Common functions

Function: similar to java method, it encapsulates a group of logical statements in the method body and exposes the method name.
Benefits: 1. Hide the implementation details.     2. Improve code reusability.
Call: SELECT function name (argument list)   [ FROM table];
characteristic:
             ① What is it called (function name)   ②   What to do (function)
Classification:
             1, Single line function
             Such as concat, length, ifnull, etc
            
            
                     Single line function classification:
                                             Character function
                                             Mathematical function
                                             Date function
                                             Other functions [ supplement ]
                                             Process control function [ supplement ]
            
            
             2, Grouping function
             Function: used for statistics, also known as statistical function and aggregate function

1. Character function
     concat: splicing
     substr: intercept substring
     upper: convert to uppercase
     lower: convert to lowercase
     trim: remove the specified spaces and characters before and after
     ltrim: go to the left space
     rtrim: go to the right space
     Replace: replace
     lpad: left fill
     rpad: right fill
     instr: returns the index of the first occurrence of a substring
     length: get the number of bytes     

Case:

#length gets the number of bytes of the parameter value

SELECT LENGTH('john');
SELECT LENGTH('Zhang Sanfeng hahaha');

SHOW VARIABLES LIKE '%char%';


#2.CONCAT(str1,str2,...) splice string

SELECT CONCAT(last_name,'_',first_name)  full name FROM employees;

#3.upper, lower;
SELECT UPPER('john');
SELECT LOWER('WQas');

#Example: change the last name to uppercase, the first name to lowercase, and then splice

SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) FROM employees;


#4.SUBSTR(str FROM pos FOR len) ,subString
#  The str string is intercepted from pos to len (character length)  
#Note: the index starts at 1

SELECT SUBSTR('Li Mochou fell in love with Lu Zhanyuan',4) out_put;

#Intercept the str string from pos until len (character length) intercepts five characters
SELECT SUBSTR('Li Mochou fell in love with Lu Zhanyuan',4,5) out_put;

#Case: the first character in the name is capitalized, the other characters are lowercase, and then spliced with an underline
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) FROM employees;

#5.INSTR(str,substr)
#Used to return the first index of the substring in the string
SELECT INSTR("Yang Bu regretted falling in love with Yin Liuxia",'Yin Liuxia');


#6.TRIM([remstr FROM] str)

#Remove the characters at the beginning and end of the string
SELECT LTRIM('    Zhang Cuishan    ');

SELECT TRIM('a' FROM 'aaaaaaaa Zhang aaaaaaaaaaaaa Emerald aaaaa mountain aaa');


#7. lpad uses the specified characters to fill the specified length left. The final length is the same as that of the parameter
 SELECT LPAD('Yin Su Su',10,'*');

#8. RPAD(str,len,padstr)
SELECT  RPAD('Yin Su Su',10,'1');

#9. REPLACE

SELECT REPLACE('Zhang Wuji fell in love with Zhou Zhiruo','Zhou Zhiruo','Zhao Min');


-------------------------------------------------------------------------
2. Mathematical function
     Round: round
     rand: random number
     floor: round down
     ceil: round up
     mod: residual
     Truncate: truncate

#round   
#Round the absolute value and take the sign
SELECT ROUND(-1.55);

#Keep two digits after the decimal point
SELECT ROUND(1.567,2);

#ceil rounding up returns > = the smallest integer of the parameter
SELECT CEIL(1.002);

SELECT CEIL(-1.002);


#FLOOR(X) rounds down and returns < = the maximum integer of the parameter
SELECT FLOOR(-9.99);


#TRUNCATE whatever comes after truncation

SELECT TRUNCATE(1.65,1);

#mod remainder

SELECT MOD(-10,-3);

SELECT 10%3;


-------------------------------------------------------------------------
3. Date function
     now: current system date + time
     Current date: current system date
     curtime: current system time
     str_to_date: converts characters to dates
     date_format: convert date to character

#now returns the current system date + time

#STR_TO_DATE(str,format): converts characters in date format to a date in the specified format

#DATE_FORMAT(date,format) converts the date into a string
SELECT NOW();

#Current date returns the current system date, excluding time

SELECT CURDATE();

#curtime returns the current time, excluding the date
SELECT CURTIME();

#Can get the specified part. Year, month, day, hour, minute, second
SELECT YEAR(NOW());
SELECT YEAR(hiredate) FROM employees;

SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT MINUTE(NOW());

#STR_TO_DATE(str,format) converts characters into dates in a specified format
SELECT STR_TO_DATE('2000-02-6','%Y-%c-%d');

#Query the employee information whose employment date is April 3, 1992

SELECT * FROM employees WHERE hiredate ='1992-4-3';

SELECT * FROM employees WHERE hiredate =STR_TO_DATE('3-4-1992','%d-%c-%Y');


#DATE_FORMAT(date,format) converts a date into characters
SELECT DATE_FORMAT(NOW(),'%y-%m-%d');

#Query the name and employment date of the employee with bonus (xx month / xx day, xx year)
SELECT last_name,DATE_FORMAT(hiredate,'%m month%d day %y year') FROM employees WHERE commission_pct IS NOT NULL;


-------------------------------------------------------------------------
4. Process control function
     if handle double branch
     case statements handle multiple branches
         Case 1: Processing equivalence judgment
         Case 2: judgment of processing conditions

#1.if function: effect of if else
SELECT IF(10<5,'large','Small');


SELECT  last_name ,commission_pct ,IF(commission_pct is NULL,'No bonus, hehe','There is a bonus. Hee hee')   FROM employees;


#Use of case function I: effect of switch case

/*

mysql in  
case Field or expression to judge
WHEN Constant - value 1 or statement 1 to be displayed by THEN;
WHEN Constant 2: value 2 or statement 2 to be displayed by THEN;
....
ELSE The value n or statement n to be realistic;
end;
*/


/*Case: query employee's salary. Requirements:
Department number = 30, the displayed salary is 1.1 times
 Department number = 40. The displayed salary is 1.2 times
 The department number is 50 and the actual salary is 1.3 times
 For other departments, the displayed salary is the original salary
*/
SELECT  salary,department_id,  CASE department_id
	WHEN  30 THEN  salary*1.1
	WHEN  40 then  salary*1.2
	WHEN  50  then  salary*1.3
	ELSE   salary
END  as 'New salary' FROM employees;


SELECT salary 'Original salary'  ,department_id ,
CASE department_id
	WHEN 30 THEN
		salary *1.1
		WHEN 40 THEN  
		salary*1.2
		WHEN 50 THEN
		 salary*1.3
	ELSE
		salary
END New salary  FROM employees;


/*3.case Use of function 2: similar to multiple if
case
WHEN Condition 1 THEN value 1 or statement 1 to display
WHEN Condition 2 THEN value 2 or statement 2 to be realistic
...
ELSE Value n or statement n to display
END
*/


#Case: query employee's salary

/*If salary > 20000, level A is displayed
>15000,B
>10000,C
 Otherwise, D

*/

SELECT salary,CASE 
	WHEN salary>20000 THEN
		'A'
		WHEN salary>15000 THEN 'B'
		WHEN salary>10000 THEN 'C'
	ELSE
		'D'
END  Wage scale FROM employees;


-------------------------------------------------------------------------
5. Other functions
     Version: version
     Database: current database
     User: currently connected user

#Version number
SELECT VERSION();   

#View the database currently in use
SELECT DATABASE();

#View current user
SELECT USER();

Tags: Database SQL

Posted on Thu, 02 Sep 2021 22:17:48 -0400 by rotto