Tuesday, May 05, 2009

DB/ Database notes (sql tips)

JDBC Format 

Sybase  (jconn3.jar)

Use Service Name (format /)
user SID (format :)

Limit Rows

// mysql
select col from tbl limit 20;

//Sybase / SqlServer
select top 10 * from people
SET 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 unique

//Sybase / SqlServer
select distinct col from table



Outer Join

// Oracle left outer Join
select * from Customer, Order where Order.customerId (+) = Customer.id
//right join
select * from Customer, Order where Order.customerId = Customer.id (+)



>mysqldump --host vc2c09mmk0326.fmr.com --port=3306 -u datasift2 -p --set-gtid-purged=OFF datasift2  > datasift.sql
  • 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
   FOR cur_rec IN (SELECT object_name, object_type
                     FROM user_objects
                    WHERE object_type IN
         IF cur_rec.object_type = 'TABLE'
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '" CASCADE CONSTRAINTS';
            EXECUTE IMMEDIATE    'DROP '
                              || cur_rec.object_type
                              || ' "'
                              || cur_rec.object_name
                              || '"';
         END IF;
         WHEN OTHERS
            DBMS_OUTPUT.put_line (   'FAILED: DROP '
                                  || cur_rec.object_type
                                  || ' "'
                                  || cur_rec.object_name
                                  || '"'

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">
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';