电工与电子技术电子商务电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第8章ppt

1、电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第8章第8章MySQL数据操作管理数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）CONTENTS插入数据1修改数据2删除数据3单表查询4多表查询4插入数据18.1.1 为表的所有字段插入数据1)INSERT语句中不指定具体的字段名INSERT语句有两种方式可以同时为表的所有字段插入数据。第一种方式是不指定具体的字段名;第二种方式是列出表的所有字段。其中，“表名”参数指定记录插入到哪个表中;“值n”参数表示要插入的数据。“值1”到“值n”分别对应着表中的每个字段。INSERT INTO 表名 VALUE

2、S(值1，值2，值n) 8.1.1 为表的所有字段插入数据【例8-1】向student表中插入记录。INSERT语句代码如下：INSERT INTO student VALUES (1418855233, 王一, 男, 1997-01-01, 1102, 商务1301);8.1.1 为表的所有字段插入数据2) INSERT语句中列出所有字段INSERT语句有两种方式可以同时为表的所有字段插入数据。第一种方式是不指定具体的字段名;第二种方式是列出表的所有字段。其中，“字段名n”参数表示表中的字段名称，此处必须列出表的所有字段的名称;“值n”参数表示每个字段的值，每个值与相应的字段对应。INSER

3、T INTO表名(字段名1，字段名2，字段名n)VALUES(值1，值2，值n);8.1.1 为表的所有字段插入数据【例8-2】向student表中插入一条新记录。INSERT语句的代码如下:INSERT INTO student(sno,sname,ssex,sbirth,zno,sclass)VALUES(1418855234,李三,男,1996-07-08,1102,商务1301); 如果表的字段比较多，用第二种方法就比较麻烦。但是，第二种方法比较灵活。可以随意地设置字段的顺序，而不需要按照表定义时的顺序。值的顺序也必须跟着字段顺序的改变而改变。8.1.1 为表的所有字段插入数据【例8-

4、3】向student表中插入一条新记录。INSERT语句中字段的顺序与表定义时的顺序不同。INSERT语句的代码如下：INSERT INTO student(sno,sname,sbirth,ssex,zno,sclass)VALUES(1418855235,张平,1996-03-15,女,1102,商务1301); sbirth字段和ssex字段的顺序发生了改变，其对于值的位置也跟着发生了改变。8.1.2 为表的指定字段插入数据上面讲解的INSERT语句只是指定全部字段，这一节为表中的部分字段插入数据。INSERT INTO表名(字段名1，字段名2，字段名n) VALUES(值1，值2，值n

5、) 【例8-4】向student表的sno，sname和ssex这3个字段插入数据。INSERT INTO student(sno,sname,ssex, sbirth) VALUES(1418855236, 张强, 男 , 1996-03-15) ; 没有赋值的字段，数据库系统会为其插入默认值。这个默认值是在创建表的时候定义的。如zno字段和sclase字段的默认值为NULL。如果某个字段没有设置默认值，而且是非空。这就必须为其赋值。8.1.2 为表的指定字段插入数据【例8-5】向student表的sno，sname和ssex字段插入数据。INSERT语句中，这3个字段的顺序可以任意排列。I

6、NSERT语句的代码如下：INSERT INTO student(sno,ssex,sname, sbirth)VALUES(1418855237,女,李苹, 1996-03-15);8.1.3 同时插入多条记录MySQL中，一个INSERT语句可以同时插入多条记录。INSERT INTO表名(字段名列表) VALUES(取值列表1)，(取值列表2)，(取值列表n) 【例8-6】向student表中插入3条新记录。INSERT语句的代码如下：8.1.2 为表的指定字段插入数据【例8-7】向student表的sno，sname和ssex这3个字段插入数据。总共插入3条记录。8.1.4 从目标表中

7、插入值使用INSERT INTO SELECT可以从一个表或者多个表向目标表中插入记录。SELECT语句中返回的是一个查询到的结果集，INSERT语句将这个结果插入到目标表中，结果集中记录的字段数和字段类型要与目标表完全一致。INSERT INTO 表名列名列表 SELECT 列名列表 FROM 表名8.1.5 REPLACE语句使用REPLACE语句也可以将一条或多条记录插入表中，或将一个表中的结果集插入到目标表中。REPLACE INTO 表名 VALUES（值列表）使用REPLACE语句添加记录时，如果新记录的主键值或者唯一性约束的字段值与已经有记录相同，则已有记录被删除后再添加新记录。

8、修改数据28.2 修改数据修改数据是更新表中已经存在的记录。MySQL中，通过UPDATE语句来修改数据。UPDATE表名SET字段名1取值1，字段名2取值2， 字段名n取值n WHERE条件表达式 【例8-8】更新student表中sno值为1418855243的记录。将sname字段的值变为李壮。将sbirth字段的值变为1996-03-23。UPDATE studentSET sname=李凯, sbirth=1996-03-23WHERE sno=1418855243 ; 使用UPDATE语句会更新所有满足条件的记录。但在MySQL中是需要一条一条的执行。8.1.2 为表的指定字段插入

9、数据【例8-9】更新student表中sname值为李凯的记录。将sbirth字段的值变为“1997-01-01”。将ssex字段的值变为“女”。删除数据3删除数据是删除表中已经存在的记录。通过这种方式可以删除表中不再使用的记录。例如，学生表中某个学生退学了，这就需要从学生表中删除该同学的信息。MySQL中，通过DELETE语句来删除数据。如果完全清除某一个表可以使用truncate。8.3.1 使用DELETE删除表数据MySQL中，DELETE语句的基本语法形式如下：DELETE FROM表名 WHERE条件表达式 【例8-10】删除student表中sno值为1418855243的记录。

10、8.3.1 使用DELETE删除表数据【例8-11】删除student表中sclass的值为商务1301的记录。DELETE FROM student WHERE sclass=商务1301;DELETE语句可以同时删除多条记录。DELETE语句中如果不加上“WHERE条件表达式”，数据库系统会删除指定表中的所有数据。请谨慎使用。8.3.2 使用TRUNCATE清空表数据TRUNCATE用于完全清空一个表，基本语法形式如下：TRUNCATE TABLE 表名【例8-12】清除SC表TRUNCATE TABLE SC;8.3.2 使用TRUNCATE清空表数据TRUNCATE TABLE：删除内

11、容、释放空间但不删除定义。DELETE TABLE:删除内容不删除定义，不释放空间。DROP TABLE：删除内容和定义，释放空间。TRUNCATE，DELETE，DROP放在一起比较：单表查询48.4.2 简单查询为了演示一下的查询，我们需要创建一下几张表：8.4.2 简单查询为了演示一下的查询，我们需要创建一下几张表：8.4.2 简单查询为了演示一下的查询，我们需要创建一下几张表：8.4.2 简单查询为了演示一下的查询，我们需要创建一下几张表：8.4.2 简单查询1. 查询所有字段查询所有字段是指查询表中的所有字段的数据，有两中方式：一种是列出表中的所有字段。另一种是使用通配符*来查询 方

12、式1：使用 SELECT zno,sclass ,sno,sname,ssex,sbirth FROM student;返回的结果字段的顺序和SELECT语句中指定的顺序一致 【例8-13】查询学生的所有信息8.4.2 简单查询方式2：使用SELECT * FROM student;返回的结果字段的顺序是固定的，和建立表时指定的顺序一致。【例8-13】查询学生的所有信息8.4.2 简单查询2. 指定字段查询有些时候并不需要将表中的所有字段都显示出来，只需要查询需要的字段就可以，这就需要我们在SELECT中指定需要的字段。SELECT sno,sname FROM student;【例8-14】

13、查询学生的学号和姓名。8.4.2 简单查询3. 避免重复数据查询DISTINCT关键字可以去除重复的查询记录。和DISTINCT相对的是all关键字，即显示所有的记录（包括重复的），而all关键字是系统默认的，可以省略不写。SELECT sclass FROM student;SELECT DISTINCT sclass FROM student;【例8-15】查询在student表中的班级。8.4.2 简单查询【例8-15】查询在student表中的班级。可以看出，用DISTINCT关键字后，结果中重复的记录只保留一条。8.4.2 简单查询4. 为表和字段取别名SELECT ALL | DI

14、STINCT AS 别名 , AS 别名 . . .FROM 别名 , 别名 . . .【例8-16】查询学生的学号，成绩。并指定返回的结果中的列名为学号,成绩，而不是sno和grade。SELECT sno 学号,grade 成绩 FROM sc;8.4.2 简单查询【例8-17】查询sc表中学生的的成绩提高10%，对显示后的成绩列，显示为“修改后成绩”。SELECT sno 学号,grade 成绩,grade*1.1 AS 修改后成绩 FROM sc;8.4.3 条件查询条件查询主要使用关键字WHERE指定查询的条件WHERE子句常用的查询条件有很多种，如下表所示。条件表达式中设置的条件越

15、多，查询出来的记录就会越少。因为，设置的条件越多，查询语句的限制就更多，能够满足所有条件的记录就更少。为了使查询出来的记录正是自己想要查询的记录，可以在WHERE语句中将查询条件设置得更加具体。8.4.3 条件查询1.带关系运算符和逻辑运算符的查询MYSQL中，可以通过关系运算符和逻辑运算符来编写“条件表达式”。MySQL支持的比较运算符有 ， ，!= ，=()，= ，90;【例8-18】查询成绩大于90分学生的学号和成绩。8.4.3 条件查询【例8-19】查询成绩在70分到80分之间（包含70分和80分）学生的学号和成绩。SELECT sno 学号,grade 成绩 FROM sc WHER

16、E grade=70 AND grade=80;8.4.3 条件查询1.IN关键字的查询IN关键字可以判断某个字段的值是否在指定的集合中，如果字段的值在集合中，则满足查询条件，该记录将被查询出来；如果不在集合中，则不满足查询条件。其中，NOT是可选参数，加上NOT表示不在集合内满足条件：字符型元素要加上单引号。NOT IN (元素1,元素2, 元素3, . . . )【例8-20】查询成绩在集合(65，75，85，95)中的学生的学号和成绩8.4.3 条件查询2.带BETWEEN AND关键字的查询BETWEEN AND关键字可以判断读某个字段的值是否在指定的范围内，如果在，则满足条件，否则不

17、满足。其中，“NOT”是可选参数，加上NOT表示不在指定范内满足条件；“取值 1”表示范围的起始值；“取值2”表示范围的终止值。 NOT BETWEEN取值1 AND取值2【例8-21】查询成绩在70分到80分之间（包含70分和80分）学生的学号和成绩。8.4.3 条件查询【例8-22】使用BETWEEN AND关键字进行查询，查询条件是sno字段的取值从14188552401418855242。SELECT * FROM student WHERE sno BETWEEN 1411855240 AND 1418855242; NOT BETWEEN AND的取值范围是小于“取值1”，而大于“

18、取值2”8.4.3 条件查询【例8-23】使用NOT BETWEEN AND关键字查询student表。查询条件是sno字段的取值不在14188552401418855242之间。SELECT * FROM student WHERE sno NOT BETWEEN 1411855240 AND 1418855242;技巧：BETWEEN AND和NOT BETWEEN AND关键字在查询指定范围的记录时很有用。8.4.3 条件查询3.带IS NULL关键字的空值查询（数据有问题）IS NULL关键字可以用来判断字段的值是否为空值（NULL）.如果字段值为空值，则满足查询条件，否则不满足。IS

19、 not NULL【例8-24】查询已经分专业的学生的学号和姓名。查询条件：已经分专业说明专业号不为空，即 zno IS NOT NULL;SELECT sno,sname,zno FROM student WHERE zno IS NOT NULL;8.4.3 条件查询IS NULL 是一个整体，不能将IS换成“=”。如果将IS换成“=”，将查询不到我们想要的结果，如下：zno=NULL表示要查询的zno的值是字符串“NULL”，而不是空值。当然IS NOT NULL 中的IS NOT也不可以换成!= 或者 。8.4.3 条件查询4.带LIKE关键字的查询 LIKE关键字可以匹配字符串是否相

20、等。如果字段的值与指定的字符串相匹配，则满足条件（1）“%”可以代表任意长度的字符串，长度可以为0。（2）“_”只能表示单个字符。 NOT LIKE “字符串”;【例8-25】使用LIKE关键字来匹配一个完整的字符串 蓝梅。SELECT * FROM student WHERE sname LIKE 蓝梅;此处的LIKE与等号（）是等价的。如果字符串中包含了通配符，两者则不等价8.4.3 条件查询【例8-26】使用LIKE关键字来匹配带有通配符%的字符串李%。SELECT语句的代码如下：SELECT * FROM student WHERE sname LIKE 李%; 【例8-27】使用LI

21、KE关键字来匹配带有通配符_的字符串。SELECT * FROM student WHERE sname LIKE 李_ _; 8.4.3 条件查询【例8-28】使用NOT LIKE关键字来查询不是姓李的所有人的记录。SELECT * FROM student WHERE sname NOT LIKE 李%;假若LIKE 字符串中的要匹配的字符串奔上就含有通配符百分号%或者下划线“_”,那么我们可以使用ESCAPE短语，对通配符进行转移。8.4.4 高级查询1. 分组查询GROUP BY关键字可以将查询结果按某个字段或多个字段进行分组。字段中值相等的为一组。其中，“字段名”是指按照该字段的值进

22、行分组；“HAVING条件表达式”用来限制分组后的显示，满足条件表达式的结果将被显示；WITH ROLLUP关键字将会在所有记录的最后加上一条记录。该记录是上面所有记录的总和。GROUP BY字段名 HAVING条件表达式 WITH ROLLUP 【例8-29】按student表的ssex字段进行分组查询。SELECT * FROM student GROUP BY ssex; 8.4.3 条件查询【例8-30】按student表的ssex字段进行分组查询。然后显示记录数大于等于10的分组（COUNT()用来统计记录的条数）。SELECT ssex,COUNT(ssex)FROM studen

23、tGROUP BY ssex HAVING COUNT(ssex)=10;说明：“WHERE条件表达式”作用于表或者视图，是表和视图的查询条件。“HAVING条件表达式”作用于分组后的记录，用于选择满足条件的组。 8.4.4 高级查询2. 对查询结果排序从表中查询出来的数据可能是无序的，或者其排列顺序不是用户所期望的顺序。ASC参数表示按升序的顺序进行排序；DESC参数表示按降序的顺序进行排序。ORDER BY字段名ASCDESC 【例8-31】查询student表中所有记录，按照zno字段进行排序。SELECT * FROM student ORDER BY zno ; 注意：按升序排序时，

24、含空值的记录将最先显示。8.4.3 条件查询【例8-32】查询student表中所有记录，按照zno字段的升序方式和sno字段的降序方式进行排序。SELECT * FROM student ORDER BY zno ASC,sno DESC; 8.4.4 高级查询3. 限制查询结果数量当使用SELECT语句返回的结果集中行数很多时，为了便于用户对结果数据的浏览和操作，可以使用LIMIT子句来限制被SELECT语句返回的行数。其中，offset为可选项，默认为数字0，用于指定返回数据的第一行在SELECT语句结果集中的偏移量，其必须是非负的整数常量。row_count用于指定返回数据的行数LIM

25、IT offset， row_count | row_count OFFSET offset 【例8-33】在student表中查找从第3名同学开始的3位学生的信息。SELECT * FROM student ORDER BY sno LIMIT 2,3; 8.4.4 高级查询4. 聚合函数集合函数包括COUNT()、SUM()、 AVG()、MAX()和MIN()。SUM、AVG、MAX和MIN都适用以下规则：如果某个给定行中的一列仅包含NULL值，则函数的值等于NULL值。如果一列中的某些值为NULL值，则函数的值等于所有非NULL值的平均值除以非NULL值的数量（不是除以所有值）。对于必

26、须计算的SUM和AVG函数，如果中间结果为空，在函数的值等于NULL值。8.4.4 高级查询（1）COUNT()函数COUNT用于统计组中满足条件的行数或总行数COUNT（ ALL |DISTINCTI*）【例8-34】使用COUNT()函数统计student表的记录数。SELECT COUNT(*) AS 学生总人数 FROM student;8.4.4 高级查询【例8-35】使用COUNT()函数统计student表不同zno值的记录数。COUNT()函数与GROUP BY关键字一起使用。SELECT zno AS 专业号,COUNT(*) AS 专业人数FROM studentGROUP

27、 BY zno; 8.4.4 高级查询（2）SUM()函数SUM()函数是求和函数。使用SUM()函数可以求表中某个字段取值的总和。例如，可以用SUM()函数来求学生的总成绩。【例8-36】使用SUM()函数统计sc表中学号为1414855328的同学的总成绩。SELECT sno 学号,SUM(grade) 总成绩FROM scWHERE sno=1414855328; SUM()函数通常和GROUP BY关键字一起使用。这样可以计算出不同分组中某个字段取值的总和。8.4.4 高级查询【例8-37】将sc表按照sno字段进行分组。然后，使用SUM()函数统计各分组的总成绩。SELECT sn

28、o 学号,SUM(grade) 总成绩FROM scGROUP BY sno ; 注意：SUM()函数只能计算数值类型的字段。包括INT类型、FLOAT类型、DOUBLE类型、DECIMAL类型等。使用SUM()函数计算字符类型字段时，计算结果都为0。8.4.4 高级查询（3）AVG()函数AVG()函数是求平均值的函数。使用AVG()函数可以求出表中某个字段取值的平均值。例如，可以用AVG()函数来求平均年龄，也可以使用AVG()函数来求学生的平均成绩。【例8-38】使用AVG()函数计算sc表中平均成绩。SELECT AVG(grade) 平均成绩FROM sc; 8.4.4 高级查询【例

29、8-39】使用AVG()函数计算sc表中不同科目的平均成绩。SELECT cno 课程号 ,AVG(grade) 平均成绩 FROM scGROUP BY cno; AVG()函数与GROUP BY关键字结合后可以灵活的计算平均值。8.4.4 高级查询（4）MAX()函数MAX()函数是求最大值的函数。使用MAX()函数可以求出表中某个字段取值的最大值。例如，可以用MAX()函数来查询最大年龄，也可以使用MAX()函数来求各科的最高成绩。【例8-40】使用MAX()函数查询sc表中不同科目的最高成绩。SELECT cno课程号,MAX(grade)最高成绩FROM scGROUP BY cno

30、; MAX()函数与GROUP BY关键字结合后可以查询出不同分组的最大值。8.4.4 高级查询MAX()不仅仅适用于数值类型，也适用于字符类型。【例8-41】句使用MAX()函数查询student表中sname字段的最大值。SELECT语句如下：SELECT MAX(sname) FROM student; MAX()函数是使用字符对应的ASCII码进行计算的。8.4.4 高级查询（5）MIN()函数MIN()函数是求最小值的函数。使用MIN()函数可以求出表中某个字段取值的最小值。例如，可以用MIN()函数来查询最小年龄，也可以使用MIN()函数来求各科的最低成绩。【例8-42】使用MIN

31、()函数查询sc表中不同科目的最低成绩。SELECT cno 课程号,MIN(grade) 最低成绩FROM scGROUP BY cno ; 多表查询58.5.1 内连接查询内连接查询是最常用的一种查询，也成为等同查询，就是在表关系的笛卡尔积数据记录中，保留表关系中所有相匹配的数据，而舍弃不匹配的数据。按照匹配条件可以分为自然连接、等值连接和不等值连接。1.等值连接（inner join）用来连接两个表的条件称为连接条件。如果连接条件中的连接运算符是=时，称为等值连接。8.5.1 内连接查询【例8-43】对选修表和课程表做等值连接(返回的结果限制在4条以内)SELECT * FROM sc

32、INNER JOIN course ON o = oLIMIT 4;8.5.1 内连接查询2.自然连接（natural join）自然连接操作就是表关系的笛卡尔积中选取满足连接条件的行。首先根据表关系中相同名称的字段进行记录匹配，然后去掉重复的字段。还可以理解为在等值连接中把目标列种重复的属性列去掉则为自然连接。【例8-44】对选修表和课程表做自然连接(返回的结果限制在4条以内)SELECT *FROM sc NATURAL JOIN courseLIMIT 4;8.5.1 内连接查询3.不等值连接（inner join）在WHERE字句中用来连接两个表的条件称为连接条件。如果连接条件中的连接

33、运算符是=时，称为等值连接。如果是其他的运算符，则是不等值连接【例8-45】对选修表和课程表做不等值连接(返回的结果限制在4条以内)SELECT * FROM sc INNER JOIN course ON o != oLIMIT 4;8.5.2 外连接查询外连接可以查询两个或两个以上的表，外连接查询和内连接查询非常的相似，也需要通过指定字段进行连接，当该字段取值相等时，可以查询出该表的记录。而且，该字段取值不相等的记录也可以查询出来。外连接可分为左连接和右连接。基本语法如下：SELECT 字段表 FROM 表1 LEFT | RIGHT OUTER JOIN 表2 ON 表1.字段=表2.字段8.5.2 外连接查询1.左外连接（left join）左外连接的结果集中包含左表（JOIN关键字左边的表）中所有的记录，然后左表按照连接条件与右表进行连接。如果右表中没有满足连接条件的记录，则结果集中右表中的相应行数据填充为NULL。【例8-46】利用左连接方式 查询课程表和选修表8.5.2 外连接查询2