User Tools

Site Tools


mysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
mysql [2013/01/09 10:34]
mantis [Table Types]
mysql [2013/01/09 10:36] (current)
mantis k
Line 1: Line 1:
 +
 +
 +====== Setup ======
 +===== Installing =====
 +<​code>​
 +  sudo aptitude install mysql-server
 +</​code>​
 +
 +===== Configuration =====
 +<​code>​
 +sudo vi /​etc/​mysql/​my.cnf ​  
 +sudo /​etc/​init.d/​mysql restart
 +</​code>​
 +==== Remote Access ====
 +If you want to access your database remotely you need to do some configuring. ​
 +  * First tell mysql to bind to an external ip address (the default is to bind to 127.0.0.1). Edit **my.cnf** and find the line
 +<​code>​
 +bind-address = 127.0.0.1
 +</​code>​
 +change that to the IP-address you want mysql to bind to.
 +
 +  * Then you need to tell mysql which users are granted remote access. This can be done via the "​Privileges"​ Page in phpmyadmin (if installed) or via the console using something like
 +<code sql>
 +GRANT ALL ON *.* TO user@'​10.101.10.128'​ IDENTIFIED BY '​my_secret_password';​
 +</​code>​
 +  * Some Notes
 +  * Remember that its not always very wise to GRANT ALL. Instead you can grant USAGE or any subset of SELECT,​INSERT,​UPDATE,​DELETE,​CREATE,​DROP,​...
 +  * *.* grants the rights to all Database Objects in the System. To grant rights only to a specific DB you can use something like **GRANT ALL ON Tolldata.* TO  ...**
 +  * The //​IDENTIFIED BY '​password'​ // clause is optional. If you choose to remove it, remember that you **must not** provide a password when you try to log into the database, or else the login will fail!
 +  * see http://​dev.mysql.com/​doc/​refman/​5.0/​en/​adding-users.html for more information on user privileges
 +
 +===== User Management =====
 +To create a user
 +<code sql>
 +CONNECT db_name ;
 +CREATE USER '​gosmart'​@'​localhost'​ IDENTIFIED BY '​gosmart';​
 +GRANT SELECT,​INSERT,​UPDATE,​DELETE ON *.* TO '​gosmart'​@'​localhost';​
 +</​code>​
 +
 +see http://​dev.mysql.com/​doc/​refman/​5.0/​en/​adding-users.html for more information.
 +
 +
 +====== SQL ======
 +===== Working with Dates =====
 +==== Resources ====
 +  * http://​dev.mysql.com/​doc/​refman/​5.1/​en/​date-and-time-functions.html#​function_addtime
 +  * http://​www.weberdev.com/​ViewArticle/​Date-Arithmetic-With-MySQL
 +
 +==== Calculate Time Interval from Date ====
 +<code sql>
 +SELECT timestamp, FLOOR(HOUR(timestamp)*4 + MINUTE(timestamp)/​15) FROM lane_measurements; ​
 +</​code>​
 +==== Add Time Period to a Date ====
 +<code sql>
 +SELECT timestamp, DATE_ADD(DATE(timestamp),​ INTERVAL 30 MINUTE ) FROM lane_measurements;​
 +</​code>​
 +===== Export to CSV File =====
 +<code sql>
 +SELECT * 
 +INTO OUTFILE "/​home/​hkoller/​prototype_complete.csv" ​
 +FIELDS TERMINATED BY ';'​ OPTIONALLY ENCLOSED BY '"'​ LINES TERMINATED BY '​\n'​
 +FROM `prototype_complete` ​
 +ORDER BY clusterid,​roadid,​laneid,​`interval` asc;
 +</​code>​
 +
 +
 +If you require headers in your CSV, you have to use a workaround (see [[http://​dev.mysql.com/​doc/​refman/​4.1/​en/​select.html here]]):
 +<code sql>
 +SELECT '​company','​contact_name'​ -- writing the header
 +UNION 
 +SELECT company , contact_name ​
 +INTO OUTFILE '/​tmp/​test.csv'​ FIELDS TERMINATED BY ','  ​
 +FROM answers;
 +</​code>​
 +
 +===== Import from CSV File =====
 +<code sql>
 +LOAD DATA LOCAL INFILE '​road_frc.csv'​
 +INTO TABLE road_2_frc
 +FIELDS TERMINATED BY ','​
 +LINES TERMINATED BY '​\n'​
 +(roadid,​frc);​
 +</​code>​
 +====== Hints ======
 +
 +===== Table Types =====
 +MySQL supports different table types with individual strengths and weaknesses. The default table type  is //MyISAM// which is fast but lacks some essential DB features. In order to get a more complete featureset (Foreign Keys, Transactions,​..) use InnoDB. eg.
 +<code sql>
 +CREATE TABLE test (
 +    id INTEGER PRIMARY KEY NOT NULL,
 +    name VARCHAR(20)
 +) **TYPE InnoDB**;
 +</​code>​
 +
 +see http://​www.devshed.com/​c/​a/​MySQL/​Storage-Engine-Table-Types/​ for a detailed discussion of the table types
 +
 +
 +===== GROUP BY weirdness =====
 +It seems that MySQL allows SELECT statements with a GROUP BY clause to contain //normal fields// (fields which are not part of an aggregate function or the grouping). Here is an example query to illustrate the problem: ​
 +<code sql>
 +  mysql> SELECT ​  ​species
 +    -> ,        MIN(birth) ​ -- birthdate of oldest pet per species
 +    -> ,        MAX(birth) ​ -- birthdate of youngest pet per species
 +    -> ,        birth       -- birthdate of ... uh oh...!
 +    -> FROM     ​pet_table
 +    -> GROUP BY species;
 +</​code>​
 +
 +The result for column "​birth"​ in the above example is a more or less random value. Modern MySQL versions provide the config parameter ​
 +
 +sql_mode=ONLY_FULL_GROUP_BY
 +(put in /​etc/​mysql/​my.cnf)  ​
 +
 +to disable this behavior (and have MySQL reject such queries like any sane DBMS should).
 +
 +See http://​rpbouman.blogspot.com/​2007/​05/​debunking-group-by-myths.html ​ (the Section about "​Running into trouble with GROUP BY" ) for details.
 +
 +====== Backup ======
 +
 +===== creating a dumpfile =====
 +<code bash>
 + ​mysqldump databasename > dumpfile.dump
 +</​code>​
 +===== restoring the database =====
 +
 +make sure that the database exists before the restauration or recreate it.
 +<code bash>
 + mysql databasename < dumpfile.dump
 +</​code>​
  
mysql.txt ยท Last modified: 2013/01/09 10:36 by mantis