黑马程序员---MySQL数据库02---表的增删改查

╰+攻爆jí腚メ 2022-11-27 10:10 158阅读 0赞

一：向表中增加数据：  
整体一行插入：insert into 表名 values(值，值…)；  
按列名插入：insert into 表名 (列名，列名) values(值，值)；  
插入多行： insert into 表名 values(值，值…)，(值，值…)；

mysql> insert into students values(0,"小明",23,185,'男',1);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from students;
    +----+--------+------+--------+--------+--------+
    | id | name   | age  | high   | gender | cls_id |
    +----+--------+------+--------+--------+--------+
    |  1 | 小明   |   23 | 185.00 | 男     |      1 |
    +----+--------+------+--------+--------+--------+
    1 row in set (0.00 sec)

mysql> insert into students(name,age,high) values('小芳',24,165);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from students;
    +----+--------+------+--------+--------+--------+
    | id | name   | age  | high   | gender | cls_id |
    +----+--------+------+--------+--------+--------+
    |  1 | 小明   |   23 | 185.00 | 男     |      1 |
    |  2 | 小芳   |   24 | 165.00 | NULL   |   NULL |
    +----+--------+------+--------+--------+--------+
    2 rows in set (0.00 sec)

mysql> insert into students(name, age) values("张三", 21),("李四", 22);
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from students;
    +----+--------+------+--------+--------+--------+
    | id | name   | age  | high   | gender | cls_id |
    +----+--------+------+--------+--------+--------+
    |  1 | 小明   |   23 | 185.00 | 男     |      1 |
    |  2 | 小芳   |   24 | 165.00 | NULL   |   NULL |
    |  3 | 张三   |   21 |   NULL | NULL   |   NULL |
    |  4 | 李四   |   22 |   NULL | NULL   |   NULL |
    +----+--------+------+--------+--------+--------+
    4 rows in set (0.00 sec)

二：修改表中的数据：  
1：按照条件修改。：update 表名 set 列1=值1，列2= 值2 where 条件；  
2：对整个表全部修改。update 表名 set 列1=值1；

mysql> update students set age = 10 where id = 1;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from students;
    +----+--------+------+--------+--------+--------+
    | id | name   | age  | high   | gender | cls_id |
    +----+--------+------+--------+--------+--------+
    |  1 | 小明   |   10 | 185.00 | 男     |      1 |
    |  2 | 小芳   |   24 | 165.00 | NULL   |   NULL |
    |  3 | 张三   |   21 |   NULL | NULL   |   NULL |
    |  4 | 李四   |   22 |   NULL | NULL   |   NULL |
    +----+--------+------+--------+--------+--------+
    4 rows in set (0.00 sec)

mysql> update students set high = 189;
    Query OK, 4 rows affected (0.00 sec)
    Rows matched: 4  Changed: 4  Warnings: 0
    
    mysql> select * from students;
    +----+--------+------+--------+--------+--------+
    | id | name   | age  | high   | gender | cls_id |
    +----+--------+------+--------+--------+--------+
    |  1 | 小明   |   10 | 189.00 | 男     |      1 |
    |  2 | 小芳   |   24 | 189.00 | NULL   |   NULL |
    |  3 | 张三   |   21 | 189.00 | NULL   |   NULL |
    |  4 | 李四   |   22 | 189.00 | NULL   |   NULL |
    +----+--------+------+--------+--------+--------+
    4 rows in set (0.00 sec)

三：查找表中的数据：  
1:查找所有：

mysql> select * from students;
    +----+--------+------+--------+--------+--------+
    | id | name   | age  | high   | gender | cls_id |
    +----+--------+------+--------+--------+--------+
    |  1 | 小明   |   10 | 189.00 | 男     |      1 |
    |  2 | 小芳   |   24 | 189.00 | NULL   |   NULL |
    |  3 | 张三   |   21 | 189.00 | NULL   |   NULL |
    |  4 | 李四   |   22 | 189.00 | NULL   |   NULL |
    +----+--------+------+--------+--------+--------+
    4 rows in set (0.00 sec)

2：根据where 条件查找：

mysql> select * from students where high=189;
    +----+--------+------+--------+--------+--------+
    | id | name   | age  | high   | gender | cls_id |
    +----+--------+------+--------+--------+--------+
    |  1 | 小明   |   10 | 189.00 | 男     |      1 |
    |  2 | 小芳   |   24 | 189.00 | NULL   |   NULL |
    |  3 | 张三   |   21 | 189.00 | NULL   |   NULL |
    |  4 | 李四   |   22 | 189.00 | NULL   |   NULL |
    +----+--------+------+--------+--------+--------+
    4 rows in set (0.00 sec)

3：查找指定的列：并且列有顺序：  
注意：顺序也是gender name,high

mysql> select gender,name,high from students;
    +--------+--------+--------+
    | gender | name   | high   |
    +--------+--------+--------+
    | 男     | 小明   | 189.00 |
    | NULL   | 小芳   | 189.00 |
    | NULL   | 张三   | 189.00 |
    | NULL   | 李四   | 189.00 |
    +--------+--------+--------+
    4 rows in set (0.00 sec)

四：删除表中数据：  
1:根据where条件删除：

mysql> delete  from students where id = 4;
    Query OK, 1 row affected (0.00 sec)
    mysql> select * from students;
    +----+--------+------+--------+--------+--------+
    | id | name   | age  | high   | gender | cls_id |
    +----+--------+------+--------+--------+--------+
    |  1 | 小明   |   10 | 189.00 | 男     |      1 |
    |  2 | 小芳   |   24 | 189.00 | NULL   |   NULL |
    |  3 | 张三   |   21 | 189.00 | NULL   |   NULL |
    +----+--------+------+--------+--------+--------+
    3 rows in set (0.00 sec)