提示：二级MySQL常用数据库db_school，练习数据库db_sp
文章目录
- 回顾上篇
- 创建练习数据库db_sp和3张表
- 一、Insert into 插入数据
- 二、Update更新修改数据
- 三、Delete 删除数据
- 四、Select 查询数据
- 1. Select命令语法格式:
- 2. Select语句命令执行顺序：
- 3. Select命令练习
- 练习1：查询供应商零件号为P1的供应商号码
- 练习2：查询供货量在 300~500 之间的所有供货情况。
- 练习3：查询供应红色零件的供应商号码和供应商名称。
- 练习4： 查询重量在15以下，Paris供应商供应的零件代码和零件名。
- 关于写好“嵌套查询”和“三表内连接查询”SQL语句的心得
- 练习5：查询由London供应商供应的零件名称。
- 练习6：查询不供应红色零件的供应商名称。
- 练习7：查询供应商S3没有供应的零件名称。
- 练习8：查询供应零件代码为P1和P2两种零件的供应商名称。
- 练习9：查询与零件名Nut颜色相同的零件代码和零件名称。
- 练习10：查询供应了全部零件的供应商名称。
回顾上篇
完全掌握 Create Table创建表定义字段、 Alter Table 修改表字段和更新约束规则，有利于我们对数据库结构的理解，以便以后更好的记录数据、查询使用数据。
创建练习数据库db_sp和3张表
#删除数据库
drop database if existsdb_sp;#创建数据库
create databasedb_spcharacter set gb2312;#选择数据库
usedb_sp;
#创建供应商表 s
create tables
(SNO char(5),
SNAME varchar(20) not null unique,
STATUS smallint,
CITY varchar(20),
primary key(sno),
constraint ck_s check( city != ‘London’ or status=20 )
);
#创建零件表 p
create tablep
(PNO char(5) primary key ,
PNAME varchar(20) not null,
COLOR varchar(20) check(color in(‘Red’,‘Yellow’,‘Green’,‘Blue’)),
WEIGHT float
);
#创建供货量表 sp
create tablesp
(SNO char(5),
PNO char(5),
#JNO char(5), 没有JNO字段数据
QTY int,
primary key(sno,pno),
constraint fk_sp1 foreign key(sno) references s(sno),
constraint fk_sp2 foreign key(pno) references p(pno)
);
一、Insert into 插入数据
命令语法格式： insert into 表名[字段名…] values(‘值1’…),(‘值2’)…(‘值n’);
当省略字段名，就要给表的所有字段输入数据值，值的顺序和字段顺序一致，
没有数据值就用null代替，每一条记录写在一对圆括号里面。
每个字符类型的值前后都要有英文单引号，但是CSDN文章会把英文单引号显示成中文引号，如果直接复制文章中SQL语句会导致无法找到字段的错误。
insert into s values (‘S1’, ‘Smith’, 20,‘London’);
例如：复制上面SQL语句到命令提示符，就会出现以下错误提示：
mysql> insert into s values (‘S1’, ‘Smith’, 20,‘London’);
ERROR 1054 (42S22): Unknown column '‘S1’' in 'field list'
解决方法：在CSND文章中使用代码块插入SQL语句，就能正确显示带有英文单引号的字符串，所有字符串显示绿色。
#插入数据到供应商表 s
insert into `s` values
('S1', 'Smith', 20,'London'),
('S2', 'Jones', 10, 'Paris'),
('S3', 'Blake', 30, 'Paris'),
('S4', 'Clark', 20, 'London'),
('S5', 'Adams', 30, 'Athens'),
('S6', 'Brown', null, 'New York');
如果表中已经有记录，插入相同的主键字段值，会提示以下错误：
ERROR 1062 (23000): Duplicate entry ‘P1’ for key ‘PRIMARY’
解决方法：先删除表中相同的数据再插入数据
#插入数据到零件表 p
insert into `p` values
('P1', 'Nut', 'Red', 12),
('P2', 'Bolt', 'Green', 17),
('P3', 'Screw', 'Blue', 17),
('P4', 'Screw', 'Red', 14),
('P5', 'Cam', 'Blue', 12),
('P6', 'Cog', 'Red', 19);
#插入数据到供应量表 sp
insert into `sp` values
('S1', 'P1', 200),
('S1', 'P5', 400),
('S2', 'P1', 200),
('S2', 'P2', 200),
('S2', 'P3', 500),
('S2', 'P4', 600),
('S2', 'P5', 400),
('S2', 'P6', 800),
('S3', 'P3', 200),
('S3', 'P4', 500),
('S4', 'P2', 300),
('S4', 'P5', 300),
('S5', 'P1', 100),
('S5', 'P6', 200),
('S5', 'P2', 100),
('S5', 'P3', 200),
('S5', 'P5', 400);
由于创建sp表定义4个字段，插入数据值只有3个字段，字段数量和值数量不匹配，提示以下错误：
ERROR 1136 (21S01): Column count doesn’t match value count at row 1
解决方法：没有字段JNO数据，删除字段JNO，然后再插入记录。
#查看字段结构
desc sp;
#删除字段JNO
alter table sp drop jno;
复制上面代码块插入sp数据
#查看供应量表所有记录
select * from sp;
二、Update更新修改数据
命令语法格式：update 表名 set 字段名=‘新数据值’ where 字段名=‘指定记录的值’
表名也可以写成 数据库名.表名
例子：将数据库db_sp的表p中的蓝色零件的重量增加20%
update p set weight=weight*1.2 where color=‘blue’;
mysql>use db_sp;
mysql>select * from p;
+-----+-------+-------+--------+
| PNO | PNAME | COLOR | WEIGHT |
+-----+-------+-------+--------+
| P1 | Nut | Red | 12 |
| P2 | Bolt | Green | 17 |
| P3 | Screw | Blue | 17 |
| P4 | Screw | Red | 14 |
| P5 | Cam | Blue | 12 |
| P6 | Cog | Red | 19 |
+-----+-------+-------+--------+
6 rows in set (0.00 sec)
# 将数据库db_sp的表p中的蓝色零件的重量增加20%
mysql> update db_sp.p set weight=weight*1.2 where color='blue';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from p;
+-----+-------+-------+--------+
| PNO | PNAME | COLOR | WEIGHT |
+-----+-------+-------+--------+
| P1 | Nut | Red | 12 |
| P2 | Bolt | Green | 17 |
| P3 | Screw | Blue | 20.4 |
| P4 | Screw | Red | 14 |
| P5 | Cam | Blue | 14.4 |
| P6 | Cog | Red | 19 |
+-----+-------+-------+--------+
6 rows in set (0.00 sec)
三、Delete 删除数据
命令语法格式：delete from 表名 where 字段名=‘指定记录的值’
例子：将数据库db_sp的表s中状态为空值的供应商信息删除。
delete from s where status is null;
mysql> select * from s;
+-----+-------+--------+----------+
| SNO | SNAME | STATUS | CITY |
+-----+-------+--------+----------+
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
| S6 | Brown | NULL | New York |
+-----+-------+--------+----------+
6 rows in set (0.00 sec)
#将数据库db_sp的表s中状态为空值的供应商信息删除。
mysql> delete from s where status is null;
Query OK, 1 row affected (0.00 sec)
mysql> select * from s;
+-----+-------+--------+--------+
| SNO | SNAME | STATUS | CITY |
+-----+-------+--------+--------+
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
+-----+-------+--------+--------+
5 rows in set (0.00 sec)
例子：删除数据库 db_sp中没有供应零件的供应商信息。
先在供应商表s新添加S7供应商信息
insert into s values(‘S7’, ‘Bill’, 50, ‘New York’);
然后通过嵌套查询方法删除这个没有供应零件的S7供应商
delete from s where sno not in(select sno from sp);
# 先在供应商表s中添加S7供应商信息
mysql> insert into `s` values('S7', 'Bill', 50, 'New York');
Query OK, 1 row affected (0.00 sec)
mysql> select * from s;
+-----+-------+--------+----------+
| SNO | SNAME | STATUS | CITY |
+-----+-------+--------+----------+
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
| S7 | Bill | 50 | New York |
+-----+-------+--------+----------+
6 rows in set (0.00 sec)
# 然后通过嵌套查询方法删除这个没有供应零件的S7供应商
mysql> delete from s where sno not in(select sno from sp);
Query OK, 1 row affected (0.00 sec)
mysql> select * from s;
+-----+-------+--------+--------+
| SNO | SNAME | STATUS | CITY |
+-----+-------+--------+--------+
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
+-----+-------+--------+--------+
5 rows in set (0.00 sec)
四、Select 查询数据
1. Select命令语法格式:
select [distinct去除重复记录] 选择查询（返回）的目标数据：字段、‘字符串’、 函数、表达式，注意：结果表的字段名是完全复制查询时输入的文字，有区分英文大小写，因此要正确输入目标数据的大小写，对于复杂的函数或表达式可以取别名，目标数据用空格或as连接别名，方便阅读。
例如：给年龄函数取中文别名：
YEAR(NOW())-YEAR(birthday) ‘年龄’
from 表名或视图名，操作的表或视图，
where 指定字段条件表达式(行级过滤)有多种 ：
and并且，or或者，and优先级比or高，
in(值1，值2…)包含，相当于多个or，
not in(值1，值2…)不包含
between 起始值小 and 结束值大，
like ‘%字符串%’ ，not like(通配符%代表任意字符，下划线_代表一个字符),
is null 查询空，is not null 查询非空。
group by 目标字段名，对查询的目标字段进行分组，或对where筛选后的结果集进行分组，相等的目标字段值分为一组，然后就能使用分组聚合函数。 [默认升序排列asc可省略 | 降序排列关键字desc]
having 指定条件筛选分组结果(组级过滤)，和where相同点，筛选满足指定条件的记录；和where不同点，where后面写指定字段条件表达式，聚合函数只能写在 select后面或 having后面，having 后面写聚合函数(指定字段)条件表达式，例如平均分大于80的语句：having avg(score)>=80。
order by字段名， 对查询结果集排序 [默认升序关键字asc可省略 | 降序关键字desc]
limit() 位置偏移量，指定结果集包含的记录数。
分组聚合函数(指定字段)，对分组后的每一个组分别计算，有5个聚合函数：
count() 计数，求每组有几条记录，
sum() 总和，求每组指定字段值的总和，
avg() 平均值，求每组指定字段值的平均值
max() 找出每组的最大值
min() 找出每组的最小值
例如：查询每个供应商的供货总量
就是先对sp供货量表的供应商代码字段SNO分组，然后用聚合函数对供货量字段QTY求总和。
select SNO 供应商编号,SUM(QTY) 供货总量
from sp
group by sno;
查询结果：
±-----------±---------+
| 供应商编号 | 供货总量 |
±-----------±---------+
| S1 | 600 |
| S2 | 2700 |
| S3 | 700 |
| S4 | 600 |
| S5 | 1000 |
±-----------±---------+
5 rows in set (0.00 sec)
例如：供货总量大于1000的供应商
就要对分组函数增加条件表达式 having sum(qty)>=1000
select sno,sum(qty) from sp
group by sno
having sum(qty)>=1000;
查询结果
±----±---------+
| sno | sum(qty) |
±----±---------+
| S2 | 2700 |
| S5 | 1000 |
±----±---------+
2 rows in set (0.00 sec)
2. Select语句命令执行顺序：
from表，where 指定条件筛选，group by 指定字段分组，having 聚合函数筛选，select返回目标数据，order by指定字段排序。
3. Select命令练习
练习1：查询供应商零件号为P1的供应商号码
#先选择数据库
use db_sp;
#查看供应商表s内容
select * from s;
±----±------±-------±---------+
| SNO | SNAME | STATUS | CITY |
±----±------±-------±---------+
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
| S6 | Brown | NULL | New York |
±----±------±-------±---------+
#查看零件表p内容
select * from p;
±----±------±------±-------+
| PNO | PNAME | COLOR | WEIGHT |
±----±------±------±-------+
| P1 | Nut | Red | 12 |
| P2 | Bolt | Green | 17 |
| P3 | Screw | Blue | 17 |
| P4 | Screw | Red | 14 |
| P5 | Cam | Blue | 12 |
| P6 | Cog | Red | 19 |
±----±------±------±-------+
#查看供货量表sp内容
select * from sp;
±----±----±-----+
| SNO | PNO | QTY |
±----±----±-----+
| S1 | P1 | 200 |
| S1 | P5 | 400 |
| S2 | P1 | 200 |
| S2 | P2 | 200 |
| S2 | P3 | 500 |
| S2 | P4 | 600 |
| S2 | P5 | 400 |
| S2 | P6 | 800 |
| S3 | P3 | 200 |
| S3 | P4 | 500 |
| S4 | P2 | 300 |
| S4 | P5 | 300 |
| S5 | P1 | 100 |
| S5 | P2 | 100 |
| S5 | P3 | 200 |
| S5 | P5 | 400 |
| S5 | P6 | 200 |
±----±----±-----+
解析：由于sp表包含目标字段“供应商号码SNO”，也包含了指定条件“零件号码P1”，所以只要用单表查询方法在sp表直接找出目标字段SNO。
注意：原表的字段名使用大写英文，因此查询语句中目标字段也应该用大写英文，返回的查询结果才能显示大写英文字段名。
练习1解题语句：
select SNO from `sp` where pno='p1';
查询结果：
±----+
| SNO |
±----+
| S1 |
| S2 |
| S5 |
±----+
3 rows in set (0.01 sec)
练习2：查询供货量在 300~500 之间的所有供货情况。
解析：取值范围用 between 起始值 and 结束值。
练习2解题语句：
select * from `sp`
where qty between 300 and 500;
查询结果：
±----±----±-----+
| SNO | PNO | QTY |
±----±----±-----+
| S1 | P5 | 400 |
| S2 | P3 | 500 |
| S2 | P5 | 400 |
| S3 | P4 | 500 |
| S4 | P2 | 300 |
| S4 | P5 | 300 |
| S5 | P5 | 400 |
±----±----±-----+
7 rows in set (0.01 sec)
练习3：查询供应红色零件的供应商号码和供应商名称。
解析：首先看到题目中只有1个指定条件是 “红色零件”，包含红色零件是零件表p，但是表p没有目标字段 “供应商号码SNO” 和“供应商名称SNAME”，这里就要使用多表查询方法。
通过表p和表sp相同的字段等值连接 p.pno=sp.pno，表和相同字段使用点号连接；再加入“红色零件”指定条件表达式 color=‘Red’ ，组合成子查询的指定条件，where p.pno=sp.pno and color=‘Red’，这里Where等值连接也称隐式内连接 ，由此获得目标字段SNO。
子查询语句:
select SNO from p,sp
where p.pno=sp.pno and color='Red';
子查询的结果如下，可以看到子查询的字段值是有重复的。
±----+
| SNO |
±----+
| S1 |
| S2 |
| S5 |
| S2 |
| S3 |
| S2 |
| S5 |
±----+
7 rows in set (0.00 sec)
接着，为了获得另一个目标字段SNAME，就需要查询供应商表s。把上面子查询语句套入到主查询中，子查询的SNO字段值就作为主查询的指定条件，Where in(子查询)，当一个查询作为另一查询的条件时称之为子查询，也称嵌套查询，嵌套查询顺序先执行内层子查询语句，再执行外层主查询语句。
嵌套查询优点是主查询Where in能筛选子查询的数据，不会生成重复的记录。
练习3解题语句( 使用嵌套查询和隐式内连接)：
select SNO,SNAME from s
where sno in
(select sno from p,sp where p.pno=sp.pno and color='Red');
查询结果：
±----±------+
| SNO | SNAME |
±----±------+
| S5 | Adams |
| S3 | Blake |
| S2 | Jones |
| S1 | Smith |
±----±------+
4 rows in set (0.01 sec)
隐式内连接语法格式：
select 目标字段 from 表1,表2 where 表1.相同字段=表2.相同字段
按from后输入的先后顺序，先输入的表1称左表，后输入的表2也称右表
因此：
select 目标字段 from 左表,右表 where 左表.相同字段=右表.相同字段
# 下面是表p和表sp等值连接后得到的表，
# 可以得知PNO和SNO是多对多关系，即零件和供应商是多对多关系，
# 按from后输入的先后顺序，表p称为左表，表sp称为右表。
select * from p,sp
where p.pno=sp.pno;
+-----+-------+-------+--------+-----+-----+------+
| PNO | PNAME | COLOR | WEIGHT | SNO | PNO | QTY |
+-----+-------+-------+--------+-----+-----+------+
| P1 | Nut | Red | 12 | S1 | P1 | 200 |
| P1 | Nut | Red | 12 | S2 | P1 | 200 |
| P1 | Nut | Red | 12 | S5 | P1 | 100 |
| P2 | Bolt | Green | 17 | S2 | P2 | 200 |
| P2 | Bolt | Green | 17 | S4 | P2 | 300 |
| P2 | Bolt | Green | 17 | S5 | P2 | 100 |
| P3 | Screw | Blue | 17 | S2 | P3 | 500 |
| P3 | Screw | Blue | 17 | S3 | P3 | 200 |
| P3 | Screw | Blue | 17 | S5 | P3 | 200 |
| P4 | Screw | Red | 14 | S2 | P4 | 600 |
| P4 | Screw | Red | 14 | S3 | P4 | 500 |
| P5 | Cam | Blue | 12 | S1 | P5 | 400 |
| P5 | Cam | Blue | 12 | S2 | P5 | 400 |
| P5 | Cam | Blue | 12 | S4 | P5 | 300 |
| P5 | Cam | Blue | 12 | S5 | P5 | 400 |
| P6 | Cog | Red | 19 | S2 | P6 | 800 |
| P6 | Cog | Red | 19 | S5 | P6 | 200 |
+-----+-------+-------+--------+-----+-----+------+
17 rows in set (0.00 sec)
# 下面是加入指定条件“红色零件” 得到的红色零件供货表。
select * from p,sp
where p.pno=sp.pno and p.color='Red';
+-----+-------+-------+--------+-----+-----+------+
| PNO | PNAME | COLOR | WEIGHT | SNO | PNO | QTY |
+-----+-------+-------+--------+-----+-----+------+
| P1 | Nut | Red | 12 | S1 | P1 | 200 |
| P1 | Nut | Red | 12 | S2 | P1 | 200 |
| P1 | Nut | Red | 12 | S5 | P1 | 100 |
| P4 | Screw | Red | 14 | S2 | P4 | 600 |
| P4 | Screw | Red | 14 | S3 | P4 | 500 |
| P6 | Cog | Red | 19 | S2 | P6 | 800 |
| P6 | Cog | Red | 19 | S5 | P6 | 200 |
+-----+-------+-------+--------+-----+-----+------+
7 rows in set (0.00 sec)
如果不用嵌套查询只用内连接，三表内连接查询，需要注意2点
第1. 多表连接会出现重复记录，需要用distinct过滤重复记录；
第2. 如果没有给目标字段指定哪个表，会提示以下错误：
select sno,sname from s,p,sp
where s.sno=sp.sno and p.pno=sp.pno;
ERROR 1052 (23000): Column ‘sno’ in field list is ambiguous
解决方法：给目标字段指定一个表，表和目标字段用点号连接 S.SNO
三表内连接语法格式：
select distinct 表1.目标字段 from 表1,表2,表3
where 表1.相同字段=表2.相同字段 and 表2.相同字段2=表3.相同字段2;
目标字段必须用点号连接指定的表：表1.目标字段
练习3解题语句（使用三表内连接）
# 三表内连接方法，需要distinct去除重复行
select distinct S.SNO, SNAME
from s,p,sp
where s.sno=sp.sno and p.pno=sp.pno
and color='Red';
查询结果和嵌套查询一样
±----±------+
| SNO | SNAME |
±----±------+
| S1 | Smith |
| S2 | Jones |
| S5 | Adams |
| S3 | Blake |
±----±------+
4 rows in set (0.00 sec)
三表内连接（隐式）的所有记录：
# 三表内连接（隐式）的所有记录
select * from s,p,sp
where s.sno=sp.sno and p.pno=sp.pno;
+-----+-------+--------+--------+-----+-------+-------+--------+-----+-----+------+
| SNO | SNAME | STATUS | CITY | PNO | PNAME | COLOR | WEIGHT | SNO | PNO | QTY |
+-----+-------+--------+--------+-----+-------+-------+--------+-----+-----+------+
| S1 | Smith | 20 | London | P1 | Nut | Red | 12 | S1 | P1 | 200 |
| S1 | Smith | 20 | London | P5 | Cam | Blue | 12 | S1 | P5 | 400 |
| S2 | Jones | 10 | Paris | P1 | Nut | Red | 12 | S2 | P1 | 200 |
| S2 | Jones | 10 | Paris | P2 | Bolt | Green | 17 | S2 | P2 | 200 |
| S2 | Jones | 10 | Paris | P3 | Screw | Blue | 17 | S2 | P3 | 500 |
| S2 | Jones | 10 | Paris | P4 | Screw | Red | 14 | S2 | P4 | 600 |
| S2 | Jones | 10 | Paris | P5 | Cam | Blue | 12 | S2 | P5 | 400 |
| S2 | Jones | 10 | Paris | P6 | Cog | Red | 19 | S2 | P6 | 800 |
| S3 | Blake | 30 | Paris | P3 | Screw | Blue | 17 | S3 | P3 | 200 |
| S3 | Blake | 30 | Paris | P4 | Screw | Red | 14 | S3 | P4 | 500 |
| S4 | Clark | 20 | London | P2 | Bolt | Green | 17 | S4 | P2 | 300 |
| S4 | Clark | 20 | London | P5 | Cam | Blue | 12 | S4 | P5 | 300 |
| S5 | Adams | 30 | Athens | P1 | Nut | Red | 12 | S5 | P1 | 100 |
| S5 | Adams | 30 | Athens | P2 | Bolt | Green | 17 | S5 | P2 | 100 |
| S5 | Adams | 30 | Athens | P3 | Screw | Blue | 17 | S5 | P3 | 200 |
| S5 | Adams | 30 | Athens | P5 | Cam | Blue | 12 | S5 | P5 | 400 |
| S5 | Adams | 30 | Athens | P6 | Cog | Red | 19 | S5 | P6 | 200 |
+-----+-------+--------+--------+-----+-------+-------+--------+-----+-----+------+
17 rows in set (0.00 sec)
等值连接有用where隐式内连接，还有带join关键字的显式内连接。
显式内连接语法格式：
select 目标字段 from 左表 inner join 右表 ** on** 左表.相同字段=右表.相同字段
仅比较两个表相同的字段，inner可省略，from后面称为左表，join后面称为右表，字段值相等的每一行记录都返回，合成表的字段顺序按原来表的顺序，先显示左表的字段再显示右表字段，不去除重复的字段。
练习3解题语句：（使用三表显式内连接），查询结果也和嵌套查询一样。
# 三表显式内连接方法，需要distinct去除重复行
select distinct S.SNO,SNAME from p
inner join sp on p.pno=sp.pno
inner join s on s.sno=sp.sno
where color='Red';
自然连接
语法格式：from 左表 natural join 右表
自然连接是特殊的等值连接，比较所有相同字段，合成表的字段顺序是先显示相同的字段，然后是左表字段再显示右表字段，去除重复的字段。
自然连接和等值连接区别：自然连接要求2个关系中至少含有1个相同的属性字段名才能连接，等值连接没有要求必须字段名相同，只要属性字段值相同就能等值连接。
mysql> select * from s natural join sp;
+-----+-------+--------+--------+-----+------+
| SNO | SNAME | STATUS | CITY | PNO | QTY |
+-----+-------+--------+--------+-----+------+
| S1 | Smith | 20 | London | P1 | 200 |
| S1 | Smith | 20 | London | P5 | 400 |
| S2 | Jones | 10 | Paris | P1 | 200 |
| S2 | Jones | 10 | Paris | P2 | 200 |
| S2 | Jones | 10 | Paris | P3 | 500 |
| S2 | Jones | 10 | Paris | P4 | 600 |
| S2 | Jones | 10 | Paris | P5 | 400 |
| S2 | Jones | 10 | Paris | P6 | 800 |
| S3 | Blake | 30 | Paris | P3 | 200 |
| S3 | Blake | 30 | Paris | P4 | 500 |
| S4 | Clark | 20 | London | P2 | 300 |
| S4 | Clark | 20 | London | P5 | 300 |
| S5 | Adams | 30 | Athens | P1 | 100 |
| S5 | Adams | 30 | Athens | P2 | 100 |
| S5 | Adams | 30 | Athens | P3 | 200 |
| S5 | Adams | 30 | Athens | P5 | 400 |
| S5 | Adams | 30 | Athens | P6 | 200 |
+-----+-------+--------+--------+-----+------+
17 rows in set (0.00 sec)
练习4： 查询重量在15以下，Paris供应商供应的零件代码和零件名。
解析：首先看到题目中有2个指定条件，需要同时满足：“重量在15以下”weight<15，“Paris供应商” city=‘paris’，查询的目标字段是pno和pname。
练习4解题语句：使用嵌套查询方法
select PNO,PNAME from p
where weight<15 and pno in
(select pno from s,sp where s.sno=sp.sno
and city='Paris');
查询结果：
±----±------+
| pno | pname |
±----±------+
| P1 | Nut |
| P4 | Screw |
| P5 | Cam |
±----±------+
3 rows in set (0.00 sec)
第2种，使用三表内连接查询方法，查询结果和嵌套查询一样。
#三表内连接方法，需要distinct去除重复行
select distinct P.PNO,PNAME
from p,sp,s
where p.pno=sp.pno and s.sno=sp.sno
and weight<15 and city='Paris';
关于写好“嵌套查询”和“三表内连接查询”SQL语句的心得
- 嵌套查询先写好子查询(内层查询)，再套入外层主查询。
- 目标字段所在表和指定条件有相同字段，只要操作2个表，使用嵌套查询语句最简洁。
- 目标字段所在表和指定条件没有相同字段，要操作3个表，使用三表内连接语句最简洁。
- 内连接 from 后面输入第一表叫左表 输入表的顺序决定了合成表字段顺序，但不影响查询目标字段的结果。
- 等值内连接要用点号连接表： 左表.相同字段=右表.相同字段
- 使用三表内连接查找目标字段也有相同字段时，要用点号连接表去除重复字段：左表.目标字段
- 三表内连接还会匹配生成重复记录，要用distinct去重。
练习5：查询由London供应商供应的零件名称。
解析：题目只有一个指定条件“ London供应商”，指定条件表达式就是city=‘London’，
因为目标字段零件名称“PNAME”和指定条件分别在不同的表，所以使用三表内连接查询。
练习5解题语句：
select distinct PNAME
from p,sp,s
where p.pno=sp.pno and s.sno=sp.sno
and city='London';
查询结果：
±------+
| PNAME |
±------+
| Nut |
| Cam |
| Bolt |
±------+
3 rows in set (0.00 sec)
练习6：查询不供应红色零件的供应商名称。
解析：题目只有一个指定条件“不供应红色零件”，先写红色零件表达式 color=‘Red’，“不供应”可以使用not in表达。因为目标字段供应商名称“SNAME”和指定条件分别在不同的表，所以用三表内连接SQL语句最简洁。
练习6解题语句：
select SNAME from s
where sno not in
(select sno from p,sp where p.pno=sp.pno
and color='Red');
查询结果：
±------+
| SNAME |
±------+
| Brown |
| Clark |
±------+
2 rows in set (0.00 sec)
练习7：查询供应商S3没有供应的零件名称。
解析：题目只有一个指定条件“供应商S3没有供应的零件”，“没有供应”可以用not in表达。因为目标字段零件名称“PNAME”所在的表有和指定条件所在表相同字段“PNO”，所以只要操作两表，使用嵌套查询方法的SQL语句最简洁。
练习7解题语句：
select PNAME from P
where PNO not in
(select pno from sp where sno='S3');
查询结果：
±------+
| PNAME |
±------+
| Nut |
| Bolt |
| Cam |
| Cog |
±------+
4 rows in set (0.00 sec)
练习8：查询供应零件代码为P1和P2两种零件的供应商名称。
解析：题目有2个指定条件“零件代码为P1和P2两种零件”，也就是同一个字段同时满足2个值，如果表达式写成 PNO=‘P1’ and 'P2’是错误的，这查询结果为空Empty。所以只能分步查询，先查询筛选出零件 PNO=‘P1’，然后再一次查询筛选零件PNO=‘P2’ ，使用嵌套查询方法找出字段SNO。
# 错误的语句
select sno from sp where pno ='P1' and 'P2';
Empty set, 1 warning (0.00 sec)
# 使用嵌套查询方法找出字段SNO
select sno from sp where pno='P2' and
sno in(select sno from sp where pno='P1');
因为查询的目标字段供应商名称“SNAME”所在表s和指定条件所在的供应表sp有相同字段“SNO”，所以再此用嵌套查询方法。
练习8解题语句：
# 使用了双层嵌套查询
select SNAME from s
where sno in (select sno from sp where pno='P2'
and sno in(select sno from sp where pno='P1'));
查询结果：
±------+
| SNAME |
±------+
| Adams |
| Jones |
±------+
2 rows in set (0.02 sec)
当然还可以换个思路，通过内连接表s和sp查询指定条件零件号为P1的供应商名称，再用子查询筛选P2零件。
练习8解题语句(内连接)：
select SNAME from s,sp
where s.sno=sp.sno and pno='P1'
and s.sno in( select sno from sp where pno='P2' );
练习9：查询与零件名Nut颜色相同的零件代码和零件名称。
解析：因为查询的目标字段是零件代码PNO和零件名称PNAME和指定条件是同一个表，所以可以用嵌套查询，在子查询中找到零件Nut的颜色，然后嵌套到主查询。
还要排除Nut自己本身，pname!=‘Nut’。
练习8解题语句：
select PNO,PNAME from p
where pname!='Nut' and color=( select color from p where pname='Nut');
查询结果：
±----±------+
| PNO | PNAME |
±----±------+
| P4 | Screw |
| P6 | Cog |
±----±------+
2 rows in set (0.02 sec)
另一种自连接查询，将表p分别取别名p1和p2
select P1.PNO, P1.PNAME
from p p1 join p p2
on p1.color=p2.color
where p1.pname!='Nut' and p2.pname='Nut';
练习10：查询供应了全部零件的供应商名称。
select SNAME from s
where not exists
(select * from P where not exists
(select * from SP where S.SNO=SP.SNO and SP.PNO=P.PNO));
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
