MySQL Commands List

Here you will find a collection of basic MySQL statements that should prove useful for basic CRUDS operations (create, replace, update, delete, select).

There may be some issues with the kind of quotes you use around your data. If you are having difficulties, try using singe quotes (  '  ) or tricky quotes (  `  -- on keyboard key next to number 1) around your data. One or the other is bound to work.

The following statements are covered in this page:

CREATE DATABASE
CREATE TABLE
INSERT
REPLACE
UPDATE
SELECT
DELETE

VALUES
SET
WHERE
IN
AND
OR
RLIKE
DISTINCT
MAX

CREATE DATABASE

CREATE DATABASE database_name ;

Will create a MySQL database.

You may have to ensure that the user you used to log in has enough privileges to create databases.

Some web hosting companies do not allow their customers to create databases -- as they have already created a couple databases for you. In this case you should already have enough privy's to create tables on the already created databases. Check with your hosting provider to see what the proper database names are and what the usernames and passwords are for the database(s).

Example:

CREATE DATABASE myMediaLibrary;

CREATE TABLE

Create a table on the database you are currently logged into.

Example:

CREATE TABLE IF NOT EXISTS `music` (
`id` int(8) unsigned NOT NULL,
`artist` text NOT NULL,
`album` text NOT NULL,
`title` text NOT NULL,
`track` text NOT NULL,
`year` varchar(8) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `artist` (`artist`),
FULLTEXT KEY `album` (`album`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `track` (`track`),
FULLTEXT KEY `year` (`year`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

The table set up will be similar to:

id	artist	album	title	track	year

NOTE: You may want to use AUTO INCREMENT to have MySQL automatically maintain the id field when you add a new item to the table. This is up to you on how you would like to maintain the id., which in this case is the "PRIMARY KEY" which needs to be unique and is what MySQL needs to keep things orderly:

CREATE TABLE IF NOT EXISTS `music` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`artist` text NOT NULL,
`album` text NOT NULL,
`title` text NOT NULL,
`track` text NOT NULL,
`year` varchar(8) NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `artist` (`artist`),
FULLTEXT KEY `album` (`album`),
FULLTEXT KEY `title` (`title`),
FULLTEXT KEY `track` (`track`),
FULLTEXT KEY `year` (`year`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Refer to the MySQL documentation for more info on defining column data types.

INSERT STATEMENTS

INSERT INTO table_name ( `col_A`, `col_B`, `col_C`) VALUES ( `col_A_data`, `col_B_data`, `col_C_data`) ;

Inserts a row into the table using the data defined in the VALUES section.

As you can see, the column names are established within the first set of parenthesis, the order of the data in the second set of parenthesis must match the order of the column names defined int he first set of parentheses.

Example:

INSERT INTO music ( 'id', `artist`, `album`) VALUES ( '1', `the beatles`, `Abbey Road`);

Puts a row into the database tables as:

id	artist	album	title	track	year
1	the beatles	Abbey Road

I've color coded each "pair" so you can see that the data "col_B_data' that is going to go into column name "col_B" must be second. if there is no data available for col_B, use empty single quotes as:

INSERT INTO table_name ( `col_A`, `col_B`, `col_C`) VALUES ( `col A data `, ``, `col C data`) ;

Example:

INSERT INTO music ( 'id', `artist`, `album`) VALUES ( '1', ``, `Abbey Road`);

Puts a row into the database tables as:

id	artist	album	title	track	year
1		Abbey Road

REPLACE STATEMENTS

REPLACE INTO table_name ( `col_A`, `col_B`) VALUES ( `col A data`, `col B data`) ;

Replaces items in a row.   This action is dependant on the "id" because all tables need to have at least one "unique" column. Meaning that one of the columns must be used to provide row data that is unique to each row. In this example, "id" is the unique row.

Example:

REPLACE INTO music ( 'id', `artist`, `album`) VALUES ( '1', `the beatles`, `abbey road`);

Puts a row into the database tables as:

id	artist	album	title	track	year
1	the beatles	Abbey Road

As you can see, we've just replaced the empty "artist" with the new data of "the beatles"

REPLACE behaves much like INSERT except that if the "unique column" is supplied, the existing row will be updated, if the "unique column" is not provided, a new row will be added.

Example:

REPLACE INTO music ( `artist`, `album`) VALUES ( `the beatles`, `abbey road`);

Puts a new row into the database tables as:

id	artist	album	title	track	year
1	the beatles	Abbey Road
	the beatles	Abbey Road

NOTE: You may get errors when trying this kind of REPLACE, because usually the "unique column" can not be null (or empty). Some people like to set up the database where the "unique column" is automatically incremented by MySQL. This is kind of confusing and can lead to a headache. So just remember to include the "unique column" when using the REPLACE statement, or you'll get duplicate rows... OR use the UPDATE statement.

UPDATE STATEMENTS

UPDATE table_name SET col_B='new_data'  WHERE col_A='reference_data' ;

Will update a row's columns with the new values specified in the SET section and the row to update is specified by the WHERE section

Example:

UPDATE music SET title='Come Together' WHERE id=1;

Notice that quotes are not around the id-1. This is because you shouldn't have to use quotes when you are entering an integer.

id	artist	album	title	track	year
1	the beatles	Abbey Road	Come Together

To update two or more columns, separate value pairs in the SET section with a comma

Example:

UPDATE music SET track='1', year='1969'  WHERE id=1;

Notice that quotes are not around the id-1. This is because you shouldn't have to use quotes when you are entering an integer.

id	artist	album	title	track	year
1	the beatles	Abbey Road	Come Together	1	1969

It may seem frustrating to see that quotes were used around the "integers' in the SET section, but not in the WHERE section. Sometimes you may need to use them, but sometimes you do not. A llot of it depends on how the database was originally set up. If we initially set up the database where "track" and "year" did not use TEXT, but rather INT(11) we could probably get away with not using quotes around the data in the SET section. This issue also has to do with how MySQL is set up on your server.

Another thing to consider is that because we're using single quotes around the data that we are entering... what if your data contains a single quote? Such as:

UPDATE music SET title='Mike's Song' WHERE id=1;

As you can see, now there are three single quotes, and chances are MySQL will return an error. There are two things you can try.

1. Escape the single quote in the data as:

UPDATE music SET title='Mike\'s Song' WHERE id=1;

1. Use tricky quotes:

UPDATE music SET title=`Mike's Song` WHERE id=1;

Tricky quotes are just above the "tab" button on your keyboard.

SELECT STATEMENTS

SELECT * FROM table_name WHERE 1 ;

Returns all rows and all columns from table_name.. All rows is specified by the * (asterisk) symbol between SELECT and FROM.

Example:

SELECT * FROM music WHERE 1;

Returns:

id	artist	album	title	track	year
1	the beatles	Abbey Road	Come Together	1	1969
2	the beatles	Abbey Road	Something	2	1969
3	the beatles	Abbey Road	Maxwell's Silver Hammer	3	1969
4	the beatles	Abbey Road	Oh! Darling	4	1969
5	the beatles	Abbey Road	Octopus's Garden	5	1969
6	the beatles	Abbey Road	I Want You (She's So Heavy)	6	1969
7	the beatles	Abbey Road	Here Comes the Sun	7	1969
8	the beatles	Abbey Road	Because	8	1969
9	the beatles	Abbey Road	You Never Give Me Your Money	9	1969
10	the beatles	Abbey Road	Sun King	10	1969
11	the beatles	Abbey Road	Mean Mr. Mustard	11	1969
12	the beatles	Abbey Road	Polythene Pam	12	1969
13	the beatles	Abbey Road	She Came in Through the Bathroom Window	13	1969
14	the beatles	Abbey Road	Golden Slumbers	14	1969
15	the beatles	Abbey Road	Carry That Weight	15	1969
16	the beatles	Abbey Road	The End	16	1969
17	the beatles	Abbey Road	Her Majesty	17	1969
18	beatles	Rubber Soul	Drive My Car	1	1969
19	beatles	Rubber Soul	Norwegian Wood (This Bird Has Flown)	2	1969
20	beatles	Rubber Soul	You Won't See Me	3	1969
21	beatles	Rubber Soul	Nowhere Man	4	1969
22	beatles	Rubber Soul	Think for Yourself	5	1969
23	beatles	Rubber Soul	The Word	6	1969
24	beatles	Rubber Soul	Michelle	7	1969
25	beatles	Rubber Soul	What Goes On	8	1969
26	beatles	Rubber Soul	Girl	9	1969
27	beatles	Rubber Soul	I'm Looking Through You	10	1969
28	beatles	Rubber Soul	In My Life	11	1969
29	beatles	Rubber Soul	Wait	12	1969
30	beatles	Rubber Soul	If I Needed Someone	13	1969
31	beatles	Rubber Soul	Run for Your Life	14	1969

SELECT * FROM table_name WHERE column_name IN ('data_1','data_2') ;

Returns all rows* and all columns that have matching data specified in the IN section. The data defined in the IN section must match exactly.

NOTE: All rows is specified by the * symbol between SELECT and FROM sections.

Example:

SELECT * FROM music WHERE track IN ('1','2');

Returns:

id	artist	album	title	track	year
1	the beatles	Abbey Road	Come Together	1	1969
2	the beatles	Abbey Road	Something	2	1969
18	beatles	Rubber Soul	Drive My Car	1	1969
19	beatles	Rubber Soul	Norwegian Wood (This Bird Has Flown)	2	1969

SELECT col_A_name, col_B_name FROM table_name WHERE col_B_name='search_term' ;

Returns only two columns from the matching row. The data defined int eh WHERE section must match exactly.

Example:

SELECT artist, album FROM music WHERE title='You Never Give Me Your Money';

Returns:

artist	album
the beatles	Abbey Road

SELECT col_A_name, col_B_name FROM table_name WHERE col_B_name IN ('search_term_1','search_term_2') ;

Returns only two columns that match the data defined in the IN section.

Example:

SELECT title, album FROM music WHERE title='You Won\'t See Me', 'I\'m Looking Through You';

NOTE: Notice that the single quotes are "escaped" by using a back slash within the data. You may want to try using tricky quotes instead as:

SELECT album, date FROM music WHERE title=`You Won't See Me`, `I'm Looking Through You`;

Returns:

album	date
Rubber Soul	1969
Rubber Soul	1969

SELECT * FROM table_name WHERE col_A_name RLIKE ('search_termA') OR col_B_name RLIKE ('search_termB') ;

This example will search through your table for matching words.

Will return all rows that contain either search_termA OR search_term_B -- the search term can be a single word or a phrase.

NOTE: This options is based on having MySQL set up to take advantage of FULLTEXT, which was established when we set up the table. Also note that some MySQL configurations will not return matches on words that are shorter than 4 characters. So for this example, MySQL may not return anything. So you may want to experiment with this kind of search with a larger word such as "Yourself."

NOTE 2: This is actually the old way to do things, but more reliable than the new method. The new method uses the MATCH statement, but requires some additional configuration on your MySQL installation / server. The method offered here is probably a little more reliable as of this writing.

NOTE TO SELF: If the year is 2013, you may have better luck using the MATCH statement. I'm sure I'll be dead by then, so your on your own. Check with the MySQL documentation.

Example:

SELECT * FROM music WHERE title RLIKE ('you') OR title RLIKE ('mustard') ;

Returns:

id	artist	album	title	track	year
6	the beatles	Abbey Road	I Want You (She's So Heavy)	6	1969
9	the beatles	Abbey Road	You Never Give Me Your Money	9	1969
20	beatles	Rubber Soul	You Won't See Me	3	1969
27	beatles	Rubber Soul	I'm Looking Through You	10	1969
11	the beatles	Abbey Road	Mean Mr. Mustard	11	1969

SELECT * FROM table_name WHERE col_A_name RLIKE ('search_termA') AND col_B_name RLIKE ('search_termB') ;

This example will search through your table for matching words.

Will return all rows that contain either search_termA OR search_term_B -- the search terms can be a single word or a phrase.

Example:

SELECT * FROM music WHERE title RLIKE ('you') AND title RLIKE ('me') ;

Returns:

id	artist	album	title	track	year
9	the beatles	Abbey Road	You Never Give Me Your Money	9	1969
20	beatles	Rubber Soul	You Won't See Me	3	1969

NOTE: In this example, we are only searching within the same column, you can search different columns if you want, and you can also add more OR or AND statements, or you can mix and match OR and AND statements to refine the search..

Example:

SELECT * FROM music WHERE title RLIKE ('you') OR artist RLIKE ('the') AND track RLIKE ('1969');

SELECT * FROM table_name WHERE 1 ORDER BY RAND() LIMIT number_of_rows_to_return ;

Will select random rows and limit the number of rows returned to the number specified in the LIMIT section.

Example:

SELECT * FROM music WHERE 1 ORDER BY RAND() LIMIT 3;

Returns something similar to :

(Can't be too sure here, since MySQL is picking rows randomly, but you can see that only three rows are returned :)

id	artist	album	title	track	year
25	beatles	Rubber Soul	What Goes On	8	1969
13	the beatles	Abbey Road	She Came in Through the Bathroom Window	13	1969
5	the beatles	Abbey Road	Octopus's Garden	5	1969

SELECT DISTINCT col_A FROM table_name WHERE 1;

Will only return rows that have unique data in the column specified.

Perhaps the best way to explain this is through example.

If we do something like this:

SELECT DISTINCT year FROM music WHERE 1;

... we will only get one row and one column because all the rows in our table have "1969" set as the year. MySQL will pick the first unique row, all subsequent rows that have the same data in the specified column will not be returned, because the data in the other rows is not unique.

year

1969

If we do something like:

SELECT DISTINCT artist FROM music WHERE 1;

We'll only get two rows:

artist

the beatles

beatles

... because the for the first album, we used "the beatles" whereas the second album, we just used "beatles" -- so only the first row that MySQL encounters with unique data in the "artist" column will be returned... because the rest of the rows are not unique.

SELECT DISTINCT col_A,col_B FROM table_name WHERE 1 ORDER BY RAND() LIMIT number_of_rows_to_return ;

You can add the DISTINCT statement to any statement. So that only unique items are returned. Or another way to think about it is that no duplicate rows are returned. For example, if you used the RLIKE statement to look for a couple key words, MySQL may return the dame row two times, you can use the DISTINCT statement to filter only unique rows.

Example:

SELECT DISTINCT artist,album FROM music WHERE 1 ORDER BY RAND() LIMIT 5;

This example is deviating slightly from out standard "All beatles" table, in this example, we'll have to imaging that our tables contains a whole bunch of artists and albums.

So, with your imagination flying, the example above would return 5  rows, where both the artist and album are unique.

artist	album
beatles	Rubber Soul
the beatles	Abbey Road
Ray Lamontagne	Trouble
The Tragically Hip	Wheat kings
The Police	Outlandos d'Amour

NOTE: Yes, both "the beatles" and "beatles" albums would be returned because the entire data contained in the "artist" column is unique for both of these albums.

SELECT MAX(col_A) FROM table_name ;

Will return the highest value for that column. Usually you want to sue this on a column that references a number. For example, let's say you wanted to find the highest ID on your table so that you could determine the next ID to use fro a new item that you are inserting into your tables. You could use the MAX statement to find the largest ID number, then when you INSERT your new item, you can simply bump up the number by one.

Example:

SELECT MAX(id) FROM music;

Returns:

id

31

... because the highest ID in our table is 31. e.g. there are 31 items in our table as defined by the ID column.

If we ran something like:

SELECT MAX(track) FROM music ;

... we'd get:

track

17

... because 17 is the highest number in the "track" column

DELETE STATEMENTS

DELETE FROM table_name WHERE column_name='search_data';

Will remove rows that match the search data defined in the WHERE section

Example:

DELETE FROM music WHERE artist='the beatles';

Will remove all rows that have "the beatles" defined in the "artist" column.

DELETE FROM table_name WHERE column_name IN ('search_data_A', 'search_data_B');

Will remove all rows that match the search data defined in the IN section.

Example:

DELETE FROM music WHERE artist IN ('the beatles', 'beatles');

... will remove all the items from our example table