My MySQL Notes @mysqlnote - Tumblr Blog

Escape Special Character from MySQL

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

#special characters

Delete All Data from Table

TRUNCATE TABLE tablename; DELETE FROM tablename;

#delete #data #table #record

Create User in MySQL's shell

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

#admin #user

Set up Root Password (Linux)

sudo mysqladmin -u root password 'password here'

#setup #account #user #password #admin

SELECT within SELECT Tutorial @ http://sqlzoo.net

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’)

#nested select

Query Clauses

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

loading sql schema

mysql -u USERNAME -p database_name < schema.txt

#load schema

insert

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

#insert #table

Alter/modify table

ALTER TABLE table_name MODIFY table_column data_type;

#alter #modify #table

describe a table

DESC person;

list all tables