Analysis of SQL syntax difference in mainstream database

The main differences of SQL statements supported by the mainstream database system are as follows: differences in data types, operator differences, function differences, common SQL differences, and differences in metadata information.

Differences in 1.1.1 data types

Integer type: In MySQL, integer-related types have tinyint, smallint, mediumint, int, integer, and bigint; the integer-related types in MSSQLSERVER have bit, int, smallint, tinyint and bigint; integer-related types in Oracle have number, and in DB2, integer-related types have smallint, integer, and bigint.

Numeric type: In MySQL, numeric-related types are float, double, real, decimal, and numeric; in MSSQLServer, the values associated with decimal, numeric, money, smallmoney, float and real; numeric-related types in Oracle have number, and values in DB2 are of type decimal, numeric, real, and double.

Character type: In MySQL, character-related types have char, varchar, tinytext, text, Mediumtext, Longtext, enum, and set; the type associated with characters in MSSQLServer has char, varchar, text, nchar, nvarchar, and ntext; the character-related types in Oracle have char, VARCHAR2, NVARCHAR2, CLOB, and NCLOB, and characters in DB2 have character, VARCHAR, LONG VARCHAR, CLOB, GRAPHIC, Vargraphic and longvargraphic.

Date Time Type: Date, time, DateTime, Timestamp, and year in MySQL-related types, date time in MSSQLServer, DateTime, smalldatetime and timestamp; date and time related types in Oracle have date and timestamp; in DB2 date time phase

The type of shutdown has date, time, and TIMESTAMP.

Binary types: Both MYSQL, Oracle, and DB2 support blob types, while image types are supported in MSSQLSERVER.

Differences in 1.1.2 operators

string concatenation is different in different database systems, and the following mainstream database systems support string concatenation:

MySQL: string concatenation in MySQL using the concat function, the concat function supports one or more parameters, such as concat (' Hello ', 1, ' world '); MySQL also provides another function for string concatenation concat _ws,concat_ws can add a specified score between strings to be spliced

separators, such as Concat_ws (' Hello ', 1, ' World ').

In Mssqlserver:mssqlserver, you can use the plus sign "+" to splice strings, such as ' Hello ' + ' world '.

Use "| |" in oracle:oracle string concatenation, such as ' Hello ' | | World ' except ' | | ', Oracle also supports string concatenation using the CONCAT () function, but unlike MySQL's CONCAT () function, Oracle's CONCAT () function supports only two parameters and does not support stitching of more than two strings.

Use "| |" in DB2:DB2 string concatenation, such as ' Hello ' | | World '.

Differences in 1.1.3 Functions

Different database systems on the function of the difference is very large, not only the functions of the same function in different database system name is different, and some advanced functions are not in all database systems to provide support. For example, a function that converts a string to lowercase is lower in mysql,mssqlserver and Oracle, while in DB2 the IF function is supported in lcase;mysql, whereas in other database systems it is only possible to do so by way of workaround.

1.1.4 Common SQL differences

Mainstream database systems support the basic syntax of SELECT, UPDATE, DELETE, CREATE, drop, but still differ in some advanced feature support.

1.1.4.1 Limit result set number of rows

In the implementation of paging search, ranking and other functions, the need to limit the number of search results set, different database systems for this support is different.

The Limit keyword is provided in MySQL to restrict the returned result set, for example:

SELECT * from T_employee

by Fsalary DESC LIMIT 2,5

The top keyword is provided in Mssqlserver:mssqlserver to return the first N records in the result set, for example:

Select Top 5 *from t_employee

Order BY Fsalarydesc;

In MSSQLServer2005 you can also use window functions row_number () to implement limits on the number of result set rows, such as:

Selectrow_number () over (order by fsalary), Fnumber,

Fname,fsalary,fage

From T_employee