The ultimate web site music player

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
ALTER TABLE
INSERT
REPLACE
UPDATE
SELECT
DELETE

VALUES
SET
WHERE
IN
RLIKE / MATCH (search)
AND
OR
DISTINCT
MAX

PHP Junkies

NOTE: For those using PHP, you'll need to established a connection before attempting to use any of the examples below.

We put a reminder <ASSUME CONNECTION ALREADY EXISTS ON PAGE> in each example, which you should replace with:

$link = mysql_connect( "localhost", "adminUserName", "adminPassword")
   or die("Unable to connect. Check username and passowrd.<BR>".mysql_error());

mysql_select_db("myCoolDatabaseName", $link)
   or die(mysql_error());

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

  Click here to download the test.php file (it's wrapped in a ZIP named "mysql_basics_php.zip")

 

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.

 

 

CREATE DATABASE

$sql = "CREATE DATABASE IF NOT EXISTS myCoolDatabaseName";
mysql_query($sql);

CREATE TABLE

$sql = "DROP TABLE IF EXISTS music";
mysql_query($sql);
$sql = "CREATE TABLE IF NOT EXISTS `music` (
`id` int(8) unsigned NOT NULL,
`artist` text,
`album` text,
`title` text,
`track` text,
`year` varchar(8) DEFAULT 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;";

mysql_query($sql);

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:

You can modify the table using the following command:

$sql = "ALTER TABLE music MODIFY id int(8) unsigned NOT NULL AUTO_INCREMENT;";
mysql_query($sql);

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.

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:

$sql = "INSERT INTO music (id,artist,album) VALUES ('1','the beatles','Abbey Road')";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road
Records: 1

 

In this example, we don't include the "artist" column in our SQL.

$sql = "INSERT INTO music (id,album) VALUES ('2','Abbey Road 2 test')";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road
2 Abbey Road 2 test
Records: 2

 

Here, we're not including the "id" column, however, since the "id" column is set up to "AUTO INCREMENT" MySQL automatically assigns the new data the next incremental number.

$sql = "INSERT INTO music (album) VALUES ('Abbey Road 3 test')";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road
2 Abbey Road 2 test
3 Abbey Road 3 test
Records: 3

 

 

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

In this example, we are replacing row #2

$sql = "REPLACE INTO music (id,artist,album) VALUES ('2','The Beatles','Let It Be')";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road
2 The Beatles Let It Be
3 Abbey Road 3 test
Records: 3

 

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.

$sql = "REPLACE INTO music (artist,album) VALUES ('The Beatles','The Magical Mystery Tour')";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road
2 The Beatles Let It Be
3 Abbey Road 3 test
4 The Beatles The Magical Mystery Tour
Records: 4

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.

$sql = "REPLACE INTO music (id, artist, album, title, track, year) 
	VALUES
	("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");";

mysql_query($sql);>> RESULT:

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);
title album year
You Won't See Me Rubber Soul 1965
I'm Looking Through You Rubber Soul 1965
Records: 2

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.

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);
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
11 the beatles Abbey Road Mean Mr. Mustard 11 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: 7

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.

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.

This SELECT statement returns random results. Plus we're limiting how many items get returned.

$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

 

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

PHP Example:

<ASSUME CONNECTION ALREADY EXISTS ON PAGE>

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

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);
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
Records: 17

 

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.

NOTE: The terms in the IN must match exactly. (Non-case-sensative)

$sql = "DELETE FROM music WHERE title IN ('Her Majesty','Because','Something','Oh! Darling');";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road Come Together 1 1969
3 the beatles Abbey Road Maxwell's Silver Hammer 3 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
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
Records: 13

 

For our last exmple, we'll first need to re-populate the table with all the data:

$sql = "REPLACE INTO music (id, artist, album, title, track, year) 
	VALUES
	("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");";
mysql_query($sql);

>> RESULT: 1

OK, now that we have a new data set in our table, lets continue.

 

 Here we will use the RLIKE search functionality to filter through what to delete.

NOTE: You may want to look into using the MATCH rather than RLIKE, it all depends on the version of MySQL you are using.

$sql = "DELETE FROM music WHERE title RLIKE ('you') OR artist RLIKE ('me') OR artist RLIKE ('my');";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road Come Together 1 1969
3 the beatles Abbey Road Maxwell's Silver Hammer 3 1969
2 the beatles Abbey Road Something 2 1969
5 the beatles Abbey Road Octopus's Garden 5 1969
7 the beatles Abbey Road Here Comes the Sun 7 1969
19 beatles Rubber Soul Norwegian Wood (This Bird Has Flown) 2 1965
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
4 the beatles Abbey Road Oh! Darling 4 1969
8 the beatles Abbey Road Because 8 1969
17 the beatles Abbey Road Her Majesty 17 1969
18 beatles Rubber Soul Drive My Car 1 1965
26 beatles Rubber Soul Girl 9 1965
25 beatles Rubber Soul What Goes On 8 1965
24 beatles Rubber Soul Michelle 7 1965
23 beatles Rubber Soul The Word 6 1965
21 beatles Rubber Soul Nowhere Man 4 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
Records: 25

 

© Michael Gieson