Comparison between oracle and mysql

Summary of the use of oracle and mysql: Comparison of the use of oracle and mysql: mysql and ORACLE have not been summarized for a while. Do some difference today: www.2cto.com 1. when using a database after using mysql for a period of time and using ORACLE again, the first thing that comes to mind is the database name. In the url setting of hibernate, the use of the database name is first involved, in ORACLE, each user has only one database name. This is also set by the user during ORACLE 10 Gb installation. If the user occasionally forgets his or her own database name during later use
The database name you set can also be managed in windows. In the service, you can find the database name based on the service name (I have never found the database name for ORACLE ); in mysql, you can run the show databases command to display all DATABASES under the user. 2. the use of group functions in mysql does not involve the problem of group functions. In mysql, group functions can be used freely in select statements. For example: select name, SUM (SALARY) from boss; // records corresponding to SUM in the first column of the table are listed. However, if a group function exists in the query statement in ORACLE, other column names must have been processed by the group function, or the column in the group by clause. Otherwise, the following query statements are returned: eg: select name, SUM (SALARY) from boss. The same query statement does not cause any problems in mysql, but an error is reported in ORACLE. The difference between the quotation marks in the statement www.2cto.com 3. When using single quotes to enclose strings in MYSQL, you can also use double quotes to enclose strings. However, in ORACLE, you can only use single quotes to enclose strings. You must replace single quotes before inserting and modifying strings: replace all the existing single quotes with two single quotes. 4. Differences of growth types when creating a table, MYSQL has a data type that increases automatically. when inserting a record, you do not need to operate on this field and the data value is automatically obtained. ORACLE does not have an auto-increasing data type, but an auto-increasing serial number needs to be created. when inserting a record, the next value of the serial number must be assigned to this field. Eg: create sequence serial number name (preferably table name + Serial number mark) increment by 1 start with 1 MAXVALUE 99999 cycle nocache; the maximum value is determined BY the length of the field, if the defined auto-increment serial NUMBER (6) is used, the maximum value is 999999 INSERT. INSERT this field value as the serial NUMBER name. NEXTVAL 5. the difference between fuzzy queries is involved in the java JDBC section. In mysql and oracle, you can use the field name like % 'string % '. However, in oracle, the speed of this method is limited, solution: Use a string to compare the instr function (field name, 'string') to 0 to get more accurate search results. Www.2cto.com 6. If the null character is used to process non-empty MYSQL fields, the ORACLE defines non-empty fields and the content is not allowed to be empty. The ORACLE table structure is defined based on the not null value of MYSQL. errors may occur when data is imported. Therefore, when importing data, you need to judge the NULL character. If it is NULL or NULL, you need to change it to a space string. Note the difference between NULL and "" and 0. 7. The use of long strings to process ORACLE also has its special features. The maximum length of a string that can be operated during INSERT and UPDATE is less than or equal to 4000 single bytes. If you want to INSERT a longer string, you 'd better consider using the CLOB type for fields ,. Before inserting a modification record, you must make a non-null and length judgment. If the field value cannot be blank or the field value beyond the length is exceeded, a warning should be given, and the last operation is returned. 8. Paging SQL statement processing www.2cto.com MYSQL is relatively simple to process paging SQL statements. Use LIMIT to start the position and record the number. In PHP, you can also use SEEK to locate the result set. For example: SELECT * FROM TABLE_NAME LIMIT 10; it is complicated for ORACLE to process paging SQL statements. Each result set has only one ROWNUM field to indicate its position, and only ROWNUM <100, not ROWNUM> 80 can be used. The following are two SQL statements (ID is the field name of the unique keyword) that have been 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 Condition 1 order by condition 2) where numrow> 80 and numrow <100) order by Condition 3; www.2cto.com Statement 2: SELECT * FROM (select rownum as numrow, c. * from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE Condition 1 order by condition 2) c) where numrow> 80 AND NUM ROW <100) order by Condition 3; 9. For comparison of data types, refer to the use of comparison of the basic types of the two.