Person.find :all, :conditions => ["#{sql_year 'birthday'} >= ?", year]

SQLITE = true # or false

def sql_concat(*args)
  SQLITE ? args.join(' || ') : "CONCAT(#{args.join(', ')})"
end

def sql_lcase(expr)
  SQLITE ? "LOWER(#{expr})" : "LCASE(#{expr})"
end

def sql_year(expr)
  SQLITE ? "CAST(STRFTIME('%y', #{expr}) as 'INTEGER')" : "YEAR(#{expr})"
end

def sql_month(expr)
  SQLITE ? "CAST(STRFTIME('%m', #{expr}) as 'INTEGER')" : "MONTH(#{expr})"
end

def sql_day(expr)
  SQLITE ? "CAST(STRFTIME('%d', #{expr}) as 'INTEGER')" : "DAY(#{expr})"
end

def sql_now
  SQLITE ? "CURRENT_TIMESTAMP" : "NOW()"
end

mysqldump -v --quick --single-transaction --all-databases -uroot > file.sql

SELECT DISTINCT field_name, COUNT(*) AS num FROM table_name GROUP BY field_name ORDER BY num DESC

SELECT * FROM texts WHERE content REGEXP '[^a-z]Hello[^a-z]' ;

SELECT * FROM texts WHERE content REGEXP BINARY '[^a-zA-Z]Hello[^a-zA-Z]' ;

SELECT * FROM texts WHERE content REGEXP '[^\w]Hello[^\w]' ;

CREATE VIEW gpsGlb AS
    SELECT 
        LocID
        ,6378 * COS(RADIANS(Latitude)) * COS(RADIANS(Longitude)) AS x
        ,6378 * COS(RADIANS(Latitude)) * SIN(RADIANS(Longitude)) AS y
        ,6378 * SIN(RADIANS(Latitude)) AS z
    FROM station;

SELECT 
    LocID
    ,ROUND((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi
FROM
    (SELECT
        SQRT(dx * dx + dy * dy + dz * dz) AS d
        ,LocID
     FROM
        (SELECT
            p1.x - p2.x AS dx
            ,p1.y - p2.y AS dy
            ,p1.z - p2.z AS dz
            ,p2.LocID
        FROM gpsGlb p1
        JOIN gpsGlb p2 ON (p1.LocID = 405 AND p2.LocID != 405)
       ) t1
    ) t2
ORDER BY dist_mi

SELECT
    LocID
    ,(360 + DEGREES(ATAN2(y, x))) % 360 AS initBearing_deg
    ,ROUND(((360 + DEGREES(ATAN2(y, x))) % 360) / 22.5) * 22.5 
     AS initBearingBoxed_deg
FROM
    (SELECT
        SIN(RADIANS(s2.Longitude - s1.Longitude)) * COS(RADIANS(s2.Latitude)) 
        AS y
        ,COS(RADIANS(s1.Latitude)) * SIN(RADIANS(s2.Latitude))
            - SIN(RADIANS(s1.Latitude)) * COS(RADIANS(s2.Latitude))
               * COS(RADIANS(s2.Longitude - s1.Longitude)) 
        AS x
        ,s2.LocID
    FROM station s1
    JOIN station s2 ON (s1.LocID = 405 AND s2.LocID != 405)
    ) q1

SELECT
    qq2.LocID
    ,dist_mi
    ,CASE initBearingBoxed_deg
        WHEN 22.5 THEN 'NNE'   WHEN 45 THEN 'NE'
        WHEN 67.5 THEN 'ENE'   WHEN 90 THEN 'E'
        WHEN 112.5 THEN 'ESE'  WHEN 135 THEN 'SE'
        WHEN 157.5 THEN 'SSE'  WHEN 180 THEN 'S'
        WHEN 202.5 THEN 'SSW'  WHEN 225 THEN 'SW'
        WHEN 247.5 THEN 'WSW'  WHEN 270 THEN 'W'
        WHEN 292.5 THEN 'WNW'  WHEN 315 THEN 'NW'
        WHEN 337.5 THEN 'NNW'  ELSE 'N'
     END AS bearing
FROM (
    SELECT 
        LocID
        ,ROUND((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi
    FROM
        (SELECT
            SQRT(dx * dx + dy * dy + dz * dz) AS d
            ,LocID
         FROM
            (SELECT
                p1.x - p2.x AS dx
                ,p1.y - p2.y AS dy
                ,p1.z - p2.z AS dz
                ,p2.LocID
            FROM gpsGlb p1
            JOIN gpsGlb p2 ON (p1.LocID = 405 AND p2.LocID != 405)
           ) t1
        ) t2
    ) qq1
JOIN (
    SELECT
        LocID
        ,(360 + DEGREES(ATAN2(y, x))) % 360 AS initBearing_deg
        ,(360 + ROUND((DEGREES(ATAN2(y, x))) / 22.5) * 22.5) % 360 
         AS initBearingBoxed_deg
    FROM
        (SELECT
            SIN(RADIANS(s2.Longitude - s1.Longitude)) * COS(RADIANS(s2.Latitude)) 
             AS y
            ,COS(RADIANS(s1.Latitude)) * SIN(RADIANS(s2.Latitude))
                - SIN(RADIANS(s1.Latitude)) * COS(RADIANS(s2.Latitude))
                   * COS(RADIANS(s2.Longitude - s1.Longitude)) 
             AS x
            ,s2.LocID
        FROM station s1
        JOIN station s2 ON (s1.LocID = 405 AND s2.LocID != 405)
        ) q1
    ) qq2 ON (qq1.LocID = qq2.LocID
              AND qq1.dist_mi <= 60)
ORDER BY dist_mi

SELECT -5 - CAST(REPLACE(TIMEDIFF(NOW(), UTC_TIMESTAMP()), ':00:00', '') AS SIGNED) AS isDst

# Find all rows created on a certain day; Rails apparently has a built-in :db string format
self.find(:all, :conditions => ["created_at >= ? AND created_at <= ?", day.beginning_of_day.to_s(:db), day.end_of_day.to_s(:db)])

# Find number of days between two dates
def days_between_dates(first, last)
  (YMD(last) - YMD(first))
end

def YMD(date)
  date.to_date.to_s.gsub("-", "").to_i
end

create table `baz`;

GIGS OF SQL YOU DON'T WANT;

create table `foo`;

A COUPLE THOUSAND LINES YOU DO WANT;

create table `bar`;

MORE SQL YOU DON'T WANT;

$ ruby -ne '@found=true if $_ =~ /^CREATE TABLE `foo`/i; next unless @found; exit if $_ =~ /^CREATE TABLE (?!`foo`)/i; puts $_;' giant_sql_dump.sql > foo.sql
$ cat foo.sql
create table `foo`;

A COUPLE THOUSAND LINES YOU DO WANT;

$ mysql mydatabase -e 'drop table foo'
$ mysql mydatabase < foo.sql

...
mysql_query( "SET NAMES 'utf8' " );
...

SELECT * INTO OUTFILE '/tmp/file.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM <tablename>