如何为MySQL数据库添加习题？

MySQL数据库习题

一、基本概念与操作

1、创建数据库：创建一个名为student的数据库。

    CREATE DATABASE student;

2、查询所有学生信息：查询students表中所有学生的姓名和年龄。

    SELECT name, age FROM students;

3、更新学生信息：更新students表中姓名为“张三”的学生的年龄为23。

    UPDATE students SET age=23 WHERE name='张三';

4、删除学生记录：删除students表中年龄大于22的学生记录。

    DELETE FROM students WHERE age>22;

5、添加字段：在students表中添加一个字段class（字符串，非空），表示学生所在的班级。

    ALTER TABLE students ADD class VARCHAR(50) NOT NULL;

二、数据查询

1、查询学生信息：查询students表中所有学生的姓名、年龄和性别。

    SELECT name, age, gender FROM students;

2、条件查询：查询students表中年龄大于20的学生姓名和班级。

    SELECT name, class FROM students WHERE age>20;

3、分组统计：查询students表中男生和女生的数量。

    SELECT gender, COUNT(*) AS count FROM students GROUP BY gender;

4、范围查询：查询students表中年龄在20到22岁之间的学生信息。

    SELECT * FROM students WHERE age BETWEEN 20 AND 22;

5、聚合函数：查询students表中每个班级的平均年龄。

    SELECT class, AVG(age) AS average_age FROM students GROUP BY class;

6、排序查询：查询students表中年龄最大的学生信息。

    SELECT * FROM students ORDER BY age DESC LIMIT 1;

7、升序排列：查询students表中按年龄升序排列的学生信息。

    SELECT * FROM students ORDER BY age ASC;

三、复杂查询与函数

1、子查询：使用子查询查询students表中年龄大于平均年龄的学生信息。

    SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

2、连接查询：查询每个班级年龄最小的学生信息。

    SELECT s1.* FROM students s1
    JOIN (
        SELECT class, MIN(age) AS min_age FROM students GROUP BY class
    ) s2 ON s1.class = s2.class AND s1.age = s2.min_age;

3、联合查询：使用LEFT JOIN查询所有学生的姓名和班级名称（假设有一个班级表classes）。

    SELECT students.name, classes.class_name 
    FROM students 
    LEFT JOIN classes ON students.class = classes.id;

4、分组与过滤：使用GROUP BY和HAVING子句查询年龄大于20的学生所在班级的平均年龄。

    SELECT class, AVG(age) AS average_age 
    FROM students 
    WHERE age > 20 
    GROUP BY class;

5、表达式查询：使用CASE表达式查询students表中学生的姓名和性别（性别显示为“男”或“女”）。

    SELECT name, 
           CASE WHEN gender='M' THEN '男' 
                WHEN gender='F' THEN '女' 
                ELSE '未知' 
           END AS gender_display 
    FROM students;

6、计算总和：计算students表中学生的年龄之和。

    SELECT SUM(age) AS total_age FROM students;

7、视图：使用CREATE VIEW创建一个视图，显示students表中男生的姓名和年龄。

    CREATE VIEW male_students AS
    SELECT name, age FROM students WHERE gender='M';

四、索引与性能优化

1、创建索引：为students表的name字段创建一个索引。

    CREATE INDEX idx_name ON students(name);

2、查看索引使用情况：查看并分析students表的索引使用情况。

    EXPLAIN SELECT * FROM students WHERE name='张三';

3、删除索引：删除students表中多余的索引。

    DROP INDEX idx_name ON students;

4、唯一索引：为students表的age字段创建一个唯一索引。

    CREATE UNIQUE INDEX idx_age ON students(age);

5、修改字段类型：使用ALTER TABLE修改students表的class字段为VARCHAR(50)。

    ALTER TABLE students MODIFY class VARCHAR(50);

五、事务与存储过程

1、事务操作：使用事务插入一条学生记录，并在中途发生错误时回滚。

    START TRANSACTION;
    INSERT INTO students (name, age, gender, class) VALUES ('李四', 21, 'M', '计算机');
    -模拟错误
    -ROLLBACK; -取消注释以回滚事务
    COMMIT; -提交事务

2、创建存储过程：创建一个存储过程，用于查询指定班级的学生信息。

    DELIMITER //
    CREATE PROCEDURE GetStudentsByClass(IN class_name VARCHAR(50))
    BEGIN
        SELECT * FROM students WHERE class = class_name;
    END //
    DELIMITER ;

3、调用存储过程：调用一个存储过程，查看students表中学生的总数。

    CALL GetStudentCount();

4、异常处理：使用事务更新students表中学生的班级信息，并在更新过程中捕获异常。

    DELIMITER //
    CREATE PROCEDURE UpdateStudentClass(IN stud_name VARCHAR(50), IN new_class VARCHAR(50))
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
            -发生错误时回滚事务
            ROLLBACK;
        END;
        START TRANSACTION;
        UPDATE students SET class = new_class WHERE name = stud_name;
        COMMIT;
    END //
    DELIMITER ;

5、触发器：创建一个触发器，当删除students表中的学生记录时，自动在日志表中记录删除操作。

    DELIMITER //
    CREATE TRIGGER AfterStudentDelete
    AFTER DELETE ON students
    FOR EACH ROW
    BEGIN
        INSERT INTO deletion_log(student_name, delete_time) VALUES (OLD.name, NOW());
    END //
    DELIMITER ;

6、游标遍历：使用游标遍历students表中的所有记录，并输出学生的姓名和年龄。

    DELIMITER //
    CREATE PROCEDURE CursorExample()
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE stu_name VARCHAR(50);
        DECLARE stu_age INT;
        DECLARE cur CURSOR FOR SELECT name, age FROM students;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        OPEN cur;
        read_loop: LOOP
            FETCH cur INTO stu_name, stu_age;
            IF done THEN
                LEAVE read_loop;
            END IF;
            SELECT stu_name, stu_age; -这里可以替换成其他操作，例如插入到日志表等。
        END LOOP;
        CLOSE cur;
    END //
    DELIMITER ;

六、权限管理与安全性

1、创建用户并授予权限：创建一个新用户user1，并授予其对student数据库的所有权限。

    CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON student.* TO 'user1'@'localhost';
    FLUSH PRIVILEGES;

2、撤销权限：撤销用户user1对students表的删除权限。

    REVOKE DELETE ON students FROM 'user1'@'localhost';

3、查看用户权限：查看当前数据库的所有用户权限。

    SHOW GRANTS FOR 'user1'@'localhost';

4、设置用户密码及IP限制：设置用户user2的密码，并限制其只能从特定IP地址登录。

    ALTER USER 'user2'@'localhost' IDENTIFIED BY 'newpassword';
    UPDATE mysql.user SET host = 'specific_ip' WHERE user = 'user2' AND host = '%';
    FLUSH PRIVILEGES; -确保更改生效，注意，实际操作中请谨慎使用此方法。

5、默认值约束：为students表的age字段设置默认值为18。

    ALTER TABLE students ALTER COLUMN age SET DEFAULT 18;

6、字符集修改：修改数据库student的字符集为utf8mb4。

    ALTER DATABASE student CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

7、显示权限设置：显示当前数据库student的所有权限设置。

    SHOW GRANTS FOR 'root'@'localhost'; -根据需要替换为实际用户和主机名，注意，实际操作中请谨慎使用此方法。

七、备份与恢复

1、备份数据库：使用mysqldump工具备份student数据库，首先登录到MySQL命令行客户端，然后执行以下命令：

   mysqldump -u [username] -p[password] student > student_backup.sql

[username]是你的MySQL用户名，[password]是你的MySQL密码，如果密码中包含特殊字符或者你不想在命令行中明文输入密码，你可以只写-p，然后在提示符下输入密码，这个命令会将student数据库导出到一个名为student_backup.sql的文件中，如果你想要压缩备份文件，可以使用以下命令：

   mysqldump -u [username] -p[password] student | gzip > student_backup.sql.gz

这将生成一个名为student_backup.sql.gz的压缩文件，要恢复数据库，请先确保目标数据库不存在，如果存在则删除或重命名，然后使用以下命令：

   mysql -u [username] -p[password] student < student_backup.sql

对于压缩文件，先解压缩再恢复：

   gunzip < student_backup.sql.gz | mysql -u [username] -p[password] student