一、库
1、创建数据库
CREATE DATABASE IF NOT EXISTS name
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;2、删除数据库
DROP DATABASE IF EXISTS name;二、表（实现一个多表的操作）
-- 1. 删除原有的表
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS user_roles;
DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS user_departments;
-- 2. 创建users表
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT DEFAULT 20,
email VARCHAR(50) NOT NULL,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 3. 创建roles表
CREATE TABLE IF NOT EXISTS roles (
id INT AUTO_INCREMENT,
role_name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 4. 创建user_roles表（多对多关系）
CREATE TABLE IF NOT EXISTS user_roles (
user_id INT,
role_id INT,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (role_id) REFERENCES roles(id),
PRIMARY KEY (user_id, role_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 5. 创建departments表
CREATE TABLE IF NOT EXISTS departments (
id INT AUTO_INCREMENT,
department_name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 6. 创建user_departments表（多对多关系）
CREATE TABLE IF NOT EXISTS user_departments (
user_id INT,
department_id INT,
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (department_id) REFERENCES departments(id),
PRIMARY KEY (user_id, department_id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 7. 添加用户信息
INSERT INTO users (name, age, email, is_active) VALUES
('Alice', 25, 'alice@example.com', true),
('Bob', 30, 'bob@example.com', true),
('Charlie', 35, 'charlie@example.com', false);
-- 8. 添加角色信息
INSERT INTO roles (role_name) VALUES ('Admin'), ('User'), ('Manager');
-- 9. 添加部门信息
INSERT INTO departments (department_name) VALUES ('HR'), ('Engineering'), ('Sales');
-- 10. 分配角色给用户
INSERT INTO user_roles (user_id, role_id) VALUES
((SELECT id FROM users WHERE name = 'Alice'), (SELECT id FROM roles WHERE role_name = 'Admin')),
((SELECT id FROM users WHERE name = 'Bob'), (SELECT id FROM roles WHERE role_name = 'User')),
((SELECT id FROM users WHERE name = 'Charlie'), (SELECT id FROM roles WHERE role_name = 'Manager'));
-- 11. 分配部门给用户
INSERT INTO user_departments (user_id, department_id) VALUES
((SELECT id FROM users WHERE name = 'Alice'), (SELECT id FROM departments WHERE department_name = 'HR')),
((SELECT id FROM users WHERE name = 'Bob'), (SELECT id FROM departments WHERE department_name = 'Engineering')),
((SELECT id FROM users WHERE name = 'Charlie'), (SELECT id FROM departments WHERE department_name = 'Sales'));
-- 12. 更新用户信息
UPDATE users SET age = 26, email = 'alice_new@example.com' WHERE name = 'Alice';
-- 13. 删除一个用户
DELETE FROM users WHERE name = 'Charlie';
-- 14. 查询所有活跃用户的信息
SELECT * FROM users WHERE is_active = true;
-- 15. 查询年龄在25到35之间的用户，并按年龄升序排序
SELECT * FROM users WHERE age BETWEEN 25 AND 35 ORDER BY age ASC;
-- 16. 统计不同年龄段的用户数量
SELECT age, COUNT(*) as user_count FROM users GROUP BY age;
-- 17. 查询用户的名字和电子邮件地址，并按名字的字母顺序排列
SELECT name, email FROM users ORDER BY name;
-- 18. 查询用户注册时间在最近一个月内的用户
SELECT * FROM users WHERE created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH);
-- 19. 查询所有用户的总数
SELECT COUNT(*) as total_users FROM users;
-- 20. 批量更新所有用户的激活状态为不活跃
UPDATE users SET is_active = false;
-- 21. 恢复之前所有用户的激活状态
UPDATE users SET is_active = true;
-- 22. 查询电子邮件域名为 'example.com' 的所有用户
SELECT * FROM users WHERE email LIKE '%@example.com';
-- 23. 添加一个新列phone到用户表中
ALTER TABLE users ADD COLUMN phone VARCHAR(15);
-- 24. 更新某个用户的电话信息
UPDATE users SET phone = '123-456-7890' WHERE name = 'Bob';
-- 25. 删除phone列
ALTER TABLE users DROP COLUMN phone;
-- 26. 删除所有年龄大于30岁的用户
DELETE FROM users WHERE age > 30;
-- 27. 查询按年龄分组，并统计每组的活跃用户数量
SELECT age, COUNT(*) as active_users FROM users WHERE is_active = true GROUP BY age;
-- 28. 查询用户的角色
SELECT u.name, r.role_name FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id;
-- 29. 查询用户的部门
SELECT u.name, d.department_name FROM users u
JOIN user_departments ud ON u.id = ud.user_id
JOIN departments d ON ud.department_id = d.id;
-- 30. 查询每个部门的用户数量
SELECT d.department_name, COUNT(*) as user_count FROM departments d
JOIN user_departments ud ON d.id = ud.department_id
GROUP BY d.department_name;
-- 31. 查询每个角色的用户数量
SELECT r.role_name, COUNT(*) as user_count FROM roles r
JOIN user_roles ur ON r.id = ur.role_id
GROUP BY r.role_name;
-- 32. 查询每个用户的所有信息，包括角色和部门
SELECT u.*, r.role_name, d.department_name
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
JOIN user_departments ud ON u.id = ud.user_id
JOIN departments d ON ud.department_id = d.id;
-- 33. 添加新用户并分配角色和部门
INSERT INTO users (name, age, email, is_active) VALUES ('David', 28, 'david@example.com', true);
INSERT INTO user_roles (user_id, role_id) VALUES ((SELECT id FROM users WHERE name = 'David'), (SELECT id FROM roles WHERE role_name = 'User'));
INSERT INTO user_departments (user_id, department_id) VALUES ((SELECT id FROM users WHERE name = 'David'), (SELECT id FROM departments WHERE department_name = 'Engineering'));
-- 34. 批量插入多个用户
INSERT INTO users (name, age, email, is_active) VALUES
('Eve', 22, 'eve@example.com', true),
('Frank', 29, 'frank@example.com', true),
('Grace', 33, 'grace@example.com', false);
-- 35. 批量分配角色和部门给新用户
INSERT INTO user_roles (user_id, role_id) VALUES
((SELECT id FROM users WHERE name = 'Eve'), (SELECT id FROM roles WHERE role_name = 'User')),
((SELECT id FROM users WHERE name = 'Frank'), (SELECT id FROM roles WHERE role_name = 'Manager')),
((SELECT id FROM users WHERE name = 'Grace'), (SELECT id FROM roles WHERE role_name = 'Admin'));
INSERT INTO user_departments (user_id, department_id) VALUES
((SELECT id FROM users WHERE name = 'Eve'), (SELECT id FROM departments WHERE department_name = 'Sales')),
((SELECT id FROM users WHERE name = 'Frank'), (SELECT id FROM departments WHERE department_name = 'Engineering')),
((SELECT id FROM users WHERE name = 'Grace'), (SELECT id FROM departments WHERE department_name = 'HR'));
-- 36. 查询所有用户及其角色和部门信息
SELECT u.name, r.role_name, d.department_name FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
LEFT JOIN user_departments ud ON u.id = ud.user_id
LEFT JOIN departments d ON ud.department_id = d.id;
-- 37. 更新用户的角色
UPDATE user_roles SET role_id = (SELECT id FROM roles WHERE role_name = 'Manager') WHERE user_id = (SELECT id FROM users WHERE name = 'Bob');
-- 38. 更新用户的部门
UPDATE user_departments SET department_id = (SELECT id FROM departments WHERE department_name = 'HR') WHERE user_id = (SELECT id FROM users WHERE name = 'Bob');
-- 39. 删除某个用户的角色
DELETE FROM user_roles WHERE user_id = (SELECT id FROM users WHERE name = 'Alice');
-- 40. 删除某个用户的部门
DELETE FROM user_departments WHERE user_id = (SELECT id FROM users WHERE name = 'Alice');
-- 41. 查询每个部门的平均用户年龄
SELECT d.department_name, AVG(u.age) as avg_age FROM departments d
JOIN user_departments ud ON d.id = ud.department_id
JOIN users u ON ud.user_id = u.id
GROUP BY d.department_name;
-- 42. 查询每个角色的最年轻用户
SELECT r.role_name, MIN(u.age) as min_age FROM roles r
JOIN user_roles ur ON r.id = ur.role_id
JOIN users u ON ur.user_id = u.id
GROUP BY r.role_name;
-- 43. 查询每个部门的最老用户
SELECT d.department_name, MAX(u.age) as max_age FROM departments d
JOIN user_departments ud ON d.id = ud.department_id
JOIN users u ON ud.user_id = u.id
GROUP BY d.department_name;
-- 44. 添加新列last_login到用户表
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL;
-- 45. 更新用户的最后登录时间
UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE name = 'Alice';
-- 46. 查询最近登录的用户
SELECT * FROM users ORDER BY last_login DESC LIMIT 1;
-- 47. 删除所有角色为Manager的用户
DELETE FROM users WHERE id IN (SELECT user_id FROM user_roles WHERE role_id = (SELECT id FROM roles WHERE role_name = 'Manager'));
-- 48. 删除所有部门为Sales的用户
DELETE FROM users WHERE id IN (SELECT user_id FROM user_departments WHERE department_id = (SELECT id FROM departments WHERE department_name = 'Sales'));
-- 49. 删除所有最后登录时间超过一年的用户
DELETE FROM users WHERE last_login < DATE_SUB(CURRENT_DATE, INTERVAL 1 YEAR);
-- 50. 查询用户的创建时间和最后登录时间的差异（以天为单位）
SELECT name, DATEDIFF(last_login, created_at) as days_diff FROM users WHERE last_login IS NOT NULL;
