mysql复盘，②增删改

– 添加：insert
– 1.确定表 2.确定列 3.每个列对应的值
– 第一种（不确定列名):要求值的顺序必须和你原表列的顺序一致
INSERT into student values(17,‘文’,‘男’,‘78787878’,‘1999-01-01’,1);

– INSERT into student values(‘张颖’,18,‘男’,‘7879895436’,‘1999-01-01’,1);
– 违反了外键约束
– INSERT into student values(18,‘张颖’,‘男’,‘79898932’,‘1999-01-01’,5);
– 违反了主键约束
– INSERT into student values(1,‘信双双’,‘女’,‘78798022’,‘1999-01-01’,2);

– 学号是一个自增，代表学号不要显示的指定值，可以让学号自己增长
– 不支持自增
– INSERT into student values(‘张运明’,‘男’,‘7878’,‘1999-01-01’,1);

– 第二种方式：确实列名，要求值的顺序必须和你列的顺序一致，建议用第二种
INSERT into student(name,stuid,phone,sex,cid,birthday)
VALUES(‘双’,18,‘676878’,‘女’,1,‘2000-01-02’);

– INSERT into student(name,stuid,phone,sex,cid,birthday)
– VALUES(18,‘孔玉’,‘676878’,‘男’,1,‘2000-01-02’);

– 支持自增
INSERT into student(name,phone,sex,cid,birthday)
VALUES(‘峰’,‘4335’,‘男’,1,‘2000-01-02’);

– 支持一次性添加多条数据
INSERT into student(name,phone,sex,cid,birthday)
VALUES(‘耀’,‘35363’,‘男’,1,‘2000-01-02’)
,(‘成’,‘35252’,‘男’,1,‘2000-01-02’);

create table stu1(
stuid int not null,
stuname VARCHAR(50),
sex VARCHAR(2)
)charset=utf8;

drop table stu1;
– 复制数据
INSERT into stu1(stuid,stuname,sex)
select stuid,name,sex from student;

select * from student;
select * from classinfo;
– UPDATE 修改一定要加上where条件，不加where条件会把当前
– 表中字段对应的值都改成了新值，where条件一般是主键
– 1.把学号是1的学员姓名改为张三
update student SET name=‘张三’ where stuid=1;
– 一次性修改多个列的值
update student set name=‘张大山’,sex=‘女’ where stuid=3;

– 违反了主键约束
– update student set stuid=3 where stuid=1;
– 违反了外键约束
– update student set cid=5 where stuid=1;

– 删除 DELETE 一定加上where条件 一般是主键
– 删除学号是18的学员信息
DELETE from student where stuid=18;

– 删除表：把表结构和表数据全删了
DROP table student;