1. MySQL基础操作
MySQL是一个强大的关系型数据库管理系统。要开始使用MySQL,首先需要掌握一些基本操作。
a. 连接到MySQL服务器
使用命令行连接MySQL:
mysql -u username -p
输入密码后,你就可以连接到MySQL服务器了。
b. 创建和管理数据库
创建新数据库:
CREATE DATABASE mydatabase;
切换到该数据库:
USE mydatabase;
查看所有数据库:
SHOW DATABASES;
删除数据库:
DROP DATABASE mydatabase;
c. 创建和管理表
创建新表:
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE
);
查看表结构:
DESCRIBE employees;
修改表结构:
ALTER TABLE employees ADD COLUMN salary DECIMAL(10,2);
删除表:
DROP TABLE employees;
2. MySQL数据类型
MySQL提供了多种数据类型,以适应不同的数据存储需求。
a. 数值类型
INT: 整数 DECIMAL(M,D): 精确小数
b. 字符串类型
VARCHAR(N): 可变长度字符串 TEXT: 长文本
c. 日期和时间类型
DATE: 日期 DATETIME: 日期和时间
d. 选择合适数据类型的原则
选择能够准确表示数据的最小数据类型 考虑未来可能的数据增长 对于频繁查询的列,选择适合索引的类型
3. MySQL的增删改查(CRUD)操作
a. 插入数据(INSERT)
INSERT INTO employees (name, email, hire_date)
VALUES ('John Doe', 'john@example.com', '2023-01-15');
b. 查询数据(SELECT)
SELECT * FROM employees WHERE hire_date > '2023-01-01';
c. 更新数据(UPDATE)
UPDATE employees SET salary = 50000 WHERE name = 'John Doe';
d. 删除数据(DELETE)
DELETE FROM employees WHERE id = 1;
4. MySQL高级查询技巧
a. 子查询
SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
b. 组函数和分组(GROUP BY)
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;
c. HAVING子句
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;
d. JOIN连接查询
SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id;
5. MySQL索引
a. 创建索引
CREATE INDEX idx_name ON employees(name);
b. 删除索引
DROP INDEX idx_name ON employees;
好的,我们继续讨论剩下的主题:
6. MySQL事务处理
事务是保证数据库操作的一致性和完整性的重要机制。
a. 事务的ACID属性
原子性(Atomicity): 事务中的所有操作要么全部完成,要么全部不完成。 一致性(Consistency): 事务开始前和结束后,数据库的完整性约束没有被破坏。 隔离性(Isolation): 多个事务并发执行时,一个事务的执行不应影响其他事务的执行。 持久性(Durability): 事务完成后,其结果是永久性的。
b. 开始、提交和回滚事务
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
如果出现错误,可以回滚事务:
ROLLBACK;
c. 事务隔离级别
MySQL支持四种事务隔离级别:
READ UNCOMMITTED READ COMMITTED REPEATABLE READ (默认) SERIALIZABLE
设置隔离级别:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
7. MySQL视图和存储过程
a. 创建和使用视图
视图是一个虚拟表,基于SQL语句的结果集:
CREATE VIEW high_salary_employees AS
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM high_salary_employees;
b. 创建和调用存储过程
存储过程是一组预编译的SQL语句:
DELIMITER //
CREATE PROCEDURE get_employee_by_id(IN emp_id INT)
BEGIN
SELECT * FROM employees WHERE id = emp_id;
END //
DELIMITER ;
CALL get_employee_by_id(1);
8. MySQL用户管理和权限控制
a. 创建和删除用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
DROP USER 'newuser'@'localhost';
b. 授予和撤销权限
GRANT SELECT, INSERT ON mydatabase.* TO 'newuser'@'localhost';
REVOKE INSERT ON mydatabase.* FROM 'newuser'@'localhost';
MySQL性能优化基础
a. 使用EXPLAIN分析查询
EXPLAIN命令可以显示MySQL如何执行查询:
EXPLAIN SELECT * FROM employees WHERE salary > 50000;
b. 查询优化技巧
只选择需要的列,避免使用SELECT * 使用适当的索引 避免在WHERE子句中使用函数,这可能会阻止使用索引 使用LIMIT限制结果集大小
示例优化查询:
-- 优化前
SELECT * FROM employees WHERE YEAR(hire_date) = 2023;
-- 优化后
SELECT id, name, email FROM employees WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';
这个优化后的查询可以使用hire_date列上的索引,并且只返回需要的列。
—完—
推荐一下最近更新的《SQL145题》专栏，目前仅售10元，精选工作中145道常见面试题，对应想提升SQL查询能力的帮助明显。
