1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

create database db01 default charset utf8 collate utf8_general_ci;
use db01;
create table userinfo (
   id int not null auto_increment primary key,
   name varchar(32) not null,
   password varchar(64) not null,
   gender enum('male','female') not null,
   email varchar(64) not null,
   amount decimal(10,2) not null default 0,
   ctime datetime
) default charset=utf8;



show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| userinfo       |
+----------------+
1 row in set

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

insert into userinfo(name,`password`,gender,email) 
   values("zhangsan","zhang123","male","[email protected]");

insert into userinfo(name, `password`, gender,email) 
   values("murong","murong123","female","[email protected]");

insert into userinfo(name,`password`,gender,email,amount)
   values ("waner","waner123","female", "[email protected]",1234.123);

insert into userinfo(name,`password`,gender,email,amount)
   values ("wangwu","wangwu123","male","[email protected]",3432.23);

insert into userinfo(name,`password`,gender,email,amount)
   values("wangyuyan","wang123","female","[email protected]",394023);

select * from userinfo;
+----+-----------+-----------+--------+-----------------------+-----------+--------+
| id | name      | password  | gender | email                 | amount    | ctime  |
+----+-----------+-----------+--------+-----------------------+-----------+--------+
| 1  | zhangsan  | zhang123  | male   | zhangsan@example.com  | 0.00      | <null> |
| 2  | murong    | murong123 | female | murong@example.com    | 0.00      | <null> |
| 3  | waner     | waner123  | female | waner@example.com     | 1234.12   | <null> |
| 4  | wangwu    | wangwu123 | male   | wangwu@example.com    | 3432.23   | <null> |
| 5  | wangyuyan | wang123   | female | wangyuyan@example.com | 394023.00 | <null> |
+----+-----------+-----------+--------+-----------------------+-----------+--------+
(END)

1
2
3

insert into userinfo(name,`password`,gender,email,amount) 
    values("zhugeliang","zhuge123",123,"[email protected]",123124);
(1265, "Data truncated for column 'gender' at row 1")

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

-- 原值
select name,gender from userinfo where id>3;
+-----------+--------+
| name      | gender |
+-----------+--------+
| wangwu    | male   |
| wangyuyan | female |
+-----------+--------+
2 rows in set
Time: 0.011s

-- 更改
update userinfo set gender='male' where id > 3;
Query OK, 1 row affected
Time: 0.005s

-- 新值
select name,gender from userinfo where id>3;
+-----------+--------+
| name      | gender |
+-----------+--------+
| wangwu    | male   |
| wangyuyan | male   |
+-----------+--------+
2 rows in set
Time: 0.010s

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

select name,amount from userinfo where amount> 1000;
+-----------+-----------+
| name      | amount    |
+-----------+-----------+
| waner     | 1234.12   |
| wangwu    | 3432.23   |
| wangyuyan | 394023.00 |
+-----------+-----------+
3 rows in set
Time: 0.019s

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

-- 原值
select name,amount from userinfo;
+-----------+-----------+
| name      | amount    |
+-----------+-----------+
| zhangsan  | 0.00      |
| murong    | 0.00      |
| waner     | 1234.12   |
| wangwu    | 3432.23   |
| wangyuyan | 394023.00 |
+-----------+-----------+
5 rows in set
Time: 0.012s

-- 更新
update userinfo set amount=amount+1000;
Query OK, 5 rows affected
Time: 0.004s

-- 新值
select name,amount from userinfo;
+-----------+-----------+
| name      | amount    |
+-----------+-----------+
| zhangsan  | 1000.00   |
| murong    | 1000.00   |
| waner     | 2234.12   |
| wangwu    | 4432.23   |
| wangyuyan | 395023.00 |
+-----------+-----------+
5 rows in set
Time: 0.010s

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

-- 删除前
select name,gender from userinfo where gender='male';
+-----------+-----------+
| name      | gender    |
+-----------+-----------+
| zhangsan  | male      |
| wangwu    | male      |
| wangyuyan | male      |
+-----------+-----------+
3 rows in set
Time: 0.010s

-- 删除
delete from userinfo where gender='male';
Query OK, 3 rows affected
Time: 0.004s

-- 删除后, 查询所有, 看不到男人了
select name,gender from userinfo;
+--------+--------+
| name   | gender |
+--------+--------+
| murong | female |
| waner  | female |
+--------+--------+
2 rows in set
Time: 0.012s