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.

I've created a test file for you to download and use as you give things a whirl on your own system. The test file was tested on a Windows 7 box running IIS. So there may be some differences in how it works on Apache or some other environment.

The test file contains a few helper functions and outputs "what you do is what you see".

Non-PHP Junkies

The example SQL statements are simple strings (no variables and such), so you should be able to simply use the "stuff between the double quotes" in your other language , app or directly in the MySQL console.

ALTER TABLE

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:

INSERT STATEMENTS

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.

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:

REPLACE STATEMENTS

Replaces items in a row. This action is dependant on the "id" because when doing a REPLACE, you must include the PRIMARY (unique) column. Since we established the "id" column as our PRIMARY key (when establishing the table), MySQL needs this info so it knows which row we are talking about. If we didn't include the "id" coumn, MySQL will have no idea which row we are trying to replace.

Here, we haven't defined the "id" column. Hence, MySQL doesn't know which row to replace, so it just adds a new row.

So as we can see, the REPLACE feature acts very similar to INSERT. We can use this to our advantage!

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

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 where:
- The row to update is specified by the WHERE section
- The new values are specified in the SET section.

Here we will change row #1's title to "Come Together"

$sql = "UPDATE music SET title='Come Together' WHERE id='1'";mysql_query($sql);

id

artist

album

title

track

year

1

the beatles

Abbey Road

Come Together

2

The Beatles

Let It Be

3

Abbey Road 3 test

4

The Beatles

The Magical Mystery Tour

Records: 4

Here we'll update the track and year information.

$sql = "UPDATE music SET track='1', year='1969' WHERE id='1'";mysql_query($sql);

id

artist

album

title

track

year

1

the beatles

Abbey Road

Come Together

1

1969

2

The Beatles

Let It Be

3

Abbey Road 3 test

4

The Beatles

The Magical Mystery Tour

Records: 4

Just for the heck of it, we'll change the track to a value that contains an apostrophe.

Also notice that quotes are not around the "id" in the WHERE. This is because you shouldn't have to use quotes when you are entering an integer.

$sql = "UPDATE music SET track='Mike\'s Song' WHERE id=1";mysql_query($sql);

id

artist

album

title

track

year

1

the beatles

Abbey Road

Come Together

Mike's Song

1969

2

The Beatles

Let It Be

3

Abbey Road 3 test

4

The Beatles

The Magical Mystery Tour

Records: 4

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;

If you're using PHP, the myStringPrep function should cover this issue, however, you may want to do some testing to see how your particular PHP set up handles quotes, as all PHP installations are different / handle things differently.

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

NOTE: Before getting into the SELECT stuff let's first populate the table with a bunch of crap. Notice that we are leveraging the REPLACE feature here.

OK, now that we've got some data to work with, we can start doing some SELECT statements.

SELECT * FROM table_name WHERE 1 ;

Returns all rows and all columns from table_name. The * means "all rows and all columns (e.g. everything). The WHERE=1 is kinda like saying WHERE=true, in a boolean kind of way. In other words, as long as data exists, get it.

$sql = "SELECT * FROM music WHERE 1;";mysql_query($sql);

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

1965

19

beatles

Rubber Soul

Norwegian Wood (This Bird Has Flown)

2

1965

20

beatles

Rubber Soul

You Won't See Me

3

1965

21

beatles

Rubber Soul

Nowhere Man

4

1965

22

beatles

Rubber Soul

Think for Yourself

5

1965

23

beatles

Rubber Soul

The Word

6

1965

24

beatles

Rubber Soul

Michelle

7

1965

25

beatles

Rubber Soul

What Goes On

8

1965

26

beatles

Rubber Soul

Girl

9

1965

27

beatles

Rubber Soul

I'm Looking Through You

10

1965

28

beatles

Rubber Soul

In My Life

11

1965

29

beatles

Rubber Soul

Wait

12

1965

30

beatles

Rubber Soul

If I Needed Someone

13

1965

31

beatles

Rubber Soul

Run for Your Life

14

1965

Records: 31

SELECT * FROM table_name WHERE column_name='some value';

Returns only the item(s) that match the data set in the column_name.

NOTE: The data defined in the WHERE section must match exactly. For example, if you set WHERE title="Life", MySQL will not return row #31, because the data for the "title" coumn is "Run for Your Life" not just "Life"

$sql = "SELECT * FROM music WHERE title='You Never Give Me Your Money';";mysql_query($sql);

id

artist

album

title

track

year

9

the beatles

Abbey Road

You Never Give Me Your Money

9

1969

Records: 1

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

Returns all rows and all columns that where the data in column_name matches the stuff specified in the IN section.

NOTE: All rows all columns is specified by the * symbol between SELECT and FROM sections.NOTE: The data defined in the IN section must match the data exactly. Case sensativity is questionable.

Here we are selecting rows that have either 1 or 2 in the "track" column.

$sql = "SELECT * FROM music WHERE track IN ('1','2');";mysql_query($sql);

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

1965

19

beatles

Rubber Soul

Norwegian Wood (This Bird Has Flown)

2

1965

Records: 4

Here we are asking MySQL to only return 3 columns in our returned data set: the title, album and year.

Plus, we only want songs that have the title of "You Won't See Me" and "I'm Looking Trhough You"

NOTE: Notice that the single quotes are "escaped" by using a back slash within the data. You may want to / need to use tricky quotes instead.NOTE: In the PHP test file, we are using the
myStringPrep() function to automatically "escape" embedded quotes.

$sql = "SELECT title,album,year FROM music WHERE title IN ('You Won\'t See Me','I\'m Looking Through You');";mysql_query($sql);

The RLIKE is a basic search mechanism. However, RLIKE is not Google, and it is limited. Most MySQL configurations set a threshold on how many characters RLIKE will accept. For example, 3 characters is a normal minimum. Meaning, that if the value defined in RLIKE is only two characters long, nothing will be returned. Of course, this all varies depending on your particular MySQL setup.

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.

Also, In order for RLIKE to work, the table must be configured where each of the column / fields you wich to conduct searches on must have the FULLTEXT attribute applied when esablishing the table. (You can apply the FULLTEXT attribute using the ALTERTABLE method.

Here we want all available columns (*), where the title contains "you" or "mustard".

$sql = "SELECT * FROM music WHERE title RLIKE ('you') OR title RLIKE ('mustard');";mysql_query($sql);

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

RLIKE can only contain one "thing" to search for at a time. However, since RLIKE is basically modifying our WHERE conditions, you can string together AND / OR statements to adjust the RLIKE search criteria.

Here we are saying: Return rows that contain "you" AND "me"

$sql = "SELECT * FROM music WHERE title RLIKE ('you') AND title RLIKE ('me');";mysql_query($sql);

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

1965

Records: 2

Again, you can only include on "thing" in a given RLIKE. For example RLIKE ("foo", "bar") will not work. To accomplish this kind of concept, you'll need two RLIKEs RLIKE ("foo") AND RLIKE ("bar").

Taking things a step further, lets get tricky and throw in some conditionals.

Here we are saying: Search for rows where:
-
the title field contains "you"
OR
-
the title field contains "me" AND "the"

Notice how we've place the stuff after the OR inside parenthesis?

$sql = "SELECT * FROM music WHERE title RLIKE ('you') OR (title RLIKE ('the') AND title RLIKE ('me'));";mysql_query($sql);

id

artist

album

title

track

year

1

the beatles

Abbey Road

Come Together

1

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

9

the beatles

Abbey Road

You Never Give Me Your Money

9

1969

13

the beatles

Abbey Road

She Came in Through the Bathroom Window

13

1969

20

beatles

Rubber Soul

You Won't See Me

3

1965

22

beatles

Rubber Soul

Think for Yourself

5

1965

27

beatles

Rubber Soul

I'm Looking Through You

10

1965

31

beatles

Rubber Soul

Run for Your Life

14

1965

Records: 9

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.

$sql = "SELECT * FROM music WHERE 1 ORDER BY RAND() LIMIT 3;";mysql_query($sql);

id

artist

album

title

track

year

17

the beatles

Abbey Road

Her Majesty

17

1969

27

beatles

Rubber Soul

I'm Looking Through You

10

1965

24

beatles

Rubber Soul

Michelle

7

1965

Records: 3

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.

Here we are looking for unique data. We don't want all of the data returned, just a simple result containing the actual data.

$sql = "SELECT DISTINCT year FROM music WHERE 1;";mysql_query($sql);

year

1969

1965

Records: 2

Likewise, we are only looking for all of the unique "artists" in our table, and returning just the data.

$sql = "SELECT DISTINCT artist FROM music WHERE 1;";mysql_query($sql);

artist

the beatles

beatles

Records: 2

Here we are asking for the unique artist / albums abailable in our table. Plus we are limiting and randomizing the results.

$sql = "SELECT DISTINCT artist,album FROM music WHERE 1 ORDER BY RAND() LIMIT 5;";mysql_query($sql);

>> RESULT: Resource id #19

artist

album

the beatles

Abbey Road

beatles

Rubber Soul

Records: 2

SELECTDISTINCT 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 same 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, we'll have to image our table 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.

Looking for the highest id in our table. Good for when AUTO INCREMENT is not set, so you can add a new row.

$sql = "SELECT MAX(id) FROM music";mysql_query($sql);

>> RESULT: Resource id #20

MAX(id)

31

Records: 1

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

$sql = "SELECT MAX(track) FROM music";mysql_query($sql);

... we'd get:

MAX(track)

17

Records: 1

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

NOTE: In order for MAX to work, the feild must be set to a number or int. Max doesn't work on varchar or text.

DELETE STATEMENTS

DELETE FROM table_name WHERE column_name='search_data';

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

This example removes all the rows that have "rubber sould" assigned ot the "album" column.

$sql = "DELETE FROM music WHERE album='rubber soul'";mysql_query($sql);