MySQL练习题(5)

**MySQL 练习题 (5)**### 一、创建表格并插入数据####1. 创建一个名为 `student` 的表格，包含以下字段：

| 字段名称 | 数据类型 |
| --- | --- |
| id | int |
| name | varchar(20) |
| age | tinyint |
| sex | enum('男','女') |
| score | decimal(3,2) |

####2. 插入5 条数据到 `student` 表格中：

| id | name | age | sex | score |
| --- | --- | --- | --- | --- |
|1 | 张三 |18 | 男 |85.50 |
|2 | 李四 |19 | 女 |90.25 |
|3 | 王五 |20 | 男 |78.75 |
|4 | 小红 |17 | 女 |92.00 |
|5 | 老王 |21 | 男 |88.50 |

####3. 使用 SQL 查询语句，找出所有年龄大于18 的学生。

sqlSELECT * FROM student WHERE age >18;

### 二、SQL 查询####1. 找出 `student` 表格中所有女生的姓名和年龄。

sqlSELECT name, age FROM student WHERE sex = '女';

####2. 使用 SQL 查询语句，找出所有学生的平均分数。

sqlSELECT AVG(score) AS avg_score FROM student;

####3. 找出 `student` 表格中所有年龄小于18 的学生的姓名和年龄。

sqlSELECT name, age FROM student WHERE age < 18;

### 三、SQL 运算符####1. 使用 SQL 运算符，找出所有年龄大于或等于18 的学生。

sqlSELECT * FROM student WHERE age >=18;

####2. 找出 `student` 表格中所有分数大于90 的学生的姓名和分数。

sqlSELECT name, score FROM student WHERE score >90;

####3. 使用 SQL 运算符，找出所有年龄小于或等于18 的学生。

sqlSELECT * FROM student WHERE age <=18;

### 四、SQL 函数####1. 找出 `student` 表格中所有学生的姓名和年龄，年龄加上2。

sqlSELECT name, age +2 AS new_age FROM student;

####2. 使用 SQL 函数，找出所有学生的平均分数。

sqlSELECT AVG(score) AS avg_score FROM student;

####3. 找出 `student` 表格中所有学生的姓名和年龄，年龄减去1。

sqlSELECT name, age -1 AS new_age FROM student;

### 五、SQL 子查询####1. 使用 SQL 子查询，找出所有年龄大于18 的学生。

sqlSELECT * FROM student WHERE id IN (SELECT id FROM student WHERE age >18);

####2. 找出 `student` 表格中所有分数大于90 的学生的姓名和分数。

sqlSELECT name, score FROM student WHERE id IN (SELECT id FROM student WHERE score >90);

####3. 使用 SQL 子查询，找出所有年龄小于或等于18 的学生。

sqlSELECT * FROM student WHERE id IN (SELECT id FROM student WHERE age <=18);

### 六、SQL 联合查询####1. 找出 `student` 表格中所有学生的姓名和年龄，年龄加上2。

sqlSELECT name, age +2 AS new_age FROM student;

####2. 使用 SQL 联合查询，找出所有学生的平均分数。

sqlSELECT AVG(score) AS avg_score FROM student UNION SELECT AVG(score) AS avg_score FROM student;

####3. 找出 `student` 表格中所有学生的姓名和年龄，年龄减去1。

sqlSELECT name, age -1 AS new_age FROM student;

### 七、SQL 分组查询####1. 使用 SQL 分组查询，找出每个年龄段的平均分数。

sqlSELECT AVG(score) AS avg_score, age FROM student GROUP BY age;

####2. 找出 `student` 表格中每个性别的平均分数。

sqlSELECT AVG(score) AS avg_score, sex FROM student GROUP BY sex;

####3. 使用 SQL 分组查询，找出每个年龄段的学生人数。

sqlSELECT COUNT(*) AS count, age FROM student GROUP BY age;

### 八、SQL 排序和限制####1. 找出 `student` 表格中所有学生的姓名和年龄，按年龄降序排列。

sqlSELECT name, age FROM student ORDER BY age DESC;

####2. 使用 SQL 排序和限制，找出前5 名学生的姓名和分数。

sqlSELECT name, score FROM student ORDER BY score DESC LIMIT5;

####3. 找出 `student` 表格中所有学生的姓名和年龄，按年龄升序排列。

sqlSELECT name, age FROM student ORDER BY age ASC;

### 九、SQL 分页####1. 使用 SQL 分页，找出第一页的学生信息。

sqlSELECT * FROM student LIMIT10 OFFSET0;

####2. 找出 `student` 表格中所有学生的姓名和年龄，按年龄降序排列。

sqlSELECT name, age FROM student ORDER BY age DESC;

####3. 使用 SQL 分页，找出第5页的学生信息。

sqlSELECT * FROM student LIMIT10 OFFSET40;

### 十、SQL 索引####1. 为 `student` 表格创建一个索引。

sqlCREATE INDEX idx_student_age ON student (age);

####2. 使用 SQL 索引，找出所有年龄大于18 的学生。

sqlSELECT * FROM student WHERE age >18;

####3. 为 `student` 表格创建一个联合索引。

sqlCREATE INDEX idx_student_age_sex ON student (age, sex);

####4. 使用 SQL 索引，找出所有年龄小于或等于18 的学生。

sqlSELECT * FROM student WHERE age <=18;

### 十一、SQL 视图####1. 为 `student` 表格创建一个视图。

sqlCREATE VIEW view_student AS SELECT * FROM student;

####2. 使用 SQL 视图，找出所有学生的姓名和年龄。

sqlSELECT name, age FROM view_student;

####3. 为 `student` 表格创建一个联合视图。

sqlCREATE VIEW view_student_age_sex AS SELECT name, age, sex FROM student;

####4. 使用 SQL 视图，找出所有学生的姓名和年龄，按年龄降序排列。

sqlSELECT name, age FROM view_student ORDER BY age DESC;

### 十二、SQL 存储过程####1. 为 `student` 表格创建一个存储过程。

sqlDELIMITER //
CREATE PROCEDURE sp_get_student_info()
BEGIN SELECT * FROM student;
END//
DELIMITER ;

####2. 使用 SQL 存储过程，找出所有学生的姓名和年龄。

sqlCALL sp_get_student_info();

####3. 为 `student` 表格创建一个联合存储过程。

sqlDELIMITER //
CREATE PROCEDURE sp_get_student_age_sex()
BEGIN SELECT name, age, sex FROM student;
END//
DELIMITER ;

####4. 使用 SQL 存储过程，找出所有学生的姓名和年龄，按年龄降序排列。

sqlCALL sp_get_student_age_sex();

### 十三、SQL 触发器####1. 为 `student` 表格创建一个触发器。

sqlDELIMITER //
CREATE TRIGGER trig_before_insert BEFORE INSERT ON student FOR EACH ROWBEGIN SET NEW.age = IF(NEW.age < 18,18, NEW.age);
END//
DELIMITER ;

####2. 使用 SQL 触发器，插入一个学生的信息。

sqlINSERT INTO student (name, age) VALUES ('张三',17);

####3. 为 `student` 表格创建一个联合触发器。

sqlDELIMITER //
CREATE TRIGGER trig_before_update BEFORE UPDATE ON student FOR EACH ROWBEGIN SET NEW.age = IF(NEW.age < 18,18, NEW.age);
END//
DELIMITER ;

####4. 使用 SQL 触发器，更新一个学生的信息。

sqlUPDATE student SET age =17 WHERE name = '张三';