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

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();

Add new comment

For adding a comment, please log in

Login or create account

0 comments