2 and id 10、mysql 中对表数据的操作：
①【增加数据 --- insert into】
select * from user；：查看表中所有的数据
insert into user(id,name,class,phone)values(1,'zhangsan',1001,'13866666666')；：在user表中插入一条数据
insert into user(id,name,class,phone)values(2,'lisi',1001,13800000000),(3,'wangwu',1002,13899999999)；：在user表中插入多条数据
insert into user(name)values('zhaoliu')；：在user表中增加一个字段的值，如果id是自增长，会自动填充一个值
insert into user values(5,'rongqi',1003,15833333333)；：不写字段名称，直接插入数据，在插入的数据中所包含的字段数，刚好等于表结构中的字段数，则可以直接插入
null：代表空的属性，代表当前位置对应的存储空间没有被占用，不代表空格，也不是0
手动输入的null：代表当前的存储空间，存储的是null这个值
②【查询数据 --- select】
select * from user；：查看表中所有的数据
select id,name from user；：只显示其中指定的字段
select * from user where class = 1001；：查询1001班所有的数据
select id,name from user where class = 1001；：查询1001班的学号和姓名
select phone,name from user；：输入的字段顺序就是显示出来的顺序
select * from user where class !=1002；：查询不是1002班的数据
select phone from user where class = 1001 and name = 'lisi'；：查询1001班的lisi的电话号码，多条件查询
select * from user where class = 1001 or class = 1003；：查询1001班和1003班的所有数据
select * from user where id > 2 and id < 7；：查询id为3-6行的数据
select * from user where id between 3 and 6；：查询id为3-6行的数据
select * from user where id in (3,4,5,6)；：查询id为3-6行的数据
select * from user where id not in (3,5,6)；：查询除了3、5、6行以外的数据
select * from user limit 0,3；：显示1-3行的数据，0代表起始位置的角标，3代表显示的行数
select * from user limit 2,4；：显示3-6行的数据，2代表起始位置的角标，4代表显示的行数
select * from user where class is null；：查询不属于任何班级的学生信息
select * from user where class is not null；：查询有班级信息的学生信息
select * from user where name like 'li%'；：查询user表中姓li的同学，%代表模糊匹配的意思
select * from user where name like '%liu'；：查询user表中叫什么什么liu的同学，%放在前面就是模糊前面的内容
select * from user where name like '%gs%'；：查询user表中名字里面带有”gs“的同学，前面后面都可以模糊匹配
3；","marks":[{"type":"fontSize","value":18},{"type":"bold"}]},{"text":"：查询id平均值大于3 的班级及其人数，","marks":[{"type":"fontSize","value":18}]},{"text":"group by 后面不能接条件的时候不能用where，要用having","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"KifS-1672038370600","name":"paragraph","data":{"style":{}},"nodes":[{"type":"text","id":"hYRP-1672038370598","leaves":[{"text":"","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"RU8O-1672038272238","name":"paragraph","data":{"style":{"textIndent":0}},"nodes":[{"type":"text","id":"7Fyy-1672038272236","leaves":[{"text":"group by 的特性：","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"FZQc-1672038290433","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"kg8F-1672038290431","leaves":[{"text":"1）、group by 后面不能接条件的时候不能用where，要用having","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"X0oZ-1672038302203","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"vZhE-1672038302201","leaves":[{"text":"2）、使用group by 分组后，仅有分组的字段，和聚合函数，才能放在from之前","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"yZqN-1672037782546","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"srVy-1672037782545","leaves":[{"text":"3）、having 后面一般是用身份运算符，或者聚合函数","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"iwtK-1672044617977","name":"paragraph","data":{"style":{"textIndent":0}},"nodes":[{"type":"text","id":"6DSy-1672044617976","leaves":[{"text":"","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"gA3t-1672044624785","name":"paragraph","data":{},"nodes":[{"type":"text","id":"6GAQ-1672044624784","leaves":[{"text":"\r","marks":[]}]}],"state":{}},{"type":"block","id":"wZE7-1672044629953","name":"paragraph","data":{"version":1,"style":{"textIndent":0}},"nodes":[{"type":"text","id":"lhK0-1672044629954","leaves":[{"text":"单表查询总结","marks":[{"type":"fontSize","value":18},{"type":"bold"}]},{"text":"\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"cPQr-1672044629955","name":"paragraph","data":{"version":1,"style":{"textIndent":28}},"nodes":[{"type":"text","id":"koK6-1672044629956","leaves":[{"text":"1、where 不能放在GROUP BY 后面\r，where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，\r即在分组之前过滤数据，条件中不能包含聚组函数比如SUM()、AVG()等，使用\rwhere条件显示特定的行。\r","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"8kGb-1672044629963","name":"paragraph","data":{"version":1,"style":{"textIndent":28}},"nodes":[{"type":"text","id":"Jzzm-1672044629964","leaves":[{"text":"2、having 是跟GROUP BY 连在一起用的，放在GROUP BY 后面，此时的作用相当\r于WHERE。\rhaving 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含\r聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"kNq1-1672038373541","name":"paragraph","data":{"style":{}},"nodes":[{"type":"text","id":"AWor-1672038373540","leaves":[{"text":"","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"yRtF-1672038373678","name":"paragraph","data":{"style":{}},"nodes":[{"type":"text","id":"Lrbq-1672038373676","leaves":[{"text":"⑦【聚合函数】","marks":[{"type":"fontSize","value":18},{"type":"bold"},{"type":"color","value":"#FF0001"}]}]}],"state":{}},{"type":"block","id":"0lJa-1672037503144","name":"paragraph","data":{"style":{}},"nodes":[{"type":"text","id":"LnJr-1672037503142","leaves":[{"text":"1)、count()：统计","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"nvYU-1672040482192","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"xbu7-1672040482190","leaves":[{"text":"select count(*) from user；","marks":[{"type":"fontSize","value":18},{"type":"bold"}]},{"text":"：统计user表中的数据量","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"rGU3-1672040584694","name":"paragraph","data":{"style":{}},"nodes":[{"type":"text","id":"s3cI-1672040584692","leaves":[{"text":"2)、avg()：求平均数","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"CDsz-1672040392731","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"6U0W-1672040392729","leaves":[{"text":"select avg(id) from user；","marks":[{"type":"fontSize","value":18},{"type":"bold"}]},{"text":"：统计user表中id的平均数","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"kI5j-1672040687548","name":"paragraph","data":{},"nodes":[{"type":"text","id":"4XPx-1672040687546","leaves":[{"text":"3)、sum()：求和","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"0vdN-1672040470787","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"DjCv-1672040470785","leaves":[{"text":"select sum(id) from user；","marks":[{"type":"fontSize","value":18},{"type":"bold"}]},{"text":"：求user表中id的和","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"pubI-1672040685327","name":"paragraph","data":{"style":{}},"nodes":[{"type":"text","id":"v6TD-1672040685325","leaves":[{"text":"4)、max()：最大值","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":" B8D-1672040739453","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"lo3Q-1672040739452","leaves":[{"text":"select max(id) from user；","marks":[{"type":"fontSize","value":18},{"type":"bold"}]},{"text":"：求user表中的最大值","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"RHzt-1672040792804","name":"paragraph","data":{"style":{}},"nodes":[{"type":"text","id":"1rgx-1672040792803","leaves":[{"text":"5)、min()：最小值","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"k1FZ-1672040780551","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"ijxD-1672040780549","leaves":[{"text":"select min(id) from user；","marks":[{"type":"fontSize","value":18},{"type":"bold"}]},{"text":"：求user表中的最大值","marks":[{"type":"fontSize","value":18}]}]}],"state":{}},{"type":"block","id":"hkS5-1672040685469","name":"paragraph","data":{"style":{}},"nodes":[{"type":"text","id":"2KSd-1672040685468","leaves":[{"text":"6)、distinct()：去重显示","marks":[{"type":"fontSize","value":18},{"type":"bold"}]}]}],"state":{}},{"type":"block","id":"JmQj-1672041100492","name":"paragraph","data":{"style":{"textIndent":28}},"nodes":[{"type":"text","id":"mrrp-1672041100490","leaves":[{"text":"select distinct(phone) from user；","marks":[{"type":"fontSize","value":18},{"type":"bold"}]},{"text":"：将电话号码去重显示（不显示重复的号码）","marks":[{"type":"fontSize","value":18}]}]}],"state":{}}]">③【修改数据 --- update...set】
update user set class = 1002 where class is null；：把没有班级信息的人，班级信息修改为1002班
update user set phone = 18577777777 where phone = 13811111111；：修改电话字段，把138的号码修改为185
④【删除数据 --- delete，drop，truncate】
delete from user where class = 1001；：删除1001班级的数据
delete from user；：删除表数据，删除user表中的所有数据（先读取有多少行数据，再从第一条开始删除，运行速度慢，不会释放储存空间）
drop table user；：删除表结构 drop database：删除数据库
truncate tbuser；：快速清空表数据（逐条推进删除，数据不能恢复，会释放出储存空间，慎用）
create table tbuser like user；：创建一个跟user表一样的表结构
insert into tbuser select * from user；：将 user 表的数据插入到 tbuser 表中
⑤【排序 --- order by】
select * from user order by id asc；：根据id字段进行升序排序，关键字：asc
select * from user order by id；：根据id进行排序，默认为升序排序
select * from user order by id desc；：根据id字段进行降序排序，关键字：desc
⑥【分组 --- group by】
select * from user group by class；：根据班级进行分组，显示的是每个组别中第一个数据，会丢失数据
select class,count(*) from user group by class；：统计每个班的人数，一般group by 与聚合函数连用
select class,count(*) from user group by class having avg(id)>3；：查询id平均值大于3 的班级及其人数，group by 后面不能接条件的时候不能用where，要用having
group by 的特性：
1）、group by 后面不能接条件的时候不能用where，要用having
2）、使用group by 分组后，仅有分组的字段，和聚合函数，才能放在from之前
3）、having 后面一般是用身份运算符，或者聚合函数
单表查询总结
1、where 不能放在GROUP BY 后面，where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数比如SUM()、AVG()等，使用where条件显示特定的行。
2、having 是跟GROUP BY 连在一起用的，放在GROUP BY 后面，此时的作用相当于WHERE。having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。
⑦【聚合函数】
1)、count()：统计
select count(*) from user；：统计user表中的数据量
2)、avg()：求平均数
select avg(id) from user；：统计user表中id的平均数
3)、sum()：求和
select sum(id) from user；：求user表中id的和
4)、max()：最大值
select max(id) from user；：求user表中的最大值
5)、min()：最小值
select min(id) from user；：求user表中的最大值
6)、distinct()：去重显示
select distinct(phone) from user；：将电话号码去重显示（不显示重复的号码）
|
|
457
0
多测师社区 
