-- 查询武力值比余沧海高的人
EXPLAIN SELECT
t_role. NAME,t_role.power
FROM
t_role
WHERE
t_role.power > (
SELECT
t_role.power
FROM
t_role
WHERE
t_role. NAME = "余沧海"
);
/*
Navicat MySQL Data Transfer
Source Server : 192.168.31.147
Source Server Version : 50639
Source Host : 192.168.31.147:3306
Source Database : dbtest
Target Server Type : MYSQL
Target Server Version : 50639
File Encoding : 65001
Date: 2019-06-19 16:29:28
*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for t_book-- ----------------------------DROPTABLEIFEXISTS`t_book`;CREATETABLE`t_book`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`name`varchar(20)NOTNULLCOMMENT'小说名称',`description`varchar(30)DEFAULTNULLCOMMENT'小说描述',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8 COMMENT='t_book 小说表';-- ------------------------------ Records of t_book-- ----------------------------INSERTINTO`t_book`VALUES('1','倚天屠龙记','射雕三部曲之三');INSERTINTO`t_book`VALUES('2','射雕英雄传','射雕三部曲之一');INSERTINTO`t_book`VALUES('3','神雕侠侣','射雕三部曲之二');INSERTINTO`t_book`VALUES('4','笑傲江湖','家喻户晓的武侠');INSERTINTO`t_book`VALUES('5','天龙八部','最好的武侠之一');-- ------------------------------ Table structure for t_gangs-- ----------------------------DROPTABLEIFEXISTS`t_gangs`;CREATETABLE`t_gangs`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`name`varchar(20)NOTNULL,`detail`varchar(20)DEFAULTNULLCOMMENT'帮派描述',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=10DEFAULTCHARSET=utf8 COMMENT='t_gangs 帮派表';-- ------------------------------ Records of t_gangs-- ----------------------------INSERTINTO`t_gangs`VALUES('1','武当派','位于武当山');INSERTINTO`t_gangs`VALUES('2','少林派','少室山');INSERTINTO`t_gangs`VALUES('3','峨眉派','峨眉山');INSERTINTO`t_gangs`VALUES('4','全真教','活死人墓对面');INSERTINTO`t_gangs`VALUES('5','华山派','位于华山');INSERTINTO`t_gangs`VALUES('6','青城派','四川青城山');INSERTINTO`t_gangs`VALUES('7','嵩山派','五岳剑派之首');INSERTINTO`t_gangs`VALUES('8','丐帮','天下第一大帮');INSERTINTO`t_gangs`VALUES('9','大理段氏','云南大理');-- ------------------------------ Table structure for t_role-- ----------------------------DROPTABLEIFEXISTS`t_role`;CREATETABLE`t_role`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`name`varchar(10)NOTNULLCOMMENT'姓名',`age`int(3)DEFAULT'0'COMMENT'年龄',`power`int(3)DEFAULT'0'COMMENT'武力',`spirit`int(3)DEFAULT'0'COMMENT'精神',`bookId`bigint(20)NOTNULLCOMMENT'关联的书本id',`gangsId`bigint(20)NOTNULLCOMMENT'关联的帮派id',PRIMARYKEY(`id`),KEY`bookId`(`bookId`),KEY`gangsId`(`gangsId`),KEY`name_power`(`name`,`power`),KEY`power`(`power`),CONSTRAINT`t_role_ibfk_1`FOREIGNKEY(`bookId`)REFERENCES`t_book`(`id`),CONSTRAINT`t_role_ibfk_2`FOREIGNKEY(`gangsId`)REFERENCES`t_gangs`(`id`))ENGINE=InnoDBAUTO_INCREMENT=14DEFAULTCHARSET=utf8 COMMENT='t_role 角色表';-- ------------------------------ Records of t_role-- ----------------------------INSERTINTO`t_role`VALUES('1','张无忌','20','98','90','1','1');INSERTINTO`t_role`VALUES('2','张三丰','110','99','95','1','1');INSERTINTO`t_role`VALUES('3','成昆','50','88','90','1','2');INSERTINTO`t_role`VALUES('4','灭绝师太','48','85','90','1','3');INSERTINTO`t_role`VALUES('5','王重阳','55','97','95','2','4');INSERTINTO`t_role`VALUES('6','尹志平','44','78','75','3','4');INSERTINTO`t_role`VALUES('7','令狐冲','23','92','90','4','5');INSERTINTO`t_role`VALUES('8','风清扬','90','98','95','4','5');INSERTINTO`t_role`VALUES('9','余沧海','46','82','80','4','6');INSERTINTO`t_role`VALUES('10','岳不群','48','86','88','4','5');INSERTINTO`t_role`VALUES('11','左冷禅','50','91','88','4','7');INSERTINTO`t_role`VALUES('12','乔峰','30','93','90','5','8');INSERTINTO`t_role`VALUES('13','段誉','20','94','88','5','9');-- ------------------------------ Procedure structure for testtt_insert-- ----------------------------DROPPROCEDUREIFEXISTS`testtt_insert`;DELIMITER;;CREATEDEFINER=`root`@`%`PROCEDURE`testtt_insert`()BEGINDECLARE i INTDEFAULT1;while i<10000doinsertinto t_test(name,description)values(concat('name',i),CONCAT('description',i));set i = i+1;endwhile;commit;end;;DELIMITER;