Question

MySQL支持五种基本的数据库操作：插入、删除、变更、选择和连接。这些操作可以完成数据库表的填充，以及修改和检索现有数据。这些操作通常包含在数据分析程序中，但为了建立对它们的认识，我们将首先在mysql命令行提示符下练习它们。

插入

插入是最重要的命令，我们就从它开始介绍。下面我们将插入一条新的记录到一张表中，并重复这一操作，直到所有的观测值都被记录到表中：

   INSERT INTO employee VALUES(NULL,NULL,"John Smith",35000,NOW());

➾ Query OK, 1 row affected, 1 warning (0.18 sec)

前两个NULL是索引和时间戳的占位符值，服务器会自动算出它们的值。函数NOW()返回当前日期和时间，但是只有“日期”部分会写入记录中。注意，查询产生了一个警告，该警告的来源就是对后者（“时间”）的截断。下面的代码用于查看最近的警告和错误的文字描述：

   SHOW WARNINGS;

➾ +-------+------+--------------------------------------------+
➾ | Level | Code | Message                                    |
➾ +-------+------+--------------------------------------------+
➾ | Note  | 1265 | Data truncated for column 'hired' at row 1 |
➾ +-------+------+--------------------------------------------+
➾ 1 row in set (0.00 sec)

如果插入操作违反了UNIQUE约束，服务器就会中止操作，除非使用了IGNORE关键字。在这种情况下，插入操作失败：

   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)

可以手动插入更多的行，不过首选方法是用Python来完成其余的插入。

删除

删除操作从表中删除所有与搜索条件匹配的记录。如果没有指定搜索条件，服务器就会删除所有记录：

-- 如果John Smith是一个低收入者，就将他的记录删除
DELETE FROM employee WHERE salaryAND empname="John Smith";
-- 删除所有记录
DELETE FROM employee;

如果只想删除某条特定的记录，则应使用其唯一主键或任何其他的具有唯一性标识的条件：

DELETE FROM employee WHERE id=387513;

需要强调的是，任何时候都要记住，删除操作是不可撤销的！

变更

变更操作会找到与搜索条件相匹配的记录，更新其指定列的值。如果未指定搜索条件，则变更操作将影响所有记录：

   -- 重置最近招入的员工工资
   UPDATE employee SET salary=35000 WHERE hired=CURDATE();
   -- 再次增加John Smith的工资
   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

想必你已经猜到了：变更也是不可撤销的。实际上，它跟删除操作一样，是一个破坏性的操作。

选择

选择操作在与搜索条件匹配的所有记录中选出所有请求的列。如果未指定搜索条件，则将获得所有记录；这种操作得到的数据记录的数量可能远超出你的需求。

   SELECT empname,salary FROM employee WHERE empname="John Smith";

➾ +------------+--------+
➾ | empname    | salary |
➾ +------------+--------+
➾ | John Smith | 36000  |
➾ +------------+--------+
➾ 1 row in set (0.00 sec)

   SELECT empname,salary FROM employee;

➾ +--------------+--------+
➾ | empname      | salary |
➾ +--------------+--------+
➾ | John Smith   | 36000  |
➾ | Jane Doe     | 75000  |
➾ | Abe Lincoln  | 0.01   |
➾ | Anon I. Muss | 14000  |
➾ +--------------+--------+
➾ 4 rows in set (0.00 sec)

可以通过对结果进行排序、分组、聚合和过滤来增强选择的功能。若想对选择结果排序，需要使用ORDER BY修饰符（可实现多列数据的降序排列或升序排列）：

   SELECT * FROM employee WHERE hired>='2000-01-01' ORDER BY salary DESC;

➾ +----+---------------------+--------------+--------+------------+
➾ | id | updated             | empname      | salary | hired      |
➾ +----+---------------------+--------------+--------+------------+
➾ | 4  | 2016-01-09 17:35:11 | Jane Doe     | 75000  | 2011-11-11 |
➾ | 1  | 2016-01-09 17:31:29 | John Smith   | 36000  | 2016-01-09 |
➾ | 6  | 2016-01-09 17:55:24 | Anon I. Muss | 14000  | 2011-01-01 |
➾ +----+---------------------+--------------+--------+------------+
➾ 3 rows in set (0.01 sec)

若想对选择结果进行分组和聚合，需要使用GROUP BY修饰符和聚合函数（比如COUNT()、MIN()、MAX()、SUM()或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)

后一个语句计算并给出每组员工的平均工资，分组的依据是员工的招聘时间在2001年1月1日之前还是之后，也就是招聘时间本身。

使用WHERE和HAVING关键字可以对选择结果进行过滤。服务器在分组之前执行WHERE命令，并在分组之后执行HAVING命令。

   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)

这条语句计算并给出2001年1月1日后同一年度内招聘员工的平均工资，同时给出每个年度最早的和最新的员工招聘时间。

连接

连接操作基于一列或多列，完成两个表的合并。MySQL支持五种类型的连接：内连接（具有所谓的直接连接的含义）、左连接、右连接、外连接和自然连接。后者也可以是左连接或右连接。当两个表中存在至少一个匹配时，内连接返回匹配的行。左连接/右连接分别连接左/右表中的所有行，即使在另一侧没有匹配，也能实现连接。外连接返回在任意表中具有匹配的行。如果一个表没有匹配，则服务器返回NULL。自然连接的行为类似于外连接，不同之处在于它隐式涉及名称相同的所有列。

以下命令创建包含员工职位的新表，然后为准备用于连接的列添加索引，最后从两个表中提取员工姓名和职位（后面的操作使用了隐式内连接的语法）：

   -- 准备并填充另一张表
   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);

   -- 获取连接后的数据
   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   |
➾ | Jane Doe     | Programmer  |
➾ +--------------+-------------+
➾ 4 rows in set (0.00 sec)