Mysql数据库操作
本帖最后由 武汉13期吴德军 于 2022-3-30 19:17 编辑【对表结构的修改】desc user;==》查看表结构
alter table user rename tb_user; ==》将user表名修改为tb_user;
alter table tb_user change id sid int(10); ==>将id字段修改为sid并且去掉自增长约束
alter table tb_user change sid id int(5)auto_increment; ==》将sid字段修改为id并且加上自增长约束;
alter table tb_user add id2 int(10); ==>添加id2这个字段
alter table tb_user add (id3 int(5),id4 int(5));==》同时添加2个字段
alter table tb_user add id5 int(5)first; ==》将id5这个字段放到最前面
alter table tb_user add id6 int(5)after phone; ==》将id6这个字段放到phone字段后
alter table tb_user drop id2;==》删除id2这个字段
alter table tb_user drop id3,drop id4,drop id5,drop id6; ===》同时删除多个字段
drop table tb_user; ==》删除tb_user表
【对表数据的操作】
增加==》insert into
insert into
tb_user(id,name,class,phone,time)values(1,'xiaowu',1801,13522222222,"2022-03-29");
insert into tb_user(name)values("xiaoli"); ==》对指定字段插入数据同时插入多条数据
insert into
user(id,name,class,phone,time)values(4,'dawu',1801,13533333333,"2022-03-28"),(5,"xiaoxu",1802,1331111111,"2022-03-29"),(6,"xiaoyang",1801,1356666666,"2022-03-29");
0不等于null null指的是空的属性,0是代表一个值
查
select * from 表名;
select * from tb_user;==>查询所有数据
select * from tb_user where name="xiaoliu"; ==》查询name为xiaoliu的所有数据
select id from tb_user where name="xiaoliu"; ==》取单个字段值
select id,class,phone from tb_user where name="xiaoliu"; ==》取多个字段值
改
update user set phone=13311111111 where name='xiaozhao';==》修改xiaozhao的数据
update user set phone=13322222222 where id>=5; ==>修改id大于等于5的数据
删
delete from user where id=8; ==>删除id等于8的数据
delete from user; ==》删除全表数据
drop table user;==>删除表数据和表结构
truncate user; ==>删除表数据
单表查询
select * from user where name="daliu"; ==>查询name等于daliu的所有数据
select * from user where name !='daliu';==》查询name字段不等于daliu的数据
select * from user where name <>'daliu';==》查询name字段不等于daliu的数据
select * from user where class=1801 and phone=13533333333;==》查询class为1801且phone为13533333333的所有数据
select * from user where class=1801 or phone=13533333333;==》查询class为1801或者phone为13533333333的所有数据
select * from user where id>4; ==》查询id大于4的数据
select * from user where id>=3 and id<=6;==》查询id大于等于3并且小于等于6的数据
select * from user where id between 3 and 6;==》查询id大于等于3并且小于等于6的数据
select * from user where class in(1801,1802); ==》查询class为1801或1802
select * from user where id not in(1,2);==》查询id 不为1或2的数据
select * from user where class is null; ==》查询class为空的数据,不能直接用字段=null
select * fromuser where class is not null;==>查询class不为空的数据
select * from user where name like "%li%"; 查询name字段所有包含li的数据
select * from user where name like "xiao%";查询name字段以xiao开头的数据
select * from user where name like "%liu";查询name字段以liu结尾的数据
select * from user id limit 1,4;==》查询2到5行数据
select * from user id limit 0,5;==》查询前5行数据limit m,n(m为下标值,n 为查询行数)
排序
从小到大--升序 asc
select * from user order by phone asc;
从大到小--降序 desc
select * from user order by phone desc;
分组
select * from user group by class; ==》通过class字段进行分组
select class,count(*) from user group by class;==》分组后求出每组对应的人数
select class,count(*) from user group by class having class is not null;通过class字段进行分组,然后加条件class不为null
注：
1.group by 分组之后,只能使用having进行条件筛选
2.使用group by 分组之后,仅分组字段和函数可以放置到from前面
常用的聚合函数
count==>统计
sum ==>求和
avg ==>求平均值
max ==>最大值
min ==>最小值
distinct ==>去重
select count(*) from user;==》统计user表中数据条数
select count(id) from user;==》根据id字段进行统计
select sum(score) from user where class=1802; ==》求1802班总成绩
select avg(score) from user where class=1802; ==》求1802班平均成绩
select id,name,max(score) from user where class=1802;==》求1802班最高成绩
select min(score) from user where class=1801; ==》求1802班最低成绩
select distinct(name) from user where class=1802; ==》对name字段去掉重复数据
select id,name,score from user where score in(select min(score) from user where class=1801);求1801最低成绩的id 姓名和成绩
页:
[1]