当前位置：首页 > news >正文

MySQL基础教程

news 2025/7/20 11:58:46

一、安装与登录

1. 安装

windows下载链接：https://dev.mysql.com/downloads/mysql/，默认安装

2. 登录

打开cmd，cd进入到安装路径，然后运行：

mysql -u root -p

输入密码后进入 MySQL 命令行。

二、基本操作

1. 创建数据库

CREATE DATABASE mydatabase;

2. 查询已创建数据库

SHOW DATABASES;

3. 选择数据库

USE mydatabase;

4. 查看当前使用数据库

SELECT DATABASE();

5. 创建表

-- 用户表
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE,age INT CHECK (age >= 18),created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 订单表
CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT,product_name VARCHAR(100),amount DECIMAL(10, 2),order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY (user_id) REFERENCES users(id)
);

6. 查询所有表

SHOW TABLES;

7. 插入数据

-- 向 users 表插入数据
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 25),('Jane Smith', 'jane@example.com', 30);-- 向 orders 表插入数据
INSERT INTO orders (user_id, product_name, amount)
VALUES (1, 'Laptop', 999.99),(1, 'Mouse', 29.99),(2, 'Keyboard', 59.99);

在这里插入图片描述

8. 查询数据

-- 查询所有用户
SELECT * FROM users;

-- 查询 John 的订单
SELECT * 
FROM orders 
WHERE user_id = (SELECT id FROM users WHERE name = 'John Doe');

在这里插入图片描述

-- 联合查询用户及其订单
SELECT users.name, orders.product_name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;

在这里插入图片描述

9. 更新数据

-- 将 John 的年龄更新为 26
UPDATE users 
SET age = 26 
WHERE name = 'John Doe';

在这里插入图片描述

-- 将所有订单金额增加 10%
UPDATE orders 
SET amount = amount * 1.1;

在这里插入图片描述

10. 删除数据

-- 删除 Jane 的订单
DELETE FROM orders 
WHERE user_id = (SELECT id FROM users WHERE name = 'Jane Smith');-- 删除 Jane 用户
DELETE FROM users 
WHERE name = 'Jane Smith';

三、数据类型与约束

1. 数据类型

MySQL 支持多种数据类型，常见的有：

数值类型: INT, FLOAT, DOUBLE, DECIMAL
字符串类型: VARCHAR, CHAR, TEXT
日期时间类型: DATE, TIME, DATETIME, TIMESTAMP
布尔类型: TINYINT(1)（0 表示FALSE，1 表示TRUE）

2. 约束

约束用于确保数据的完整性，常见约束有：

PRIMARY KEY: 唯一标识表中每条记录。
UNIQUE: 确保字段值唯一。
NOT NULL: 字段不能为空。
CHECK: 限制字段值的范围。
FOREIGN KEY: 建立表间关联。

四、表间关系示例

1. 一对多关系

-- 部门表（主表）
CREATE TABLE departments (id INT PRIMARY KEY,name VARCHAR(50)
);-- 员工表（从表）
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(50),department_id INT,salary DECIMAL(10, 2),FOREIGN KEY (department_id) REFERENCES departments(id)
);-- 插入示例数据
INSERT INTO departments (id, name) VALUES (1, 'IT'), (2, 'HR');
INSERT INTO employees (id, name, department_id, salary) 
VALUES (101, 'Alice', 1, 8000),(102, 'Bob', 1, 7500),(103, 'Charlie', 2, 6000);

在这里插入图片描述

2. 多对多关系

-- 学生表
CREATE TABLE students (student_id INT PRIMARY KEY,name VARCHAR(50)
);-- 课程表
CREATE TABLE courses (course_id INT PRIMARY KEY,course_name VARCHAR(50)
);-- 中间表（学生-课程关联）
CREATE TABLE student_courses (student_id INT,course_id INT,PRIMARY KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES students(student_id),FOREIGN KEY (course_id) REFERENCES courses(course_id)
);-- 插入示例数据
INSERT INTO students (student_id, name) VALUES (1, 'David'), (2, 'Emma');
INSERT INTO courses (course_id, course_name) VALUES (1001, 'Math'), (1002, 'Physics');
INSERT INTO student_courses (student_id, course_id) VALUES (1, 1001), (1, 1002), (2, 1001);

在这里插入图片描述

五、高级查询示例

1. 连接查询

-- 查询每个部门的员工数量
SELECT departments.name, COUNT(employees.id) AS employee_count
FROM departments
LEFT JOIN employees ON departments.id = employees.department_id
GROUP BY departments.name;

在这里插入图片描述

-- 查询选修了 Math 课程的学生
SELECT students.name
FROM students
JOIN student_courses ON students.student_id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.course_id
WHERE courses.course_name = 'Math';

在这里插入图片描述

2. 子查询

-- 查询工资高于平均工资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

在这里插入图片描述

-- 查询没有订单的用户
SELECT name
FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

在这里插入图片描述

3. 聚合函数

-- 统计每个部门的总工资和平均工资
SELECT department_id,SUM(salary) AS total_salary,AVG(salary) AS average_salary,COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

在这里插入图片描述

4. 分组与过滤

-- 查询订单总金额超过 1000 的用户
SELECT users.name,SUM(orders.amount) AS total_spent
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.id
HAVING SUM(orders.amount) > 1000;

在这里插入图片描述

六、用户权限管理

-- 创建只读用户
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON mydatabase.* TO 'readonly_user'@'localhost';-- 创建读写用户（仅限 users 表）
CREATE USER 'user_manager'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.users TO 'user_manager'@'localhost';

七、备份与恢复

# 备份单个表
mysqldump -u root -p mydatabase users > users_backup.sql# 备份多个表
mysqldump -u root -p mydatabase users orders > data_backup.sql# 恢复表（需先创建数据库）
mysql -u root -p mydatabase < data_backup.sql