mysql答案_MySQL练习题参考答案

小咪咪 2022-10-25 05:00 253阅读 0赞

![48304ba5e6f9fe08f3fa1abda7d326ab.png][]

/\*

Navicat Premium Data Transfer

Source Server : localhost

Source Server Type : MySQL

Source Server Version : 50624

Source Host : localhost

Source Database : sqlexam

Target Server Type : MySQL

Target Server Version : 50624

File Encoding : utf-8

Date: 10/21/2016 06:46:46 AM

\*/

SET NAMES utf8;

SET FOREIGN\_KEY\_CHECKS = 0;

\-- ----------------------------

\-- Table structure for \`class\`

\-- ----------------------------

DROP TABLE IF EXISTS \`class\`;

CREATE TABLE \`class\` (

\`cid\` int(11) NOT NULL AUTO\_INCREMENT,

\`caption\` varchar(32) NOT NULL,

PRIMARY KEY (\`cid\`)

) ENGINE=InnoDB AUTO\_INCREMENT=5 DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of \`class\`

\-- ----------------------------

BEGIN;

INSERT INTO \`class\` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');

COMMIT;

\-- ----------------------------

\-- Table structure for \`course\`

\-- ----------------------------

DROP TABLE IF EXISTS \`course\`;

CREATE TABLE \`course\` (

\`cid\` int(11) NOT NULL AUTO\_INCREMENT,

\`cname\` varchar(32) NOT NULL,

\`teacher\_id\` int(11) NOT NULL,

PRIMARY KEY (\`cid\`),

KEY \`fk\_course\_teacher\` (\`teacher\_id\`),

CONSTRAINT \`fk\_course\_teacher\` FOREIGN KEY (\`teacher\_id\`) REFERENCES \`teacher\` (\`tid\`)

) ENGINE=InnoDB AUTO\_INCREMENT=5 DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of \`course\`

\-- ----------------------------

BEGIN;

INSERT INTO \`course\` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');

COMMIT;

\-- ----------------------------

\-- Table structure for \`score\`

\-- ----------------------------

DROP TABLE IF EXISTS \`score\`;

CREATE TABLE \`score\` (

\`sid\` int(11) NOT NULL AUTO\_INCREMENT,

\`student\_id\` int(11) NOT NULL,

\`course\_id\` int(11) NOT NULL,

\`num\` int(11) NOT NULL,

PRIMARY KEY (\`sid\`),

KEY \`fk\_score\_student\` (\`student\_id\`),

KEY \`fk\_score\_course\` (\`course\_id\`),

CONSTRAINT \`fk\_score\_course\` FOREIGN KEY (\`course\_id\`) REFERENCES \`course\` (\`cid\`),

CONSTRAINT \`fk\_score\_student\` FOREIGN KEY (\`student\_id\`) REFERENCES \`student\` (\`sid\`)

) ENGINE=InnoDB AUTO\_INCREMENT=53 DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of \`score\`

\-- ----------------------------

BEGIN;

INSERT INTO \`score\` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');

COMMIT;

\-- ----------------------------

\-- Table structure for \`student\`

\-- ----------------------------

DROP TABLE IF EXISTS \`student\`;

CREATE TABLE \`student\` (

\`sid\` int(11) NOT NULL AUTO\_INCREMENT,

\`gender\` char(1) NOT NULL,

\`class\_id\` int(11) NOT NULL,

\`sname\` varchar(32) NOT NULL,

PRIMARY KEY (\`sid\`),

KEY \`fk\_class\` (\`class\_id\`),

CONSTRAINT \`fk\_class\` FOREIGN KEY (\`class\_id\`) REFERENCES \`class\` (\`cid\`)

) ENGINE=InnoDB AUTO\_INCREMENT=17 DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of \`student\`

\-- ----------------------------

BEGIN;

INSERT INTO \`student\` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');

COMMIT;

\-- ----------------------------

\-- Table structure for \`teacher\`

\-- ----------------------------

DROP TABLE IF EXISTS \`teacher\`;

CREATE TABLE \`teacher\` (

\`tid\` int(11) NOT NULL AUTO\_INCREMENT,

\`tname\` varchar(32) NOT NULL,

PRIMARY KEY (\`tid\`)

) ENGINE=InnoDB AUTO\_INCREMENT=6 DEFAULT CHARSET=utf8;

\-- ----------------------------

\-- Records of \`teacher\`