Shulou(Shulou.com)06/01 Report--

This article mainly introduces the specific differences and functions of MySQL and Oracle, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the specific differences and functions of MySQL and Oracle.

The difference between MySQL and Oracle:

Both MySQL and Oracle are popular relational database management systems (RDBMS), which are widely used around the world. Most databases work in a similar way.

What's the difference between the two? The characteristics of the two databases are different, so MySQL is used differently than Oracle, and Oracle is used differently than MySQL. Their characteristics are also different.

1. The essential difference:

Oracle database is an object-relational database management system (ORDBMS). It is usually called OracleRDBMS or Oracle for short, and it is a paid database.

MySQL is an open source relational database management system (RDBMS). It is the most widely used RDBMS in the world. It runs as a server and provides multi-user access to multiple databases. It is an open source, free database.

two。 Database security:

MySQL uses three parameters to authenticate the user, namely, user name, password, and location; Oracle uses many security features, such as user name, password, profile, local authentication, external authentication, advanced security enhancements, and so on.

Differences in 3.SQL syntax:

Oracle's SQL syntax is very different from MySQL. Oracle provides more flexibility for a programming language called PL/SQL. Oracle's SQL*Plus tool provides more commands than MySQL for generating report output and variable definitions.

4. Differences in storage:

Compared to Oracle, MySQL has no tablespaces, role management, snapshots, synonyms and packages, and automatic storage management.

5. Differences in object names:

Although some schema object names are case-insensitive in both Oracle and MySQL, such as columns, stored procedures, indexes, and so on. In some cases, however, the case difference between the two databases is different.

Oracle is case-insensitive for all object names, while some MySQL object names, such as databases and tables, are case-sensitive (depending on the underlying operating system).

6. Running programs and external program support:

The Oracle database supports several programming languages written, compiled, and executed from within the database. In addition, to transfer data, the Oracle database uses XML.

MySQL does not support the execution of other languages within the system, nor does it support XML.

Comparison of character data types between 7.MySQL and Oracle:

There are some differences in the character types supported in the two databases. For character types, MySQL has CHAR and VARCHAR, and the maximum length allowed is 65535 bytes (CHAR can be up to 65535 bytes, VARCHAR is 65535 bytes).

Oracle supports four character types, that is, CHAR,NCHAR,VARCHAR2 and NVARCHAR2; all need at least 1 byte long; CHAR and NCAHR can be up to 2000 bytes, and the maximum limits for VARCHAR2 and NVARCHAR2 are

4000 bytes. It may be extended in the latest version.

Comparison of additional features of 8.MySQL and Oracle:

MySQL databases do not support any features on their servers, such as Audit Vault. On the other hand, Oracle supports several extensions and programs on its database server, such as Active Data Guard,Audit Vault,Partitioning and Data Mining.

9. The difference between temporary tables:

Oracle and MySQL handle temporary tables in different ways.

In MySQL, temporary tables are database objects that are visible only to the current user session and are automatically deleted once the session ends.

The definition of temporary tables in Oracle is slightly different from that of MySQL because temporary tables exist once created until they are explicitly deleted and visible to all sessions with appropriate permissions. However, the data in the temporary table is visible only to user sessions in the data insertion table

And the data may persist during a transaction or user session.

Backup types in 10.MySQL and Oracle:

Oracle provides different types of backup tools, such as cold backup, hot backup, export, import, data pump. Oracle provides the most popular backup utility called RecoveryManager (RMAN). With RMAN, we can use very few commands or save

Save scripts to automate our backup scheduling and recovery database.

MySQL has mysqldump and mysqlhotcopy backup tools. There is no utility like RMAN in MySQL.

Database management for 11.Oracle and MySQL:

In the database management section, Oracle DBA is more profitable than MySQL DBA. Compared to MySQL, Oracle DBA has a lot of available ranges.

MySQL

1. If the comparison operator can be used, there is no need for "!":

"! =" increases the probability of using the index.

two。 Knowing that there is only one query result, use "LIMIT 1":

"LIMIT 1" can avoid scanning the whole table, and if you find the corresponding result, you will not continue to scan it.

3. Select the appropriate data type for the column

You don't need SMALLINT if you can use TINYINT, and you don't need INT if you can use SMALLINE. You know, the smaller the disk and memory consumption, the better.

4. Turn a large DELETE,UPDATE or INSERT query into multiple small queries

Isn't it high to be able to write dozens or hundreds of lines of SQL statements? However, in order to achieve better performance and better data control, they should be written as multiple small queries.

5. Use UNION ALL instead of UNION if the result set allows repetition. Because UNINON ALL does not remove weight, the efficiency is higher than UNION.

6. To get multiple executions of the same result set, keep the SQL statement consistent. The purpose of this is to take full advantage of query buffering.

For example, query the product price based on region and product ID, and use it for the first time:

SELECT price FROM order WHERE id='123456' and region='BEIJING'

So the second time the same query, please keep the above statements consistent, for example, do not change the id and region positions in the where statement.

7. Avoid using "SELECT" as much as possible

If you do not query all the columns in the table, try to avoid using SELECT, because it will perform a full table scan, cannot effectively utilize the index, and increase the burden on the database server, as well as the network IO overhead between it and the application client.

The columns in the 8.WHERE clause are indexed as much as possible

Just "try", not all the columns. Adjust to local conditions and adjust to the actual situation, because sometimes too many indexes will degrade performance.

The columns in the 9.JOIN clause are indexed as much as possible. It also means "as much as possible", not all columns.

The columns of 10.ORDER BY are indexed as much as possible. OEDER BY columns will also perform better if they are indexed.

11. Use LIMIT to implement paging logic. It not only improves performance, but also reduces unnecessary network transmission between databases and applications.

twelve。 Use the EXPLAIN keyword to view the execution plan. EXPLAIN can check index usage and scanned rows.

Summary: there are many ways to tune SQL, and there are many different ways to query the same query result. In fact, the best way is to test with the closest real dataset and hardware environment in the development environment, and then release it to the production environment.

Oracle

Basic SQL statement:

Query: select from tab_name where conditions order by field1 desc; (precise query) desc descending, asc ascending

Select from tab_name where field1 like'% value1%' (fuzzy query)

Insert: insert into tab_name (field1,field2) values (value1,value2)

Delete: delete from tab_name where conditions

Update: update tab_name set field1=value1,field2=value2 where conditions

Submit data: commit

Rollback data: rollback

Total: select count () from tab_name

Summation: select sum (field1) as sumvalue from tab_name

Average: select avg (field1) as avgvalue from tab_name

Maximum: select max (field1) as maxvalue from tab_name

Minimum: select min (field1) as minvalue from tab_name

Grouping: grounp by: a table, once grouped, the query can only get group-related information.

The use of between. Between includes boundary values when limiting the scope of query data, while not between does not include:

Select from tab_name where time between time1 and time2

Select a,b,c from tab_name where a not between num1 and num2

Usage of in: select from tab_name where a [not] in ('value 1', value 2', 'value 3,' value 4')

Two associated tables to delete information in the primary table that is not in the secondary table: delete from tab1 where not exists (select from tab2 where tab1.field1=tab2.field2)

Joint check of four tables: select from a left inner join b on a.a=b.b right inner join con a.a=c.c inner join d on a.a=d.d where conditions

Query the same entry and record the number of repeats (greater than 2): SELECT a _ score count (a) FROM tab_name GROUP BY a HAVING count () > 2

Create a database: create database database-name

Delete database: drop database database-name

Create a new table: create table depart (

Dept_id int (11) not null auto_increment

Depr_name varchar (255) default null

Primary key (dept_id)

);

Create a new table based on an existing table:

Create table tab_new like tab_old; (create new form A using old form B) (MySQL)

Note: when copying table B to An in this way, the complete field structure and index of table B are copied to table A. But the data is not replicated.

Create table tab_new as select col1,col2... From tab_old definition only

Note: this method will only copy the field structure of Table B to Table A, but will not copy the indexes in Table B to Table A. This method is more flexible, you can specify which fields to copy while copying the original table structure, and you can also copy the table itself.

Add the field structure as needed.

Delete table: drop table tab_name

Alias for the newly added table: comment on tab_name col is' test'

Change the table name: alter table tab_oldName rename to tab_newName

Add a column: alter table tab_name add column_name type

Add primary key: alter table tab_name add primary key (col)

Delete primary key: alter table tab_name drop primary key (col) A data table can have only one primary key, so there is no primary key to delete a column.

Create an index: create [unique] index idxname on tab_name (col...)

Delete index: the drop index idxname; index cannot be changed. If you want to change it, you must delete and rebuild it.

Create a view: create view viewname as select statement

Delete View: drop view viewname

Pro

Query lock: select from dba_blockers

Query blocked sessions: select from dba_waiters

Display the same data in both tables: select column_name from tab_old intersect select column_name from tab_new

Limit the number of in parameters in Oracle:

There is a limit to the number of parameters in parentheses after in in Oracle. The number in Oracle 9i cannot exceed 256 Oracle 10g and cannot exceed 1000.

When the number of in is greater than 1000, there are two solutions:

(1) the parameters are processed and divided into multiple in, in which the parameter in each in list is less than 1000. For example, params in (1prim 2m 3.1000) or params in (1001m 1002. 2000).

However, this method is not good in performance and maintainability.

(2) change the string after in into a subquery, and save the data in in to the temporary table, params in (select.... from dual)

Filter the difference between conditional where and having in the SQL statement:

Where is a constraint declaration that uses where to constrain data from the database, where works before the result is returned, and aggregate functions cannot be used in where.

Having is a filter declaration that filters query results after the query returns a result set, and aggregate functions can be used in Having.

The aggregate statement (sum,min,max,avg,count) takes precedence over the having clause during the query. The where clause takes precedence over the aggregate statement during the query.

After reading the specific differences and functions of MySQL and Oracle, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.