【MYSQL】基本查询（表的增删查改）--详解

表的增删改查CRUD : Create(创建), Retrieve(读取)Update(更新)Delete删除给大家补充小知识点后面还会持续更新的mysql不区分大小写都可以针对 SQL 关键字和数据库名、表名、字段名mysql不支持select支持数学运算一.Create语法INSERT [INTO] table_name [(column [, column ...])] VALUES (value_list) [, (value_list)] ...部分含义是否必须INSERT [INTO]插入数据的关键字INTO可省略必须table_name要插入数据的表名必须[(column [, column ...])]指定要插入的列名可省略省略全列插入可选VALUES表示后面跟要插入的值必须(value_list)一组值对应前面指定的列必须[, (value_list)] ...可以跟多组值表示一次插入多行可选全列插入时VALUES左侧不指定列名右侧必须为表中所有列按定义顺序提供对应值数量和类型必须完全匹配按列插入时VALUES左侧明确列出所需列名右侧只需为这些列提供对应值其余列自动使用默认值或NULL。两者核心区别在于是否显式指定列及值的数量是否与表结构一致。创建一张学生表1-1单行数据 全列插入插入两条记录value_list 数量必须和定义表的列的数量及顺序一致注意这里在插入的时候也可以不用指定id(当然那时候就需要明确插入数据到那些列了)那么mysql会使用默认的值进行自增1-2多行数据 指定列插入插入两条记录value_list 数量必须和指定列数量及顺序一致我前面还补充了几个都是用的单行插入但是不影响讲解1-3插入否则更新由于主键 或者 唯一键对应的值已经存在而导致插入失败可以选择性的进行同步更新操作 语法INSERT ... ON DUPLICATE KEY UPDATEcolumn value [, column value] ...之前还插入了一些为了方便我后续进行其他的操作INSERT ... ON DUPLICATE KEY UPDATE是一种有条件的插入或更新操作当插入的数据没有与表中现有的主键或唯一键发生冲突时就执行普通的插入操作。当插入的数据与主键或唯一键发生冲突即该键的值已存在时则不插入新记录而是更新该冲突行中指定的列。示例INSERT INTO students (id, name, score) VALUES (1, 张三, 90) ON DUPLICATE KEY UPDATE name VALUES(name), score VALUES(score);VALUES(列名)表示本次要插入的新值。可以同时更新多个列用逗号分隔。0 row affected表中已存在冲突数据主键或唯一键重复且冲突行的当前值与UPDATE子句中指定的新值完全相同因此无需实际修改故影响行数为 0。1 row affected表中没有冲突数据执行的是纯插入操作因此影响行数为 1。2 row affected表中存在冲突数据且冲突行的原值与UPDATE子句中的新值不同因此先删除旧行1 行影响再插入新行1 行影响总共影响 2 行。简单记忆0 表示有冲突但值未变1 表示无冲突直接插入2 表示有冲突且值被更新。通过 MySQL 函数获取受到影响的数据行数返回值场景-1执行的是SELECT等非修改语句0有冲突但新旧值相同无实际修改1无冲突执行插入2有冲突且执行了更新尝试插入一条(id13, sn132, namexuyou, qq1111111)的记录但如果主键id13已经存在就改为更新这条已存在记录的sn、name、qq字段为指定值。执行结果2 rows affected的含义2 rows affected表示发生了主键冲突且执行了更新操作。在INSERT ... ON DUPLICATE KEY UPDATE中1 row affected--没有冲突执行插入2 rows affected--有冲突执行更新MySQL 内部是先删旧行再插新行的逻辑尝试插入一条id13, sn132, namexuyou, qq1111111的记录但如果id13已经存在主键冲突则改为更新这条记录将它的sn、name、qq分别设为指定的值。Query OK, 1 row affected表示没有发生冲突id13原本不存在执行的是插入操作所以新行(13,132,xuyou,1111111)被成功插入ALUES (13,132,xuyou,1111111)要插入的数据id13, sn132, namexuyou, qq1111111ON DUPLICATE KEY UPDATE如果主键id或唯一键冲突就执行更新sn 132, name xuyou, qq 11111111冲突时把这些列更新成新值ON DUPLICATE KEY UPDATE是先删除再插入不是“比较新旧值是否相同”这就是“指定的值”的意义冲突时以 UPDATE 里写的为准而不是插入时的值。指定的值”指的是ON DUPLICATE KEY UPDATE后面等号右边你手动写的那个值1-4替换主键 或者 唯一键 没有冲突则直接插入主键 或者 唯一键 如果冲突则删除后再插入rows affected含义1无冲突插入新行2有冲突执行了删除 插入数据可能变也可能不变但 MySQL 物理上做了删插0有冲突且新旧数据完全相同MySQL 跳过了实际修改三次执行结果对应的“替换情况”第一次1 row affected没有冲突没有发生任何替换插入新行第二次2 rows affected冲突发生发生了替换将id18这一行的sn/name/qq替换成 UPDATE 里指定的值虽然插入值和旧值可能不同但 MySQL 执行了物理删插第三次0 rows affected冲突发生没有发生有效替换因为 UPDATE 里的值和当前行已经一模一样MySQL 认为“无需替换”和REPLACE的区别容易混淆的点语句是否删除整行是否可能丢列自增行为REPLACE是会丢未指定的列一定会新增自增 IDINSERT ... ON DUPLICATE KEY UPDATE否逻辑是更新只改指定列不一定会新增自增 ID除非物理删插1 row affected表中没有冲突数据数据被插入。2 row affected表中有冲突数据删除后重新插入。二.Retrieve语法SELECT[DISTINCT] {* | {column [, column] ...}[FROM table_name][WHERE ...][ORDER BY column [ASC | DESC], ...]LIMIT ...部分含义是否必须SELECT查询关键字必须[DISTINCT]去重去掉结果集中重复的行可选{* | column [, column ...]}选择哪些列*表示所有列或列出指定列名必须FROM table_name从哪张表查必须[WHERE condition]筛选条件只返回满足条件的行可选[ORDER BY ...]排序按指定列升序ASC或降序DESC排列可选[LIMIT count]限制返回的行数可选2-1select列1)全列查询通常情况下不建议使用 * 进行全列查询1. 查询的列越多意味着需要传输的数据量越大2. 可能会影响到索引的使用。索引待后面课程讲解2)指定列查询指定列的顺序不需要按定义表的顺序来3查询字段为表达式表达式不包含字段表达式不包含字段表达式包含一个字段表达式包含对个字段4为查询结果指定别名语法SELECT column [AS] alias_name [...] FROM table_name;5结果去重98 分重复了2-2WHERE 条件比较运算符在 MySQL 中用于判断两个值是否相等但不能用来判断NULL因为NULL NULL的结果不是TRUE而是NULL不会匹配任何行。判断NULL必须使用IS NULL或IS NOT NULL。此外NULL表示“未知值”与数字0或空字符串完全不同不要混用。逻辑运算符练习英语不及格的同学及英语成绩 ( 60 )基本比较语文成绩在 [80, 90] 分的同学及语文成绩使用 AND 进行条件连接使用 BETWEEN ... AND ... 条件数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩使用 OR 进行条件连接使用 IN 条件姓孙的同学 及 孙某同学% 匹配任意多个包括 0 个任意字符_ 匹配严格的一个任意字符语文成绩好于英语成绩的同学WHERE 条件中比较运算符两侧都是字段总分在 200 分以下的同学WHERE 条件中使用表达式别名不能用在 WHERE 条件中语文成绩 80 并且不姓孙的同学AND 与 NOT 的使用孙某同学否则要求总成绩 200 并且 语文成绩 数学成绩 并且 英语成绩 80综合性查询NULL 的查询查询 qq 号已知的同学姓名NULL 和 NULL 的比较 和 的区别三.结果排序练习语法-- ASC 为升序从小到大-- DESC 为降序从大到小-- 默认为 ASCSELECT ... FROM table_name [WHERE ...]ORDER BY column [ASC|DESC], [...];注意没有 ORDER BY 子句的查询返回的顺序是未定义的永远不要依赖这个顺序同学及数学成绩按数学成绩升序显示我验证了颠倒一下啥感觉同学及 qq 号按 qq 号排序显示NULL 视为比任何值都小升序出现在最上面NULL 视为比任何值都小降序出现在最下面查询同学各门成绩依次按 数学降序英语升序语文升序的方式显示多字段排序排序优先级随书写顺序查询同学及总分由高到低ORDER BY 中可以使用表达式ORDER BY 子句中可以使用列别名查询姓孙的同学或者姓曹的同学数学成绩结果按数学成绩由高到低显示结合 WHERE 子句 和 ORDER BY 子句筛选分页结果语法起始下标为 0从 s 开始筛选 n 条结果SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;从 0 开始筛选 n 条结果SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;从 s 开始筛选 n 条结果比第二种用法更明确建议使用SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;建议对未知表进行查询时最好加一条 LIMIT 1避免因为表中数据过大查询全表数据导致数据库卡死 按 id 进行分页每页 3 条记录分别显示 第 1、2、3 页第 3 页如果结果不足 3 个不会有影响LIMIT的本质功能是控制结果显示的数量与起始位置而不是条件筛选。它的执行顺序非常靠后必须先有数据经过WHERE筛选、JOIN关联、ORDER BY排序等完整的数据准备阶段最后才由LIMIT决定“从第几行开始显示、显示多少行”。也就是说LIMIT不参与“哪些数据要被拿出来”的判断只参与“拿出来的数据怎么展示”的最终控制。因此LIMIT不能理解为“条件过滤”而应理解为结果集的截取与分页展示工具。四.Update语法UPDATE table_name SET column expr [, column expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]对查询到的结果进行列值更新将孙悟空同学的数学成绩变更为 80 分更新值为具体值查看原数据数据更新查看更新后数据将曹孟德同学的数学成绩变更为 60 分语文成绩变更为 70 分一次更新多个列查看更新后数据将总成绩倒数前三的 3 位同学的数学成绩加上 30 分更新值为原值基础上变更查看原数据别名可以在ORDER BY中使用数据更新不支持 math 30 这种语法更新后查看更新后数据思考这里还可以按总分升序排序取前 3 个么按总成绩排序后查询结果将所有同学的语文成绩更新为原来的 2 倍注意更新全表的语句慎用没有 WHERE 子句则更新全表查看原数据数据更新查看更新后数据五. 删除数据删除数据语法DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]删除孙悟空同学的考试成绩查看原数据删除数据查看删除结果删除整张表数据注意删除整表操作要慎用准备测试表插入测试数据查看测试数据删除整表数据查看删除结果再插入一条数据自增 id 在原值上增长查看数据查看表结构会有 AUTO_INCREMENTn 项截断表语法TRUNCATE [TABLE] table_name截断整表数据影响行数是 0所以实际上没有对数据真正操作注意这个操作慎用1. 只能对整表操作不能像 DELETE 一样针对部分数据操作2. 实际上 MySQL 不对数据操作所以比 DELETE 更快但是TRUNCATE在删除数据的时候并不经过真正的事物所以无法回滚3. 会重置 AUTO_INCREMENT 项准备测试表插入测试数据查看测试数据截断整表数据注意影响行数是 0所以实际上没有对数据真正操作查看删除结果再插入一条数据自增 id 在重新增长查看数据查看表结构会有 AUTO_INCREMENT2 项这张表叫for_truncate有一个自增主键id下一个自增值是 2。使用utf8mb4字符集不区分大小写和重音的排序规则存储引擎是 InnoDB。AUTO_INCREMENT2表示下一个自增 id 的值是 2说明表中已经有一条记录id1或者曾经有过 id1 的记录后来被删除但自增不会回退DELETE和TRUNCATE的区别两者都可以清空表中的数据但行为上有本质区别。对自增计数器的影响DELETE FROM不会重置自增计数器下次插入时 id 会从之前的值继续递增而TRUNCATE会重置自增计数器下次插入时 id 从 1 开始。事务与回滚DELETE FROM支持事务每行删除都在事务中执行可以通过ROLLBACK回滚恢复数据TRUNCATE不支持事务不经过事务处理一旦执行就无法回滚数据永久丢失。因此TRUNCATE操作必须慎用。操作粒度DELETE FROM可以带WHERE条件只删除部分数据TRUNCATE只能整表清空不能加任何条件。执行效率DELETE FROM是逐行删除并记录每行的日志所以速度较慢TRUNCATE本质上是直接释放数据页不逐行操作也不记录每行的日志所以速度远快于DELETE。六.插入查询结果语法INSERT INTO table_name [(column [, column ...])] SELECT ...删除表中的的重复复记录重复的数据只能有一份创建原数据表插入测试数据错误思路直接用distinct正确思路创建一张空表 no_duplicate_table结构和 duplicate_table 一样将 duplicate_table 的去重数据插入到 no_duplicate_table通过重命名表实现原子的去重操作查看最终结果为什么最后是通过 rename 方式进行的具体过程是先创建一个临时表或新表。在临时表中完成所有的数据清洗、去重、合并等操作这个过程可能很慢但它不影响线上业务。确认临时表中的数据已经准备就绪、完全正确后再通过一条RENAME TABLE语句原子地将原表替换成新表RENAME TABLE的本质是把“复杂的数据准备过程”和“最终生效”分开。准备阶段在幕后偷偷做最后通过一条原子指令瞬间切换做到业务无感知、操作可回滚。一.当前在 bash 看到了文件说明你已经在 MySQL 的数据目录里比如cd /var/lib/mysql/test_db/ ls -l但去重操作不能在 bash 里做必须回到 MySQL 命令行。二、回到 MySQL 命令行mysql -u root -p输入密码然后进入对应数据库USE test_db;三、查看当前有哪些表SHOW TABLES;应该能看到duplicate_table原表有重复数据no_duplicate_table临时空表你已经创建好了四、向临时表插入去重数据INSERT INTO no_duplicate_table SELECT * FROM duplicate_table GROUP BY name;这里假设按name去重。如果你想按其他字段把name换成你的字段名。五、原子替换表名RENAME TABLE duplicate_table TO duplicate_table_old, no_duplicate_table TO duplicate_table;这一步执行完新表duplicate_table上线无重复数据原表被备份为duplicate_table_old六、验证结果SELECT * FROM duplicate_table;确认数据正确后删除备份表DROP TABLE duplicate_table_old七. 聚合函数练习统计班级共有多少同学使用 * 做统计不受 NULL 影响使用表达式做统计统计本次考试的数学成绩分数个数COUNT(math) 统计的是全部成绩COUNT(DISTINCT math) 统计的是去重成绩数量distinct 在括号内因为是要对 math 去重而不是对 count() 的结果去重。NULL 不会计入结果。统计数学成绩总分不及格 60 的总分没有结果返回 NULL有直接返回成绩统计数学平均成绩统计平均总分返回英语最高分返回 70 分以上的数学最低分8.group by子句的使用在select中使用group by 子句可以对指定列进行分组查询select column1, column2, .. from table group by column;目的为了进行分组后方便进行聚合统计。准备工作创建一个雇员信息表来自oracle 9i的经典测试表EMP员工表DEPT部门表SALGRADE工资等级表1. DEPT部门表2. EMP员工表3. SALGRADE工资等级表数据插入1.如何显示每个部门的平均工资和最高工资2.如何显示每个部门的平均工资和最高工资分组GROUP BY的本质GROUP BY指定列名后实际分组是以该列中不同取值的行来划分的。分组后同一组内的该列值一定是相同的这意味着该列可以被“聚合压缩”——即多个行在分组后合并为一个组组内的其他列需要通过聚合函数如SUM、AVG、MAX、MIN、COUNT来处理。理解分组的一种方式分组就是把一张表按照指定的条件在逻辑上拆分成多个子表每个子表对应一个组。然后针对每个子表单独进行聚合统计。例如按部门编号分组就是把员工表拆成“10号部门子表”、“20号部门子表”、“30号部门子表”等然后分别统计每个子表的平均工资、最高工资等。3.显示每个部门的每种岗位的平均工资和最低工资4.显示平均工资低于2000的部门和它的平均工资统计各个部门的平均工资having和group by配合使用对group by结果进行过滤having经常和group by搭配使用作用是对分组进行筛选作用有些像where5.having VS where 的区别与执行顺序是什么的。都能够做条件筛选这是它们的共性。但它们是完全不同的条件筛选它们的条件筛选的阶段是不同的。WHERE是在分组之前对原始行进行筛选筛完再分组HAVING是在分组之后对聚合结果进行筛选。所以WHERE里不能用AVG、SUM这类聚合函数但HAVING里可以。执行顺序是FROM→WHERE→GROUP BY→HAVING。能用WHERE提前筛掉数据就尽量别等分组后靠HAVING再筛这样效率更高。理解 MySQL 中的“表”不要单纯地认为只有在磁盘上将表结构导入到 MySQL、真实存在的表才叫表。在 MySQL 中一切查询结果都是逻辑上的表。中间筛选出来的结果集是表最终输出的结果也是表子查询返回的结果还是表甚至一个聚合统计后的输出同样是表只要你能够处理好单张表的增删改查CURD所有的 SQL 场景都可以用统一的方式去理解和操作。因为无论是多表查询、子查询、分组统计还是聚合计算本质上都是在“逻辑表”上做操作——只是这个表可能是临时的、筛选过的、聚合后的但它依然遵循单表操作的基本规则。【细节补充】SQL查询中各个关键字的执行先后顺序 from on join where group by with having select distinct order by limit