MySQL数据表内容的简单管理 表数据的增、删、改、查

插入数据：

INSERT INTO 表名称[(字段名称)] VALUE(前面字段对应的值)；

查询数据：

SELECT 字段名称1，字段名称2，… FROM 表名称

更改数据：

UPDATE 数据表 set 字段名称=新修改的值 [WHERE 条件]

删除数据：

DELETE FROM 表名称 [WHERE 条件] 

--- 表数据的增、删、改、查：
mysql> desc uu;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(16)   | YES  |     | NULL    |       |
| age   | tinyint(4)    | YES  |     | NULL    |       |
| sex   | enum('m','w') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 数据的添加格式：insert into 表名[(字段列表)] values(数据列表);

-- 标准添加，指定所有字段，给定所有值添加
mysql> insert into uu(id,name,age,sex) values(1,'zhangsan',20,'m');
Query OK, 1 row affected (0.05 sec)

-- 不指定字段名，添加值，注意值的顺序要和表结构顺序一致
mysql> insert into uu values(2,'lisi',22,'w');
Query OK, 1 row affected (0.02 sec)

-- 指定部分字段名添加值，
mysql> insert into uu(id,name,sex) values(3,'wangwu','w');
Query OK, 1 row affected (0.17 sec)

-- 批量添加值。
mysql> insert into uu values
    -> (4,'zhaoliu',25,'w'),
    -> (5,'xiaozhang',24,'m'),
    -> (6,'xiaoli',23,'w');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

-- 查询所有数据
mysql> select * from uu;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    1 | zhangsan  |   20 | m    |
|    2 | lisi      |   22 | w    |
|    3 | wangwu    | NULL | w    |
|    4 | zhaoliu   |   25 | w    |
|    5 | xiaozhang |   24 | m    |
|    6 | xiaoli    |   23 | w    |
+------+-----------+------+------+
6 rows in set (0.00 sec)

-- 数据的修改 格式：update 表名 set 字段名1=值1,字段名2=值2,... where 条件

-- 修改id号为2的，将lisi的age值改为25
mysql> update uu set age=25 where id=2;
--或
mysql> update uu set age=25 where name='lisi';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 将uu表id值3的 name改为laowang,age改为36
mysql> update uu  set name='laowang',age=36 where id=3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from uu;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    1 | zhangsan  |   20 | m    |
|    2 | lisi      |   25 | w    |
|    3 | laowang   |   36 | w    |
|    4 | zhaoliu   |   25 | w    |
|    5 | xiaozhang |   24 | m    |
|    6 | xiaoli    |   23 | w    |
+------+-----------+------+------+
6 rows in set (0.00 sec)



-- 数据的删除  delete from 表名 [where 条件]
-- 尝试删除id大于10的信息
mysql> delete from uu where id>10;
Query OK, 0 rows affected (0.14 sec)
-- 删除id值为3的信息。
mysql> delete from uu where id=3;
Query OK, 1 row affected (0.05 sec)

mysql> select * from uu;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    1 | zhangsan  |   20 | m    |
|    2 | lisi      |   25 | w    |
|    4 | zhaoliu   |   25 | w    |
|    5 | xiaozhang |   24 | m    |
|    6 | xiaoli    |   23 | w    |
+------+-----------+------+------+
5 rows in set (0.00 sec)

mysql>
--删除年龄大于等于30并且小于等于40的信息。
mysql> delete from uu where age>=30 and age<=40;
Query OK, 0 rows affected (0.00 sec)

欢迎转载，转载需注明地址： http://www.luowebs.com/Skill_desc/32/393.html

很赞哦！ (0)