阅读量:0
MySQL 场景面试题
目录
场景1：用户注册和登录系统
1.1 数据库设计
设计一个简单的用户注册和登录系统，包含用户表 users，表结构如下：
CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 1.2 用户注册
用户注册时，需要将用户名、密码和邮箱存入数据库。使用如下 SQL 语句进行用户注册：
INSERT INTO users (username, password, email) VALUES ('test_user', 'password123', 'test_user@example.com'); 假设在代码中，使用准备好的语句进行注册操作：
import mysql.connector def register_user(username, password, email): conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test_db') cursor = conn.cursor() try: cursor.execute("INSERT INTO users (username, password, email) VALUES (%s, %s, %s)", (username, password, email)) conn.commit() print("User registered successfully") except mysql.connector.Error as err: print("Error: {}".format(err)) finally: cursor.close() conn.close() # Example usage register_user('test_user', 'password123', 'test_user@example.com') 1.3 用户登录
用户登录时，需要验证用户名和密码。使用如下 SQL 语句进行验证：
SELECT * FROM users WHERE username = 'test_user' AND password = 'password123'; 假设在代码中，使用准备好的语句进行登录验证：
def login_user(username, password): conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test_db') cursor = conn.cursor() cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password)) user = cursor.fetchone() if user: print("Login successful") else: print("Invalid username or password") cursor.close() conn.close() # Example usage login_user('test_user', 'password123') 场景2：订单管理系统
2.1 数据库设计
设计一个简单的订单管理系统，包括订单表 orders 和订单详情表 order_items，表结构如下：
CREATE TABLE orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, total_amount DECIMAL(10, 2) NOT NULL ); CREATE TABLE order_items ( item_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, price DECIMAL(10, 2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(order_id) ); 2.2 创建订单
创建订单时，需要在 orders 表中插入订单记录，并在 order_items 表中插入订单项记录。使用如下 SQL 语句进行插入：
INSERT INTO orders (user_id, total_amount) VALUES (1, 100.00); INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (LAST_INSERT_ID(), 101, 2, 50.00); 假设在代码中，使用事务进行创建订单操作：
def create_order(user_id, order_items): conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test_db') cursor = conn.cursor() try: cursor.execute("INSERT INTO orders (user_id, total_amount) VALUES (%s, %s)", (user_id, sum(item['quantity'] * item['price'] for item in order_items))) order_id = cursor.lastrowid for item in order_items: cursor.execute("INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (%s, %s, %s, %s)", (order_id, item['product_id'], item['quantity'], item['price'])) conn.commit() print("Order created successfully") except mysql.connector.Error as err: conn.rollback() print("Error: {}".format(err)) finally: cursor.close() conn.close() # Example usage order_items = [ {'product_id': 101, 'quantity': 2, 'price': 50.00}, {'product_id': 102, 'quantity': 1, 'price': 100.00} ] create_order(1, order_items) 2.3 查询订单
查询订单时，需要关联 orders 表和 order_items 表。使用如下 SQL 语句进行查询：
SELECT o.order_id, o.order_date, o.total_amount, i.product_id, i.quantity, i.price FROM orders o JOIN order_items i ON o.order_id = i.order_id WHERE o.user_id = 1; 假设在代码中，进行查询订单操作：
def get_orders(user_id): conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test_db') cursor = conn.cursor() cursor.execute(""" SELECT o.order_id, o.order_date, o.total_amount, i.product_id, i.quantity, i.price FROM orders o JOIN order_items i ON o.order_id = i.order_id WHERE o.user_id = %s """, (user_id,)) orders = cursor.fetchall() for order in orders: print(order) cursor.close() conn.close() # Example usage get_orders(1) 场景3：博客系统
3.1 数据库设计
设计一个简单的博客系统，包括文章表 posts 和评论表 comments，表结构如下：
CREATE TABLE posts ( post_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, content TEXT NOT NULL, author_id INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE comments ( comment_id INT AUTO_INCREMENT PRIMARY KEY, post_id INT NOT NULL, user_id INT NOT NULL, comment TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (post_id) REFERENCES posts(post_id) ); 3.2 发布文章
发布文章时，需要在 posts 表中插入记录。使用如下 SQL 语句进行插入：
INSERT INTO posts (title, content, author_id) VALUES ('My First Post', 'This is the content of my first post.', 1); 假设在代码中，进行发布文章操作：
def create_post(title, content, author_id): conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test_db') cursor = conn.cursor() try: cursor.execute("INSERT INTO posts (title, content, author_id) VALUES (%s, %s, %s)", (title, content, author_id)) conn.commit() print("Post created successfully") except mysql.connector.Error as err: print("Error: {}".format(err)) finally: cursor.close() conn.close() # Example usage create_post('My First Post', 'This is the content of my first post.', 1) 3.3 评论功能
用户可以对文章进行评论，需要在 comments 表中插入记录。使用如下 SQL 语句进行插入：
INSERT INTO comments (post_id, user_id, comment) VALUES (1, 2, 'This is a comment.'); 假设在代码中，进行评论操作：
def add_comment(post_id, user_id, comment): conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='test_db') cursor = conn.cursor() try: cursor.execute("INSERT INTO comments (post_id, user_id, comment) VALUES (%s, %s, %s)", (post_id, user_id, comment)) conn.commit() print("Comment added successfully") except mysql.connector.Error as err: print("Error: {}".format(err)) finally: cursor.close() conn.close() # Example usage add_comment(1, 2, 'This is a comment.') 以上是关于 MySQL 的详细场景面试题及解答，涵盖了用户注册和登录系统、订单管理系统和博客系统的具体场景和代码实现，供参考学习。
