80 lines
2.9 KiB
SQL
80 lines
2.9 KiB
SQL
/*
|
||
Navicat Premium Data Transfer
|
||
|
||
Source Server : 106.54.193.225
|
||
Source Server Type : MySQL
|
||
Source Server Version : 50736
|
||
Source Host : 106.54.193.225:3306
|
||
Source Schema : day601rk
|
||
|
||
Target Server Type : MySQL
|
||
Target Server Version : 50736
|
||
File Encoding : 65001
|
||
|
||
Date: 18/07/2024 09:00:26
|
||
*/
|
||
|
||
SET NAMES utf8mb4;
|
||
SET FOREIGN_KEY_CHECKS = 0;
|
||
|
||
-- ----------------------------
|
||
-- Table structure for t_stu
|
||
-- ----------------------------
|
||
DROP TABLE IF EXISTS `t_stu`;
|
||
CREATE TABLE `t_stu` (
|
||
`stu_id` int(11) NOT NULL,
|
||
`stu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
|
||
`stu_sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
|
||
`stu_number` int(11) NULL DEFAULT NULL,
|
||
`clazz_id` int(11) NULL DEFAULT NULL,
|
||
`stu_age` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
|
||
`stu_sal` int(11) NULL DEFAULT NULL,
|
||
PRIMARY KEY (`stu_id`) USING BTREE
|
||
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
|
||
|
||
-- ----------------------------
|
||
-- Records of t_stu
|
||
-- ----------------------------
|
||
INSERT INTO `t_stu` VALUES (1, '张依依', '男', 123123, 1, '22', 200);
|
||
INSERT INTO `t_stu` VALUES (2, '刘晓红', '女', 456456, 20, '20', 7800);
|
||
INSERT INTO `t_stu` VALUES (3, '李四', '男', 13123, 1, '30', 4000);
|
||
INSERT INTO `t_stu` VALUES (4, '我开', '女', 546456, 30, '33', 3000);
|
||
INSERT INTO `t_stu` VALUES (5, '王凯', '男', 546456, 20, '21', 8000);
|
||
INSERT INTO `t_stu` VALUES (6, '刘德华', '男', 546211, 30, '22', 2000);
|
||
INSERT INTO `t_stu` VALUES (7, '陈乐基', '女', 333333, 20, '20', 9000);
|
||
INSERT INTO `t_stu` VALUES (10, '张依依', '男', 123123, 30, '10', 1000);
|
||
INSERT INTO `t_stu` VALUES (11, '刘玲玲', '男', 546456, 20, '21', 10000);
|
||
|
||
SET FOREIGN_KEY_CHECKS = 1;
|
||
|
||
|
||
SELECT * FROM t_stu ORDER BY stu_age desc
|
||
|
||
-- 2.查询所有学生信息，并根据年龄进行降序排序。（10分）
|
||
SELECT * FROM t_stu ORDER BY stu_age desc
|
||
-- 3.显示姓王的学生的详细信息。（10分）
|
||
|
||
SELECT * FROM t_stu WHERE stu_name like '%王%'
|
||
SELECT * FROM t_stu WHERE stu_name like '%李%'
|
||
|
||
|
||
-- 4.查询工资大于5000的所有男同学的信息。（10分）
|
||
|
||
SELECT * FROM t_stu WHERE stu_sal >5000 and stu_sex = '男'
|
||
|
||
-- 5.查询班级编号为20的男同学的电话号码。（10分）
|
||
SELECT stu_number FROM t_stu WHERE clazz_id = 20 and stu_sex = '男'
|
||
|
||
-- 6.查询最低工资。（10分）
|
||
SELECT * FROM t_stu ORDER BY stu_sal asc LIMIT 0,1
|
||
|
||
|
||
-- 7.查询班级为30的学生的最小年龄。（10分）
|
||
SELECT * FROM t_stu WHERE clazz_id =30 ORDER BY stu_age ASC LIMIT 0,1
|
||
|
||
-- 8.查询女生的最低工资。（15分）
|
||
SELECT stu_name,stu_sal FROM t_stu WHERE stu_sex='女' ORDER BY stu_sal ASC LIMIT 0,1
|
||
-- 9.查询女学生中薪资最低的人的全部信息。（15分）
|
||
|
||
SELECT * FROM t_stu WHERE stu_sex='女' ORDER BY stu_sal ASC LIMIT 0,1
|