From SwissExperiment

MySQL Help

Contents 1 MySQL Help 1.1 At SLF 1.2 Usefull SQL commands 1.2.1 Data extraction 1.2.2 Database management

Very frequently, databases use SQL as the language for expressing queries. Various tutorials exist on the web:

• http://www.firstsql.com/tutor.htm
• http://www.w3schools.com/sql/sql_intro.asp

At SLF

On a Linux computer, you can use the Mysql client in order to perform your queries:

• at the command prompt, enter "mysql --host={database host name} --user={user name on the database} --password={matching password} --database={database name}"
• then, you have a prompt allowing you to enter SQL commands
• For the Wannengrat database, the host is ws-9-53, the user name is request and the database name is data.

Of course, other interfaces allow you to perform the same queries, like DbVisualizer, Mysqlcc, etc (look for the sql entry field)

Usefull SQL commands

Data extraction

• retrieve snow heights for the stations wan2 and wan5: SELECT wan2.timestamp,wan2.hs1,wan5.hs1 FROM wan2,wan5 WHERE wan2.timestamp=wan5.timestamp AND wan2.timestamp>'2007-01-01 00:00:00' AND wan2.timestamp<'2007-01-30 23:59:59'

Database management

These commands might necessitate the administrator privileges ...

• correct some data in the database (offset correction): UPDATE WANxx SET hs1=hs1+xxx WHERE timestamp <= 'date 1' AND timestamp >= 'date 2'
• extract the whole database structure and content for archiving: mysqldump --all-databases | bzip2 > backup.slq.bz2
• extract only the data from the database, re-order by date (if needed): mysqldump --where '1=1 ORDER BY timestamp' data | bzip2 > backup.slq.bz2
• reload some values from an sql file (from within mysql client): source file.sql