mysql基础复习——数据增删改练习

创建需要用的数据表

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

# 查看两个表的结构
DESC my_employees;
DESC users;

# 向my_employees表中添加数据
# 方式一
INSERT INTO my_employees
VALUE(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);

# 方式二：
INSERT INTO my_employees
SELECT 1, 'patel', 'Ralph', 'Rpatel',895 UNION
SELECT 2, 'Dancs', 'Betty', 'Bdancs', 860 UNION
SELECT 3, 'Biri', 'Ben', 'Bbiri',1100 UNION
SELECT 4, 'Newman', 'Chad', 'Cnewman', 750 UNION
SELECT 5, 'Ropeburn', 'Audrey', 'Aropebur', 1550; 

# 向users表中添加数据
# 方式一：
insert into users
value(1, 'Rpatel', 10),
(2, 'Bdancs', 10),
(3, 'Bbiri', 20),
(4, 'Cnewman', 30),
(5, 'Aropebur', 40);

# 方式二：
INSERT INTO users
SELECT 1, 'Rpatel', 10 UNION
SELECT 2, 'Bdancs', 10 UNION
SELECT 3, 'Bbiri', 20 UNION
SELECT 4, 'Cnewman', 30 UNION
SELECT 5, 'Aropebur', 40;

# 将3号员工的last_name修改为“drelxer”
UPDATE my_employees AS me
SET me.`last_name` = 'drelxer'
WHERE me.`id`=3;

# 将工资少于900的员工的工资修改为1000
UPDATE my_employees AS me
SET me.`salary` = 1000
WHERE me.`salary`<900;

# 将userid为Bbiri的USERS表和my_employees表的记录全部删除
DELETE me,u
FROM my_employees AS me
INNER JOIN users AS u
ON me.`userid` = u.`userid`
WHERE me.`userid` = 'Bbiri';

# 删除所有数据
DELETE FROM my_employees;
DELETE FROM users;

# 清空两表
TRUNCATE TABLE my_employees;
TRUNCATE TABLE users;