MySQL增删改查

1.MySQL安装教程

创建数据库和创建表请看http://blog.csdn.net/whzhaochao/article/details/12858711

2.登录MySQL

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

3.插入数据

3.1插入方法一

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

3.2插入方法二

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

3.3插入方法三

将自身插入自身

 insert into user select * from user;

4.删除字段

4.1删除全部字段

 delete from user;

mysql> delete from user;
Query OK, 8 rows affected (0.02 sec)

4.2删除指定字段

 delete from user where id=4;

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;

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;

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 ;

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;

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)