ABS()Return the absolute valueACOS()Return the arc cosineADDDATE()Add time values (intervals) to a date valueADDTIME()Add timeAES_DECRYPT()Decrypt using AESAES_ENCRYPT()Encrypt using AESAND, &&Logical ANDANY_VALUE()Suppress ONLY_FULL_GROUP_BY value rejectionASCII()Return numeric value of left-most characterASIN()Return the arc sine=Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement):=Assign a valueASYMMETRIC_DECRYPT()Decrypt ciphertext using private or public keyASYMMETRIC_DERIVE()Derive symmetric key from asymmetric keysASYMMETRIC_ENCRYPT()Encrypt cleartext using private or public keyASYMMETRIC_SIGN()Generate signature from digestASYMMETRIC_VERIFY()Verify that signature matches digestATAN()Return the arc tangentATAN2(), ATAN()Return the arc tangent of the two argumentsAVG()Return the average value of the argumentBENCHMARK()Repeatedly execute an expressionBETWEEN … AND …Whether a value is within a range of valuesBIN()Return a string containing binary representation of a numberBIN_TO_UUID()Convert binary UUID to stringBINARYCast a string to a binary stringBIT_AND()Return bitwise ANDBIT_COUNT()Return the number of bits that are setBIT_LENGTH()Return length of argument in bitsBIT_OR()Return bitwise ORBIT_XOR()Return bitwise XOR&Bitwise AND~Bitwise inversionBitwise OR^Bitwise XORCAN_ACCESS_COLUMN()Internal use onlyCAN_ACCESS_DATABASE()Internal use onlyCAN_ACCESS_TABLE()Internal use onlyCAN_ACCESS_VIEW()Internal use onlyCASECase operatorCAST()Cast a value as a certain typeCEIL()Return the smallest integer value not less than the argumentCEILING()Return the smallest integer value not less than the argumentCHAR()Return the character for each integer passedCHAR_LENGTH()Return number of characters in argumentCHARACTER_LENGTH()Synonym for CHAR_LENGTH()CHARSET()Return the character set of the argumentCOALESCE()Return the first non-NULL argumentCOERCIBILITY()Return the collation coercibility value of the string argumentCOLLATION()Return the collation of the string argumentCOMPRESS()Return result as a binary stringCONCAT()Return concatenated stringCONCAT_WS()Return concatenate with separatorCONNECTION_ID()Return the connection ID (thread ID) for the connectionCONV()Convert numbers between different number basesCONVERT()Cast a value as a certain typeCONVERT_TZ()Convert from one time zone to anotherCOS()Return the cosineCOT()Return the cotangentCOUNT()Return a count of the number of rows returnedCOUNT(DISTINCT)Return the count of a number of different valuesCRC32()Compute a cyclic redundancy check valueCREATE_ASYMMETRIC_PRIV_KEY()Create private keyCREATE_ASYMMETRIC_PUB_KEY()Create public keyCREATE_DH_PARAMETERS()Generate shared DH secretCREATE_DIGEST()Generate digest from stringCUME_DIST()Cumulative distribution valueCURDATE()Return the current dateCURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()CURRENT_ROLE()Return the current active rolesCURRENT_TIME(), CURRENT_TIMESynonyms for CURTIME()CURRENT_TIMESTAMP(), CURRENT_TIMESTAMPSynonyms for NOW()CURRENT_USER(), CURRENT_USERThe authenticated user name and host nameCURTIME()Return the current timeDATABASE()Return the default (current) database nameDATE()Extract the date part of a date or datetime expressionDATE_ADD()Add time values (intervals) to a date valueDATE_FORMAT()Format date as specifiedDATE_SUB()Subtract a time value (interval) from a dateDATEDIFF()Subtract two datesDAY()Synonym for DAYOFMONTH()DAYNAME()Return the name of the weekdayDAYOFMONTH()Return the day of the month (0-31)DAYOFWEEK()Return the weekday index of the argumentDAYOFYEAR()Return the day of the year (1-366)DECODE()Decode a string encrypted using ENCODE()DEFAULT()Return the default value for a table columnDEGREES()Convert radians to degreesDENSE_RANK()Rank of current row within its partition, without gapsDES_DECRYPT()Decrypt a stringDES_ENCRYPT()Encrypt a stringDIVInteger divisionDivision operatorELT()Return string at index numberENCODE()Encode a stringENCRYPT()Encrypt a string=Equal operator<=>NULL-safe equal to operatorEXP()Raise to the power ofEXPORT_SET()Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off stringEXTRACT()Extract part of a dateExtractValue()Extract a value from an XML string using XPath notationFIELD()Index (position) of first argument in subsequent argumentsFIND_IN_SET()Index (position) of first argument within second argumentFIRST_VALUE()Value of argument from first row of window frameFLOOR()Return the largest integer value not greater than the argumentFORMAT()Return a number formatted to specified number of decimal placesFORMAT_BYTES()Convert byte count to value with unitsFORMAT_PICO_TIME()Convert time in picoseconds to value with unitsFOUND_ROWS()For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clauseFROM_BASE64()Decode base64 encoded string and return resultFROM_DAYS()Convert a day number to a dateFROM_UNIXTIME()Format Unix timestamp as a dateGeomCollection()Construct geometry collection from geometriesGeometryCollection()Construct geometry collection from geometriesGET_DD_COLUMN_PRIVILEGES()Internal use onlyGET_DD_CREATE_OPTIONS()Internal use onlyGET_DD_INDEX_SUB_PART_LENGTH()Internal use onlyGET_FORMAT()Return a date format stringGET_LOCK()Get a named lock>Greater than operator>=Greater than or equal operatorGREATEST()Return the largest argumentGROUP_CONCAT()Return a concatenated stringGROUPING()Distinguish super-aggregate ROLLUP rows from regular rowsGTID_SUBSET()Return true if all GTIDs in subset are also in set; otherwise false.GTID_SUBTRACT()Return all GTIDs in set that are not in subset.HEX()Hexadecimal representation of decimal or string valueHOUR()Extract the hourICU_VERSION()ICU library versionIF()IfIFNULL()Null ifIN()Whether a value is within a set of valuesINET_ATON()Return the numeric value of an IP addressINET_NTOA()Return the IP address from a numeric valueINET6_ATON()Return the numeric value of an IPv6 addressINET6_NTOA()Return the IPv6 address from a numeric valueINSERT()Insert substring at specified position up to specified number of charactersINSTR()Return the index of the first ourrence of substringINTERNAL_AUTO_INCREMENT()Internal use onlyINTERNAL_AVG_ROW_LENGTH()Internal use onlyINTERNAL_CHECK_TIME()Internal use onlyINTERNAL_CHECKSUM()Internal use onlyINTERNAL_DATA_FREE()Internal use onlyINTERNAL_DATA_LENGTH()Internal use onlyINTERNAL_DD_CHAR_LENGTH()Internal use onlyINTERNAL_GET_COMMENT_OR_ERROR()Internal use onlyINTERNAL_GET_ENABLED_ROLE_JSON()Internal use onlyINTERNAL_GET_HOSTNAME()Internal use onlyINTERNAL_GET_USERNAME()Internal use onlyINTERNAL_GET_VIEW_WARNING_OR_ERROR()Internal use onlyINTERNAL_INDEX_COLUMN_CARDINALITY()Internal use onlyINTERNAL_INDEX_LENGTH()Internal use onlyINTERNAL_IS_ENABLED_ROLE()Internal use onlyINTERNAL_IS_MANDATORY_ROLE()Internal use onlyINTERNAL_KEYS_DISABLED()Internal use onlyINTERNAL_MAX_DATA_LENGTH()Internal use onlyINTERNAL_TABLE_ROWS()Internal use onlyINTERNAL_UPDATE_TIME()Internal use onlyINTERVAL()Return the index of the argument that is less than the first argumentISTest a value against a booleanIS_FREE_LOCK()Whether the named lock is freeIS_IPV4()Whether argument is an IPv4 addressIS_IPV4_COMPAT()Whether argument is an IPv4-patible addressIS_IPV4_MAPPED()Whether argument is an IPv4-mapped addressIS_IPV6()Whether argument is an IPv6 addressIS NOTTest a value against a booleanIS NOTNULL NOT NULL value testIS NULLNULL value testIS_USED_LOCK()Whether the named lock is in use; return connection identifier if trueIS_UUID()Whether argument is a valid UUIDISNULL()Test whether the argument is NULLJSON_ARRAY()Create JSON arrayJSON_ARRAY_APPEND()Append data to JSON documentJSON_ARRAY_INSERT()Insert into JSON arrayJSON_ARRAYAGG()Return result set as a single JSON array->Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().JSON_CONTAINS()Whether JSON document contains specific object at pathJSON_CONTAINS_PATH()Whether JSON document contains any data at pathJSON_DEPTH()Maximum depth of JSON documentJSON_EXTRACT()Return data from JSON document->>Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).JSON_INSERT()Insert data into JSON documentJSON_KEYS()Array of keys from JSON documentJSON_LENGTH()Number of elements in JSON documentJSON_MERGE()(deprecated 8.0.3) Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()JSON_MERGE_PATCH()Merge JSON documents, replacing values of duplicate keysJSON_MERGE_PRESERVE()Merge JSON documents, preserving duplicate keysJSON_OBJECT()Create JSON objectJSON_OBJECTAGG()Return result set as a single JSON objectJSON_OVERLAPS()Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in mon, otherwise FALSE (0)JSON_PRETTY()Print a JSON document in human-readable formatJSON_QUOTE()Quote JSON documentJSON_REMOVE()Remove data from JSON documentJSON_REPLACE()Replace values in JSON documentJSON_SCHEMA_VALID()Validate JSON document against JSON schema; returns TRUEJSON_SCHEMA_VALIDATION_REPORT()Validate JSON document against JSON schema; returns report in JSON format on oute on validation including suess or failure and reasons for failureJSON_SEARCH()Path to value within JSON documentJSON_SET()Insert data into JSON documentJSON_STORAGE_FREE()Freed space within binary representation of JSON column value following partial updateJSON_STORAGE_SIZE()Space used for storage of binary representation of a JSON documentJSON_TABLE()Return data from a JSON expression as a relational tableJSON_TYPE()Type of JSON valueJSON_UNQUOTE()Unquote JSON valueJSON_VALID()Whether JSON value is validLAG()Value of argument from row lagging current row within partitionLAST_DAYReturn the last day of the month for the argumentLAST_INSERT_ID()Value of the AUTOINCREMENT column for the last INSERTLAST_VALUE()Value of argument from last row of window frameLCASE()Synonym for LOWER()LEAD()Value of argument from row leading current row within partitionLEAST()Return the smallest argumentLEFT()Return the leftmost number of characters as specified<<Left shiftLENGTH()Return the length of a string in bytes<Less than operator<=Less than or equal operatorLIKESimple pattern matchingLineString()Construct LineString from Point valuesLN()Return the natural logarithm of the argumentLOAD_FILE()Load the named fileLOCALTIME(), LOCALTIMESynonym for NOW()LOCALTIMESTAMP, LOCALTIMESTAMP()Synonym for NOW()LOCATE()Return the position of the first ourrence of substringLOG()Return the natural logarithm of the first argumentLOG10()Return the base-10 logarithm of the argumentLOG2()Return the base-2 logarithm of the argumentLOWER()Return the argument in lowercaseLPAD()Return the string argument, left-padded with the specified stringLTRIM()Remove leading spacesMAKE_SET()Return a set of ma-separated strings that have the corresponding bit in bits setMAKEDATE()Create a date from the year and day of yearMAKETIME()Create time from hour, minute, secondMASTER_POS_WAIT()Block until the slave has read and applied all updates up to the specified positionMATCHPerform full-text searchMAX()Return the maximum valueMBRContains()Whether MBR of one geometry contains MBR of anotherMBRCoveredBy()Whether one MBR is covered by anotherMBRCovers()Whether one MBR covers anotherMBRDisjoint()Whether MBRs of two geometries are disjointMBREquals()Whether MBRs of two geometries are equalMBRIntersects()Whether MBRs of two geometries intersectMBROverlaps()Whether MBRs of two geometries overlapMBRTouches()Whether MBRs of two geometries touchMBRWithin()Whether MBR of one geometry is within MBR of anotherMD5()Calculate MD5 checksumMEMBER OF() Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0)MICROSECOND()Return the microseconds from argumentMID()Return a substring starting from the specified positionMIN()Return the minimum value-Minus operatorMINUTE()Return the minute from the argumentMOD()Return the remainder%, MODModulo operatorMONTH()Return the month from the date passedMONTHNAME()Return the name of the monthMultiLineString()Contruct MultiLineString from LineString valuesMultiPoint()Construct MultiPoint from Point valuesMultiPolygon()Construct MultiPolygon from Polygon valuesNAME_CONST()Cause the column to have the given nameNOT, !Negates valueNOT BETWEEN … AND …Whether a value is not within a range of values!=, <>Not equal operatorNOT IN()Whether a value is not within a set of valuesNOT LIKENegation of simple pattern matchingNOT REGEXPNegation of REGEXPNOW()Return the current date and timeNTH_VALUE()Value of argument from N-th row of window frameNTILE()Bucket number of current row within its partition.NULLIF()Return NULL if expr1 = expr2OCT()Return a string containing octal representation of a numberOCTET_LENGTH()Synonym for LENGTH()OR,ORD()Return character code for leftmost character of the argumentPASSWORD()Calculate and return a password stringPERCENT_RANK()Percentage rank valuePERIOD_ADD()Add a period to a year-monthPERIOD_DIFF()Return the number of months between periodsPI()Return the value of pi+Addition operatorPoint()Construct Point from coordinatesPolygon()Construct Polygon from LineString argumentsPOSITION()Synonym for LOCATE()POW()Return the argument raised to the specified powerPOWER()Return the argument raised to the specified powerPS_CURRENT_THREAD_ID()Performance Schema thread ID for current threadPS_THREAD_ID()Performance Schema thread ID for given threadQUARTER()Return the quarter from a date argumentQUOTE()Escape the argument for use in an SQL statementRADIANS()Return argument converted to radiansRAND()Return a random floating-point valueRANDOM_BYTES()Return a random byte vectorRANK()Rank of current row within its partition, with gapsREGEXPWhether string matches regular expressionREGEXP_INSTR()Starting index of substring matching regular expressionREGEXP_LIKE()Whether string matches regular expressionREGEXP_REPLACE()Replace substrings matching regular expressionREGEXP_SUBSTR()Return substring matching regular expressionRELEASE_ALL_LOCKS()Release all current named locksRELEASE_LOCK()Release the named lockREPEAT()Repeat a string the specified number of timesREPLACE()Replace ourrences of a specified stringREVERSE()Reverse the characters in a stringRIGHT()Return the specified rightmost number of characters>>Right shiftRLIKEWhether string matches regular expressionROLES_GRAPHML()Return a GraphML document representing memory role subgraphsROUND()Round the argumentROW_COUNT()The number of rows updatedROW_NUMBER()Number of current row within its partitionRPAD()Append string the specified number of timesRTRIM()Remove trailing spacesSCHEMA()Synonym for DATABASE()SEC_TO_TIME()Converts seconds to ‘hh:mm:ss’ formatSECOND()Return the second (0-59)SESSION_USER()Synonym for USER()SHA1(), SHA()Calculate an SHA-1 160-bit checksumSHA2()Calculate an SHA-2 checksumSIGN()Return the sign of the argumentSIN()Return the sine of the argumentSLEEP()Sleep for a number of secondsSOUNDEX()Return a soundex stringSOUNDS LIKECompare soundsSPACE()Return a string of the specified number of spacesSQRT()Return the square root of the argumentST_Area()Return Polygon or MultiPolygon areaST_AsBinary(), ST_AsWKB() Convert from internal geometry format to WKBST_AsGeoJSON()Generate GeoJSON object from geometryST_AsText(), ST_AsWKT()Convert from internal geometry format to WKTST_Buffer()Return geometry of points within given distance from geometryST_Buffer_Strategy()Produce strategy option for ST_Buffer()ST_Centroid()Return centroid as a pointST_Contains()Whether one geometry contains anotherST_ConvexHull()Return convex hull of geometryST_Crosses()Whether one geometry crosses anotherST_Difference()Return point set difference of two geometriesST_Dimension()Dimension of geometryST_Disjoint()Whether one geometry is disjoint from anotherST_Distance()The distance of one geometry from anotherST_Distance_Sphere()Minimum distance on earth between two geometriesST_EndPoint()End Point of LineStringST_Envelope()Return MBR of geometryST_Equals()Whether one geometry is equal to anotherST_ExteriorRing()Return exterior ring of PolygonST_GeoHash()Produce a geohash valueST_GeomCollFromText() , ST_GeometryCollectionFromText(), ST_GeomCollFromTxt()Return geometry collection from WKTST_GeomCollFromWKB(), ST_GeometryCollectionFromWKB()Return geometry collection from WKBST_GeometryN()Return N-th geometry from geometry collectionST_GeometryType()Return name of geometry typeST_GeomFromGeoJSON()Generate geometry from GeoJSON objectST_GeomFromText(), ST_GeometryFromText()Return geometry from WKTST_GeomFromWKB(), ST_GeometryFromWKB()Return geometry from WKBST_InteriorRingN()Return N-th interior ring of PolygonST_Intersection()Return point set intersection of two geometriesST_Intersects()Whether one geometry intersects anotherST_IsClosed()Whether a geometry is closed and simpleST_IsEmpty()Placeholder functionST_IsSimple()Whether a geometry is simpleST_IsValid()Whether a geometry is validST_LatFromGeoHash()Return latitude from geohash valueST_Latitude()Return latitude of PointST_Length()Return length of LineStringST_LineFromText(), ST_LineStringFromText()Construct LineString from WKTST_LineFromWKB(), ST_LineStringFromWKB()Construct LineString from WKBST_LongFromGeoHash()Return longitude from geohash valueST_Longitude()Return longitude of PointST_MakeEnvelope()Rectangle around two pointsST_MLineFromText(), ST_MultiLineStringFromText()Construct MultiLineString from WKTST_MLineFromWKB(), ST_MultiLineStringFromWKB()Construct MultiLineString from WKBST_MPointFromText(), ST_MultiPointFromText()Construct MultiPoint from WKTST_MPointFromWKB(), ST_MultiPointFromWKB()Construct MultiPoint from WKBST_MPolyFromText(), ST_MultiPolygonFromText()Construct MultiPolygon from WKTST_MPolyFromWKB(), ST_MultiPolygonFromWKB()Construct MultiPolygon from WKBST_NumGeometries()Return number of geometries in geometry collectionST_NumInteriorRing(), ST_NumInteriorRings()Return number of interior rings in PolygonST_NumPoints()Return number of points in LineStringST_Overlaps()Whether one geometry overlaps anotherST_PointFromGeoHash()Convert geohash value to POINT valueST_PointFromText()Construct Point from WKTST_PointFromWKB()Construct Point from WKBST_PointN()Return N-th point from LineStringST_PolyFromText(), ST_PolygonFromText()Construct Polygon from WKTST_PolyFromWKB(), ST_PolygonFromWKB()Construct Polygon from WKBST_Simplify()Return simplified geometryST_SRID()Return spatial reference system ID for geometryST_StartPoint()Start Point of LineStringST_SwapXY()Return argument with XST_SymDifference()Return point set symmetric difference of two geometriesST_Touches()Whether one geometry touches anotherST_Transform()Transform coordinates of geometryST_Union()Return point set union of two geometriesST_Validate()Return validated geometryST_Within()Whether one geometry is within anotherST_X()Return X coordinate of PointST_Y()Return Y coordinate of PointSTATEMENT_DIGEST()Compute statement digest hash valueSTATEMENT_DIGEST_TEXT()Compute normalized statement digestSTD()Return the population standard deviationSTDDEV()Return the population standard deviationSTDDEV_POP()Return the population standard deviationSTDDEV_SAMP()Return the sample standard deviationSTR_TO_DATE()Convert a string to a dateSTRCMP()Compare two stringsSUBDATE()Synonym for DATE_SUB() when invoked with three argumentsSUBSTR()Return the substring as specifiedSUBSTRING()Return the substring as specifiedSUBSTRING_INDEX()Return a substring from a string before the specified number of ourrences of the delimiterSUBTIME()Subtract timesSUM()Return the sumSYSDATE()Return the time at which the function executesSYSTEM_USER()Synonym for USER()TAN()Return the tangent of the argumentTIME()Extract the time portion of the expression passedTIME_FORMAT()Format as timeTIME_TO_SEC()Return the argument converted to secondsTIMEDIFF()Subtract time*Multiplication operatorTIMESTAMP()With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the argumentsTIMESTAMPADD()Add an interval to a datetime expressionTIMESTAMPDIFF()Subtract an interval from a datetime expressionTO_BASE64()Return the argument converted to a base-64 stringTO_DAYS()Return the date argument converted to daysTO_SECONDS()Return the date or datetime argument converted to seconds since Year 0TRIM()Remove leading and trailing spacesTRUNCATE()Truncate to specified number of decimal placesUCASE()Synonym for UPPER()-Change the sign of the argumentUNCOMPRESS()Unpress a string pressedUNCOMPRESSED_LENGTH()Return the length of a string before pressionUNHEX()Return a string containing hex representation of a numberUNIX_TIMESTAMP()Return a Unix timestampUpdateXML()Return replaced XML fragmentUPPER()Convert to uppercaseUSER()The user name and host name provided by the clientUTC_DATE()Return the current UTC dateUTC_TIME()Return the current UTC timeUTC_TIMESTAMP()Return the current UTC date and timeUUID()Return a Universal Unique Identifier (UUID)UUID_SHORT()Return an integer-valued universal identifierUUID_TO_BIN()Convert string UUID to binaryVALIDATE_PASSWORD_STRENGTH()Determine strength of passwordVALUES()Define the values to be used during an INSERTVAR_POP()Return the population standard varianceVAR_SAMP()Return the sample varianceVARIANCE()Return the population standard varianceVERSION()Return a string that indicates the MySQL server versionWAIT_FOR_EXECUTED_GTID_SET()Wait until the given GTIDs have executed on the slave.WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()(deprecated 8.0.18) Deprecated. Use WAIT_FOR_EXECUTED_GTID_SET().WEEK()Return the week numberWEEKDAY()Return the weekday indexWEEKOFYEAR()Return the calendar week of the date (1-53)WEIGHT_STRING()Return the weight string for a stringXORLogical XORYEAR()Return the yearYEARWEEK()Return the year and week
二、时间类型的函数
2.1、格式化 - DATE_FORMAT
按照年月日统计，分组统计中还有count(1),count(*).count(字段)的区别
SELECT orders.orderDate from orders;SELECTDATE_FORMAT( orders.orderDate, '%Y-%m-%d' ),count( orders.orderDate )
FROMorders
GROUP BYDATE_FORMAT( orders.orderDate, '%Y-%m-%d' );
-- 以月为维度
WITH a AS (
SELECT *, TIMESTAMPDIFF( MONTH, orderDate, CURRENT_DATE ( ) ) AS DiffMonth FROM orders
) SELECT
a.*
FROMa
WHEREa.DiffMonth = 200
-- 以年为维度
WITH a AS (
SELECT *, TIMESTAMPDIFF( YEAR, orderDate, CURRENT_DATE ( ) ) AS DiffMonth FROM orders
) SELECT
a.*
FROMa
WHEREa.DiffMonth <=14 ORDER BY a.DiffMonth-- 以天为维度
EXPLAIN WITH a AS (
SELECT *, TIMESTAMPDIFF( DAY, orderDate, CURRENT_DATE ( ) ) AS DiffMonth FROM orders
) SELECT
a.*
FROMa
WHEREa.DiffMonth <=1400 ORDER BY a.DiffMonth
或者
EXPLAIN WITH a AS (
SELECT *, DATEDIFF(current_date(),orderDate) AS DiffDay FROM orders
) SELECT
a.*
FROMa
WHEREa.DiffDay <=1400 ORDER BY a.DiffDay
2.3、 select sys.format_time(‘1’)
select sys.format_time(1) --1.00ps
select sys.format_time(1000) --1.00ns
select sys.format_time(1000*1000) -- 1.00 us
select sys.format_time(1000*1000*1000) -- 1.00 ms
select sys.format_time(1000*1000*1000*1000) -- 1.00 s
-- concat
SELECT CONCAT(m.lastname,', ',m.firstname) AS 'Manager',CONCAT(e.lastname,', ',e.firstname) AS 'Direct report'
FROM employees e
INNER JOIN employees m ON m.employeeNumber = e.reportsto
ORDER BY manager DESC-- 这里多了个 IFNULL 函数，结果是把自交笛卡尔积，右表数据空值默认值为 top Manager
SELECT IFNULL(CONCAT(m.lastname, ', ', m.firstname),'Top Manager') AS 'Manager',CONCAT(e.lastname, ', ', e.firstname) AS 'Direct report'
FROMemployees eLEFT JOINemployees m ON m.employeeNumber = e.reportsto
ORDER BY manager DESC
-- CONCAT_WS
SELECT CONCAT_WS(', ', lastName, firstname) `Full name`
FROMemployees
ORDER BY `Full name`;
四、结果集拼接构造数据
4.1、union select
单列追加数据
SELECTCONCAT(firstName,' ',lastName) fullname
FROMemployees
UNION SELECTCONCAT(contactFirstName,' ',contactLastName)
FROMcustomers;
多列构造初始数据
SELECTCONCAT(firstName, ' ', lastName) fullname,'Employee' as contactType
FROMemployees
UNION SELECTCONCAT(contactFirstName, ' ', contactLastName),'Customer' as contactType
FROMcustomers
ORDER BYfullname
五、统计/计算类函数
5.1、row_number使用
常用三个操作
5.1.1、给行分配序号
SELECTROW_NUMBER( ) OVER ( ORDER BY productName ) row_num,productName,msrp
FROMproducts
ORDER BYproductName;
5.1.2、查找每个组的前N行
WITH inventory AS ( SELECT productLine, productName, quantityInStock, ROW_NUMBER ( ) OVER ( PARTITION BY productLine ORDER BY quantityInStock DESC ) row_num FROM products ) SELECT
productLine,
productName,
quantityInStock
FROMinventory
WHERErow_num <= 3;
SELECTproductLine,productName,quantityInStock,ROW_NUMBER ( ) OVER ( PARTITION BY productLine ORDER BY quantityInStock DESC ) row_num
FROMproducts
5.2、RANK(排名函数)
查看所有分区部门，以及所有人的工资排名
SELECT first_name, last_name, department_id,salary, RANK() OVER (PARTITION BY department_idORDER BY salary) salary_rankFROM employees
在此基础上，查询所有部门排名第二的人信息
WITH payroll AS (SELECT first_name, last_name, department_id,salary, RANK() OVER (PARTITION BY department_idORDER BY salary) salary_rankFROM employees
)
SELECT first_name, last_name,department_name,salary
FROM payroll pINNER JOIN departments d ON d.department_id = p.department_id
WHERE salary_rank = 2;
每个部门工资最高的人
SELECT department_name,first_name,last_name,salary
FROM (SELECT department_name,ROW_NUMBER() OVER (PARTITION BY department_nameORDER BY salary DESC) row_num, first_name, last_name, salaryFROM employees eINNER JOIN departments d ON d.department_id = e.department_id) t
WHERE row_num = 1;
5.3、PARTITION BY
SELECT first_name,last_name,department_id, ROUND(AVG(salary) OVER (PARTITION BY department_id)) avg_department_salary
FROMemployees;
六、其它类函数
6.1、FIELD(),可以自定义排序规则
SELECTorderNumber,
STATUS
FROMorders
ORDER BYFIELD( STATUS, 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped' );
SELECT FIND_IN_SET("q", null) test;
# nullSELECT FIND_IN_SET("q", "s,q,l");
# 2SELECT FIND_IN_SET("a", "s,q,l");
# 0explain select * from nodelist where find_in_set("node1",nodelist.nodecontent);explain select count(*) from nodelist;
需要注意的是，在没有索引的情况下，性能很低…
6.3、除重
CREATE TABLE t (id INT,name VARCHAR(10) NOT NULL
);INSERT INTO t(id,name)
VALUES(1,'A'),(2,'B'),(2,'B'),(3,'C'),(3,'C'),(3,'C'),(4,'D'); WITH temp AS(SELECT id,name,ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) AS row_num
FROM t) SELECT temp.id,temp.name FROM temp WHERE temp.row_num=1
6.4、COALESCE(返回第一个非空值)
SELECTo.city,o.postalCode,COALESCE ( state, country ) AS Address
FROMoffices o
发布评论