My MySQL Notes

http://stackoverflow.com/questions/881194/how-to-escape-special-character-in-mysql

TRUNCATE TABLE tablename; DELETE FROM tablename;

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

sudo mysqladmin -u root password 'password here'

1. SELECT name FROM bbc WHERE population > (SELECT population FROM bbc WHERE name='Russia') 2. SELECT name, region FROM bbc WHERE region IN (SELECT region FROM bbc WHERE name='India' OR name='Iran') 2. (A better answer) SELECT name, region FROM bbc WHERE region IN (SELECT region FROM bbc WHERE name IN ('India','Iran')) 3. SELECT name FROM bbc WHERE (gdp/population) > (SELECT (gdp/population) FROM bbc WHERE name='United Kingdom' ) AND region='Europe' 4. SELECT name FROM bbc WHERE Population > (SELECT population FROM bbc WHERE name='Canada') AND Population < (SELECT population FROM bbc WHERE name='Algeria') 5. SELECT name FROM bbc WHERE name NOT IN (SELECT name FROM bbc WHERE region='Europe') AND gdp > (SELECT max(gdp) FROM bbc WHERE region='Europe') 5. (A better answer) SELECT name FROM bbc WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region=’Europe’)

SELECT - Determines which columns to include in the query's result set FROM - Identifies the tables from which to draw data and how the tables should be joined WHERE - Filters out unwanted data GROUP BY - Used to group rows together by common column values HAVING - Filters out unwanted groups ORDER BY - Sorts the rows of the final result set by one or more columns

mysql -u USERNAME -p database_name < schema.txt

INSERT INTO person (person_id, fname, lname, gender, birth_date) VALUES (null, 'William','Turner', 'M', '1979-05-27');

ALTER TABLE table_name MODIFY table_column data_type;

DESC person;

show tables;

use database_name;

show databases;

create database database_name;