MySQL增删改查

1.MySQL安装教程

创建数据库和创建表请看

2.登录MySQL

选择zhaochao数据库，并查看user表中字段如下：

3.插入数据

3.1插入方法一

登录后复制

insert into user (id,username,userpasswd) values (3,'赵超','zhaochao');

• 1.

3.2插入方法二

登录后复制

insert into user values (4,'赵云','zhaoyun');

• 1.

3.3插入方法三

将自身插入自身

登录后复制

insert into user select * from user;

• 1.

4.删除字段

4.1删除全部字段

登录后复制

delete from user;

• 1.

mysql> delete from user;

Query OK, 8 rows affected (0.02 sec)

4.2删除指定字段

登录后复制

delete from user where id=4;

• 1.

mysql> select * from user;

+------+----------+------------+

| id   | username | userpasswd |

+------+----------+------------+

|    1 | admin    | admin      |

|    2 | zhaochao | zhaochao   |

|    3 | 赵超     | zhaochao   |

|    4 | 赵云     | zhaoyun    |

+------+----------+------------+

4 rows in set (0.00 sec)

mysql> delete from user where id=4;

Query OK, 1 row affected (0.03 sec)

mysql> select * from user;

+------+----------+------------+

| id   | username | userpasswd |

+------+----------+------------+

|    1 | admin    | admin      |

|    2 | zhaochao | zhaochao   |

|    3 | 赵超     | zhaochao   |

+------+----------+------------+

3 rows in set (0.00 sec)

5.修改字段内容

登录后复制

update user set username='root' where id=1;

• 1.

mysql> select * from user;

+------+----------+------------+

| id   | username | userpasswd |

+------+----------+------------+

|    1 | admin    | admin      |

|    2 | zhaochao | zhaochao   |

|    3 | 赵超     | zhaochao   |

+------+----------+------------+

3 rows in set (0.00 sec)

mysql> update user set username='root' where id=1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;

+------+----------+------------+

| id   | username | userpasswd |

+------+----------+------------+

|    1 | root     | admin      |

|    2 | zhaochao | zhaochao   |

|    3 | 赵超     | zhaochao   |

+------+----------+------------+

3 rows in set (0.00 sec)

6.查询

6.1查询一

登录后复制

select id,username,userpasswd from user;

• 1.

mysql> select id,username,userpasswd from user;

+------+----------+------------+

| id   | username | userpasswd |

+------+----------+------------+

|    1 | root     | admin      |

|    2 | zhaochao | zhaochao   |

|    3 | 赵超     | zhaochao   |

+------+----------+------------+

3 rows in set (0.00 sec)

6.2查询二

登录后复制

select id '用户编号',username '用户名',userpasswd '密码' from user a where a.id=1 ;

• 1.

mysql> select id '用户编号',username '用户名',userpasswd '密码' from user a where a.id=1 ;
+----------+--------+-------+
| 用户编号 | 用户名 | 密码  |
+----------+--------+-------+
|        1 | root   | admin |
+----------+--------+-------+
1 row in set (0.00 sec)

6.1查询三

登录后复制

select id,username,userpasswd from user limit 1,2;

• 1.

mysql> select id,username,userpasswd from user limit 1,2;

+------+----------+------------+

| id   | username | userpasswd |

+------+----------+------------+

|    2 | zhaochao | zhaochao   |

|    3 | 赵超     | zhaochao   |

+------+----------+------------+

2 rows in set (0.00 sec)

原创作者: u_4937244 转载于: https://blog.51cto.com/u_4937244/11575838