BIAD is a MySQL database. This means that all interactions with it are done via the MySQL programming language. If you are using BIAD via a user friendly GUI such as HeidiSQL or sequelPro you are just clicking menu options, but under the hood every click initiates a MySQL query to do that actual work.
If the idea of learning a programming language sounds worrying, don't panic - you probably don't need to. That's the whole point of using a GUI like HeidiSQL or SequelPro. Most basic interactions such as looking at individual tables, filtering results, sorting, searching, and data input can all be done by just clicking buttons in the GUI. However, if you want to extract more sophisticated queries, this page provides a few examples to get you started. More are available on the shared BIAD drive in the folder SQLcode which also contains any bespoke queries that have been specifically requested by a user.
What are you planning to do with the data you extract from BIAD?
It is highly unlikely that the table of data you extract from a query is the final product you are aiming for. More likely, you plan to do some analysis on it. Perhaps you plan to plot the coordinates of sites with certain cultural associations or isotopic values; or perhaps you wish to test for differences in species proportions through time. If you are planning to generate this analysis using a scripted program (such as R, bash, Python etc), you should incorporate the initial SQL query into your script which pulls the required data from BIAD and performs the analysis in one clean pipeline. There are robust and reliable R packages that easily facilitate this initial step of extracting data from BIAD into your R workspace such as RMySQL and the Github repository BIADwiki has an example R script of how this can be used. This approach still requires a MySQL query to be handed to an R function, but it is easy to merge tables in R, so often only the most trivial of MySQL queries are actually required, with all merging, conditions, and analysis performed downstream in R.
BIAD uses MySQL 5.7 for which there is of course an extensive manual. Be aware that syntax and functions in MySQL do evolve slightly over time. There is a huge amount of backward and forward compatibility, but if google searching for MySQL snippets, sometimes code from a very different version just doesn't work!
A super simple start. It does the same job if it was all on one line. It gets the column ‘TaxonCode’ from the table ‘FaunalIsotopes’. Notice the code block must end with a semicolon.
SELECT TaxonCode
FROM FaunalIsotopes;
However, it is always better to ensure you are explicit about the column and table you want by selecting table.column. This might seem overkill as your second line already states where to select from, but this is crucial once we start building joins.
SELECT FaunalIsotopes.TaxonCode
FROM FaunalIsotopes;
Better still is to be explicit about the table name and column name, in case there are any unusual characters. This is done with the back tick ` which is not a single quote ' . Without the back ticks, the parentheses after collagen are not interpreted as part of the column name, and it will fail!
SELECT `FaunalIsotopes`.`TaxonCode`, `FaunalIsotopes`.`δ13C_collagen(‰)`
FROM `FaunalIsotopes`;
Now things get fun as we start to see the need for being explicit about which table the column comes from. First consider lines 2 and 3. They join two tables (FaunalIsotopes and Phases) to form a new aggregated table. Line 1 then selects two columns from this new aggregation. Therefore it becomes crucial that we specify that we want the column TaxonCode from the table FaunalIsotopes. Notice also that the semicolon has moved to the end of line 3, which is the end of the code block.
SELECT `FaunalIsotopes`.`TaxonCode`, `Phases`.`Culture1`
FROM `FaunalIsotopes`
INNER JOIN `Phases` ON `FaunalIsotopes`.`PhaseID`=`Phases`.`PhaseID`;
Next we include a constraint. Lines 2 and 3 join two tables. Line 4 subsets it with the constraint. Finally, line 1 selects two columns from it. Notice also that the PhaseID ECS1 is placed in single quotes , not back ticks, because it is a value, not a column name. The semicolon has moved again to the new end of the code block.
SELECT `FaunalIsotopes`.`TaxonCode`, `Phases`.`Culture1`
FROM `FaunalIsotopes`
INNER JOIN `Phases` ON `FaunalIsotopes`.`PhaseID`=`Phases`.`PhaseID`
WHERE `Phases`.`PhaseID`='ECS1';
We can add further joins as required. Notice also the use of * which represents a wildcard. In other words, it selects all columns.
SELECT *
FROM `FaunalIsotopes`
INNER JOIN `Phases` ON `FaunalIsotopes`.`PhaseID`=`Phases`.`PhaseID`
INNER JOIN `Sites` ON `Sites`.`SiteID`=`Phases`.`SiteID`
WHERE `Sites`.`Country`='Hungary';Presence of diacritic signs can generate problems when using the filter bar from the default GUI HeidiSQL. This is because the default query generated in the background looks for the designated phrase across all columns of the selected table. If any of the columns has an encoding which does not include the specific diacritics, the program returns an error. This does not mean that the phrase is not present in the table, it just means that there is a conflict of encoding issues across the table. This problem will likely occur in the Sites table, where diacritics are present in the SiteName column.
The workaround for this issue requires the use of the SQL wildcard character %, which corresponds to zero or more characters. It means that the query will look for the specified phrase but depending on the position of the wildcard it will also include results where the searched phrase is preceded by other characters (when % is placed in the beginning of the phrase), followed by other characters (when % is placed at the end of the phrase), or both (when % is placed in the beginning and the end of the phrase).
The two workarounds use an example phrase ‘Łojewo’ but they should work just as well with other examples.
Workaround 1 - write a specific query which includes the phrase in a specific table:
SELECT `SiteName`
FROM Sites
WHERE SiteName LIKE '%Łojewo%;Workaround 2 - when using the default filter tool replace the default command in the filter bar with the single line:
`SiteName` LIKE '%Łojewo%'