Oracle vs. MySQL vs. SQL Server: A Comparison of Popular RDBMS
Since their introduction in the 1980s,
relational database management systems (RDBMS) have become the standard
database type for a variety of industries. As their name implies, these systems
are based on the relational model that organizes data into groups of tables
referred to as relations. This post explores the history and features of three
popular RDBMS: Oracle, MySQL, and SQL Server. The comparison should help you
understand the differences between the systems, and if considering implementing
a RDBMS, provide you with information that will help make a decision. If you
are interested in learning more about how RDBMS work, there are many courses
available. For example, an Oracle getting started course can introduce you to this platform and
teach you detailed information about how it works.
Summary Feature Comparison
The following table includes information
about the Oracle, MySQL, and SQL Server databases and how they compare.
|
Oracle
History
IBM was the first company to develop a
RDBMS, however, Oracle Corporation made history in 1980 by releasing its RDBMS,
Oracle, for commercial use. Just a few years later the company would release a
version of its system for IBM computers. Since its exhibition to the RDBMS
market, Oracle has consistently led the way. According to Gartner, Oracle owned
nearly 50% of the RDBMS market in 2011. In addition to opening up the
commercial market for RDBMS, the Oracle Corporation also was the first company
to develop a commercial-level version of SQL that was designed to manipulate
data in a RDBMS using (at that time) queries and joins.
Features
The first “real” release of the Oracle RDBMS
was Oracle 2. This system supported only basic SQL features, and it was written
in an assembly language. The following year, and for the next 10 years or so,
Oracle Corporation released updates to its flagship database. Probably one of
the reasons the Oracle RDBMS has managed to remain at the top of mighty RDBMS
is linked to its product updates that are closely tied to changes in the
market. Database buzzwords such as “scalable”, “programmable”, “distributed”,
and “portable” are also tied to Oracle release. For example, in 1985 support
for a client-server model was added in anticipation of a growing acceptance of
network communication. As the Internet paved the way for the Digital Era, the
Oracle RDBMS was updated to include a native Java virtual machine (JVM).
Oracle Database 12c is the most recent
release of the RDBMS, and it includes the following features:
·
New data redaction to enhance security of
sensitive data
·
Introduction of Oracle Advanced Analytics
platform
·
New database handling for archiving Flash Data
Archive (FDA)
·
Support for integrating with operating system
processor groups
·
Support for data pump for database consolidation
·
Several enhancements to Oracle Application
Express, a rapid-development tool that allows users to develop Web apps using
SQL and/or PL/SQL.
·
Advanced network compression to enhance
performance
If you’re interested in how you code with
Oracle SQL, an introduction to Oracle SQL course can provide the basics of the
language.
SQL Server
History
Microsoft SQL Server entered the RDBMS
market as a serious competitor in the mid 1990s when Microsoft purchased it
from Sybase, and then released version 7.0. The companies originally
worked together to develop the platform to run on the IBM OS/2 platform. However,
Microsoft eventually developed its own operating system (Windows NT), and
wanted to work solo to create a database management for it. It would take
several years for the Microsoft and Sybase to completely sever their ties.
Sybase eventually changed it’s product name so that it would be completely
different from the product sold to Microsoft. Microsoft SQL Server version 4.2
was the initial release.
Features
In 2000, Microsoft released SQL Server 2000.
The release was a significant milestone for the company because it marked the
first release of the product where the original Sybase code was completely
replaced. In the same vein as Oracle Corporation, Microsoft has attempted to
enhance SQL Server to keep up with changing technology. SQL Server 2005 is an
example. The eXtensible Markup Language (XML) received stamp of approval from
W3C and started gaining ground in the late 1990s. One of the major new features
of SQL Server 2005 was support for XML data. Other notable features of the
flagship product include the introduction of SQL Server Always On (data
management technology to decrease user downtime), support for structured and
semi-structured data, enhanced compression, and several add-ons to support
other products on the market. SQL Server 2012 was proclaimed as the last
release to include native support for OLE. A SQL Server 2012 essentials course can offer more information
about this platform and how to use it.
SQL Server 2014 is the latest release of SQL
Server and includes the following features:
·
Introduction of In-Memory Online Traction
Processing (OLTP), an embedded feature that allows sophisticated database
management to enhance performance
·
New solutions to handle disaster recovery
·
Updated version of SQL Server Data Tools for
Business Intelligence (SSDT BI)
MySQL
History
There are two unique aspects of MySQL in
comparison to Oracle and SQL Server: it was not originally developed for
commercial use and it is an open source database. The database platform was a
happenstance as the individuals who developed it started out trying to use mSQL
to interface with their database tables, and decided they needed a much more
powerful interface. The initial phase of MySQL used an API leveraged from mSQL,
enhancements that increased speed considerably, and other features that
included the InnoDB storage engine, full text search, portability, and
internationalization.
Another difference of the MySQL platform in
comparison to the other two is that it is open source. The Digital Age spawned
a movement in software development collaboration that has blossomed into a
competitive market for databases and other software. According to market
reports, there is an excess of 10 million installations of MySQL, which
indicates it is quickly moving into the enterprise space.
The ownership of MySQL has transitioned from
the product’s humble beginnings. The two most notable acquisitions are (1) in
2008 when Sun Microsystems acquired MySQL AB, the company that created MySQL,
and (2) in 2010 when Oracle acquired Sun Microsystems.
Features
Oracle and SQL Server are considered tools
that favor users with large enterprise systems, while MySQL is considered a
tool that appeals most often to individuals interested in managing databases
associated with their websites. As with Oracle and SQL Server, MySQL has
released updates to its software just about every year. The original version
was developed in the mid 1990s. The most notable changes to MySQL was in 2010,
the time of the last acquisition in 2010. The enhancements to this release (GA
release 5.5) included semisynchronous replication, custom partitioning,
improved support for SMP and updates to the InnoDB I/O subsystem. If you are
just learning about MySQL, you may be interested in learning more details about
it. A MySQL database for beginners course is a good place to start your
education.
Conclusion
This comparison shows just how close the
databases are in three key areas. Considering your unique situation is probably
more relevant for deciding which one to implement than determining which one is
best.
Comparison
Of SQL, MySQL and ORACLE
Description
Microsoft SQL
Server
MySQL
ORACLE
DB Engine
Ranking
Rank
3Score1207.80
Rank
2Score1309.10
Rank
1Score1502.74
Developer
Microsoft
Oracle
Oracle
Technical
documentation
Initial release
First release in
1989 based on Ingress(1974),Sybase(1987)
1995
1980
Implementation
language
C++
C and C++
C and C++
Server operating
systems
Windows Server
,Windows Clients
FreeBSD
Linux
OS X
Solaris
Windows
AIX
HP-UX
Linux
OS X
Solaris
Windows
z/OS
Database model
RDBMS
RDBMS
RDBMS
Data scheme
yes
yes
yes
Typing
yes
yes
yes
Secondary indexes
yes
yes
yes
SQL
yes
yes
yes
APIs and other
access methods
OLE DB
Tabular Data Stream (TDS)
ADO.NET
JDBC
ODBC
ADO.NET
JDBC
ODBC
ODP.NET
Oracle Call Interface (OCI)
JDBC
ODBC
Supported
programming languages
.Net
Java
PHP
Python
Ruby
Visual Basic
Ada
C
C#
C++
D
Eiffel
Erlang
Haskell
Java
Objective-C
OCaml
Perl
PHP
Python
Ruby
Scheme
Tcl
C
C#
C++
Clojure
Cobol
Eiffel
Erlang
Fortran
Groovy
Haskell
Java
JavaScript
Lisp
Objective C
OCaml
Perl
PHP
Python
R
Ruby
Scala
Tcl
Visual Basic
Server-side scripts
Transact-SQL and
.NET languages
Yes(proprietary
syntax)
PL/SQL
Triggers
yes
yes
yes
Partitioning methods
tables can be
distributed across several files (horizontal partitioning), but no sharding
horizontal
partitioning in MySQL Cluster
horizontal
partitioning
Replication methods
yes, but depending
on the SQL-Server Edition
Master-master
replication
Master-slave replication
MySQL Cluster
Master-master
replication
Master-slave replication
MapReduce
no
no
no
Foreign keys
yes
yes
yes
Transaction concepts
ACID
ACID
ACID
Concurrency
yes
yes
yes
Durability
yes
yes
yes
User concepts
Users with
fine-grained authorization concept
Users with
fine-grained authorization concept
Users with
fine-grained authorization concept
License
commercial
Open Source
commercial
Typical Applications
SharePoint, SCOM,
SCCM, WSUS
Joomla, WordPress,
MyBB, phpBB, Drupal, many open-source
OBI, SAP
Current Version
SQL Server 2012
5.5.x
11g
Job Scheduling
Yes (Agent)
v5.1 (Event
Scheduler)
Yes (Oracle
Scheduler)
Data Type Comparison
Max DB Size
524,258 TB (32,767
files * 16 TB max file size)
Unlimited
Unlimited (4 GB *
block Size per tablespace)
Max Table Size
524,258 TB
MyISAM ( limits 256
TB)
InnoDB limits (64 TB)
4 GB * block Size (
with BIGFILE tablespace)
Max Row Size
Unlimited
64 KB
8 KB
Max Cols per
Row
30000
4096
1000
Max Blob/Clob
Size
2 GB
4 GB (longtext long
blob)
Unlimited
Max Char Size
2 GB
64 KB (text)
4000 B
Max Number
Size
126 Bits
64 bits
126 Bits
Min Date Value
0001
1000
-4712
Max Date Value
9999
9999
9999
Max Col name
size
128
64
30
Union
Yes
Yes
Yes
Intersect
Yes ( 2005 and
beyond)
No
Yes
Except
Yes ( 2005 and
beyond)
No
Yes, Via MINUS
Inner Joins
Yes
Yes
Yes
Outer Joins
Yes
Yes
Yes
Inner Selects
Yes
Yes
Yes
Merge Joins
Yes
Yes
Yes
Blobs and clobs
Yes
Yes
Yes
Common Table
Expressions
Yes
No
Yes. Recursive
CTEsIntroduced in 11g R2
Windowing
Functions
Yes
No
Yes
Parallel Query
Yes
No
Yes
Type System
Static
Static
Static + Dynamic
(through ANYDATA)
Integer
TINYINT,
SMALLINT,
INT,
BIGINT
TINYINT (8-bit),
SMALLINT (16-bit), MEDIUMINT (24-bit), INT (32-bit), BIGINT (64-bit)
NUMBER
Floating Point
FLOAT,
REAL
FLOAT (32-bit),
DOUBLE (aka REAL) (64-bit)
BINARY_FLOAT,
BINARY_DOUBLE
Decimal
NUMERIC,
DECIMAL,
SMALLMONEY,
MONEY
DECIMAL
NUMBER
String
CHAR,
VARCHAR,
TEXT,
NCHAR,
NVARCHAR,
NTEXT
CHAR, BINARY,
VARCHAR, VARBINARY, TEXT TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
CHAR, VARCHAR2,
CLOB, NCLOB, NVARCHAR2, NCHAR
Binary
BINARY,
VARBINARY,
IMAGE,
FILESTREAM
TINYBLOB, BLOB,
MEDIUMBLOB, LONGBLOB
BLOB, RAW, LONGRAW,
BFILE
Date/Time
DATE,
DATETIMEOFFSET,
DATETIME2,
SMALLDATETIME,
DATETIME,
TIME
DATETIME, DATE,
TIMESTAMP, YEAR
DATE, TIMESTAMP
(with/without TIMEZONE), INTERVAL
Boolean
BIT
BOOLEAN (aka BOOL) =
synonym for TINYINT
N/A
Other
CURSOR,
TIMESTAMP,
HIERARCHID,
UNIQUEIDENTIFIER,
SQL_VARIANT,
xml,
TABLE
ENUM, SET, GIS data
types (Geometry, Point, Curve, LineString, Surface, Polygon,
GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface,
MultiPolygon)
SPATIAL, IMAGE,
AUDIO, VIDEO, DICOM, XMLType
Currency
MONEY,SMALLMONEY
NA
NA
Data Domain
Yes
No
Yes
Cursor
Yes
Yes
Yes
Trigger
Yes
Yes
Yes
Function
Yes
Yes
Yes
Procedure
Yes
Yes
Yes
External Routine
Yes
Yes
Yes
Range
Yes
Yes
Yes
Hash
No
Yes
Yes
Composite(Range+Hash)
No
Yes
Yes
List
No
Yes
Yes
String Manipulation
Functions
LEFT
LEFT
(Use SUBSTR)
String Manipulation
Functions
RIGHT
RIGHT
(Use SUBSTR)
String Manipulation
Functions
SUBSTRING
SUBSTRING
SUBSTR
String Manipulation
Functions
CHARINDEX
INSTR
INSTR
String Manipulation
Functions
LEN
LENGTH
LENGTH
String Manipulation
Functions
UPPER
UCASE/UPPER
UPPER
String Manipulation
Functions
LOWER
LCASE/LOWER
LOWER
=
Yes
Yes
Yes
!=
Yes
Yes
Yes
^=
No
No
Yes
<>
Yes
Yes
Yes
>
Yes
Yes
Yes
>=
Yes
Yes
Yes
!<
Yes
No
No
<
Yes
Yes
Yes
<=
Yes
Yes
Yes
!>
Yes
No
No
NA
No
No
Yes
%
With LIKE operator
No
No
_
With LIKE operator
No
No
^
No
With REGEXP or RLIKE
No
$
No
With REGEXP or RLIKE
No
.
No
With REGEXP or RLIKE
No
*
No
With REGEXP or RLIKE
No
{n}
No
With REGEXP or RLIKE
No
Table Name, Field
Name
[name]
?
“name”
String Concatenation
FirstName + ‘ ‘ +
Lastname
FirstName + ‘ ‘ +
Lastname
FirstName || ‘ ‘ ||
LastName
Date
?
?
?
TOP
SELECT TOP RowCnt ColA,ColB
FROM TABLE;
SELECT ColA,ColB
FROM TABLE LIMIT StartRecord,TotalRecords;
SELECT ColA,ColB
FROM TABLE WHERE ROWNUM <=5;
Square root
SQRT
SQRT
SQRT
Round UP AND
DOWN
FLOOR() – Round Down
CEILING() – Round UP
ROUND() – Round Nearest.
FLOOR() – Round Down
CEILING() – Round UP
ROUND() – Round Nearest.
FLOOR() – Round Down
CEILING() – Round UP
ROUND() – Round Nearest.
Casting to
String
STR(float,length,precision)
Ex: STR(43.2461,5,2) is ‘43.25’
MySQL is weakly
typed and will generally try to convert values as necessary. If you need to
convert a value explicitly to a string, you can simply concatenate the value
with an empty string: CONCAT(23.52,”) — returns ‘23.52’
TO_CHAR(333,’000.0′)
is ‘0333.0’
Casting to
Numbers
CAST()/CONVERT()
Because of Weak
typing MySQL allows you to convert strings or dates to numbers just by adding
zero to them
Example ‘23.5’ + 0 returns 23.5 as a numeric type
TO_NUMBER(”,”)
FULL OUTER
JOINS
Yes
No
Yes
Comparison
Of SQL, MySQL and ORACLE
|
Description
|
Microsoft SQL
Server
|
MySQL
|
ORACLE
|
|
DB Engine
Ranking
|
Rank
3Score1207.80
|
Rank
2Score1309.10
|
Rank
1Score1502.74
|
|
Developer
|
Microsoft
|
Oracle
|
Oracle
|
|
Technical
documentation
|
|||
|
Initial release
|
First release in
1989 based on Ingress(1974),Sybase(1987)
|
1995
|
1980
|
|
Implementation
language
|
C++
|
C and C++
|
C and C++
|
|
Server operating
systems
|
Windows Server
,Windows Clients
|
FreeBSD
Linux OS X Solaris Windows |
AIX
HP-UX Linux OS X Solaris Windows z/OS |
|
Database model
|
RDBMS
|
RDBMS
|
RDBMS
|
|
Data scheme
|
yes
|
yes
|
yes
|
|
Typing
|
yes
|
yes
|
yes
|
|
Secondary indexes
|
yes
|
yes
|
yes
|
|
SQL
|
yes
|
yes
|
yes
|
|
APIs and other
access methods
|
OLE DB
Tabular Data Stream (TDS) ADO.NET JDBC ODBC |
ADO.NET
JDBC ODBC |
ODP.NET
Oracle Call Interface (OCI) JDBC ODBC |
|
Supported
programming languages
|
.Net
Java PHP Python Ruby Visual Basic |
Ada
C C# C++ D Eiffel Erlang Haskell Java Objective-C OCaml Perl PHP Python Ruby Scheme Tcl |
C
C# C++ Clojure Cobol Eiffel Erlang Fortran Groovy Haskell Java JavaScript Lisp Objective C OCaml Perl PHP Python R Ruby Scala Tcl Visual Basic |
|
Server-side scripts
|
Transact-SQL and
.NET languages
|
Yes(proprietary
syntax)
|
PL/SQL
|
|
Triggers
|
yes
|
yes
|
yes
|
|
Partitioning methods
|
tables can be
distributed across several files (horizontal partitioning), but no sharding
|
horizontal
partitioning in MySQL Cluster
|
horizontal
partitioning
|
|
Replication methods
|
yes, but depending
on the SQL-Server Edition
|
Master-master
replication
Master-slave replication MySQL Cluster |
Master-master
replication
Master-slave replication |
|
MapReduce
|
no
|
no
|
no
|
|
Foreign keys
|
yes
|
yes
|
yes
|
|
Transaction concepts
|
ACID
|
ACID
|
ACID
|
|
Concurrency
|
yes
|
yes
|
yes
|
|
Durability
|
yes
|
yes
|
yes
|
|
User concepts
|
Users with
fine-grained authorization concept
|
Users with
fine-grained authorization concept
|
Users with
fine-grained authorization concept
|
|
License
|
commercial
|
Open Source
|
commercial
|
|
Typical Applications
|
SharePoint, SCOM,
SCCM, WSUS
|
Joomla, WordPress,
MyBB, phpBB, Drupal, many open-source
|
OBI, SAP
|
|
Current Version
|
SQL Server 2012
|
5.5.x
|
11g
|
|
Job Scheduling
|
Yes (Agent)
|
v5.1 (Event
Scheduler)
|
Yes (Oracle
Scheduler)
|
|
Data Type Comparison
|
|||
|
Max DB Size
|
524,258 TB (32,767
files * 16 TB max file size)
|
Unlimited
|
Unlimited (4 GB *
block Size per tablespace)
|
|
Max Table Size
|
524,258 TB
|
MyISAM ( limits 256
TB)
InnoDB limits (64 TB) |
4 GB * block Size (
with BIGFILE tablespace)
|
|
Max Row Size
|
Unlimited
|
64 KB
|
8 KB
|
|
Max Cols per
Row
|
30000
|
4096
|
1000
|
|
Max Blob/Clob
Size
|
2 GB
|
4 GB (longtext long
blob)
|
Unlimited
|
|
Max Char Size
|
2 GB
|
64 KB (text)
|
4000 B
|
|
Max Number
Size
|
126 Bits
|
64 bits
|
126 Bits
|
|
Min Date Value
|
0001
|
1000
|
-4712
|
|
Max Date Value
|
9999
|
9999
|
9999
|
|
Max Col name
size
|
128
|
64
|
30
|
|
Union
|
Yes
|
Yes
|
Yes
|
|
Intersect
|
Yes ( 2005 and
beyond)
|
No
|
Yes
|
|
Except
|
Yes ( 2005 and
beyond)
|
No
|
Yes, Via MINUS
|
|
Inner Joins
|
Yes
|
Yes
|
Yes
|
|
Outer Joins
|
Yes
|
Yes
|
Yes
|
|
Inner Selects
|
Yes
|
Yes
|
Yes
|
|
Merge Joins
|
Yes
|
Yes
|
Yes
|
|
Blobs and clobs
|
Yes
|
Yes
|
Yes
|
|
Common Table
Expressions
|
Yes
|
No
|
Yes. Recursive
CTEsIntroduced in 11g R2
|
|
Windowing
Functions
|
Yes
|
No
|
Yes
|
|
Parallel Query
|
Yes
|
No
|
Yes
|
|
Type System
|
Static
|
Static
|
Static + Dynamic
(through ANYDATA)
|
|
Integer
|
TINYINT,
SMALLINT, INT, BIGINT |
TINYINT (8-bit),
SMALLINT (16-bit), MEDIUMINT (24-bit), INT (32-bit), BIGINT (64-bit)
|
NUMBER
|
|
Floating Point
|
FLOAT,
REAL |
FLOAT (32-bit),
DOUBLE (aka REAL) (64-bit)
|
BINARY_FLOAT,
BINARY_DOUBLE
|
|
Decimal
|
NUMERIC,
DECIMAL, SMALLMONEY, MONEY |
DECIMAL
|
NUMBER
|
|
String
|
CHAR,
VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXT |
CHAR, BINARY,
VARCHAR, VARBINARY, TEXT TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT
|
CHAR, VARCHAR2,
CLOB, NCLOB, NVARCHAR2, NCHAR
|
|
Binary
|
BINARY,
VARBINARY, IMAGE, FILESTREAM |
TINYBLOB, BLOB,
MEDIUMBLOB, LONGBLOB
|
BLOB, RAW, LONGRAW,
BFILE
|
|
Date/Time
|
DATE,
DATETIMEOFFSET, DATETIME2, SMALLDATETIME, DATETIME, TIME |
DATETIME, DATE,
TIMESTAMP, YEAR
|
DATE, TIMESTAMP
(with/without TIMEZONE), INTERVAL
|
|
Boolean
|
BIT
|
BOOLEAN (aka BOOL) =
synonym for TINYINT
|
N/A
|
|
Other
|
CURSOR,
TIMESTAMP, HIERARCHID, UNIQUEIDENTIFIER, SQL_VARIANT, xml, TABLE |
ENUM, SET, GIS data
types (Geometry, Point, Curve, LineString, Surface, Polygon,
GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface,
MultiPolygon)
|
SPATIAL, IMAGE,
AUDIO, VIDEO, DICOM, XMLType
|
|
Currency
|
MONEY,SMALLMONEY
|
NA
|
NA
|
|
Data Domain
|
Yes
|
No
|
Yes
|
|
Cursor
|
Yes
|
Yes
|
Yes
|
|
Trigger
|
Yes
|
Yes
|
Yes
|
|
Function
|
Yes
|
Yes
|
Yes
|
|
Procedure
|
Yes
|
Yes
|
Yes
|
|
External Routine
|
Yes
|
Yes
|
Yes
|
|
Range
|
Yes
|
Yes
|
Yes
|
|
Hash
|
No
|
Yes
|
Yes
|
|
Composite(Range+Hash)
|
No
|
Yes
|
Yes
|
|
List
|
No
|
Yes
|
Yes
|
|
String Manipulation
Functions
|
LEFT
|
LEFT
|
(Use SUBSTR)
|
|
String Manipulation
Functions
|
RIGHT
|
RIGHT
|
(Use SUBSTR)
|
|
String Manipulation
Functions
|
SUBSTRING
|
SUBSTRING
|
SUBSTR
|
|
String Manipulation
Functions
|
CHARINDEX
|
INSTR
|
INSTR
|
|
String Manipulation
Functions
|
LEN
|
LENGTH
|
LENGTH
|
|
String Manipulation
Functions
|
UPPER
|
UCASE/UPPER
|
UPPER
|
|
String Manipulation
Functions
|
LOWER
|
LCASE/LOWER
|
LOWER
|
|
=
|
Yes
|
Yes
|
Yes
|
|
!=
|
Yes
|
Yes
|
Yes
|
|
^=
|
No
|
No
|
Yes
|
|
<>
|
Yes
|
Yes
|
Yes
|
|
>
|
Yes
|
Yes
|
Yes
|
|
>=
|
Yes
|
Yes
|
Yes
|
|
!<
|
Yes
|
No
|
No
|
|
<
|
Yes
|
Yes
|
Yes
|
|
<=
|
Yes
|
Yes
|
Yes
|
|
!>
|
Yes
|
No
|
No
|
|
NA
|
No
|
No
|
Yes
|
|
%
|
With LIKE operator
|
No
|
No
|
|
_
|
With LIKE operator
|
No
|
No
|
|
^
|
No
|
With REGEXP or RLIKE
|
No
|
|
$
|
No
|
With REGEXP or RLIKE
|
No
|
|
.
|
No
|
With REGEXP or RLIKE
|
No
|
|
*
|
No
|
With REGEXP or RLIKE
|
No
|
|
{n}
|
No
|
With REGEXP or RLIKE
|
No
|
|
Table Name, Field
Name
|
[name]
|
?
|
“name”
|
|
String Concatenation
|
FirstName + ‘ ‘ +
Lastname
|
FirstName + ‘ ‘ +
Lastname
|
FirstName || ‘ ‘ ||
LastName
|
|
Date
|
?
|
?
|
?
|
|
TOP
|
SELECT TOP RowCnt ColA,ColB
FROM TABLE;
|
SELECT ColA,ColB
FROM TABLE LIMIT StartRecord,TotalRecords;
|
SELECT ColA,ColB
FROM TABLE WHERE ROWNUM <=5;
|
|
Square root
|
SQRT
|
SQRT
|
SQRT
|
|
Round UP AND
DOWN
|
FLOOR() – Round Down
CEILING() – Round UP ROUND() – Round Nearest. |
FLOOR() – Round Down
CEILING() – Round UP ROUND() – Round Nearest. |
FLOOR() – Round Down
CEILING() – Round UP ROUND() – Round Nearest. |
|
Casting to
String
|
STR(float,length,precision)
Ex: STR(43.2461,5,2) is ‘43.25’ |
MySQL is weakly
typed and will generally try to convert values as necessary. If you need to
convert a value explicitly to a string, you can simply concatenate the value
with an empty string: CONCAT(23.52,”) — returns ‘23.52’
|
TO_CHAR(333,’000.0′)
is ‘0333.0’
|
|
Casting to
Numbers
|
CAST()/CONVERT()
|
Because of Weak
typing MySQL allows you to convert strings or dates to numbers just by adding
zero to them
Example ‘23.5’ + 0 returns 23.5 as a numeric type |
TO_NUMBER(”,”)
|
|
FULL OUTER
JOINS
|
Yes
|
No
|
Yes
|
No comments:
Post a Comment