☰ ☰

SQL - SQL Data manipulation

1. Add records

Add 1 or more records. The values per record are separated by a comma.

INSERT INTO `category` (`id`, `name`, `active`) VALUES
(1, 'HTML', 1),
(2, 'CSS', 1),
(3, 'Javascript', 1);

2. Edit a record

Change the contents of a record of a database table.

UPDATE category
SET id=1,name='HTML5',active=1
WHERE id=1;

3. Delete a record

Delete a record from a database table.

DELETE FROM category
WHERE id=14;

4. Select all records

Select the contents of all fields and all records from a database table.

SELECT * FROM category;

5. Conditional Selection

Select the content of all fields and all records where the content of the field 'active' is equal to 1.

SELECT * 
FROM category 
WHERE active = 1;

6. Sorted selection

Select the contents of all fields and all records from a database table.
Sort the result records on the 'name' field.
Limit the number of records of the result to the records of the sixth record through the tenth record.

SELECT * 
FROM category 
ORDER BY name LIMIT 6,10;

7. Number of records

Count the number of records of a database table.
The result contains 1 record with 1 field containing the value of the count.

SELECT COUNT(id) 
FROM category;

8. Select 1 record

Select 1 record from a database table where the content of the 'id' field is equal to a specified value.
The result consists of 1 record with the contents of all fields of that record.

SELECT * 
FROM category 
WHERE id = 4;

9. Records of 2 tables

Select the contents of the fields 'wine_name' and 'cost' of all records from the database tables 'wine' and 'inventory' where the contents of the field 'cost' (from the 'inventory' table) is less than 5.2
The relationship between the 2 tables is established between the contents of the field 'wine_id' of the table 'wine' and the field 'wine_id' of the table 'inventory'. The contents of these 2 fields must be equal.
The primary key 'wine_id' of the table 'wine' is compared with the foreign key 'wine_id' of the table 'inventory'.

SELECT wine_name, cost 
FROM wine, inventory 
WHERE wine.wine_id = inventory.wine_id 
     AND cost < 5.2;

Pages

Paragraphs