DDL
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50731
Source Host : localhost:3306
Source Schema : schooldb
Target Server Type : MySQL
Target Server Version : 50731
File Encoding : 65001
Date: 24/05/2024 11:21:37
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`studentNo` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学号 主键',
`studentName` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`gender` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别',
`birthday` date NULL DEFAULT NULL COMMENT '出生日期',
`address` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '河北省石家庄市' COMMENT '地址 默认值',
`national` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '汉' COMMENT '民族 默认值',
`phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电话号码',
`classNo` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级编号',
PRIMARY KEY (`studentNo`) USING BTREE,
UNIQUE INDEX `index_phone`(`phone`) USING BTREE,
INDEX `班级编号`(`classNo`) USING BTREE,
INDEX `index_student_studentName`(`studentName`) USING BTREE,
INDEX `index_name_classNo`(`studentName`, `classNo`) USING BTREE,
UNIQUE INDEX `studentName666`(`studentName`) USING BTREE,
CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classNo`) REFERENCES `class` (`classNo`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('2020110103', '王一敏', '女', '2001-09-12', '河北', '汉', '19923454321', 'AC1301');
INSERT INTO `student` VALUES ('2020310102', '李怡然', '女', '2000-12-31', '', '汉', NULL, 'IS1401');
INSERT INTO `student` VALUES ('2020310103', '吴昊', '男', '2003-11-18', '河北', '汉', '13844566543', 'IS1401');
INSERT INTO `student` VALUES ('2020310105', '王林', '男', '2003-10-09', '河南', '汉', NULL, 'IS1401');
INSERT INTO `student` VALUES ('2020410001', '李牧', '男', '1998-10-21', '广东', '汉', NULL, NULL);
INSERT INTO `student` VALUES ('2021210101', '刘涛', '男', '2001-04-03', '湖南', '侗', NULL, 'CS1401');
INSERT INTO `student` VALUES ('2021210102', '郭志坚', '男', '2002-02-21', NULL, '汉', NULL, 'CS1401');
INSERT INTO `student` VALUES ('2021310102', '黄菊', '女', '2000-09-30', NULL, '汉', '13845675432', 'IS1301');
-- ----------------------------
-- Triggers structure for table student
-- ----------------------------
DROP TRIGGER IF EXISTS `t_delScoreOnDelStu`;
delimiter ;;
CREATE TRIGGER `t_delScoreOnDelStu` BEFORE DELETE ON `student` FOR EACH ROW begin
#删除成绩表的关联数据
delete from score where studentno=old.studentno;
end
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;
SQL
#基础查询
explain SELECT * from student;
#索引查询
explain SELECT * from student where studentName='王林';
#非索引查询
explain SELECT * from student where address='河北';
#主键查询
explain SELECT * from student where studentNo='2020310102';
#范围查询
explain SELECT * from student where studentNo>'2020310102';