数据库day3-练习

重庆城科李立川

已于 2023-07-15 09:48:48 修改

阅读量74

点赞数

文章标签：数据库

于 2023-07-10 18:27:24 首次发布

本文链接：https://blog.csdn.net/lilichuan132/article/details/131644955

版权

在MySQL中创建了一个名为employee的员工表，包含id、name、gender和salary字段。接着插入了张三、李四和王五的记录，并进行了多次数据修改，包括批量调整薪资、修改特定员工的薪资和性别，以及对王五的薪资进行增加操作。

摘要由CSDN通过智能技术生成

1.创建表：

创建员工表employee，字段如下：
id（员工编号），name（员工名字），gender（员工性别），salary（员工薪资）

2.插入数据

1，‘张三’，‘男’，2000
2，‘李四’，‘男’，1000
3，‘王五’，‘女’，4000

3.修改表数据

3.1 将所有员工薪水修改为5000元
3.2将姓名为张三的员工薪水修改为3000元
3.3将姓名为李四的员工薪水修改为4000元，gender改为女
3.4 将王五的薪水在原有基础上增加1000元

// 进入数据库
[root@localhostjx ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
//创建数据库emp
mysql> create database emp;
Query OK, 1 row affected (0.00 sec)

// 按要求创建数据表employee
mysql> use emp;
Database changed
mysql> create table employee(
    -> id int auto_increment primary key not null,
    -> name varchar(20) not null,
    -> gender varchar(20),
    -> salary int);
Query OK, 0 rows affected (0.00 sec)

mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | NO   |     | NULL    |                |
| gender | varchar(20) | YES  |     | NULL    |                |
| salary | int(11)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

// 按要求插入数据
mysql> insert into employee values
    -> (1,"张三","男",2000);
Query OK, 1 row affected (0.00 sec)

mysql> insert into employee values
    -> (2,"李四","男",1000),
    -> (3,"王五","女",4000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

// 查看数据效果
mysql> select * from employee;
+----+--------+--------+--------+
| id | name   | gender | salary |
+----+--------+--------+--------+
|  1 | 张三   | 男     |   2000 |
|  2 | 李四   | 男     |   1000 |
|  3 | 王五   | 女     |   4000 |
+----+--------+--------+--------+
3 rows in set (0.00 sec)

// 将所有员工薪水修改为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)

// 将姓名为张三的员工薪水修改为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)

// 将姓名为李四的员工薪水修改为4000元，gender改为女
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> select * from employee;
+----+--------+--------+--------+
| id | name   | gender | salary |
+----+--------+--------+--------+
|  1 | 张三   | 男     |   3000 |
|  2 | 李四   | 女     |   4000 |
|  3 | 王五   | 女     |   5000 |
+----+--------+--------+--------+
3 rows in set (0.00 sec)

// 将王五的薪水在原有基础上增加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)