MySQL 增删改查基础

演示数据表

后面的文章我们将以 students 和 classes 2张数据表为例来演示，表结构 :

classes 班级表

CREATE TABLE `classes`  (
  `class_id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `class_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '班级名称',
  `class_total_number` int NULL DEFAULT 0 COMMENT '学生总数',
  PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;

-- ----------------------------
-- Records of classes
-- ----------------------------
INSERT INTO `classes` VALUES (1, '一年一班', 2);
INSERT INTO `classes` VALUES (2, '一年二班', 1);
INSERT INTO `classes` VALUES (3, '一年三班', 1);
INSERT INTO `classes` VALUES (4, '一年四班', 0);
INSERT INTO `classes` VALUES (5, '一年五班', 1);
INSERT INTO `classes` VALUES (6, '二年一班', 0);
INSERT INTO `classes` VALUES (7, '二年二班', 0);
INSERT INTO `classes` VALUES (8, '二年三班', 0);
INSERT INTO `classes` VALUES (9, '二年四班', 0);
INSERT INTO `classes` VALUES (10, '二年五班', 0);

students 学员表

CREATE TABLE `students`  (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `class_id` int NULL DEFAULT NULL,
  `age` tinyint NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `class_id`(`class_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '小明', 1, 10);
INSERT INTO `students` VALUES (2, '小敏', 2, 10);
INSERT INTO `students` VALUES (3, '小林', 2, 10);
INSERT INTO `students` VALUES (4, '小虎', 1, 9);

添加数据

语法 :

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

示例

insert into students (`name`,`class_id`,`age`) values ('小花',3,10);

删除数据

语法 :

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

示例

DELETE FROM students WHERE id = 1;

修改数据

语法 :

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

示例

UPDATE students set `name` = 'test' WHERE id = 2;

查询数据

语法 :

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

示例 :

# 查询所有字段
# SELECT * FROM 表名称 ....
SELECT * FROM students WHERE id > 1;

# 查询指定字段
#SELECT 字段名称,字段名称,... FROM 表名称
SELECT id, name FROM students