The following is a personal opinion. If you have any questions, please ask: (* indicates that the certificate is required, Oracle uses 8.1.6)
1. Select the top 20 records
Oracle: Select * from Tablename WHERE ROWNUM <20;
SQL Server: SELECT TOP 20 * from TableName;
2. Connect other databases
Oracle:
Create Database Link DB_LINK_OHAHU Connect ON User Name Identified by Password Using 'Connection String'; // Create Data Connection
'Connection String' can use Net8 Easy Config or direct service names defined in tnsnames.ora
Select * from tablename @ db_link_ohahu; // Using db_link_ohahu on table TableName;
SQL Server: INSERT INTOOPENROWSET ('Microsoft.jet.OleDb.4.0', 'E: /DB/AA.MDB'; 'Admin'; '', T1) SELECT * from T1;
Access: select * into [; database = e: /db/aa.mdb; pwd = password] .t1 from T1;
3. About SELECT INTO;
Oracle:
NCNT INTEGER
Select Count (FieldName) INTO NCNT from TableName; // Store count (*) to the variable NCNT;
SQL Server:
Select * INTO TABLENAME2 from Tablename1; // Put the data of Tablename1 to Tablename2, including database structure
4. Date field problem
Oracle: Select * from tablename where fieldname> to_date ('1981-02-23', 'YYYY-MM-DD');
The above is just saying that Oracle can only use 'as a division, in fact for the date field, Oracle's display is very strange, such as 1981-8-20, SELECT's format is the '20-281-81' So is still looking for > 200 - August-81 'can be used. Also seems to datediff does not support in Oracle
Access:
Select * from TableName Where FieldName> '1981-2-23';
Select * from tablename wherefieldname> # 1981-2-23 #;
All can be, advanced reference datediff usage
5. Spend from the field
Oracle To generate a sequence, trigger:
CREATE SEQUENCE SEQnameINCREMENT BY 1START WITH 1MAXVALUE 99999999 / CREATE TRIGGER TRGnameBEFORE INSERT ON table_nameREFERENCINGNEW AS: NEWFOR EACH ROWBeginSELECT SEQname.NEXTVALINTO: NEW.FIELDnameFROM DUAL; End; /
Access: CREATE TABLE TABLENAME (Lid AutoIncrement (1, 1), ...
SQL Server: CREATE TABLE TABLENAME (Lid IdenTentity (1, 1), ..........
Identity (Seed, Growth Speed), AutoIncrement (Seed, Growth Speed) 6. Default Login Method
Oracle: Use the console program SQLPLUS or DBA. The external service must be added to TNSNames.ora. SQLPLUS Usage: Command Line Next Enter> SQLPLUS User Name / Password [@ Service Name]
Username Password:
System Manager // can manage most of the work: tablespace, user, authorization, data file, etc.
Sys change_on_install // is higher than the System, often used to set the system parameters, or special system views
INTERNAL ORACLE // is often used in starting the shutdown database through SVRMGRL, because the default is OS authentication, so no password is required
SQL Server: Using Enterprise Manager or Query Analyzer
Username: SA password: SA // can be logged in using system authentication
7. Problems with ADO *
Oracle: When the data set is empty, it cannot m_PRS-> MoveFirst ();
Access, SQL Server: Yes
8. About Order by statement
Select * from (SELECT A, B from Tablename Order By A) TMP;
Such sentences do not have problems in Oracle, but the SQL Server will prompt the error.
The reason for the error is that the ORDER BY must be used as a clause, and the method to be solved by the TOP keyword:
SELECT * FROM (SELECT TOP 100 Percent A, B from Tablename Order By A) TMP;
TOP 100 Percent means a record of 100%
There are also places that need to be paid, whether Oracle or SQL Server.
When using the group by keyword, the field name and SELECT field name, must be found in the field name of SELECT, such as two errors below:
Select a from tablename group by b Order by c
The following web page provides reference:
Database summary (1) --- Various database usage difference http://community.9cbs.net/expert/topic/3108/3108440.xml?temp=.6947443
Some simple commands of Oracle and MySQL reference http://202.101.18.235/club/bbs/showssnce.asp?id=22161&page=2
