1、创建员工表employee,id(员工编号),name(员工名字),gender(员工性别),salary(员工薪资)
mysql> create table employee(
-> id int comment'员工编号',
-> name varchar(20) comment'员工姓名',
-> gender char(5) comment'员工性别',
-> salary int comment'员工薪资'
-> );
Query OK, 0 rows affected (0.00 sec)
2、插入数据
1,‘张三’,‘男’,2000
2,‘李四’,‘男’,1000
3,‘王五’,‘女’,4000
2.1 修改字符格式
mysql> alter table employee change name name varchar(255) characcter set utf8;
mysql> alter table employee change gender gender varchar(255) ch
2.2 插入数据
mysql> insert into employee
-> values (1,'张三','男',2000), (2,'..四','男',1000), (3,' 王五','女',4000);
3、修改表数据
3.1 将所有员工薪水修改为5000元
mysql> update employee set salary = 5000 where id = 1 or id =2 or id = 3;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
3.2将姓名为张三的员工薪水修改为3000元
mysql> update employee set salary = 3000 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3.3将姓名为李四的员工薪水修改为4000元,gener改为女
mysql> update employee set salary = 4000 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update employee set gender = '女' where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings:
3.4 将王五的薪水在原有基础上增加1000元
mysql> update employee set salary = 6000 where name = '王五';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
最后表结构和表内容
mysql> desc employee;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| gender | varchar(255) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from employee;
+------+--------+--------+--------+
| id | name | gender | salary |
+------+--------+--------+--------+
| 1 | 张三 | 男 | 3000 |
| 2 | 李四 | 女 | 4000 |
| 3 | 王五 | 女 | 6000 |
+------+--------+--------+--------+
3 rows in set (0.00 sec)