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

• Give the user power over the database: grant all on mydatabase.* to myuser@localhost;
• Give the user power over all databases: grant all on *.* to myuser@localhost;
• Give the user effective root-alike-ness (as I understand it): grant all on *.* to myuser@localhost with grant option;
• Do the above and set a password at the same time: grant all on *.* to myuser@localhost identified by 'prettysecret' with grant option;
• Set the user's password on a separate line: set password for myuser@localhost = password('prettysecret');

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 ... ;