MySQL学习 - DML 增删改

DML 增删改

增加

• 字段顺序一一对应添加

  按照字段声明的先后顺序插入

  INSERT INTO table_name VALUES(v1,v2,v3...);

  INSERT INTO empl VALUES(1,'xxcheng','2023-08-01',5000.0);

• 指定字段名添加

  INSERT INTO table_name(field_1,field_2,field3...)
  VALUES(v1,v2,v3...);

   SQL-> INSERT INTO empl(id,name,hire_date,salary)
    -> VALUES(2,'jpc','2023-01-01',9999);

• 添加多条记录

  INSERT INTO table_name(field_1,field_2,field_3...)
  VALUES(v1,v2,v3...),
  (v21,v22,v23...);

  SQL -> INSERT INTO empl(id,name,hire_date,salary)
    -> VALUES(3,'abc','2000-01-01',19999),
    -> (4,'www','2020-01-01',999);

• 从查询结果添加记录

  查询结果的字段要与添加字段一一对应，数据类型最好不要缩小，避免出现错误。

  INSERT INTO table_name(field_1,field_2,field_3...)
  SELECT field_1_1,field_1_2,field_1_3...
  FROM table_name_2;

   SQL-> INSERT INTO empl(id,name,hire_date,salary)
    -> SELECT employee_id,last_name,hire_date,salary
    -> FROM employees;

更新

UPDATE table_name
SET field_1=v1,
    field_2=v2
WHERE 条件;

SQL -> UUPDATE empl 
    -> SET name='xxcheng123',salary=6000
    -> WHERE id=1;

删除

DELETE FROM table_name
WHERE 条件;

SQL->DELETE FROM empl WHERE id=4;

计算列

8.0 新特性，某一字段依赖于其他字段的值，在被依赖字段的值发生改变后，自动计算更新当前字段的值。

SQL -> CREATE TABLE test_a(
    -> a int,
    -> b int,
    -> c int GENERATED ALWAYS AS (a+b) VIRTUAL
    -> );
Query OK, 0 rows affected (0.1468 sec)
SQL-> INSERT INTO test_1(a,b)
   -> VALUES(10,20);
Query OK, 1 row affected (0.0028 sec)
SQL-> SELECT * FROM test_a;
+----+----+----+
| a  | b  | c  |
+----+----+----+
| 10 | 20 | 30 |
+----+----+----+
1 row in set (0.0007 sec)
SQL-> UPDATE test_a SET a=66 WHERE b=20;
Query OK, 1 row affected (0.0035 sec)

Rows matched: 1  Changed: 1  Warnings: 0
SQL-> SELECT * FROM test_a;
+----+----+----+
| a  | b  | c  |
+----+----+----+
| 66 | 20 | 86 |
+----+----+----+
1 row in set (0.0007 sec)