Differences between oracle and mysql and oraclemysql

Differences between oracle and mysql and oraclemysql

I. Self-Growth of primary keys:

1)Oracle:

Use sequence implementation, such as creating a sequence. The starting point is 1000, and the step is 1:

Create sequence myseq increment by 1 start with 1000

Add record:

Insert into student (stuId, stuName) values (myseq. nextval, 'Peter ')

2)Mysql:

Specify primary key auto-increment when creating a table

Create table student (stuId int auto_increment primary key, stuName char (20 ))

Add record:

Insert into student (stuName) values ('Peter ')

Ii. command line tool:

The official Oracle command line tool is SQLPlus.

 

The mysql command line tool is mysql:

 

Iii. User concepts:

1) Oracle

There is a user concept in Oracle that is used to log on to a database, such as an openlab user. You have certain permissions to create tables and views. A collection of data tables, views, and other objects under a user name is called Schema.

After the database is installed. The default user is sys. system. scott.

Create user: create user username identified by password (generally created by DBA, normal users do not have permission to create users)

In the SQLPLus tool, switch user: conn user name/password @ host string

View Current user: show user

Show all users: select * from all_users

Show tables under the user: select * from tab

2) mysql

Mysql corresponds to the concept of database in oracle. You must create a database before creating a table. The default database is mysql and test.

Create Database: create databases Database Name

Switch User: use database Name

View Current User: connect

Show all databases

Show tables

4. Suppose there is an external script file C: \ script. SQL. The method for executing the external script file is as follows:

1) Oracle:

@ Script. SQL

2) mysql:

Source script. SQL

5. Expressions in SQL statements:

1) Oracle:

Select 12*10 from dual

2) mysql:

Select 12*10

6. Obtain the system time

1) Oracle:

Select sysdate from dual

2) mysql:

select now()select sysdate()select curdate()select select current_dateselect curtime()select current_time

 

VII. Date Format Conversion:

1) Oracle:

select to_char(sysdate,'yyyy-mm-dd') from dual;select to_char(sysdate,'hh24-mi-ss') from dual;

 

2) mysql:

Select date_format (now (), '% Y-% m-% D ')

Select time_format (now (), '% H-% I-% s ')

 

1. process the date and time directly using SQL statements in the database.

2. Date Processing in Java code (framework)

3. Handle pages with JavaScript code

4. Tag (c s)

8. Date function: 6 months later

1) Oracle:

Select add_months (sysdate, 6) from dual

2) mysql:

Select date_add (now (), interval 6 month)

9. Character functions: extract hello from 'helloworld'

1) Oracle:

Select substr ('helloworld', 1, 5) from dual;

2) mysql:

Select substring ('helloworld', 1, 5)

10. Modify the table name:

1) Oracle:

Rename oldname to newname

You can also modify object names.

2) mysql:

Alter table oldname to newname

11. Group functions

Select deptno, count (*) from emp

This syntax is normal in mysql, and an error is reported in oracle. The deptno column in the select list must follow the group by phrase:

Select deptno, count (*) from emp group by deptno

 

12. Paging statements

 

1) Oracle:

select * from (select t.*,rownum rn from (select * from emp) t where rownum < 11) where rn > 5

 

Found 6th ~ 10 records

2) mysql:

Select * from emp limit 5

Found 6th ~ 10 records

13. Calculate the annual salary of each employee: Salary * 12 + bonus * 12

Select ename, sal * 12 + nvl (comm, 0) * 12 from emp

Nvl: single-row function. If it is null, 0 is returned.

Oracle: nvl

Mysql: ifnull

Sqlserver: isnull