4)DML操作

DML操作:

Data Manipulation Language,数据操纵语言;包含insert、update、delete操作;

1、单行插入数据格式:

insert into 表名【字段列表】 values(值列表);

向教师表插入:

insert into teacher values('001','李老师', '1110000000');
insert into teacher(teacher_no, teacher_name,teacher_contact) values('002','王老师','1110000001');
insert into teacher(teacher_name,teahcer_no,teacher_contact) values('孙老师','003','1110000002');

 向班级表插入:

insert into classes values(null,'2023自动化1班','自动化');
insert into classes(class_name,department_name) values('2023自动化2班','自动化');
insert into classes values(3,'2023自动化3班','自动化');

 向课程表插入:

insert into course values(null,'c语言',default,'暂无','已审核','001');
  或者 insert into course (course_name,description,status,teacher_no) values('c语言','暂无','已审核','001');
insert into course values(null,'MySQL',150,'暂无','已审核','002');
insert into course values(nuil,'c++',230,'暂无','已审核','003');

 2、批量插入多行数据格式:

insert into 表名【字段列表】 values(值列表1),(值列表2),..(值列表n);

向学生表批量插入:

insert into student values('2023001','张三','2220000000',1),('2023002','李四','2220000001',1),('2023003','王五','2220000002',3),('2023004','马六','2220000003',2),('2023005','田七','2220000004',2);

 

 3、查询插入多行数据:

insert into 目标表名【字段列表1】 select(字段列表2) from 源表 where 条件表达式;

先创建一个新表:

create table stu1 like student;
insert into stu1 select * from student;
select * from stu1;

 4、update语句:

update 表名 set 字段名1=值1[,字段名2=值2] [where 条件表达式];

新建一个表,做测试:

create table exam (
    stu_no int auto_increment primary key,
    exam_score tinyint unsigned,
    regular_score tinyint unsigned
);
insert into exam values(null,80,85),(null,99,90),(null,65,70),(null,52,null),(null,20,null);

update exam set  exam_score=exam_score+5;
update exam set exam_score=100 where exam_score>=100;
update exam set exam_score=60 where exam_score>=55 and exam_score<60;

 update之后,要满足键值约束;

5、删除表数据:

delete from 表名 [where 条件表达式];

条件删除:

delete from exam where exam_score<=50;

清空表:

 删除也要满足约束限制;

总结:

对于主表:删除、更新数据可能会违反外键约束,添加数据不会违反外键约束;

对于从表:删除数据不会违反外键约束,添加和更新可能会违反外键约束;

6、truncate语句:

截断表,相当于没有where字句的delete语句;但是不能用于主表,即使没有子表,也不能删除;

格式:

truncate table table_name;

测试:

truncate table student;
insert into exam values(null,80,80);
truncate table exam;
insert into exam values(null,80,80);

 对于自增长字段,truncate之后,编号重新从1开始;但是delete数据,编号仍然继续增加;

使用truncate的对于自增长字段的影响:

 

热门相关:首席的独宠新娘   夫人你马甲又掉了   修仙界最后的单纯   法医娇宠,扑倒傲娇王爷   战神