SQL自学，mysql从入门到精通 --- 第 9天，INSERT、UPDATE、DELETE 的使用

Mr.L-OAM

已于 2023-09-12 15:58:30 修改

阅读量94

点赞数

分类专栏： SQL 文章标签： sql mysql 数据库

于 2023-09-08 15:31:16 首次发布

本文链接：https://blog.csdn.net/weixin_46153337/article/details/132761363

版权

SQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

INSERT VALUES

root@mysqldb 14:51:  [d1]> INSERT INTO bikes 
    -> VALUES
    -> ("AA","AA","10");
Query OK, 1 row affected (0.00 sec)

root@mysqldb 14:54:  [d1]> INSERT INTO bikes
    -> (type,prIce)
    -> VALUES
    -> ("B","100");
Query OK, 1 row affected (0.01 sec)


root@mysqldb 14:54:  [d1]> SELECT * FROM bikes;
+------+-------------+-------+
| type | description | price |
+------+-------------+-------+
| SS   | SHUSHIXING  |   200 |
| ZG   | ZUNGUIXING  |   500 |
| JC   | JICHUXING   |   150 |
| SDX  | SHANDIXING  |  1000 |
| AA   | AA          |    10 |
| B    | NULL        |   100 |
+------+-------------+-------+
6 rows in set (0.00 sec)

INSERT SELECT 语句

root@mysqldb 14:58:  [d1]> SELECT * FROM worker1;
+------+
| name |
+------+
| 赵1  |
| 钱2  |
| 孙3  |
| 李4  |
| 周5  |
| 王6  |
+------+
6 rows in set (0.00 sec)

root@mysqldb 14:58:  [d1]> INSERT INTO worker1
    -> SELECT * FROM worker2;
Query OK, 6 rows affected (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 0



root@mysqldb 14:59:  [d1]> SELECT * FROM worker1;
+------+
| name |
+------+
| 赵1  |
| 钱2  |
| 孙3  |
| 李4  |
| 周5  |
| 王6  |
| 张1  |
| 武2  |
| 孙3  |
| 李4  |
| 黄5  |
| 吕6  |
+------+
12 rows in set (0.00 sec)

root@mysqldb 15:01:  [d1]> INSERT INTO worker1 (name)
    -> SELECT name FROM worker2 WHERE name = '吕6';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0


root@mysqldb 15:03:  [d1]> SELECT * FROM worker1;
+------+
| name |
+------+
| 赵1  |
| 钱2  |
| 孙3  |
| 李4  |
| 周5  |
| 王6  |
| 张1  |
| 武2  |
| 孙3  |
| 李4  |
| 黄5  |
| 吕6  |
| 吕6  |
+------+
13 rows in set (0.00 sec)

root@mysqldb 15:07:  [d1]> CREATE TABLE NN2 (  
    -> A1 INT,  
    -> B2 INT,  
    -> C3 INT  
    -> );
Query OK, 0 rows affected (0.01 sec)

root@mysqldb 15:07:  [d1]> SELECT * FROM nnum;
+------+------+------+------+
| n1   | n2   | n3   | n4   |
+------+------+------+------+
|    1 |    2 |    3 |    4 |
|   11 |   12 |   13 |   14 |
|   22 |   23 |   24 |   25 |
|    6 |    5 |   10 |    9 |
+------+------+------+------+
4 rows in set (0.00 sec)

root@mysqldb 15:07:  [d1]> INSERT INTO NN2
    -> (A1, B2, C3)
    -> SELECT n2, n3, n4 FROM nnum;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@mysqldb 15:09:  [d1]> SELECT * FROM NN2;
+------+------+------+
| A1   | B2   | C3   |
+------+------+------+
|    2 |    3 |    4 |
|   12 |   13 |   14 |
|   23 |   24 |   25 |
|    5 |   10 |    9 |
+------+------+------+
4 rows in set (0.00 sec)

UPDATE 语句

root@mysqldb 15:19:  [d1]> UPDATE NN2
    -> SET A1='900' 
    -> WHERE A1='2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@mysqldb 15:21:  [d1]> SELECT * FROM NN2;
+------+------+------+
| A1   | B2   | C3   |
+------+------+------+
|  900 |    3 |    4 |
|   12 |   13 |   14 |
|   23 |   24 |   25 |
|    5 |   10 |    9 |
+------+------+------+
4 rows in set (0.00 sec)

root@mysqldb 15:21:  [d1]> UPDATE NN2
    -> SET B2='800', C3='700'
    -> WHERE A1='900';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@mysqldb 15:23:  [d1]> SELECT * FROM NN2;
+------+------+------+
| A1   | B2   | C3   |
+------+------+------+
|  900 |  800 |  700 |
|   12 |   13 |   14 |
|   23 |   24 |   25 |
|    5 |   10 |    9 |
+------+------+------+
4 rows in set (0.00 sec)

-- 如果UPDATE语句中省略了WHERE子句，那么给定的表中所有的记录都会更新。
root@mysqldb 15:23:  [d1]> UPDATE NN2
    -> SET C3='500';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

root@mysqldb 15:25:  [d1]> SELECT * FROM NN2;
+------+------+------+
| A1   | B2   | C3   |
+------+------+------+
|  900 |  800 |  500 |
|   12 |   13 |  500 |
|   23 |   24 |  500 |
|    5 |   10 |  500 |
+------+------+------+
4 rows in set (0.01 sec)

root@mysqldb 15:25:  [d1]> UPDATE NN2 SET C3=C3*2;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

root@mysqldb 15:26:  [d1]> SELECT * FROM NN2;
+------+------+------+
| A1   | B2   | C3   |
+------+------+------+
|  900 |  800 | 1000 |
|   12 |   13 | 1000 |
|   23 |   24 | 1000 |
|    5 |   10 | 1000 |
+------+------+------+
4 rows in set (0.00 sec)

DELETE 语句

root@mysqldb 15:26:  [d1]> DELETE FROM NN2 WHERE A1=900;
Query OK, 1 row affected (0.00 sec)

root@mysqldb 15:27:  [d1]> SELECT * FROM NN2;
+------+------+------+
| A1   | B2   | C3   |
+------+------+------+
|   12 |   13 | 1000 |
|   23 |   24 | 1000 |
|    5 |   10 | 1000 |
+------+------+------+
3 rows in set (0.00 sec)

root@mysqldb 15:27:  [d1]> DELETE FROM NN2 WHERE A1>10;
Query OK, 2 rows affected (0.00 sec)

root@mysqldb 15:29:  [d1]> SELECT * FROM NN2;
+------+------+------+
| A1   | B2   | C3   |
+------+------+------+
|    5 |   10 | 1000 |
+------+------+------+
1 row in set (0.00 sec)