《（本科）第8章MySQL数据操作管理ppt课件.pptx》由会员分享，可在线阅读，更多相关《（本科）第8章MySQL数据操作管理ppt课件.pptx（95页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、课程主讲人：（本科）第8章-MySQL数据操作管理ppt课件中国农业大学 李辉p插入数据p修改数据p删除数据p查询：单表查询，多表查询p知识点小结p本章实验pMySQL对数据的操作主要有： 添加：向数据库表中添加不存在的记录 删除：删除数据库中已存在的记录 修改：对已经存在的记录进行更新 查询：指数据库管理系统按照数据用户指定的条件，从数据库中相关表中找到满足条件的记录过程。p 本章将主要介绍如何查询MySQL数据库中的数据，还将介绍如何向数据库中添加记录，以及删除和修改记录。p插入数据插入数据p修改数据p删除数据p查询：单表查询，多表查询p知识点小结p本章实验p插入数据是向表中插入新的记录。
2、通过这种方式可以为表中增加新的数据。p在MySQL中，通过INSERT语句语句来插入新的数据。 可以同时为表的所有字段所有字段插入数据 可以为表的指定字段指定字段插入数据 还可以同时插入多条记录多条记录p两种方式： 不指定不指定具体的字段名语法格式：INSERT INTO 表名 VALUES(值1，值2，值n) 列出所有字段所有字段语法格式： INSERT INTO表名(字段名1，字段名2，字段名n)VALUES(值1，值2，值n);其中，“字段名n”参数表示表中的字段名称，此处必须列出表的所有字段的名称;“值n”参数表示每个字段的值，每个值与相应的字段对应。p 为表的指定字段插入数据: 语法
3、格式： INSERT INTO表名(字段名1，字段名2，字段名n) VALUES(值1，值2，值n) 其中，“字段名m”参数表示表中的字段名称，此处指定表的部分字段的名称；“值m”参数表示指定字段的值，每个值与相应的字段对应相应的字段对应。 没有赋值的字段，数据库系统会为其插入默认值默认值。这个默认值是在创建表的时候定义的。 可以随意的随意的设置字段的顺序，而不需要按照表定义时的顺序。p 同时插入多条记录多条记录，是指一个INSERT语句插入多条记录。 语法格式： INSERT INTO表名(字段名列表) VALUES(取值列表1)，(取值列表2)，(取值列表n) 其中，“表名”参数指明向哪个
4、表中插入数据；“字段名列表”参数是可选参数，指定哪些字段插入数据，没有指定字段时向所有字段插入数据；“取值列表n”参数表示要插入的记录，每条记录之间用逗号逗号隔开。 如果插入的记录很多时，一个INSERT语句插入多条记录的方式的速度会比较快。p 从目标表目标表中插入值 使用INSERT INTO SELECT可以从一个表或者多个表向目标表中插入记录。 SELECT语句中返回的是一个查询到的结果集，INSERT语句将这个结果插入到目标表中，结果集中记录的字段数和字段类型要与目标表完全一致。 语法结构：INSERT INTO 表名列名列表 SELECT 列名列表 FROM 表名 p 使用REPLA
5、CE语句也可以将一条或多条记录插入表中一条或多条记录插入表中，或将一个表中的结果集插入到目标表中一个表中的结果集插入到目标表中。p 其语法格式：REPLACE INTO 表名 VALUES（值列表）p 使用REPLACE语句添加记录时，如果新记录的主键值或者唯一性约束的字段值与已经有记录相同，则已有记录被删除后再添加新记录。p插入数据p修改数据修改数据p删除数据p查询：单表查询，多表查询p知识点小结p本章实验p 修改数据是更新表中已经存在的记录。通过UPDATE语句语句来修改数据。p UPDATE语句的基本语法形式如下： 语法格式： UPDATE表名SET字段名1取值1，字段名2取值2， 字段
6、名n取值n WHERE条件表达式 其中，“字段名n”参数表示需要更新的字段的名称；“取值n”参数表示为字段更新的新数据；“条件表达式”参数指定更新满足条件的记录。p插入数据p修改数据p删除数据删除数据p查询：单表查询，多表查询p知识点小结p本章实验p 删除数据是删除表中已经存在的记录记录。 通过DELETE语句来删除数据。基本语法：DELETE FROM表名 WHERE条件表达式 其中，“表名”参数指明从哪个表中删除数据；“ WIHERE条件表达式”指定删除表中的哪些数据。如果没有该条件表达式，数据库系统就会删除表中的所有数据。 使用truncate完全清除完全清除某一个表。 基本语法：Tru
7、ncate table 表名p TRUNCATE TABLE 与DELETE的区别： TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快速度快，且使用的系统和事务日志资源少系统和事务日志资源少。 TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。 TRUNCATE，DELETE，DROP放在一起比较： TRUNCATE TABLE：删除内容、释放空间但不删除定义不删除定义。 DELETE TABLE：删除内容不删除定义不
8、删除定义，不释放空间不释放空间。 DROP TABLE：删除内容和定义，释放空间。p插入数据p修改数据p删除数据p查询：单表查询，多表查询查询：单表查询，多表查询p知识点小结p本章实验p SELECT语法： SELECT语句是在所有数据库操作中使用频率最高的SQL语句。 SELECT语句的语法格式如下:SELECT 字段列FROM WHERE GROUP BY HAVING ORDER BY ASC| DESC LIMIT 子句 其中内的内容是可选的p SELECT子句： 指定要查询的列名称，列与列之间用逗号隔开。 还可以为列指定新的别名别名，显示在输出的结果中。 ALL关键字表示显示所有的行
9、，包括重复行重复行，是系统默认的系统默认的 DISTINCT表示显示的结果要消除重复的行消除重复的行。p FROM子句：指定要查询的表，可以指定两个以上的表，表与表之间用逗号隔开。p WHERE子句：指定要查询的条件。 如果有WHERE子句，就按照“条件表达式”指定的条件进行查询； 如果没有WHERE子句，就查询所有记录。p GROUP BY ：子句用于对查询结构进行分组。 按照“列名1”指定的字段进行分组； 如果GROUP BY子句后带着HAVING关键字关键字，那么只有满足“条件表达式2”中指定的条件的才能够输出。 GROUP BY子句通常和COUNT()、SUM()等聚合函数聚合函数一起
10、使用。p HAVING子句：指定分组的条件，通常放Group by字句之后p ORDER BY子句：用于对查询结果的进行排序排序。p 排序方式由ASC和DESC两个参数指出；p ASC参数表示按升序进行排序。默认情况下是ASC。p DESC参数表示按降序的顺序进行排序。升序表示值按从小到大的顺序排列。p LIMIT 子句：限制查询的输出结果的行数行数。p 演示查询，创建几张表： 专业表（specialty） 课程表（course） 学生表（student） 选修表（sc）1. 查询所有字段：查询表中的所有字段的数据 两种方式： 一种是列出表中的所有字段 另一种是使用通配符通配符*来查询 例：查
11、询学生的所有信息 方式1：使用 SELECT zno,sclass ,sno,sname,ssex,sbirth FROM student; （返回的结果字段的顺序和SELECT语句中指定的顺序一致。） 方式2：使用SELECT * FROM student;（返回的结果字段的顺序是固定的，和建立表时指定的顺序一致。）方法1：方法2：2. 指定字段查询 例：查询学生的学号和姓名。SELECT sno,sname FROM student;3. 避免重复数据查询避免重复数据查询 DISTINCT关键字可以去除重复的查询记录。和DISTINCT相对的是all关键字，即显示所有的记录（包括重复的），
12、而all关键字是系统默认的，可以省略不写。 例：查询在student表中的班级。 SELECT sclass FROM student; SELECT DISTINCT sclass FROM student;3. 避免重复数据查询（续）4. 为表和字段取别名别名 当查询数据时，MySQL会显示每个输出列的名称。默认的情况下，显示的列名是创建表时定义的列名。SELECT ALL | DISTINCT AS 别名 , AS 别名 . . .FROM 别名 , 别名 . . . 例：查询学生的学号，成绩。并指定返回的结果中的列名为学号,成绩，而不是sno和grade。 SELECT sno 学号,
13、grade 成绩 FROM sc;4. 为表和字段取别名（续） 在使用SELECT语句对列进行查询时，在结在结果集中可以输出对列果集中可以输出对列值计算后的值。值计算后的值。 例：查询sc表中学生的的成绩提高10%，对显示后的成绩列，显示为“修改后成绩”。 S E L E C T s n o , grade,grade*1.1 AS 修改后成绩 FROM sc;p 条件查询主要使用关键字WHERE指定查询的条件p WHERE子句常用的查询条件如下：p “”表示不等于，其作用等价于“!=”；“!”表示不大于，等价于“=”；“!=”；BETWEEN AND指定了某字段的取值范围；“IN”指定了某字
14、段的取值的集合；IS NULL用来判断某字段的取值是否为空；AND和OR用来连接多个查询条件。2.带IN关键字的查询 IN关键字可以判断某个字段的值是否在指定的集合集合中，如果字段的值在集合中，则满足查询条件，该记录将被查询出来；如果不在集合中，则不满足查询条件。 语法格式 NOT IN (元素1,元素2, 元素3, . . . ) 其中，NOT是可选参数，加上NOT表示不在集合内满足条件：字符型元素要加上单引号。例：查询成绩在集合(65，75，85，95)中的学生的学号和成绩SELECT sno,grade FROM sc WHERE grade In (65，75，85，95);3.带BE
15、TWEEN AND关键字的查询 BETWEEN AND关键字可以判断读某个字段的值是否在指定的范围内，如果在，则满足条件，否则不满足。 语法规则： NOT BETWEEN取值1 AND取值2 其中，“NOT”是可选参数，加上NOT表示不在指定范内满足条件；“取值 1”表示范围的起始值；“取值2”表示范围的终止值。例：查询成绩在70分到80分之间（包含70分和80分）学生的学号和成绩。SELECT sno,grade FROM sc WHERE grade BETWEEN 75 AND 80;4.带IS NULL关键字的空值查询 IS NULL关键字可以用来判断字段的值是否为空值（NULL）.如
16、果字段值为空值，则满足查询条件，否则不满足。语法规则：IS not NULL IS NULL 是一个整体整体，不能将不能将IS换成换成“=”。 IS NOT NULL 中的IS NOT也不可以换成也不可以换成!= 或者或者 。例：查询还没有分专业的学生的学号和姓名。查询条件：没分专业说明专业号为空，即 zno IS NULL;SELECT sno,sname,zno FROM student WHERE zno IS NULL;5.带LIKE关键字的查询 LIKE关键字可以匹配字符串是否相等。如果字段的值与指定的字符串相匹配，则满足条件，否则不满足。语法规则: NOT LIKE “字符串”;
17、通配符“%”与“_”的区别：（1）“%”可以代表任意长度任意长度的字符串，长度可以为0。例如，b%k表示以字母b开头，以字母k结尾的任意长度的字符串。该字符串可以代表bk、buk、book、break、bedrock等字符串。（2）“%”只能表示单个字符单个字符。例如，b_ k表示以字母b开头，以字母k结尾的3个字符。中间的“_”可以代表任意一个字符。字符串可以代表bok、bak和buk等字符串。例：使用LIKE关键字来匹配一个完整的字符串 蓝梅。SELECT * FROM student WHERE sname LIKE 蓝梅;此处的LIKE与等号（）是等价的与等号（）是等价的。可以直接换成
18、“”，查询结果是一样的。SELECT * FROM student WHERE sname =马小梅;例：使用LIKE关键字来匹配带有通配符%的字符串李%。SELECT语句的代码如下：SELECT * FROM student WHERE sname LIKE 李%; 例：使用LIKE关键字来匹配带有通配符_的字符串。SELECT * FROM student WHERE sname LIKE 李_ _; 需要匹配的字符串需要加引号。可以是单引号，也可以是双引号。如果要匹配姓张且名字只有两个字的人的记录，“张”字后面必须有两个“_”符号。因为一个汉字是两个字符，而一个“_”符号只能代表一个字符
19、。因此，匹配的字符串应该为“张_ _”，必须是两个“_”符号。p 假若LIKE 字符串中的要匹配的字符串奔上就含有通配符百分号%或者下划线“_”,那么我们可以使用ESCAPE短语，对通配符进行转移。例如ESCAPE 表示为转码字符。这样匹配串中紧跟在后面的字符“_”不在具有通配符的含义，转义为普通的“_”字符。p 如果要查询以“DB_”开头的，且倒数第三个字符为i的课程的详细情况。可使用如下语句：SELECT * FROM course WHERE cname like DB_%i_ _ESCAPE ; NOT LIKE表示字符串不匹配不匹配的情况下满足条件。例：使用NOT LIKE关键字来查
20、询不是姓李的所有人的记录。SELECT * FROM student WHERE sname NOT LIKE 李%;p 高级查询： 分组查询 对查询结果进行排序 限制查询结果数量 聚合函数p GROUP BY关键字可以将查询结果按某个字段或多个字段某个字段或多个字段进行分组。字段中值相等的为一组。p 语法格式： GROUP BY字段名 HAVING条件表达式 WITH ROLLUP p 其中，“字段名”是指按照该字段的值进行分组；“HAVING条件表达式”用来限制分组后的显示，满足条件表达式的结果将被显示；WITH ROLLUP关键字将会在所有记录的最后加上一条记录。该记录是上面所有记录的总
21、和。p 如果单独使用GROUP BY关键字，查询结果只显示一个分组的一条记录。p GROUP BY关键字加上“HAVING条件表达式条件表达式”，可以限制输出的结果。只有满足条件表达式的结果才会显示。p 例：按student表的ssex字段进行分组查询。SELECT * FROM student GROUP BY ssex; p 例：按student表的ssex字段进行分组查询。然后显示记录数大于等于10的分组（COUNT()用来统计记录的条数）。SELECT ssex,COUNT(ssex)FROM studentGROUP BY ssex HAVING COUNT(ssex)=10; p
22、说明：“HAVING条件表达式条件表达式”与与“WHERE条件表达式条件表达式”都是都是用来限制显示的。但是，两者起作用的地方不一样。用来限制显示的。但是，两者起作用的地方不一样。“WHERE条件表达式条件表达式”作用于表或者视图，是表和视图的查询条件。作用于表或者视图，是表和视图的查询条件。“HAVING条件表达式条件表达式”作用于分组后的记录，用于选择满足作用于分组后的记录，用于选择满足条件的组。条件的组。p 使用ORDER BY关键字对记录进行排序。p 语法格式： ORDER BY字段名ASCDESC p 其中，“字段名”参数表示按照该字段进行排序； ASC参数表示按升序的顺序进行排序；
23、 DESC参数表示按降序的顺序进行排序。 默认的情况下，按照ASC方式进行排序。p 例：查询student表中所有记录，按照zno字段进行排序。SELECT * FROM student ORDER BY zno ; p 注意：如果存在一条记录zno字段的值为空值空值(NULL)时，这条记录将显示为第一条记录第一条记录。因为，按升序升序排序时，含空值的记录将最先最先显示。可以理解为空值是该字段的最小值。而按降序降序排列时，zno字段为空值的记录将最后最后显示。p MySQL中，可以指定按多个字段多个字段进行排序。例如，可以使student表按照zno字段和sno字段进行排序。排序过程中，先按照
24、zno字段进行排序。遇到zno字段的值相等的情况时，再把zno值相等的记录按照sno字段进行排序。p 例：p 查询student表中所有记录，按照zno字段的升序方式和sno字段的降序方式 进 行 排 序 。SELECT语句如下：p SELECT * FROM student ORDER BY zno ASC,sno DESC; p 当使用SELECT语句返回的结果集中行数很多时，为了便于用户对结果数据的浏览和操作，可以使用LIMIT子句子句来限制被SELECT语句返回的行数。p 语法格式： LIMIT offset， row_count | row_count OFFSET offset p
25、 其中，offset为可选项，默认为数字0，用于指定返回数据的第一行在SELECT语句结果集中的偏移量，其必须是非负的整数常量。注意，SELECT语句结果集中第一行（初始行）的偏移量为0而不是1。row_count用于指定返回数据的行数，其也必须是非负的整数常量。若这个指定行数大于实际能返回的行数时，MySQL将只返回它能返回的数据行。row_count OFFSET offset是MySQL5.0开始支持的另外一种语法，即从第offset+1行开始，取row_count行。p 例：在student表中查找从第3名同学开始的3位学生的信息。SELECT * FROM student ORDER
26、 BY sno LIMIT 2,3; p 集合函数包括COUNT()、SUM()、 AVG()、 MAX()和MIN()。其中， COUNT()用来统计记录的条数； SUM()用来计算字段的值的总和； AVG()用来计算字段的值的平均值； MAX()用来查询字段的最大值； MIN()用来查询字段的最小值。p GROUP BY关键字通常需要与集合函数集合函数一起使用。如果某个给定行中的一列仅包含NULL值，则函数的值等于NULL值。如果一列中的某些值为NULL值，则函数的值等于所有非NULL值的平均值除以非NULL值的数量（不是除以所有值）。对于必须计算的SUM和AVG函数，如果中间结果为空，在
27、函数的值等于NULL值。（1）COUNT()函数 COUNT用于统计组中满足条件的行数或总行数。 格式如下：COUNT（ ALL I DISTINCTI*） ALL、DISTINCT的含义及默认值与SUM/AVG函数相同。 选择*时将统计总行数。 COUNT用于计算列中非NULL值的数量。 如果要统计student表中有多少条记录，可以使用COUNT()函数。p 例：使用COUNT()函数统计student表不同zno值的记录数。COUNT()函数与GROUP BY关键字一起使用。SELECT zno,COUNT(*) AS 专业人数FROM studentGROUP BY zno; （2）S
28、UM()函数p SUM()函数是求和函数。使用SUM()函数可以求表中某个字段取值的总和。p 例：使用SUM()函数统计sc表中学号为1414855328的同学的总成绩。SELECT sno,SUM(grade)FROM scWHERE sno=1414855328; p SUM()函数通常和GROUP BY关键字一起使用。这样可以计算出不同分组中某个字段取值的总和。p 例：将sc表按照sno字段进行分组。然后，使用SUM()函数统计各分组的总成绩。SELECT sno,SUM(grade)FROM scGROUP BY sno ; p SUM()函数只能计算数值类型的字段。p INT、FLO
29、AT、DOUBLE、DECIMAL等（3）AVG()函数p AVG()函数是求平均值的函数。使用AVG()函数可以求出表中某个字段取值的平均值。p 例：使用AVG()函数计算sc表中平均成绩。SELECT AVG(grade)FROM sc;p 例：使用AVG()函数计算sc表中不同科目的平均成绩。SELECT cno,AVG(grade)FROM scGROUP BY cno; （4）MAX()函数p MAX()函数是求最大值的函数。使用MAX()函数可以求出表中某个字段取值的最大值。p MAX()不仅仅适用于数值数值类型，也适用于字符字符类型。p MAX()函数是使用字符对应的ASCII码
30、码进行计算的。 在MySQL表中，字母a最小，字母z最大。因为a的ASCII码值最小。在使用MAX()函数进行比较时，先比较第一个字母。如果第一个字母相等时，再继续往下一个字母进行比较。（5）MIN()函数MIN()函数是求最小值的函数。使用MIN()函数可以求出表中某个字段取值的最小值。p 例：使用MAX()函数查询sc表中不同科目的最高成绩。SELECT sno,cno,MAX(grade)FROM scGROUP BY cno; p 例：使用MAX()函数查询student表中sname字段的最大值。SELECT语句如下：SELECT MAX(sname) FROM student; p
31、 连接查询：一个查询语句中显示多张表的数据，即：多表数据记录连接查询。 内连接查询和外连接查询。 主要区别在于，内连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。 最常用的是内连接查询。p 子查询子查询操作：代替连接查询，具有更高的操作效率。p 内连接查询是最常用的一种查询，也成为等同查询，就是在表关系的笛卡尔积数据记录中，保留表关系中所有相匹配的数据，而舍弃不匹配的数据。p 按照匹配条件可以分为 自然连接 等值连接 不等值连接p 用来连接两个表的条件称为连接条件。p 如果连接条件中的连接运算符是=时，称为等值连接。p 例：对选修表和课程表做等值连接(返回的结果限制在4条以内
32、)p 自然连接操作就是表关系的笛卡尔积中选取满足连接条件的行。p 具体过程是，首先根据表关系中相同名称的字段进行记录匹配，然后去掉重复的字段。p 还可以理解为在等值连接中把目标列种重复的属性列去掉则为在等值连接中把目标列种重复的属性列去掉则为自然连接。自然连接。p 例：对选修表和课程表做自然连接(返回的结果限制在4条以内)SELECT *FROM sc natural join courselimit 4;p 例：对选修表和课程表做自然连接(返回的结果限制在4条以内)p 自然连接结果：p 用来连接两个表的条件称为连接条件。p 如果连接条件中的连接运算符是=时，称为等值连接。p 如果是其他的运算
33、符其他的运算符，则是不等值连接不等值连接。p 例：对选修表和课程表做不等值连接(返回的结果限制在4条以内)SELECT *FROM sc inner join courseon o!=olimit 4;p 例：对选修表和课程表做不等值连接(返回的结果限制在4条以内)p 不等值连接结果：p 外连接可以查询两个或两个以上的表，外连接查询和内连接查询非常的相似，也需要通过指定字段进行连接，当该字段取值相等时，可以查询出该表的记录。而且，该字段取值不相等的记录也可以查询出来。p 外连接可分为左连接左连接和右连接右连接。基本语法如下：SELECT 字段表 FROM 表1 LEFT | RIGHT OUT
34、ER JOIN 表2 ON 表1.字段=表2.字段p 左外连接的结果集中包含左表（JOIN关键字左边的表）中所有的记录，然后左表按照连接条件与右表进行连接。如果右表中没有满足连接条件的记录，则结果集中右表中的相应行数据填充为NULL。p 例：利用左连接方式 查询课程表和选修表SELECT o, ame,o,sc.snoFROM course left join scon o=olimit 10;p 例：利用左连接方式查询课程表和选修表p 右外连接的结果集中包含满足连接条件的所有数据和右表（JOIN关键字右边的表）中不满足条件的数据，左表中左表中的相应行数据为NULL。p 例：利用右连接方式查询
35、课程表和选修表SELECT o, ame,o,sc.snoFROM course right join scon o=olimit 10;p 例：利用右连接方式查询课程表和选修表p 子查询：查询时，需要用到另外一个另外一个SELECT语句语句的结果。p 通过子查询，可以实现多表之间的查询多表之间的查询。p 子查询中可能包括IN， NOT IN， ANY， EXISTS和 NOT EXISTS等关键字。p 子查询中还可能包含比较运算符，如“=”、“!=”、“”和“ ANY (SELECT sbirth FROM studentWHERE sclass=计算1401);p 例：查询比其他班级（比如
36、，计算1401班级）某一个同学年龄小的学生的姓名和年龄。p 为了对照结果，我们给出计算1401班所有人的年龄如下。p ALL关键字表示满足所有的条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果所有结果，才能执行外层的查询语句。 ALL表示大于所有的值，ALL表示小于所有的值。p ALL关键字和ANY关键字的使用方式一样，但两者的差距很大，前者是满足所有的内层查询语句返回的所有结果，才执行外查询，后者是只需要满足其中一条记录，就执行外查询。p 查询比其他班级（比如计算1401班级）所有同学年龄都大的学生的姓名和年龄。SELECT sname , (date_format(from_d
37、ays(to_days(now() - to_days(sbirth),%Y) + 0) as 年龄FROM studentWHERE sbirth ALL (SELECT sbirthFROM studentWHERE sclass=计算1401);p 查询比其他班级（比如计算1401班级）所有同学年龄都大的学生的姓名和年龄。p插入数据p修改数据p删除数据p查询：单表查询，多表查询p知识点小结知识点小结p本章实验本章知识小结：p 数据库的增删改查p SELECT语句的使用方法及语法要素p插入数据p修改数据p删除数据p单表查询p知识点小结p本章实验本章实验p 实验内容：见教材“8.8.1 MySQL数据库表的数据插入、修改、删除操作实验”以及“8.8.2 MySQL数据库表数据的查询操作实验”谢谢！中国农业大学 李辉 Email：