一、表的增删查改1.1 插入数据1.1.1insert语法INSERT [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ... value_list: value, [, value] ...使用示例1.单行数据全列插入2.单行数据指定列插入3.多行数据全列插入4.多行数据指定列插入在insert插入过程中可能会因为主键或者唯一键对应的值已经存在而导致插入失败此时我们可以选择性地同步更新操作语句语法如下INSERT ... ON DUPLICATE KEY UPDATE column value [, column value] ...在更新数据时要保证不能和其他的主键或唯一键冲突1.1.2 replace在发生主键或唯一键冲突时除了使用上面的update我们还可以直接使用replace来替换语法REPLACE [INTO] table_name [(column [, column] ...)] VALUES (value_list) [, (value_list)] ... value_list: value, [, value] ...replace在主键或唯一键没用冲突时会直接进行插入当发生冲突时则先对数据进行删除再进行插入操作。使用示例1.2 查询数据1.2.1 select语法SELECT [DISTINCT] {* | {column [, column] ...}} [FROM table_name] [WHERE ...] [ORDER BY column [ASC | DESC], ...] LIMIT ...使用示例1.全列查询select * from table_name但是通常情况下不建议使用*来进行全列查询原因是一个数据库中可能有几千万条数据如果全部查询需要传输的数据量非常大非常浪费资源其次全列查询可能会影响索引的使用。2.指定列查询select xx from table_name指定列查询时还可以同时指定多列进行查询select会将查询的结果拼入表格中同时在查询时我们可以对数据进行求和并且将求和的结果重命名后作为新的一列并入查询结果重命名可以带as也可以不带as在使用select时添加distinct字段可以去除对选中的字段的查询结果中重复的数据。例如下图中在不带上distinct字段前的结果是这样的带上distinct字段之后结果如下图所示1.2.2 where条件当我们需要对做结果进行筛选时就需要使用到where条件下面是使用where条件进行条件筛选时较为常用的运算符。1.比较运算符运算符说明,,,大于大于等于小于小于等于等于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 个任意字符_表示任意一个字符2.逻辑运算符运算符说明AND多个条件必须都为 TRUE (1)结果才是 TRUE (1)OR任意一个条件为 TRUE (1), 结果为 TRUE (1)NOT条件为 TRUE (1)结果为 FALSE (0)MySQL中支持多条件组合查询使用示例如下当我们统计表中的总分并查询其中总分小于200的同学的姓名和总分根据前面的知识我们可能会写出这样的语句但是这样的语句是必然会报错的。原因是select是先执行from再执行where按条件筛选最后再执行加和并重命名所以mysql不认识total所以在where筛选时无法识别total所以只能像下面这样写1.2.3 order by使用order by可以对结果进行排序order by默认升序排序需要注意的是使用order by对数据进行排序时首先需要有对应的数据。所以order by要先拿到数据之后才能对数据排序。因此该语句的执行顺序是在重命名语句的后面所以在上面的例子中使用别名不会报错。如果需要使用降序排序我们可以在后面加上desc1.2.4 limit我们可以添加limit选项来筛选分页结果进行简单的分页功能。其中limit n表示从表的第0行开始连续读取n行数据limit 2,4表示从指定位置开始连续读取多少条记录。建议在对未知表进行查询时加上一条limit 1避免因为表中数据过大查询全表数据导致数据库卡死。由于limit的本质功能时进行分页显示所以只有数据准备好了才能进行显示。故limit的执行顺序在order by之后。所以数据的查询操作的执行顺序如下select1-from2-where3-order by4-limit51.3 修改数据1.3.1 update当我们需要修改表中的数据时可以使用语句update语法UPDATE table_name SET column expr [, column expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]使用示例更新数据后的效果1.4 删除数据1.4.1 delete使用delete语句可以删除表中指定的数据语法UPDATE table_name SET column expr [, column expr ...] [WHERE ...] [ORDER BY ...] [LIMIT ...]1.4.2 truncate清除数据的第二种方法截断表语法TRUNCATE [TABLE] table_nametrucnate只能对整表进行操作不能像delete一样针对部分数据进行操作。此外trucnate不会把自己包装成事务不会把自己的操作记录记录到mysql的日志中所以truncate在删除数据时比delete快但是也正是由于它不会把自己的操作记录记录到mysql的日志中所以这个语句不能回滚。同时截断表在删除数据的同时会重置auto_increment项在截断表t1前auto_increment的值为6截断后auto_increment的值就被重置了所以我们在查看创建表的语句时就查不到这一项的值了前面提到过我们可以在查询时添加distinct字段来去除查询结果中重复的值但是当我们要删除表中重复的数据时可以像下面这样做首先建立一个列属性和原表一模一样的新表然后查找去重并插入到新表中然后重命名这个操作与distinct有明显不同distinct是去除查询结果中的数据相当于select查询就是建立了一个新的表而删除表中重复的数据是为了改变原表中的数据。修改前的表首先建立一个新表然后将去重后的查询结果插入到新表中修改表名修改完成后的表二、聚合函数在查询时通常会配合一些聚合函数使用常用的聚合函数有下面这些函数说明COUNT([DISTINCT] expr)返回查询到的数据的数量SUM([DISTINCT] expr)返回查询到的数据的总和不是数字没有意义AVG([DISTINCT] expr)返回查询到的数据的平均值不是数字没有意义MAX([DISTINCT] expr)返回查询到的数据的最大值不是数字没有意义MIN([DISTINCT] expr)返回查询到的数据的最小值不是数字没有意义聚合统计使用示例COUNT([DISTINCT] expr)SUM([DISTINCT] expr)AVG([DISTINCT] expr)MAX([DISTINCT] expr)MIN([DISTINCT] expr)三、分组聚合统计3.1 group by使用group by 语句可以对查询的数据进行分组。分组的目的是为了在进行分组之后方便进行聚合统计。使用示例如下显示每种产品的最贵商品价格和平均商品价格GROUP BY 分组的逻辑是指定列名就会拿这一列不同的值来划分分组。以 category 商品类别分组举例同一个分组里面 category 的值一定是相同的这一列可以直接跟着聚合结果一起输出。分组本质上就是把一张表在逻辑上拆成多个子表之后分别对每一个子表做聚合统计。分组聚合统计的执行顺序是先分组再聚合。group by 后面可以跟多个列联合分组select 里写的普通列必须是 group by 分组里出现过的列否则会报错。3.2 having分完组得到聚合统计的结果之后我们可以使用having对结果进行判断显示平均订单金额低于1000的用户和他的平均订单金额李四不参与统计虽然where和having在语义上是相同的但是在select中不推荐使用having需要将二者区分开3.3 where和having的区别WHERE 是在 GROUP BY 之前对原始数据行做筛选不能使用聚合函数HAVING 是在 GROUP BY 完成分组之后针对分组聚合后的结果做筛选支持写聚合函数。简单来说where 针对原始表的任意列过滤数据having 针对分组聚合之后的结果过滤二者执行的筛选阶段不一样。另外要记住不是只有磁盘上真实存在的表才叫表一条 select 查询出来的结果集本质上也可以看作一张表。理解这点之后只要掌握好单表的增删查改绝大多数 SQL 场景都可以用这套思路去处理。
