作业:
1.创建表:
创建员工表employee,字段如下:
id(员工编号),name(员工名字),gender(员工性别),salary(员工薪资)
create database company
use company
create table employee( id int auto_increment comment "员工编号", name varchar(6) comment "员工 名字", gender enum('男','女') default '男' comment '员工性别', salary int comment "员工薪资", primary key(id) );
2.插入数据
1,‘张三’,‘男’,2000
2,‘李四’,‘男’,1000
3,‘王五’,‘女’,4000
insert into employee(name,gender,salary)values('张三',"男","2000"),("李四","男",1000),("王五","女",4000);
mysql> select * from employee;
±—±-------±-------±-------+
| id | name | gender | salary |
±—±-------±-------±-------+
| 1 | 张三 | 男 | 2000 |
| 2 | 李四 | 男 | 1000 |
| 3 | 王五 | 女 | 4000 |
±—±-------±-------±-------+
3.修改表数据
3.1 将所有员工薪水修改为5000元
mysql> update employee set salary=5000;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from employee;
+----+--------+--------+--------+
| id | name | gender | salary |
+----+--------+--------+--------+
| 1 | 张三 | 男 | 5000 |
| 2 | 李四 | 男 | 5000 |
| 3 | 王五 | 女 | 5000 |
+----+--------+--------+--------+
3 rows in set (0.00 sec)
3.2将姓名为张三的员工薪水修改为3000元
mysql> update employee set salary=3000 where name="张三";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+----+--------+--------+--------+
| id | name | gender | salary |
+----+--------+--------+--------+
| 1 | 张三 | 男 | 3000 |
| 2 | 李四 | 男 | 5000 |
| 3 | 王五 | 女 | 5000 |
+----+--------+--------+--------+
3 rows in set (0.00 sec)
3.3将姓名为李四的员工薪水修改为4000元,gener改为女
mysql> update employee set salary=4000,gender="女" where name="李四";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+----+--------+--------+--------+
| id | name | gender | salary |
+----+--------+--------+--------+
| 1 | 张三 | 男 | 3000 |
| 2 | 李四 | 女 | 4000 |
| 3 | 王五 | 女 | 5000 |
+----+--------+--------+--------+
3 rows in set (0.00 sec)
3.4 将王五的薪水在原有基础上增加1000元
mysql> update employee set salary=salary+1000 where name="王五";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from employee;
+----+--------+--------+--------+
| id | name | gender | salary |
+----+--------+--------+--------+
| 1 | 张三 | 男 | 3000 |
| 2 | 李四 | 女 | 4000 |
| 3 | 王五 | 女 | 6000 |
+----+--------+--------+--------+
3 rows in set (0.00 sec)