[Js-mysql] Function

Data processing functions/single-line processing functions

• Lower Convert lowercase
• Upper Convert Uppercase
• SUBSTR substring (substr (truncated string, starting subscript, truncated length))
• Length Take Lengths
• Trim away Spaces
• 35/103
• Str_to_date Convert a string to a date
• Date_format formatted date
• Format set thousand bits
• Round rounding
• RAND () generates random numbers
• Ifnull can convert null to a specific value

1 Select Lower(ename) fromEmp--Search for employees, change employee names to lowercase2 3 Select *  fromEmpwhereJob=Upper('Manager');--to query the job manager's employees4 5 Select *  fromEmpwhereSUBSTR (ename,1,1)=Upper('m');--query names start with M all employees6 7 SelectEname fromEmpwhereLength (ename)=5    --get the employee name length of 58 9 Select *  fromEmpwhereJob=TrimUpper('Manager'));--get jobs for all manager employees

1 /*2 two ways to query time in an SQL statement3 */4 Select *  fromEmpwhereHireDate='1981-02-20';--query employees who are employed in 1981-02-20 (the first method, which matches the format of the database)5 Select *  fromEmpwhereHireDate=Str_to_date ('1981-02-20','%y-%m-%d');--query employees who are in 1981-02-20 (the second method converts a string to a date type)6 Select *  fromEmpwhereHireDate=Str_to_date ('02-20-1981','%m-%d-%y');--str_to_date can convert string to date, format str_to_date (string, match format)

1 SelectEmpno, ename, Date_format (HireDate,'%y-%m-%d%h:%i:%s') asHireDate fromEmp--for employees who have entered the post after 1981-02-20, format the entry date into YYYY-MM-DD hh:mm:ss2 SelectDate_format (now (),'%y-%m-%d%H%i%s');-- Get and format current time now () Get current time
Description of the date format%Y: Represents a 4-bit year%Y: Represents a 2-bit year%m: represents the month, in the format (01 ... %c: Represents the month in the format (1 ...). %H: Represents the hour, in the format (00 ...). %h: Represents the hour in the format of (01 ...). %i: Represents minutes in the format of (00 ... %r: Represents the time, in the format of 12 hours (Hh:mm:ss [ap]m)%T: Represents the time, in the format of 24 hours (HH:MM:SS)%s: represents seconds, in the format (00 ... ()%s: represents seconds in the format (00 ...). () 

1 SelectEmpno, ename, Format (Sal,0) fromEmp--Find employee salaries and join thousands of people2 SelectEmpno, ename, Format (Sal,2) fromEmp--Query Employee Salaries add thousands of decimal places and retain two decimals3 4 Select round(123.56);--Rounding 1245 Select round(123.56,1);--123.66 Select round(123.56,-1);-- -7 8 Select Rand();--generate random number 0.26058804268072189 Select *  fromEmpOrder  by Rand() limit2;--the number of randomly extracted records order by must be written onTen  One Select  A empno, ename, Job, Sal, -      CaseJob -      when 'MANAGER'  ThenSal*1.1  the      when 'salesman'  ThenSal*1.5  -     End  asNewsal -  fromEmp--If job is Managerg salary up 10%, if job is salesman wage rises 50% -  + Select  -E.*, Sal, +      CaseJob A      when 'salesman'  ThenSal*1.1 at      when 'Clerk'  ThenSal*1.2 -     ElseSal -     End  asNew_sal -  fromEMP e;--Other wages do not move, need to add else -  - SelectIfnull (Comm,0) fromEMP; in /* - If comm is null, replace with 0 to If there is a null value in the SQL statement to participate in the mathematical operation, the calculation result must be null + to prevent NULL in the calculation results, it is recommended to pre-process using the Ifnull null handler function.  - */ the --The following SQL is calculated for the yearly salary: * SelectEmpno,ename,sal, (Sal+Ifnull (Comm,0))* A  asYearsal fromEMP; $ Panax NotoginsengData processing functions are also called single-line processing functions, features: input one line of output

grouping functions/aggregate functions/multirow processing functions

• Count gets the number of records
• Sum sum
• 42/103
• AVG Averaging
• Max takes the maximum number
• Min takes the smallest number

Note: The grouping function automatically ignores null values and does not require a manual add where condition to exclude null values.

Select COUNT (*) from emp where xxx; The total number of records that meet the criteria.

Select COUNT (comm) from EMP; Comm The total number of elements in this field that are not empty.

Note: Grouping functions cannot be used directly after the WHERE keyword.

Mysql> Select Ename,sal from emp where sal > Avg (SAL);

ERROR 1111 (HY000): Invalid use of group function

Select Count(*) fromEmp--gets all the number of employees, COUNT (*) indicates that all records are obtained, ignores NULL, and the null value is also obtainedSelect Count(distinctJob fromEmp--number of jobs acquiredSelect Count(comm) fromEmp--acquisition allowance is not a null number of employees, with count (field name), which does not get a record of NULL--Sum can be obtained for a column, and NULL is ignoredSelect sum(SAL) fromEmp--the total of the earned salarySelect sum(comm) fromEmp--total of the grant, NULL will be ignoredSelect sum(Sal+Comm fromEmp/*get the total salary (Sal+comm), the result is incorrect, because the Comm field has a null value, so can not be calculated, sum will be ignored, the correct way is to convert the Comm field to 0, the correct wording is as follows*/Select sum(Sal+Ifnull (Comm,0)) fromEMP;Select avg(SAL) fromEmp--Get average salarySelect Max(SAL) fromEmp--get the highest salarySelect Max(Str_to_date (HireDate,'%y-%m-%d')) fromEmp--get the latest entry staffSelect min(SAL) fromEmp--get a minimum salarySelect min(Str_to_date (HireDate,'%y-%m-%d')) fromEmp--to get the earliest recruits--combining aggregate FunctionsSelect Count(*),sum(SAL),avg(SAL),Max(SAL),min(SAL) fromEmp

Lower Convert lowercase upper convert uppercase substr substring (substr (truncated string, starting subscript, truncated length) length trim Remove space 35/103str_to_date convert string to date date_format format Date format set round rounding rand () generates a random number ifnull can convert null to a specific value

[Js-mysql] Function