mySQL_learning.sql

-- installation: http://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/index.html--repo-qg-apt-replacing
-- a quick reference from 3wschool:
http://www.w3schools.com/sql/sql_quickref.asp

-- check mysql service status:
 sudo service mysql status
 sudo service mysql stop
 sudo service mysql start



-- start mysql interactively: 
 abc@abc-Notebook:~/Dropbox/html$ mysql -u root -p
 Enter password: aj...*

 mysql -u user -h hostname -p
 mysql -h host -u user -p menagerie(name of a database you want to use directly)

 \c -- abandon
 \q l

-- examples:
 112 mysql -u sudhir -pmyDB007 -h sdev1.ams.sunysb.edu
 114 mysql -u sudhir -pmyDB007 -h yyao@sdev1.ams.sunysb.edu
 115 mysql --help
 118 mysql -u root -p
 121 mysql -u root -p immunoglobulin



-- system information: 
 select version(), current_date();
 select now(), user();


-- create and change databases:
 select database(); -- To find out which database is currently selected
 show databases;
 create database test;
 use test; 
 SHOW TABLES;



-- create tables:
 CREATE TABLE pet (
 name VARCHAR(20) NOT NULL,
 owner VARCHAR(20) DEFAULT 'yy',
 species VARCHAR(20),
 sex CHAR(1),
 birth DATE,
 death DATE
 CHECK (ID > 0));

 CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer CHAR(20) DEFAULT '' NOT NULL,
 price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer));

 
 CREATE TABLE person (
	 id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
	 name CHAR(60) NOT NULL,
	 PRIMARY KEY (id));
	-- due to auto_increment, null in the below insert will be replaced.
	INSERT INTO person VALUES (NULL, 'Antonio Paz');


	CREATE TABLE animals (
	 id MEDIUMINT NOT NULL AUTO_INCREMENT,
	 name CHAR(30) NOT NULL,
	 PRIMARY KEY (id));
	INSERT INTO animals (name) VALUES
	 ('dog'),('cat'),('penguin'),
	 ('lax'),('whale'),('ostrich');
	SELECT * FROM animals;

	--An index can be created in a table to find data more quickly and efficiently. The users cannot see the indexes, they are just used to speed up searches/queries. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.
	CREATE INDEX index_name 
	ON table_name (column_name);
	-- DROP INDEX Syntax for MySQL:
	ALTER TABLE table_name DROP INDEX index_name


	--To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE, like this:
	ALTER TABLE tbl AUTO_INCREMENT = 100;


 SHOW CREATE TABLE tbl_name -- hows the CREATE TABLE statement that creates the named table.

 DESCRIBE pet; -- pet here is a table, not a database


-- delete rows from a table
	DELETE FROM table_name
	WHERE some_column=some_value; 
	-- delete the all rows without deleting the whole table
	DELETE FROM table_name;
	DELETE * FROM table_name;

	-- The DROP TABLE statement is used to delete a table.
	DROP TABLE table_name

-- Alter a table
	-- To add a column in a table, use the following syntax:
	ALTER TABLE table_name
	ADD column_name datatype;

	-- To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
	ALTER TABLE table_name
	DROP COLUMN column_name;

	--To change the data type of a column in a table
	ALTER TABLE table_name
	MODIFY COLUMN column_name datatype;



-- load text file 
 LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
 LOAD DATA LOCAL INFILE '/home/john/pet.txt' INTO TABLE pet fields terminated by ' ';
 input file format: (tab separated, \N is for NULL)
 Whistler Gwen bird \N 1997-12-09 \N



-- insert data entries:
	INSERT INTO table_name
		VALUES (value1,value2,value3,...);
	
	INSERT INTO table_name (column1,column2,column3,...)
		VALUES (value1,value2,value3,...);

 INSERT INTO pet
 VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

 INSERT INTO shop VALUES
 (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
 (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
 -- use the default values
	insert into t2 values (default,'a'),(4,default);




-- query - SELECT with where
 SELECT name, birth FROM pet LIMIT 1;
 SELECT * FROM pet WHERE birth >= '1998-1-1';
 SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
 -- <> is not equal to 

 
 SELECT DISTINCT owner FROM pet;

 -- group by and having
	select *, count(*) from (SELECT distinct * FROM t1) as dis group by year, month;
 	SELECT *, count(*) FROM t1 group by t1.year, t1.month, t1.day;
 SELECT *, count(*) FROM t1 group by t1.year, t1.month, t1.day having count(*)>1;

 -- in a set
	SELECT * FROM Customers
	WHERE City IN ('Paris','London');
	-- between 
	SELECT column_name(s)
	FROM table_name
	WHERE column_name BETWEEN value1 AND value2;



 -- You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name. 
 
 -- order by certain field:
 SELECT name, birth FROM pet ORDER BY birth [ASC|DESC]; -- desc means decrease
 SELECT name, species, birth FROM pet ORDER BY species ASC, birth DESC;




 -- Pattern Matching 
 “_” to match any single character and 
 “%” to match an arbitrary number of characters (including zero characters).
 [charlist] 	Sets and ranges of characters to match
	[^charlist] or [!charlist] 	Matches only a character NOT specified within the brackets 

 SELECT * FROM pet WHERE name LIKE 'b%';
 SELECT * FROM pet WHERE name LIKE '%fy';
 SELECT * FROM pet WHERE name LIKE '_____';
 SELECT * FROM pet WHERE name NOTLIKE '_____';
	SELECT * FROM Customers WHERE City LIKE '[a-c]%';

 -- with regular expression
 SELECT * FROM pet WHERE name REGEXP '^b';
 SELECT * FROM pet WHERE name REGEXP 'fy$';
 SELECT * FROM pet WHERE name REGEXP 'w'; -- a regular expression pattern matches if it occurs anywhere in the value
 SELECT * FROM pet WHERE name REGEXP '^.{5}$';


 -- group by, factorization
 SELECT article, MAX(price) AS price
 FROM shop
 GROUP BY article;



 -- count:
 SELECT COUNT(*) FROM pet;
 SELECT sex, COUNT(*) FROM pet GROUP BY sex;
 SELECT owner, COUNT(*) FROM pet GROUP BY owner;
 SELECT species, COUNT(*) FROM pet GROUP BY species;
 SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;


	-- SQL Alias Syntax for Columns
	SELECT column_name AS alias_name
	FROM table_name;
	-- SQL Alias Syntax for Tables
	SELECT column_name(s)
	FROM table_name AS alias_name; 

-- use multiple tables, join, inner/left/right join:
 -- inner join or just join
 select pet.name, event.* from pet -- pet with event.
 inner join event on pet.name=event.name;
 
 SELECT pet.name,(YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark
 FROM pet INNER JOIN event
 ON pet.name = event.name
 WHERE event.type = 'litter';

 SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species -- same table, match maker for pets
 FROM pet AS p1 INNER JOIN pet AS p2
 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';
 SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species 
 FROM ((pet AS p1) INNER JOIN (pet AS p2) 
 ON p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm');


 -- same table, highest price for one article
 -- inner join (uncorrelated subquery)
	 SELECT s1.article, dealer, s1.price 
	 FROM shop s1
	 JOIN (
	 SELECT article, MAX(price) AS price
	 FROM shop
	 GROUP BY article) AS s2
	 ON s1.article = s2.article AND s1.price = s2.price;

 -- left join
		SELECT s1.article, s1.dealer, s1.price
		FROM shop s1
		LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
		WHERE s2.article IS NULL;

	-- correlated subquery
		SELECT article, dealer, price
		FROM shop s1
		WHERE price=(SELECT MAX(s2.price)
 FROM shop s2
 WHERE s1.article = s2.article);

	-- union, combines the rows
	SELECT column_name(s) FROM table1
	UNION [ALL] -- all will keep duplicates
	SELECT column_name(s) FROM table2;

-- Copy only the columns we want into the NEW table:
	SELECT column_name(s)
	INTO newtable [IN externaldb]
	FROM table1;
	--The new table will be created with the column-names and types as defined in the SELECT statement. You can apply new names using the AS clause.

	--repo-qg-apt-replacingNSERT INTO SELECT statement copies data from one table and inserts it into an existing table.
	INSERT INTO table2
	(column_name(s))
	SELECT column_name(s)
	FROM table1;

-- change, modify, update
	UPDATE table_name
		SET column1=value1,column2=value2,...
		WHERE some_column=some_value;

 UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
 update shop set price=4.99 where (article=0003 and dealer='B');



-- useful functions:
	MAX(); Avg(); Sum(); etc.
 SELECT article, dealer, price
 FROM shop
 WHERE price=(SELECT MAX(price) FROM shop);


	LAST_INSERT_ID() --Value of the AUTOINCREMENT column for the last INSERT 

 SELECT name, birth, CURDATE(),
 TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
 FROM pet ORDER BY age;

 SELECT name, birth, MONTH(birth) FROM pet;
 SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

 SELECT name, birth FROM pet
 WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;


 -- user-defined variables
	 SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
		select @min_price, @max_price;
		SELECT * FROM shop WHERE price=@min_price OR price=@max_price;



-- 3.5 Using mysql in Batch Mode
 shell> mysql < batch-file
 C:\> mysql -e "source batch-file" -- windows
 shell> mysql -h host -u user -p < batch-file
 Enter password: ********
 mysql -t < batch-file -- If you want to get the interactive output format in batch mode. 
 mysql -vvv < batch-file -- To echo to the output the commands that are executed. 
 
 -- You can also use scripts from the mysql prompt by using the source command or \. command:
 mysql> source filename;
 mysql> \. filename


--Back up and restore: mysqldump is used in a terminal $
	$mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
	$ mysqldump -u root -p DB_name > tut_backup.sql
	$ mysqldump -u root -p DB_name table1 table2 > tut_backup.sql
	$ mysqldump -u root -p -B Tutorials Articles Comments > content_backup.sql -- or replace -A with --databases
	$ mysqldump -u root -p --all-databases > alldb_backup.sql 
	
	--no-data: Dumps only the database structure, not the contents. 

	$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]
	$ gunzip [backupfile.sql.gz] -- extract zip file

	-- restore:
	http://dev.mysql.com/doc/refman/5.6/en/reloading-sql-format-dumps.html
	$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
	$ mysql -u root -p Tutorials < tut_backup.sql
	gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]




-- Suppose that you want to call yours menagerie. The administrator needs to execute a command like this: 
 GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';