[MYAQL / Mariadb] 数据库学习-管理表记录的增删改查

管理表记录

增加表记录

mysql> insert into user values (30,'bob','x',2023,2023,'test user','/home/bob','/bin/bash');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values (32,'bob','x',2023,2023,'test user','/home/bob','/bin/bash'),(31,'tom','x',2023,2023,'test user','/home/tom','/bin/bash');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user where id >=30;
+----+------+----------+------+------+-----------+-----------+-----------+
| id | name | password | uid  | gid  | comment   | homedir   | shell     |
+----+------+----------+------+------+-----------+-----------+-----------+
| 30 | bob  | x        | 2023 | 2023 | test user | /home/bob | /bin/bash |
| 31 | tom  | x        | 2023 | 2023 | test user | /home/tom | /bin/bash |
| 32 | bob  | x        | 2023 | 2023 | test user | /home/bob | /bin/bash |
+----+------+----------+------+------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

增加指定字段的值

mysql> insert into user(name,uid,gid) values('lisi',2022,2022),('susan',2022,2022);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user where id >=30;                                                       +----+-------+----------+------+------+-----------+-----------+-----------+
| id | name  | password | uid  | gid  | comment   | homedir   | shell     |
+----+-------+----------+------+------+-----------+-----------+-----------+
| 30 | bob   | x        | 2023 | 2023 | test user | /home/bob | /bin/bash |
| 31 | tom   | x        | 2023 | 2023 | test user | /home/tom | /bin/bash |
| 32 | bob   | x        | 2023 | 2023 | test user | /home/bob | /bin/bash |
| 33 | lisi  | NULL     | 2022 | 2022 | NULL      | NULL      | NULL      |
| 34 | susan | NULL     | 2022 | 2022 | NULL      | NULL      | NULL      |
+----+-------+----------+------+------+-----------+-----------+-----------+
5 rows in set (0.00 sec)

查询语句：select 列名列表（1控制列） from 表 【where 条件】（2控制行）；

mysql> select name from user where id >=30;
+-------+
| name  |
+-------+
| bob   |
| tom   |
| bob   |
| lisi  |
| susan |
+-------+
5 rows in set (0.02 sec)

mysql> select name,uid from user where id >=30;
+-------+------+
| name  | uid  |
+-------+------+
| bob   | 2023 |
| tom   | 2023 |
| bob   | 2023 |
| lisi  | 2022 |
| susan | 2022 |
+-------+------+
5 rows in set (0.00 sec)

mysql>

更新：update 表 set 列名=‘值’，列名=‘值’ 【where 条件】；

mysql> update user set uid=2023,gid=2020  where id >=30;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select name,uid,gid from user where id >=30;
+-------+------+------+
| name  | uid  | gid  |
+-------+------+------+
| bob   | 2023 | 2020 |
| tom   | 2023 | 2020 |
| bob   | 2023 | 2020 |
| lisi  | 2023 | 2020 |
| susan | 2023 | 2020 |
+-------+------+------+
5 rows in set (0.01 sec)

删除：delete from 表（删表里的所有数据） where 条件；

mysql> insert into user(name,uid)  values ('haha',2021),('hehe',2019);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select name,uid,gid from user where id >=30;
+-------+------+------+
| name  | uid  | gid  |
+-------+------+------+
| bob   | 2023 | 2020 |
| tom   | 2023 | 2020 |
| bob   | 2023 | 2020 |
| lisi  | 2023 | 2020 |
| susan | 2023 | 2020 |
| haha  | 2021 | NULL |
| hehe  | 2019 | NULL |
+-------+------+------+
7 rows in set (0.00 sec)

mysql> delete from user where gid is null;
Query OK, 2 rows affected (0.01 sec)

mysql> select name,uid,gid from user where id >=30;
+-------+------+------+
| name  | uid  | gid  |
+-------+------+------+
| bob   | 2023 | 2020 |
| tom   | 2023 | 2020 |
| bob   | 2023 | 2020 |
| lisi  | 2023 | 2020 |
| susan | 2023 | 2020 |
+-------+------+------+
5 rows in set (0.00 sec)