mysql notes

use mysql command line

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A -H -D hg38 -e "select * from hg38.snp141 where hg38.snp141.name = 'rs7412';"

If you have a certificate from whoever is running a secured mysql server you’ll need to tell mysql to use it.

mysql --user=<user> --host=<ip_address> --port=<port> --ssl-ca=<file, generally in the pem format> --password

MySql will prompt you for your password after entring the command.

interactive mysql examples

The following examples assume you’re connected to a mysql instance. You could do that like so -

mysql --user=genome --host=genome-mysql.cse.ucsc.edu -A

use database

USE sampdb;
SELECT DATABASE();

create table in database

CREATE TABLE tbl_name (column_specs);

Explore the data

SHOW DATABASES;
SHOW TABLES;

describe a table

DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;

Adding data

Adding data in mysql client from `insert_data.sql` file

source insert_data.sql;

Adding data from command line from `insert_data.sql` file

mysql testdb < insert_data.sql

Adding data in mysql client using a text file `member.txt`

LOAD DATA LOCAL INFILE 'member.txt' INTO TABLE member;

Retrieving data

selecting data

SELECT what to retrieve FROM table or tables WHERE conditions that data must satisfy;

concatenating data

In the following snippet, the AS is optional. CONCAT just concatenates some columns together into a more convenant form.

SELECT CONCAT(first_name,' ',last_name) AS 'Full Name',
CONCAT(city,', ',state) AS 'current city',
FROM member;

dates

Dates are always in the form YYYY-MM-DD.

calculate date differences

SELECT last_name, first_name, expiration FROM member
WHERE (TO_DAYS(expiration) - TO_DAYS(CURDATE())) < 60;

Alternatively:

SELECT last_name, first_name, expiration FROM member
WHERE TIMESTAMPDIFF(DAY, CURDATE(), expiration) < 60;

calculate one date from another

Use DATE_ADD() or DATE_SUB() like this:

SELECT DATE_ADD('1970-1-1', INTERVAL 10 YEAR);

Pattern matching

use % to match stuff.

Summarizing data

Use SELECT DISTINCT state FROM member order by state to tally up the states your members are from.

Use SELECT COUNT(*) FROM member to count the number of rows that were selected by your query. You could thow a WHERE clause to determine how many rows the clause matches.

To get a summary of the summary from COUNT(*) add the WITH ROLLUP clause.

Joining multiple tables

Inner Join

Simply joins the intersection of the 2 or more tables.

Left/Right Join

Joins using either the left or right table as the reference table such that values present in that values present in that table will always be given even if they may not have any results.

Join - same column in 2 different tables

When joining tables with the same name mysql will return the names of both columns (and their contents), but problems arise when you’re trying to have them returned as an hashref since they will end up having the same name in the resultant table. To get around this use the ‘AS’ command to create an alias for one or more of the columns.

SELECT a.name, b.name AS name2 FROM a INNER JOIN b WHERE a.name = b.name

Perl DBI Notes

DBI hashref

Force the hashref from a DBI query to give the keys, which are column names, as either upper or lower case.

$hash_ref = $sth->fetchrow_hashref ("NAME_lc"); # use lowercase names
$hash_ref = $sth->fetchrow_hashref ("NAME_uc"); # use uppercase names

DBI fetchall_arrayref()

my $array_ref = $sth->fetchall_arrayref ();

This places all of the results from a query into one large array, which you can access like $array_ref->[$i][$j]. It will return a reference to an empty array if the set is empty.

DBI selectall_arrayref()

my $array_ref = $dbh->selectall_arrayref($statement);

selectall_arrayref() will perform the prepare(), execute(), and fetch loop for you. It returns an array reference like $array_ref->[$i][$j].

DBI NULL vs zero vs “” (i.e., empty string)

Check that the value is not defined first then you can determine if it’s a string or zero or has some value.

if (!defined($val)) { $val = "NULL"; }
elsif ($val eq "")  { $val = "Empty String"; }
elsif ($val == 0)   { $val = 0; }
else                { print "do something"; }

DBI an quoting characters

Quoting within strings leads to annoying problems sometimes. One solution is to use qq{ } to place the string inside quotation markers without the need to escape all of the single or double quotes you might need to write.

Quoting strings with a single or double quote is necessary if you want to insert them with $dbh->quote() into mysql. For example,

Wrong:

$airport_name = "O'Hare";
$row = $dbh->do(qq{
  INSERT INTO airport (airport_name)
  VALUES '$airport_name'
  });

Right:

$airport_name = $dbh->quote("O'Hare");
$row = $dbh->do(qq{
  INSERT INTO airport (airport_name)
  VALUES $airport_name
  });

DBI prepare and placeholder values

When inserting a lot of things as in a loop situation, it’s more efficient to use prepare within the loop like so.

$dbh->do( qq{ DELETE FROM airport }) # delete existing rows
my $sth = $dbh->prepare( qq{ INSERT INTO airport SET name = ?});
while(<>)
{
  chomp;
  $sth->execute($_); # dumps each line into the table as a new row
}

To specify each value separately try this:

my $sth = $dbh->prepare( qq{ INSERT INTO airport airport_name airport_city airport_state VALUES(?, ?, ?)});
while(<>)
{
  chomp;
  my ($name, $city, $state) = split(/\t/, $_);
  $sth->execute($name, $city, $state);
}

DBI bind query results to variables

Use $sth->bind_columns() to bind results of queries to a variable in perl.

my ($airport_name, $airport_city, $airport_state);
my $sth = $dbh->prepare ( qq{
                SELECT name, city, state
                FROM airport ORDER BY airport_name
              });
$sth->execute();
$sth->bind_columns(\$airport_name, \$airport_city, \$airport_state);
while ($sth->fetch)
{
  print join("\t", $airport_name, $airport_city, $airport_state)
}

DBI connection to a database

my $dsn = "DBI:mysql:db_name:host_name";
my $dbh = DBI->connect($dsn, $username, $password);

Alternatively, you can use a file.

my $dsn = "DBI:mysql:db_name;mysql_read_default_file=/home/User/.my.cnf";

Or,

my $dsn = "DBI:mysql:db_name;mysql_read_default_file=$ENV{HOME}/.my.cnf";

Then .my.cnf would have the following information.

[client]
host=localhost
user=sampadm
password=secret

To make things specific to a particular database use mysql_read_default_group provided that the database is listed in some common place like /etc/my.cnf and specify the database (i.e., groupe). For example,

my $dsn = "DBI:mysql:db_name;mysql_read_default_group=client";

This will only read the [client] section of the file.

DBI connection to a database boilerplate

Taken from MySQL, 5th Ed. by Paul DuBois, Print ISBN-13: 978-0-321-83387-7.

#!/usr/bin/perl

use strict;
use warnings;
use DBI;

# parse connection parameters from command line if given

use Getopt::Long;
$Getopt::Long::ignorecase = 0; # options are case sensitive
$Getopt::Long::bundling = 1;   # -uname = -u name, not -u -n -a -m -e

# default parameters - all undefined initially
my ($host_name, $password, $port_num, $socket_name, $user_name);

GetOptions (
  # =i means an integer value is required after option
  # =s means a string value is required after option
  "host|h=s"      => \$host_name,
  "password|p=s"  => \$password,
  "port|P=i"      => \$port_num,
  "socket|S=s"    => \$socket_name,
  "user|u=s"      => \$user_name
  ) or exit (1);

  # construct data source
  my $dsn = "DBI:mysql:sampdb";
  $dsn .= ";host=$host_name" if $host_name;
  $dsn .= ";port=$port_num" if $port_num;
  $dsn .= ";mysql_socket=$socket_name" if $socket_name;
  $dsn .= ";mysql_read_default_group=client";

  # connect to server
  my %conn_attrs = (RaiseError => 1, PrintError => 0, AutoCommit => 1);
  my $dbh = DBI->connect ($dsn, $user_name, $password, \%conn_attrs);

DBI tracing

There are 4 levels of tracing with DBI->trace(0) having no tracing. You can turn on tracing a couple of ways. In the script, after preparing the statement do this, $sth->trace(1). Alternatively, when you connect turn it on with $dbh->{Tracelevel} = 3. Finally, you can set an environmental variable in the shell, e.g., export DBI_TRACE=3 and to turn it off export DBI_TRACE=0.

DBI meta data.

$sth->{NUM_OF_FIELDS} is a scalar and $sth->{mysql_max_length} and $sth->{NAME} are arrayrefs. They can be used to help format the output as with this example.

my @max_col_width = @{ $sth->{mysql_max_length}};
my $column_count  = $sth->{NUM_OF_FIELDS};
foreach my $i (0..$column_count)
{
  my $col_name = $sth->{NAME}->[$i];
  my $col_name_width = length $col_name;
  $max_col_width[$i] = $col_name_width if $max_col_width[$i] < $col_name_width;
  $max_col_width[$i] = 4 if $sth->{NULLABLE}->[$i] && $max_col_width[$i] < 4;
}