删除
delete from xiaohu where id=5;==》指定删除id为5的数据
delete from xiaohu where id>=4; ===>删除大于等于4的数据
delete from xiaohu; ===>删除全表数据
drop table test; 删除表结构及数据;
truncate test;一次删除全表数据,不能删除部分表数据;
修改
update xiaohu set phone=13366666666 where id=1; ==》把id=1的phone字段对应的值修改为13366666666;
查询:
select * from 表名;===查询表中所有数据
select * from 表名 where name=”xiaohu“;查询表中名字叫chenhan的表数据
select * from hujun where phone!=1327748888;==查询表中手机号不等于1327748888的数据
select * from hujun where phone<>=1327748888;==查询表中手机号不等于1327748888的数据
select * from hujun where name="xiaohu" and phone=1327788888;==查询name为xiaohu及phone为1327788888的表数据,同时满足多个条件使用and。
select * from hujun where name="xiaohu" or phone=13344788888;==查询name为xiaohu或phone为1327788888的表数据,满足一个条件
mysql> select * from hhh where id between 1 and 4;==查询id1到4之间的数据
select * from xiaohu where id in(3); ==》
select * from xiaohu where phone in(12388888888,13566666666);
select * from xiaohu where name like "xiao%"; ===>查询xiao开头的
select * from xiaohu where name like "%hu"; ====》查询hu结尾的
select * from xiaohu where name like "%ow%"; ====》查询中间包含ow的
select * from xiaohu limit 1,3;===》显示2到4行
select * from xiaohu limit 3;====》显示前5行
limint m,n(m为下标,n为上标)
排序asc 从小到到大--升序select * from xiaohu order by id asc;desc 从大到小-- 降序select * from xiaohu order by id desc;
select * from hujun group by class; ==》通过class字段进行分组select * from hujun group by class having soce=98;==》通过class进行分组,然后查询soce等于98的数据
常用的 聚合函数
count(*)-----统计
select count(*) from xiaohu; 统计 xiaohu表中数据条数
sum()-----求和
select sum(id) from xiaohu; 求ID的和
select sum(soce) from xiaohu where class=18; ===》求18班的分数之和
avg()----求平均
select avg(id) from xiaohu; 求平均分
max()----求最大
select max(id) from xiaohu; 求最多值
min()----求最小
select min(id) from xiaohu; 求最小值
distinct----去重
select distinct(time) from xiaohu; 去重
单表查询总结
|
|