»增
新增命令,必填字段不写入值会报错
insert into 表名 (字段1,字段2,字段3) values (值1,值2,值3);
例:insert into t_class (id,class,teacher) values (5,"机械班","王也");
»删
delete from 表名 where 条件;
例:delete from t_results where class is null;
»改
update 表名 set 字段1 = 值1 where 条件;
例:update t_student set sname = "王亮",age = 22 where id = 1;
»查
select 字段1,字段2 from 表名 where 条件;
例:select * from t_student;
select * from t_student where xuili = "专科";
»多表联查
select 字段1 from 表1 join 表2 on 表关系 where 条件;
例:select t_student.id,t_student.sname,t_class.class,t_results.chinese from t_student join t_class on t_student.classname = t_class.id join t_results on t_student.id = t_results.sid where t_results.chinese >65;
也可用“as”对表名/字段进行临时的重命名:select a.id,a.sname,b.class,c.chinese from t_student a join t_class b on a.classname = b.id join t_results c on a.id = c.s_id where c.chinese >65;
练习:
A.查询姓“王”的学生的个数
B.查询“数学”比“语文”成绩高的所有学生的学号
A:select count(sname) as '姓王的学生个数' from t_student where sname like"王%";
B:select t_student.id as "数学比语文成绩高的学生" from t_results join t_student on t_results.sid = t_student.id where math > chinese;
select b.id as "数学比语文成绩高的学生" from t_results a join t_student b on a.sid = b.id where math > chinese;
网友评论