mysql 练习

第一步：创建数据库和表

-- 创建数据库
CREATE DATABASE IF NOT EXISTS practice_db     #if not exists 表示没有数据库就创建有就忽略
  CHARACTER SET utf8mb4                       #设置字符集为utf8mb4
  COLLATE utf8mb4_unicode_ci;                 
USE practice_db;

-- 创建用户表
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,     #int整数类型，auto自动递增，key设置主键
    name VARCHAR(50) NOT NULL,              #varchar字符类型，最大50个，not null不能为空
    email VARCHAR(100),
    age INT,
    join_date DATE                     #date注册时间，必填（格式 YYYY-MM-DD）
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product VARCHAR(50),
    amount DECIMAL(10,2),                 #数据类型，10是最大位数，2是允许2个小数点
    order_date DATE,                      #date订单日期
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

DESCRIBE table_name;     #查看表所需填入的内容

第二步：插入测试数据 (使用 Python Faker)

from faker import Faker
import mysql.connector
from datetime import datetime, timedelta
import random

fake = Faker()

# 连接数据库
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="admin123.",
    database="practice_db",
    auth_plugin='mysql_native_password'
)

cursor = db.cursor()

# 插入用户数据
for _ in range(50):
    name = fake.name()
    email = fake.email()
    age = random.randint(18, 70)
    join_date = fake.date_between(start_date='-5y')

    cursor.execute(
        "INSERT INTO users (name, email, age, join_date) VALUES (%s, %s, %s, %s)",
        (name, email, age, join_date)
    )

db.commit()
print("已插入50个用户数据")

# 插入订单数据
cursor.execute("SELECT user_id FROM users")
user_ids = [row[0] for row in cursor.fetchall()]

products = ['Laptop', 'Phone', 'Tablet', 'Headphones', 'Camera']

for _ in range(100):
    user_id = random.choice(user_ids)
    product = random.choice(products)
    amount = round(random.uniform(50, 2000), 2)
    order_date = fake.date_between(start_date='-1y')

    cursor.execute(
        "INSERT INTO orders (user_id, product, amount, order_date) VALUES (%s, %s, %s, %s)",
        (user_id, product, amount, order_date)
    )

db.commit()
print("已插入100个订单数据")
cursor.close()
db.close()

基础练习题目

1. 增删改查 (CRUD)

-- 插入新用户
INSERT INTO users (name, email, age, join_date) 
VALUES ('张三', 'zhangsan@example.com', 30, '2023-05-01');

-- 更新用户年龄
UPDATE users SET age = 31 WHERE name = '张三';

select * from users where name = '张三';

-- 删除测试用户 (注意：实际工作慎用DELETE)
DELETE FROM users WHERE email = 'test@example.com';

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

2. 条件语句 (WHERE/AND/OR)

-- 查找30岁以上的用户
SELECT * FROM users WHERE age > 30;

-- 查找手机或相机订单
SELECT * FROM orders 
WHERE product = 'Phone' OR product = 'Camera';

-- 查找2023年下半年的大额订单 (>$1000)
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-07-01' AND '2023-12-31'
AND amount > 1000;

3. 模糊匹配和排序 (LIKE/ORDER BY)

-- 查找名字包含"李"的用户
SELECT * FROM users WHERE name LIKE '%李%';

-- 查找Gmail邮箱用户
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- 按年龄降序排列用户
SELECT name, age FROM users ORDER BY age DESC;
# 从表中选择name和age字段，来自users表，选择age为关键字，DESC降序，ACE是升序，默认是升序
#ORDER BY 选择关键字

-- 按订单金额从高到低排序
SELECT * FROM orders ORDER BY amount DESC;

4. 常用函数

-- 统计用户总数
SELECT COUNT(*) AS total_users FROM users;
# count统计，as给结果列表命名为total_users

-- 计算平均订单金额
SELECT AVG(amount) AS avg_order_amount FROM orders;

-- 找到最高订单金额
SELECT MAX(amount) AS max_order FROM orders;

-- 显示当前日期和时间
SELECT NOW() AS current_datetime;

-- 统计每个产品的订单数量
SELECT product, COUNT(*) AS order_count 
FROM orders GROUP BY product;
#group by 表示分组，每个商品分成一个组

5. 多表连接

-- 显示所有订单及对应的用户信息
SELECT o.order_id, u.name, o.product, o.amount, o.order_date
FROM orders o
JOIN users u ON o.user_id = u.user_id;
#o指的是orders表，u是users,join表示把users的user_id和orders的user_id匹配，然后列出来
# join 表名  on  在 JOIN 中使用，用于指定两个表之间如何进行连接的条件

-- 查找没有订单的用户
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
#LEFT JOIN表示保留左表的内容，即users表

-- 计算每个用户的总消费金额
SELECT u.name, SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id
ORDER BY total_spent DESC;

JOIN 类型	描述	是否常用
`INNER JOIN`	只返回两边都匹配的记录	✅ 高频
`LEFT JOIN`	返回左表所有记录，右表无匹配则为 NULL	✅ 高频
`RIGHT JOIN`	返回右表所有记录，左表无匹配则为 NULL	❌ 较少
`FULL JOIN`	返回两边所有记录，无匹配则为 NULL	❌（MySQL 不支持）
`CROSS JOIN`	所有组合，笛卡尔积	❌ 极少
`SELF JOIN`	同一张表内部连接	✅ 特殊场景

6.删除数据库然后重新恢复

#导出数据库的数据
mysqldump -u username -p database_name > backup.sql
#删除数据库
DROP DATABASE database_name;
#创建数据库
mysql -uroot -padmin123 -e 'CREATE DATABASE practice_db CHARACTER SET utf8mb4;'
#导入数据库数据
mysql -u username -p database_name < backup.sql

练习答案验证技巧

执行查询后检查返回的行数是否符合预期
使用 LIMIT 5 查看部分结果样本
验证计算结果的合理性：

``` -- 检查平均订单金额是否在合理范围 SELECT AVG(amount) FROM orders;

-- 检查最高金额订单 SELECT * FROM orders ORDER BY amount DESC LIMIT 1;

-- 验证连接查询 SELECT COUNT(*) FROM orders WHERE user_id NOT IN (SELECT user_id FROM users); ```

Oracle 11g 练习

这里使用docker 拉取镜像进行练习

docker pull wnameless/oracle-xe-11g-r2

docker run -d \
  --name oracle-11 \
  -p 1521:1521 \
  wnameless/oracle-xe-11g-r2

docker exec -it oralce bash  #进入容器
sqlplus system/oracle@xe   #连接数据库

镜像默认

用户名：system 密码：oracle 用户名：sys 密码：oracle（连接为SYSDBA）

创建一个用户进行练习

CREATE USER test_user IDENTIFIED BY password;   #创建用户

GRANT CONNECT, RESOURCE TO test_user;            #授权

sqlplus test_user/password@xe                   #登录

插入数据进行练习

-- 员工数据
INSERT INTO employees VALUES (1, '张三', 'IT', 8000, TO_DATE('2020-01-15', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (2, '李四', '销售', 6500, TO_DATE('2021-03-22', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (3, '王五', 'IT', 9000, TO_DATE('2019-11-05', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (4, '赵六', 'HR', 5500, TO_DATE('2022-07-30', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (5, '钱七', '销售', 7200, TO_DATE('2020-05-18', 'YYYY-MM-DD'));

-- 部门数据
INSERT INTO departments VALUES (10, 'IT', '北京');
INSERT INTO departments VALUES (20, '销售', '上海');
INSERT INTO departments VALUES (30, 'HR', '广州');

一、增删改查 (CRUD)

插入新员工：刘八，财务部，工资6000，入职日期今天

sql INSERT INTO employees VALUES (7,'刘八','财务部',6000,TO_DATE('2027-07-04','YYYY-MM-DD'));

将王五的工资更新为9500

sql UPDATE employees set salary=9500 where name = '王五';

删除部门为HR的所有员工

sql DELETE FROM employees WHERE department = 'HR';

查询所有员工信息

sql SELECT * FROM employees;

二、条件语句 (WHERE/AND/OR)

查询工资大于7000的员工

sql SELECT * FROM employees where salary > 7000;

查询IT部门或工资低于6000的员工

sql SELECT * FROM employees WHERE department = 'IT' or salary < 6000;

查询2020年后入职且工资在6000-8000之间的员工

sql SELECT * FROM employees WHERE hire_date > TO_DATE('2020-01-01','YYYY-MM-DD') AND salary BETWEEN 6000 AND 8000;

三、模糊匹配和排序 (LIKE/ORDER BY)

查询姓名中包含"五"的员工（使用LIKE）

sql SELECT * FROM employees WHERE NAME LIKE '%五%'

查询所有员工，按工资降序排列

sql SELECT * FROM employees ORDER BY salary DESC;

按部门升序、工资降序双排序

sql SELECT * FROM employees ORDER BY department ASC, salary DESC;

四、常用函数

统计员工总数

sql SELECT COUNT(*) FROM employees;

计算公司每月工资总额

sql SELECT SUM(salary) FROM employees;

查询最高工资和最低工资

sql SELECT MAX(salary),MIN(salary) FROM employees;

显示每个员工的工龄（年）

sql SELECT name, TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date)/12) AS years FROM employees;

查询当前日期和时间

sql SELECT SYSDATE FROM DUAL;

五、多表连接

查询所有员工及其部门信息（INNER JOIN）

SELECT e.*, d.location FROM employees e JOIN departments d ON e.department = d.dept_name;

查询上海地区所有员工（使用部门表位置字段）

sql SELECT e.* FROM employees e JOIN DEPARTMENTS D on E.DEPARTMENT = d.dept_name WHERE d.location = '上海';

统计每个部门的平均工资（GROUP BY）

sql SELECT department, AVG(salary) FROM EMPLOYEES e GROUP BY department;

查询工资高于部门平均工资的员工

sql SELECT e.* FROM employees e JOIN ( SELECT department, AVG(salary) avg_sal FROM employees GROUP BY department ) d ON e.department = d.department WHERE e.salary > d.avg_sal;

显示没有员工的部门（LEFT JOIN）

SELECT d.dept_name 
FROM departments d 
LEFT JOIN employees e ON d.dept_name = e.department 
WHERE e.emp_id IS NULL;