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.)
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) ) ;
Max values (unsigned):
255
65,535
16,777,215
4,294,967,295
18,446,744,073,709,551,615 (18 quintillion)
int or float gets next number in sequence if
column is auto_increment and NULL is inserted.
can store 1 of up to 65535 values (bits)
can store multple values, up to 64 of them
can insert and select as values or as bits examples
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.
alter table t add primary key(id), add index (created), add unique (netID) ;
alter table t modify id int auto_increment primary key ;(or)
alter table t modify id int auto_increment, add primary key(id) ;
Create table w/select ... from sets default value
to "0" if original column was auto_increment.
create table t2 ( id int auto_increment primary key, updated timestamp, index (fullname) ) select concat(fname, ' ' ,lname) as fullname from t1 ;
create temporary table t select c1, c2 from t1 join t2 using(c3) ;
create temporary table t ( index (col) ) select . . . from . . . ;
create temporary table lastweek select uwnetid,copies from papertrends where week=13 ;
create temporary table thisweek select uwnetid,copies from papertrends where week=14 ;
select a.uwnetid, a.copies - b.copies as weekcopies from thisweek as a join lastweek as b using(uwnetid) ;
create temporary table t ( primary key (uwnetid), index (col), index name (lname, fname), /* compound index */ unique (col) /* values in col must be distinct */ ) select c1, c2 from t1 join t2 using(c3) ;
alter table t add index (phone) , add unique (uwnetid) , add index name (lname,fname) , drop index mobile ;(No parentheses around dropped index.)
alter table t drop primary key, add primary key (c) ;
Remove from old key with modify.
Add it to new key, if needed.
alter table t modify old int unsigned default '0', drop primary key, add primary key (new) ;
alter table t modify old int, drop primary key, modify new int auto_increment primary key ;
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) ;
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 current user show grants for user@host ; for specific user show grants for user ; assumes user@'%'There is no command to list privileges for all users.
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 ;
root@localhost
root@%
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".use mysql;
delete from user where user="username";
delete from user where host like "%";
delete from user where user like "wil%";
(See WHERE above, esp. like and rlike.)
ex: statements:
"select * from db.t"
"use db ; select * from t"
"show databases"
(see "SQL statements in files" for operations you need to repeat)
-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)
Quote values as needed.
into is optional.
Insert 3 last names (creates 3 new rows).
(Update multiple rows with 1 query when possible.)
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, limiting this to 1 row per query.
This can update many rows w/one query:
insert t ( c1,c2,c3 ) values ( v1,v2,v3 ),( v1, v2, v3 ) on duplicate key update c2=values( c2 ), c3=values( c3 ) ;
result includes auto_increment
PHP example:
if ( !($result = $dbc->query( "show table status like 't'")) )
throw new Exception( $dbc->error );
$r = $result->fetch_assoc();
$nextID = $r['Auto_increment'];
Creates new auto_increment ID.
insert t2 set t1_id = last_insert_id() ;Uses new ID for foreign key `t1_id`.
NB: Could be stored in user variable for reuse.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.
matches click, smith, stick, swipe, etc.
matches Johnson, Johanson, Jorgenson, etc.
doesn't match Johnsen, Johanssen, etc.
matches Johnson, etc. but not Jorgenson
(See rlike for more sophisticated matching.)
Matches 'smith', 'smythe', 'smothers', 'smithsonian', etc.
More about like. (NB: like and rlike are relatively slow.)
Here's a regex cheat sheet.
Matches 'gray', 'grey', 'greyson', etc.
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
select c, count(c) as num from t group by c having num>1;
Here, v represents another column, an expression, or a literal value.
NB! without where condition, ALL rows are updated.
update t1 FROM t2
update both t1 AND t2
Without where condition, ALL rows are DELETED.
Limit delete to 1 row.
Delete from T1 when corresponding key is NOT found in T2.
Delete from 2 tables at once.
Count last names starting with "s".
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.
for multi-byte character sets, use char_length()
NB: MySQL counts the first character as 1 (not 0)
aliases: substr() , mid()
substring_index( 'www.washington.edu' , '.' , 2 ) www.washington
substring_index( 'www.washington.edu' , '.' , -2 ) washington.edu
NB: char(s) not included in result.
locate( 'b' , 'abc' ) 2
Returns null if ANY argument is null.
NB: insert() replaces characters. For true insert, select from derived table that breaks c into a and b, then concat results — concat( a , s , b ). example
date_format( now() , '%W %M %D, %Y' ) ; Saturday March 5th, 2011
curdate() + 0 current date as number YYYYMMDD
curtime() + 0 current time as number HHMMSS.000000
now() + 0 as number YYYYMMDDHHMMSS.000000
case c when v then r when v then r else r end example
case when e then r when e then r else r end example
coalesce( c1 , c2 , c3 ) example
Ignores rows in left-hand table (t1) and
right-hand table (t2) if keys don't match.
Good for selecting subgroups.
Ex: get names only of employees who teach.
select name from people
inner join crsfac on people.id=crsfac.id ;
Inner is optional. It's assumed if not specified in all version above.
select c1,c2 from t1 inner join t2 on t1.key=t2.key ;
Multiple ON criteria:
select c from t1 join t2 on t1.c1=t2.c1 and ( t1.c2=t2.c2 ) ;
(Useful when several columns create the key for a table.)
select c from t1 join t2 on t1.c1=t2.c1 or ( t1.c2=t2.c2 ) ;
Returns all left-hand table (t1) rows.
Returns right-hand (t2) if keys match.
Otherwise, returns NULL.
Good if t2 holds optional data, or to see what's missing.
Ex: get employees who haven't submitted office hours
select name from t1
left join t2 on t1.id=t2.id
where hours is null ;
select c1,c2 from t1 left join t2 on t1.key=t2.key ;
Returns all right-hand table (t2) rows.
Returns left-hand (t1) if keys match.
Otherwise, returns NULL.
Similar to left join, and can be written as such by swapping t1 and t2.
select c1,c2 from t1 right join t2 on t1.key=t2.key ;
Returns data when columns match other columns in same table.
Aliases allow SQL to treat 1 table as 2.
Extract hierarchical relationships within a table.
Ex: display supervisors and their employees
select t1.lname as supervisor, t2.lname as employee
from people as t1
join people as t2
on t2.empID = t1.superID ;
Assumes superID refers to empID in another row.
(See below 'minutes between appointments' for example
of calculating values between rows.)
select a.c1, b.c1 from t1 as a join t1 as b on a.key=b.key
Use union between left join and right join selects.
(select ... left join) union distinct (select right join) ;
If staff and faculty were in separate tables, could get combined names this way:
(select staff.lname from staff left join fac using(id))
union distinct
(select fac.lname from staff right join fac using(id))
order by lname ;
Or, a more fancy form of output:
(select staff.lname,
if(staff.lname is null,'','x') as staff,
if( fac.lname is null,'','x') as fac
from staff left join fac using (id))
union distinct
(select fac.lname,
if(staff.lname is null,'','x') as staff,
if(fac.lname is null,'','x') as fac
from staff right join fac using (id))
order by lname ;
distinct applies to all columns returned in row. Examples above return lastnames. Potential problem is same name for 2 different people. In which case, include a unique column (like the primary key) in the selection to keep people from being conflated.
This requires 3 steps, but leaves a temporary table that can be reused during the session.
On a web page, temporary table disappears at end of script, so maybe more useful from the command line.
Views might provide another approach.
Not supported by MySQL. Approximate with union.
Or, create temporary table and fill with 2 distinct queries.
Each row of t1 is joined to all rows of t2.
(You almost never want this. If both tables have 10 rows,
a cross join returns 100 rows.)
Might be useful in report or poll to create exhaustive
list of options.
Ex: list family members and possible vacations:
select name,dest from family join vacations ;
(GroupBy "name" in report could tidy it up for voting.)
select c1,c2 from t1 join t2 ;
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 ;
Combine results from 2 or more select statements.
insert emps (id, qtr, misc) select id, uwid as qtr, major as misc from a union distinct /* distinct is optional */ select id, qtr, uwid as misc from c order by id ;
Get similar data from different tables.
Ex: get last names ending with "s" from staff, fac, & student tables:
(select lname from staff where lname like "%s")
union distinct
(select lname from fac where lname like "%s")
union distinct
(select lname from student where lname like "%s")
order by lname ;
insert employees (id) select id from faculty union select id from staff order by id ;
Columns don't need to be of the same type,
but the same number must be in each select.
Add blanks to even up column count if needed, e.g.:
select c1, ' ', ' ', c4 from t ;
Order By must be on column(s) common to all selects.
MySQL only sorts the final results of a union.
The order of tables, views, or queries combined by union is lost.
Union & union distinct are identical.
Use union all to include duplicates.
Parentheses around select statements are optional.
Retrieve columns from union of tables.
NB! See notes about sorting.
Insert from union of tables.
Views can't contain subqueries. Temporary tables can.
Views persist from session to session.
update t1 inner join t2 on t1.c=t2.c set t1.c1=t2.c1, t2.c2=t1.c2 where some_condition ;
select c from t where c = (select c1 from t1);
select c1 , ( select count(*) from t1 where id=t1.id ) as c2 from t ; example
select last , first , ( select count(crsID) from crsfac where facID=p.facID) as courses from people as p having courses > 0 ;
select last , first , count(crsID) as courses from people join crsfac using(employID) group by employID ;
Inner join is a little faster.
It excludes employees who haven't taught — no need for "courses > 0".
See "Examples:" for ANSI and Theta syntax of this join.
(See insert example at end of string functions above).
Use view instead, when possible. Derived tables are 10 times or more slower.
create function get_label( g tinyint, i tinyint ) returns text return ( select description from codes where groupID=g and id=i ) ;
Alternative version of the same function
delimiter // create function get_label( g tinyint, i tinyint ) returns varchar(25) begin declare label varchar(25) ; select description into label from codes where groupID=g and id=i ; return label ; end // delimiter ;
Redefine the delimiter so ; can be used in the function body.
Name the function and declare the arguments and their types.
Wrap the function in begin and end statements.
End the definition block with the new delimiter.
Restore the ; delimiter.
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
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
if [ -z "$3" ]
then
echo "usage: ./putpat.sh db pattern outputfile"
exit 1
fi
# next is a single line
bin/mysql -u root -p
-s --skip-column-names
-D $1 -e "show tables like '$2' " |
xargs bin/mysqldump -u --add-drop-table root -p $1 > $3
windows: putpat.bat
if exist %3.sql del %3.sql
REM next is a single line
FOR /F "tokens=*" %%T in
('bin\mysql -u root -pmysql4engl -s --skip-column-names
-D %1 -e "show tables like '%2' "')
DO bin\mysqldump -u root -pmysql4engl %1 %%T >> %3.sql
usage: putpat db pattern outputfile
select * from t into outfile 'f' ; (export) load data infile 'f' into table t ; (import)
select year, qtr, num, section, concat( last,',',first ) from ( course join crsfac using(crsID)) left join people using(employID) order by year, qtr, num, section into outfile 'c:/course.csv' fields terminated by ',' enclosed by '"' ;CSV extension allows Excel to open directly.
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.
lines terminated by '\r\n' create pc-style CR/LF line breaks
select c1 , ifnull( c2 , 'n/a' ) . . . convert NULLs, if needed (NULLs are exported as \N by default.)
ignore 2 lines skip unwanted header lines in source
lines starting by 'prefix' skip unwanted text at start of each line NB! lines lacking prefix are skipped.
... into table t (c1,@x,@x,c5,@x) ; skip unwanted in-coming columns NB: must account for every column in source, so user var @x becomes place-holder.Alternatively, import all then drop columns with alter.
bin/mysql -u root -p db < file.sql
If "use db" is first line of file, db can be skipped in command.
source file.sql
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
1st implode converts $s to
v1 "," v2 "," v3
2nd implode converts $values to
("v1","v2","v3") , ("v1","v2","v3") , ("v1","v2","v3")
$values=array(); // new array
foreach ($a as $r) {
$s = array(); // new or reset array
foreach ($r as $c) {
$s[] = prepMYSQL( $c ); // sanitize
}
$values[] = '("' . implode( '","' , $s ) . '")';
}
$sql='insert into t (c1,c2) values ' . implode(',' , $values);
mysql_query( $sql );
in loop: collect data in an array
after loop: feed INSERT with implode()
select last, first, count(crsID) from people join crsfac using(employID) group by employID order by last, first ;
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 ;
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 ;
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.)
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
)
;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.
alter table t auto_increment=1 ; (newkey will begin with 1 when created)
alter table t modify oldkey int,
drop primary key,
add newkey int auto_increment primary key
after 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 ;
alter table t drop oldkey ;
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 ;
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' ;
select uwnetid, sum(copies) as allcopies, sum(prints) as allprints, sum(copies) + sum(prints) as allpages from paper group by uwnetid having uwnetid='weller' ;
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)
create temporary table apptsbyhour select concat( substr( created , 12 , 2 ) , ':00' ) as hour , count( apptID ) as num from t_appts group by hour ;
select * from apptsbyhour into outfile 'apptsbyhour.txt' ;
select * from apptsbyhour into outfile 'apptsbyhour.csv' fields terminated by ',' enclosed by '"' ;
select 'hour' , 'appts' union all select * from apptsbyhour into outfile 'apptsbyhour.txt' ;
/* 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 ;
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 ;