MySQL 基础面试题

CREATE DATABASE your_database_name;

CREATE TABLE your_table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

INSERT INTO your_table_name (name, age) VALUES ('John', 25);

UPDATE your_table_name SET age = 26 WHERE name = 'John';

DELETE FROM your_table_name WHERE name = 'Jane';

DROP TABLE your_table_name;

ALTER TABLE your_table_name ADD COLUMN new_column INT;
ALTER TABLE your_table_name DROP COLUMN old_column;
ALTER TABLE your_table_name MODIFY COLUMN column_name VARCHAR(255);

SELECT * FROM your_table_name;

SELECT * FROM your_table_name WHERE age > 20;

SELECT * FROM your_table_name ORDER BY age DESC;

SELECT SUM(age), AVG(age), COUNT(*) FROM your_table_name;

SELECT name, COUNT(*) FROM your_table_name GROUP BY name;

SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

SELECT product_name
FROM products
WHERE price > (SELECT AVG(price) FROM products);

SELECT * FROM sales LIMIT 10;

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

CREATE INDEX idx_product_name ON products (product_name);

DROP INDEX idx_product_name ON products;

mysqldump -u username -p dbname > backup.sql
mysql -u username -p dbname < backup.sql

START TRANSACTION;
INSERT INTO orders (customer_id, amount) VALUES (1, 100);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
COMMIT; -- 提交事务
-- 或 ROLLBACK; -- 回滚事务

DELIMITER //
CREATE PROCEDURE GetOrderCount(IN customerID INT)
BEGIN
    SELECT COUNT(*) FROM orders WHERE customer_id = customerID;
END //
DELIMITER ;

CALL GetOrderCount(1);

DELIMITER //
CREATE TRIGGER UpdateTotalSpent
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE customers SET total_spent = total_spent + NEW.amount WHERE customer_id = NEW.customer_id;
END //
DELIMITER ;

LOAD DATA INFILE 'data.csv'
INTO TABLE your_table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(column1, column2, ...);

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SHOW DATABASES;
SHOW TABLES;
SHOW COLUMNS FROM your_table_name;

EXPLAIN SELECT * FROM your_table_name WHERE column_name = 'value';

SELECT * FROM articles WHERE MATCH(content) AGAINST('search_keyword');

SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1;

SELECT * FROM orders WHERE order_date > '2023-01-01';