Comparison between the three major databases

Data | database
2004-11-16
Comparison between the three most popular databases today:
MySQL Sql-server Oracle

Mysql
User variables: Set @varialname ={integer expression | real expression | string expression}
Sql-server
User variable: Declare @varialname integer
Set @varialname = must be a numeric type;
DECLARE @varialname varchar (40)
Set @varialname = must be a character type of 40 characters in the specified length, if the length exceeds 40, only
Read out the first 40 characters.
You can also assign a value to a user variable by using a SELECT statement. That is: select @varialname = ' AA '
Oracle
User variables have different criteria in different areas.
For example, to support standard Java user variables within a DBA's function,
In the DBA sequence: Create sequence fstrname @PCTFREE @PCTUSED 10

Mysql
Supported field types: Number type, date and time type, and string (character) type

Number Type tinyint[(M)] [UNSIGNED] [Zerofill]
A very small integer. The signed range is 128 to 127, and the unsigned range is 0 to 255.
smallint[(M)] [UNSIGNED] [Zerofill]
A small integer. The signed range is 32768 to 32767, and the unsigned range is 0 to 65535.
mediumint[(M)] [UNSIGNED] [Zerofill]
A medium sized integer. The signed range is 8388608 to 8388607, and the unsigned range is 0 to 16777215.
int[(M)] [UNSIGNED] [Zerofill]
A normal size integer. The signed range is 2147483648 to 2147483647, and the unsigned range is 0 to 4294967295.
integer[(M)] [UNSIGNED] [Zerofill]
This is a synonym for int.
bigint[(M)] [UNSIGNED] [Zerofill]
A large integer. The signed range is 9223372036854775808 to 9223372036854775807, and the unsigned range is 0 to 18446744073709551615. Note that all arithmetic operations are done with signed bigint or double values, so you should not use a signed large integer greater than 9223372036854775807 (63 bits), except for the bit function! Note that when two parameters are integer,-, +, and * will use the bigint operation! This means that if you multiply by 2 large integers (or from functions that return integers), you can get unexpected results if the result is greater than 9223372036854775807. A floating-point number, which cannot be unsigned, can be <=24 for a single precision float, and the precision of a double-precision float is between 25 and 53, these types such as float and double are described immediately below. Float (X) has the same range as float and double, but the display dimensions and decimal places are undefined. In MySQL3.23, this is a real floating-point value. In earlier versions of MySQL, FLOAT (precision) always had 2 decimal places. This syntax is provided for ODBC compatibility.
float[(m,d)] [Zerofill]
A small (single precision) floating-point number. cannot be unsigned. The allowable values are -3.402823466E+38 to -1.175494351e-38,0 and 1.175494351E-38 to 3.402823466E+38. M is the display width and d is the number of digits. A float with no parameters or a parameter with <24 represents a single precision floating-point number.
double[(m,d)] [Zerofill]
A normal size (double precision) floating-point number. cannot be unsigned. The allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. M is the width of the display and D is the decimal digit. Double or float (x) with no arguments (< = x < = 53) represents a double-precision floating-point number.
DOUBLE precision[(m,d)] [Zerofill]
　
real[(m,d)] [Zerofill]
These are double synonyms.
decimal[(M[,d])] [Zerofill]
An uncompressed (unpack) floating-point number. cannot be unsigned. Behaves like a char column: "Uncompressed" means that the number is stored as a string, and each digit of the value uses one character. decimal point, and for negative numbers, the "-" symbol is not evaluated in M. If D is 0, the value will not have a decimal point or fractional fraction. The maximum range of decimal values is the same as double, but for a given decimal column, the actual range can be limited by the choice of M and D. If D is omitted, it is set to 0. If M is omitted, it is set to 10. Note that in MySQL3.22, the M parameter includes a symbol and a decimal point.
NUMERIC (M,D) [Zerofill]
This is a synonym for decimal.

Date and Time type
DATE
A date. The scope of support is ' 1000-01-01 ' to ' 9999-12-31 '. MySQL Displays the date value in ' YYYY-MM-DD ' format, but allows you to assign the value to the Date column using a string or a number.
Datetime
A combination of dates and times. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. MySQL displays datetime values in the ' yyyy-mm-dd HH:MM:SS ' format, but allows you to assign values to datetime columns using strings or numbers.
timestamp[(M)]
A time stamp. The range is ' 1970-01-01 00:00:00 ' to a certain time of 2037. MySQL Displays the timestamp value in Yyyymmddhhmmss, Yymmddhhmmss, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or omitted), 12, 8, or 6, But allows you to assign values to timestamp columns using strings or numbers. A timestamp column is useful for recording the date and time of an insert or update operation, because if you do not assign a value to it yourself, it is automatically set to the date and time of the most recent operation. You can set it to the current date and time by assigning it a null value. See 7.3.6 date and Time type.
Time
A time. The range is ' -838:59:59 ' to ' 838:59:59 '. MySQL displays the time value in ' HH:MM:SS ' format, but allows you to assign the value to the time column using a string or a number.
year[(2|4)]
The year of a 2-or 4-bit number format (the default is 4 bits). The allowable values are 1901 to 2155, and 0000 (4-bit year format) if you use 2 bits, 1970-2069 (70-69). MySQL Displays the year value in yyyy format, but allows you to assign a string or numeric value to the year column. (The year type is the new type in MySQL3.22.) ）
CHAR (M) [BINARY]
A fixed-length string that, when stored, always fills the right to the specified length with a space. The range of M is 1 ~ 255 characters. When the value is retrieved, the trailing space is deleted. Char values are sorted and compared according to the default character set in a case-insensitive manner, unless a binary keyword is given. National CHAR (short form nchar) is an ANSI-SQL way to define that the CHAR column should use the default character set. This is the default for MySQL. Char is an abbreviation for character.

The string (character) type.
[National] VARCHAR (M) [BINARY]
A variable-length string. Note: When the value is stored, the trailing spaces are removed (this is different from the ANSI SQL specification). The range of M is 1 ~ 255 characters. VarChar values are sorted and compared according to the default character set in a case-insensitive manner, unless a binary keyword value is given. See 7.7.1 Implicit column specifies the change. VARCHAR is an abbreviation of character varying.
Tinyblob
　
Tinytext
A blob or text column with a maximum length of 255 (2^8-1) characters. See 7.7.1 Implicit column specifies the change.
Blob
　
TEXT
A blob or text column with a maximum length of 65535 (2^16-1) characters. See 7.7.1 Implicit column specifies the change.
Mediumblob
　
Mediumtext
A blob or text column with a maximum length of 16777215 (2^24-1) characters. See 7.7.1 Implicit column specifies the change.
Longblob
　
Longtext
A blob or text column with a maximum length of 4294967295 (2^32-1) characters. See 7.7.1 Implicit column-specific changes
ENUM (' value1 ', ' value2 ',...)
Enumeration. A string object that has only one value, which is selected from the Value list ' value1 ', ' value2 ', ..., or null. An enum can have a maximum of 65535 different values.
SET (' value1 ', ' value2 ',...)
A collection. can have 0 or more values of a string object, where each table ' value1 ', ' value2 ', ... Elected. A set can have up to 64 members.

Sql-server
Supported field types:
Number type: Real numeric bit int decimal
Time Type: DateTime smalldatetime
Character type: varchar char nvarchar text
Oracle
Numeric type: Number
Time Type: Date
Character Type: VARCHAR2

Mysql
Maintenance statement for the table structure:
Create a table structure
CREATE TABLE table2 (finttable2id int,fstrtable2date date,fstrtable2code varchar (), Fstrtable2name varchar (50));

Modify Table Structure
Add a character field after sdeptname, field name "Finttable1id"
ALTER TABLE ' dept ' ADD ' finttable1id ' VARCHAR (one) not NULL after ' sdeptname ';

Maintenance statement for the record:
Insert Table Record statement:
Support format:
Insert INTO table1 select ' JPG00000101 ', ' J0203 ', ' must be from Value column '
Format is also supported:
Insert INTO table1 (finttable1id,fstrtable1code,fstrtable1name)
VALUES (' JPB000000077 ', ' G00098 ', ' See 7.7.1 implicit column ')
Format is also supported:
Insert INTO table1 select ' JPG00000123 ', ' Y0101 ', ' clear Collection ' union
All select ' JGH000000002 ', ' JGH0011 ', ' the site ';

Query table record statement:
SELECT * FROM table1 where fstrfieldname like '%0 ' (Fuzzy Lookup)
SELECT * FROM Tbale1 limit 10,30 (display query from line 10th to line 30th)

Execute the following statement
Select fstrfieldname+ ' AA ' from table1
The recordset displayed is as follows:
fstrfieldname+ ' AA '
0
0
0
and execute the following statement
Select Fstrfieldname from table1
Fstrfieldname
JPH0000000
JGP0000001
JPH0000100

To modify a table record statement:
Update table1 set fstrfieldname= ' SGML000000010 ' where fstrfieldname= ' JPG00000002 '
Update Dept ' Set finttable1id= ' SGML002 ' where sdeptid= ' A2A '


To delete a table record statement:
DELETE from table1 WHERE fstrtable1name = ' export '

Sql-server
Maintenance statement for the table structure:
Create a table Structure statement
CREATE TABLE table2 (finttable2id int,fstrtable2date date,fstrtable2code varchar (), Fstrtable2name varchar (50));

Modify a Table Structure statement
Add a character field after sdeptname, field name "Finttable1id"
ALTER TABLE ' dept ' ADD ' finttable1id ' VARCHAR (one) not NULL

Maintenance statement for the record:
Insert Table Record statement:
Support format:
Insert INTO table1 select ' JPG00000101 ', ' J0203 ', ' must be from Value column '
Format is also supported:
Insert INTO table1 (finttable1id,fstrtable1code,fstrtable1name)
VALUES (' JPB000000077 ', ' G00098 ', ' See 7.7.1 implicit column ')
Format is also supported:
Insert INTO table1 select ' JPG00000123 ', ' Y0101 ', ' clear Collection ' union
All select ' JGH000000002 ', ' JGH0011 ', ' the site ';

Query table record statement:
SELECT * FROM table1 where fstrfieldname like '%0 ' (Fuzzy Lookup)
Select Top * FROM table1 (query showing the first 10 rows)

Execute the following statement
Select fstrfieldname+ ' AA ' from table1
The recordset displayed is as follows:
fstrfieldname+ ' AA '
Jph0000000aa
Jph0000001aa
Jph0000100aa
and execute the following statement
Select Fstrfieldname from table1
Fstrfieldname
JPH0000000
JGP0000001
JPH0000100


To modify a table record statement:
Update table1 set fstrfieldname= ' export out ' where fstrfieldname = ' exporting '

To delete a table record statement:
Delete table1 where fstrfieldname = ' export '



Oracle
Maintenance statement for the table structure:
Create a table structure
CREATE TABLE table2 (finttable2id int,fstrtable2date date,fstrtable2code varchar (), Fstrtable2name varchar (50));

Modify Table Structure
Add a character field after sdeptname, field name "Finttable1id"
ALTER TABLE ' dept ' ADD ' finttable1id ' VARCHAR (one) not NULL

Maintenance statement for the record:
Insert statement:
Support format:
Insert INTO table1 select ' JPG00000101 ', ' J0203 ', ' must be from Value column '
Format is also supported:
Insert INTO table1 (finttable1id,fstrtable1code,fstrtable1name)
VALUES (' JPB000000077 ', ' G00098 ', ' See 7.7.1 implicit column ')
Format not supported:
Insert INTO table1 select ' JPG00000123 ', ' Y0101 ', ' clear Collection ' union
All select ' JGH000000002 ', ' JGH0011 ', ' the site ';

Query statement:
SELECT * FROM table1 where fstrfieldname like '%0 ' (Fuzzy Lookup)
Select Top * FROM table1 (query showing the first 10 rows)

Execute the following statement
Select fstrfieldname+ ' AA ' from table1
The recordset displayed is as follows:
fstrfieldname+ ' AA '
Aa
Aa
Aa
and execute the following statement
Select Fstrfieldname from table1
Fstrfieldname
JPH0000000
JGP0000001
JPH0000100

To modify a table record statement:
Update table1 set fstrfieldname= ' export out ' where fstrfieldname = ' exporting '

To delete a table record statement:
Delete table1 where fstrfieldname = ' export '