MySQL_Z11数据处理之增删改

#1. 创建数据库dbtest11
create database if not exists dbtest11 character set 'utf8';
#2. 运行以下脚本创建表my_employees
USE dbtest11;
CREATE TABLE my_employees(
id INT(10),
first_name VARCHAR(10),
last_name VARCHAR(10),
userid VARCHAR(10),
salary DOUBLE(10,2)
);
CREATE TABLE users(
id INT,
userid VARCHAR(10),
department_id INT
);

#3. 显示表my_employees的结构
desc my_employees;

#4. 向my_employees表中插入下列数据
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),
(5, 'Ropeburn', 'Audrey', 'Aropebur', 1550);

select *
from my_employees;

#5. 向users表中插入数据
insert into users
values
(1, 'Rpatel', 10),
(2, 'Bdancs', 10),
(3, 'Bbiri', 20),
(4, 'Cnweman', 30),
(5, 'Aropebur', 40);

select *
from users;

#6. 将3号员工的last_name修改为“drelxer”
update my_employees
set LAST_NAME = 'drelxer'
where ID = 3;

#7. 将所有工资少于900的员工的工资修改为1000
update my_employees
set SALARY = 1000
where SALARY < 900;

select *
from my_employees;

#8. 将userid为Bbiri的user表和my_employees表的记录全部删除
delete u, e
from users u join my_employees e 
on u.userid = e.Userid
where u.userid = 'Bbiri';

#9. 删除my_employees、users表所有数据
delete from my_employees;
delete from users;

#10. 检查所作的修正
select *
from my_employees;

#11. 清空表my_employees
truncate table my_employees;