表的增删改查

1、创建表

mysql> create table employee (
    -> id int(1) comment '员工编号',
    -> name varchar(6) comment '员工名字',
    -> gender varchar(2) comment '员工性别',
    -> salary int (4) comment '员工薪资');
Query OK, 0 rows affected (0.01 sec)

2、插入数据

insert employee values(1,'张三','男',2000);

insert into employee values (2,'李四','男',1000);

insert into employee values (3,'王五','女',4000)

*问题：插入记录报错

解决：将字符集换成 utf8mb4 即可，对mysql来说utf8mb4才是真正的utf8, 支持4个字节的字符，也兼容mysql的自己的utf8 。

执行如下语句：该语句直接将表的字符集设置为utf8mb4，并将表中字符类型的字段也转换为utf8mb4。

ALTER TABLE user_info CONVERT TO CHARACTER SET utf8mb4;

3、修改表数据

①  mysql> update employee set salary=5000;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

②  mysql> update employee set salary=3000 where name='张三';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

③  mysql> update employee set salary=4000,gender='女' where name='李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

④mysql> update employee set salary=salary+1000 where name='王五';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0