The difference between the three of SQL server,mysql,oracle

Oracle

Oracle can run on all major platforms (including Windows). Full support for all industry standards. Adopt a fully open strategy. Enables customers to choose the solution that best suits them. Full support for developers, Oracle parallel servers extend the capabilities of Windows NT by enabling a set of nodes to share work within the same cluster, providing a solution for high availability and highly scalable clusters. If Windows NT does not meet the needs, users can move the database to UNIX. Oracle's parallel servers have a fairly high level of integration into the clustering mechanisms of various UNIX platforms. Oracle obtains the highest certification level of ISO Standard certification. Oracle's highest performance, keeping the world of tpc-d and Tpc-c in an open platform Oracle Multi-level network computing, supporting a variety of industry standards, can be used in ODBC, JDBC, OCI and other network customers connect.
Oracle has advantages in compatibility, portability, connectivity, high productivity, and openness. Oracle products are standard SQL and tested by the U.S. National Institute of Standards and Technology (NIST).　Compatible with IBM sql/ds,db2,ingres,idms/r. Oracle's products can run on a wide range of hardware and operating system platforms. Can be installed in more than 70 different large, medium and small machines, can be in VMs, DOS, UNIX, Windows and other operating systems under the work. Can be connected with a variety of communication networks, supporting a variety of protocols (TCP/IP, DECnet, LU6.2, etc.). Provides a variety of development tools, can greatly facilitate the user for further development. Oracle's good compatibility, portability, connectivity, and high productivity are all well-open to Oracle RDBMS.
Oracle prices are relatively expensive. It is said that a set of genuine Oracle software has reached 6-digit prices in the market as early as the end of 2006. So if your project is not one of those super-big projects, just give up Oracle.

SQL Server

SQL Server is a Microsoft launch a set of products, it is easy to use, scalability, and related software integration of the advantages, and gradually become a Windows platform for database application development is a more ideal choice. SQL Server is currently one of the popular databases, it has been widely used in finance, insurance, electricity, administration and other database-related industries. Moreover, due to its ease of operation and friendly interface, won the favor of the majority of users, especially SQL Server and other databases, such as Access, FoxPro, Excel, etc. have a good ODBC interface, you can transfer the above database into SQL Server database, As a result, more and more readers are using SQL Server today.
SQL Server because it is Microsoft's products, but also has such a powerful function, so his influence is a few database systems in the larger, the user is more. It is generally used in conjunction with the. NET platform that is Microsoft products. Of course, all the other development platforms provide a connection to the database. Therefore, developing software to do a database with SQL Server is the right choice.

Mysql

MySQL does not support transactional processing, no views, no stored procedures and triggers, no user-defined functions on the database side, and cannot fully use standard SQL syntax.
The first thing that the experts heard from the database is that MySQL lacks transactions,rollbacks, and subselects functions. If you plan to use MySQL to write a bank, accounting application, or plan to maintain a number of counters of different classes that need to be linearly incremented at any time, you will lack transactions functionality. Under the existing release version of MySQL, please do not have any of these ideas. (Please note that MySQL's Beta 3.23.x series is now supported for transactions).
in a very necessary situation, the limitations of MySQL can be overcome by the efforts of a subset of developers. The main function you lose in MySQL is the subselect statement, which is what all the other databases have. In other words, this loss of function is a pain.
MySQL cannot handle complex relational database functions, such as subqueries (subqueries), although most subqueries can be rewritten as join
Another feature that MySQL does not provide support for IS transaction processing (transaction) and transaction commit/revocation (rollback). A transaction refers to a group or set of commands that are executed collectively as a unit. If a transaction cannot be completed, then none of the instructions in the whole transaction are actually executed. For commercial websites that have to deal with online orders, MySQL does not support this feature, which is really frustrating.　 But you can use Maxsql, a separate server, which can be used to support the transaction function through the external table.
foreign keys (foreignkey) and referential integrity restrictions (referentialintegrity) allow you to set constraints on the data in the table, and then add the constraints (constraint) to the data you specify. None of these MySQL features means that an application that relies on complex data relationships is not suitable for MySQL. When we say that MySQL does not support foreign keys, we are referring to the database referential integrity limit--MYSQL does not support the foreign key rules, of course, there is no support for the chain Delete (cascadingdelete) function.　 In short, if your job requires complex data correlation, then you should use the original access.
you will not find the storage process (StoredProcedure) and triggers (trigger) in MySQL. (For these features, the relative event process (eventprocedure) is provided in Access.)
Mysql+php+apache is known by software developers as the "PHP Gold portfolio."

The main differences between Oracle and MySQL Oracle: Client and command windows are user-determined content, Conn User_name/password; MySQL: Client and Command window, are determined by the database content, use datebase;

Can be created multi-database multiuser, individuals tend to oracle a database in the form of multiple users, MySQL multiple database multiple user forms (preferably one user per database)

Oracle is a large database and MySQL is a small and medium-sized database, Oracle has a market share of 40%,mysql only about 20%, while MySQL is open source and Oracle is very expensive. Oracle supports large concurrency, large traffic, and is the best tool for OLTP (on-line Transaction processing online transaction processing system). The space used for the installation is also very different, after MySQL installation is 152M and Oracle has about 3G, and when used, Oracle occupies a particularly large memory space and other machine performance. Oracle also has some differences with MySQL Operations Group function usage rules

The group functions in MySQL are freely available in the SELECT statement, but in Oracle if there is a group function in the query statement, the other column names must be processed by the group function, or the columns in the GROUP BY clause will otherwise be error-
eg
Select Name,count from user; This is not a problem in MySQL. There is a problem in Oracle.

Automatic growth of data type processing

MySQL has an auto-growing data type that does not manipulate this field when inserting records and automatically obtains data values. Oracle does not have an auto-growing data type, it needs to create an autogrow serial number, and the next value of the sequence number is assigned to this field when inserting the record.
The name of the CREATE sequence serial number (preferably table name + serial number tag) INCREMENT by 1 START with 1 MAXVALUE 99999 CYCLE NOCACHE;
Where the maximum value is determined by the length of the field, if the defined auto-grow serial number is no. (6), the maximum value is 999999
Insert statement inserts the field value: The name of the serial number. Nextval

Processing of single quotation marks

In MySQL, you can use double quotes to wrap strings, and Oracle can only wrap strings with single quotes. Single quotation marks must be replaced before inserting and modifying strings: Replace all occurrences of a single quotation mark with two single quotes.

Processing of page-turn SQL statements

mysql The SQL statement that handles paging is relatively simple, with the limit start position, the number of records In PHP, you can also use seek to position the result set. Oracle's SQL statements that handle paging are cumbersome. Each result set has only one rownum field indicating its location and can only be used with rownum<100, not rownum>80.

statement one:
select ID, [Field_name,...] From table_name where ID in (SELECT ID from (select ROWNUM as Numrow, ID from table_name where condition 1 ORDER by condition 2) where NU Mrow > Numrow <) ORDER by condition 3;

Statement two:
SELECT * FROM ((select ROWNUM as Numrow, c.* from (SELECT [Field_name,...] From table_name WHERE Condition 1 order by Condition 2) c) where Numrow > B and Numrow < 3) Order by conditions;

Processing of long strings

The processing of long strings Oracle also has its special place. Insert and update when the maximum operand length is less than or equal to 4,000 single bytes, if you want to insert a longer string, consider the field with the Clob type, the method borrows the Dbms_lob package from the Oracle. Be sure to do non-null and length judgments before inserting a modified record, and the field values that cannot be empty and the value beyond the length field should be warned to return the last action.

Processing of date fields

The MySQL date field is dated and time two, the Oracle Date field is only date, contains the date of the month and minute information, the system time of the current database is sysdate, accurate to the second, or the string is converted into a date-type function to_date (' 2001-08-01 ', ' YYYY-MM-DD ') year-month-day 24 hours: minutes: The format of the second YYYY-MM-DD HH24:MI:SS to_date () There are many kinds of date formats that you can refer to Oracle DOC.

Date field converted to String function to_char (' 2001-08-01 ', ' yyyy-mm-dd HH24:MI:SS ')
The mathematical formula for a date field differs greatly. MySQL found 7 days from current time with Date_field_name > Subdate (Now (), INTERVAL 7 day) Oracle found 7 days from current time with Date_field_name >SYSDATE-7;
Several functions for inserting the current time in MySQL are: the Now () function returns the current datetime with ' Yyyy-mm-dd HH:MM:SS ' and can be stored directly in the DateTime field. Curdate () returns today's date in the format ' Yyyy-mm-dd ', which can be stored directly in the Date field. Curtime () returns the current time in the format ' HH:MM:SS ', which can be stored directly in the Date field. Example: INSERT INTO TableName (fieldname) VALUES (now ())
And the current time in Oracle is Sysdate

Handling of empty characters

MySQL's non-empty fields also have empty content, and Oracle defines non-empty fields that are not allowed to have empty content. The Oracle table structure is defined by not NULL for MySQL, and errors are generated when the data is being directed. As a result, null characters are judged on the data, and if NULL or NULL, it is necessary to change it to a space string.

Fuzzy Comparison of strings

MySQL uses the field name like% ' String% ', Oracle can also use the field name like% ' String% ' but this method can not use the index, the speed is not fast, with a string comparison function InStr (field name, ' string ') >0 will be more accurate results found.

Programs and functions, note the release of result sets and pointers after the work of the database is completed.

Primary key

MySQL generally uses the automatic growth type, when the table is created as long as the primary key of the specified table is auto increment, when inserting a record, no need to specify the primary key value of the record, MySQL will automatically grow; Oracle does not have an auto-grow type, the primary key is generally used by the sequence, The next value of the sequence number is paid to the field when the record is inserted, but only if the ORM framework is a native primary key generation strategy.

Oracle implements most of the features in Ansiisql, such as the isolation level of transactions, propagation characteristics, and so on, where MySQL is still relatively weak

The difference between the three of SQL server,mysql,oracle