MySQL cheat sheet

Assumes you are running the MySQL client. See "getting started" below.
(If needed, see UW-IT's installation and administration instructions to bring up a new MySQL instance.)

working w/TABLES

create database & table from scratch
create database db ;
use db ;
create table t ( 
	id int auto_increment primary key,
	flag smallint unsigned default '0',
	fname varchar(30),
	lname varchar(30),
	updated timestamp,
	index (lname),
	index (flag)
) ;
desc t ;
Column types.
see databases
show databases ;
select database
use db ;
see tables in selected database
show tables ;
see tables in other database
show tables in db ;
rename table
rename table t1 to t2 ;
empty table & reset auto_increment
truncate t ;
delete table
drop table t ;
drop table if exists t ;
copy table structure to new table
create table t2 like t1 ;
create table t2 like db.t1 ;   table from other database
DOES recreate all indexes and attributes.
insert into table from other table
insert into t2 select * from t1 ;
insert into t2 (lname)
	select lname from t1 
	where lname like "S%" ;

Insert lastnames starting with "S" from other table.

Select can be a complex join.

create table from another table (beware)
create table t2 select * from t1 ;
NB! does NOT recreate indexes and attributes.
Fix with alter.
create table from some fields in another table
create table t2 select lname, fname from t1 ;
create table w/new and selected fields
create table t2 (
	id int auto_increment primary key,
	updated timestamp,
	index (fullname)
) select concat(fname, ' ' ,lname) as fullname from t1 ;
create temporary table
create temporary table t
	select c1, c2 
	from  t1 join t2 using(c3) ;
with indexes
create temporary table t
	( index (col) )
	select . . . 
	from . . . ;
examples
add column(s)
alter table t add id int ;   (appends column)
position new column with first or after
alter table t add id int first ;
alter table t add id int after lname ;
add multiple columns
alter table t add c1 int, c2 date ;
rename column
alter table t change c1 c2 char(10) ;
Must define column type, even if it's unchanged.
Must NOT include primary key if it already exists.
Keys and indexes survive rename.
modify column type
alter table t modify c varchar(15) ;
move column
alter table t modify c varchar(15) first ;
alter table t modify c varchar(15) after c2 ;
delete column
alter table t drop c ;
add an index
alter table t 
	add index (phone) ,
	add unique (uwnetid) ,
	add index name (lname,fname) ,
	drop index mobile  ;
(No parentheses around dropped index.)
add primary key
alter table t add primary key (c) ;
alter table t modify c int primary key ;
a compound primary key, based on multiple columns
alter table t add primary key (c1,c2) ;
"If you do not have a PRIMARY KEY and an application asks
for the PRIMARY KEY in your tables, MySQL returns the first
UNIQUE index that has no NULL columns as the PRIMARY KEY."
move primary key
alter table t 
	drop primary key,
	add primary key (c) ;
NB: If auto_increment is set on old key,
it must be removed or moved — like this.
add new primary key at top of table
(These 2 statements are equivalent.)
alter table t 
	add id int unsigned auto_increment primary key first ;
(or)
alter table t 
	add id int unsigned auto_increment first,
	add primary key (id) ;
see indexes
show indexes from t ;

getting started

launch MySQL client program
SSH to ovid01.u.washington.edu
(On Homer, drop to webdev environment with O, W.)
cd mysql
bin/mysql -u root -p
To open MySQL client on different host:
bin/mysql -u root -p -h ovid01.u.washington.edu -P portnum
create new empty database
create database db ;
privileges
grant select on db.* 
	to "joeuser"@"%" identified by "pw" ;
grant select, insert, update, delete, create, drop on db.* 
	to "VIP"@"%" identified by "pw" ; 
revoke delete, drop on db.* 
	from "VIP"@"%" ;
grant all privileges on *.* 
	to "root"@"%" identified by "pw" with grant option ; 
show grants ;   lists privileges for all users
show grants for user@host ;    for specific user
show grants for user ;   assumes user@'%'
revoke all privileges, grant option from "root"@"%" ;
limits
grant select on db.* 
	to "joeuser"@"%" identified by "pw"
		with max_user_connections 2 ;
grant usage on *.* to "VIP"@"%" identified by "pw"
	with max_queries_per_hour 20
		max_updates_per_hour 10
		max_connections_per_hour 5
		max_user_connections 2 ;
with can take 1 or more limits in any order.
usage retains current privileges.
Set limit to 0 to remove it.
MySQL and accounts
MySQL stores accounts & privileges in mysql database.
User@host creates a unique account. These are separate accounts:

root@localhost

root@%

Each account gets only 1 password. These 2 commands

grant select on db1.* to "joe"@"%" identified by "pw1" ;

grant select on db2.* to "joe"@"%" identified by "pw2" ;

would reset joe's password for ALL databases to "pw2".
Delete a single account

use mysql;
delete from user where user="username";

Delete all accounts

delete from user where host like "%";

Delete all accounts starting with "wil"

delete from user where user like "wil%";
(See WHERE above, esp. like and rlike.)

leave MySQL command-line
exit ;
execute commands from OS prompt -e
cd mysql/bin
mysql -u root -p -e "statements"

ex: statements:

"select * from db.t"

"use db ; select * from t"

"show databases"

(see "SQL statements in files" for operations you need to repeat)

check / repair databases
bin/mysqlcheck -u root -p
	 -c db t1 t2 t3	(check 1 or more tables)
	 -c db 		(check all tables in DB)
	 -c -B db1 db2 	(-B multiple databases)
	 -c -A 		(same as --all-databases)
	 -a db t		(analyze) 
	 -o db t		(optimize) 
	 -r db t 		(repair)
-c check, -a analyze, -o optimize, -r repair
(see: http://www.thegeekstuff.com/2011/12/mysqlcheck/)
(Good summary of reasons MySQL tables crash:
How to repair a crashed MySQL table)
fix forgotten password
UW-IT's step-by-step guide

working w/DATA

insert
insert into t ( c1,c2,c3,c4 ) values ( v1,v2,v3,v4 ) ;

Quote values as needed.

into is optional.

insert t (lname) values ('Abrams'), ('Johnson'), ('Smith') ;

Insert 3 last names (creates 3 new rows).

insert t set c1=v1, c2=v2, c3=v3 ;

insert / set is handy in a script when a form might be inserting or updating data. Ex:

	$action = ($editing) ? 'update' : 'insert'; 
	$sql = "$action t set c1=v1, c2=v2 ;"

NB: update usually needs a where condition (not illustrated above).

Find next auto-increment value: table status

Use last auto-increment value: last_insert_id()

select
select * from t ;
select * from t limit 10 ;
select * from t order by added desc limit 5;   most recent 5
select c1,c2 from t ;
select c1 , ifnull( c2, 'nuthin' ) from appts ;
select substr( c,1,10 ) as date , substr( c ,-8 ) as time from t ;

Gets date/time separately (if "c" is timestamp or datetime).
Negative position (-8 above) starts 8 chars from end of string.

Alternative to substr() above:

	date_format(c ,'%Y-%m-%d' )  
	date_format(c ,'%H:%i:%S' )  

See date functions.

where
select lname from t where lname = 'smith' ;
select lname from t where lname = 'smith' or lname = 'smythe' ;
select lname from t where lname like 'sm_th%' ;

Matches 'smith', 'smythe', 'smothers', 'smithsonian', etc.

More about like.   (NB: like and rlike are relatively slow.)

select lname from t where lname rlike 'gr[ae]' ;

Matches 'gray', 'grey', 'greyson', etc.

More about rlike.

select lname from t where left( lname,1 ) between 'F' and 'L' ;
select * from t where c between '2010-01-01' and '2010-12-31' ;
select count(*) from t where c & 4 ;   test bit 2, false if 0

other bitwise operators (bits counted from 0):
where (( c >> 2 ) & 1 ) = 1 ;   SHR 2, then test bit 0
where (( c >> 2 ) & 1 ) ;   (same as above, FALSE if 0)
where ( c & ( 1 << 6 )) ;   SHL to test bit 6 in c

having
use in place of where when grouping

select c, count(c) as num from t group by c having num>1;

update
update t set c1=v1 , c2=v2 where some_condition ;

Here, v represents another column, an expression, or a literal value.

NB! without where condition, ALL rows are updated.

joins or unions preceed set.
update t1 join t2 using(key) set t1.c = t2.c where condition ;

update t1 FROM t2

update t1 join t2 using(key) set t1.c=v, t2.c=v where condition ;

update both t1 AND t2

delete
delete from t where some_condition ;

Without where condition, ALL rows are DELETED.

delete from t where some_condition limit 1 ;

Limit delete to 1 row.

delete t1 from t1 left join t2 using( c ) where t2.c is null ;

Delete from T1 when corresponding key is NOT found in T2.

delete t1, t2 from t1 join t2 using( c ) where some_condition ;

Delete from 2 tables at once.

count
select count(*) from t ;
select count(*) from t where lname like "s%" ;

Count last names starting with "s".

select lname, count(*) from t group by lname ;

Show lastnames and their counts.

select lname, count(*) from t 
	where lname like "s%"
	group by lname ;

Show lastnames that start with "s" and their counts.

select lname, count(*) as num from t 
	where lname like "s%"
	group by lname 
	having num > 1 
	order by num desc ;

Show duplicate lastnames that start with "s" sorted most to least.

(NB: limit has no effect on count.)
functions
string date abbreviations numeric group control
joins
inner join :  return data when t1 and t2 keys match

select c1,c2 from t1 inner join t2 on t1.key=t2.key ;

left join :  return all t1, but t2 only if keys match

select c1,c2 from t1 left join t2 on t1.key=t2.key ;

right join :  return all t2, but t1 only if keys match

select c1,c2 from t1 right join t2 on t1.key=t2.key ;

self join :  teases out recursive relations in a table

select a.c1, b.c1 from t1 as a join t1 as b on a.key=b.key

full join :  return all t1 & t2.

Not supported by MySQL. Approximate with union.

Or, create temporary table and fill with 2 distinct queries.

cross join :  returns the product of both tables  

select c1,c2 from t1 join t2 ;

compound join — join acts as "table" for subsequent join(s)

select c1, c2, c3 from ( t1 inner join t2 on t1.key=t2.key )
  left join t3 on t1.c=t3.c
  left join t4 on t1.key=t4.key ;

union
(select ... ) union distinct (select ... ) ;

Retrieve columns from union of tables.

insert t (c) select c from t1 union select c from t2 order by c ;

Insert from union of tables.

views
create view v as (regular-sql-statment-here) ;
Once created, views act like tables, and appear along with tables.
select c1, c2 from v ;
desc v ;
show create view v ;
drop v ;
Views vs temporary tables

Views can't contain subqueries. Temporary tables can.

Views persist from session to session.

user variable — @v (store / reuse results)
select @mydate := "2016-11-05" ;
select @cid := crsID from crsfac where facID=someval ;
select crstitle from courses where crsID=@cid ;
update
update t set c1=" ", c2=" " where some_condition ;
update t set c1=" ", c2=" " where some_condition limit 1;
update t1 inner join t2 on t1.c=t2.c 
	set t1.c1=t2.c1, t2.c2=t1.c2 
	where some_condition ;
Columns in both tables can be updated. Other joins are OK.
DON'T forget where condition. Otherwise, ENTIRE TABLE is altered.
subqueries
Derive where condition from select statement

select c from t where c = (select c1 from t1);

Derive column from select statement
select c1 , 
	( select count(*) from t1 where id=t1.id ) as c2 
	from t ;  example
Derive table from select statement

(See insert example at end of string functions above).

Use view instead, when possible. Derived tables are 10 times or more slower.

web resource
backup data
bin/mysqldump -u root -p db > db.sql
mysqldump overview
add options in front of database name to...

preserve views --add-drop-table db > db.sql

preserve procedures --routines db > db.sql

backup all databases --all-databases > db.sql

to create archive ... db | gzip > db.sql.gz

to archive all databases
-u root -p --all-databases | gzip > alldata.sql.gz
to export all views for a given database (linux)

mysql -u root INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'db'" | xargs mysqldump -u root db > db_views.sql

dump tables matching a pattern
restore data — mysql
bin/mysqldump -u root -p db | gzip > db.sql.gz
bin/mysqldump -u root -p --all-databases | gzip > alldata.sql.gz
bin/mysql -u root -p db < db.sql
gunzip < db.sql.gz | bin/mysql -u root -p db
dump definitions / data separately
mysqldump --no-data db > defs.sql
mysqldump --no-create-info db > data.sql
export/import data
Default source/destination folder is mysql/data/dbname
Specify other folders using / — c:/transfer/myfile.txt
(even on Windows).
tab-delimited
	select * from  t into outfile 'f' ;	(export)
	load data infile 'f' into table t ;	(import)
comma-separated values
	select * from  t into outfile 'f' 	example
		fields terminated by ',' 
		enclosed by '"' ;
	load data infile 'f' into table t 	(import)
		fields terminated by ',' 
		enclosed by '"' ;

NB: load data and select into options must match.

other useful options
SQL statements in files
From system prompt:

bin/mysql -u root -p db < file.sql

If "use db" is first line of file, db can be skipped in command.

In the MySQL client:

    source file.sql

optimizing

references
net.tutsplus 20 best practices

cache: avoid non-determinable functions in SQL

use limit 1 when simply checking existence

avoid select * when you need fewer columns

enum over varchar

avoid null fields when possible

use prepared statements

avoid SQL queries in loops — (last tip on Let's make the web faster)

in loop: collect data in an array

after loop: feed INSERT with implode()

example

Yang Yang Kavoir MySQL Performance Tips

Examples:

count of courses taught by faculty
mysql syntax
select last, first, count(crsID) 
	from people join crsfac using(employID)
	group by employID 
	order by last, first ;
ansi syntax
select last, first, count(crsID) 
	from people as p inner join crsfac as cf 
		on p.employID=cf.employID
	group by cf.employID
	order by last, first ;
theta syntax
select last, first, count(crsID)
	from people as p, crsfac as cf
	where p.employID=cf.employID
	group by cf.employID 
	order by last, first ;
10 fac teaching most courses
select last, first, count(crsID) cnt 
	from people join crsfac using(employID)
	group by employID
	order by cnt desc limit 10;
(Limit 10 is deceptive — 11th, 12th, and 13th faculty might teach same amount as 10th.)
teacher(s) teaching max courses
select last, count(num) as cnt 
from
  (people join crsfac using (employID) ) 
  join course using(crsID)
group by employID
having cnt =
  (select max(tally.cnt)
  from 
    /* count courses grouped by teacher */
    (select count(num) as cnt
      from course join crsfac using(crsID)  
      where employID is not null
      group by employID
    ) as tally
  )
;
teachers whose course totals are within top 10
select last, first, count(num) as cnt 
from
  (people join crsfac using (employID) ) 
  join course using(crsID)
group by employID
having cnt >=
  (select max(tally.cnt)
  from 
    /* count courses grouped by teacher */
    (select count(num) as cnt
      from course join crsfac using(crsID)  
      where employID is not null
      group by employID
    ) as tally
  ) - 10
order by last, first, cnt  ;
Everything in parentheses after having resolves to max(tally.cnt), which can then be subtracted.
replace old auto_increment key with new
1. reset auto_increment to starting value
alter table t auto_increment=1 ; 
(newkey will begin with 1 when created)
2. remove auto_increment,
drop primary key,
add new auto_increment primary key
alter table t modify oldkey int, 
  drop primary key, 
  add newkey int auto_increment primary key 
    after oldkey ;
3. repeat for all tables that depended on oldkey
add newkey to other table
  alter table t2 add newkey int after oldkey ;
copy new keys into t2
  update t2 inner join t on t2.oldkey=t.oldkey 
    set t2.newkey = t.newkey ;
drop old key from t2
  alter table t2 drop oldkey ;
4. remove oldkey from original table
alter table t drop oldkey ;
retrieve faculty data from compound join
select p.employID, p.Last, p.First, p.Middle, (...) ,
	pr.pic, pr.pictitle, pr.vita, 
	pr.heading1, pr.text1, pr.heading2, pr.text2, (...) ,
	r.roomID, r.OfficePhone phone, r.VoiceMail vmail, r.private, 
	pos.title jobtitle, r1.roomID as posroomID, r1.officephone as posphone,
	v_jobcodes.description emp_type
	from ( people p inner join profile pr on p.employID = pr.employID )
		left join rooms r on p.Room = r.Room
		left join t_position pos on p.employID = pos.employID 
		left join rooms r1 on pos.roomID = r1.roomID
		left join v_jobcodes on p.typeID = v_jobcodes.ID
	where p.employID=$myID and p.status>0 ;
retrieve copies made by user from specific academic year
select usercode, ricohname, sum(copier) as copies, acadyr 
	from ( select * from v_ricohs_now where acadyr="2010" ) 
	as myyear  /* every derived table requires alias */
	group by usercode 
	having usercode="88507";

OBSOLETE: getpaper.php now calculates sums before feeding PAPER table,
so this can be retrieved with the following simple select:

select uwnetid, copies, prints, copies+prints as pages
	from paper
	where acadyr='2010' and uwnetid='weller' ;
retrieve history of page consumption for specific user
select uwnetid, sum(copies) as allcopies, sum(prints) as allprints, 
	sum(copies) + sum(prints) as allpages
	from paper
	group by uwnetid 
	having uwnetid='weller' ;
upgrade passwords to new 41 character hash
On MySQL command-line
set session old_passwords=0;
use mysql;
set password for 'user'@'host' = password('password') ;
	(repeat for all accounts)
set global old_passwords=0;
	(this affects all new connections)
edit my.ini (Win) or my.cnf (*nix)
remove old-passwords=1
extract data for an Excel chart
Create a temporary table showing appointments during each hour.
Extract hour from the 'created' field and group by it to confine the counts.
Note: concat() appends ":00" to the hour returned by substr(). This makes Excel happy.
create temporary table  apptsbyhour 
select 
	concat( substr( created , 12 , 2 ) , ':00' ) as  hour ,
	count( apptID ) as  num  
from t_appts 
group by hour ;
export a tab-delimited file
select * from apptsbyhour into outfile 'apptsbyhour.txt' ;
export a CSV file
select * from apptsbyhour into outfile 'apptsbyhour.csv'
	fields terminated by ','
	enclosed by '"' ;
export with column names
select 'hour' , 'appts' 
union all
select * from apptsbyhour into outfile 'apptsbyhour.txt' ;
Note:
extract traffic numbers for Excel chart
Convert [qtr] for sorting and display using select case.
/* include column names w/union all */
select 
	"sortcol", "year_qtr", "sessions", "booked", "visits", "dropins", "noshows", "noshowsOK" 
union all 
select
	/* sortcol */
	concat( 
		acadyear, 
		(select case qtr  /* convert qtr to sort value */
			when 3 then "1"
			when 4 then "2"
			when 1 then "3"
			else "4" end
		)
	),
	/* year_qtr */
	concat(  
		acadyear, 
		" ", 
		(select case qtr  /* convert qtr to string */
			when 4 then "aut" 
			when 1 then "win"
			when 2 then "spr"
			else "sum" end
		)
	),
	sum(sessions), 
	sum(booked),
	(sum(booked) - sum(noshows)),  /* visits */
	sum(dropins),
	sum(noshows),
	sum(noshowsOK)
from t_traffic
group by acadyear, qtr
order by sortcol
;
minutes between appointments
Use self join to calculate values between rows in same table.
Assume id is auto_increment on table `t` ordered by created .
Hence, ids reflect the sequence of appointments.
unix_timestamp() has generated u_created & u_canceled .
So, calculate (u_created - u_canceled)/60 (where u_canceled is from previous row).
select
	( a.u_create  - coalesce(
	   ( select b.u_cancel from t as b where (b.id = a.id-1 ) ), 
	   a.u_create 
	) /60 )  /* convert seconds to minutes */
	as minutes_free
from t as a order by id desc ;
Notes:
task
example
example