9.5.1插入数据

9.5 管理和查询数据

在创建表时，定义了表的结构，但表中并没有数据。本节介绍如何插入数据、修改数据、删除数据和查询表中的数据。

9.5.1 插入数据

可以使用phpMyAdmin工具在图形界面中插入数据，也可以使用INSERT语句插入数据。

1．使用phpMyAdmin工具插入数据

在phpMyAdmin的数据库管理页面中，单击要插入数据的表后面的“插入”超链接，打开插入数据页面，如图9-26所示。

在每个字段后面的“值”文本框中添加该字段的值，然后单击“执行”按钮，可以将输入的记录添加到表中。因为表Departments的DepId字段被设置了auto_increment属性，系统会自动为此分配值，所以在输入数据时只需要使用DepName字段的值即可。

【例9-17】 向表Departments中插入如表9-8所示的数据。

这些记录将在9.5.4小节中介绍查询数据时使用到。

插入完成后，在数据库管理页面中单击表 Departments 后面的“浏览”超链接，可以打开浏览表数据的页面，如图9-27所示。

2．使用INSERT语句插入数据

INSERT语句的基本使用方法如下：

INSERT INTO 表名 (列名1, 列名2, …, 列名n)

VALUES (值1, 值2, …, 值n);

列与值必须一一对应。

【例9-18】 使用INSERT语句在表Departments中添加一列数据，列Dep_name的值为“人事部”，具体语句如下：

INSERT INTO Departments (Dep_name)

VALUES ('人事部')

因为DepId字段被设置了auto_increment属性，所以不需要指定它的值。

【例9-19】 参照表9-9向表Employees中插入数据。

INSERT语句如下：

INSERT INTO Employees (EmpName, DepId, Title, Salary) VALUES('张三', 1, '部门经理', 6000);

INSERT INTO Employees (EmpName, DepId, Title, Salary) VALUES('李四', 1, '职员', 3000);

INSERT INTO Employees (EmpName, DepId, Title, Salary) VALUES('王五', 1, '职员', 3500);

INSERT INTO Employees (EmpName, DepId, Title, Salary) VALUES('赵六', 2, '部门经理',6500);

INSERT INTO Employees (EmpName, DepId, Title, Salary) VALUES('高七', 2, '职员', 2500);

INSERT INTO Employees (EmpName, DepId, Title, Salary) VALUES('马八', 2, '职员', 3100);

INSERT INTO Employees (EmpName, DepId, Title, Salary) VALUES('钱九', 3, '部门经理',5000);

INSERT INTO Employees (EmpName, DepId, Title, Salary) VALUES('孙十', 3, '职员', 2800);

这些记录将在9.5.4小节中介绍查询数据时使用到。可以在MySQL命令行工具中执行此SQL语句，执行前使用USE命令将当前数据库切换为MySQLDB。

9.5.2 修改数据

在phpMyAdmin的数据库管理页面中，可以查看到数据库中包含表的基本信息。单击表后面的“浏览”超链接，可以查看表中的数据，如图9-28所示。

单击每条记录前面的“编辑”超链接，可以打开修改记录的页面，如图9-29所示。

可以通过修改字段后面文本框中的内容来修改字段的值。修改完成后，单击“执行”按钮，返回浏览表数据的页面。

也可以使用UPDATE语句修改表中的数据。UPDATE语句的基本使用方法如下：

UPDATE 表名 SET 列名1 =值1, 列名2 =值2, …, 列名n =值n

WHERE 更新条件表达式

当执行UPDATE语句时，指定表中所有满足WHERE子句条件的行都将被更新，列1的值被设置为值1，列2的值被设置为值2，列n的值被设置为值n。如果没有指定WHERE子句，则表中所有的行都将被更新。

更新条件表达式实际上是一个逻辑表达式，通常需要使用到关系运算符和逻辑运算符，返回True或者False。

MySQL的常用关系运算符和比较函数如表9-10所示。

MySQL的逻辑运算符如表9-11所示。

【例9-20】 在表Employees中，将张三的工资修改为6500元，可以使用下面的SQL语句：

UPDATE Employees SET Salary=6500 WHERE EmpName='张三'

也可以通过设置WHERE子句批量修改表中的数据。

【例9-21】 对所有职务为部门经理的员工的工资增加100元，可以使用下面的SQL语句：

UPDATE Employees SET Salary=Salary+100 WHERE Title ='部门经理'

可以同时修改多个字段的值，字段使用逗号分隔。

【例9-22】 将张三的职务修改为职员，将其工资修改为3000元，代码如下：

UPDATE Employees SET Title='职员', Salary=3000 WHERE EmpName='张三'

9.5.3 删除数据

在浏览表数据的页面中，单击记录前面的删除超链接可以删除指定的记录，如图9-30所示。

选择“删除”图标后，将弹出确认删除对话框，如图9-31所示。单击“确定”按钮，可以删除选择的记录。

可以使用DELETE语句删除表中的数据，基本使用方法如下。

DELETE 表名WHERE 删除条件表达式

当执行DELETE语句时，指定表中所有满足WHERE子句条件的行都将被删除。

【例9-23】 删除表Departments中列DepName等于“abc”的数据，可以使用以下SQL语句：

DELETE FROM Departments WHERE Dep_Name = 'abc';

9.5.4 在phpMyAdmin中查询数据

前面已经介绍了在phpMyAdmin中浏览表中所有数据的方法。用户也可以通过本节介绍的方法，查询指定条件的记录。

在数据库管理页面中，单击“搜索”超链接，可以打开搜索数据页面，如图9-32所示。

在搜索文本框中输入要搜索的文字，然后选择查找的方式和查找的表。例如，在表employees中查找包含张三的记录，单击“执行”按钮，查询结果如图9-33所示。

在返回结果页面中提示用户找到了一个匹配项。单击后面的“浏览”超链接，可以查看搜索到的记录，如图9-34所示。

这只是简单的对字符串进行搜索，还可以设置更复杂的查询条件。

在数据库管理页面中，单击“搜索”超链接后面的“查询”超链接，打开设置查询条件的页面，如图9-35所示。

在“字段”组合框中，可以选择指定表中的所有字段；在“排序”组合框中可以选择是否按此字段排序以及排序的类型；在“显示”行中，可以选择是否显示指定的字段；在“条件”行中，可以设置指定字段的查询条件。如果存在多个查询条件，可以使用查询条件之间的关系（包括与和或的关系），然后在下面的多行文本框中输入其他查询条件。

默认情况下，页面中只包含3个字段。如果需要更多或更少的字段，可以在“添加/删除字段”组合框中选择需要添加和删除字段的数量，然后单击后面的“更新查询”按钮，即可实现添加和删除字段的功能。

在本实例中，为了查询工资大于3000元的员工信息，选择了3个字段，即Employees.EmpName、Employees.Title和Employees.Salary。为了实现按工资数额的降序排列，在Employees.Salary字段下面的“排序”组合框中选择“递减”。在所有字段下面的显示行中，选中对应的复选框。在Employees.Salary 字段下面的“条件”文本框中，输入“>3000”。配置完成后，单击“更新查询”按钮，在页面左下角的SQL语句文本框中会生成对应的SELECT语句。单击页面右下部的“提交查询”按钮，打开显示查询结果的页面，如图9-36所示。

可以看到，在表格中显示了所有工资大于3000元的员工信息，记录按工资的降序排列。

9.5.5 使用SELECT语句查询数据

SELECT语句是最常用的SQL语句之一。使用SELECT语句可以进行数据查询，它的基本使用方法如下：

SELECT子句

[ INTO 子句 ]

FROM 子句

[ WHERE 子句 ]

[ GROUP BY 子句]

[ HAVING 子句 ]

[ ORDER BY 子句 ]

[UNION 运算符]

各子句的主要功能说明如下。

• SELECT子句：指定查询结果集的列组成，列表中的列可以来自一个或多个表或视图。

• INTO子句：将查询结果集中的数据保存到一个文件中。

• FROM子句：指定要查询的一个或多个表或视图。

• WHERE子句：指定查询的条件。

• GROUP BY子句：对查询结果进行分组统计。

• HAVING子句：指定分组或集合的查询条件。

• ORDER BY子句：指定查询结果集的排列顺序。

• UNION运算符：将多个SELECT语句连接在一起，得到的结果集是所有SELECT语句的结果集的并集。

【例9-24】 下面是一个比较简单的 SELECT 语句，它的功能是查看表 Departments 中所有记录的部门名称。

SELECT DepName FROM Departments;

在phpMyAdmin中执行此脚本，查询结果如图9-37所示。

1．显示唯一数据

在SELECT子句中可以使用DISTINCT关键字指定不重复显示指定列值相同的行。

【例9-25】 使用下面语句查看所有的员工职务情况。

SELECT Title FROM Employees;

运行结果如图9-38所示。

结果集中有很多重复数据。使用DISTINCT关键字过滤重复数据的SELECT语句如下：

SELECT DISTINCT Title FROM Employees;

运行结果如图9-39所示。

可以看到，重复的数据已经被过滤掉。

2．显示列标题

在上面的实例中，结果集中列的标题部分都是显示列名，可以使用 AS 子句，设置自己需要的显示标题。

【例9-26】 查询员工姓名和职务，显示中文列名。

SELECT EmpName As 姓名, Title As 职务 FROM Employees;

运行结果如图9-40所示，这样的结果看起来更加直观。也可以省略掉AS关键字，代码如下：

SELECT EmpName 姓名, Title 职务 FROM Employees;

返回的结果是一样的。

3．设置查询条件

可以在WHERE子句中指定返回结果集的查询条件。

【例9-27】 要查询部门编号为1的员工信息，可以使用下面的SELECT语句。

SELECT EmpName As 姓名, Title As 职务 FROM Employees WHERE DepId = 1;

查询结果如图9-41所示。

可以在WHERE子句中使用=、>、<等比较运算符，也可以使用LIKE关键字和通配符%。通配符%表示任意字符串。

【例9-28】 要查询所有姓李的员工可以使用以下语句：

SELECT EmpName As 姓名, Title As 职务 FROM Employees

WHERE EmpName LIKE '李%';

返回的结果如图9-42所示。

4．对结果集进行排序

在SELECT语句中使用ORDER BY子句可以对结果集进行排序。

【例9-29】 要按照工资升序显示员工信息，可以使用以下命令：

SELECT EmpName As 姓名, Title As 职务, Salary AS 工资 FROM Employees

ORDER BY Salary;

查询结果如图9-43所示。默认情况下，数据库会按照指定字段的升序排列。

如果需要按照降序显示，可以在ORDER BY子句中使用DESC关键字，例如：

SELECT EmpName As 姓名, Title As 职务, Salary AS 工资 FROM Employees ORDER BY Salary DESC;

查询结果如图9-44所示。

5．使用统计函数

可以在SELECT语句中使用统计函数，对指定的列进行统计。常用的统计函数包括COUNT、AVG、SUM、MAX、MIN等。

（1）使用COUNT()函数

COUNT()函数用于统计记录数量。

【例9-30】 可以使用下面的SELECT语句统计所有员工的数量：

SELECT COUNT(*) AS 员工数量 FROM Employees;

查询结果为8。

（2）使用AVG()函数

AVG()函数用于统计指定列的平均值。

【例9-31】 可以使用下面的SELECT语句统计所有员工的平均工资：

SELECT AVG(Salary) FROM Employees;

查询结果为4050.0000。

（3）使用SUM()函数

SUM()函数用于统计指定列的累加值。

【例9-32】 可以使用下面的SELECT语句统计所有员工的工资之和：

SELECT SUM(Salary) FROM Employees;

查询结果为32400。

（4）使用MAX()函数

MAX()函数用于统计指定列的最大值。

【例9-33】 可以使用下面的SELECT语句统计所有员工中的最高工资的数额：

SELECT MAX(Salary) FROM Employees;

查询结果为6500。

（5）使用MIN()函数

MIN()函数用于统计指定列的最小值。

【例9-34】 可以使用下面的SELECT语句统计所有员工中的最低工资的数额：

SELECT MIN(Salary) FROM Employees;

查询结果为2500。

6．分组统计

在对结果集进行统计时，有时需要将结果集分组，计算每组数据的统计信息。可以使用GROUP BY子句实现此功能。

【例9-35】 要统计不同职务的平均工资，可以使用以下SQL语句：

SELECT Title AS 职务, AVG(Salary) AS 平均工资 FROMEmployees

GROUP BY Title;

运行结果如图9-45 所示。GROUP BY 可以和WHERE 子句结合使用。

【例9-36】 要统计编号为2的部门中各职务的平均工资，

可以使用下面的SQL语句。

SELECT Title AS 职务, AVG(Salary) AS 平均工资 FROM Employees

WHERE DepId = 2

GROUP BY Title;

运行结果如图9-46所示。

但是，WHERE 子句中不能包含聚合函数。例如，要统计平均工资大于 4000 元的职务类型，使用下面的SELECT语句：

SELECT Title AS 职务, AVG(Salary) AS 平均工资FROMEmployees

WHERE AVG(Salary) > 4000

GROUP BY Title;

运行上面的语句，会返回如下错误信息：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Employees WHERE AVG(Salary) > 4000 GROUP BY Title' at line 1

在这种情况下，可以使用HAVING子句指定搜索条件。上面的语句可以改写为：

SELECT Title AS 职务, AVG(Salary) AS 平均工资 FROM Employees

GROUP BY Title

HAVING AVG(Salary) > 4000;

执行结果如图9-47所示。

可以看到，只有部门经理的平均工资大于4000元，为5833.3333。

7．连接查询

如果SELECT语句需要从多个表中提取数据，则这种查询可以称为连接查询，因为在WHERE子句中需要设置每个表之间的连接关系。

【例9-37】 在查询员工信息时显示所属部门的名称，可以使用下面的SQL语句：

SELECT e.EmpName AS 姓名, e.Title AS 职务, d.DepName As 部门

FROM Employees e, Departments d

WHERE e.DepId=d.DepId;

运行结果如图9-48所示。

在上面的 SELECT 语句中涉及两个表：表 Employees 和表Departments。在FROM子句中，为每个表指定一个别名，表Employees的别名为e，表Departments的别名为d。在SELECT子句中，可以使用别名标记列所属的表。在WHERE子句中设置两个表的连接条件。

上面的SELECT语句也可以使用内连接的方法实现，代码如下：

SELECT e.EmpName AS 姓名, e.Title AS 职务, d.DepName

FROM Employees e INNER JOIN Departments d

ON e.DepId=d.DepId;

INNER JOIN 关键字表示内连接，内连接指两个表中的数据平等的相互连接，连接的表之间没有主次之分。ON关键字用来指示连接条件。

8．子查询

所谓子查询就是在一个SELECT语句中又嵌套了一个SELECT语句。WHERE子句和HAVING子句可以嵌套SELECT语句。

【例9-38】 要显示人事部的所有员工，但是又不知道财务部的部门编号，可以使用以下命令：

SELECT EmpName FROM Employees WHERE DepId =

(SELECT DepId FROM Departments WHERE DepName = '人事部')

运行结果如图9-49所示。