《MySQL数据库应用实战教程（微课版）》第04章-使用数据库图书借阅数据库

项目4使用数据库

——图书借阅数据库任务1数据操纵任务1数据操纵4.1.1【实训4–1】数据插入4.1.2【实训4–2】数据更新4.1.3【实训4–3】数据删除4.1.4数据操纵与数据约束任务2理解数据完整性约束任务3简单数据查询任务4复杂数据查询4.1.1【实训4–1】数据插入根据Jitor校验器的要求，在dbForge上完成“【实训4–1】数据插入”省略列名列表，指定主键值省略列名列表，主键值为null，自动增量列出所有列，主键值为null，自动增量Insertinto表名values(1,值1,值2…);Insertinto

表名values(null,值1,值2…);Insert

into(主键,列1,列2…)表名values(null,值1,值2…);Insertintolib_uservalues(1,'2249739484','管理员','admin','123456',2,'M',);Insertintolib_uservalues(null,'2249733829','杰克','jack','123456',1,'M',);Insertintolib_user(id_lib_user,col_rfid,col_name,col_account,col_password,col_type,col_sex,col_mobile)values(null,'2249731839','艾米','amy','123456',1,'F',);【实训4-1】4.1.1【实训4–1】数据插入（续）列出所有列，不含自动增量的主键仅列出所有非空列，不含自动增量的主键下述3类列可以省略自增量的列应该省略，其值由程序根据规则自动填入。或用用null作为它的值允许为空的列，省略时，其值为空。有默认约束的列，省略时，其值为默认值Insert

into(列1,列2…)表名values(值1,值2…);Insert

into(列1,…)表名values(值1,…);Insertintolib_user(col_rfid,col_name,col_account,col_password,col_type,col_sex,col_mobile)values('2249738325','张三','zhangs','123456',0,'M',);Insertintolib_user(col_rfid,col_name,col_account,col_password)values('2249738303','李四','lisi','123456');4.1.2【实训4–2】数据更新根据Jitor校验器的要求，在dbForge上完成“【实训4–2】数据更新”更新指定行的一列数据更新指定行的多列数据Update表名set列名=值whereid=主键;Update表名set列名1=值1,列名2=值2…

whereid=主键;Updatelib_usersetcol_name='王五'whereid_lib_user=5;Updatelib_usersetcol_account='wangwu',col_password='abcde'whereid_lib_user=5;【实训4-2】4.1.2【实训4–2】数据更新（续）更新多行数据更新所有行的数据这时没有where子句，将更新表中所有行的数据，因此操作时需要特别谨慎。Update表名set列名1=值1,列名2=值2…

where条件;Update表名set列名1=值1,列名2=值2…;Updatelib_usersetcol_password=col_accountwherecol_type=0;Updatelib_usersetcol_password='abc123';4.1.3【实训4–3】数据删除根据Jitor校验器的要求，在dbForge上完成“【实训4–3】数据删除”删除指定的一行删除部分行删除所有行——Delete语句删除所有行——Truncate语句Deletefrom表名whereid=主键;Deletefrom表名where条件;Deletefrom表名;Truncate表名;Deletefromlib_userwhereid_lib_user=2;Deletefromlib_lendingwherecol_returned_date<'2021-12-30';Deletefromlib_lending;Truncate表名;【实训4-3】4.1.4数据操纵与数据约束任务2理解数据完整性约束任务1数据操纵任务2理解数据完整性约束4.2.1实体完整性约束（主键约束）4.2.2参照完整性约束（外键约束）4.2.3其他完整性约束任务3简单数据查询任务4复杂数据查询4.2.1实体完整性约束（主键约束）主键应当是对用户没有意义的，在理论上，只要是具有唯一性的列都可以作为主键，例如“身份证号”“学号”等属性，但实际上通行的做法是采用无业务含义的主键主键应该是单属性的，以便提高查询和连接操作的效率。在理论上，允许多属性的组合作为主键，但实际上通行的做法是采用单属性的主键主键的值应当由计算机自动生成，手动输入主键的值既不能保证它的唯一性，也极大地增加大地增加了输入工作的难度永远也不要更新主键，主键的作用只是标识一行，没有其他的用途，所以也就没有理由，更没有必要去更新它。如果更新主键，那么还要更新相应的外键，这样容易导致错误4.2.2参照完整性约束（外键约束）外键的值只能取被参照的表的主键的值外键的值允许为空外键的值允许为空，表示主从表之间的联系不是非常紧密。例如学生的籍贯外键，在输入学生的入学数据时，可以暂时不输入，留待日后补录外键的值非空外键的值非空，表示主从表之间的联系非常紧密。例如学生的班级，在输入学生的入学数据时是必须输入的，因为一个学生不可能在入学时是没有班级的外键的值不允许重复如果外键的值不允许重复，表示主从表是一对一的联系。就是在一对多的联系中，将“多”的一方限制为不允许重复，从而成为一对一的联系多个外键一张表可能与多张表有联系，这时每个外键表示与一张表的联系。有多少个外键，就表示与多少张表有联系一张表也可以有多个外键参照同一张表，这时每个外键都有不同的含义，例如借阅表有3个外键，都是参照用户表的，它们分别表示借阅人、借出经手人和归还经手人4.2.3其他完整性约束1．非空约束非空约束是很容易理解的一种约束，即指定该列的值是否允许取空值。需要注意的是，取空值和值为0或值为空字符串是不同的2．唯一性约束具有唯一性约束的属性不允许出现重复的值。唯一性约束可以是单属性的，也可以是多属性的属性组主键默认具有唯一性约束。唯一性约束与主键的区别如下唯一性约束根据业务需求，不允许出现空值或允许出现一次空值，主键则不允许出现空值一张表允许有多个唯一性约束，但只能有一个主键约束3．默认约束默认约束用于当插入行时，如果没有为该列提供值，该列的值将被赋给默认约束指定的值。更新数据时，默认约束不起作用任务3简单数据查询任务1数据操纵任务2理解数据完整性约束任务3简单数据查询4.3.1【实训4–4】单表查询4.3.2【实训4–5】理解查询条件4.3.3【实训4–6】排序和分页4.3.4【实训4–7】联合查询任务4复杂数据查询4.3.1【实训4–4】单表查询根据Jitor校验器的要求，在dbForge上完成“【实训4–4】单表查询”1.查询所有数据Select*from表名;Select*fromlib_user;微课：4-1单表查询【实训4-4】4.3.1【实训4–4】单表查询（续）选择列1)选择列（无标题）2)列的别名（列标题）Select列名1AS别名1,列名2AS别名2…from表名;Selectcol_nameas姓名,col_accountas账号,col_mobileas手机fromlib_user;Select列名1,列名2…from表名;Selectcol_name,col_account,col_mobilefromlib_user;4.3.1【实训4–4】单表查询（续）选择列（续）3)消除重复数据Selectdistinct

列名1,列名2…from表名;Selectcol_sexfromlib_user;Selectdistinctcol_sexfromlib_user;4.3.1【实训4–4】单表查询（续）3.选择行（下一小节详细讲解）4.计算列1)常量Select*from表名

where条件;Select常量,列名1,列名2…from表名;Select'图书',col_titlefromlib_book;Select*fromlib_userwherecol_sex='F';4.3.1【实训4–4】单表查询（续）2)表达式3)If表达式Select表达式,列名1AS别名1,列名2AS别名2…from表名;Selectif(条件,值1,值2),列名1,列名2…from表名;Selectconcat(col_author,'著：',col_title)fromlib_book;Selectcol_nameas姓名,col_sex,if(col_sex='M','男','女')性别fromlib_user;4.3.2【实训4–5】理解查询条件根据Jitor校验器的要求，在dbForge上完成“【实训4–5】理解查询条件”Select*from表名

where条件;【实训4-5】1．关系表达式1．关系表达式关系运算符可以连接列名和常量（或表达式），从而形成关系表达式，用于查询条件。等于、大于、小于、大于等于、小于等于、不等于：=、>、<、>=、<=、<>可以比较数字、字符串、日期时间例如如查询用户表中所有男性的姓名、电话和性别的代码如下。查询价格小于50元的图书的代码如下。Selectcol_name,col_mobile,col_sexfromlib_userwherecol_sex='M';Select*fromlib_bookwherecol_price<50

;2．范围查询2．范围查询用于查询表达式的值是否在（不在）一个连续的范围在…范围之间、不在…范围之间：between…and…、notbetween…and…可用于数字、字符串、日期时间例如查询价格是40～50元的图书（包括40和50）的代码如下查询在2020/04/08与2020-04-12之间借出的图书的代码如下，日期要用单引号引起来，年、月、日的分隔符可以用斜线，也可以用减号Select*fromlib_bookwherecol_pricebetween40and50;Select*fromlib_lendingwherecol_lending_datebetween'2020/04/08'and'2020-04-12';3．集合查询3．集合查询用于查询表达式的值是否在（不在）一个不连续的集合中。在…集合之内、不在…集合之内：in(…)、notin(…)可用于数字、字符串、日期时间例如查询图书ID是1或3的图书副本的代码如下，由于不是连续的数字，所以应该使用集合查询，而不是用范围查询Select*fromlib_copywhereid_lib_bookin(1,3);4．模糊查询4．模糊查询模糊查询利用通配符来达到不精确匹配的查询要求类似于、不类似于：like、notlike，并且配合通配符使用，通配符有两种，如表4.4所示。仅用于字符串，不用于数字和日期时间例如查询书名中，以“SQLServer”开始的图书（只有一本）的代码如下。查询书名中，包含“SQLServer”的图书（共有两本）的代码如下。Selectcol_author,col_titlefromlib_bookwherecol_titlelike'sqlserver%';Selectcol_author,col_titlefromlib_bookwherecol_titlelike'%sqlserver%';5．逻辑表达式5．逻辑表达式需要使用多个查询条件时，可以使用and、or等将查询条件连接起来，形成逻辑表达式。并且、或者、不：and、or、not仅用于连接其他查询条件，一般不用直接于数字、字符串和日期例如例如查询用户表中姓“李”（条件一）的女性（条件二）的代码如下。例如查询用户表中姓“李”（条件一）或者女性（条件二）的代码如下。Selectcol_nameas姓名,col_sexas性别fromlib_userwherecol_namelike'李%'andcol_sex='F';Selectcol_nameas姓名,col_sexas性别fromlib_userwherecol_namelike'李%'or

col_sex='F';6．空值判断6．空值判断判断值是否为空为空、不为空：isnull、isnotnull，不能用等于号“=”，用英语的语法isnull或者isnotnull可用于任何数字类型例如查询用户表中“手机”值为空的数据，代码如下。查询用户表中“手机”值不为空的数据，代码如下。Select*fromlib_userwherecol_mobileisnull;Select*fromlib_userwherecol_mobileisNOTnull;4.3.3【实训4–6】排序和分页根据Jitor校验器的要求，在dbForge上完成“【实训4–6】排序和分页”1．升序排序将数据从小到大进行排序，用asc表示，这是默认的排序方式，因此可以省略asc。例如对用户表以“账号”列为升序排序的代码如下。2．降序排序降序排序是从大到小进行排序，用desc表示。例如对相同的数据根据“账号”列进行降序排序的代码如下Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_accountasc;Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_accountdesc

;【实训4-6】升序和降序的结果升序：从小到大降序：从大到小4.3.3【实训4–6】排序和分页（续）3．多个排序列也可以指定多个排序列，这时先以第一排序列进行排序，如果第一排序列的排序相同，再按第二排序列进行排序，以此类推。下述代码是对用户表中的“性别”列和“账号”列进行排序，即先按“性别”列进行排序，如果“性别”列相同，再按“账号”列进行排序。下述代码将“账号”列改为降序，其余不变Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_sex,col_account;Selectcol_rfid,col_name,col_sex,col_account,col_mobilefromlib_userorderbycol_sex,col_accountdesc;多个排序列的结果先按第一排序进行排序第一排序相同的，再按第二排序进行排序如果第一排序没有相同的，第二排序是不起作用的如果子句中的两个列对调，成为orderbycol_account,col_sex，那么第二个列col_sex是没有作用的，因为每行的账号都不同，按第一列col_account排序就已经把所有行排好序了4.3.3【实训4–6】排序和分页（续）4．分页查询如果表中的数据太多，例如达到100万行，这时显示所有行将是不现实的，可以指定只显示其中的部分行，使用limit关键字来实现。例如下述代码是只显示前3行limit关键字通常用于分页，例如百度的查询结果是分页显示的。分页的语法格式如下假设tab_abc表有100行以上的数据，以每页50行的要求，查询第3页的数据，代码如下。其中，100=（页号-1）*每页行数=（3-1）*50。Select*fromtab_abclimit100,50;Select*fromlib_userlimit3;Select列名列表from表名limit(页号-1)*每页行数,每页行数;4.3.4【实训4–7】联合查询根据Jitor校验器的要求，在dbForge上完成“【实训4–7】联合查询”注意以下几点两个查询的列数相同，如果不相同，则会出错两个查询对应列的含义相同，如果不相同，则会造成误解列名以第一个查询的列名为准，第二个查询的列名不显示（被忽略）两个查询有各自的查询条件等子句，只有排序子句是针对整个联合的，并且在最后排序列名使用第一个查询的列名，在上述例子中，如果按“性别”列排序时应用col_sex，而不能用sex。Selectid_lib_user,col_name,col_sex,col_mobilefromlib_userunionSelectid,name,sex,mobilefromlib_staff;【实训4-7】联合查询的结果左图：两条查询语句单独执行，结果是独立的，为便于比较，将其上下放置右图：两条查询语句改为联合查询，变成一条语句，结果就是单独查询时的结果的上下联合任务4复杂数据查询任务1数据操纵任务2理解数据完整性约束任务3简单数据查询任务4复杂数据查询4.4.1【实训4–8】内连接查询4.4.2【实训4–9】外连接查询4.4.3【实训4–10】自连接查询4.4.4【实训4–11】统计与分组统计4.4.5【实训4–12】综合练习4.4.1【实训4–8】内连接查询根据Jitor校验器的要求，在dbForge上完成“【实训4–8】内连接查询”1．两张表的内连接（联系人数据库）Selecttab_contact.id_contactas人员id,tab_contact.col_nameas姓名,tab_contact_type.col_nameas联系人类型,tab_contact_type.id_contact_type类型idfromtab_contact

innerjointab_contact_type

on

tab_contact.id_contact_type=tab_contact_type.id_contact_type;from

人员表

innerjoin类型表

on

人员表的外键=类型表的主键可以省略关键字inner微课：4-2内连接【实训4-8】内连接的查询结果内连接在关系数据库查询操作中是一项最重要的技能内连接是外键参照（引用）主表的主键的概念在查询中的体现内连接是数据库设计中，拆分表之后，再合并两张表的数据的关键技术没有内连接查询，就没有关系数据库技术参考下图中的数据，深入理解上面的的概念4.4.1【实训4–8】内连接查询（续）2．三张表的内连接（联系人数据库）先连接两张表，再与第三张连接Selecttab_contact.col_nameas姓名,

tab_contact_info.col_infoas联系方式,tab_contact_info.col_noteas说明,tab_contact_type.col_nameas联系人类型fromtab_contact_infoinnerjointab_contactontab_contact_info.id_contact=tab_contact.id_contactinnerjointab_contact_typeontab_contact.id_contact_type=tab_contact_type.id_contact_type;三张表的内连接两张表连接之后，再连接到第三张表4.4.1【实训4–8】内连接查询（续二）3．多张表的内连接（图书借阅数据库）Select列名列表from表名1innerjoin表名2on表名1.外键1=表名2.主键2innerjoin…on…innerjoin表名non表名n.外键n=表名m.主键m;Selectlib_book.col_title书名,lib_book.col_author作者,lib_user.col_name借阅者姓名,lib_copy.col_bar_code副本条码,lib_lending.col_lending_date借书时间,lib_lending.col_returned_date还书时间fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_book;4.4.1【实训4–8】内连接查询（续三）5张表的内连接的例子注意：编写连接查询语句时，一般是从下向上连接，要一级一级地连接不论是从下向上连接，还是从上向下连接，都不能跳过某一级直接连到更高或更低的的一级。Selectlib_publisher.col_name出版社,lib_book.col_title书名,lib_book.col_author作者,lib_user.col_name借阅者姓名,lib_copy.col_bar_code副本条码,lib_lending.col_lending_date借书时间,lib_lending.col_returned_date还书时间fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_bookinnerjoinlib_publisheronlib_book.id_lib_publisher=lib_publisher.id_lib_publisher;4.4.1【实训4–8】内连接查询（续四）4．连接查询与其他子句（图书借阅数据库）from,where,orderby,limit子句的次序Selectlib_book.col_title书名,lib_book.col_author作者,lib_user.col_name借阅者姓名,lib_copy.col_bar_code副本条码,lib_lending.col_lending_date借书时间,lib_lending.col_returned_date还书时间fromlib_lendinginnerjoinlib_useronlib_lending.id_lib_user1reader=lib_user.id_lib_userinnerjoinlib_copyonlib_lending.id_lib_copy=lib_copy.id_lib_copyinnerjoinlib_bookonlib_copy.id_lib_book=lib_book.id_lib_bookwherecol_returned_dateisnullorderbylib_user.col_name;Select列名列表from表1innerjoin表2on连接条件where选择条件orderby排序列limit行数;4.4.2【实训4–9】外连接查询根据Jitor校验器的要求，在dbForge上完成“【实训4–9】外连接查询”男生四位，女生三位，共七人两位单身的男生，一位单身的女生，以及两对夫妻微课：4-3外连接和自连接【实训4-9】4.4.2【实训4–9】外连接查询（续一）1．内连接作为对比，先看内连接的结果结果是两对夫妻问题：如果还想列出单身女生的信息，如何编写Select语句？答案见下页Select

as男性,man.wifeas妻子ID,

as女性frommanjoinwomanonman.wife=woman.id;4.4.2【实训4–9】外连接查询（续二，右外连接）2．右外连接right

join可以列出右边表的所有信息，即woman表，女生的信息问题：如果还想列出的是单身男生，而不是单身女生的信息，如何编写Select语句？答案见下页Select

as男性,man.wifeas妻子ID,

as女性fromman

rightjoinwomanonman.wife=woman.id;4.4.2【实训4–9】外连接查询（续三，左外连接）3．左外连接leftjoin可以列出左边表的所有信息，即man表，男生的信息问题：如果还想同时列出单身男生和单身女生的信息，又要如何编写Select语句？答案见下页Select

as男性,man.wifeas妻子ID,

as女性fromman

leftjoinwomanonman.wife=woman.id;4.4.2【实训4–9】外连接查询（续四，全外连接）4．全外连接（full?，SQLServer使用full，但是MySQL不支持）列出两边表的所有数据正确的代码是将右外连接rightjoin和左外连接leftjoin的结果合并union

到一起还要加上distinct，去除重复的已婚夫妻Select

as男性,man.wifeas妻子ID,

as女性fromman

fulljoinwomanonman.wife=woman.id;Select

as男性,man.wifeas妻子ID,as女性fromman

right

outerjoinwomanonman.wife=woman.iduniondistinctSelect

,man.wife,fromman

left

outerjoinwomanonman.wife=woman.id;4.4.3【实训4–10】自连接查询根据Jitor校验器的要求，在dbForge上完成“【实训4–10】自连接查询”一个家庭，6人，父母、祖父母、外祖父母，其中祖母已逝，不在数据库中【实训4-10】4.4.3【实训4–10】自连接查询（续一）1．查询每个人的父亲Select

姓名,

父亲frommemberasmejoinmemberasfatheronme.father_id=father.id;me（自己）father（父亲）物理上一张表member逻辑上两张表asme,asfather4.4.3【实训4–10】自连接查询（续二）2．查询每个人的父亲和母亲Select

姓名,

父亲,

母亲frommemberasmejoinmemberasfatheronme.father_id=father.idjoinmemberasmotheronme.mother_id=mother.id;物理上一张表member逻辑上三张表asme,asfather,asmother4.4.3【实训4–10】自连接查询（续三）3．查询所有人的父亲和母亲（包括父母已去世）Select

姓名,

父亲,

母亲frommemberasme

left

joinmemberasfatheronme.father_id=father.id

left

joinmemberasmotheronme.mother_id=mother.id;4.4.3【实训4–10】自连接查询（续四）4．查询所有人的父亲和母亲（父母至少一人健在）Select

姓名,

父亲,

母亲frommemberasmeleftjoinmemberasfatheronme.father_id=father.idleftjoinmemberasmotheronme.mother_id=mother.idwhereme.father_idisnotnull--父亲健在orme.mother_idisnotnull;--或母亲健在想一想，如何查询祖父母？如何查询外祖父母？4.4.4【实训4–11】统计与分组统计根据Jitor校验器的要求，在dbForge上完成“【实训4–11】统计与分组统计”【实训4-11】4.4.4【实训4–11】统计与分组统计（续一）1．简单的统计Selectcount(*)图书数量,round(avg(col_price),2)平均价格,min(col_price)最低价格,max(col_price)最高价格fromlib_book;这时，结果永远只有一行4.4.4【实训4–11】统计与分组统计（续二）2．分组统计Selectid_lib_publisher,count(*)图书数量,round(avg(col_price),2)平均价格,min(col_price)最低价格,max(col_price)最高价格fromlib_bookgroupbyid_lib_publisher;这时，有多少个组，就有多少行例中，三个出版社就有三行4.4.4【实训4–11】统计与分组统计（续三）3．having子句Selectid_lib_publisher,count(*)图书数量,round(avg(col_price),2)平均价格,min(col_price)最低价格,max(col_price)最高价格fromlib_bookgroupbyid_lib_publisher

having

平均价格<80;Having用于对统计结果进行过滤例中，只列出平均价格低于80元的出版社4.4.4【实训4–11】统计与分组统计（续四）having和where的比较Selectid_lib_publisher,count(*)图书数量,round(avg(col_price),2)平均价格,min(col_price)最低价格,max(col_price)最高价格fromlib_bookwherecol_price<80groupbyid_lib_publish