MySQL数据库EXPLAIN查询

最新推荐文章于 2024-07-16 10:28:31 发布

NBCjj

最新推荐文章于 2024-07-16 10:28:31 发布

阅读量121

点赞数 2

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/NBCjj/article/details/139170514

版权

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';