This is a new set of notes designed to isolate any information that is not specific to a particular database engine. Previously I had miscellaneous SQL notes strewn over several documents aimed at specific database engines. Those documents are still useful and maybe even the most useful and can be found here.

Select Records

Displays all of the records in a particular table for the specified fields (* can be used as a synonym for all fields):

mysql> SELECT fieldname1, fieldname2 FROM tablename;

Complex query using multiple tables and field aliases. This selects the "name" field from the "pet" table. It calculates the age in years of the event’s "date" by subtracting the "birth" date. This age is to be used as a pseudo field which will be handled with the alias "age". The WHERE clause matches up the two tables invloved and screens for a particular type of event.

mysql> SELECT pet.name, (TO_DAYS(date) - TO_DAYS(birth))/365 AS age, remark
-> FROM pet, event
-> WHERE pet.name = event.name AND type = "litter";

Complex query using a joined table that is joined to itself. The purpose here is to match up compatible pairs of records from the same table. Notice the table aliasing in the from clause. The where clause matches up the SPECIES and ensures compitible SEX types.

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1, pet AS p2
-> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";

Count

To see how many records you have do something like:

SELECT COUNT(*) FROM mytable;

Something more complicated like a report of totals for daily transactions?

SELECT DATE(timestamp) AS TheDate, COUNT(*) AS DayMsgTot
FROM sbdMessage
GROUP BY DATE(timestamp)
ORDER BY TheDate;

Limiting Records

It’s common to want to see that data is in your table and looking roughly the way it should. You want to see some records, any will do, but all of them would take way too long. Try something like:

SELECT * FROM mytable LIMIT 5;

You can go after specific rows this way too:

SELECT * FROM mytable LIMIT 5,10;

This will produce 10 records starting at number 6 (i.e. 6-15).

Modify Records

Fix screwed up data. To change a particular record that is afoul:

mysql> UPDATE point SET x="1.902" WHERE pid="9";

Here, point is the table and the value of X needs to be modified on record #9 (PID="9").

To make some changes to existing records:

UPDATE MyTable1 SET f1="xed" WHERE f1 IS NULL;
Note
 When checking for nullness, you use f1 is [not] NULL but when checking for something you use f1="theValue"

Delete Records

Pretty much like SELECT.

mysql> DELETE FROM Actions WHERE code="bogus";

To delete an existing record:

DELETE FROM MyTable1 WHERE f1='xed' AND f2='Redundant';

To delete all records in one shot:

DELETE FROM MyTable1 WHERE 1;

Fancier Queries

Standard format showing condition and sort preference:

SELECT * FROM MyTable1 WHERE MyField1!="undesirable" ORDER BY MyField2;

The greater than means "later than" or "after" the date mentioned. Note that the date can be in many formats. Tends to like something like this though:

SELECT code FROM actions WHERE trans="acquisition" AND date>03/3/2011;

Here’s how you can get a report of how many items are associated with a particular property. In this example, how many actions did each contributer do?

SELECT user,COUNT(*) FROM actions GROUP BY user;'
helper1|32
helper2|16
xed|455

Here is how to get a list of the most popular entries.

SELECT COUNT(id) n,property FROM proplist GROUP BY property ORDER BY n DESC;

Often you want to find something where you don’t have an exact match. Here is how to do a case insensitive substring match:

SELECT * FROM thetable WHERE refcode LIKE '%123%';

Here the % is a wildcard and the results would be any containing "123".

Joining

Full gory details on the riot of possible join operations can, of course, be found on Wikipedia.

To join, use the full name of the field which is tablename.fieldname. Here’s an example of a simple "equi-join":

SELECT actions.code,vendors.phone
FROM actions,vendors
WHERE actions.code=vendors.code
  AND trans="acquisition"
  AND date>03/3/2011;

This is also a "natural join" where identically named fields are combined into a single field that is returned.

The formal JOIN keyword works like this.

SELECT t1.f1,t1.f2,t2.f1,t2.f2
FROM t1
INNER JOIN t2 ON t1.f1 = t2.f1
WHERE t2.f2 LIKE '%something%';
Types of joins

  • INNER JOIN - Only records present in both tables returned.

  • LEFT JOIN - All records from the first (left) table are returned joined to any counterpart (or not) in the second table.

  • RIGHT JOIN - Like left join but reversed. Not in SQLite but just switch the order and you get the same thing.

  • FULL OUTER JOIN - All records from both tables return matched where possible, with the other table’s missing fields blank where no match is found. SQLite lacks this specfically but it can be simulated with UNION.

Modifying

Create a new table:

CREATE TABLE MyTable1 (f1 TEXT PRIMARY KEY, f2 TEXT, f3 INTEGER);

To create a new record:

INSERT INTO MyTable1 VALUES('val4field','val4f2',10);

Example SQL

Creating Tables
CREATE TABLE team (
    teamid INTEGER PRIMARY KEY,
    city TEXT,
    teamname TEXT,
    league TEXT);
CREATE TABLE player (
    playerid INTEGER PRIMARY KEY,
    playername TEXT,
    salary INTEGER,
    position TEXT,
    teamID INTEGER,
    FOREIGN KEY(teamID) REFERENCES team(teamid)
    );
Table 1. Table: player

playerid

playername

salary

position

teamID
Table 2. Table: team

teamid

city

teamname

league
Simple Joins
SELECT T.league,COUNT(T.city) c FROM player P
    JOIN team T ON P.teamID=T.teamID
    WHERE salary > 1000000
    GROUP BY T.league
    ORDER BY c DESC;

This produces a list of the leagues and the number of players who make more than a million dollars.

Table 3. Simple Join Result

ALE

93

NLC

76

NLW

73

NLE

69

ALC

64

ALW

56
Aggregate Function
SELECT P.position,COUNT(P.position),AVG(P.salary) s FROM player P
    JOIN team T ON P.teamID=T.teamID
    GROUP BY P.position
    ORDER BY s DESC;
Table 4. Aggregate Function Example Result

Designated Hitter

8

10481246.75

First Baseman

48

4942973.70833333

Third Baseman

48

4385985.9375

Outfielder

148

4018163.93243243

Pitcher

423

3188562.53427896

Second Baseman

54

3140917.33333333

Shortstop

45

2995696.22222222

Catcher

69

2160711.23188406

Infielder

12

925083.333333334

Joining The Same Table Twice

Sometimes you have a table that you need to join to multiple times. For example, imagine a table of baseball games in conjunction with the previous baseball tables:

Table 5. Matches

matchid

date

hometeamID

awayteamID

In this table there are two fields that contain foreign keys which both relate to the team table. Here’s the syntax for creating such joins.

Multiple Joins To The Same Table
SELECT AT.teamname, HT.teamname, HT.city
    FROM match
    JOIN team AT ON awayteamID = AT.teamid
    JOIN team HT ON hometeamID = HT.teamid
    WHERE HT.city = "San Diego";

In this query the away team and the home team are related to the same table, but in two separate JOIN clauses with two separate aliases for the table (AT and HT) thereby distinguishing them. This query shows the visiting team and the home team for all games in San Diego.

Table 6. Multiple Join Example Result

Rockies

Padres

San Diego

Giants

Padres

San Diego

Here’s another example I’ve run into:

SELECT D.name, M1.name, M2.name FROM Reaction
JOIN Drug D ON drugID = D.id
JOIN Malady M1 ON usemaladyID = M1.id
JOIN Malady M2 ON effectmaladyID = M2.id
WHERE reports > 1000;

Complex Query Example

Here’s a query I wrote that contains a bunch of useful tricks:

SELECT
v.code,v.name,
v.url, v.searchurl,
A.user,
STRFTIME('%Y.%m.%d %H:%M', A.maxdate),
CAST( ROUND(JULIANDAY("NOW")-JULIANDAY(A.maxdate),1) AS TEXT),
v.land, v.phone,v.types,
--v.alive, v.public, v.date_init,
    CASE
    WHEN JULIANDAY("NOW")-JULIANDAY(A.maxdate)> 183 THEN "sixmon"
    WHEN JULIANDAY("NOW")-JULIANDAY(A.maxdate)> 90 THEN "threemon"
    WHEN JULIANDAY("NOW")-JULIANDAY(A.maxdate)> 30 THEN "onemon"
    WHEN JULIANDAY("NOW")-JULIANDAY(A.maxdate)> 7 THEN "oneweek"
    ELSE "recent"
    END
FROM Vendors v
INNER JOIN
    (SELECT code,user,MAX(date) AS maxdate FROM Actions GROUP BY code) A
    ON v.code=A.code
ORDER BY A.maxdate
;

The -- starts a comment in SQL. (Beware!) The CASE allows the field to contain one of a few possibilities. The CAST can help prevent problems where your programming language interface gets back a float but you’re processing everything as text. The AS allows aliases to be put to things that are hard to specify. This style of INNER JOIN allows only the latest action record to be considered for each vendor record (each vendor can have many actions, one to many).

The Dreaded Microsoft Access

Did some sad person send you a "database" in a .mdb format? That’s Microsoft Access and can be quite a pain. The way to recover from this calamity is to use the mdbtools suite which can be run from sensible Linux command lines. Most Linux distributions have a package for this called mdbtools.

mdb-array

?

mdb-header

?

mdb-parsecsv

?

mdb-schema

Creates an SQL process for recreating the tables, like SQLite’s .dump does. This does work with SQLite, but sometimes the table names have ~ in them and must be cleaned of that.

mdb-tables

Print a list of the tables contained in the specified DB file.

mdb-export

Export a particular table in the specified database to bar separated data. There is also a mode (-I) that will compose IMPORT statements to use in SQLite reconstructions but note the trailing semicolon is missing.

mdb-hexdump

?hexdump?

mdb-prop

?Properties?

mdb-sql

Execute very simple SQL statements on the data within.

mdb-ver

What format the database is in.

Unix join Command

SQLite is light but what if it’s not light enough. What if you want crazy light? If you don’t want to depend on any kind of SQL software you can use a classic Unix command join to do many of your simple relational database tasks.

Here is an example of how this works. There are two input files, A and B. For each line (record), join finds matching fields in the two files and combines the fields in a specified format (-o).

$ cat A
01|AK|Alaska
02|AL|Alabama
03|AZ|Arizona
04|AR|Arkansas
05|CA|California
06|CO|Colorado
07|CT|Connecticut
08|DE|Delaware
09|FL|Florida
10|GA|Georgia
11|HI|Hawaii
12|ID|Idaho

$ cat B
Juneau|01
Anchorage|01
Montgomery|02
Flagstaff|03
Tuscon|03
Little Rock|04
San Diego|05
San Jose|05
Riverside|05
Keystone|06
Denver|06
Leadville|06
Hartford|07
Atlanta|10
Hilo|11
Honolulu|11
Boise|12

$ join -t'|' -2 2 -o 2.1,1.2 A B
Juneau|AK
Anchorage|AK
Montgomery|AL
Flagstaff|AZ
Tuscon|AZ
Little Rock|AR
San Diego|CA
San Jose|CA
Riverside|CA
Keystone|CO
Denver|CO
Leadville|CO
Hartford|CT
Atlanta|GA
Hilo|HI
Honolulu|HI
Boise|ID

$ join -t'|' -2 2 -o 2.1,1.3 A B
Juneau|Alaska
Anchorage|Alaska
Montgomery|Alabama
Flagstaff|Arizona
Tuscon|Arizona
Little Rock|Arkansas
San Diego|California
San Jose|California
Riverside|California
Keystone|Colorado
Denver|Colorado
Leadville|Colorado
Hartford|Connecticut
Atlanta|Georgia
Hilo|Hawaii
Honolulu|Hawaii
Boise|Idaho