03.MySQL添加、更新与删除数据

添加数据

1.向student表中添加一条新纪录，记录中id的字段的值为1，name的字段为zhangsan,grade的字段为98.5

#创建数据库：
CREATE DATABASE chapter03;
#选择使用数据库chapter03：
USE chapter03;
#创建student表：
CREATE TABLE student(
   id   INT(4),
   name    VARCHAR(20) NOT NULL,
   grade    FLOAT
);
#插入一条数据：
INSERT INTO student(id,name,grade) 
VALUES(1,'zhangsan',98.5);

2.为表的指定字段添加数据

向student表中添加一条新纪录
INSERT INTO student(name,grade,id)

VALUES('lisi',95,2);

INSERT INTO student

VALUES(3,'lisi',61.5);

INSERT INTO student(id,name)

VALUES(4,'zhaoliu');

INSERT INTO student(id,grade) 

VALUES(5,97);

INSERT INTO student(grade,name)
VALUES(55,'sunbin');

其他写法：

INSERT INTO student
SET id=5,name='boya',grade=99;

3.添加三条记录：

INSERT INTO student VALUES

(6,'lilei',99),
(7,'hanmeimei',100),

(8,'poly',40.5);

INSERT INTO student(id,name) VALUES
(9,'liubei'),(10,'guanyu'),(11,'zhangfei');

更新数据
1.更新student表中id字段值为1的记录，将记录中的name字段的值更改为caocao,grade字段的值更改为50

UPDATE student

set name='caocao',grade=50
WHERE id=1;

2.更新表中id字段小于4的记录

UPDATE student
SET grade=100
WHERE id<4;

UPDATE student
SET grade=80;

删除数据
1.删除部分数据

DELETE FROM student
WHERE id=11;

DELETE FROM student
WHERE id>5;

2.删除所有记录

DELETE FROM student;

3.使用关键字TRUNCATE删除表中数据

#建库
CREATE TABLE tab_truncate(
    id INT(3) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(4)
);

#插入数据
INSERT INTO tab_truncate(name)
VALUES('A'),('B'),('C'),('D'),('E');

#删除记录
TRUNCATE TABLE tab_truncate;

#查询
SELECT * FROM tab_truncate;

4.在空表重新添加5条记录

INSERT INTO tab_truncate(name)

VALUES('F'),('G'),('H'),('I'),('J');

#删除

DELETE FROM tab_truncate;

#插入数据

INSERT INTO tab_truncate(name) VALUES('K');

#查询
SELECT * FROM tab_truncate;