DDL&DML课堂练习（MySQL）

1.创建db_demo数据库，设置数据库字符集为utf8;

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

2.使用db_demo数据库

2.1 创建表dept1

字段名	是否为空	数据类型
id		int(7)
name		varchar(25)

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

2.2 创建表emp5

字段名	是否为空	数据类型
id		int(7)
frist_name		varchar(25)
last_name		varchar(25)
dept_id		int(7)

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

id	first_name	last_name	userid	salary
1	patel	Ralph	Rpatel	895
2	Dancs	Betty	Bdancs	860
3	Biri	Ben	Bbiri	1100
4	Newman	Chad	Cnewman	750

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 表中插入数据

id	userid	department_id
1	Rpatel	10
2	Bdancs	10
3	Bbiri	20
4	Cnewman	30
5	Aropebur	40

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`;