MySql数据库原理及应用项目5

项目5查询与维护商品信息管理数据表MySQL数据库原理及应用目录CONTENTS掌握简单数据查询任务1掌握多表连接查询任务2理解嵌套查询任务3理解集合查询任务4数据更新任务5掌握简单数据查询01掌握简单数据查询1.单表无条件数据查询语法格式如下：SELECT<字段列名1,字段列名2>FROM<表名>在字段列名中，多个不同字段列名用英文逗号“，”分隔，SELECT语句只返回相应字段的数据。如果要显示所有字段，可以使用星号（*）来代替，SELECT语句会返回表的所有字段数据。掌握简单数据查询2.使用WHERE子句实现条件查询在MySQL中，如果需要有条件的从数据表中查询数据，可以使用WHERE关键字来指定查询条件。使用WHERE关键字的语法格式如下：WHERE查询条件可以是：带比较运算符和逻辑运算符的查询条件带BETWEENAND关键字的查询条件带ISNULL关键字的查询条件带IN关键字的查询条件带LIKE关键字的查询条件掌握简单数据查询2.使用WHERE子句实现条件查询1）单一条件查询单一条件指的是在WHERE关键字后只有一个查询条件。2）多条件查询在WHERE关键词后可以有多个查询条件，这样能够使查询结果更加精确AND：记录满足所有查询条件时，才会被查询出来。OR：记录满足任意一个查询条件时，才会被查询出来。XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。掌握简单数据查询3.使用常用聚集函数统计数据函数名作用MAX(column)返回某列的最低值（有则不会返回NULL，没有则返回NULL）MIN(column)返回某列的最高值（没有则返回NULL）COUNT(column)返回某列的行数（不包括NULL值）COUNT(*)返回被选列行数（包括NULL）SUM(column)求和AVG(column)求平均值掌握简单数据查询4.分组筛选数据在MySQL中，GROUPBY关键字可以根据一个或多个字段对查询结果进行分组。在分组的列上我们可以使用COUNT,SUM,AVG,等函数。使用GROUPBY关键字的语法格式如下：GROUPBY<字段名>其中，“字段名”表示需要分组的字段名称，多个字段时用逗号隔开。1）GROUPBY单独使用单独使用GROUPBY关键字时，查询结果会只显示每个分组的第一条记录。2）GROUPBY与聚合函在数据统计时，GROUPBY关键字经常和聚合函数一起使用。掌握简单数据查询5.对查询结果进行排序通过条件查询语句可以查询到符合用户需求的数据，但是查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。为了使查询结果的顺序满足用户的要求，MySQL提供了ORDERBY关键字来对查询结果进行排序。其语法格式如下：ORDERBY<字段名>[ASC|DESC]语法说明如下。字段名：表示需要排序的字段名称，多个字段时用逗号隔开。ASC|DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值。使用ORDERBY关键字应该注意以下几个方面：ORDERBY关键字后可以跟子查询。当排序的字段中存在空值时，ORDERBY会将该空值作为最小值来对待。ORDERBY指定多个字段进行排序时，MySQL会按照字段的顺序从左到右依次进行排序。掌握多表连接查询02掌握多表连接查询1.交叉连接多个表交叉连接时，在FROM后连续使用“CROSSJOIN”或逗号“，”即可。两种语法的返回结果是相同的，但是第一种语法是官方建议的标准写法。交叉连接的语法格式如下：SELECT<字段名>FROM<表1>CROSSJOIN<表2>[WHERE子句]或SELECT<字段名>FROM<表1>,<表2>[WHERE子句]语法说明如下：字段名：需要查询的字段名称。<表1><表2>：需要交叉连接的表名。WHERE子句：用来设置交叉连接的查询条件。当连接的表之间没有关系时，我们会省略掉WHERE子句，这时返回结果就是两个表的笛卡尔积，返回结果数量就是两个表的数据行相乘。掌握多表连接查询1.交叉连接SELECT*FROMgoods_tbCROSSJOINcatalogs_tb;掌握多表连接查询1.交叉连接为了将无意义的数据过滤掉，需要用在交叉连接时使用WHERE子句。SELECT*FROMgoods_tbCROSSJOINcatalogs_tbWHEREgoods_tb.goodsCatId=catalogs_tb.catId;掌握多表连接查询2.内连接内连接（INNERJOIN）主要通过设置连接条件的方式，来移除查询结果中某些数据行的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。内连接使用INNERJOIN关键字连接两张表，并使用ON子句来设置连接条件。内连接的语法格式如下：SELECT<字段名>FROM<表1>INNERJOIN<表2>[ON子句]语法说明如下：字段名：需要查询的字段名称。<表1><表2>：需要内连接的表名。INNERJOIN：内连接中可以省略INNER关键字，只用关键字JOIN。ON子句：用来设置内连接的连接条件。掌握多表连接查询2.内连接当对多个表进行查询时，要在SELECT语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT语句后面的写法是“表名.列名”。另外，如果表名非常长的话，也可以给表设置别名。掌握多表连接查询3.自连接自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。MySQL有时在信息查询时需要采用自连接，使用自连接时需要为表定义别名。例如查询goods_tb表中库存量大于棒棒糖库存量的所有商品信息，其中棒棒糖库存量goodsStock的值为50，采用自连接进行查询时，SQL语句和执行结果如下。掌握多表连接查询4.外连接1）左连接左外连接又称为左连接，使用LEFTOUTERJOIN关键字连接两个表，并使用ON子句来设置连接条件。左连接的语法格式如下：SELECT<字段名>FROM<表1>LEFTOUTERJOIN<表2><ON子句>语法说明如下。字段名：需要查询的字段名称。<表1><表2>：需要左连接的表名。LEFTOUTERJOIN：左连接中可以省略OUTER关键字，只使用关键字LEFTJOIN。ON子句：用来设置左连接的连接条件，不能省略。上述语法中，“表1”为基表，“表2”为参考表。左连接查询时，可以查询出“表1”中的所有记录和“表2”中匹配连接条件的记录。如果“表1”的某行在“表2”中没有匹配行，那么在返回结果中，“表2”的字段值均为空值（NULL）。掌握多表连接查询4.外连接1）左连接采用左连接查询商品信息和每个商品对应的类别名称，其SQL语句和运行结果如下。goodsName为“苹果”的商品没有类别名称，因为对应的catalogs_tb表中没有该类别的信息，所以该条记录只取出了goods_tb表中相应的值，而从catalogs_tb表中取出的值为NULL。掌握多表连接查询4.外连接2）右连接右外连接又称为右连接，右连接是左连接的反向连接。使用RIGHTOUTERJOIN关键字连接两个表，并使用ON子句来设置连接条件。右连接的语法格式如下：SELECT<字段名>FROM<表1>RIGHTOUTERJOIN<表2><ON子句>语法说明如下。字段名：需要查询的字段名称。<表1><表2>：需要右连接的表名。RIGHTOUTERJOIN：右连接中可以省略OUTER关键字，只使用关键字RIGHTJOIN。ON子句：用来设置右连接的连接条件，不能省略。与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。掌握多表连接查询4.外连接2）右连接采用左连接查询商品信息和每个商品对应的类别名称，其SQL语句和运行结果如下。catName为“玩具”的类别没有相关的商品，所以该条记录只取出了catalogs_tb表中相应的值，而从goods_tb表中取出的值为NULL。理解嵌套查询03理解嵌套查询1.嵌套子查询嵌套子查询是MySQL中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在SELECT、UPDATE和DELETE语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在WHERE子句中。子查询在WHERE中的语法格式如下：WHERE<表达式><操作符>(子查询)其中，操作符可以是比较运算符和IN、NOTIN、EXISTS、NOTEXISTS等关键字。MySQL子查询可以分为相关子查询和非相关子查询两类。理解嵌套查询2.非相关子查询非相关子查询的执行不依赖与外部的查询，其执行过程如下：（1）执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。（2）执行外部查询，并显示整个结果。返回单值的子查询返回列表的子查询理解嵌套查询3.相关子查询相关子查询的执行依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。执行过程：（1）从外层查询中取出一个元组，将元组相关列的值传给内层查询。（2）执行内层查询，得到子查询操作的值。（3）外查询根据子查询返回的结果或结果集得到满足条件的行。（4）然后外层查询取出下一个元组重复做步骤1-3，直到外层的元组全部处理完毕。理解嵌套查询3.相关子查询查询goods_tb表中大于该类商品价格平均值的商品信息，其SQL语句和执行结果如下。与前面介绍过的子查询不同，相关子查询无法独立于外部查询而得到解决。该子查询需要一个商品类别编号goodsCatId的值。而这个值是个变量，随MySQL检索goods_tb表中的不同行而改变。理解集合查询04理解集合查询SELECT语句的查询结果是元组的集合，所以多个SELECT语句的结果可进行集合操作。集合操作主要包括并操作UNION、交操作INTERSECT、差操作EXCEPT。但需要注意的是，参加集合操作的各查询结果的列数必须相同，且对应的数据类型也必须相同。1.

并操作MySQL中并操作有直接的关键字支持，那就是UNION和UNIONALL，但是他俩是有区别的，UNION得到的结果会把重复的元组去掉，而UNIONALL会保留所有元组。理解集合查询2.

交操作在SQL语法定义中有交操作的关键字INTERSECT，但是遗憾的是MySQL并不支持，只能通过替代语句来实现。查询goods_tb表中库存大于50，价格小于8的商品信息，在支持INTERSECT关键字的环境中，其SQL语句可以如下。SELECT*FROMgoods_tbWHEREgoodsStock>50INTERSECTSELECT*FROMgoods_tbWHEREgoodsPrice<8MySQL中替代的SQL语句及运行结果如下。SELECT*FROMgoods_tbWHEREgoodsStock>50ANDgoodsPrice<8;理解集合查询3.

差操作在SQL语法定义中有差操作的关键字EXCEPT，但是遗憾的是MySQL同样不支持，只能通过替代语句来实现。查询goods_tb表中库存大于20的商品与价格小于等于8的商品差交集，在支持EXCEPT关键字的环境中，其SQL语句可以如下。SELECT*FROMgoods_tbWHEREgoodsStock>20EXCEPTSELECT*FROMgoods_tbWHEREgoodsPrice<=8MySQL中替代的SQL语句及运行结果如下。SELECT*FROMgoods_tbWHEREgoodsStock>20ANDgoodsPrice>8;数据更新05数据更新1.数据记录的插入1）INSERT插入一条数据数据库与表创建成功以后，需要向数据库的表中插入数据。在MySQL中可以使用INSERT语句向数据库已有的表中插入一行或者多行数据。以下为向MySQL数据表插入一条数据的INSERTINTO语法。INSERTINTOtable_name(field1,field2,...fieldN)VALUES(value1,value2,...valueN);需要注意的是，如果第一列没有设置主键自增，添加第一列数据时比较容易出错，要不断的查看待添加的主键值是否与现有值重复。如果添加过主键自增，第一列可以省略，或者在增加数据的时候，可以写为0或者NULL，这样添加数据可以自增，从而可以添加全部数据。数据更新1.数据记录的插入INSERTINTOgoods_tb(goodsId,goodsName,goodsPrice,goodsUnit,goodsStock,goodsCatId)VALUES (0,'玩具汽车',56,'辆',17,4);INSERTINTOgoods_tb(goodsId,goodsName,goodsPrice,goodsUnit,goodsStock,goodsCatId)VALUES (NULL,'玩具汽车',56,'辆',17,4);INSERTINTOgoods_tb(goodsName,goodsPrice,goodsUnit,goodsStock,goodsCatId)VALUES ('玩具汽车',56,'辆',17,4);INSERTINTOgoods_tbVALUES (NULL,'玩具汽车',56,'辆',17,4);INSERTINTOgoods_tbVALUES (0,'玩具汽车',56,'辆',17,4);数据更新1.数据记录的插入2）INSERT插入多条数据INSERT语句可以一次插入多条数据，其语法如下,其形式与插入一条数据的类似，只是在VALUES后面增加多行值，且每行值之间用逗号分隔。INSERTINTOtable_name(field1,field2,...fieldN)VALUES(valueA1,valueA2,...valueAN),