SQL

Sometimes there’s a need to keep a simple log in a database. A common format could be a table with a layout like this:

log

area (char)
lognotice (char or text)
logtime (timestamp when the event was logged).

Fetching

Fetching all log entries from a certain area is a simple matter of fetching by the area field, but when building a dashboard with the most recent entry from each area is slightly more complicated - the Query to fetch the data could typically look like this:

When playing the role of the DBA, it’s often useful to get a quick listing of how many rows each table in a database contains. The syntax for this is pretty simple in Mysql:

SELECT table_name, table_rows 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = '***database name***';

Replace database name with the actual database name in the SQL above.

Notice that when using innodb tables, it’s only a rough estimate.

Mysql is a wonderful database, and while many use it, most people only scratch the surface of what the database can do. One of the practical functions available is the substring_index function, and an imaginary mailing list example is a nice way to show how to use it.

Let imagine we have a mailinglist in a table named “mailinglist” and it has a (char) column with the email addresses subscribed to the list. We now want to figure out how many users, that are subscribed from the various domains in the list.

It seems there are two camps when it comes to SQL and how to do database optimizations - the “generic camp” and “the specialist camp”. While I don’t consider myself an extremist, I am absolutely in the specialist camp and this little post is an explanation of why.

SQL is a generic database langauge . There are a few different standards in use (the language has progressed over time), but the core of the SQL language is pretty much the standard in most databases. It’s probably also standard - in any database - that the SQL standard has been extended with database-specific extensions which provides optimizations, functions or other options not available in the SQL standard.

Through the mysql client:

update mysql.user set password=password('NEW_PASSWORD') where user='USERNAME' and host='HOSTNAME';
flush privileges;

Through the command line:

mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD

Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.

Finding records that do not match between two tables.

          CREATE TABLE bookreport (
            b\_id int(11) NOT NULL auto\_increment,
            s\_id int(11) NOT NULL,
            report varchar(50),
            PRIMARY KEY  (b\_id)

          );

          CREATE TABLE student (
            s\_id int(11) NOT NULL auto\_increment,
            name varchar(15),
            PRIMARY KEY  (s\_id)
          );

          insert into student (name) values ('bob');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'A Death in the Family');

          insert into student (name) values ('sue');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'Go Tell It On the Mountain');

          insert into student (name) values ('doug');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'The Red Badge of Courage');

          insert into student (name) values ('tom');

 To find the sudents where are missing reports:

          select s.name from student s
            left outer join bookreport b on s.s_id = b.s_id
          where b.s_id is null;

              +------+
              | name |
              +------+
              | tom  |
              +------+
              1 row in set (0.00 sec)

 Ok, next suppose there is an orphan record in
 in bookreport. First delete a matching record
 in student:

       delete from student where s_id in (select max(s_id) from bookreport);

 Now, how to find which one is orphaned:

       select * from bookreport b left outer join
       student s on b.s_id=s.s_id where s.s_id is null;

     +------+------+--------------------------+------+------+
     | b_id | s_id | report                   | s_id | name |
     +------+------+--------------------------+------+------+
     |    4 |    4 | The Red Badge of Courage | NULL | NULL |
     +------+------+--------------------------+------+------+
     1 row in set (0.00 sec)

To clean things up (Note in 4.1 you can’t do subquery on same table in a delete so it has to be done in 2 steps):

Assume you have the table “exams” in the database “test”.Then, the following will give you XML output if executed from the shell prompt with the “-X” option. For html output use the “-H” option.

mysql -X -e "select \* from exams" test

To dump data into a comma separated file use this:

  SELECT * INTO OUTFILE 'tablename.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'n'
  FROM tablename;

Replace tablename with the tablename of the table you which to dump to a file.

Loading Data into Tables from Text Files.

Assume you have the following table.

            CREATE TABLE loadtest (
                pkey int(11) NOT NULL auto\_increment,
                name varchar(20),
                exam int,
                score int,
                timeEnter timestamp(14),
                PRIMARY KEY  (pkey)
               );

And you have the following formatted text file as shown below with the unix “tail” command:

    $ tail /tmp/out.txt
    'name22999990',2,94
    'name22999991',3,93
    'name22999992',0,91
    'name22999993',1,93
    'name22999994',2,90
    'name22999995',3,93
    'name22999996',0,93
    'name22999997',1,89
    'name22999998',2,85
    'name22999999',3,88

NOTE: loadtest contains the "pkey" and "timeEnter" fields which are not
present in the "/tmp/out.txt" file. Therefore, to successfully load
the specific fields issue the following:

         mysql> load data infile '/tmp/out.txt' into table loadtest
                  fields terminated by ',' (name,exam,score);

Getting a random roll of the dice:

          CREATE TABLE dice (
            d\_id int(11) NOT NULL auto\_increment,
            roll int,
            PRIMARY KEY  (d\_id)
          );

          insert into dice (roll) values (1);
          insert into dice (roll) values (2);
          insert into dice (roll) values (3);
          insert into dice (roll) values (4);
          insert into dice (roll) values (5);
          insert into dice (roll) values (6);

          select roll from dice order by rand() limit 1;

Fetching the most recent entry from a log-table

Fetching

Mysql: display row count for all tables in a database

Substring magic with mysql

Should you use sql specific statements?

Mysql: Change account password

Mysql: Delete orphan records

Mysql: Dump data in XML or HTML

Mysql: Dumping data to a file

Mysql: Loading data from file

Mysql: Random dice