[Js-mysql] Function

Source: Internet
Author: User
Tags lowercase rand truncated

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.