MySQL学习笔记——数据的操作(DML)

数据的操作（DML）

插入数据

• 不指定具体的字段名

INSERT [INTO] tbl_name VALUES|VALUE(值...)

• 列出指定字段

INSERT [INTO] tbl_name(字段名称1,...) VALUES|VALUE(值1,...)

• 同时插入多条记录

INSERT [INTO] tbl_name[(字段名称...)] VALUES(值...),(值...)...

• 通过SET形式插入记录

INSERT [INTO] tbl_name SET 字段名称=值,...

• 将查询结果插入到表中

INSERT [INTO] tbl_name[(字段名称,...)] SELECT 字段名称 FROM tbl_name [WHERE 条件]

更新数据

UPDATE tbl_name SET 字段名称=值,... [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

删除数据

DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

• 彻底清空数据表

TRUNCATE [TABLE] tbl_name

代码示例：

CREATE DATABASE excise;
USE excise;
CREATE TABLE IF NOT EXISTS user1
(
    id       TINYINT PRIMARY KEY AUTO_INCREMENT,
    username CHAR(20) NOT NULL,
    depId    INT
);


# 插入数据

-- 不指定具体的字段名
# INSERT [INTO] tbl_name VALUES|VALUE(值...)
INSERT INTO user1
VALUES (1, 'ZHANGSAN', 1);

-- 列出指定字段
# INSERT [INTO] tbl_name(字段名称1,...) VALUES|VALUE(值1,...)
INSERT INTO user1(USERNAME, DEPID)
VALUES ('LISI', 2);

-- 同时插入多条记录
# INSERT [INTO] tbl_name[(字段名称...)] VALUES(值...),(值...)...
INSERT INTO user1
VALUES (2, 'WANGWU', 3),
       (3, 'ZHANGLIU', 5);

-- 通过SET形式插入记录
# INSERT [INTO] tbl_name SET 字段名称=值,...
INSERT INTO user1
SET id=5,
    username='zhoubapi',
    depId=3;

-- 将查询结果插入到表中
# INSERT [INTO] tbl_name[(字段名称,...)] SELECT 字段名称 FROM tbl_name [WHERE 条件]
CREATE TABLE IF NOT EXISTS department
(
    id      TINYINT UNSIGNED AUTO_INCREMENT KEY,
    depName VARCHAR(20) NOT NULL UNIQUE
) ENGINE = INNODB;

INSERT department(depName)
VALUES ('教学部'),
       ('市场部'),
       ('运营部'),
       ('督导部');
DESC department;
SELECT *
FROM department;

CREATE TABLE IF NOT EXISTS employee
(
    id       TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username CHAR(20) NOT NULL,
    depId    TINYINT UNSIGNED,
    FOREIGN KEY (depId) REFERENCES department (id)
) ENGINE = INNODB;

INSERT employee(username, depId)
VALUES ('king', 1),
       ('queen', 2),
       ('张三', 3),
       ('李四', 4),
       ('王五', 1);
SELECT * FROM employee;

CREATE TABLE IF NOT EXISTS employee2(
    id       TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username CHAR(20) NOT NULL,
    depId    TINYINT UNSIGNED
);
SELECT * FROM employee2;
INSERT employee2 SELECT * FROM employee;
SELECT * FROM employee2;

# 更新数据
# UPDATE tbl_name SET 字段名称=值,... [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

UPDATE employee SET username='HACKER' WHERE id=1;

SELECT * FROM employee;


## 删除数据
# DELETE FROM tbl_name [WHERE 条件][ORDER BY 字段名称][LIMIT 限制条数]

DELETE FROM employee WHERE depId=1;
DELETE FROM department WHERE id=1;

-- 彻底清空数据表
# TRUNCATE [TABLE] tbl_name
TRUNCATE employee2;