Mysql cheat sheet

Basic mysql operations I'm always forgetting (see also Neal Parikh's Mysql Cheat Sheet).

Create a table

create table users (userid int(10) unsigned, primary key (userid), name text);

Create a user and give it all privileges on a database

Find entries in one table which are not in another table

select name as myname from namestable where (select count(*) from persondetails where name = myname) = 0;

Add a column to a table

alter table mytable add column mycolumn text

or: alter table mytable add column mycolumn int unsigned not null auto_increment ...etc

Get a list of all groups, along with an indicator of whether a particular member is part of each

select groupname, groupnum, (select count(*) from group_members where groupid = groupnum and person = '456') as nummembers from groups

List all rows with a duplicate field

Say you have many entries, and some of them may be duplicates, based on their titles:

select * from (select title, count(title) as howmany from mytable group by title order by title) as innerquery where howmany > 1;

Change the primary key to a different column

alter table mytable drop primary key;
alter table mytable change column newid newid int(10) primary key; (or your own definition).

Put the result of a query into another table

insert into newtable select mycol, myothercol, mythirdcol from oldtable where ... ;