数据库基本增删改查、分页查询、模糊查询、多表查询、分组查询

增删改查

新增：

新增数据：

添加一条数据：

insert into 表名 values (列的值，列的值…)

如果某一列是时间：

1.用引号引起来

insert into 表名 values(‘2022-05-20 11:11:00’,‘zhangsan’)

2.通过sql语句中的函数now()来插入当前时间

insert into 表名 values(now(),‘zhangsan’)

插入时可以插入某一列或几列，其他的都为默认值null

insert into 表名 （列名，列名） values (列的值，列的值)

同时插入多行：

insert into 表名 values (列的值)，（列的值）…

新增字段（表中多一列）

1.在一个已经建好的表中添加一列：

alter TABLE user add COLUMN email VARCHAR(20) not null

在user表中添加一列名为email的字段（会加在表的最后一列位置）

2.将添加的一列指定在某一列后面：

alter TABLE user add COLUMN age VARCHAR(20) not null AFTER phone

在user表中添加age字段，放在phone字段后面

3.将添加的一列放在第一列：

alter TABLE user add COLUMN sex VARCHAR(20) not null first

删除

删除数据

delete from 表名 where 条件

删除表

drop 表名

修改

修改数据

update 表名 set 列名=值，列名=值… where 条件

查询

1.最基本的查找：
把所有的列和行都查询出来

select * from 表名

不建议使用 * 通配符
2.指定列查询

select 列名、列名… from 表名 where 条件

3.针对查询结果去重（使用distinct）

select distinct 列名，列名… from 表名 where 条件

4.排序，在查询的时候对某个列进行排序,如果某一行的值为null，null为最小值

select 列名，列名… from 表名 where 条件 order by 列名 asc/desc

order by要写在where之后 ，limit之前
asc 升序
desc 降序
如果不写asc或desc，则默认升序

分页查询：

1.使用limit
sql语句：
select 字段 from table limit offset,rows
offset 是行的偏移量（从哪个下标开始查）。
row是行的最大数目（显示几条记录）。

offset可以省略：
select 字段 from table limit rows
优化：
（1）使用子查询优化：
先定位偏移位置的id,然后往后查询，这种方法适用于id递增。
子查询只查主键id,然后使用子查询中确定的主键关联查询其他的属性字段。
减少回表操作。

select answer from e_repository where id >= (select id from e_repository where safe_type_id=1 LIMIT 100,1) limit 5

使用子查询可能会遇到的错误：
1.错误：Subquery returns more than 1 row

产生原因：子查询结果多于一行，即子查询中查询的结果多于一个解决办法：

解决办法：在子查询条件语句后加limit 1,或者 在子查询前加关键字any或 some 或 in（in的用法稍有不同）

2.错误：This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

使用的这个版本mysql是不支持在子查询中使用limit分页查询的

（2）使用id限定优化：
id between … and…

select answer from e_repository where id between 100 and 300 limit 5

模糊查询

使用 LIKE ‘字符串’
字符串可以是一个完整的字符串，也可以包含通配符，LIKE关键字支持两种通配符：百分号%、下划线_ 。
通配符：
%是mysql最常用的通配符，它能代表任何长度的字符串，字符串的长度可以为0.
a%b 表示是以a开头，b结尾的任意长度的字符串。
匹配的字符串必须加单引号或双引号。
_ 只能代表单个字符，字符的长度不能为0
大小写：
LIKE默认不支持大小写，要想区分可加BINABY关键字。

多表查询

分为连接查询和子查询。
连接查询分为：内连接、外连接、自连接
子查询分为：标准子查询，列子查询，行子查询，表子查询。

连接查询：

内连接

内连接有隐式内连接、显式内连接。
隐式内连接：

select 字段 from 表1，表2 where 条件

显式内连接：

select 字段 from 表1 join 表2 on 条件

内连接查询的是两个表格交集部分

外连接

外连接有左外连接和右外连接。

左外连接：

select 字段 from 表1 left join 表2 on 条件

查询的是左表全部内容和左表和右表的交集部分。

右外连接：

select 字段 from 表1 right join 表2 on 条件

查询的是右表全部内容和左右表的交集。

自连接

是同一张表用两个别名查询，可以是内连接查询，也可以是外连接查询。

select 字段 from 表A 别名1 join 表A 别名2 on 条件

子查询

标准子查询

返回结果是单个值（数字，字符串，日期等）
常用操作符: = , <> , >, <,>=,<=

列子查询

返回结果是一列（可以是多行）。
常用操作符：in ，out in , some (满足一个) ， all （满足全部）

行子查询

返回结果是一行（或多列）
常用操作符：=， <>,in , not in

表子查询

返回结果是多行多列

条件查询

聚合函数：
对表中的数据进行统计和计算，一般结合分组来使用，用于统计和计算分组数据。
常用的聚合函数：
1.count():表示求指定列的总行数

select count(age) from 表名

2.max():表示求指定列的最大值

select MAX(age) from 表名

3.min():表示求指定列的最小值

select MIN(age) from 表名

4.sum():表示求指定列的和

select SUM(age) from 表名

5.avg():表示求指定列的平均值

select AVG(age) from 表名

聚合函数默认忽略字段为null的记录，要想列值为null的记录也参与计算，使用ifnull函数对null值做替换。

分组查询：

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的为一组。
基本语法格式：

GROUP BY 列名 [ HAVING 条件表达式 ] [WITH ROLLUP]
列名是指按照指定字段的值进行分组
HAVING条件表达式是用来过滤分组后的数据
WITH ROLLUP：在所有记录的最后加上一条记录

使用：
group by 可用于单个字段分组，也可用于多个字段分组。

group by + 聚合函数的使用：

select sex,AVG(age) from user GROUP BY sex

统计不同性别的人的平均年龄

group by + having的使用：
过滤分组数据

select sex,COUNT() from user GROUP BY sex HAVING COUNT()>1

根据sex进行分组，统计分组条数大于1的组

group by + with rollup的使用：

select sex,COUNT(*) from user GROUP BY sex WITH ROLLUP

根据sex进行分组，汇总总人数。