(一)添加
1.1全列添加
1.1.1⼀次添加⼀条
insert into score values(1,'张三');
1.1.2⼀次添加多条
insert into score values
(2,'李四'),
(3,'王五');
1.2
指定列添加
⼀次添加⼀条:insert into score(id,name) values(4,'可可');
⼀次添加多条 :insert into score(id,name) values(5,'乐乐'),(6,'扣扣');
(二)查询
2.1
全列查询
select * from
score
;
2.2
指定列查询
select name,math from
score
;
2.3表达式查询
计算每个⼈的总成绩:select name,chinese+math+english from score;
2.4
使⽤别名
select chinese+math+english as total from score;
2.5
去重 distinct
数学成绩去重:select distinct math from score;
2.6
排序 order by
根据某⼀列升序:order by math asc;
根据某⼀列降序 :
order by math desc;
根据语⽂和数学成绩进⾏组合排序:select name,chinese,math from score order by
chinese,math;
2.7条件查询(注意:where 中不能使⽤别名。)
数学成绩为 NULL 的⼈:select * from score where math is null;
数学成绩及格的⼈ :
select * from score where math>60
;
语⽂成绩⼤于数学成绩的⼈ :
select * from score where chinese>math;
总成绩⼤于
150
分的⼈ :
select name,math+chinese+english as total from score where
math+chinese+english>150;
语⽂成绩和数学成绩都 >
80
分的⼈ :
select name,math,chinese from score where math>80
and
chinese>80
;
语⽂成绩或数学成绩⼤于
80
分的⼈ :
select name,math,chinese from score where math>80
or
chinese>80;
between x and y:select name,math from score where math between
60 and 90;
in 查询 (
数学成绩等于
99
和
100
分的同学 ):
select name,math from score where math
in(99,100);
模糊查询:%:匹配任意字符;
_:匹配⼀个字符。
2.8 分页
总分数排名前三 :
select name,chinese+math+english as total from score order by total
desc
limit 3;
总分数第⼆⻚(4-6条数据)
:select name,chinese+math+english as total from score order
by
total desc limit 3 offset 3;
(三)修改
全部修改:update score set math=
85
;
部分修改 :
update score set math=
85
where name='可可';
修改多列 :
update score set math=
86
,chinese=
86
where name='乐乐';
(四)删除
删除单条 :
delete from score where name='可可';
全部删除 :
delete from score;
清空表 :
truncate [表名] ;
delete 和 truncate的区别:truncate 删除快,但是数据不能被恢复;delete 删除慢,但数据能被恢
复。
delete 删除可以加条件,⽽ truncate 不能加条件。
delete 和 drop 的区别 :
delete:只删除表中的数据;drop:删除表数据+表结构。