JDBC Format
jdbc:oracle:thin:@server:1521/fmwa
user SID (format :)
jdbc:oracle:thin:@bggwrsoa12:1521:fmwa
Limit Rows
// mysqlselect col from tbl limit 20;
//Sybase / SqlServer
select top 10 * from peopleSET ROWCOUNT 10 // use SET ROWCOUNT 0 to turn off// Oracle
select col from tbl where rownum <=20;
Using rank()
This query will return the top 10 rows..
select col1,col2 from (select rank() over (order by col1) r , col1,col2 from TABLE) where r<11 For bottom 10 rows use select col1,col2 from (select rank() over (order by col1 DESC) r , col1,col2 from TABLE) where r<11
select col1,col2 from (select rank() over (order by col1) r , col1,col2 from TABLE) where r<11 For bottom 10 rows use select col1,col2 from (select rank() over (order by col1 DESC) r , col1,col2 from TABLE) where r<11
Select unique
//Sybase / SqlServerselect distinct col from table
Oracle
MySql
Outer Join
// Oracle left outer Joinselect * from Customer, Order where Order.customerId (+) = Customer.id
//right join
select * from Customer, Order where Order.customerId = Customer.id (+)
Backup
Mysql
>mysqldump --host vc2c09mmk0326.fmr.com --port=3306 -u datasift2 -p
- This will create a sql with all the tables. Note the --set-gtid-purged=OFF is required on newer versions of the client. If you are running on vc2c09mmk0326 it is probably not required.
- Note the sql will be over 100MB in size
Oracle drop all tables
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
END;
/
// HSQL
from Customer left join Order where Order.customerId = Customer.id
Could only get this working by explicitly linking the join in the hbm file, and marking it as optional e.g.
Need some more testing to see if this can be done without updating the hbm file
<class name="AdjustmentCode" table="adjustment_code">
<id name="id" column="adjustment_code" type="string">
<generator class="native">
<property name="description" column="description" type="string">
<property name="adjustmentCode" column="adjustment_code" type="string" insert="false" update="false">
<join table="adjustment_message">optional="true">
<key column="adjustment_code">
</join>
<class>
Insert if row not Exists
// MySql
if not exists (select * from url where url = ...)
insert into url...
//Oracle (where not exists)
insert into <table> (<row1>, <row2>)
select <value1>, <value2> from dual
where not exists (
select * from <table> where <col1> = <value1>)
//SQL Server
if not exists (select * from url where url = ...)
insert into url...
Date Operations
GetDate
//MySql
DATE() see (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html)
//Oracle
SYSDATE
// Sql Server
getDate()
To Date
//Oracle
to_date('2009-01-01','YYYY-MM-DD')
to_date('2009-05-19 18:32','YYYY-MM-DD HH24:MI')
http://www.dba-oracle.com/f_to_date.htm
Detecting Duplicates (group by criteria)
Say you want to detect duplicates (instances of more than one value) in a column. This is how to do it
select dupCol, count(dupCol) from table group by dupCol having count(dupCol)>1
Listing Constrainsts
Oracle
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'myTable';
