MySQL row operations

From Knowledge Kitchen
Jump to navigation Jump to search


This document outlines the classic Create, Read, Update, and Delete (CRUD) methods available in the SQL language for manipulating records in a relational database.

Create a new row in a table

Commands

  • INSERT

Examples

 1 #the following query creates a new row in the table
 2 INSERT INTO favorite_viking_metal_bands (
 3 	band,
 4 	formed,
 5 	origin
 6 )
 7 VALUES (
 8 	'Amon Amarth',  
 9 	1992,  
10 	'Sweden'
11 );
 1 #the following query uses MySQL's LOWER() function to convert the strings to lowercase before saving in the table.  
 2 #such string processing can be useful to make sure all data is stored in a standardized format.
 3 INSERT INTO favorite_viking_metal_bands (
 4 	band,
 5 	formed,
 6 	origin
 7 )
 8 VALUES (
 9 	LOWER('Ancient Rites'),  
10 	'1998', 
11 	LOWER('Belgium')
12 );

Importing data from a file into rows

Notes

Commands

  • LOAD DATA

Examples

1 #the following query, will import the data from a standard CSV file, ignoring the first line in the file
2 #in this case, we are specifying specific values for many of the options, so this will work
3 LOAD DATA LOCAL INFILE 'viking_metal_bands.csv' 
4 INTO TABLE favorite_viking_metal_bands 
5 FIELDS TERMINATED BY ',' 
6 ENCLOSED BY ''
7 LINES TERMINATED BY '\n'
8 IGNORE 1 LINES
9 (band, formed, origin);

Read rows from a table

Commands

  • SELECT

Examples

#the following query retrieves all rows of data from the table
SELECT * from favorite_viking_metal_bands WHERE 1;
#the following query retrieves only a single field from all rows of data from the table
SELECT band from favorite_viking_metal_bands WHERE 1;
#the following query retrieves only those rows of data from the table where the 'formed' field has a value greater than 1990:
SELECT * from favorite_viking_metal_bands WHERE formed>1990;
#the following query retrieves only a single field from those rows of data from the table where the 'formed' field has a value greater than 1990:
SELECT band from favorite_viking_metal_bands WHERE formed>1990;
#the following query retrieves three fields from those rows of data in the table where the 'origin' is either 'Norway' or 'Finland'
SELECT band, origin, formed from favorite_viking_metal_bands WHERE origin='Norway' OR origin='Finland';
#the following query retrieves three fields from those rows of data in the table where the origin is neither 'Norway' nor 'Finland'
SELECT band, origin, formed from favorite_viking_metal_bands WHERE origin!='Norway' AND origin!='Finland';
#the following query retrieves all rows of data from the table, and orders them alphabetically by the band field
SELECT * FROM favorite_viking_metal_bands WHERE 1 ORDER BY band ASC;
#the following query will order all rows alphabetically, and then return only the first 10
SELECT band from favorite_viking_metal_bands WHERE 1 ORDER BY band ASC LIMIT 10;
#the following query will order all rows alphabetically, and then return only the first 10... note the minor change in syntax from the previous example
SELECT band from favorite_viking_metal_bands WHERE 1 ORDER BY band ASC LIMIT 0,10;
#the following query will order all rows alphabetically, and then return only the second 10 results
SELECT band from favorite_viking_metal_bands WHERE 1 ORDER BY band ASC LIMIT 10,10;
#the following query will order all rows alphabetically, and then return only the third 10 results
SELECT band from favorite_viking_metal_bands WHERE 1 ORDER BY band ASC LIMIT 20,10;
#the following query retrieves those rows of data from the table where the origin has the value "Netherlands", and orders them by the formed field in reverse numeric order
SELECT * FROM favorite_viking_metal_bands WHERE origin="Netherlands" ORDER BY formed DESC;
#the following will read all the bands with names including the word "Thor"
SELECT * FROM favorite_viking_metal_bands WHERE band LIKE "%Thor%";

Read aggregate data from a table

Aggregate functions allow you to generate insights into sets of rows. They are used in tandem with the GROUP BY clause.

Aggregate functions

  • MIN()
  • MAX()
  • AVG()
  • SUM()
  • COUNT()

Examples

#the following query retrieves the earliest year a viking metal band was formed, broken down by country of origin
SELECT origin, MIN(formed) FROM favorite_viking_metal_bands GROUP BY origin;
#the following query retrieves the latest year a viking metal band was formed, broken down by country of origin
SELECT origin, MAX(formed) FROM favorite_viking_metal_bands GROUP BY origin;
#the following query retrieves the average year a viking metal band was formed, broken down by country of origin
SELECT origin, AVG(formed) FROM favorite_viking_metal_bands GROUP BY origin;
#the following query retrieves the sum of all years that viking metal band were formed, broken down by country of origin
SELECT origin, SUM(formed) FROM favorite_viking_metal_bands GROUP BY origin;
#the following query retrieves the number of bands formed, broken down by country of origin
SELECT origin, COUNT(formed) FROM favorite_viking_metal_bands GROUP BY origin;

Update a row in a table

Commands

  • UPDATE

Examples

#the following example updates the row with the id value of 9, and changes the origin field to be "sweden"
UPDATE favorite_viking_metal_bands SET origin='sweden' WHERE id=9;
#the following example updates any rows with the band field, "Amon Amarth", and the formed field greater than 1998
UPDATE favorite_viking_metal_bands SET origin='sweden' WHERE band="Amon Amarth" AND formed>1998;

Delete a row in a table

Commands

  • DELETE

Examples

#the following query deletes all rows from a table
DELETE from favorite_viking_metal_bands WHERE 1;
#the following query deletes only that row with id 9
DELETE from favorite_viking_metal_bands WHERE id=9;
#the following query deletes any rows with band name "Ancient Rites" and origin, "belgium"
DELETE from favorite_viking_metal_bands WHERE band="Ancient Rites" and origin="Belgium";
#the following query deletes any rows where the formed field is greater than 1998
DELETE from favorite_viking_metal_bands WHERE formed>1998;

What links here