【MySQL】表基础：CRUD操作

文章目录一、新增数据Create1.1 语法格式1.2 具体案例1.2.1 单行数据 全列插入1.2.2 多行数据 指定列插入二、查询数据Retrieve2.1 语法格式2.2 具体案例2.2.1 全列查询2.2.2 指定列查询2.2.3 查询字段为表达式2.2.4 别名2.2.5 去重distinct2.2.6 排序order by2.2.7 条件查询where2.2.8 分页查询limit三、修改数据Update3.1 语法格式3.2 具体案例四、删除数据Delete4.1 语法格式4.2 具体案例五、重要操作总结5.1 新增5.2 查询5.3 修改5.4 删除CRUD是增加Create、查询Retrieve、更新Update、删除Delete四个单词的首字母缩写它涵盖了对数据库表数据的基本操作是日常开发中与数据库交互的主要方式。一、新增数据Create新增数据即向数据库表中插入一条或多条记录MySQL提供了灵活的语法来满足不同的插入需求。1.1 语法格式insert[into]表名[(字段1[,字段2]...)]values(值1)[,(值2)]...1.2 具体案例创建一张学生表droptableifexistsstudent;createtablestudent(idint,snintcomment学号,namevarchar(20)comment姓名,qq_mailvarchar(20)commentQQ邮箱);1.2.1 单行数据 全列插入当需要插入一条数据并且为表中的所有列都赋值时可以使用这种方式。注意value_list的数量必须和定义表的列的数量及顺序一致。如果不一致会报错并且这条数据无法插入。-- 插入两条记录insertintostudentvalues(01,10000,Daisy,NULL);insertintostudentvalues(02,10001,Rare,11111);1.2.2 多行数据 指定列插入如果只需要为表中的部分列插入数据或者一次插入多条数据可以采用这种方式。此时value_list的数量必须和指定列的数量及顺序一致。-- 插入两条记录insertintostudent(id,sn,name)values(102,20001,曹孟德),(103,20002,孙仲谋);允许为null的字段没有指定的数据默认为null二、查询数据Retrieve查询数据是从数据库表中获取所需信息的操作MySQL提供了丰富的查询语法可以满足各种复杂的查询需求。2.1 语法格式select[distinct]{*|{column[,column]...}from表名[where...][orderbycolumn[asc|desc],...][limit...]distinct去重*查询所有列column指定要查询的列from 表名指定查询的表where添加查询条件order by对查询结果排序limit分页查询。2.2 具体案例先创建一张考试成绩表并插入测试数据droptableifexistsexam_result;createtableexam_result(idint,namevarchar(20),chinesedecimal(3,1),mathdecimal(3,1),englishdecimal(3,1));-- 插入测试数据insertintoexam_result(id,name,chinese,math,english)values(1,daisy,67,98,56),(2,rare,87.5,78,77),(3,postcard,88,98.5,90),(4,exile,82,84,67),(5,daylight,55.5,85,45),(6,22,70,73,78.5),(7,imagination,75,65,30);2.2.1 全列查询全列查询即查询表中的所有列使用*表示。但通常情况下不建议使用*进行全列查询原因如下查询的列越多意味着需要传输的数据量越大会影响查询效率。可能会影响到索引的使用索引相关知识将在后续文章讲解。select*fromexam_result;2.2.2 指定列查询指定列查询是只查询表中我们需要的列指定列的顺序不需要按定义表的顺序来。selectid,name,englishfromexam_result;2.2.3 查询字段为表达式查询字段可以是一个表达式表达式既可以不包含字段也可以包含一个或多个字段。注意原表中本身不包含表达式所有的查询结果都是通过临时表返回给用户。表达式不包含字段效果是让所有列都包含一个表达式的值selectname,chinese,math,10fromexam_result;表达式包含一个字段selectchinese10fromexam_result;表达式包含多个字段列与列之间可以参与运算selectid,name,chinesemathenglishfromexam_result;2.2.4 别名为查询结果中的列指定别名表示返回的结果集中以别名作为该列的名称。使用as关键字也可以省略来指定别名。别名可以用单引号引起来也可以直接写。但如果别名中本身包含空格则必须使用单引号表示一个整体。-- 结果集中表头的列名别名selectid,name,chinesemathenglishas总分fromexam_result;-- 省略 as关键字selectid,name,chinesemathenglish 总分fromexam_result;2.2.5 去重distinct当查询结果中某列存在重复数据时我们可以使用distinct关键字对该列数据进行去重。注意在指定的查询字段中行与行之间每一列的数据都相等MySQL才认为是重复数据。去重后只保留一条数据。例如查询考试成绩表中的数学成绩其中98分出现了重复selectmathfromexam_result;去重后selectdistinctmathfromexam_result;2.2.6 排序order by使用order by子句可以对查询结果进行排序asc表示升序从小到大默认desc表示降序从大到小。注意没有order by子句的查询返回的顺序是未定义的永远不要依赖这个顺序。null数据排序视为比任何值都小升序出现在最上面降序出现在最下面在数值比较中小于负数。查看表结构也用到了desc关键字describe描述排序中desc表示降序descend下降按指定列排序-- 按 math 升序显示selectname,mathfromstudentorderbymath;-- 按 math 降序显示selectname,mathfromstudentorderbymathdesc;使用表达式及别名排序-- 按总分降序显示使用表达式selectid,name,chineseenglishmathfromexam_resultorderbychineseenglishmathdesc;-- 按总分降序显示使用别名selectid,name,chineseenglishmath totalfromexam_resultorderbytotaldesc;注意null与其他数据类型运算得到的结果都为null与数字中的0不同。在布尔类型判断时始终判断为false对多个字段进行排序排序优先级随书写顺序-- 依次按数学降序、英语升序、语文升序显示select*fromexam_resultorderbymathdesc,chineseasc,englishasc;2.2.7 条件查询where条件查询是根据指定的条件筛选出符合要求的数据WHERE子句中可以使用比较运算符和逻辑运算符。比较运算符运算符说明, , , 大于、大于等于、小于、小于等于等于NULL 不安全例如NULL NULL的结果是 NULL等于NULL 安全例如NULL NULL的结果是 TRUE(1)!, 不等于between a0 and a1范围匹配[a0, a1]左右闭区间如果 a0 value a1返回 TRUE(1)in (option, …)如果是 option 中的任意一个返回 TRUE(1)is NULL是 NULLis not NULL不是 NULLlike模糊匹配。% 表示任意多个包括 0 个任意字符_ 只能匹配任意一个字符,相当于占位符逻辑运算符运算符说明AND多个条件必须都为 TRUE(1)结果才是 TRUE(1)OR任意一个条件为 TRUE(1)结果为 TRUE(1)NOT条件为 TRUE(1)结果为 FALSE(0)注意事项where条件可以使用表达式但不能使用别名。and的优先级高于or在同时使用时需要使用小括号()包裹优先执行的部分。基本查询案例-- 查询英语不及格的同学及英语成绩 60select*fromexam_resultwhereenglish60;-- 查询语文成绩好于数学成绩的同学select*fromexam_resultwheremathchinese;-- 查询总分在 200 分以下的同学selectid,name,mathchineseenglishastotalfromexam_resultwheremathchineseenglish200orderbytotalasc;注意同一行数据列与列之间可以比较但不同行的数据列与列不可以比较不可以把别名作为查询条件,必须把表达式写完整。这种现象与MySQL执行的顺序有关先确定表from exam_result在查询的过程中先根据符合条件的数据过滤出来(where)执行select后指定的列加入结果集中排序order byand与or案例-- 查询语文成绩大于80分且英语成绩大于80分的同学select*fromexam_resultwherechinese80andenglish80;-- 查询语文成绩大于80分或英语成绩大于80分的同学select*fromexam_resultwherechinese80orenglish80;范围查询案例between ... and ...-- 查询语文成绩在 [80, 90] 分的同学及语文成绩selectname,chinesefromexam_resultwherechinesebetween80and90;in-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩selectname,mathfromexam_resultwheremathin(58,59,98,99);模糊查询like案例模糊查询的字符串要用单引号引起来-- % 匹配任意多个包括 0 个字 匹配easy exileselect*fromexam_resultwherenamelikee%;-- _ 匹配严格的一个任意字符 匹配exileselect*fromexam_resultwherenamelikee____;NULL查询案例-- 查询 qq_mail 已知的同学姓名selectname,qq_mailfromstudentwhereqq_mailisnotNULL;-- 查询 qq_mail 未知的同学姓名selectname,qq_mailfromstudentwhereqq_mailisNULL;2.2.8 分页查询limit当查询结果数据量较大时我们可以使用LIMIT子句进行分页查询避免一次性加载过多数据影响性能。LIMIT子句中起始下标为0。语法格式从0开始筛选n条作为结果select...fromtable_name[where...][orderby...]limitn;从第s条开始筛选n条结果select...fromtable_name[where...][orderby...]limits,n;从s开始筛选n条结果offset表示偏移量更明确建议使用select...fromtable_name[where...][orderby...]limitnoffsets;如果s超出整个结果集也是可以执行的返回结果是空结果集案例按math升序进行分页每页3条记录分别显示第1、2、3页-- 第 1 页select*fromexam_resultorderbymathlimit3offset0;-- 第 2 页select*fromexam_resultorderbymathlimit3offset3;-- 第 3 页如果结果不足 3 个不会有影响select*fromexam_resultorderbymathlimit3offset6;s n*(当前页号-1)三、修改数据Update修改数据即更新表中已有的记录通过update语句实现。3.1 语法格式update表名set字段1值1[,字段2值2...][where...][orderby...][limit...]where指定修改的条件order by对数据进行排序后再修改limit限制修改的行数。3.2 具体案例-- 将daisy同学的数学成绩变更为 80 分updateexam_resultsetmath80wherenamedaisy;-- 将rare同学的数学成绩变更为 60 分语文成绩变更为 70 分updateexam_resultsetchinese70,math60wherenamerare;-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分updateexam_resultsetmathmath30orderbychinesemathenglishlimit3;-- 将所有同学的语文成绩更新为原来的 2 倍updateexam_resultsetchinesechinese*2;注意当条件匹配到多条数据会修改需要修改的数据如果有相匹配并且数据是要修改的目标值就不会再次修改。如果没有条件限制会把表中的所有数据都修改是非常危险的操作sql不支持-*/运算符四、删除数据Delete删除数据即删除表中的记录通过delete语句实现。4.1 语法格式deletefrom表名[where...][orderby...][limit...]where指定删除的条件order by对数据进行排序后再删除limit限制删除的行数。4.2 具体案例-- 删除daisy同学的考试成绩deletefromexam_resultwherenamedaisy;-- 删除整表数据deletefromexam_result;五、重要操作总结5.1 新增-- 单行插入insertinto表(字段1,...,字段N)values(value1,...,valueN);-- 多行插入insertinto表(字段1,...,字段N)values(value1,...),(value2,...),(value3,...);5.2 查询-- 全列查询select*from表;-- 指定列查询select字段1,字段2...from表;-- 查询表达式字段select字段1100,字段2字段3from表;-- 别名select字段1别名1,字段2别名2from表;-- 去重distinctselectdistinct字段from表;-- 排序order byselect*from表orderby排序字段;-- 条件查询WHERE可使用(1)比较运算符 (2)between ... and ... (3)in (4) (5)like (6)and (7)or (8)notselect*from表where条件;5.3 修改update表set字段1value1,字段2value2...where条件orderby排序字段limit行数;5.4 删除deletefrom表where条件orderby排序字段limit行数;