Oracle
Oracle runs on all major platforms (including Windows). Fully supports all industry standards. Adopt a fully open strategy. Customers can be enabled to choose the most suitable solution. Full support for developers, Oracle Parallel Server extends the power of Windows NT by enabling a group of nodes to share work in the same cluster, providing a solution for highly available and scalable clusters. If Windows NT is not sufficient, users can move the database to UNIX. Oracle’s parallel servers are highly integrated with the clustering mechanisms of various UNIX platforms. Oracle is certified to ISO standards, the highest level of certification. Oracle has the highest performance, maintaining the world record of TPC-D and TPC-C under the open platform, Oracle multi-level network calculation, supporting a variety of industry standards, can use ODBC, JDBC, OCI and other network customers to connect.
Oracle also has advantages in compatibility, portability, connectivity, high productivity, and openness. Oracle products use standard SQL and are tested by the National Institute of Standards and Technology (NIST). Compatible with IBM SQL/DS, DB2, INGRES, IDMS/R, and more. Oracle products run on a wide range of hardware and operating system platforms. Can be installed in more than 70 different large, medium and small machine; It can work in VMS, DOS, UNIX, WINDOWS and other operating systems. Can be connected with a variety of communication networks, support various protocols (TCP/IP, DECnet, LU6.2, etc.). Provides a variety of development tools, can greatly facilitate the user to further development. Oracle’s good compatibility, portability, connectivity and high productivity make Oracle RDBMS open.
Oracle price is relatively expensive. A copy of Oracle software was said to have been on the market for six figures at the end of 2006. So if your project isn’t super big, just drop Oracle.
SQL Server
SQL Server is Microsoft launched a set of products, it has the advantages of convenient use, good scalability, and high degree of integration with related software, and gradually become one of the ideal choice for database application development under Windows platform. SQLServer is one of the popular databases, it has been widely used in finance, insurance, power, administrative management and other database-related industries. Moreover, because of its easy operation and friendly interface, has won the favor of the majority of users, especially SQLServer and other databases, such as Access, FoxPro, Excel and other good ODBC interface, SQLServer can be converted from the above database, so SQLServer is now being used by more and more readers.
SQL Server is Microsoft’s product, and has such a powerful function, so his influence is relatively large in several database systems, users are more. It is usually used in conjunction with the.NET platform, which is also a Microsoft product. Other development platforms, of course, provide a database connection mode related to it. Therefore, developing software using SQL Server as a database is a correct choice.
MySQL
MySQL does not support transaction processing, has no views, stored procedures and triggers, has no user-defined functions on the database side, and cannot fully use standard SQL syntax.
One of the first things you hear from database cognoscences is that MySQL lacks transactions, rollbacks, and subselects. If you plan to use MySQL to write an application about banking or accounting, or to maintain a number of different classes of counters that require linear increments all the time, you will lack transactions. Under the current release of MySQL, don’t get any of these ideas. (Note that the 3.23.x series of MySQL beta now supports transactions).
If necessary, the limitations of MySQL can be overcome through the efforts of some developers. The main feature you lose in MySQL is the subselect statement, which all other databases have. In other words, this lost function is a pain.
MySQL cannot handle complex relational database functions, such as subqueries, While most subqueries can be rewritten to join, another feature MySQL does not support is transaction and commit/rollback. A transaction is a group or set of commands that are executed together as a unit. If a transaction cannot be completed, then none of the instructions in the entire transaction are actually executed. For commercial sites that have to process online orders, it’s disappointing that MySQL doesn’t support this feature. However, MaxSQL, a separate server, can be used to support transactions through a plug-in table.
Foreign keys and reference integrity allow you to specify constraints between data in a table and then add constraints to the data you specify. These features that MySQL does not have mean that an application that relies on complex data relationships is not suitable to use MySQL. When we say MySQL does not support foreign keys, we are referring to database referential integrity restrictions –MySQL has no rules for supporting foreign keys, and certainly no support for cascadingdelete. In short, if your work requires complex data associations, use old Access.
You won’t find store procedures or triggers in MySQL either. For these functions, a relative eventprocedure is provided in Access.
MySQL+ PHP + Apache are called the “GOLDEN combination of PHP” by software developers.
Key differences between Oracle and MySQL
-> conn user_name/password;
MySQL: client and command window are determined by database -> use datebase;
Both can create multiple databases and multiple users. Personally, I prefer Oracle with multiple users in one database and MySQL with multiple users in multiple databases (preferably one user for each database).
Oracle is a large database while MySQL is a small and medium-sized database. Oracle has a market share of 40% while MySQL only has about 20%. Meanwhile, MySQL is open source and Oracle has a very high price.
Oracle is the best tool for ONLINE Transaction Processing (OLTP) because it supports large amounts of concurrency and traffic.
The space used for installation is also very different. After MySQL is installed, it is only 152M, while Oracle has about 3G, and Oracle takes up very large memory space and other machine performance when using it.
Oracle also operates differently from MySQL
Group function usage rules
In MySQL, the group function can be used arbitrarily in the SELECT statement, but in Oracle, if there is a group function in the query, the other column names must be processed by the group function or columns in the group by clause otherwise an error is reported
Eg:
Select name,count(money) from user; This is fine in MySQL but not in Oracle.
Automatic growth of data type processing
MySQL has auto-growing data types. When inserting a record, you do not need to operate this field, and the data value is automatically obtained. Oracle does not have an auto-growing data type. You need to create an auto-growing sequence number and assign the next value of the sequence number to this field when inserting a record.
INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;
The maximum value is determined by the length of the field. If you define the auto-growing serial NUMBER(6), the maximum value is 999999
The INSERT statement inserts this field value as: the name of the sequence number. NEXTVAL
Handling of single quotes
MySQL can use double quotes to enclose strings, Oracle can only use single quotes to enclose strings. Single quotes must be replaced before inserting and modifying strings: replace all occurrences of one single quote with two single quotes.
Processing of SQL statements turning pages
MySQL handles page turning SQL statements simply, use LIMIT start position, record number; You can also use SEEK in PHP to locate the result set. Oracle handles page-turning SQL statements more cumbersome. Each result set has only one ROWNUM field to indicate its position, and only ROWNUM<100, not ROWNUM>80.
Here are two types of Oracle page-turning SQL statements (ID is the field name with the unique keyword) that are better analyzed:
Statement 1:
SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE conditional 1 ORDER BY conditional 2) WHERE NUMROW > 80 AND NUMROW < 100 ORDER BY conditional 3;Copy the codeStatement 2:
SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (SELECT [FIELD_NAME,...] Select * FROM TABLE_NAME WHERE NUMROW > 80 AND NUMROW < 100 ORDER BY NUMROW 2) C) WHERE NUMROW > 80 AND NUMROW < 100) ORDER BY conditional 3;Copy the codeHandling of long strings
The handling of long strings is also special to Oracle. The maximum string length for INSERT and UPDATE operations is 4000 bytes or less. If you want to INSERT longer strings, consider using cloBS for fields and using DBMS_LOB from Oracle. Before inserting the modified record, be sure to check whether the field value cannot be empty and the field value exceeding the length should be warned and return to the last operation.
Date field processing
The MySQL DATE field has two types: DATE and TIME. The Oracle DATE field has only DATE, which contains information about year, month, day, hour, minute, second. TO_DATE(‘2001-08-01′,’YYYY-MM-DD’) year – month – day 24 hour: minute: second YYYY-MM-DD HH24:MI:SS TO_DATE() See the Oracle DOC.
TO_CHAR(‘2001-08-01′,’YYYY-MM-DD HH24:MI:SS’)
The mathematical formulas for date fields are quite different. SUBDATE(NOW(), INTERVAL 7 DAY) DATE_FIELD_NAME > sysdate-7;
The NOW() function returns the current date and time as ‘YYYY-MM-DD HH:MM:SS’, which can be stored directly in the DATETIME field. CURDATE() returns today’s DATE in ‘YYYY-MM-DD’ format, which can be stored directly in the DATE field. CURTIME() returns the current TIME in ‘HH:MM:SS’ format, which can be stored directly in the TIME field. Insert into tablename (fieldname) values (now())
In Oracle, the current time is sysdate
Null character handling
MySQL defines non-empty fields as null fields. Oracle defines non-empty fields as null fields. MySQL > alter table table table NOT NULL Therefore, NULL characters are evaluated when the data is derivative. If NULL or NULL characters, it needs to be changed to a string of Spaces.
Fuzzy comparison of strings
MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL MySQL
In programs and functions, note the release of result sets and Pointers after the work of manipulating the database is done.
A primary key
MySQL generally uses the auto increment type. When creating a table, you only need to specify auto increment as the primary key of the table. When inserting a record, you do not need to specify the primary key value of the record. Oracle does not have automatic growth type, the primary key is generally used in the sequence, when the record is inserted to pay the next value of the serial number to this field; The ORM framework is a native primary key generation strategy.
Original: http://database.51cto.com/art/201809/582742.htm
