MYSQL基础上机练习题（一）数据库、表、字段的创建、修改、删除

灰太狼 2022-07-13 03:11 335阅读 0赞

写这些MYSQL基础上机练习题的文章的目的主要是因为，在某一次碰见一家公司的数据分析师，希望能转行到做SQL上面，然而当我看到她对SQL的认知只是仅仅在背语句、甚至是不太清楚语句真正的意义的时候，发现很多人其实都缺少实际操作的能力。  
而且很多教材中，很少有关于某一个数据库的从基础到综合应用的练习，所以在这里，从SQL SERVER的一本书中模仿了一些数据的格式，同时添加一些自己觉得比较重要的知识点，从基础做起，把日常能用到的数据库语句用一遍，让有需要的小白可以多练练手，以免纸上谈兵。

### 一、实验目的： ###

1.  创建、修改、删除数据库
2.  创建、修改、删除表
3.  创建、修改、删除字段

### 二、内容： ###

创建一个用于企业管理的员工管理数据库，包括员工信息（Employees）、部门信息（Departments）、员工薪水情况（Salary）三个表，并对该数据库进行增删改查

**Employees表**

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th align="center">数据类型</th> 
   <th align="right">长度</th> 
   <th>是否可空</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>EmployeeID</td> 
   <td align="center">字符串型</td> 
   <td align="right">6</td> 
   <td>否</td> 
   <td>员工编号，主键</td> 
  </tr> 
  <tr> 
   <td>Name</td> 
   <td align="center">字符串型</td> 
   <td align="right">10</td> 
   <td>否</td> 
   <td>姓名</td> 
  </tr> 
  <tr> 
   <td>Education</td> 
   <td align="center">字符串型</td> 
   <td align="right">4</td> 
   <td>否</td> 
   <td>学历</td> 
  </tr> 
  <tr> 
   <td>Birthday date</td> 
   <td align="center">日期型</td> 
   <td align="right">系统默认</td> 
   <td>否</td> 
   <td>出生日期</td> 
  </tr> 
  <tr> 
   <td>Gender</td> 
   <td align="center">位型</td> 
   <td align="right">系统默认</td> 
   <td>否</td> 
   <td>性别，默认值为1</td> 
  </tr> 
  <tr> 
   <td>Workyear</td> 
   <td align="center">整数型</td> 
   <td align="right">系统默认</td> 
   <td>是</td> 
   <td>工作时间</td> 
  </tr> 
  <tr> 
   <td>Address</td> 
   <td align="center">字符串型</td> 
   <td align="right">40</td> 
   <td>是</td> 
   <td>地址</td> 
  </tr> 
  <tr> 
   <td>Phonenumber</td> 
   <td align="center">字符串型</td> 
   <td align="right">12</td> 
   <td>是</td> 
   <td>电话号码</td> 
  </tr> 
  <tr> 
   <td>DepartmentID</td> 
   <td align="center">字符型</td> 
   <td align="right">3</td> 
   <td>否</td> 
   <td>员工部门号，外键</td> 
  </tr> 
 </tbody> 
</table>

**Departments表**

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th align="center">数据类型</th> 
   <th align="right">长度</th> 
   <th>是否可空</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DepartmentID</td> 
   <td align="center">字符串型</td> 
   <td align="right">3</td> 
   <td>否</td> 
   <td>部门编号，主键</td> 
  </tr> 
  <tr> 
   <td>DepartmentName</td> 
   <td align="center">字符串型</td> 
   <td align="right">20</td> 
   <td>否</td> 
   <td>部门名</td> 
  </tr> 
  <tr> 
   <td>Note</td> 
   <td align="center">字符串型</td> 
   <td align="right">100</td> 
   <td>是</td> 
   <td>备注</td> 
  </tr> 
 </tbody> 
</table>

**Salary表**

### 三、题目 ###

**1. 创建、修改、删除数据库**  
（1）创建YGGL数据库  
（2）删除该数据库

**2. 创建、修改、删除表**  
（1）根据内容中表格的要求，创建员工信息（Employees）  
（2）删除Employees表中Address字段  
（3）修改Employees表中Gender字段的默认值以及备注，改为默认值为1  
（4）修改Employees表中Gender字段名称，改为Sex  
（5）显示Employees中字段的所有属性  
（6）创建部门信息（Departments）表  
（7）创建员工薪水情况（Salary）表  
（8）编写触发器，使创建或修改ActIncome时，都遵循：  
ActIncome = InCome - OutCome

**3. 创建、修改、删除字段**

### 四、代码及演示，以及易错点 ###

**1. 创建、修改、删除数据库**  
（1）创建YGGL数据库  
‘`CREATE DATABASE YGGL`  
![Mysql新建数据库][Mysql]

（2）删除该数据库  
‘`DROP DATABASE YGGL`  
![Mysql删除数据库][Mysql 1]

**2. 创建、修改、删除表**  
（1）根据内容中表格的要求，创建员工信息（Employees）

CREATE TABLE Employees1(
    EmployeeID varchar(6) NOT NULL PRIMARY KEY COMMENT '员工编号，主键',
    Name varchar(10) NOT NULL COMMENT '姓名',
    Education varchar(4) NOT NULL COMMENT '学历',
    Birthday date NOT NULL COMMENT '出生日期',
    Gender bit NOT NULL DEFAULT 0 COMMENT '性别，默认值为0' ,
    WorkYear tinyint COMMENT '工作时间',
    Address varchar(40) COMMENT '地址',
    PhoneNumber varchar(12) COMMENT '电话号码',
    DepartmentID varchar(3) NOT NULL COMMENT '员工部门号，外键'
    )

![Mysql 创建表][Mysql 2]

（2）删除Employees表中Address字段  
`ALTER TABLE Employees DROP Address`

![Mysql 删除字段][Mysql 3]  
在运行了代码之后，有可能Address依然存在列表中，刷新也刷新不出来删除后的界面，可以尝试关掉数据库重新进入

（3）修改Employees表增加字段Address  
`ALTER TABLE Employees ADD Address varchar(40) COMMENT '地址'`  
![Mysql 增加字段][Mysql 4]

（4）修改Employees表中Gender字段的默认值以及备注，改为默认值为1  
`ALTER TABLE Employees MODIFY COLUMN Gender bit COMMENT '性别，默认值为1`  
![Mysql 修改默认值以及备注][Mysql 5]

（5）修改Employees表中Gender字段名称，改为Sex（改完记得改回来哦，不然可能会影响下一次练习）  
`ALTER TABLE Employees CHANGE Gender Sex bit DEFAULT 1 COMMENT '性别，默认值为1'`  
![Mysql 修改字段名称][Mysql 6]  
（注意：改名称时要**同时重新**指定数据类型，默认值，备注等）

（6）显示Employees中字段的所有属性  
首先，要进入information\_schema的数据库  
`USE information_schema`  
然后选择yggl数据库中的Employees表名进行查询  
`select * from COLUMNS where TABLE_SCHEMA='yggl' and TABLE_NAME='Employees'`

![Mysql显示表中字段属性][Mysql 7]

（7）创建部门信息（Departments）表

CREATE TABLE Departments(
    DepartmentID varchar(3) NOT NULL PRIMARY KEY COMMENT '部门编号，主键',
    DepartmentName varchar(20) NOT NULL COMMENT '部门名',
    Note varchar(100) COMMENT '备注'
    )

![Mysql创建表][Mysql 8]

（8）增加Employees表中DepartmentID的约束，外键指向Departments表中的DepartmentID

ALTER TABLE Employees ADD CONSTRAINT department_employee FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)

有可能遇到的错误：[\#1005 Errno 150][1005 Errno 150]

![Mysql 增加约束][Mysql 9]

（9）员工薪水情况（Salary）表，并建立外键指向Employees表中的EmployeeID

CREATE TABLE Salary(
    EmployeeID varchar(6) NOT NULL PRIMARY KEY,
    InCome FLOAT(11,2) NOT NULL,
    OutCome FLOAT(11,2) NOT NULL,
    ActIncome FLOAT(11,2),
    FOREIGN KEY(EmployeeID) references Employees(EmployeeID)
    )

![Mysql创建表建立外键][Mysql 10]  
（10）编写触发器，使创建或修改ActIncome时，都遵循：  
ActIncome = InCome - OutCome

在插入数据时，会触发addActIncome触发器，计算ActIncome的值

CREATE TRIGGER addActIncome BEFORE INSERT ON salary
       FOR EACH ROW
         SET NEW.ActIncome = NEW.InCome - NEW.OutCome

在修改数据时，会触发alterActIncome触发器，重新计算ActIncome的值

CREATE TRIGGER alterActIncome BEFORE UPDATE ON salary
       FOR EACH ROW
         SET NEW.ActIncome=NEW.InCome-NEW.OutCome

在此操作中，注意SET后面的语法， NEW.计算列 而不是只是计算列

[Mysql]: /images/20220713/92c3bffeace649929cea6bd3752f488f.png
[Mysql 1]: /images/20220713/49c1b8baa7344031b88ff43870f045b1.png
[Mysql 2]: /images/20220713/0b873ef6546d414bb7b47b66bd6f29c0.png
[Mysql 3]: /images/20220713/a400bbb811f64a14bda2d269c86c24e1.png
[Mysql 4]: /images/20220713/7c22f54d013e4e7da4a6ec5527ed43af.png
[Mysql 5]: /images/20220713/8b2183b4131d4fdf854f7eaa59fa0566.png
[Mysql 6]: /images/20220713/a062bb7c13d04b3cb5bf592e7657ebf8.png
[Mysql 7]: /images/20220713/13c42cac293f40de99ae2468fc65e0ae.png
[Mysql 8]: /images/20220713/30c4c5cbdc4e43d99a36ac5ffe5a0279.png
[1005 Errno 150]: http://blog.csdn.net/CheyenneLam/article/details/53861552
[Mysql 9]: /images/20220713/45c623d2179a4db49b287aa281b8593c.png
[Mysql 10]: /images/20220713/c93876f6847e4b8682f4028bbc60cd75.png