install:
	apt-get install mysql-client mysql-server
		asks for root password directly (cool!)
		standard password: ""

start/stop:
	(sudo /etc/rc.d/init.d/mysql start)
	sudo service mysql start
	mysqladmin -u root -p shutdown

init database: /usr/bin/mysql_install_db

db location: /var/lib/msql/

config file: /etc/mysql/my.cnf

root password:
	assign root password: mysqladmin -u root password 'new-password'
	reset under windows:
		stop mysql service
		mysql -u root --skip-grant-tables
		go to user table and modify password (or insert) using PASSWORD()

login/connect to mysql client: mysql -h host -u root -p
	mysql -u root -p

add user: 
	USE mysql;
	new user: CREATE USER user@localhost IDENTIFIED BY pwd;
	INSERT INTO user (Host, User, Password, Select_priv) VALUES ('', 'Dudel', password('supersecret'), 'Y');
	FLUSH PRIVILEGES;
	GRANT ALL PRIVILEGES ON db.* TO Dudel;
	FLUSH PRIVILEGES;

dump:
	mysqldump > file.sql
	transfer/copy database: mysqldump --opt db | mysql --host=host-name -C database
		i have my own bash script for it now
	rename database: not possible
		use the bash script for copying, then remove	
	import/restore: mysql database < dump.sql
	backup names: [remote_]dbname_year_month_day.sql
	only table creations: --no-data
	foreign keys: SET foreign_key_checks = 0; first
	truncate database: my own script mysql_truncate_database

phpmyadmin:
	config:
		sudo vi /etc/dbconfig-common/phpmyadmin.conf
		sudo dpkg-reconfigure phpmyadmin
	will be in localhost/phpmyadmin

operations:
	select database: USE db;
	list dbs: SHOW DATABASES;
	list tables: SHOW TABLES;
	list columns: show columns;
	create db: CREATE DATABASE db;
		create database if not exists db;
		mysqladmin -h localhost -u root -p create dbname
		in /var/lib/mysql/dbname
	drop database:
		mysqladmin -u root -p drop dbname
	create table: CREATE TABLE tb (field TYPE(SIZE)) ENGINE engname;
		create table if not exists ...
	column types:
		auto_increment, not null
		primary key: primary key (id)
		foreign key: foreign key (col) references Table(col) on delete/update opt
			opt: restrict | cascade | set null | no action
			needs table engine InnoDB
		index: index (col)
		unique: unique (col)
			a type of index
			alter table tb add unique (col)
	constraints:
		check is not supported by mysql! even though they accept it in the syntax!!!
		http://jordanhall.co.uk/general-articles/sql-check-constraints-not-supported-in-mysql-2204849/
		at w3schools they don't mention it! http://www.w3schools.com/Sql/sql_check.asp
	load data (tab-delimited): LOAD DATA LOCAL INFILE "file.txt" INTO TABLE tb
	insert row: INSERT INTO tb (cols) VALUES ('val1', 'val2');
		insert or update/on duplicate: INSERT ... ON DUPLICATE KEY UPDATE ...,
	update: update tb set col=expr .. 
	delete: delete from tb where
	select: SELECT col1, col2 or * FROM tb WHERE conds;
	select unique: SELECT DISTINCT col FROM tb;
	join: select * from tb left join tb2 on expr;
		left join: take all from left and map if possible with right
		join: take all which can be mapped with right
		right join: take all from right and map if possible with left
	sort: SELECT cols FROM tb ORDER BY col2[ DESC];
	pattern matching:
		normal: WHERE col like 'pattern';
			any: %
			one: _
		regexp: WHERE name REGEXP 'pattern';
			[[regexp]]
			
aggregate/group operations:
	count rows: SELECT COUNT(*) FROM tb;
	count groups: SELECT col, COUNT(*) FROM tb GROUP BY col;
	max value: SELECT MAX(col) AS label FROM tb;
	compare with all: select * from tb where col >= all (subselect);
	average: avg
	percentage: 
		credit: http://forums.mysql.com/read.php?52,134684,134741#msg-134741
		SELECT foo.c, COUNT(*) * t.factor AS pct FROM foo JOIN (SELECT 100/COUNT(*) AS factor FROM foo) AS t GROUP BY foo.c;

databases and tables:
	select from more tables: SELECT tb.col, col FROM t1, t2 WHERE t1.c = t2.c
	selected db: SELECT DATABASE();
	rename table: rename table old to new
	drop table: DROP TABLE tb
	drop all tables: using a bash script
		http://www.thingy-ma-jig.co.uk/blog/10-10-2006/mysql-drop-all-tables
	alter:
		add column: ALTER TABLE tb ADD COLUMN [col] AFTER c;
			ALTER TABLE tb ADD col type;
		rename column/alter column/change column:
			(alter table tb change col col type;)
			alter table tb modify col type;
		add primary key:
			alter table tb add primary key;
		add foreign key:
			alter table tb add foreign key (col) references table (col)
		drop foreign key:
			... drop foreign key constraint_id
			find constraint_id with  'show create table'
	remove column/delete column/drop column: ALTER TABLE tb DROP COLUMN col;
	batch mode/execute script/execute file: mysql -u user -p db < file.sql
	execute query/execute statement: -e
	backup: mysqldump --opt -u uname -p > backup.sql
	backup: mysqldump --opt -u uname -p > backup.sql

minus: select_statement minus select_statement doesn't work
	select distinct cols from t1 left join td using (cols) where b.sth is null

check null: where col is null
		
views:
	create view name as (select ...)
	drop view
	can update! (only propagatable, e.g. avg doesn't)
	can't delete from join view

engine:
	alter table name engine = innodb;
	in my.cnf default-storage-engine = innodb
	see engine: select engine from information_schema.tables where table_name = 'tb'

watch: my own script mysqlwatch
	mysqlwatch db query

transactions:
	start transaction
	commit
	abort: rollback

character sets/charsets/latin/utf8:
	set: in my.ini: default-character-set=utf8
	create database db default character set utf8;
	change:
		manually:
			for all dbs, tables, cols
			alter database db charset=utf8;
			alter table tb charset=utf8;
			alter table tb alter column col charset=utf8;
		alternative:
			dump all, recreate and import
			script:  http://yoonkit.blogspot.com/2006/03/mysql-charset-from-latin1-to-utf8.html
			iconv

string:
	concat: concat("asd","asd")