MySQL基础教程

发布于：2025-07-21 ⋅ 阅读:(17) ⋅ 点赞:(0)

一、安装与登录

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

命令拆解：

  • mysqldump：备份工具主命令
  • -u root：指定连接数据库的用户（这里是 root）
  • -p：提示输入用户密码（执行后会要求输入 root 的密码）
  • mydatabase：指定要备份的数据库名
  • users：指定要备份的表名（仅备份该表）
  • users_backup.sql：将备份内容输出到 users_backup.sql 文件（重定向输出）

备份原理：

  • mysqldump 通过 root 用户连接 MySQL 服务器，验证密码后访问 mydatabase 数据库；
  • 读取 users 表的元信息（结构）：生成 CREATE TABLE users (…) 语句（包含字段、类型、约束等）；
  • 读取 users 表的所有数据：生成 INSERT INTO users (…) VALUES (…) 语句（一行数据对应一条或批量 INSERT）；
  • 为了避免恢复时的冲突，会自动添加 DROP TABLE IF EXISTS users;（如果表已存在则先删除）；
  • 将上述所有 SQL 语句通过 > 重定向到 users_backup.sql 文件，完成备份。

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布