`
lovelong1
  • 浏览: 3796 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
最近访客 更多访客>>
文章分类
社区版块
存档分类
最新评论

SQL语句MSSQL.MySQL.Oracle.PostgreSQL不同用法

阅读更多

The database vendor implementations shown in examples below (Microsoft SQL Server, MySQL, Oracle, and PostgreSQL) are discussed in our upcoming book SQL in a Nutshell. There are a great many function calls that are universally supported by the ANSI (American National Standards Institute) standard and all the database vendors. For example, most vendors support the commonly used aggregate functions of SUM, AVG, MIN, and MAX. These functions extract summary value, average value, and minimum or maximum value from a column or an expression, respectively. There are also a whole variety of functions that are not universally supported, such as RPAD and LPAD or SUBSTRING versus SUBSTR.

Although this article discusses database implementations by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL, this information represents just the tip of the iceberg on how business operations accomplish common, everyday SQL coding tasks using functions calls. As you will see, these functions can vary widely.

Date Operations
This first set of examples show how to query the database for common date-processing operations using functions. To get the current date and time:

Microsoft SQL Server 

 SELECT GETDATE()
 GO
MySQL [retrieving the date but not time] 
 SELECT CURDATE();
MySQL [retrieving date and time] 
 SELECT NOW();
Oracle 
 SELECT SYSDATE 
 FROM dual;
PostgreSQL 
 SELECT CURRENT_DATE; 

As the examples illustrate, each vendor retrieves the current date and time differently using its own proprietary function calls. Microsoft SQL Server uses a SELECT statement calling the GETDATE() function. MySQL has two different function calls: CURDATE() and NOW(). The former retrieves the date without time; the latter retrieves date and time. Oracle uses the SYSDATE function call. And PostgreSQL uses the SQL99 CURRENT_DATE function call. Note that for all of these function calls, no passed parameters are needed.

These next examples show how to find out what day a given date falls on: 
 

Microsoft SQL Server 

 SELECT DATEPART(dw, GETDATE())
 GO
MySQL 
 SELECT DAYNAME(CURDATE());
Oracle 
 SELECT TO_CHAR(SYSDATE,'Day')
 FROM dual;
PostgreSQL 
 SELECT DATE_PART('dow', date 'now'); 

Microsoft SQL Server uses the DATEPART function call using the syntax DATEPART(datetype, date_expression). This function requires the type of date (month, day, week, day of week, and so on), as the first argument, and the date expression (either a column containing a date or an actual date value), as the second part. MySQL offers the DAYNAME(date_expression) as its function of choice for finding the day of the week for a given date value. Oracle requires that the date be converted into a character value using TO_CHAR, but allows the application of a format mask that returns the data of the week value. Conversions of this type in Oracle follow the syntax TO_CHAR(conversion_expression, 'datetype'). In this case, TO_CHAR can be used to convert any other datatype to character datatype, including INT and DATE datatypes. PostgreSQL accomplishes date conversion using the DATE_PART function to extract the day of the week from the date expression. The syntax is DATE_PART('text', timestamp), where text defines how the date is returned (in our example, as a day of the week), and timestamp defines the date expression.

Sometimes an application needs to know how far two dates are from one another. To determine how far away a date is from the current date (or any other date for that matter), either in the future or in the past, use these examples:

Microsoft SQL Server 

 SELECT DATEDIFF(dd, '1/1/01', GETDATE())
 GO
MySQL 
 SELECT FROM_DAYS(TO_DAYS(CURDATE()) - 
 TO_DAYS('2001-11-25'));
Oracle 
 SELECT TO_DATE('25-Nov-2000','dd-mon-yyyy') - 
 TO_DATE('25-Aug-1969','dd-mon-yyyy') 
 FROM dual;
PostgreSQL 
 SELECT AGE(CURRENT_DATE, '25-Aug-1969'); 

Measuring the time span between two dates is a procedure best left to procedure calls. But again, the syntax varies widely between the vendors. Microsoft uses the DATEDIFF function to measure the time span between two dates (in the example, between January 1, 2001 and today's date). The syntax is DATEDIFF(datetype, start_date, end_date), where datetype is a code representing how the time span should be represented (days, weeks, months, and so on), the start_date is the date to measure from, and the end_date is the date to measure to. MySQL must use the somewhat more cumbersome FROM_DAYS and TO_DAYS functions in a nested format to tell the time span between two dates. Oracle very neatly allows date addition and subtraction. The only reason the TO_DATE function is even needed is that the operation is being performed on character strings. If the operation were performed against two columns of DATE datatype, then no TO_DATE conversion would be necessary and the subtraction operation would act directly on the date expression. PostgreSQL has a cool function called AGE(start_date, end_date) that tells the time span between two passed dates as parameters.

It is common procedure to retrieve a date in a different format mask (Mon, DD, YYYY; mm/dd/yy; dd/mm/yy; etc.). Here are some examples: 
 

Microsoft SQL Server 

 SELECT CONVERT(VARCHAR(11), GETDATE(), 102)
 GO
MySQL 
 SELECT DATE_formAT( "2001-11-25", "%M %e, %Y");
Oracle 
 SELECT TO_CHAR(SYSDATE,'dd-Mon-yyyy hh:mi:ss PM') 
 FROM dual;
PostgreSQL 
 SELECT TO_CHAR (timestamp(CURRENT_DATE),
 'dd-Mon-yyyy hh:mi:ss PM'); 

In these examples, the vendors use specialized function calls to retrieve date expressions in a specific format. Microsoft SQL Server uses the CONVERT function (though CAST could also be used) in the syntax of CONVERT(convert_to_datatype, source_expression, date_format), where the convert_to_datatype is the datatype to return in the query, the source_expression is the source that will be converted, and the date_format is a set of codes that Microsoft has set aside for specific date format masks. MySQL uses the DATE_formAT function in the syntax of DATE_formAT(source_expression, date_format). Oracle uses TO_CHAR, as shown earlier, in the syntax of TO_CHAR(source_expression, date_format). PostgreSQL also uses TO_CHAR, though somewhat differently in that the source_expression must be enclosed within the time-stamp subfunction, as shown in the example above.

String Operations
Often, an application may need to find one string within another string. This is one way of performing this operation across the different vendors:

 

Microsoft SQL Server 

 SELECT CHARINDEX('eat', 'great')
 GO
MySQL 
 SELECT POSITION('eat' IN 'great');
Oracle 
 SELECT INSTR('Great','eat') FROM dual;
PostgreSQL 
 SELECT POSITION('eat' IN 'great'); 

 

Microsoft SQL Server uses its own function, CHARINDEX, to extract values from other strings. In this example, it will return the starting position of one string, 'eat,' within another, 'great.' The syntax is CHARINDEX(search_string, searched_string, [starting_position]). MySQL and PostgreSQL both accomplish a similar operation using the POSITION function, showing where 'eat' occurs within 'great.' Oracle uses the INSTR function, although the order of the passed parameters are reversed. Unlike the other vendors, Oracle requires the searched_string first, then the search_string.

It is often necessary to trim trailing and leading spaces from an expression in an SQL operation:

Microsoft SQL Server 

 SELECT LTRIM('  sql_in_a_nutshell'), 
    SELECT RTRIM('sql_in_a_nutshell      '),
    SELECT LTRIM(RTRIM('     sql_in_a_nutshell     ')
 GO
MySQL 
 SELECT LTRIM('  sql_in_a_nutshell'), 
    SELECT RTRIM('sql_in_a_nutshell      '),
    SELECT TRIM('     sql_in_a_nutshell      '),
    SELECT TRIM(BOTH FROM '     sql_in_a_nutshell     ');
Oracle 
 SELECT LTRIM('  sql_in_a_nutshell'), 
    SELECT RTRIM('sql_in_a_nutshell      '),
    TRIM('     sql_in_a_nutshell      ') 
 FROM dual;
PostgreSQL 
 SELECT TRIM(LEADING FROM '     sql_in_a_nutshell'),
     TRIM(TRAILING FROM 'sql_in_a_nutshell     '),
     TRIM(BOTH FROM '     sql_in_a_nutshell     '); 

 

Microsoft SQL Server uses the LTRIM and RTRIM functions to remove spaces from the left or right side of an expression, respectively. When trimming spaces on both sides of an expression in Microsoft SQL Server, the LTRIM function must encapsulate the RTRIM function (or vice versa). MySQL and Oracle both use LTRIM and RTRIM, but differ from SQL Server in that spaces can be trimmed from both sides of an expression with the TRIM function. MySQL also allows TRIM with the BOTH operator to indicate that both left and right sides of the expression should be trimmed. PostgreSQL uses only the TRIM function and controls whether the left, right, or both sides should be trimmed using the LEADING, TRAILING, and BOTH operators, as shown in the example above.

The opposite of trimming spaces is to pad them into an expression. To pad in x number of trailing or leading spaces with the various vendors:

Microsoft SQL Server 

 Not supported
MySQL 
 SELECT LPAD('sql_in_a_nutshell', 20, ' '),  
 RPAD('sql_in_a_nutshell', 20, ' ');
Oracle 
 SELECT LPAD(('sql_in_a_nutshell', 20, ' '), 
 RPAD(('sql_in_a_nutshell', 20, ' ') 
 FROM dual;
PostgreSQL 
 SELECT LPAD('sql_in_a_nutshell', 20, ' '),  
 RPAD('sql_in_a_nutshell', 20, ' '); 

 

In this example, the supporting vendors all use LPAD to insert spaces (or a character expression) on the left side of a string expression and RPAD to put them on the right. The syntax for MySQL, Oracle, and PostgreSQL is xPAD('string_expression1', length, 'string_expression2'), where string_expression1 is the string to have characters padded, length is the total length of the string, and string_expression2 is the characters to pad out.

An operation similar to pad is to substitute characters within a string with other characters:

Microsoft SQL Server [returns 'wabbit_hunting_season'] 

 SELECT STUFF('wabbit_season', 7, 1, '_hunting_') 
 GO
MySQL [returns 'wabbit_hunting_season'] 
 SELECT 
    REPLACE('wabbit_season','it_','it_hunting_');
Oracle [returns 'wabbit_hunting_season'] 
 SELECT 
    REPLACE('wabbit_season','it_','it_hunting_') 
 FROM dual; 
PostgreSQL 
 SELECT TRANSLATE('wabbit_season','it_','it_hunting_'); 

 


Microsoft SQL Server uses the STUFF function to overwrite existing characters. Using this syntax, STUFF(string_expression, start, length, replacement_characters), string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string. MySQL and Oracle both use the function call REPLACE, using the syntax REPLACE(string_expression, search_string, replacement_string), where every incidence of search_string found in the string_expression will be replaced with replacement_string. PostgreSQL uses the TRANSLATE function as a synonym of REPLACE.

Many times, a SQL statement must retrieve only a portion of a string. The following examples show how to extract 'duck_season' from the string 'wabbit_duck_season' for each vendor:

Microsoft SQL Server 

 SELECT SUBSTRING('wabbit_duck_season', 7, 11) 
 GO
MySQL 
 SELECT 
    SUBSTRING('wabbit_duck_season', 7, 11);
Oracle 
 SELECT SUBSTR('wabbit_duck_season', 7, 11) 
 FROM dual;
PostgreSQL 
 SELECT 
    SUBSTR('wabbit_duck_season', 7, 11); 

 

In each example, the syntax for SUBSTRING (or SUBSTR) is essentially the same: SUBSTRING(string_expression, start, length), where string_expression is the expression or column to be searched, start is an integer telling the starting position, and length is an integer telling the database how many characters to extract.

Some vendors allow function calls that can structure an IF, THEN, ELSE result set within the query:

Microsoft SQL Server 

 SELECT  CASE 
     WHEN foo = 'hi'   THEN 'there'
     WHEN foo = 'good' THEN 'bye'
     ELSE 'default'
 END
 FROM t2
 GO
MySQL 
 N/A 
Oracle 
 SELECT DECODE 
    (payments_info,'CR','Credit','DB','Debit', null)
 FROM dual;
PostgreSQL 
 SELECT CASE
     WHEN foo = 'hi'   THEN 'there'
     WHEN foo = 'good' THEN 'bye'
     ELSE 'default'
 END 
 FROM t2; 

 


Microsoft SQL Server and PostgreSQL support the powerful ANSI SQL command CASE. CASE has two usages: simple and searched. Simple CASE expression compares one value, the input_value, with a list of other values and returns a result associated with the first matching value. Searched CASE expressions allow the analysis of several logical conditions and returns a result associated with the first one that is true.

Simple comparison operation

 CASE input_value 
 WHEN when_condition THEN resulting_value 
 [...n] 
 [ELSE else_result_value] 
 END
Boolean searched operation 
 CASE 
 WHEN Boolean_condition THEN resulting_value 
 [...n] 
 [ELSE else_result_expression] 
 END 

 


In the simple CASE function, the input_value is evaluated against each WHEN clause. The resulting_value is returned for the first TRUE instance of input_value = when_condition. If no when_condition evaluates as TRUE, the else_result_value is returned. If no else_result_value is specified, then NULL is returned.

In the more elaborate Boolean searched operation, the structure is essentially the same as the simple comparison operation except that each WHEN clause has its own Boolean comparison operation. In either usage, multiple WHEN clauses are used, although only one ELSE clause is necessary.

Oracle supports its own extremely powerful IF, THEN, ELSE function call: DECODE. DECODE has a unique syntax along these lines, DECODE(search_expression, search1, replace1, search[,.n], replace,.n], default), where search_expression is the string to be searched; subsequently each search string is paired with a replacement string. If a search is successful, the corresponding result is returned. In our example, when returning a result set from the payments_info column, any incident of 'CR' will be replaced with 'Credit,' any instance of 'DB' will be replace with 'Debit,' and any other values will be replaced with a default value of Null.

Nulls Operations
Nulls are sometimes tricky business. Sometimes a company process, such as a query or other data manipulation statement, must explicitly handle NULLs. These examples show how to return a value specified when a field or result is null:

Microsoft SQL Server 

 SELECT ISNULL(foo, 'value is Null')
 GO
MySQL 
 N/A
Oracle 
 SELECT NVL(foo,'value is Null') 
 FROM dual;
PostgreSQL [allows you to write a user-defined function to handle this feature] 
 N/A 

 


Microsoft SQL Server uses the ISNULL function following the syntax ISNULL(string_expression, replacement_value), where string_expression is a string or column being searched and replacement value is the value returned if string_expression is NULL. Oracle uses a different function, NVL, but follows an almost identical syntax.

Alternately, there may be times when a NULL value is needed if a field contains a specific value:

Microsoft SQL Server [returns NULL when foo equates to 'Wabbits!'] 

 SELECT NULLIF(foo, 'Wabbits!')
 GO
MySQL 
 N/A
Oracle 
 SELECT DECODE(foo,'Wabbits!',NULL) 
 FROM dual;
PostgreSQL 
 SELECT NULLIF(foo, 'Wabbits!'); 

 

 

Aside from using CASE or DECODE to solve this problem, Microsoft and PostgreSQL allow use of the NULLIF function. The syntax is NULLIF(expression1, expression2), which tells the database that if expression1 equals expression2, then returns a NULL value.


Summary
There are a great many function calls that are universally supported by the ANSI standard and all the database vendors. This article has shown a variety of useful function calls available in database implementations by Microsoft SQL Server, MySQL, Oracle, and PostgreSQL. For more details on ANSI standard functions, check out our book, SQL in a Nutshell.

 

 

分享到:
评论

相关推荐

    SNS单模无芯光纤仿真与传感器结构特性分析——基于Rsoft beamprop模块

    内容概要:本文主要探讨了SNS单模无芯光纤的仿真分析及其在通信和传感领域的应用潜力。首先介绍了模间干涉仿真的重要性,利用Rsoft beamprop模块模拟不同模式光在光纤中的传播情况,进而分析光纤的传输性能和模式特性。接着讨论了光纤传输特性的仿真,包括损耗、色散和模式耦合等参数的评估。随后,文章分析了光纤的结构特性,如折射率分布、包层和纤芯直径对性能的影响,并探讨了镀膜技术对光纤性能的提升作用。最后,进行了变形仿真分析,研究外部因素导致的光纤变形对其性能的影响。通过这些分析,为优化光纤设计提供了理论依据。 适合人群:从事光纤通信、光学工程及相关领域的研究人员和技术人员。 使用场景及目标:适用于需要深入了解SNS单模无芯光纤特性和优化设计的研究项目,旨在提高光纤性能并拓展其应用场景。 其他说明:本文不仅提供了详细的仿真方法和技术细节,还对未来的发展方向进行了展望,强调了SNS单模无芯光纤在未来通信和传感领域的重要地位。

    发那科USM通讯程序socket-rece

    发那科USM通讯程序socket-set

    嵌入式八股文面试题库资料知识宝典-WIFI.zip

    嵌入式八股文面试题库资料知识宝典-WIFI.zip

    JS+HTML源码与image

    源码与image

    物流行业车辆路径优化:基于遗传算法和其他优化算法的MATLAB实现及应用

    内容概要:本文详细探讨了物流行业中路径规划与车辆路径优化(VRP)的问题,特别是针对冷链物流、带时间窗的车辆路径优化(VRPTW)、考虑充电桩的车辆路径优化(EVRP)以及多配送中心情况下的路径优化。文中不仅介绍了遗传算法、蚁群算法、粒子群算法等多种优化算法的理论背景,还提供了完整的MATLAB代码及注释,帮助读者理解这些算法的具体实现。此外,文章还讨论了如何通过MATLAB处理大量数据和复杂计算,以得出最优的路径方案。 适合人群:从事物流行业的研究人员和技术人员,尤其是对路径优化感兴趣的开发者和工程师。 使用场景及目标:适用于需要优化车辆路径的企业和个人,旨在提高配送效率、降低成本、确保按时交付货物。通过学习本文提供的算法和代码,读者可以在实际工作中应用这些优化方法,提升物流系统的性能。 其他说明:为了更好地理解和应用这些算法,建议读者参考相关文献和教程进行深入学习。同时,实际应用中还需根据具体情况进行参数调整和优化。

    嵌入式八股文面试题库资料知识宝典-C and C++ normal interview_8.doc.zip

    嵌入式八股文面试题库资料知识宝典-C and C++ normal interview_8.doc.zip

    基于灰狼优化算法的城市路径规划Matlab实现——解决TSP问题

    内容概要:本文介绍了基于灰狼优化算法(GWO)的城市路径规划优化问题(TSP),并通过Matlab实现了该算法。文章详细解释了GWO算法的工作原理,包括寻找猎物、围捕猎物和攻击猎物三个阶段,并提供了具体的代码示例。通过不断迭代优化路径,最终得到最优的城市路径规划方案。与传统TSP求解方法相比,GWO算法具有更好的全局搜索能力和较快的收敛速度,适用于复杂的城市环境。尽管如此,算法在面对大量城市节点时仍面临运算时间和参数设置的挑战。 适合人群:对路径规划、优化算法感兴趣的科研人员、学生以及从事交通规划的专业人士。 使用场景及目标:①研究和开发高效的路径规划算法;②优化城市交通系统,提升出行效率;③探索人工智能在交通领域的应用。 其他说明:文中提到的代码可以作为学习和研究的基础,但实际应用中需要根据具体情况调整算法参数和优化策略。

    嵌入式八股文面试题库资料知识宝典-Intel3.zip

    嵌入式八股文面试题库资料知识宝典-Intel3.zip

    嵌入式八股文面试题库资料知识宝典-2019京东C++.zip

    嵌入式八股文面试题库资料知识宝典-2019京东C++.zip

    嵌入式八股文面试题库资料知识宝典-北京光桥科技有限公司面试题.zip

    嵌入式八股文面试题库资料知识宝典-北京光桥科技有限公司面试题.zip

    物理学领域十字形声子晶体的能带与传输特性研究及应用

    内容概要:本文详细探讨了十字形声子晶体的能带结构和传输特性。首先介绍了声子晶体作为新型周期性结构在物理学和工程学中的重要地位,特别是十字形声子晶体的独特结构特点。接着从散射体的形状、大小、排列周期等方面分析了其对能带结构的影响,并通过理论计算和仿真获得了能带图。随后讨论了十字形声子晶体的传输特性,即它对声波的调控能力,包括传播速度、模式和能量分布的变化。最后通过大量实验和仿真验证了理论分析的正确性,并得出结论指出散射体的材料、形状和排列方式对其性能有重大影响。 适合人群:从事物理学、材料科学、声学等相关领域的研究人员和技术人员。 使用场景及目标:适用于希望深入了解声子晶体尤其是十字形声子晶体能带与传输特性的科研工作者,旨在为相关领域的创新和发展提供理论支持和技术指导。 其他说明:文中还对未来的研究方向进行了展望,强调了声子晶体在未来多个领域的潜在应用价值。

    嵌入式系统开发_USB主机控制器_Arduino兼容开源硬件_基于Mega32U4和MAX3421E芯片的USB设备扩展开发板_支持多种USB外设接入与控制的通用型嵌入式开发平台_.zip

    嵌入式系统开发_USB主机控制器_Arduino兼容开源硬件_基于Mega32U4和MAX3421E芯片的USB设备扩展开发板_支持多种USB外设接入与控制的通用型嵌入式开发平台_

    e2b8a-main.zip

    e2b8a-main.zip

    少儿编程scratch项目源代码文件案例素材-火柴人跑酷(2).zip

    少儿编程scratch项目源代码文件案例素材-火柴人跑酷(2).zip

    【HarmonyOS分布式技术】远程启动子系统详解:跨设备无缝启动与智能协同的应用场景及未来展望

    内容概要:本文详细介绍了HarmonyOS分布式远程启动子系统,该系统作为HarmonyOS的重要组成部分,旨在打破设备间的界限,实现跨设备无缝启动、智能设备选择和数据同步与连续性等功能。通过分布式软总线和分布式数据管理技术,它能够快速、稳定地实现设备间的通信和数据同步,为用户提供便捷的操作体验。文章还探讨了该系统在智能家居、智能办公和教育等领域的应用场景,展示了其在提升效率和用户体验方面的巨大潜力。最后,文章展望了该系统的未来发展,强调其在技术优化和应用场景拓展上的无限可能性。 适合人群:对HarmonyOS及其分布式技术感兴趣的用户、开发者和行业从业者。 使用场景及目标:①理解HarmonyOS分布式远程启动子系统的工作原理和技术细节;②探索该系统在智能家居、智能办公和教育等领域的具体应用场景;③了解该系统为开发者提供的开发优势和实践要点。 其他说明:本文不仅介绍了HarmonyOS分布式远程启动子系统的核心技术和应用场景,还展望了其未来的发展方向。通过阅读本文,用户可以全面了解该系统如何通过技术创新提升设备间的协同能力和用户体验,为智能生活带来新的变革。

    嵌入式八股文面试题库资料知识宝典-C and C++ normal interview_1.zip

    嵌入式八股文面试题库资料知识宝典-C and C++ normal interview_1.zip

    少儿编程scratch项目源代码文件案例素材-激光反弹.zip

    少儿编程scratch项目源代码文件案例素材-激光反弹.zip

    COMSOL相控阵检测技术在有机玻璃斜楔中检测工件内部缺陷的应用研究

    内容概要:本文详细介绍了COMSOL相控阵检测技术在有机玻璃斜楔上放置16阵元进行工件内部缺陷检测的方法。首先阐述了相控阵检测技术的基本原理,特别是通过控制各阵元的激发时间和相位来实现声波的聚焦和扫描。接着,重点解析了横孔缺陷的反射接收波,解释了波的折射现象及其背后的物理原因。最后,通过实例展示了COMSOL模拟声波传播过程的成功应用,验证了该技术的有效性和准确性。 适合人群:从事固体力学、无损检测领域的研究人员和技术人员,尤其是对相控阵检测技术和COMSOL仿真感兴趣的读者。 使用场景及目标:适用于需要精确检测工件内部缺陷的研究和工业应用场景,旨在提高检测精度和效率,确保产品质量和安全。 其他说明:文中提到的声速匹配现象有助于理解波在不同介质间的传播特性,这对优化检测参数设置有重要意义。

    少儿编程scratch项目源代码文件案例素材-极速奔跑者.zip

    少儿编程scratch项目源代码文件案例素材-极速奔跑者.zip

    嵌入式八股文面试题库资料知识宝典-微软_interview.zip

    嵌入式八股文面试题库资料知识宝典-微软_interview.zip

Global site tag (gtag.js) - Google Analytics