MySQL supports five basic database operations: insertion, deletion, mutation, selection, and join. They are used to populate database tables and modify and retrieve the existing data. These operations would normally originate in your data analysis program, but to get the sense of them, we will first practice them at the mysql command-line prompt.
Insertion
First things first. We’ll insert a new record into a table, and then another one, and another one, until the table has all observations:
INSERT INTO employee VALUES(NULL,NULL,"John Smith",35000,NOW());
Query OK, 1 row affected, 1 warning (0.18 sec)
➾
The first two NULLs are placeholder values of the index and the timestamp.
The server calculates them automatically. The function NOW() returns the current date and time, but only the “date” part is used to populate the record.
Note that the query produced a warning, and the reason for it is the latter truncation. Let’s look at the verbal descriptions and codes of the most recent warning(s) and error(s):
SHOW WARNINGS;
+---+---+---+
➾
| Level | Code | Message |
➾
+---+---+---+
➾
| Note | 1265 | Data truncated for column 'hired' at row 1 |
➾
+---+---+---+
➾
1 row in set (0.00 sec)
➾
If an insertion operation violates the UNIQUE constraint, the server aborts it unless you specify the IGNORE keyword, in which case the insertion fails:
INSERT INTO employee VALUES(NULL,NULL,"John Smith",35000,NOW());
ERROR 1062 (23000): Duplicate entry 'John Smith' for key 'empname'
➾
INSERT IGNORE INTO employee VALUES(NULL,NULL,"John Smith",35000,NOW());
Query OK, 0 rows affected, 1 warning (0.14 sec)
➾
You could insert more rows by hand, but the preferred way is to let Python do the rest of the insertions.
Deletion
Deletion removes from the table all the records that match the search criterion.
If you don’t specify the search criterion, the server will remove all records:
-- Remove John Smith if he is low-paid
DELETE FROM employee WHERE salary<11000 AND empname="John Smith";
-- Remove everyone DELETE FROM employee;
If you want to remove only a particular record, use its unique primary key or any other unique identifying condition:
DELETE FROM employee WHERE id=387513;
Remember that deletion is irreversible!
Mutation
Mutation updates the values of specified columns in the records that match the search criterion. If you don’t specify the search criterion, the operation will affect all records:
-- Reset all recent hires' salary
UPDATE employee SET salary=35000 WHERE hired=CURDATE();
-- Increase John Smith's salary again
UPDATE employee SET salary=salary+1000 WHERE empname="John Smith";
Query OK, 1 row affected (0.06 sec)
➾
Rows matched: 1 Changed: 1 Warnings: 0
➾
And you’ve guessed it right: mutation is irreversible, too. Just like deletion, it is a destructive operation.
Selection
Selection selects all requested columns from all records that match the search criterion. If you don’t specify the search criterion, you’ll get all records, which may be way more than you want:
SELECT empname,salary FROM employee WHERE empname="John Smith";
+---+---+
➾
| empname | salary |
➾
Chapter 4. Working with Databases
•
52+---+---+
You can enhance selection by sorting, grouping, aggregating, and filtering the results. To sort the results, use the ORDER BY modifier (sorting by multiple columns in either DESCending or ASCending order is possible):
SELECT * FROM employee WHERE hired>='2000-01-01' ORDER BY salary DESC;
+----+---+---+---+---+
To group and aggregate the results, use the GROUP BY modifier and an aggrega-tion funcaggrega-tion, such as COUNT(), MIN(), MAX(), SUM(), or AVG():
SELECT (hired>'2001-01-01') AS Recent,AVG(salary) FROM employee
GROUP BY (hired>'2001-01-01');
+---+---+
➾
| Recent | AVG(salary) |
➾
+---+---+
➾
| 0 | 0.009999999776482582 |
➾
| 1 | 41666.666666666664 |
➾
+---+---+
➾
2 rows in set (0.00 sec)
➾
The latter statement calculates and reports the average salary of each group of employees based on whether they were hired before or after 01/01/2001, as well as the hiring range itself.
The keywords WHERE and HAVING filter the selection results; the server executes WHERE before grouping and HAVING after grouping.
SELECT AVG(salary),MIN(hired),MAX(hired) FROM employee GROUP BY YEAR(hired)
HAVING MIN(hired)>'2001-01-01';
+---+---+---+
➾
| AVG(salary) | MIN(hired) | MAX(hired) |
➾
+---+---+---+
➾
| 44500 | 2011-01-01 | 2011-11-11 |
➾
| 36000 | 2016-01-09 | 2016-01-09 |
➾
+---+---+---+
➾
2 rows in set (0.00 sec)
➾
This statement calculates and reports the average salary and the earliest and latest hiring dates for each group hired on the same year after 01/01/2001.
Join
The join operation combines the contents of two tables based on one or more columns. MySQL supports five types of joins: inner (with a flavor called straight join), left, right, outer, and natural. The latter can also be left or right. The inner join returns rows with at least one match in both tables. Left/right joins all rows from the left/right table, respectively, even if there is no match on the other side. Outer returns rows with a match in either table. If one table doesn’t have a match, the server returns a NULL instead. A natural join behaves like outer, except that it implicitly involves all columns with the same names.
The following commands create a new table with employee positions, add an index to the column that will be used for joining, and extract employee names and positions from both tables (the syntax in the latter example is for an implicit inner join):
-- Prepare and populate another table
CREATE TABLE position (eid INT, description TEXT);
INSERT INTO position (eid,description) VALUES (6,'Imposter'), (1,'Accountant'),(4,'Programmer'),(5,'President');
ALTER TABLE position ADD INDEX(eid);
-- Fetch the joined data
SELECT employee.empname,position.description
FROM employee,position WHERE employee.id=position.eid ORDER BY position.description;
+---+---+
➾
| empname | description |
➾
+---+---+
➾
| John Smith | Accountant |
➾
| Anon I. Muss | Imposter |
➾
| Abe Lincoln | President |
➾
Chapter 4. Working with Databases