MySQL中数据的DML操作(增、删、改)
1. 新增(INSERT)
INSERT INTO 表名 (列1,列2,列3...) VALUES(值1,值2,值3.....)
#新增一条数据
INSERT INTO t_countries(COUNTRY_ID,COUNTRY_NAME) VALUES ('CH','中国');
#增加两条数据
#多行添加,在值列表外边追加,再写一个值列表
INSERT INTO t_departments(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)
VALUES ('2500','Teach','111','6000'),('25000','Teach','111','6000');
- 表名后的列名列表以及VALUES里的值列表要一一对应(个数、顺序、类型)
2. 修改(UPDATE)
UPDATE 表名 SET 列名1=新值1,列名2 = 新值2...... WHERE 条件
#不加条件,默认修改整张表
UPDATE t1 SET a = 3,b = 'kk';
SELECT * FROM t1;
#加条件
UPDATE t1 SET a = 34,b = 'kk' WHERE id = 3;
SELECT * FROM t1;
#修改员工表
SELECT * FROM t_employees;
UPDATE t_employees
SET FIRST_NAME = 'Lex' ,LAST_NAME = 'De Haan'
WHERE EMPLOYEE_ID = 103;
- SET后跟着多个列 = 值;大多数情况下,要加WHERE条件,指定修改的目标,否则为整表更新
3. 删除(DELETE)
DELETE FROM 表名 WHERE 条件
#不加条件全部删除
SELECT * FROM t1;
DELETE FROM t1;
#加条件,删除员工号位103的员工
SELECT * FROM t_employees;
DELETE FROM t_employees
WHERE EMPLOYEE_ID = '103' ;
#删除多个
DELETE FROM t_employees
WHERE EMPLOYEE_ID = '103' OR EMPLOYEE_ID = '104';
- 删除时,如若不加WHERE条件,删除的是整张表的数据;结构不变;
4. 清空(TRUNCATE)
TRUNCATE TABLE 表名;
#清空t1整张表
TRUNCATE TABLE t1;
- TRUNCATE与DELETE不加WHERE删除整张表数据不同:
DELETE仅仅删除数据,结构不变;
TRUNCATE是把整张表销毁,再按照原表的格式和结构创建一张新表;