mysql北京联通mysql数据库综合-02

1、10月8日10日9日下午1数据库概述上午1数据库编程2MySQL安装配置2函数3SQL概述3视图4数据库设计4临时表5表管理5子查询晚上1一般查询下午1存储过程2多表查询2游标3子查询3事务管理4全文检索4Oracle简介5.1 select语句概述MySQL服务实例select语句编译MySQL客户机定的格式果集result_set整理返回结果集执行按照规成结执行计划5.1 select语句概述select语句的语法格式如下。select 字段列表from 数据源 where条件表达式 group by 分组字段 having条件表达式 1 使用select子句指定字段列表使用谓词限制记录的

2、行数2使用from子句指定数据源34多表连接 order by 排序字段 asc | desc 5.1.1 使用select子句指定字段列表使用以下几种方式指定字段列表：MySQL数据库基础与实例教程字段列表说明*字段列表为数据源的全部字段。表名.*多表查询时，指定某个表的全部字段。字段列表指定所需要显示的列。5.1.1 使用select子句指定字段列表可以为字段列表中的字段名或表达式指定别名，中间使用as关键字分隔即可（as关键字可以省略）。多表查询时，同名字段前必须添加表名前缀，中间使用“.”分隔。MySQL数据库基础与实例教程5.1.2 使用谓词限制记录的行数MySQL中的两个谓词dis

3、tinct和limit可以限制记录 的行数。（1）使用谓词distinct过滤结果集中的重复记录数据库表中不允许出现重复的记录，但这不意味着select的查询结果集中不会出现记录重复的现象。如果需要过滤结果集中重复的记录，可以使用谓词关键字distinct，语法格式如下。distinct 字段名MySQL数据库基础与实例教程5.1.2 使用谓词限制记录的行数（2）使用谓词limit查询某几行记录查询前几条或者中间某几条记录，可以使用谓词关键字limit实现。语法格式如下。select字段列表from数据源limit start,length;start表示从第几行记录开始检索，length表示

4、检索多少行记录。表中第一行记录的start值为0。MySQL数据库基础与实例教程5.1.2 使用谓词限制记录的行数例如：select * from student limit 0,3;该SQL语句等效于：select * from student limit 3;例如检索choose表中从第2条记录开始的3条记录 信息，可以使用下面的SQL语句。select * from choose limit 1,3;MySQL数据库基础与实例教程5.1.3 使用from子句指定数据源多张数据库表（或者视图）“缝补”成一个结 果集时，需要指定“缝补”条件，该“缝补”条件称为连接条件。指定连接条件的方法有两

5、种：第一种方法是在where子句中指定连接条件（稍后讲解）。第二种方法是在from子句中使用连接（join）运算将多个数据源按照某种连接条件“缝补”在一起。MySQL数据库基础与实例教程5.1.3 使用from子句指定数据源第二种方法from子句的语法格式如下。from 表名1 连接类型 join表名2表1和on表2之间的连接条件说明：SQL标准中的连接类型主要分为inner连接（内连接）和outer连接（外连接），而外连接又分为left（左外连接，简称为左连接）、right（右外连接，简称为右连接）以及full（完全外连接，简称完全连接）。MySQL数据库基础与实例教程连接字段student

6、表与classes表连接后产生的结果集student_nostudent_namestudent_contactclass_noclass_namedepartment_name2012001张三1500000000012012自动化1班机电工程2012002李四1600000000012012自动化1班机电工程2012003王五1700000000032012自动化3班机电工程2012004马六1800000000022012自动化2班机电工程2012005田七1900000000022012自动化2班机电工程student_nostudent_namestudent_contactclas

7、s_no2012001张三1500000000012012002李四1600000000012012003王五1700000000032012004马六1800000000022012005田七190000000002class_noclass_namedepartment_name12012自动化1班机电工程22012自动化2班机电工程32012自动化3班机电工程5.1.3 使用from子句指定数据源MySQL数据库基础与实例教程右连接FROM A RIGHT OUTER JOIN B ON A.a1=B.b1a1a2b1b2a1a11b2b12NULLNULLd14b1b2a11b12d1

8、4B表左连接FROM A LEFT OUTER JOIN B ON A.a1=B.b1a1a2b1b2a1a11b2b12c3NULLNULLa1a2a1b2c3a1a2b1b2a1a11b2b12内连接FROM A,B WHERE A.a1=B.b1A表SELECT a1,a2,b1,b25.1.3 使用from子句指定数据源1内连接（inner join）内连接将两个表中满足指定连接条件的记录连接成新的结果集，舍弃所有不满足连接条件的记录。内连接是最常用的连接类型，也是默认的连接类型，可以在from子句中使用inner join（inner关键字可以省略）实现内连接，语法格式如下。from

9、表1 连接条件inner join 表2on表1和表2之间的MySQL数据库基础与实例教程5.1.3 使用from子句指定数据源2外连接（outer join）外连接又分为左连接（left join）、右连接（right join）和完全连接（full）。与内连接不同，外连接（左连接或右连接）的连接条件只过滤一个表，对另一个表不进行过滤（该表的所有记录出现在结果集中）；注意：MySQL暂不支持完全连接 。MySQL数据库基础与实例教程5.1.3 使用from子句指定数据源（1）左连接的语法格式from表1 接条件leftjoin 表2on表1和表2之间的连说明：语法格式中表1左连接表2，意味着

10、查询结果集中须包含表1的全部记录，然后表1按指定的连接条件与表2进行连接，若表2中没有满足连接条件的记录，则结果集中表2相应的字段填入NULL。MySQL数据库基础与实例教程5.1.3 使用from子句指定数据源（2）右连接的语法格式from表1 连接条件join表2on表1和表2之间的right说明：语法格式中表1右连接表2，意味着查询结果集中须包含表2的全部记录，然后表2按指定的连接条件与表1进行连接，若表1中没有满足连接条件的记录，则结果集中表1相应的字段填入NULL。MySQL数据库基础与实例教程5.1.4 多表连接以3个表为例，语法格式如下。from表1连接类型join 表2on表1

11、和表2之间的连接条件连接类型join 表3 条件表2和表3之间的连接onMySQL数据库基础与实例教程course_nocourse_nameup_limitdescriptionstatusteacher_no1java语言程序设计60暂无已审核0012MySQL数据库150暂无已审核0023c语言程序设计230暂无已审核003choose_nostudent_nocourse_noscorechoose_time120120012402012-8-11 10:33220120011502012-8-11 17:33320120023602012-8-12 0:334201200227020

12、12-8-12 7:33520120031802012-8-12 14:33620120042902012-8-12 21:33720120053NULL2012-8-13 4:33820120051NULL2012-8-13 11:33student_nostudent_namestudent_contactclass_no2012001张三1500000000012012002李四1600000000012012003王五1700000000032012004马六1800000000022012005田七1900000000022012005201200520120042012003201

13、2002201200220120012012001student_no田七田七马六王五李四李四张三张三student_name13212312course_nojava语言程序设计c语言程序设计MySQL数据库java语言程序设计MySQL数据库c语言程序设计java语言程序设计MySQL数据库course_nameNULLNULL908070605040score5.2 使用where子句过滤结果集数据库中存储着海量数据，数据库用户往往需要的是满足特定条件的记录，where子句可以实现结果集的过滤筛选。where子句的语法格式： where 条件表达式1 使用单一的条件过滤结果集is NUL

14、L运算符23select语句与字符集使用逻辑运算符4使用like进行模糊查询55.2.1 使用单一的条件过滤结果集单一的过滤条件可以使用下面的布尔表达式表示。表达式1比较运算符表达式2说明：“表达式1”和“表达式2”可以是一个字段名、常量、变量、函数甚至是子查询。比较运算符用于比较两个表达式的值，比较的结果是一个布尔值（true或者false）。MySQL数据库基础与实例教程5.2.1 使用单一的条件过滤结果集常用的比较运算符有=（等于）、（大于）、=（大于等于）、（小于）、=（小于等于）、（不等于）、!=（不等于）、!（不大于）。如果表达式的结果是数值，则按照数值的大小进行比较；如果表达式的

15、结果是字符串，则需要参考字符序collation的设置进行比较。MySQL数据库基础与实例教程is NULL运算符5.2.2is NULL用于判断表达式的值是否为空值NULL（is not 恰恰相反），is NULL的语法格式如下。表达式 is not NULL说明：不能将“score is NULL”写成“score = NULL;”，原因是NULL是一个不确定的数，不能使用“=”、“！=”等比较运算符与NULL进行比较MySQL数据库基础与实例教程5.2.3 select语句与字符集任务布置4：完成场景描述4：MySQL客户机显示结果集时出现乱码问题的任务要求。任务布置5：完成场景描述5：

16、查询结果失败问题的任务要求。任务布置6：完成场景描述6：SQL语句解析错误问题的任务要求。MySQL数据库基础与实例教程5.2.4 使用逻辑运算符where子句中可以包含多个查询条件，使用逻辑运算符可以将多个查询条件组合起来，完成更 为复杂的过滤筛选。常用的逻辑运算符包括逻辑与（and）、逻辑或（or）以及逻辑非（！），其中逻辑非（！）为单目运算符。MySQL数据库基础与实例教程5.2.4 使用逻辑运算符1逻辑非（！）逻辑非（！）为单目运算符，逻辑非（！）的使用方法较为简单，如下所示。使用逻辑非（！） 操作布尔表达式时，布尔表达式的值为true时，整个逻辑表达式的结果为false，反之亦然。!

17、布尔表达式MySQL数据库基础与实例教程5.2.4 使用逻辑运算符2and逻辑运算符使用and逻辑运算符连接两个布尔表达式时，只 有两个布尔表达式的值都为true时，整个逻辑表达式的结果才为true。语法格式如下。布尔表达式1 and 布尔表达式2MySQL数据库基础与实例教程5.2.4 使用逻辑运算符另外MySQL还支持betweenand运算符， betweenand运算符用于判断一个表达式的值是否位于指定的取值范围内，betweenand的语法格式如下。表达式not between起始值and终止值MySQL数据库基础与实例教程5.2.4 使用逻辑运算符3使用or逻辑运算符使用or逻辑运

18、算符连接两个布尔表达式时，只有两个表达式的值都为false时，整个逻辑表达式的结果才为false。语法格式如下。布尔表达式1 or 布尔表达式2MySQL数据库基础与实例教程5.2.4 使用逻辑运算符另外MySQL还支持in运算符，in运算符用于判定一个表达式的值是否位于一个离散的数学集合内，in的语法格式如下。表达式 not in （数学集合）MySQL数据库基础与实例教程5.2.5 使用like进行模糊查询like运算符用于判断一个字符串是否与给定的模式相匹配。模式是一种特殊的字符串，特殊之处在于不仅包含普通字符，还包含有通配符。在实际应用中， 如果不能对字符串进行精确查询，此时可以使用l

19、ike运算符与通配符实现模糊查询，like运算符的语法格式如下。字符串表达式 not like模式MySQL数据库基础与实例教程5.2.5 使用like进行模糊查询模式是一个字符串，其中包含普通字符和通配符。在MySQL中常用的通配符如表所示。MySQL数据库基础与实例教程通配符描述%包含零个或多个字符组成的任意字符串_(下划线)任意一个字符5.2.5 使用like进行模糊查询模糊查询“%”或者“_”字符时，需要将“%”或者“_”字符转义，例如检索学生姓名中所有带“_” 的学生信息，可以使用下面的SQL语句，其中new_student表在表记录的更新操作章节中创建。 执行结果如图5-23所示。

20、select * from new_student wherestudent_name like %_%;MySQL数据库基础与实例教程5.2.5 使用like进行模糊查询如果不想使用“”作为转义字符，可以使用escape关键字自定义一个转义字符，例如下面的SQL语句使用字符“!”作为转义字符。select * from new_student wherestudent_name like %!_% escape !;MySQL数据库基础与实例教程5.3 使用orderby子句对结果集排序select语句的查询结果集的排序由数据库系统动态确定，往往是无序的，order by子句用于对结果集排序

21、。在select语句中添加order by子句，就可以使结果集中的记录按照一个或多个字段的值进行排序，排序的方向可以是升序（asc）或降序（desc）。order by子句的语法格式如下。order by字段名1asc|desc ,字段名nasc|desc5.4 使用聚合函数汇总结果集聚合函数用于对一组值进行计算并返回一个汇总值，常用的聚合函数有累加求和sum()函数、平均值avg()函数、统计记录的行数count()函数、最大值max()函数和最小值min()函数等。5.4 使用聚合函数汇总结果集使用count()对NULL值统计时，count()函数将忽略NULL值。sum()函数、avg

22、()函数、max()以及min()函数等统计函数，统计数据时也将忽略NULL值。5.5 使用groupby子句对记录分组统计group by子句将查询结果按照某个字段（或多个字段） 进行分组（字段值相同的记录作为一个分组。group by子句与聚合函数123group by子句与having子句group by子句与group_concat()函数group by子句与with rollup选项45.5 使用groupby子句对记录分组统计group by子句通常与聚合函数一起使用。group by子句的语法格式如下。group by 字段列表 having条件表达式 withrollup 任

23、务布置7：完成场景描述7的任务要求。group by子句与聚合函数5.5.1例如统计每一个班的学生人数。例如统计每个学生已经选修多少门课程，该生的最高分、最低分、总分及平均成绩。MySQL数据库基础与实例教程group by子句与having子句5.5.2having子句用于设置分组或聚合函数的过滤筛选条件，having子句通常与group by子句一起使用。having子句语法格式与where子句语法格式类似，having子句语法格式如下。having条件表达式其中条件表达式是一个逻辑表达式，用于指定分组后的筛选条件。MySQL数据库基础与实例教程group by子句与having子句5.5

24、.2例如检索平均成绩高于70分的学生信息及平均成绩。MySQL数据库基础与实例教程group by子句与having子句5.5.2下面select语句的语法格式中，select语句的执行过程为：首先使用where子句对结果集进行过滤筛选，接着group by子句分组where子句的输出， 最后having子句从分组的结果中再进行筛选。select 字段列表from 数据源where条件表达式group by 分组字段having条件表达式MySQL数据库基础与实例教程group by子句与group_concat()函数5.5.3group_concat()函数的功能是将集合中的字符串连接起来

25、，此时group_concat()函数的功能与字符串连接函数concat()的功能相似。MySQL数据库基础与实例教程group by子句与group_concat()函数5.5.3group_concat()函数的功能是将集合中的字符串连接起来，此时group_concat()函数的功能与字符串连接函数concat()的功能相似。例如下面SQL语句中的group_concat()函数以及concat()函数负责将集合中(java, 程序, 设计) 三个字符串连接起来。select group_concat(java,程序,设计), concat(java,程序,设计);MySQL数据库基础与

26、实例教程group by子句与group_concat()函数5.5.3group_concat()函数还可以按照分组字段，将另一个字段的值（NULL值除外）使用逗号连接起来。 concat()却函数没有提供这样的功能。MySQL数据库基础与实例教程group by子句与withrollup选项5.5.4group by子句将结果集分为若干个组，使用聚合函数可以对每个组内的数据进行信息统计，有时需要对各个组进行汇总运算，则需要在每个分组后加上一条汇总记录，这个任务可以通过with rollup选项实现。MySQL数据库基础与实例教程5.6 合并结果集使用union可以将多个select 语句的

27、查询结果集组合成一个结果集。select 字段列表1fromtable1 union allselect 字段列表2fromtable2.说明：字段列表1与字段列表2的字段个数必须相同，且具有相同的数据类型。合并产生的新结果集的字段名与字段列表1中的字段名对应。MySQL数据库基础与实例教程5.6 合并结果集union 与 union all 的区别：当使用union时，MySQL 会筛选掉select结果集中重复的记录（在结果集合并后会对新产生的结果集进行排序运算，效率稍低）。而使用union all时，MySQL会直接合并两个结果集，效率高于union。如果可以确定合并前的两个结果集中不包

28、含重复的记录，建议使用union all。5.7 子查询如果一个select语句能够返回单个值或者一列值且该select语句嵌套在另一个SQL语句中（例如select语句、insert语句、update语句或者delete 语句）中，那么该select语句称为子查询（也叫内层查询）， 包含子查询的SQL 语句称为主查询（也叫外层查询）。为了标记子查询与主查询之间的关系，通常将子查询写在小括号内。子查询分为相关子查询与非相关子查询。5.7 子查询子查询一般用在主查询的where子句或having 子句中， 与比较运算符或者逻辑运 算 符 一 起 构 成where 筛选条件或having筛选条件。

29、1 子查询与比较运算符2子查询与in运算符3子查询与exists逻辑运算符子查询与any运算符4子查询与all运算符55.7.1 子查询与比较运算符如果子查询返回单个值，则可以将一个表达式的值与子查询的结果集进行比较。子查询可以仅仅使用自己定义的数据源，也可以“直接引用”主查询中的数据源，但两者意义完全不同。MySQL数据库基础与实例教程5.7.1 子查询与比较运算符如果子查询中仅仅使用了自己定义的数据源，这种查询是非相关子查询，非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给主查询。如果子查询中使用了主查询的数据源，这种查询是相关子查询，此时主查询的执行与相关子

30、查询的执行相互依赖。MySQL数据库基础与实例教程5.7.2 子查询与in运算符子查询经常与in运算符一起使用，用于将一个表达式的值与子查询返回的一列值进行比较，如果表达式的值是此列中的任何一个值，则条件表达式的结果为true；否则为false。MySQL数据库基础与实例教程5.7.3 子查询与exists逻辑运算符exists逻辑运算符用于检测子查询的结果集是否包含有记录，如果结果集中至少包含一条记录则exists的结果为true；否则为false。在exists 前面加上not时，与上述结果恰恰相反。MySQL数据库基础与实例教程5.7.4 子查询与any运算符any运算符通常与比较运算符

31、一起使用。使用any运算符时，通过比较运算符将一个表达式的值与子查询返回的一列值逐一进行比较，若某次的比较结果为true，则整个表达式的值为true； 否则为false。any逻辑运算符的语法格式如下。表达式 比较运算符 any(子查询)MySQL数据库基础与实例教程5.7.4 子查询与any运算符举例来说：当比较运算符为大于号（）时， “表达式 any(子查询)”表示至少大于子查询结果集中的某一个值（或者说大于结果集中的最小值），那么整个表达式的结果为true。MySQL数据库基础与实例教程5.7.5 子查询与all运算符all运算符通常与比较运算符一起使用。使用all 运算符时，通过比较运

32、算符将一个表达式的值与子查询返回的一列值逐一进行比较，若每次的比较结果都为true，则整个表达式的值为true；否则为false。any逻辑运算符的语法格式如下。表达式 比较运算符 all(子查询)MySQL数据库基础与实例教程5.7.5 子查询与all运算符举例来说：当比较运算符为大于号（）时，“表达式 all(子查询)”表示大于子查询结果集中的任何一个值（或者说大于结果集中的最大值），那么整个表达式的结果为true。MySQL数据库基础与实例教程5.8 选课系统综合查询任务布置8：书写本章节的select代码。5.9 使用正则表达式模糊查询与like运算符相似，正则表达式主要用于判断一个字

33、符串是否与给定的模式匹配，但正则表达式的模式匹配功能比like运算符的模式匹配功能更为强大，且更加灵活。使用正则表达式进行模糊查询时，需要使用regexp关键字，语法格式如下。字段名 not regexp binary 正则表达式MySQL数据库基础与实例教程5.9 使用正则表达式模糊查询正则表达式由一些普通字符和一些元字符构成，普通字符包括大写字母、小写字母和数字，而元字符具有特殊的含义。在最简单的情况下，一个正则表达式是一个不包含元字符的字符串。例如正则表达式testing中没有包含任何元字符，它 可以匹配testing、123testing等字符串。MySQL数据库基础与实例教程5.9

34、使用正则表达式模糊查询常用的元字符MySQL数据库基础与实例教程元字符说明.匹配任何单个的字符匹配字符串开始的部分$匹配字符串结尾的部分字符集合或数字集合匹配方括号内的任何字符，可以使用-表示范围。例如abc匹配字符串“a”、“b”或“c”。a-z匹配任何字母，而0-9匹配任何数字字符集合或数字集合匹配除了方括号内的任何字符字符串1|字符串2匹配字符串1或者字符串2*表示匹配0个或多个在它前面的字符。例如x*表示0个或多个x字符，.*表示匹配任何数量的任何字符；+表示匹配1个或多个在它前面的字符。如a+表示1个或多个a字符?表示匹配0个或1个在它前面的字符。如a?表示0个或1个a字符。字符串n

35、字符串出现n次字符串m,n字符串至少出现m次，最多出现n次5.9 使用正则表达式模糊查询例如检索含有“java”的课程信息，可以使用下面的SQL语句。select * from course where course_nameregexp java;5.9 使用正则表达式模糊查询例如检索以“程序设计”结尾的课程信息，可以使用下面的SQL语句。select * from course where course_nameregexp 程序设计$;5.9 使用正则表达式模糊查询例如检索以“j”开头，以“程序设计”结尾的课程信息，可以使用下面的SQL语句。select * from course wh

36、ere course_nameregexp j.*程序设计$;5.9 使用正则表达式模糊查询例如检索学生联系方式中以15开头或者18开头且后面跟着9位数字的学生信息，可以使用下面的SQL语句。select * from student where student_contactregexp 1580-99;5.10 全文检索大多数时候，使用like关键字或者正则表达式对字符串进行模糊查询，需要对表进行全表扫描，检索效率较低。通过全文检索，可以有效提升字符串检索效率。5.10 全文检索简单地说，MySQL全文检索使用特定的分词技术、利用查询关键字和查询字段内容之间的相关度进行检索，通过全文索引提

37、高文本匹配的速度。全文检索的语法格式如下。select字段列表from 表名where match (全文索引字段1,全文索引字段2,.) against (搜索关键字 全文检索方式 )5.10 全文检索在2011年发布的5.6 版本中InnoDB存储引擎表实现了全文检索的支持，这将大幅提升InnoDB存储引擎的字符串检索效率，实现更快速、更高质量的模糊查询。1 全文检索的简单应用全文检索方式2345布尔检索模式的复杂应用MySQL全文检索的注意事项InnoDB表的全文检索5.10.1 全文检索的简单应用create table book(isbn char(20) primary key,

38、name char(100) not null, brief_introduction text not null, price decimal(6,2), publish_time date not null,unique index isbn_unique (isbn), index name_index (name (20),fulltext index brief_fulltext (name,brief_introduction), index complex_index (price,publish_time) engine=MyISAM default charset=gbk;创

39、建书籍book表（注意该表为MyISAM存储引擎），在book表中的字段组合（name, brief_introduction）创建一个全文索引 。MySQL数据库基础与实例教程5.10.1 全文检索的简单应用insert into book(isbn,name,brief_introduction,price,publish_time) values (978-7-115-25626-3,PHP Fundamentals & Practices,Web Database Applications MySQL offers web developers a mixture of theoret

40、ical and practical information on creating web database applications. ,42.0,2012-7-1),(978-7-115-25626-4,MySQL COOKBOOK,The MySQL databasemanagement system has become quite popular in recent years.,128.0,2008-1-1),(978-7-115-25626-5,Beginning MySQL, MySQL is especially heavily used in combination wi

41、th a web server for constructing database- backed web sites that involve dynamic content generation.,98.0,2008- 1-1);向book表插入测试数据。MySQL数据库基础与实例教程5.10.1 全文检索的简单应用例如检索书名或者简介中涉及到practices单词的所有图书信息，可以使用下面的SQL语句。select * from book where match(name,brief_introduction) against (practices)GMySQL数据库基础与实例教程5.

42、10.1 全文检索的简单应用例如检索书名或者简介中涉及到practices或者cookbook单词的所有图书信息，可以使用下面的SQL语句。select * from book where match(name,brief_introduction) against (practices cookbook)GMySQL数据库基础与实例教程5.10.1 全文检索的简单应用检索书名或者简介中涉及到mysql单词的所有图书信息，将检索失败。MySQL在进行全文检索时，默认情况下将忽略权重超过50%的记录，50%称为阈值（注意读作： y zh）， 因此检索失败。MySQL数据库基础与实例教程5.10.1 全文检索的简单应用如果希望忽略阈值的因素，例如检索书名或者简介中涉及到“mysql”单词的所有图书信息，可以使用下面的SQL语句，执行结果如图5-66所示， 该SQL语句使用了布尔检索模式（稍后讲解）。select * from book where match(name,brief_introduction) against (mysql in boolean mode)GMySQL数据库基础与实例教程5.10.1 全文检索的简单应用检索书名或者简介中涉及到php单词的所有图书信息，将检索失败。这是由于MySQL全文检索对搜