MySQL笔记
点击查看练习表
/*
Navicat MySQL Data Transfer
Source Server : 3306
Source Server Version : 50519
Source Host : localhost:3306
Source Database : myschool1
Target Server Type : MYSQL
Target Server Version : 50519
File Encoding : 65001
Date: 2022-02-26 16:37:12
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
`GradeID` int(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
`GradeName` varchar(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`GradeID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of grade
-- ----------------------------
INSERT INTO `grade` VALUES ('1', '大一');
INSERT INTO `grade` VALUES ('2', '大二');
INSERT INTO `grade` VALUES ('3', '大三');
INSERT INTO `grade` VALUES ('4', '大四');
INSERT INTO `grade` VALUES ('5', '预科班');
-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`StudentNo` int(4) NOT NULL COMMENT '学号',
`SubjectNo` int(4) NOT NULL COMMENT '课程编号',
`ExamDate` datetime NOT NULL COMMENT '考试日期',
`StudentResult` int(4) NOT NULL COMMENT '考试成绩',
KEY `SubjectNo` (`SubjectNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of result
-- ----------------------------
INSERT INTO `result` VALUES ('1000', '1', '2013-11-11 16:00:00', '94');
INSERT INTO `result` VALUES ('1000', '2', '2012-11-10 10:00:00', '75');
INSERT INTO `result` VALUES ('1000', '3', '2011-12-19 10:00:00', '76');
INSERT INTO `result` VALUES ('1000', '4', '2010-11-18 11:00:00', '93');
INSERT INTO `result` VALUES ('1000', '5', '2013-11-11 14:00:00', '97');
INSERT INTO `result` VALUES ('1000', '6', '2012-09-13 15:00:00', '87');
INSERT INTO `result` VALUES ('1000', '7', '2011-10-16 16:00:00', '79');
INSERT INTO `result` VALUES ('1000', '8', '2010-11-11 16:00:00', '74');
INSERT INTO `result` VALUES ('1000', '9', '2013-11-21 10:00:00', '69');
INSERT INTO `result` VALUES ('1000', '10', '2012-11-11 12:00:00', '78');
INSERT INTO `result` VALUES ('1000', '11', '2011-11-11 14:00:00', '66');
INSERT INTO `result` VALUES ('1000', '12', '2010-11-11 15:00:00', '82');
INSERT INTO `result` VALUES ('1000', '13', '2013-11-11 14:00:00', '94');
INSERT INTO `result` VALUES ('1000', '14', '2012-11-11 15:00:00', '98');
INSERT INTO `result` VALUES ('1000', '15', '2011-12-11 10:00:00', '70');
INSERT INTO `result` VALUES ('1000', '16', '2010-09-11 10:00:00', '74');
INSERT INTO `result` VALUES ('1001', '1', '2013-11-11 16:00:00', '76');
INSERT INTO `result` VALUES ('1001', '2', '2012-11-10 10:00:00', '93');
INSERT INTO `result` VALUES ('1001', '3', '2011-12-19 10:00:00', '65');
INSERT INTO `result` VALUES ('1001', '4', '2010-11-18 11:00:00', '71');
INSERT INTO `result` VALUES ('1001', '5', '2013-11-11 14:00:00', '98');
INSERT INTO `result` VALUES ('1001', '6', '2012-09-13 15:00:00', '74');
INSERT INTO `result` VALUES ('1001', '7', '2011-10-16 16:00:00', '85');
INSERT INTO `result` VALUES ('1001', '8', '2010-11-11 16:00:00', '69');
INSERT INTO `result` VALUES ('1001', '9', '2013-11-21 10:00:00', '63');
INSERT INTO `result` VALUES ('1001', '10', '2012-11-11 12:00:00', '70');
INSERT INTO `result` VALUES ('1001', '11', '2011-11-11 14:00:00', '62');
INSERT INTO `result` VALUES ('1001', '12', '2010-11-11 15:00:00', '90');
INSERT INTO `result` VALUES ('1001', '13', '2013-11-11 14:00:00', '97');
INSERT INTO `result` VALUES ('1001', '14', '2012-11-11 15:00:00', '89');
INSERT INTO `result` VALUES ('1001', '15', '2011-12-11 10:00:00', '72');
INSERT INTO `result` VALUES ('1001', '16', '2010-09-11 10:00:00', '90');
INSERT INTO `result` VALUES ('1002', '1', '2013-11-11 16:00:00', '61');
INSERT INTO `result` VALUES ('1002', '2', '2012-11-10 10:00:00', '80');
INSERT INTO `result` VALUES ('1002', '3', '2011-12-19 10:00:00', '89');
INSERT INTO `result` VALUES ('1002', '4', '2010-11-18 11:00:00', '88');
INSERT INTO `result` VALUES ('1002', '5', '2013-11-11 14:00:00', '82');
INSERT INTO `result` VALUES ('1002', '6', '2012-09-13 15:00:00', '91');
INSERT INTO `result` VALUES ('1002', '7', '2011-10-16 16:00:00', '63');
INSERT INTO `result` VALUES ('1002', '8', '2010-11-11 16:00:00', '84');
INSERT INTO `result` VALUES ('1002', '9', '2013-11-21 10:00:00', '60');
INSERT INTO `result` VALUES ('1002', '10', '2012-11-11 12:00:00', '71');
INSERT INTO `result` VALUES ('1002', '11', '2011-11-11 14:00:00', '93');
INSERT INTO `result` VALUES ('1002', '12', '2010-11-11 15:00:00', '96');
INSERT INTO `result` VALUES ('1002', '13', '2013-11-11 14:00:00', '83');
INSERT INTO `result` VALUES ('1002', '14', '2012-11-11 15:00:00', '69');
INSERT INTO `result` VALUES ('1002', '15', '2011-12-11 10:00:00', '89');
INSERT INTO `result` VALUES ('1002', '16', '2010-09-11 10:00:00', '83');
INSERT INTO `result` VALUES ('1003', '1', '2013-11-11 16:00:00', '91');
INSERT INTO `result` VALUES ('1003', '2', '2012-11-10 10:00:00', '75');
INSERT INTO `result` VALUES ('1003', '3', '2011-12-19 10:00:00', '65');
INSERT INTO `result` VALUES ('1003', '4', '2010-11-18 11:00:00', '63');
INSERT INTO `result` VALUES ('1003', '5', '2013-11-11 14:00:00', '90');
INSERT INTO `result` VALUES ('1003', '6', '2012-09-13 15:00:00', '96');
INSERT INTO `result` VALUES ('1003', '7', '2011-10-16 16:00:00', '97');
INSERT INTO `result` VALUES ('1003', '8', '2010-11-11 16:00:00', '77');
INSERT INTO `result` VALUES ('1003', '9', '2013-11-21 10:00:00', '62');
INSERT INTO `result` VALUES ('1003', '10', '2012-11-11 12:00:00', '81');
INSERT INTO `result` VALUES ('1003', '11', '2011-11-11 14:00:00', '76');
INSERT INTO `result` VALUES ('1003', '12', '2010-11-11 15:00:00', '61');
INSERT INTO `result` VALUES ('1003', '13', '2013-11-11 14:00:00', '93');
INSERT INTO `result` VALUES ('1003', '14', '2012-11-11 15:00:00', '79');
INSERT INTO `result` VALUES ('1003', '15', '2011-12-11 10:00:00', '78');
INSERT INTO `result` VALUES ('1003', '16', '2010-09-11 10:00:00', '96');
INSERT INTO `result` VALUES ('1004', '1', '2013-11-11 16:00:00', '84');
INSERT INTO `result` VALUES ('1004', '2', '2012-11-10 10:00:00', '79');
INSERT INTO `result` VALUES ('1004', '3', '2011-12-19 10:00:00', '76');
INSERT INTO `result` VALUES ('1004', '4', '2010-11-18 11:00:00', '78');
INSERT INTO `result` VALUES ('1004', '5', '2013-11-11 14:00:00', '81');
INSERT INTO `result` VALUES ('1004', '6', '2012-09-13 15:00:00', '90');
INSERT INTO `result` VALUES ('1004', '7', '2011-10-16 16:00:00', '63');
INSERT INTO `result` VALUES ('1004', '8', '2010-11-11 16:00:00', '89');
INSERT INTO `result` VALUES ('1004', '9', '2013-11-21 10:00:00', '67');
INSERT INTO `result` VALUES ('1004', '10', '2012-11-11 12:00:00', '100');
INSERT INTO `result` VALUES ('1004', '11', '2011-11-11 14:00:00', '94');
INSERT INTO `result` VALUES ('1004', '12', '2010-11-11 15:00:00', '65');
INSERT INTO `result` VALUES ('1004', '13', '2013-11-11 14:00:00', '86');
INSERT INTO `result` VALUES ('1004', '14', '2012-11-11 15:00:00', '77');
INSERT INTO `result` VALUES ('1004', '15', '2011-12-11 10:00:00', '82');
INSERT INTO `result` VALUES ('1004', '16', '2010-09-11 10:00:00', '87');
INSERT INTO `result` VALUES ('1005', '1', '2013-11-11 16:00:00', '82');
INSERT INTO `result` VALUES ('1005', '2', '2012-11-10 10:00:00', '92');
INSERT INTO `result` VALUES ('1005', '3', '2011-12-19 10:00:00', '80');
INSERT INTO `result` VALUES ('1005', '4', '2010-11-18 11:00:00', '92');
INSERT INTO `result` VALUES ('1005', '5', '2013-11-11 14:00:00', '97');
INSERT INTO `result` VALUES ('1005', '6', '2012-09-13 15:00:00', '72');
INSERT INTO `result` VALUES ('1005', '7', '2011-10-16 16:00:00', '84');
INSERT INTO `result` VALUES ('1005', '8', '2010-11-11 16:00:00', '79');
INSERT INTO `result` VALUES ('1005', '9', '2013-11-21 10:00:00', '76');
INSERT INTO `result` VALUES ('1005', '10', '2012-11-11 12:00:00', '87');
INSERT INTO `result` VALUES ('1005', '11', '2011-11-11 14:00:00', '65');
INSERT INTO `result` VALUES ('1005', '12', '2010-11-11 15:00:00', '67');
INSERT INTO `result` VALUES ('1005', '13', '2013-11-11 14:00:00', '63');
INSERT INTO `result` VALUES ('1005', '14', '2012-11-11 15:00:00', '64');
INSERT INTO `result` VALUES ('1005', '15', '2011-12-11 10:00:00', '99');
INSERT INTO `result` VALUES ('1005', '16', '2010-09-11 10:00:00', '97');
INSERT INTO `result` VALUES ('1006', '1', '2013-11-11 16:00:00', '82');
INSERT INTO `result` VALUES ('1006', '2', '2012-11-10 10:00:00', '73');
INSERT INTO `result` VALUES ('1006', '3', '2011-12-19 10:00:00', '79');
INSERT INTO `result` VALUES ('1006', '4', '2010-11-18 11:00:00', '63');
INSERT INTO `result` VALUES ('1006', '5', '2013-11-11 14:00:00', '97');
INSERT INTO `result` VALUES ('1006', '6', '2012-09-13 15:00:00', '83');
INSERT INTO `result` VALUES ('1006', '7', '2011-10-16 16:00:00', '78');
INSERT INTO `result` VALUES ('1006', '8', '2010-11-11 16:00:00', '88');
INSERT INTO `result` VALUES ('1006', '9', '2013-11-21 10:00:00', '89');
INSERT INTO `result` VALUES ('1006', '10', '2012-11-11 12:00:00', '82');
INSERT INTO `result` VALUES ('1006', '11', '2011-11-11 14:00:00', '70');
INSERT INTO `result` VALUES ('1006', '12', '2010-11-11 15:00:00', '69');
INSERT INTO `result` VALUES ('1006', '13', '2013-11-11 14:00:00', '64');
INSERT INTO `result` VALUES ('1006', '14', '2012-11-11 15:00:00', '80');
INSERT INTO `result` VALUES ('1006', '15', '2011-12-11 10:00:00', '90');
INSERT INTO `result` VALUES ('1006', '16', '2010-09-11 10:00:00', '85');
INSERT INTO `result` VALUES ('1007', '1', '2013-11-11 16:00:00', '87');
INSERT INTO `result` VALUES ('1007', '2', '2012-11-10 10:00:00', '63');
INSERT INTO `result` VALUES ('1007', '3', '2011-12-19 10:00:00', '70');
INSERT INTO `result` VALUES ('1007', '4', '2010-11-18 11:00:00', '74');
INSERT INTO `result` VALUES ('1007', '5', '2013-11-11 14:00:00', '79');
INSERT INTO `result` VALUES ('1007', '6', '2012-09-13 15:00:00', '83');
INSERT INTO `result` VALUES ('1007', '7', '2011-10-16 16:00:00', '86');
INSERT INTO `result` VALUES ('1007', '8', '2010-11-11 16:00:00', '76');
INSERT INTO `result` VALUES ('1007', '9', '2013-11-21 10:00:00', '65');
INSERT INTO `result` VALUES ('1007', '10', '2012-11-11 12:00:00', '87');
INSERT INTO `result` VALUES ('1007', '11', '2011-11-11 14:00:00', '69');
INSERT INTO `result` VALUES ('1007', '12', '2010-11-11 15:00:00', '69');
INSERT INTO `result` VALUES ('1007', '13', '2013-11-11 14:00:00', '90');
INSERT INTO `result` VALUES ('1007', '14', '2012-11-11 15:00:00', '84');
INSERT INTO `result` VALUES ('1007', '15', '2011-12-11 10:00:00', '95');
INSERT INTO `result` VALUES ('1007', '16', '2010-09-11 10:00:00', '92');
INSERT INTO `result` VALUES ('1008', '1', '2013-11-11 16:00:00', '96');
INSERT INTO `result` VALUES ('1008', '2', '2012-11-10 10:00:00', '62');
INSERT INTO `result` VALUES ('1008', '3', '2011-12-19 10:00:00', '97');
INSERT INTO `result` VALUES ('1008', '4', '2010-11-18 11:00:00', '84');
INSERT INTO `result` VALUES ('1008', '5', '2013-11-11 14:00:00', '86');
INSERT INTO `result` VALUES ('1008', '6', '2012-09-13 15:00:00', '72');
INSERT INTO `result` VALUES ('1008', '7', '2011-10-16 16:00:00', '67');
INSERT INTO `result` VALUES ('1008', '8', '2010-11-11 16:00:00', '83');
INSERT INTO `result` VALUES ('1008', '9', '2013-11-21 10:00:00', '86');
INSERT INTO `result` VALUES ('1008', '10', '2012-11-11 12:00:00', '60');
INSERT INTO `result` VALUES ('1008', '11', '2011-11-11 14:00:00', '61');
INSERT INTO `result` VALUES ('1008', '12', '2010-11-11 15:00:00', '68');
INSERT INTO `result` VALUES ('1008', '13', '2013-11-11 14:00:00', '99');
INSERT INTO `result` VALUES ('1008', '14', '2012-11-11 15:00:00', '77');
INSERT INTO `result` VALUES ('1008', '15', '2011-12-11 10:00:00', '73');
INSERT INTO `result` VALUES ('1008', '16', '2010-09-11 10:00:00', '78');
INSERT INTO `result` VALUES ('1009', '1', '2013-11-11 16:00:00', '67');
INSERT INTO `result` VALUES ('1009', '2', '2012-11-10 10:00:00', '70');
INSERT INTO `result` VALUES ('1009', '3', '2011-12-19 10:00:00', '75');
INSERT INTO `result` VALUES ('1009', '4', '2010-11-18 11:00:00', '92');
INSERT INTO `result` VALUES ('1009', '5', '2013-11-11 14:00:00', '76');
INSERT INTO `result` VALUES ('1009', '6', '2012-09-13 15:00:00', '90');
INSERT INTO `result` VALUES ('1009', '7', '2011-10-16 16:00:00', '62');
INSERT INTO `result` VALUES ('1009', '8', '2010-11-11 16:00:00', '68');
INSERT INTO `result` VALUES ('1009', '9', '2013-11-21 10:00:00', '70');
INSERT INTO `result` VALUES ('1009', '10', '2012-11-11 12:00:00', '83');
INSERT INTO `result` VALUES ('1009', '11', '2011-11-11 14:00:00', '88');
INSERT INTO `result` VALUES ('1009', '12', '2010-11-11 15:00:00', '65');
INSERT INTO `result` VALUES ('1009', '13', '2013-11-11 14:00:00', '91');
INSERT INTO `result` VALUES ('1009', '14', '2012-11-11 15:00:00', '99');
INSERT INTO `result` VALUES ('1009', '15', '2011-12-11 10:00:00', '65');
INSERT INTO `result` VALUES ('1009', '16', '2010-09-11 10:00:00', '83');
INSERT INTO `result` VALUES ('1010', '1', '2013-11-11 16:00:00', '83');
INSERT INTO `result` VALUES ('1010', '2', '2012-11-10 10:00:00', '87');
INSERT INTO `result` VALUES ('1010', '3', '2011-12-19 10:00:00', '89');
INSERT INTO `result` VALUES ('1010', '4', '2010-11-18 11:00:00', '99');
INSERT INTO `result` VALUES ('1010', '5', '2013-11-11 14:00:00', '91');
INSERT INTO `result` VALUES ('1010', '6', '2012-09-13 15:00:00', '96');
INSERT INTO `result` VALUES ('1010', '7', '2011-10-16 16:00:00', '72');
INSERT INTO `result` VALUES ('1010', '8', '2010-11-11 16:00:00', '72');
INSERT INTO `result` VALUES ('1010', '9', '2013-11-21 10:00:00', '98');
INSERT INTO `result` VALUES ('1010', '10', '2012-11-11 12:00:00', '73');
INSERT INTO `result` VALUES ('1010', '11', '2011-11-11 14:00:00', '68');
INSERT INTO `result` VALUES ('1010', '12', '2010-11-11 15:00:00', '62');
INSERT INTO `result` VALUES ('1010', '13', '2013-11-11 14:00:00', '67');
INSERT INTO `result` VALUES ('1010', '14', '2012-11-11 15:00:00', '69');
INSERT INTO `result` VALUES ('1010', '15', '2011-12-11 10:00:00', '71');
INSERT INTO `result` VALUES ('1010', '16', '2010-09-11 10:00:00', '66');
INSERT INTO `result` VALUES ('1011', '1', '2013-11-11 16:00:00', '62');
INSERT INTO `result` VALUES ('1011', '2', '2012-11-10 10:00:00', '72');
INSERT INTO `result` VALUES ('1011', '3', '2011-12-19 10:00:00', '96');
INSERT INTO `result` VALUES ('1011', '4', '2010-11-18 11:00:00', '64');
INSERT INTO `result` VALUES ('1011', '5', '2013-11-11 14:00:00', '89');
INSERT INTO `result` VALUES ('1011', '6', '2012-09-13 15:00:00', '91');
INSERT INTO `result` VALUES ('1011', '7', '2011-10-16 16:00:00', '95');
INSERT INTO `result` VALUES ('1011', '8', '2010-11-11 16:00:00', '96');
INSERT INTO `result` VALUES ('1011', '9', '2013-11-21 10:00:00', '89');
INSERT INTO `result` VALUES ('1011', '10', '2012-11-11 12:00:00', '73');
INSERT INTO `result` VALUES ('1011', '11', '2011-11-11 14:00:00', '82');
INSERT INTO `result` VALUES ('1011', '12', '2010-11-11 15:00:00', '98');
INSERT INTO `result` VALUES ('1011', '13', '2013-11-11 14:00:00', '66');
INSERT INTO `result` VALUES ('1011', '14', '2012-11-11 15:00:00', '69');
INSERT INTO `result` VALUES ('1011', '15', '2011-12-11 10:00:00', '91');
INSERT INTO `result` VALUES ('1011', '16', '2010-09-11 10:00:00', '69');
INSERT INTO `result` VALUES ('1012', '1', '2013-11-11 16:00:00', '86');
INSERT INTO `result` VALUES ('1012', '2', '2012-11-10 10:00:00', '66');
INSERT INTO `result` VALUES ('1012', '3', '2011-12-19 10:00:00', '97');
INSERT INTO `result` VALUES ('1012', '4', '2010-11-18 11:00:00', '69');
INSERT INTO `result` VALUES ('1012', '5', '2013-11-11 14:00:00', '70');
INSERT INTO `result` VALUES ('1012', '6', '2012-09-13 15:00:00', '74');
INSERT INTO `result` VALUES ('1012', '7', '2011-10-16 16:00:00', '91');
INSERT INTO `result` VALUES ('1012', '8', '2010-11-11 16:00:00', '97');
INSERT INTO `result` VALUES ('1012', '9', '2013-11-21 10:00:00', '84');
INSERT INTO `result` VALUES ('1012', '10', '2012-11-11 12:00:00', '82');
INSERT INTO `result` VALUES ('1012', '11', '2011-11-11 14:00:00', '90');
INSERT INTO `result` VALUES ('1012', '12', '2010-11-11 15:00:00', '91');
INSERT INTO `result` VALUES ('1012', '13', '2013-11-11 14:00:00', '91');
INSERT INTO `result` VALUES ('1012', '14', '2012-11-11 15:00:00', '97');
INSERT INTO `result` VALUES ('1012', '15', '2011-12-11 10:00:00', '85');
INSERT INTO `result` VALUES ('1012', '16', '2010-09-11 10:00:00', '90');
INSERT INTO `result` VALUES ('1013', '1', '2013-11-11 16:00:00', '73');
INSERT INTO `result` VALUES ('1013', '2', '2012-11-10 10:00:00', '69');
INSERT INTO `result` VALUES ('1013', '3', '2011-12-19 10:00:00', '91');
INSERT INTO `result` VALUES ('1013', '4', '2010-11-18 11:00:00', '72');
INSERT INTO `result` VALUES ('1013', '5', '2013-11-11 14:00:00', '76');
INSERT INTO `result` VALUES ('1013', '6', '2012-09-13 15:00:00', '87');
INSERT INTO `result` VALUES ('1013', '7', '2011-10-16 16:00:00', '61');
INSERT INTO `result` VALUES ('1013', '8', '2010-11-11 16:00:00', '77');
INSERT INTO `result` VALUES ('1013', '9', '2013-11-21 10:00:00', '83');
INSERT INTO `result` VALUES ('1013', '10', '2012-11-11 12:00:00', '99');
INSERT INTO `result` VALUES ('1013', '11', '2011-11-11 14:00:00', '91');
INSERT INTO `result` VALUES ('1013', '12', '2010-11-11 15:00:00', '84');
INSERT INTO `result` VALUES ('1013', '13', '2013-11-11 14:00:00', '98');
INSERT INTO `result` VALUES ('1013', '14', '2012-11-11 15:00:00', '74');
INSERT INTO `result` VALUES ('1013', '15', '2011-12-11 10:00:00', '92');
INSERT INTO `result` VALUES ('1013', '16', '2010-09-11 10:00:00', '90');
INSERT INTO `result` VALUES ('1014', '1', '2013-11-11 16:00:00', '64');
INSERT INTO `result` VALUES ('1014', '2', '2012-11-10 10:00:00', '81');
INSERT INTO `result` VALUES ('1014', '3', '2011-12-19 10:00:00', '79');
INSERT INTO `result` VALUES ('1014', '4', '2010-11-18 11:00:00', '74');
INSERT INTO `result` VALUES ('1014', '5', '2013-11-11 14:00:00', '65');
INSERT INTO `result` VALUES ('1014', '6', '2012-09-13 15:00:00', '88');
INSERT INTO `result` VALUES ('1014', '7', '2011-10-16 16:00:00', '86');
INSERT INTO `result` VALUES ('1014', '8', '2010-11-11 16:00:00', '77');
INSERT INTO `result` VALUES ('1014', '9', '2013-11-21 10:00:00', '86');
INSERT INTO `result` VALUES ('1014', '10', '2012-11-11 12:00:00', '85');
INSERT INTO `result` VALUES ('1014', '11', '2011-11-11 14:00:00', '86');
INSERT INTO `result` VALUES ('1014', '12', '2010-11-11 15:00:00', '75');
INSERT INTO `result` VALUES ('1014', '13', '2013-11-11 14:00:00', '89');
INSERT INTO `result` VALUES ('1014', '14', '2012-11-11 15:00:00', '79');
INSERT INTO `result` VALUES ('1014', '15', '2011-12-11 10:00:00', '73');
INSERT INTO `result` VALUES ('1014', '16', '2010-09-11 10:00:00', '68');
INSERT INTO `result` VALUES ('1015', '1', '2013-11-11 16:00:00', '99');
INSERT INTO `result` VALUES ('1015', '2', '2012-11-10 10:00:00', '60');
INSERT INTO `result` VALUES ('1015', '3', '2011-12-19 10:00:00', '60');
INSERT INTO `result` VALUES ('1015', '4', '2010-11-18 11:00:00', '75');
INSERT INTO `result` VALUES ('1015', '5', '2013-11-11 14:00:00', '78');
INSERT INTO `result` VALUES ('1015', '6', '2012-09-13 15:00:00', '78');
INSERT INTO `result` VALUES ('1015', '7', '2011-10-16 16:00:00', '84');
INSERT INTO `result` VALUES ('1015', '8', '2010-11-11 16:00:00', '95');
INSERT INTO `result` VALUES ('1015', '9', '2013-11-21 10:00:00', '93');
INSERT INTO `result` VALUES ('1015', '10', '2012-11-11 12:00:00', '79');
INSERT INTO `result` VALUES ('1015', '11', '2011-11-11 14:00:00', '74');
INSERT INTO `result` VALUES ('1015', '12', '2010-11-11 15:00:00', '65');
INSERT INTO `result` VALUES ('1015', '13', '2013-11-11 14:00:00', '63');
INSERT INTO `result` VALUES ('1015', '14', '2012-11-11 15:00:00', '74');
INSERT INTO `result` VALUES ('1015', '15', '2011-12-11 10:00:00', '67');
INSERT INTO `result` VALUES ('1015', '16', '2010-09-11 10:00:00', '65');
INSERT INTO `result` VALUES ('1016', '1', '2013-11-11 16:00:00', '97');
INSERT INTO `result` VALUES ('1016', '2', '2012-11-10 10:00:00', '90');
INSERT INTO `result` VALUES ('1016', '3', '2011-12-19 10:00:00', '77');
INSERT INTO `result` VALUES ('1016', '4', '2010-11-18 11:00:00', '75');
INSERT INTO `result` VALUES ('1016', '5', '2013-11-11 14:00:00', '75');
INSERT INTO `result` VALUES ('1016', '6', '2012-09-13 15:00:00', '97');
INSERT INTO `result` VALUES ('1016', '7', '2011-10-16 16:00:00', '96');
INSERT INTO `result` VALUES ('1016', '8', '2010-11-11 16:00:00', '92');
INSERT INTO `result` VALUES ('1016', '9', '2013-11-21 10:00:00', '62');
INSERT INTO `result` VALUES ('1016', '10', '2012-11-11 12:00:00', '83');
INSERT INTO `result` VALUES ('1016', '11', '2011-11-11 14:00:00', '98');
INSERT INTO `result` VALUES ('1016', '12', '2010-11-11 15:00:00', '94');
INSERT INTO `result` VALUES ('1016', '13', '2013-11-11 14:00:00', '62');
INSERT INTO `result` VALUES ('1016', '14', '2012-11-11 15:00:00', '97');
INSERT INTO `result` VALUES ('1016', '15', '2011-12-11 10:00:00', '76');
INSERT INTO `result` VALUES ('1016', '16', '2010-09-11 10:00:00', '82');
INSERT INTO `result` VALUES ('1017', '1', '2013-11-11 16:00:00', '100');
INSERT INTO `result` VALUES ('1017', '2', '2012-11-10 10:00:00', '88');
INSERT INTO `result` VALUES ('1017', '3', '2011-12-19 10:00:00', '86');
INSERT INTO `result` VALUES ('1017', '4', '2010-11-18 11:00:00', '73');
INSERT INTO `result` VALUES ('1017', '5', '2013-11-11 14:00:00', '96');
INSERT INTO `result` VALUES ('1017', '6', '2012-09-13 15:00:00', '64');
INSERT INTO `result` VALUES ('1017', '7', '2011-10-16 16:00:00', '81');
INSERT INTO `result` VALUES ('1017', '8', '2010-11-11 16:00:00', '66');
INSERT INTO `result` VALUES ('1017', '9', '2013-11-21 10:00:00', '76');
INSERT INTO `result` VALUES ('1017', '10', '2012-11-11 12:00:00', '95');
INSERT INTO `result` VALUES ('1017', '11', '2011-11-11 14:00:00', '73');
INSERT INTO `result` VALUES ('1017', '12', '2010-11-11 15:00:00', '82');
INSERT INTO `result` VALUES ('1017', '13', '2013-11-11 14:00:00', '85');
INSERT INTO `result` VALUES ('1017', '14', '2012-11-11 15:00:00', '68');
INSERT INTO `result` VALUES ('1017', '15', '2011-12-11 10:00:00', '99');
INSERT INTO `result` VALUES ('1017', '16', '2010-09-11 10:00:00', '76');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`StudentNo` int(4) NOT NULL COMMENT '学号',
`LoginPwd` varchar(20) DEFAULT NULL,
`StudentName` varchar(20) DEFAULT NULL COMMENT '学生姓名',
`Sex` tinyint(1) DEFAULT NULL COMMENT '性别，取值0或1',
`GradeId` int(11) DEFAULT NULL COMMENT '年级编号',
`Phone` varchar(50) DEFAULT NULL COMMENT '联系电话，允许为空，即可选输入',
`Address` varchar(255) DEFAULT NULL COMMENT '地址，允许为空，即可选输入',
`BornDate` datetime DEFAULT NULL COMMENT '出生时间',
`Email` varchar(50) DEFAULT NULL COMMENT '邮箱账号，允许为空，即可选输入',
`IdentityCard` varchar(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`StudentNo`),
UNIQUE KEY `IdentityCard` (`IdentityCard`),
KEY `Email` (`Email`),
KEY `student_name` (`StudentName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1000', '111111', '郭靖', '1', '1', '13500000001', '北京海淀区中关村大街1号', '1986-12-11 00:00:00', 'test1@bdqn.cn', '450323198612111234');
INSERT INTO `student` VALUES ('1001', '123456', '李文才', '1', '2', '13500000002', '河南洛阳', '1981-12-31 00:00:00', 'test1@bdqn.cn', '450323198112311234');
INSERT INTO `student` VALUES ('1002', '111111', '李斯文', '1', '1', '13500000003', '天津市和平区', '1986-11-30 00:00:00', 'test1@bdqn.cn', '450323198611301234');
INSERT INTO `student` VALUES ('1003', '123456', '武松', '1', '3', '13500000004', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612314234');
INSERT INTO `student` VALUES ('1004', '123456', '张三', '1', '4', '13500000005', '北京市通州', '1989-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311244');
INSERT INTO `student` VALUES ('1005', '123456', '张秋丽 ', '2', '1', '13500000006', '广西桂林市灵川', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311214');
INSERT INTO `student` VALUES ('1006', '123456', '肖梅', '2', '4', '13500000007', '地址不详', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311134');
INSERT INTO `student` VALUES ('1007', '111111', '欧阳峻峰', '1', '1', '13500000008', '北京东城区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311133');
INSERT INTO `student` VALUES ('1008', '111111', '梅超风', '1', '1', '13500000009', '河南洛阳', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311221');
INSERT INTO `student` VALUES ('1009', '123456', '刘毅', '1', '2', '13500000011', '安徽', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311231');
INSERT INTO `student` VALUES ('1010', '111111', '大凡', '1', '1', '13500000012', '河南洛阳', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311044');
INSERT INTO `student` VALUES ('1011', '111111', '奥丹斯', '1', '1', '13500000013', '北京海淀区中关村大街*号', '1984-12-31 00:00:00', 'test1@bdqn.cn', '450323198412311234');
INSERT INTO `student` VALUES ('1012', '123456', '多伦', '2', '3', '13500000014', '广西南宁中央大街', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311334');
INSERT INTO `student` VALUES ('1013', '123456', '李梅', '2', '1', '13500000015', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311534');
INSERT INTO `student` VALUES ('1014', '123456', '张得', '2', '4', '13500000016', '北京海淀区中关村大街*号', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311264');
INSERT INTO `student` VALUES ('1015', '123456', '李东方', '1', '4', '13500000017', '广西桂林市灵川', '1976-12-31 00:00:00', 'test1@bdqn.cn', '450323197612311234');
INSERT INTO `student` VALUES ('1016', '111111', '刘奋斗', '1', '1', '13500000018', '上海卢湾区', '1986-12-31 00:00:00', 'test1@bdqn.cn', '450323198612311251');
INSERT INTO `student` VALUES ('1017', '123456', '可可', '2', '3', '13500000019', '北京长安街1号', '2022-02-22 23:00:00', 'test1@bdqn.cn', '450323198109108311');
INSERT INTO `student` VALUES ('10066', '', 'Tom', '1', '1', '13500000000', '', '2022-02-18 16:19:04', 'email@22.com', '33123123123123123');
-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
`SubjectNo` int(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`SubjectName` varchar(50) DEFAULT NULL COMMENT '课程名称',
`ClassHour` int(4) DEFAULT NULL COMMENT '学时',
`GradeID` int(4) DEFAULT NULL COMMENT '年级编号',
PRIMARY KEY (`SubjectNo`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES ('1', '高等数学-1', '110', '1');
INSERT INTO `subject` VALUES ('2', '高等数学-2', '110', '2');
INSERT INTO `subject` VALUES ('3', '高等数学-3', '100', '3');
INSERT INTO `subject` VALUES ('4', '高等数学-4', '130', '4');
INSERT INTO `subject` VALUES ('5', 'C语言-1', '110', '1');
INSERT INTO `subject` VALUES ('6', 'C语言-2', '110', '2');
INSERT INTO `subject` VALUES ('7', 'C语言-3', '100', '3');
INSERT INTO `subject` VALUES ('8', 'C语言-4', '130', '4');
INSERT INTO `subject` VALUES ('9', 'JAVA第一学年', '110', '1');
INSERT INTO `subject` VALUES ('10', 'JAVA第二学年', '110', '2');
INSERT INTO `subject` VALUES ('11', 'JAVA第三学年', '100', '3');
INSERT INTO `subject` VALUES ('12', 'JAVA第四学年', '130', '4');
INSERT INTO `subject` VALUES ('13', '数据库结构-1', '110', '1');
INSERT INTO `subject` VALUES ('14', '数据库结构-2', '110', '2');
INSERT INTO `subject` VALUES ('15', '数据库结构-3', '100', '3');
INSERT INTO `subject` VALUES ('16', '数据库结构-4', '130', '4');
INSERT INTO `subject` VALUES ('17', 'C#基础', '130', '1');
-- ----------------------------
-- Procedure structure for addresult
-- ----------------------------
DROP PROCEDURE IF EXISTS `addresult`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `addresult`(sname VARCHAR(50),subname VARCHAR(50),res INT(4))
BEGIN
#根据学生姓名获得学号
select StudentNo into @stuNo from student where StudentName = sname;
#根据课程名称获得课程编号
select SubjectNo,SubjectName into @subNo,@subName from `subject` where SubjectName = subname;
#添加考试信息
INSERT INTO result(StudentNo,SubjectNo,StudentResult)
VALUES (@stuNo,@subNo,res);
select @subName;
END
;;
DELIMITER ;
基础语法
create DATABASE if not EXISTS school;
-- 删除数据库
DROP DATABASE if EXISTS school;
-- 查询所有的数据库
show DATABASES;
-- 切换数据库
use myschool;
-- 创建表（简单语法）
CREATE TABLE `grade`(
grade_id int(11),
grade_name VARCHAR(50)
);
-- 删除表
drop table if EXISTS grade; --grade表名
-- 创建表并设置属性
-- AUTO_INCREMENT 自动递增
-- not null 不能为空
-- COMMENT '' 注释
-- PRIMARY KEY 设置为主键
-- DEFAULT 默认值
-- CHARSET=UTF8 设置编码格式
CREATE TABLE if not EXISTS grade(
grade_id int(11) not null AUTO_INCREMENT PRIMARY KEY COMMENT '年级编号',
grade_name VARCHAR(50) DEFAULT '无' not null COMMENT '年级名称'
)CHARSET=UTF8;
新增表数据
-- 新增 insert into 表名 values(值1,值2....);
INSERT INTO mysh_grade VALUES(1,"大一");
-- 新增 insert into 表名(列1,列2...) values(值1,值2....);
INSERT INTO mysh_grade(grade_name) values("大二");
-- 增加多条数据
INSERT INTO mysh_grade(grade_name) values("大三"),("大四");
insert into mysh_student(stu_name,stu_pwd,stu_grade_id,stu_born_date)
values("王五","123",4,"1998-12-12");
-- 修改 update 表名 set 列1=值1,列2=值2...... where 条件;
update mysh_student set stu_sex=1,stu_pwd="456";
update mysh_student set stu_sex=0 where stu_name="张三";
-- 修改一年级或者性别为女的学生密码为 123
update mysh_student set stu_pwd="123"
where stu_grade_id=1 or stu_sex = 1;
-- 删除 delete from 表名 where 条件
delete from mysh_subject where subject_no=4;
基础查询
-- 查询所有学生考试成绩 返回指定列
select res_student_no,res_subject_no,res_student_result from mysh_result;
-- 查询所有学生考试成绩 返回所有列 *代码所有的列
select * from mysh_result;
-- 查询成绩大于80分的考试信息
select * from mysh_result where res_student_result>80;
-- 获得张三的java考试成绩
select * from mysh_result
where res_student_no=1 and res_subject_no = 1;
-- 获得成绩大于60分并且小于95分的成绩信息
select * from mysh_result
where res_student_result>60 and res_student_result<95;
select * FROM mysh_result
where res_student_result BETWEEN 60 and 95;
-- 列别名 as as可省略
select res_student_no '学生学号',
res_subject_no '课程编号',
res_student_result+1 "成绩"
from mysh_result;
-- 表别名 as
select a.stu_name,a.stu_no from mysh_student as a;
-- 查询出生日日期为空的学生信息
select * from mysh_student where stu_born_date = "";
-- is null 和 is not null
select * from mysh_student where stu_phone is not NULL;
-- like 模糊查询
-- %匹配多项 _ 匹配单项
-- 获取姓张的学生信息
select * from mysh_student a where a.stu_name LIKE '张%' ;
-- 获得名字中带狗字的学生信息
select * from mysh_student a
where a.stu_name like '%狗%' and stu_sex=0;
-- 获得学生编号为1 3 2的学生信息
select * from mysh_student
where stu_no in (1,2,3);
-- 获得java php .net的考试信息
-- 表连接
-- 内连接（全连接）: INNER JOIN ...on
-- 两张表连接，两张表字段完全匹配才返回数据
-- 外连接
-- 左外连接 :LEFT JOIN ... ON
-- 以左表为主表进行连接，显示左表所有数据
-- 右外连接:RIGHT JOIN...ON
-- 以右表为主表进行连接,显示右表所有数据
-- 主表 子表
-- 内连接 ：获得所有学生的考试成绩，并显示学生姓名和成绩
select b.stu_name,a.res_student_result from
mysh_result a INNER JOIN mysh_student b
on a.res_student_no = b.stu_no ;
-- 左连接 ：获得所有学生的考试成绩，并显示学生姓名和成绩
select b.stu_name,a.res_student_result from
mysh_result a LEFT JOIN mysh_student b
on a.res_student_no = b.stu_no;
-- 右连接 : 获得所有学生的考试成绩，并显示学生姓名和成绩
select b.stu_name,a.res_student_result from
mysh_result a RIGHT JOIN mysh_student b
on a.res_student_no = b.stu_no;
-- 获得所有的考试信息
SELECT a.*,b.stu_name,c.subject_name from
mysh_result a LEFT JOIN mysh_student b
on a.res_student_no = b.stu_no LEFT JOIN mysh_subject c
on a.res_subject_no = c.subject_no
where c.subject_name="JAVA" and a.res_student_result>50;
-- 内连接
select * from mysh_result a,mysh_student b
where a.res_student_no=b.stu_no;
-- 自连接
select a.type_name '子类' ,
b.type_name '父类'
from goods_type a LEFT JOIN goods_type b
on a.type_pid = b.type_id
where a.type_pid!=0;
DQL 查询 排序
-- select 列名 from 表名 where 条件 order by 列名 desc|asc(默认)
-- 查询《数据库结构-1》的所有考试结果，并按成绩由高到低排列
select
b.SubjectNo,
b.SubjectName,
a.ExamDate,
a.StudentResult,a.StudentNo
from result a LEFT JOIN `subject` b
on a.SubjectNo = b.SubjectNo
where b.SubjectName="数据库结构-1"
ORDER BY a.StudentResult desc,a.StudentNo asc;
-- limit [第几条开始,]显示条数
--
-- 查询所有《数据库结构-2》的考试成绩，并按照由高到低显示，
-- 同时把该成绩对应的学生的学号、姓名打印出来
select c.StudentNo,
c.StudentName,
b.SubjectName,
a.StudentResult
from result a LEFT JOIN `subject` b
on a.SubjectNo = b.SubjectNo
LEFT JOIN student c
on c.StudentNo = a.StudentNo
where b.SubjectName = "数据库结构-2"
ORDER BY a.StudentResult desc
LIMIT 3;
-- 子查询
-- 1.列子查询 必须返回单行单列
-- 2.表子查询 可以返回多列多行
-- 3.条件子查询 必须返回单例 返回单行使用等号 返回多行使用in
-- 条件子查询：
-- 使用子查询获得高等数学-2且分数不小于80分的信息
select * from result
where StudentResult>80 and SubjectNo = (
select SubjectNo from `subject` where SubjectName = '高等数学-2'
) ORDER BY StudentResult desc;
select SubjectNo from `subject`
where SubjectName = '高等数学-2' or SubjectName = '高等数学-1';
-- 使用子查询获得高等数学-2和高等数学-1 且分数不小于80分的信息
select * from result
where StudentResult>80 and SubjectNo in (
select SubjectNo from `subject`
where SubjectName = '高等数学-2' or SubjectName = '高等数学-1'
) ORDER BY StudentResult desc;
-- 列子查询
select a.StudentNo,a.SubjectNo,a.StudentResult,
(
select b.StudentName
from student b
where b.StudentNo = a.StudentNo
) as '学生名称'
from result a;
select * from result a LEFT JOIN `subject` b
on a.SubjectNo = b.SubjectNo LEFT JOIN
(
select StudentNo,StudentName,GradeName
from student c LEFT JOIN grade d on
c.GradeId = d.GradeID
) as e on a.StudentNo = e.StudentNo
where e.GradeName="大一";
-- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SELECT a.*,b.s_score FROM student a LEFT JOIN score b
ON a.s_id=b.s_id and b.c_id = '01' LEFT JOIN score c
ON a.s_id=c.s_id AND c.c_id = '02'WHERE b.s_score>c.s_score;
-- 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT a.*,b.s_score FROM student a LEFT JOIN score b
ON a.s_id=b.s_id AND b.c_id='01' LEFT JOIN score c
ON a.s_id=c.s_id AND c.c_id='02'WHERE b.s_scoreSQL 函数
-- 日期函数
select SYSDATE();
-- 获得当天考试的信息
select * from result
where DATE_FORMAT(ExamDate,"%Y%m%d") = DATE_FORMAT(now(),"%Y%m%d");
-- 获得2011-2013年考试信息
select * from result
where DATE_FORMAT(ExamDate,"%Y") BETWEEN 2011 and 2013;
select to_days('2022-02-14');
select to_days('2022-02-13');
-- 获得今天之前的所有考试数据
select * from result where to_days(ExamDate)80 and SubjectNo = (
select SubjectNo from `subject` where SubjectName = '高等数学-2'
) ORDER BY StudentResult desc;
select SubjectNo from `subject`
where SubjectName = '高等数学-2' or SubjectName = '高等数学-1';
-- 使用子查询获得高等数学-2和高等数学-1 且分数不小于80分的信息
select * from result
where StudentResult>80 and SubjectNo in (
select SubjectNo from `subject`
where SubjectName = '高等数学-2' or SubjectName = '高等数学-1'
) ORDER BY StudentResult desc;
-- 列子查询
select a.StudentNo,a.SubjectNo,a.StudentResult,
(
select b.StudentName
from student b
where b.StudentNo = a.StudentNo
) as '学生名称'
from result a;
select * from result a LEFT JOIN `subject` b
on a.SubjectNo = b.SubjectNo LEFT JOIN
(
select StudentNo,StudentName,GradeName
from student c LEFT JOIN grade d on
c.GradeId = d.GradeID
) as e on a.StudentNo = e.StudentNo
where e.GradeName="大一";
-- 数学函数
-- count(列) 求总记录数
select count(1) from student ;
-- sum() 求和 1280/16
select StudentNo,avg(StudentResult) from result
where StudentNo = 1003 ;
select b.SubjectName
,SUM(StudentResult) '总成绩'
,avg(StudentResult) 'avgRes'
,max(StudentResult) '最高分'
,min(StudentResult) '最低分'
from result a LEFT JOIN `subject` b
on a.SubjectNo = b.SubjectNo
GROUP BY b.SubjectName
HAVING avg(StudentResult)>80
ORDER BY avgRes desc
LIMIT 5;
存储过程
DROP PROCEDURE if EXISTS addresult; #删除存储过程
-- 添加张三的高等数学-1考试成绩信息 80分
CREATE PROCEDURE addresult(sname VARCHAR(50),subname VARCHAR(50),res INT(4))
BEGIN
#根据学生姓名获得学号
select StudentNo into @stuNo from student where StudentName = sname;
#根据课程名称获得课程编号
select SubjectNo,SubjectName into @subNo,@subName from `subject` where SubjectName = subname;
#添加考试信息
INSERT INTO result(StudentNo,SubjectNo,StudentResult)
VALUES (@stuNo,@subNo,res);
select @subName;
END;
CALL addresult("1001","高等数学-1",80);
#参数传递
CREATE PROCEDURE test4(uname VARCHAR(50),in age INT(10))
BEGIN
set age = 19;
END;
set @age = 18;
CALL test4('张三',@age);
select @age;
#WHILE 循环
CREATE PROCEDURE test3()
BEGIN
DECLARE i INT(10) DEFAULT 0;
WHILE i<5 DO
set i = i + 1;
END WHILE;
select i;
END;
CALL test3();
# if 判断
CREATE PROCEDURE test2()
BEGIN
DECLARE uname VARCHAR(50) DEFAULT '张三';
IF uname='张三1' THEN
select 0;
ELSE
select 1;
END IF;
END;
CALL test2();
CREATE PROCEDURE test1()
BEGIN
DECLARE t_error int DEFAULT 0; #定义变量 默认值0
set t_error = 2; #变量赋值
select t_error; #返回值
END;
call test1(); #调用
-- 创建存储过程
CREATE PROCEDURE showStudentRes()
BEGIN
select * from student where StudentNo=1000;
END;
-- 调用存储过程
call showStudentRes();
事务
CREATE PROCEDURE updateMoney(uid1 INT,uid2 INT,m INT(11))
BEGIN
#定义变量 记录错误 0正常 1错误
DECLARE t_error int DEFAULT 0;
DECLARE CONTINUE HANDLER for SQLEXCEPTION SET t_error = 1;
START TRANSACTION; #开启事务
update user set money = money - m where uid=uid1;
update user set money = money + m where uid=uid2;
if t_error=1 THEN
ROLLBACK;#回滚
ELSE
COMMIT; #提交
END if;
select t_error;
END;
CALL updateMoney(1,2,100);