数据库的基本操作
本章目标
- 会使用MySQL语句添加数据
- 会使用MySQL语句修改数据
- 会使用MySQL语句删除数据
1. 实现数据增删改查
1.1 MySQL的存储引擎
存储引擎指定了表的存储类型，即如何存储和索引数据、是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。MySQL支持的存储引擎有InnoDB、MyISAM、MEMORY、MRG_MyISAM、ARCHIVE、FEDERATED、CSV、BLACKHOLE、PERFORMANCE_SCHEMA共9种，可以使用SHOW ENGINES语句查看系统所支持的引擎类型，执行结果如图所示：
1. 常用的存储引擎
常用的存储引擎有InnoDB和MyISAM，功能和特点如下：
| 名称 | MyISAM | InnoDB |
|---|---|---|
| 事务处理 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大,约2倍 |
InnoDB和MyISAM各自的使用场合如下：
- MyISAM存储引擎适用于对不需要事务处理，以访问为主的应用场合
- InnoDB存储引擎使用于需要进行频繁的更新、删除操作，同时还对事务的完整性要求比较高，需要实现并发控制的场合
2. 操作默认存储引擎
MySQL5.5以上版本默认的存储引擎是InnoDB，可以通过以下语句来查看当前默认的存储引擎。
SHOW VARIABLES LIKE '%storage_engine%';
其中，LIKE后要查询的关键字为‘%storage_engine%’，表示查询默认存储引擎，结果如图所示：
3. 指定表的存储引擎
数据表的创建默认使用当前MySQL默认的存储引擎，有时为了达到数据表的特殊功能要求，也可重新设置表的存储类型。语法格式如下：
CREATE TABLE 表名(
#省略代码
)ENGINE=MyISAM
4. MySQL的数据文件
（1）数据文件的存储位置
MySQL的数据文件存储的位置，可通过配置my.ini中参数datadir获取或者修改，如图所示：
（2）MyISAM类型的表文件
存储引擎是myisam， 在data目录下会看到3类文件：.frm、.myi、.myd
-
.frm–表定义，是描述表结构的文件。
-
.MYD–"D"数据信息文件，是表的数据文件。
-
.MYI–"I"索引信息文件，是表数据文件中任何索引的数据树
（3）InnoDB类型的表文件
存储引擎是InnoDB，在data目录下会看到2类文件：.frm、.ibd
-
.frm–表结构的文件。
-
.ibd–表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。
1.2 插入数据记录
添加数据有两种方式：
- 在SQLyog中插入数据。这种方式比较简单，只需要右击表，在弹出的快捷菜单中打开表，就可以像表中直接输入数据行
- 使用SQL可以向表中添加新数据，也可以将现有表中的数据添加到新创建的表中
1. 插入单行数据
语法格式如下：
INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( '值1', '值2', '值3', …)
其中：
- 表的字段是可选的，如果省略，则依次插入所有的字段
- 多个列表和多个值之间用逗号分隔
- 值列表必须和字段名列表相同，且数据类型相符
- 如果插入的是表中部分列的数据，字段名列表必须填写
2. 插入多行数据
MySQL中的INSERT语句支持一次插入多条记录，插入时可以指定多个值列表，每个值列表之间用逗号分隔
语法格式如下：
INSERT INTO 表名 [ ( 字段1, 字段2, 字段3, … ) ] VALUES ( '值1', '值2', '值3', …),( '值1', '值2', '值3', …),......;
例如，一次向student表中插入3条数据，SQL语句如下：
insert into student
(StudentNo,StudentName,Sex,GradeId,Phone,Address,Email,IdentityCard)
values
(1111,"郭靖",1,1,"13500000001","北京海淀区中关村大街1号","guojing@bdqn.cn","450323198612111000"),
(1112,"李文才",1,2,"13500000002","河南洛阳","liwencai@bdqn.cn","450323198512311000"),
(1113,"李梅",2,3,"13500000015","上海卢湾区","limei@bdqn.cn","450323198412311000");
3. 将查询结果插入到新表中
语法格式如下：
CREATE TABLE 新表 (SELECT 字段1,字段 2,...... FROM 原表);
例如，将student表中的studentName、phone字段数据保存到新表phoneList中，SQL语句如下：
CREATE TABLE phoneList (SELECT studentName,phone FROM student);
1.3 更新数据记录
数据更新是经常发生的事情，使用SQL更新表中某行数据的语法格式如下：
UPDATE 表名
SET column_name = value [ , column_name2 = value2, …. ] [ WHERE condition ];
其中：
- column_name 为要更改的数据列
- value 为修改后的数据，可以为变量、具体值、表达式或者嵌套的SELECT结果
- condition为筛选条件，如不指定则修改该表的所有列数据
1.4 删除数据记录
1. 使用DELETE删除数据
使用SQL语句删除表中的数据，语法格式如下：
DELETE FROM 表名 [ WHERE condition ];
其中
- ncondition为筛选条件，如不指定则删除该表的所有列数据
注意：
DELETE语句删除的是整条记录，不会只删除单个列，所以在DELETE后不能出现列名。
2. 使用TRUNCATE TABLE删除数据
TRUNCATE TABLE用来删除表中的所有行，功能上类似于没有WHERE子句的DELETE语句的。
例如：要删除学生信息表中的所有记录行，可以使用以下语句
TRUNCATE TABLE student;
但TRUNCATE TABLE比DELETE执行速度快，使用的系统资源和事务日志资源更小，并且删除数据表后的标识列重新开始。
注意：
TRUNCATE TABLE会删除表中的所有行，但表的结构、列、约束、索引等不会被改动。TRUNCATE TABLE不能用于有外键约束引用的表，这种情况下，需要使用DELETE语句。
实际工作中，不建议使用TRUNCATE TABLE语句，因为使用它删除数据不能恢复。
1.5 数据查询语句
1. 使用SELECT语句进行查询
查询数据使用SELECT语句，最简单的查询语句的语法格式如下：
SELECT <列名|表达式|函数|常量>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名> [ASC或DESC]]
其中，WHERE条件是可选的，若不限制，则查询所有返回行的数据。ORDER BY 用来排序，将会在后续的章节进行详细介绍。
注意：
查询语句可以分为多个子句，例如，上面的查询语法可以划分为SELECT…FROM…WHERE…ORDER BY四个子句，对于复杂的SQL语句，可以将每个子句单独写成一行，以方便调试和查找错误。
（1）查询所有的数据行和列
把表中的所有行和列都列举出来比较简单，这时候可以使用*表示所有的列，例如：
SELECT * FROM student;
（2）查询部分行和列
查询部分列需要列举不同的列名，而查询部分行需要使用WHERE子句进行条件限制，例如：
SELECT studentNo,studentName,address FROM student WHERE address='河南新乡';
以上的查询语句只查询地址为“河南新乡”的学生，并且只显示学生编号、姓名和地址列。同理，以下语句用来查询地址不是“河南新乡”的学生信息。
SELECT studentNo,studentName,address FROM student WHERE address <> '河南新乡';
（3）在查询中使用列的别名
AS子句可以用来改变结果集中列的名称，也可以为组合或者计算出的列指定名称，还有一种情况是让标题列的信息更易懂，例如，查询studentNo列并将列名显示为“学生编号”。
SELECT studentNo AS 学生编号,studentName AS 学生姓名,address AS 学生地址
FROM student
WHERE address <> '河南新乡';
为通过计算计算、合并得到的新列取别名。例如，假设在某数据库的雇员表employee中存在firstName列和lastName列，现在需要将这两列合并成一个叫作“姓名”的列，可以使用以下的查询语句：
SELECT firstName+"."+lastName AS 姓名 FROM employee;
（4）查询空值
在SQL语句中采用"IS NULL"或者“IS NOT NULL”来判断列值是否为空，因此，如果要查询学生信息表中没有填写Email信息的学生，可以使用以下查询语句。
SELECT studentName FROM student WHERE email IS NULL;
（5）在查询中使用常量列
有时候，需要将一些常量的默认信息添加到查询输出结果中，以方便统计或计算，例如，查询学生信息的时候，学校名称统一都是"北京新乡桥"，查询语句如下：
SELECT studentNo AS 学生编号,studentName AS 学生姓名,address AS 学生地址,'北京新乡桥' AS 学校名称
FROM student
WHERE address <> '河南新乡';
2. 常用函数
MySQL中的函数将一些常用的处理数据的操作封装起来，这样大大简化了程序员的工作，提高了开发效率。
MySQL常用的函数如下：
（1）聚合函数
聚合函数用来对已有数据进行汇总，如求和、平均值、最大值、最小值等。
| 函数名称 | 描述 |
|---|---|
| COUNT( ) | 返回满足SELECT条件的记录总和数，如 SELECT COUNT(*)… |
| SUM( ) | 返回数字字段或表达式列作统计，返回一列的总和 |
| AVG( ) | 通常为数值字段或表达列作统计，返回一列的平均值 |
| MAX( ) | 可以为数值字段、字符字段或表达式列作统计，返回最大的值 |
| MIN( ) | 可以为数值字段、字符字段或表达式列作统计，返回最小的值 |
（2）字符串函数
字符串函数用来对字符串进行各种处理，MySQL中使用频率较高的字符串函数如表所示：
| 函 数 名 | 作 用 | 举 例 |
|---|---|---|
| CONCAT(str1, str1…strn) | 字符串连接 | SELECT CONCAT(‘My’,‘S’,‘QL’); 返回：MySQL |
| INSERT(str, pos,len, newstr) | 字符串替换 | SELECT INSERT( ‘这是SQL Server数据库’, 3,10,‘MySQL’); 返回：这是MySQL数据库 |
| LOWER(str) | 将字符串转为小写 | SELECT LOWER(‘MySQL’); 返回：mysql |
| UPPER(str) | 将字符串转为大写 | SELECT UPPER(‘MySQL’); 返回：MYSQL |
| SUBSTRING (str,num,len) | 字符串截取 | SELECT SUBSTRING( ‘JavaMySQLOracle’,5,5); 返回：MySQL |
（3）时间日期函数
常用的时间日期函数如下：
| 函数名 | 作用 | 举例（结果与当前时间有关） |
|---|---|---|
| CURDATE() | 获取当前日期 | SELECT CURDATE(); 返回：2016-08-08 |
| CURTIME() | 获取当前时间 | SELECT CURTIME(); 返回：19:19:26 |
| NOW() | 获取当前日期和时间 | SELECT NOW(); 返回：2016-08-08 19:19:26 |
| WEEK(date) | 返回日期date为一年中的第几周 | SELECT WEEK(NOW()); 返回：26 |
| YEAR(date) | 返回日期date的年份 | SELECT YEAR(NOW()); 返回：2016 |
| HOUR(time) | 返回时间time的小时值 | SELECT HOUR(NOW()); 返回：9 |
| MINUTE(time) | 返回时间time的分钟值 | SELECT MINUTE(NOW()); 返回：43 |
| DATEDIFF(date1,date2) | 返回日期参数date1和date2之间相隔的天数 | SELECT DATEDIFF(NOW(), ‘2008-8-8’); 返回：2881 |
| ADDDATE(date,n) | 计算日期参数date加上n天后的日期 | SELECT ADDDATE(NOW(),5); 返回：2016-09-02 09:37:07 |
| DATE_FORMAT(date,format) | 用于以不同的格式显示日期/时间数据。 | SELECT DATE_FORMAT(NOW(),’%y-%M-%d’);返回：2016-09-02 |
（4）数学函数
常用的数学函数如下：
| 函数名 | 作 用 | 举 例 |
|---|---|---|
| CEIL(x) | 返回大于或等于数值x的最小整数 | SELECT CEIL(2.3) 返回：3 |
| FLOOR(x) | 返回小于或等于数值x的最大整数 | SELECT FLOOR(2.3) 返回：2 |
| RAND() | 返回0~1间的随机数 | SELECT RAND() 返回：0.5525468583708134 |
3. ORDER BY 子句
如果需要按照一定顺序排列结果，则需要使用ORDER BY 子句，并且排序可以是升序（ASC）或者降序（DESC）。如果不指定ASC或者DESC，结果集默认按ASC升序排序。
4. LIMIT 子句
LIMIT子句要求显示指定位置指定行数的记录。
语法格式如下：
SELECT [ALL | DISTINCT]
{ * | table.* | [ table.field1 [ as alias1] [,table.field2 [as alias2]][,…]] }
FROM table_name [ as table_ alias ]
[left|out|inner join table_name2] #连接查询或者联合查询
[ WHERE … ] #指定结果需满足的条件
[ GROUP BY …] #指定结果按照哪几个字段来分组
[HAVING …] #过滤分组的记录必须满足的次要条件
[ ORDER BY… ] #指定查询记录按一个或者多个条件排序
[ LIMIT [位置偏移量],行数] ; #指定查询的记录从哪条至哪条
上述语法中的LIMIT部分介绍如下：
- 位置偏移量是指从结果集中第几条数据开始显示（第1条记录的位置偏移量是0，第2条记录的位置偏移量是1…），此参数可选，当省略时默认从第1条记录（即参数是0）开始显示。如果是分页，则是（当前页-1）
*每页显示记录数 - 行数指定显示记录的条数
分页查询需求：
查询所有《数据库结构-2》的考试成绩，并按照由高到低显示，同时把该成绩对应的学生的学号、姓名打印出来
要求：
- 第一次查询前5条记录
- 第二次查询6-10条记录
代码如下：
SELECT s.studentno,s.studentname,c.subjectname,r.studentresult
FROM student s JOIN result r
ON s.StudentNo=r.StudentNo
JOIN SUBJECT c
ON r.SubjectNo=c.SubjectNo
WHERE c.subjectname="数据库结构-2"
ORDER BY r.StudentResult DESC LIMIT 0,5; #limit 5,5
2. 查询考试成绩
2.1 什么是子查询
学生表数据如下：
问题：编写SQL语句，查看年龄比“李斯文”小的学生，要求显示这些学生的信息
分析：
- 第一步：查询得到“李斯文”的出生日期
- 第二步：利用WHERE语句，筛选出生日期比“李斯文”大的学生
实现方法1：
分两步实现：
-
查找出“李斯文”的出生日期，语句如下：
SELECT `bornDate` FROM `student` WHERE `studentName` = '李斯文';如图所示：
-
利用WHERE语句筛选出生日期比“李斯文”大的学生，语句如下：
SELECT `studentNo`, `studentName`,`sex`,`bornDate`,`address` FROM `student` WHERE bornDate > '1993-07-23';
实现方法2：
采用子查询
子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询
子查询在WHERE语句中的一般用法如下：
SELECT … FROM 表1 WHERE 字段1 比较运算符(子查询);
语句如下：
SELECT `studentNo`,`studentName`,`sex`,`bornDate`,`address`
FROM `student`
WHERE `bornDate` >
(SELECT `bornDate` FROM `student` WHERE `studentName`='李斯文');
注意：
- 将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个
2.2 使用子查询替换表连接
问题：查询“Logic Java”课程至少一次考试刚好等于60分的学生名单
学生表和成绩表的数据分别如下：
实现方法1：
使用表连接，语句如下：
SELECT `studentName`
FROM `student` stu
INNER JOIN `result` r ON stu.studentNO = r.studentNo
INNER JOIN `subject` sub ON r.subjectNo = sub.subjectNo
WHERE `studentResult` = 60 AND `subjectName` = 'Logic Java';
实现方法2：
采用子查询
分析：
-
查询subject表，获得Logic Java课程的课程编号，并根据课程编号，查询result表中成绩是60分的学生的学号，使用表连接实现，语句如下：
SELECT `studentNo` FROM `result` INNER JOIN `Subject` ON result.subjectNo= subject.subjectNo WHERE `studentResult`=60 AND `subjectName`='Logic Java'结果如图所示：
-
根据学号，查询student表得到学生姓名
SELECT `studentName` FROM `student` WHERE `studentNo` = 1002;
完整语句如下：
SELECT `studentName`
FROM `student`
WHERE `studentNo` = (
SELECT `studentNo` FROM `result`
INNER JOIN `Subject` ON result.subjectNo= subject.subjectNo
WHERE `studentResult`=60 AND `subjectName`='Logic Java'
);
2.3 查询指定学生的考试成绩
需求说明：
- 查询参加最近一次Logic Java考试成绩的学生的最高分和最低分
实现思路：
-
查询获得“Logic Java”课程的课程编号
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java' -
查询获得“Logic Java”课程最近一次的考试日期
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo`=( SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java' ) -
根据课程编号查询考试成绩的最高分和最低分
SELECT MAX(`studentResult`) AS 最高分, MIN(`studentResult`) AS 最低分 FROM `result`
完整语句如下：
SELECT MAX(`studentResult`) AS 最高分,
MIN(`studentResult`) AS 最低分
FROM `result`
WHERE `subjectNo`=(
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java' )
AND `examDate`=(
SELECT MAX(`examDate`) FROM `result`
WHERE `subjectNo`=(
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'
) );
3. 查询课程
3.1 IN子查询
使用IN关键字可以使父查询匹配子查询返回的多个单字段值
问题1：
查询“Logic Java”课程考试成绩为60分的学生名单
语句如图所示：
错误信息：子查询返回不止一个值时，使用比较运算符会出错
解决方法：采用IN查询
SELECT `studentName`
FROM `student`
WHERE `studentNo` IN(
SELECT `studentNo`
FROM `result`
WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java'
)
AND `studentResult` = 60
);
问题2：
查询参加“Logic Java”课程最近一次考试的在读学生名单
分析：
-
获得 “Logic Java”课程的课程编号
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'; -
根据课程编号查询得到Logic Java课程最近一次的考试日期
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo`= ( SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java' ); -
根据课程编号和最近一次的考试日期查询学生学生信息
实现这个需要需要查询三个表：课程表subject、成绩表result、学生表student，具体的SQL语句如下所示：
SELECT `studentNo`, `studentName`
FROM `student`
WHERE `studentNo` IN (
SELECT `studentNo` FROM `result`
WHERE `subjectNo` IN (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java'
) AND `examDate` = (
SELECT MAX(`examDate`) FROM `result`
WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java '
)
)
);
3.2 NOT IN子查询
问题1：
查询未参加“Logic Java”课程最近一次考试的在读学生名单
在上个示例代码的IN关键字之前加上否定的NOT即可获得未参加考试的学生名单，示例如下：
/*查询得到未参加Logic Java课程最近一次考试在读学生姓名的功能*/
SELECT `studentNo`, `studentName`
FROM `student`
WHERE `studentNo` NOT IN(
SELECT `studentNo`
FROM `result`
WHERE `subjectNo` = (
# 获得参加Logic Java课程最近一次考试的学生学号
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'
) AND `examDate` = (
# 获得Logic Java课程最近一次的考试日期
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (
# 获得Logic Java课程的课程编号
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='Logic Java')
)
)
AND `gradeID` = (
SELECT `gradeID` FROM `subject`
WHERE `subjectName` = 'Logic Java'
);
问题2：
查询未参加“HTML”课程最近一次考试的在读学生名单
分析：
-
获得 “HTML”课程的课程编号
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='HTML'; -
根据课程编号查询得到HTML课程最近一次的考试日期
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo`= ( SELECT `subjectNo` FROM `subject` WHERE `subjectName`='HTML' ); -
根据课程编号和最近一次的考试日期查询未在其中的学生信息
完整语句如下：
SELECT `studentName`
FROM `student`
WHERE `studentNo` NOT IN (
SELECT `studentNo` FROM `result`
WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='HTML'
)
AND `examDate` = (
SELECT MAX(`examDate`) FROM `result`
WHERE `subjectNo` =(
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='HTML' )
)
)
AND `gradeId` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName`='HTML'
);
3.3 EXISTS和NOT EXISTS子查询
1. EXISTS子查询的语法
EXISTS子查询用来确认后边的查询是否继续进行，返回值是true或false。例如，如果存在数据表temp，则先删除它，然后重新创建。语法格式如下：
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (
… … #省略建表语句
);
除了以上用法之外，EXISTS也可以作为WHERE语句的子查询，其基本语法格式如下：
SELECT …… FROM 表名 WHERE EXISTS (子查询);
EXISTS关键字后面的参数是一个任意的子查询，如果该子查询有返回行，则EXISTS子查询的结果为true，此时再执行外层查询语句。如果子查询没有返回行，则EXISTS子查询的结果为false，此时不再执行外层查询语句
问题：
检查“Logic Java”课程最近一次考试成绩，如果有 80分以上的成绩，显示分数排在前5名的学员学号和分数
分析：
- 采用EXISTS检测是否有学员考试成绩达到80分以上
- 如果有成绩达80分以上者，则使用SELECT语句按成绩从高到低排序，显示前5名学员学号和成绩
解决以上的问题的SQL语句如下所示：
/*检查Logic Java课程最近一次考试。如果有成绩达到80分以上者，则显示分数排在前5名学员的学号和分数*/
SELECT `studentNo` AS 学号,`studentResult` 成绩
FROM `result`
WHERE EXISTS (
# 查询Logic Java最后一次考试成绩大于80的记录
SELECT * FROM `result` WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java'
) AND `examDate` = (
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java'
)
) AND `studentResult` > 80
)
AND `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java'
)
ORDER BY `studentResult` DESC LIMIT 5; # 按成绩降序排序，显示前5名
2. NOT EXISTS子查询
EXISTS和IN一样，同样允许添加NOT关键字实现取反操作，NOT EXISTS表示不存在对应查询条件的记录。
问题：
检查“Logic Java”课程最近一次考试成绩，如果全部未通过考试（60分及格），认为本次考试偏难，计算的该次考试平均分加5分
分析：
所有人都没通过考试，即不存在“考试成绩都大于等于60分”的学生，可以采用NOT EXISTS来检测。SQL语句如下所示：
SELECT AVG(studentresult)+5 AS 平均分
FROM result
WHERE NOT EXISTS (
SELECT * FROM `result`
WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java'
) AND `examDate` = (
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java'
)
) AND `studentResult` > 60
)AND `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java'
) AND `examDate` = (
SELECT MAX(`examDate`) FROM `result`
WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java'
)
);
3.4 子查询注意事项
-
任何允许使用表达式的地方都可以使用子查询
-
嵌套在父查询SELECT语句的子查询可包括
-
SELECT子句
-
FROM子句
-
WHERE子句
-
GROUP BY子句
-
HAVING子句
-
只出现在子查询中而没有出现在父查询中的列不能包含在输出列中
3.5 制作学生成绩单
需求说明：
- 为每个学生制作在校期间每门课程的成绩单，要求每个学生参加每门课程的最后一次考试成绩作为该生本课程的最终成绩
- 成绩单的数据项
- 学生姓名
- 课程所属的年级名称
- 课程名称
- 考试日期
- 考试成绩
实现思路：
- 使用分组查询获得各门课程最近一次考试日期
- 使用连接查询得到各门课程最近一次考试成绩的学生姓名、年级、课程名称，考试日期和考试成绩
参考解决方案：
-
使用分组查询获得各门课程最近一次考试的日期。需要按课程分组，使用GROUP BY子句。语句如下：
SELECT MAX(`examDate`),`subjectNo` FROM `result` GROUP BY `result`.`subjectNo` -
使用连接查询得到各门课程最近一次考试成绩的信息。最终数据来源于学生表、课程表、成绩表，需关联三张表。不同课程最近一期考试日期为多条记录，因此使用IN子查询，SQL语句如下：
SELECT `studentName` 姓名, `subjectName` 课程名称, `examDate` 考试日期, `studentResult` 成绩 FROM `result` r1 INNER JOIN `student` ON r1.`studentNo`=`student`.`studentNo` INNER JOIN `subject` ON `subject`.`subjectNo`=r1.`subjectNo` WHERE r1.`examDate` IN ( SELECT MAX(`examDate`) FROM `result` r2 WHERE r1.`subjectNo` = r2.`subjectNo`#主查询和子查询间参数值绑定 GROUP BY r2.`subjectNo` ) ORDER BY subject.gradeId; -
在父查询的SELECT子句中使用子查询获得年级名称。年级名称来源于年级表，SQL语句如下：
SELECT `studentName` 姓名, (SELECT `gradeName` FROM `grade` WHERE `gradeId`=`subject`.`gradeId`) AS 课程所属年级 , `subjectName` 课程名称, `examDate` 考试日期, `studentResult` 成绩 FROM `result` r1 INNER JOIN `student` ON r1.`studentNo`=`student`.`studentNo` INNER JOIN `subject` ON `subject`.`subjectNo`=r1.`subjectNo` WHERE r1.`examDate` IN ( SELECT MAX(`examDate`) FROM `result` r2 WHERE r1.`subjectNo` = r2.`subjectNo` #主查询和子查询间参数值绑定 GROUP BY r2.`subjectNo` ) ORDER BY subject.gradeId;
完整SQL语句如下：
SELECT `studentName` 姓名,
(SELECT `gradeName` FROM `grade` WHERE `gradeId`=`subject`.`gradeId`) AS 课程所属年级 ,
`subjectName` 课程名称, `examDate` 考试日期, `studentResult` 成绩
FROM `result` r1
INNER JOIN `student` ON r1.`studentNo`=`student`.`studentNo`
INNER JOIN `subject` ON `subject`.`subjectNo`=r1.`subjectNo`
WHERE r1.`examDate` IN (
SELECT MAX(`examDate`) FROM `result` r2
WHERE r1.`subjectNo` = r2.`subjectNo` #主查询和子查询间参数值绑定
GROUP BY r2.`subjectNo`
)
ORDER BY subject.gradeId;
3.6 分组查询
1. 使用GROUP BY进行分组查询
分组统计需要使用聚合函数，如SUM()、AVG()等
（1）查询男、女学生的人数各是多少
分析：
首先按照性别列进行分组：GROUP BY sex；其次对每个组进行总数的统计，用到聚合函数COUNT()，完整的SQL语句如下：
SELECT COUNT(*) AS 人数,sex FROM student GROUP BY sex;
（2）查询每个年级的总人数
分析：
思路同前面的一样，按照年级进行分组即可
完整的SQL语句如下：
SELECT COUNT(*) AS 年级人数,gradeId FROM student gradeId;
（3）查询每个课程的平均分，并且按照由高到低的顺序排列显示。
分析：
思路同前面的一样，按照课程进行分组即可。分数由高到低进行排序，需要用到ORDER BY子句，ORDER BY子句应该放在GROUP BY子句之后。
完整的SQL语句如下：
SELECT subjectNo,AVG(studentResult) AS 课程平均成绩 FROM result
GROUP BY subjectNo
ORDER BY AVG(studentResult) DESC
备注：升序为ASC，降序为DESC，默认为升序
2. 多列分组查询
分组查询有时候可能还要按照多个列来进行分组。
统计每个年级的男女生人数
需要按照所属年级和性别两列进行分组，SQL语句如下：
SELECT COUNT(*) AS 人数,gradeId AS 年级,sex AS性别
FROM student
GROUP BY gradeId,sex
ORDER BY gradeId
备注：
使用GROUP BY关键字时，在SELECT后面可以指定的列是有限制的，仅允许以下几项：
- GROUP BY子句后的列
- 聚合函数计算出的列
被分组的列为每个分组返回一个值的表达式，如聚合函数计算出的列
3. 使用HAVING子句进行分组筛选
（1）查询年级总人数超过2个人的年级
首先可以通过分组查询获取每个年级的总人数，对应的SQL语句如下：
SELECT COUNT(*) AS 人数,gradeId AS 年级 FROM student GROUP BY gradeId;
但是还有一个条件，即人数超过2个人的年级。这牵扯到分组统计后的条件限制，限制条件为COUNT(*) >2。这时使用WHERE子句是不能满足查询要求的，因为WHERE子句只能对没有分组的数据进行筛选，对分组后的条件的筛选必须使用HAVING子句。
简单来说，HAVING子句用来对分组后的数据进行筛选，将“组”看作“列”来限定条件。
实现以上的需求的SQL语句如下：
SELECT COUNT(*) AS 人数,gradeId AS 年级
FROM student
GROUP BY gradeId
HAVING COUNT(*) > 2;
（2）查询平均分及格的课程信息
分析：按照课程分组之后，使用AVG(studentResult)>=60控制及格条件即可。SQL语句如下：
SELECT subjectNo AS 课程编号,AVG(studentResult) AS 课程平均成绩
FROM result
GROUP BY subjectNo
HAVING AVG(studentResult) >= 60;
（3）查询每门课程及格总人数和及格学生的平均分
分析：通过需求了解到所查询的信息都是对及格成绩进行统计，这样就首先从数据源中将不及格的学生信息进行滤除，然后对符合及格要求的数据进行分组处理。
完整的SQL语句如下：
SELECT COUNT(*) AS 人数,AVG(studentResult) AS 平均分,subjectNo AS 课程
FROM result
WHERE studentResult>=60
GROUP BY subjectNo
（4）查询每门课程及格总人数和及格平均分在80分以上的记录
分析：同上一个查询需求思路一致，只是加了一个队分组后数据进行筛选的条件，即及格平均分在80分以上，增加HAVING子句即可。
完整的SQL语句如下：
SELECT COUNT(*) AS 人数,AVG(studentResult) AS 平均分,subjectNo AS 课程
FROM result
WHERE studentResult>=60
GROUP BY subjectNo
HAVING AVG(studentResult) >= 80;
3.7 多表连接查询
1. 多表连接查询的分类
多表连接查询是通过各个表之间共同列的关联性来查询塑胶的，它是关系数据库查询的最主要的特征
常用的多表连接查询方式有：内连接查询、外连接查询
- 内连接查询：是最典型、最常用的连接查询，它根据表中共同的列进行匹配。特别是两个表存在主外键关系时通常用到内连接查询
- 外连接查询：是至少返回一个表中的所有记录，根据匹配条件有选择性的返回另一个表的记录。外连接查询分为左外连接、右外链接
2. 内连接查询
内连接查询通常会使用“=”或“<>”等比较运算符来判断两列数据值是否相等。
内连接查询使用INNER JOIN …ON关键字或WHERE子句来进行表之间的关联。内连接查询可以通过如下两种方式实现：
（1）在WHERE子句中指定连接条件。
例如：查询学生姓名和成绩的SQL语句如下：
SELECT student.studentName,result.subjectNo,result.studentResult
FROM student,result
WHERE student.studentNo=result.studentNo;
上面这种形式的查询相当于FROM后面紧跟了两个表名，在字段列表中使用“表名.列名”来区分列，再在WHERE条件中加以判断，要求学生编号信息相等。
（2）在FROM子句中使用INNER JOIN …ON
上面的查询也可以通过以下的JOIN… ON子句来实现：
SELECT s.studentName,r.subjectNo,r.studentResult
FROM student AS s
INNER JOIN result AS r ON s.studentNo=r.studentNo;
3. 外连接查询
内连接查询的结果是从两个或者两个以上的表的组合中挑选出符合条件的数据，如果数据无法满足连接条件则将其忽略。在内连接查询中，参与连接的表的地位是平等的
如内连接查询相对的方式成为外连接查询。在外连接查询中参与连接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中；对于那些不符合连接条件的列，将被填上NULL值（空值）后再返回到结果集中
（1）左外连接查询
左外连接查询的结果集包括LEFT JOIN子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。若左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列均为空值
左外连接查询使用LEFT JOIN…ON或者LEFT OUTER JOIN…ON关键字来进行表之间的关联。例如：要统计所有学生的考试情况，要求显示所有参加考试学生的每次考试分数，没有参加考试的学生也要显示出来。这时候，以学生信息表为主表（有时也叫左表）、学生成绩表为从表的左外连接查询的SQL语句如下：
SELECT s.studentName,r.subjectNo,r.studentResult
FROM student AS s
LEFT JOIN result AS r ON s.studentNo=r.studentNo;
（2）右外连接查询
右外连接查询与左外连接查询类似，只不过要包含右表中所有匹配的行。若右表中有的项在左表总没有对应的项，则以NULL填充。
右外连接查询使用RIGHT JOIN…ON 或 RIGHT OUTER JOIN…ON关键字来进行表之间的关联。例如：要统计所有学生的考试情况，要求显示所有参加考试学生的每次考试分数，没有参加考试的学生也要显示出来。这时候，以学生成绩表为主表（有时也叫右表）、学生信息表为从表的右外连接查询的SQL语句如下：
SELECT s.studentName,r.subjectNo,r.studentResult
FROM result AS r
RIGHT JOIN student AS s ON s.studentNo=r.studentNo
4. 数据库笔试题
有个年度统计表demo，表结构如下：
CREATE TABLE `demo` (
`year` int(11) NOT NULL,
`month` int(11) DEFAULT NULL,
`amount` double DEFAULT NULL
)
插入表数据如下：
INSERT INTO `demo` VALUES ('1991', '1', '1.1');
INSERT INTO `demo` VALUES ('1991', '2', '1.2');
INSERT INTO `demo` VALUES ('1991', '3', '1.3');
INSERT INTO `demo` VALUES ('1991', '4', '1.4');
INSERT INTO `demo` VALUES ('1992', '1', '2.1');
INSERT INTO `demo` VALUES ('1992', '2', '2.2');
INSERT INTO `demo` VALUES ('1992', '3', '2.3');
INSERT INTO `demo` VALUES ('1992', '4', '2.4');
如图所示：
要求查询的结果如下：
如何编写SQL语句？
分析两种方式实现：
-
使用子查询，语句如下：
SELECT year, (SELECT amount FROM demo d WHERE MONTH=1 AND d.year=demo.year) AS m1, (SELECT amount FROM demo d WHERE MONTH=2 AND d.year=demo.year) AS m2, (SELECT amount FROM demo d WHERE MONTH=3 AND d.year=demo.year) AS m3, (SELECT amount FROM demo d WHERE MONTH=4 AND d.year=demo.year) AS m4 FROM demo GROUP BY year; -
使用关键字CASE WHEN THEN，语句如下：
SELECT year, SUM(CASE WHEN MONTH = 1 THEN amount ELSE 0 END ) AS m1, SUM(CASE WHEN MONTH = 2 THEN amount ELSE 0 END ) AS m2, SUM(CASE WHEN MONTH = 3 THEN amount ELSE 0 END ) AS m3, SUM(CASE WHEN MONTH = 4 THEN amount ELSE 0 END ) AS m4 FROM `demo` GROUP BY year;
2759
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
