Useful MySQL Statements

Published on January 19th 2010.

Updated on July 30th 2010.

I'm constanly moving between MySQL and Oracle - the former being the database I use at home, and the latter, the database I use at work. As a result of switching between the two, I constantly get frustrated remembering different SQL commands, because they differ between the two languages. To combat this (and save me from lots of Googling) I wrote down a list of useful MySQL statements, and thought I would share it with you!

Here is it:

Add Primary Key

Adding primary keys in MySQL is quite simple. We use primary key(id), which is added to the end of our create table statement.

create table <tablename> (table structure...primary key (<idfield>)).

     
        CREATE TABLE users (
        ID int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
        username varchar(100),
        password varchar(100),
        PRIMARY KEY (id)
        );

Show databases

To see a list of your databases simply type show databases. Note — it doesn't matter where you are in MySQL, or if you are already in a database, this command will print out a list of all of your databases.

Show tables

To see a list of tables in a given database, go to that database by typing use databasename, then type show tables. This will display a list of all of tables within the database you are currently in.

Change datatype or size of column

Option #1

Here, we're changing the size of our field username to 100.

alter table <tablename> modify <fieldname> <data_type>.

        
		ALTER TABLE users modify username varchar(100);

It's the same principal when changing the datatype:

		ALTER TABLE users modify username timestamp;

Here, we're changing the datatype of username from varchar to timestamp, just to screw up someone's system!

Option #2

This is a different approach using change column instead of modify.

alter table <tablename> change column <fieldname> <fieldname> <new_data_type>.

        ALTER TABLE users CHANGE COLUMN username username varchar(100);

Rename column

This is the same syntax as above, except we're changing the name of the column instead of the column's datatype.

alter table <tablename> change column <old_fieldname> <new_fieldname> <definition>.

        
       	ALTER TABLE users change column username username varchar(100);

Of course, you can change the column name and datatype at once...if you want to be radical!

        
        ALTER TABLE users CHANGE COLUMN username banned_username varchar(100);

Drop column

The drop command is used to delete a column.

alter table <tablename> drop column <fieldname>.

        ALTER TABLE users DROP COLUMN timestamp;

Delete row

This is fairly self-explanatory.

delete from <tablename> where <condition>.

        DELETE FROM users WHERE username = 'John Smith';

Or another example...deleting by the ID of a record.

        
		DELETE FROM USERS users WHERE ID = 10;

Limiting and offsetting records

The limit command is useful for pagination, whereby you pass the current page and the number of rows per page into your select statement - obviously, the current page would be a requested variable of some sorts.

select * from <tablename> limit <offset>,<row_count>.

        SELECT * FROM users LIMIT 20,50;

Here, we're selecting 50 users from our users table, starting from the 20th row.

Select top (n) records

People always say 'where is MySQL's top command?' for selecting the top nth row from a database...well, it's the same keyword we used above - it's the limit keyword.

select * from <tablename> limit <offset>,<row_count>.

        SELECT * FROM users LIMIT 0,10;

This will select the top 10 rows from our users table. Simple as that.

Joins

This frustrated me when first learning Oracle, because Oracle has a different approach to joins from MySQL. In Oracle, joins are typically written as where clauses appended to an SQL statement. However, MySQL uses something called ANSI Joins (Oracle does support these, but most of the Oracle books I've read tell you to use a where clause). There are four types of ANSI Joins:

The Inner Join

The inner join is used to return records that only exist in both tables specified in your join. Here is a simple example that joins our user table with our roles table:

        SELECT u.username, r.role FROM users u INNER JOIN roles r ON u.role_id = r.id;

This will return a list of users and their associated roles.

The Outer Join

The outer join is used to return all records from both tables specified in your join. The only thing I dislike about MySQL is that it doesn't utilise a full outer join. To accomplish this we have to union two tables together, one of which uses a right join and the other, a left join. Here's how it's done:

        SELECT * FROM blog_tags
        LEFT JOIN tags_lookup
        ON blog_tags.id = tags_lookup.tag_id
        UNION
        SELECT * from blog_categories
        RIGHT JOIN category_lookup
        ON blog_categories.id = category_lookup.category_id;

In this example, we are returning a list of tags and categories for blogs. Using an outer join means that we can see which tags and categories are associated with which blogs, as well as any tags and categories that aren't associated with a blog.

The Left Join

The left join will return matching fields that exist in all of the tables specified in your join, as well as additional records for each unmatched record in the table that comes before LEFT JOIN in your query.

         
        SELECT u.username, c.item FROM users u LEFT JOIN cart c ON u.item_id = c.id;

This will return a list of users and their shopping cart items, as well as any users who have not purchased anything.

The Right Join

The right join will return matching fields that exist in all of the tables specified in your join, as well as additional records for each unmatched record in the table that comes after RIGHT JOIN in your query.

         
        SELECT u.username, s.timestamp FROM session s RIGHT JOIN users u ON s.u_id = u.id;

This will return a list of users and the times they have logged in, as well as any users who have not logged in.

Save output to file

This is very useful when you want to save results from a query to a file. The tee command is used, followed by the name of the file.

tee <filename>.

        
        TEE userlist.txt;

MySQL will save files to your C drive, by default, unless otherwise specified. If you wish to change this, simply type the full location to the file and MySQL will create it there.

        
        TEE C:/users/Acheron/Documents/userlist.txt;

Note — if you have Windows Access Control set to anything above medium, you will not be able to save directly to your C drive. You can still save to your User directory though.

Show errors

Sometimes, MySQL will error during certain operations. In these instances, it's useful to know what's causing the errors. The MySQL command show errors will display a list of the errors encountered during a given operation (if any are present).

        
       	SHOW ERRORS

That's it! Simple, eh?

Formatting dates

MySQL date and datetime values aren't very pretty to look at. It makes more sense to display your dates in a fashion that is easy to read, such as 04/03/2010 or 4th of March 2010. To do this we use the MySQL date_format function. We pass it our date field, as the first parameter, and the format we wish to display our date in, as the second parameter.

date_format(<datefield>, <format>)

Some examples

        
		DATE_FORMAT(created,'%d/%m/%Y')

Outputs:

04/03/2010

        DATE_FORMAT(created,'%M %D %Y %r')

Outputs:

March 4th 2010, 21:30:36 PM

        DATE_FORMAT(created,'%b %D %Y %r')

Outputs:

Mar 4th 2010 21:32:50 PM

        DATE_FORMAT(created,'%W %M %D %Y %r')

Outputs:

Wednesday March 4th 2010 21:32:50 PM

For more date formatting options, visit the MySQL date format page.

Got any questions or comments? Feel free to mail me @ helen@alternategateways.com.

Share this:

Back to resources.