数据基础及应用 9

第4章使用SQL进行数据库操作4.4基本查询4.3数据删除数据插入4.2数据更新4.5嵌套查询CONTENTS目录

4.1连接查询

4.6窗口函数查询

4.74.1数据插入4.1.1INSERT语句语法INSERT语句向表添加新行，基本语法：INSERT[INTO]table_name[(column_list)]VALUES(value_list)table_name：接收数据的表或视图名称column_list：列的列表，用圆括号括起，逗号分隔VALUES：引入要插入的数据值列表省略column_list时，默认包含表中所有列并按定义顺序排列4.1.2基本插入操作（例4-1~4-3）例4-1简单INSERT（省略列名，按顺序插入）：INSERTcourseVALUES('C903','大学物理','必修',4);例4-2按指定列顺序插入数据：INSERTcourse(name,code,category,mark)VALUES('艺术欣赏','C606','选修',2);例4-3显式指定列插入（未给值的列应允许为空）：INSERTcourse(name,code,category)VALUES('绘画技巧','C607','选修');4.1.3特殊插入操作（例4-4~4-5）例4-4将数据插入到带有自增列的表：CREATETABLEtest01(idintAUTO_INCREMENTPRIMARYKEY,namevarchar(30));INSERTtest01(name)VALUES('a01');--系统自动生成标识值INSERTtest01(id,name)VALUES(99,'a02');--手动指定值4.1.3特殊插入操作（例4-4~4-5）（续）例4-5插入数据到score表（JSON类型）：INSERTscoreVALUES('{"chinese":90,"math":86,"english":85}');

id列自增、created列为timestamp类型，无需提供值4.1.4批量插入与查询结果插入（例4-6~4-7）例4-6一次性插入多条记录（效率更高）：INSERTINTOcourseVALUES('C907','Java程序设计','选修',2),('C908','Python程序设计','选修',3);例4-7将查询结果插入到表中：INSERTINTOcourse_bak(id,name,category,mark)SELECTid,name,category,markFROMcourse;查询结果的字段类型必须与插入字段类型匹配4.2数据更新4.2数据更新（例4-8~4-9）UPDATE语句更改表或视图中单行、多行或所有行的数据语法：UPDATEtable_nameSETcolumn_name=expression[,..][WHEREcondition]例4-8将所有课程的学分加1（不带WHERE更新全部行）：UPDATEcourseSETmark=mark+1;4.2数据更新（例4-8~4-9）（续）

例4-9使用WHERE子句限定更新范围：UPDATEcourseSETmark=mark+1WHEREcategory='必修';注意：通常应通过WHERE限制被更新的记录4.3数据删除4.3.1DELETE语句（例4-10~4-11）DELETE语句删除表或视图中的一行或多行语法：DELETE[FROM]table_name[WHEREcondition]例4-10删除所有行（不带WHERE）：DELETEFROMtest01;例4-11删除特定行（带WHERE条件）：DELETEFROMcourseWHEREid='C607';DELETE删除数据但表结构保留，DROPTABLE则删除表本身4.3.2TRUNCATETABLE语句（例4-12）TRUNCATETABLE一次删除表中所有行，速度更快语法：TRUNCATETABLE表名;与DELETE的区别：DELETE逐行删除并记日志，TRUNCATE释放数据页只记页释放TRUNCATE重置自增列计数器为种子值TRUNCATE不能用于被外键约束引用的表TRUNCATE不激活触发器例4-12：TRUNCATETABLEcourse;4.4基本查询4.4.1简单查询（例4-13~4-15）语法：SELECT[ALL|DISTINCT]select_listFROMtable_name[LIMITn]例4-13查询student表所有记录：SELECT*FROMstudent;4.4.1简单查询（例4-13~4-15）（续）例4-14查询指定列并使用函数计算年龄：SELECTid,name,Year(curDate())-Year(birthday)ASageFROMstudent;例4-15使用COUNT函数查询学生人数：SELECTCOUNT(*)AStotalFROMstudent;4.4.1聚合函数（例4-16~4-18）常用聚合函数：COUNT()、AVG()、MAX()、MIN()、SUM()例4-16查询所有学生mark的平均值：SELECTAVG(mark)ASavg_markFROMstudent;例4-17查询所有学生mark的最大值：SELECTMAX(mark)ASmax_markFROMstudent;例4-18查询所有学生mark的总和：SELECTSUM(mark)ASsum_markFROMstudent;4.4.2带条件查询（例4-19~4-21）WHERE子句指定查询条件，比较符：=、!=、>、>=、<、<=例4-19查询mark>=600的学生：SELECT*FROMstudentWHEREmark>=600;例4-20查询所有选修课程：SELECTid,name,category,markFROMcourseWHEREcategory='选修';4.4.2带条件查询（例4-19~4-21）（续）例4-21使用BETWEEN查询学分在2~5之间的课程：SELECT*FROMcourseWHEREmarkBETWEEN2and5;等价于：WHEREmark>=2ANDmark<=54.4.2特殊运算符查询（例4-22~4-24）例4-22使用LIKE模糊查询（%匹配任意字符）：SELECT*FROMcourseWHEREnameLIKE'%数据库%';例4-23使用IN查询多个值之一：SELECTcourse_id,markFROMstudent_courseWHEREcourse_idIN('C606','C607');例4-24使用ISNULL查询空值：SELECT*FROMmajorWHEREbriefISNULL;注意：不能用'=null'，必须用'ISNULL'4.4.2逻辑运算符组合查询（例4-25~4-27）AND：两条件都为真；OR：之一为真；NOT：取反例4-25查询专业为会计学且性别为女的学生：SELECT*FROMstudentWHEREmajor='会计学'ANDgender='女';例4-26查询专业为金融学或会计学的学生：SELECT*FROMstudentWHEREmajor='金融学'ORmajor='会计学';例4-27查询JSON字段（score表成绩都及格）：4.4.2逻辑运算符组合查询（例4-25~4-27）（续）SELECT*FROMscoreWHEREmark->'$.chinese'>60ANDmark->'$.math'>60ANDmark->'$.english'>60;4.4.3查询结果排序与重定向（例4-28~4-29）ORDERBY子句排序输出：ASC升序（默认），DESC降序例4-28按专业升序，专业相同的按成绩降序：SELECTid,name,gender,major,markFROMstudentORDERBYmajorASC,markDESC;重定向输出：CREATETABLE新表SELECT查询结果例4-29将查询结果存入新表st_new：CREATETABLEst_new(SELECT*FROMstudentWHEREis_bonus=true);4.4.3联合查询（例4-30）UNION操作符将不同查询的数据组合起来UNION自动去除重复行，UNIONALL保留全部合并规则：两个SELECT必须输出同样的列数各相应列的数据类型必须相同仅最后一个SELECT可用ORDERBY4.4.3联合查询（例4-30）（续）例4-30查询工程管理或工程力学专业的学生：SELECTid,name,majorFROMstudentWHEREmajor='工程管理'UNIONSELECTid,name,majorFROMstudentWHEREmajor='工程力学';4.4.3分组统计与筛选（例4-31~4-33）GROUPBY分组，HAVING对分组结果筛选HAVING作用于组，WHERE作用于基本表或视图例4-31按category统计课程门数：SELECTcategory,COUNT(category)AScFROMcourseGROUPBYcategory;例4-32查询每门课程的最高分：SELECTcourse_id,max(mark)ASmax_mark4.4.3分组统计与筛选（例4-31~4-33）（续）FROMstudent_courseGROUPBYcourse_id;例4-33查询平均成绩>=80的课程：SELECTcourse_id,AVG(mark)ASavg_markFROMstudent_courseGROUPBYcourse_idHAVINGAVG(mark)>=80;4.4.3限制返回与去重（例4-34~4-37）LIMIT[偏移量,]行数--偏移量从0开始例4-34返回前3名（按mark降序）：SELECT*FROMstudentORDERBYmarkDESCLIMIT3;例4-35返回第5行开始的3条记录：SELECT*FROMstudentORDERBYmarkDESCLIMIT4,3;DISTINCT去除重复记录例4-37查询学生专业并去重：SELECTDISTINCT(major)FROMstudent;4.5嵌套查询4.5.1单值嵌套查询（例4-38）嵌套查询：在一个SELECT的WHERE中嵌入另一个SELECT处理方式：由里向外，先处理最内层子查询单值嵌套查询：子查询返回一个值可直接使用=、<>、>、<、>=、<=等运算符例4-38查询和李思思相同专业的同学：4.5.1单值嵌套查询（例4-38）（续）SELECTid,nameFROMstudentWHEREmajor=(SELECTmajorFROMstudentWHEREname='李思思');执行过程：先查李思思的专业，再查该专业所有学生4.5.2多值嵌套查询-ANY与ALL（例4-39~4-40）多值嵌套查询：子查询返回多个值需配合ANY、ALL、IN、EXISTS等运算符使用例4-39ANY运算符（比子查询任一值高即满足）：SELECTstudent_id,markFROMstudent_courseWHEREcourse_id='C901'ANDmark>ANY(SELECTmarkFROMstudent_courseWHEREcourse_id='C902');4.5.2多值嵌套查询-ANY与ALL（例4-39~4-40）（续）含义：比C902的最低成绩高例4-40ALL运算符（比子查询所有值高才满足）：...ANDmark>ALL(SELECTmark...WHEREcourse_id='C902');含义：比C902的最高成绩还高4.5.2多值嵌套查询-IN与EXISTS（例4-41~4-42）例4-41IN运算符（等价于=ANY）：SELECTid,nameFROMstudentWHEREidIN(SELECTstudent_idFROMstudent_courseWHEREcourse_id='C901'ORcourse_id='C902');含义：查询选修了C901或C902课程的学生例4-42EXISTS运算符（子查询返回行则为true）：4.5.2多值嵌套查询-IN与EXISTS（例4-41~4-42）（续）SELECTid,nameFROMcourseWHEREEXISTS(SELECTidFROMstudent_courseWHEREcourse_id='C901'ORcourse_id='C902');NOTEXISTS返回结果与EXISTS相反4.6连接查询4.6.1连接概述（例4-43）连接查询：根据表间逻辑关系从多个表中检索数据连接可在WHERE子句或FROM子句中建立FROM子句建立连接的语法：FROMjoin_table[join_type]JOINjoin_tableONjoin_condition连接类型：内连接(INNERJOIN)、外连接(OUTERJOIN)、交叉连接(CROSSJOIN)4.6.1连接概述（例4-43）（续）例4-43输出所有学生成绩单（三表连接）：SELECTst.id,,c.id,,sc.markFROMstudentst,coursec,student_coursescWHEREst.id=sc.student_idANDc.id=sc.course_id;4.6.2内连接（例4-44~4-46）内连接分3种：等值连接、不等值连接、自然连接例4-44等值连接（统计每门课程选课人数）：SELECTname,count(*)cFROMcourseINNERJOINstudent_courseONcourse.id=student_course.course_id;例4-45不等值连接（查询成绩高于某同学的同学）：SELECTa.student_id,a.markFROMstudent_coursea4.6.2内连接（例4-44~4-46）（续）INNERJOINstudent_coursebONa.course_id=b.course_idANDa.mark>b.markWHEREb.course_id='C901'ANDb.student_id='S0101';例4-46自然连接：SELECTa.*,b.*FROMstudent_courseaINNERJOINcoursebONa.course_id=b.id;4.6.3外连接（例4-47~4-48）左外连接(LEFTOUTERJOIN)：包括左表未匹配行，右表字段置NULL右外连接(RIGHTOUTERJOIN)：包括右表未匹配行，左表字段置NULL例4-47学生表左外连接选课表：SELECTa.id,,b.course_id,b.markFROMstudentaLEFTOUTERJOINstudent_coursebONa.id=b.student_id;4.6.3外连接（例4-47~4-48）（续）未选课学生的course_id和mark显示为NULL例4-48选课表右外连接课程表：SELECTa.student_id,a.mark,b.id,FROMstudent_courseaRIGHTOUTERJOINcoursebONa.course_id=b.id;无人选修的课程信息也显示出来4.6.4交叉连接（例4-49）交叉连接(CROSSJOIN)不带WHERE子句返回两个表的笛卡尔积结果行数=第一个表行数x第二个表行数例4-49学生表交叉连接选课表：SELECTa.id,,b.student_id,b.course_id,b.markFROMstudentaCROSSJOINstudent_courseb;学生表每一行都和选课表所有记录进行连接4.7窗口函数查询4.7窗口函数查询（例4-50~4-51）MySQL8.0开始支持窗口函数（也叫OLAP函数）窗口函数对每组记录执行计算但不像GROUPBY合并行常用窗口函数：ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()、AVG()等语法：函数名()OVER(PARTITIONBY列名ORDERBY列名)4.7窗口函数查询（例4-50~4-51）（续）例4-50查询本专业平均成绩：SELECTid,name,major,mark,AVG(mark)OVER(PARTITIONBYmajor)FROMstudentORDERBYmajor;例4-51按成绩降序排名（成