Step by step commands

You must have SID or DIF installed to run the SQL commands that use spherical coordinates functions / procedures.

Open two terminals and in one start the MySQL client application as root, preferably from the Data working directory (and with the files used in the examples present in it), and give the (MySQL) root password:
shell> mysql --local-infile=1 -u root -p
Below the MySQL client default prompt mysql> (or MariaDB [...]>) is omitted for an easier cut-and-paste.

Create the mpe2018db database:

create database mpe2018db;

Create a course reference user mpeusr:

create user mpeusr@localhost identified by "mpe2018pass";

Grant all privileges (select, insert, delete, update, ...) to user mpeusr on the database mpe2018db:

grant all privileges on mpe2018db.* to mpeusr@localhost;
flush privileges;
exit;

Because you can perform SQL queries directly from the shell command line using the option -e for the mysql command, lets give some simple usage examples:

shell> mysql -u mpeusr -pmpe2018pass mpe2018db -e "show databases"
shell> mysql -u mpeusr -pmpe2018pass mpe2018db -e "show tables from information_schema"
shell> mysql -u mpeusr -pmpe2018pass -e "describe information_schema.tables"
shell> mysql -u mpeusr -pmpe2018pass -e "describe columns" information_schema
shell> mysql -u mpeusr -p -e "select version(), database(), user(), connection_id()"
Enter password: mpe2018pass
shell> mysql -u mpeusr -p -e "select curdate(), curtime(), now()" mpe2018db
Enter password: mpe2018pass

In these cases the database name mpe2018db could be omitted because we are not using any table in it. It will be mandatory for other queries we will use in the course.
Now let’s login as mpeusr and move to the mpe2018db DB:

shell> mysql -u mpeusr -p
Enter password: mpe2018pass

If you want to avoid to give user and password at each mysql invocation, you can set the default values in the special file .my.cnf placed in your home directory ($HOME):

[client]
user = mpeusr
password = mpe2018pass

Of course, if you prefer to work always with the root privileges, can set user = root and set the corresponding password. See also below.

use mpe2018db;
-- see who you are and in which database
select user(), current_user(), database();
+------------------+------------------+------------+
| user()           | current_user()   | database() |
+------------------+------------------+------------+
| mpeusr@localhost | mpeusr@localhost | mpe2018db  |
+------------------+------------------+------------+

In MySQL commands / functions / procedures / column names / etc. are NOT case sensitive. However, to be compliant with any operating system, you must assume that anything connected to your machine, like the database, table and user names ARE case sensitive!

First demo tables

Note: We assume that you have created a working directory, e.g. mpe2018, in your $HOME

shell> cd
shell> mkdir mpe2018
shell> cd mpe2018
shell> mkdir Data
shell> ...

and downloaded there (or will download when requested) the files with a directory structure like that reported in the course summary.

An optical catalogue: the UCAC-2
From the second terminal just copy the UCAC2 table files (MyISAM format) from the Data directory into the mpe2018db database directory. Can check its location in several ways, depending on installation and OS. These methods are generic:
shell> mysqladmin -u root -p variables | grep datadir
or
shell> mysql -u root -p -e "show variables like 'datadir'"

or from the MySQL client terminal:

SHOW VARIABLES LIKE 'datadir'; 
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| datadir       | /usr/local/mysql/var/ |
+---------------+-----------------------+

Assuming it is in /var/lib/mysql/:
shell> sudo chown mysql:mysql ~/mpe2018/Data/ucac2_initial.*
shell> sudo cp -a ~/mpe2018/Data/ucac2_initial.* /var/lib/mysql/mpe2018db/

To avoid duplicated file, instead of cp -a you could have used mv !
Let’s check the catalogue:

-- be sure the table is visible
flush tables;
-- show its structure
describe ucac2_initial;
-- show the number of rows it has
select count(*) from ucac2_initial;

We’ll use this catalogue later (see below).

The system information_schema table:

show tables from information_schema;
describe information_schema.tables;
describe information_schema.columns;
select * from information_schema.tables where table_name='ucac2_initial';
select * from information_schema.columns where table_name='ucac2_initial';
SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema='mpe2018db' AND table_type != 'View';
SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema='mpe2018db' AND table_type = 'View';
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name like 'sky%';

An X-ray catalogue from the Chandra archive (see https://cxc.cfa.harvard.edu/csc/cli/).
Create the Chandra sources demo table using the SQL dump file (see file):

source ~/mpe2018/Soft/SQL/crea_cx_tab.sql
show create table cx_src1;

-- show the number of rows
select count(*) from cx_src1;

Import the Chandra sources demo table created with the command (in the Data dir., where csc_query.sql is located):
shell> curl -o csc_out.csv --form coordFormat=decimal --form outputFormat=csv --form nullAppearance=NULL --form query=@csc_query.sql "https://cda.cfa.harvard.edu/csccli/getProperties"
Have a look to the output file csc_query.csv.

load data local infile "~/mpe2018/Data/csc_out.csv" into table cx_src1 IGNORE 11 LINES;

or, to make explicit the defaults parameters:

load data local infile "~/mpe2018/Data/csc_out.csv" into table cx_src1 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' IGNORE 11 LINES;

If LOCAL capability is disabled, on either the server or client side, a client that attempts to issue a LOAD DATA LOCAL statement receives the following error message:

ERROR 1148: The used command is not allowed with this MySQL version

See the MySQL reference page.

Will note Warnings: 35862. That is because of logical fields marked TRUE/FALSE and empty fields that are not managed correctly. In fact:

show warnings;
select * from cx_src1 limit 10;

select count(*) from cx_src1 where extent_flag=1 or conf_flag=1;
-- will return 0

We should have used BIT datatype, but it is not recommended. Then we use the correct import transforming them into 1/0 and also consider empty flux columns:

load data infile "~/mpe2018/Data/csc_out.csv" into table cx_src1 IGNORE 11 LINES (name,ra,de,@c4,err_ellipse_r0,err_ellipse_r1,@c7,@c8,@c9,significance) set extent_flag=(@c4 like "%TRUE"), conf_flag=(@c7 like "%TRUE"), flux_aper_w = nullif(@c8,""), flux_aper_b = nullif(@c9,"");

Note the use of local variables (starting with @) and the % in the like comparison. Note also that nullif(exp1,expx2) returns NULL if expr1=expr2:

select * from cx_src1 limit 10;
select count(*) from cx_src1 where extent_flag=1 or conf_flag=1;
-- will now return 3282

select count(*) from cx_src1 where isnull(flux_aper_b);

or

select count(*) from cx_src1 where flux_aper_b is null;
-- will return 11193

See also the function ifnull().

Some SQL commands you can try:

help
help contents
status
prompt MySQL>
prompt \u@\h [\d]>
prompt  -- back to default prompt
show tables;
show table status like "cx%" ;
delimiter //  -- change the end command delimiter
show collation //
show engines\G
delimiter ;  -- back to default delimiter
select version(), database(), user(), connection_id();
select curdate(), curtime(), now();

select @sql_mode;
select @@sql_mode;

Let’s perform a query on the INFORMATION_SCHEMA to see which tables I have access to, excluding those in INFORMATION_SCHEMA itself:

-- see who you are and in which database
select current_user(), database();
+------------------+------------+
| current_user()   | database() |
+------------------+------------+
| mpeusr@localhost | mpe2018db  |
+------------------+------------+

use mpe2018db;
SELECT table_schema, table_name from INFORMATION_SCHEMA.TABLES WHERE table_schema != 'INFORMATION_SCHEMA';

You can customize the MySQL client editing the user specific configuration file $HOME/.my.cnf (or the system level my.cnf) in the section [client] or using environment variables. An example file content is (comment rows start with #):

[client]
user     = root
password = _root_password_
host     = localhost
socket   = /tmp/mysql.sock
#port     = 3306

This would allow the user to enter the MySQL client without the need to enter user and password, but simply typing mysql. For example to list the table in the mpe2018db database:

shell> mysql -e "show tables from mpe2018db"

Some environment variable also affect the MySQL behavior. From the terminal you can, for example, change the prompt like this:
(bash) export MYSQL_PS1="\u@\h [\d]> "
(tcsh) setenv MYSQL_PS1 "\u@\h [\d]> "
which will produce something like: mpeusr@localhost [mpe2018db]>

Let’s practice with SQL performing SELECT queries, creating new tables and changing columns format. You do not need to follow the examples in this order. Just make sure you have SID (or DIF) installed.

Practicing with the Messier Catalogue

TSV files used: messier.tsv and messier_legend.tsv
Download the files into your working data directory Data and go to the MySQL client terminal.
Step by step exercises using SID here.
Step by step exercises using DIF here.

Practicing with the Bright Stars Catalogue

Catalogue reference: https://cdsarc.u-strasbg.fr/viz-bin/Cat?V/50
SQL dump files used: bsc.sql and bsc.sqlsimpleBSC.sql (compact version).
Download the files into your working data directory Data and go to the MySQL client terminal.
Step by step exercises here.

Practicing with the ASCC2.5

Catalogue reference: https://cdsarc.u-strasbg.fr/viz-bin/Cat?I/280A
SQL dump file used: ascc25_initial.sql.gz
Copy and decompress it into the data directory Data and go to the MySQL client terminal.
Step by step exercises here.

Practicing with a sample of SDSS galaxies

Catalogue reference: SDSS SkyServer
CSV file used: abell2065_2deg_radius.csv
Copy it into the directory Data and go to the MySQL client terminal.
Step by step exercises here.

Exporting data to CSV file

First check which is the MySQL predefined dump directory you can write into:

shell> mysqladmin -u root -p  variables | grep secure_file_priv

Returning for example /var/lib/mysql-files/.
The default value is platform specific and can be changed using the --secure-file-priv=dir option given to the MySQL server deamon mysqld. See the manual page for more details.

Then use this directory in the query:

SELECT ramas/3.6e6 as radeg, decmas/3.6e6 as decdeg, Vmm/1000 as Vmag, (Bmm-Vmm)/1000 as color
FROM ascc25
WHERE Bmm > -9999 AND Vmm > -9999
LIMIT 1000 -- remove to dump the whole catalogue
INTO OUTFILE '/var/lib/mysql-files/ascc25_out.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n';

Check the output file, e.g. sudo vi /var/lib/mysql-files/ascc25_out.csv.

So let’s explain the various pieces.
The first part before the INTO line is your query. This can be any SELECT query, including conditions and joins, to retrieve the data-set you wish.
After the SELECT query, the CSV output data definitions start:

INTO OUTFILE: here you state the path where you want MySQL to store the CSV file. Keep in mind that the path needs to be writable for the MySQL user.
FIELD TERMINATED BY ',': this is the field delimiter character. You could have used a semi-colon or a tab (\t) instead.
OPTIONALLY ENCLOSED BY: the character you use to enclose the fields in.
ESCAPED BY: the escape character used in case of special characters in your output
LINES TERMINATED BY: here you define the line-end.

The LIMIT 1000 is just to reduce the output file size.
In the output CSV you might see values as \N when NULL values are present in the table. This is a required if you want to re-import that CSV file into a DB table.

User defined SQL functions

Let’s add a function to display in scientific notation real/double values. First exit the MySQL client session and enter as user root:

mysql> exit;
shell> mysql -u root -p mpe2018db;

Then let’s create the function (we keep 6 fractional digits):

drop function if exists sci;
DELIMITER $$
create function sci (val DOUBLE) returns text
deterministic language sql
begin
  set @exp = IF(val=0, 0, FLOOR(LOG10(ABS(val))));
  return CONCAT(FORMAT(val*POWER(10,-@exp), 6), 'e', @exp);
end$$
DELIMITER ;

select sci(min(Vmm/1000)), sci(max(Vmm/1000)), sci(avg(Vmm/1000)) from ascc25 where Vmm > -9999;

+--------------------+--------------------+--------------------+
| sci(min(Vmm/1000)) | sci(max(Vmm/1000)) | sci(avg(Vmm/1000)) |
+--------------------+--------------------+--------------------+
| -1.110000e0        | 1.675000e1         | 1.107064e1         |
+--------------------+--------------------+--------------------+

Assuming the color field is present (if not, create it as the difference between Bmm/1000 and Vmm/1000):

select sci(min(color)), sci(max(color)), sci(avg(color)) from ascc25 where color != -900;

And now let’s extend the privileges for the user mpeusr:

show grants for mpeusr@localhost;

grant execute on function mpe2018db.sci to mpeusr@localhost;

Note how in the examples above we mixed the use of lower / upper case for command and function names, as well as for column names. Again, this is because MySQL is not case sensitive!