DDL&DML课堂练习（MySQL）

谁践踏了优雅 2024-04-01 10:04 73阅读 0赞

### 1.创建db\_demo数据库，设置数据库字符集为utf8; ###

CREATE DATABASE IF NOT EXISTS db_demo;
    ALTER DATABASE db_demo CHARACTER SET utf8;

### 2.使用db\_demo数据库 ###

#### 2.1 创建表dept1 ####

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>是否为空</th> 
   <th>数据类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td></td> 
   <td>int(7)</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td></td> 
   <td>varchar(25)</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE dept1(
    id INT(7),
    NAME VARCHAR(25)
    );

#### 2.2 创建表emp5 ####

<table> 
 <thead> 
  <tr> 
   <th>字段名</th> 
   <th>是否为空</th> 
   <th>数据类型</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td></td> 
   <td>int(7)</td> 
  </tr> 
  <tr> 
   <td>frist_name</td> 
   <td></td> 
   <td>varchar(25)</td> 
  </tr> 
  <tr> 
   <td>last_name</td> 
   <td></td> 
   <td>varchar(25)</td> 
  </tr> 
  <tr> 
   <td>dept_id</td> 
   <td></td> 
   <td>int(7)</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE emp5(
    id INT(7),
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    dept_id INT(7)
    );

### 3.将表departments中的数据插入新表dept2中 ###

CREATE TABLE dept2
    SELECT department_id,department_name
    FROM myemployees.`departments`;

#### 3.2 根据表employees创建employees2 ####

CREATE TABLE employees2 LIKE myemployees.`employees`;

#### 3.3 删除表emp5 ####

DROP TABLE IF EXISTS emp5;

#### 3.4 向表dept2的id列中添加PRIMARY KEY约束（my\_dept\_id\_pk） ####

ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);

#### 3.5 向表employees2中定义FOREIGNKEY约束，与之相关联的列是dept2表中的id列 ####

ALTER TABLE emp2 ADD COLUMN dept_id INT;
    ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);

### 4.运行以下脚本创建表my\_employees ###

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>first_name</th> 
   <th>last_name</th> 
   <th>userid</th> 
   <th>salary</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>patel</td> 
   <td>Ralph</td> 
   <td>Rpatel</td> 
   <td>895</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Dancs</td> 
   <td>Betty</td> 
   <td>Bdancs</td> 
   <td>860</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Biri</td> 
   <td>Ben</td> 
   <td>Bbiri</td> 
   <td>1100</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Newman</td> 
   <td>Chad</td> 
   <td>Cnewman</td> 
   <td>750</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE my_employees(
    id INT,
    first_name VARCHAR(20),
    last_name VARCHAR(20),
    userid VARCHAR(20),
    salary INT
    );
    
    
    INSERT INTO my_employees(id,first_name,last_name,userid,salary)
    VALUES (1,'patel','Ralph','Rpatel',895),
    (2,'Dancs','Betty','Bdancs',860),
    (3,'Biri','Ben','Bbiri',1100),
    (4,'Newman','Chad','Cnewman',750);

#### 4.2 向 users 表中插入数据 ####

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>userid</th> 
   <th>department_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Rpatel</td> 
   <td>10</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Bdancs</td> 
   <td>10</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Bbiri</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Cnewman</td> 
   <td>30</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Aropebur</td> 
   <td>40</td> 
  </tr> 
 </tbody> 
</table>

CREATE TABLE users(
    id INT,
    userid VARCHAR(20),
    department_id INT
    );
    
    INSERT INTO users(id,userid,department_id)
    VALUES (1,'Rpatel',10),
    (2,'Bdancs',10),
    (3,'Bbiri',20),
    (4,'Cnewman',30),
    (5,'Aropebur',40);

#### 4.3 将 3 号员工的 last\_name 修改为“drelxer” ####

UPDATE `my_employees` SET last_name='drelxer'
    WHERE id=3;

#### 4.4 将所有工资少于 900 的员工的工资修改为 1000 ####

UPDATE `my_employees` SET salary=1000
    WHERE salary<900;

####  ####

#### 4.5 将 userid 为 Bbiri 的 user 表和 my\_employees 表的记录全部删除 ####

DELETE FROM `users`
    WHERE userid='Bbiri';
    
    TRUNCATE TABLE `my_employees`;

#### 4.6 删除所有数据 ####

TRUNCATE TABLE `users`;

#### 4.7 检查所作的修正 ####

#### 4.8 清空表 my\_employees ####

TRUNCATE TABLE `my_employees`;