Mysql

2024-06-12 10:16:02
开发
10

一、库

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;

原文地址:https://blog.csdn.net/m0_73337964/article/details/139587181 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：https://www.suanlizi.com/kf/1800713639632703488.html 如若内容造成侵权/违法违规/事实不符，请联系《酸梨子》网邮箱：1419361763@qq.com进行投诉反馈，一经查实，立即删除！

阅读全部

Mysql

一、库

二、表（实现一个多表的操作）

相关推荐

最近更新

热门阅读