mysql version 50713_MYSQL 练习

导出现有数据库数据：

mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构+数据

mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径       # 结构

导入现有数据库数据：

mysqldump -uroot -p密码  数据库名称 < 文件路径

一、表关系

请创建如下表，并创建相关约束

二、操作表

1、自行创建测试数据

/*

Navicat MySQL Data Transfer

Source Server : abc

Source Server Version : 50713

Source Host : localhost:3306

Source Database : lx

Target Server Type : MYSQL

Target Server Version : 50713

File Encoding : 65001

Date: 2016-10-24 18:07:25

*/

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(20) DEFAULT NULL,

PRIMARY KEY (`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of class

-- ----------------------------

INSERT INTO `class` VALUES ('1', '三年二班');

INSERT INTO `class` VALUES ('2', '一年三班');

INSERT INTO `class` VALUES ('3', '三年一班');

INSERT INTO `class` VALUES ('4', 'python 二期');

-- ----------------------------

-- Table structure for `course`

-- ----------------------------

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`cid` int(11) NOT NULL AUTO_INCREMENT,

`cname` varchar(20) DEFAULT NULL,

`teache_id` int(11) DEFAULT NULL,

PRIMARY KEY (`cid`),

KEY `fk_c_t` (`teache_id`),

CONSTRAINT `fk_c_t` FOREIGN KEY (`teache_id`) REFERENCES `teacher` (`tid`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of course

-- ----------------------------

INSERT INTO `course` VALUES ('1', '生物', '1');

INSERT INTO `course` VALUES ('2', '体育', '1');

INSERT INTO `course` VALUES ('3', '物理', '2');

-- ----------------------------

-- Table structure for `score`

-- ----------------------------

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (

`sid` int(11) NOT NULL AUTO_INCREMENT,

`student_id` int(11) DEFAULT NULL,

`corse_id` int(11) DEFAULT NULL,

`number` int(11) DEFAULT NULL,

PRIMARY KEY (`sid`),

KEY `fk_s_st` (`student_id`),

KEY `fk_s_co` (`corse_id`),

CONSTRAINT `fk_s_co` FOREIGN KEY (`corse_id`) REFERENCES `course` (`cid`),

CONSTRAINT `fk_s_st` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)

) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of score

-- ----------------------------

INSERT INTO `score` VALUES ('1', '1', '1', '60');

INSERT INTO `score` VALUES ('2', '1', '2', '59');

INSERT INTO `score` VALUES ('3', '2', '2', '100');

INSERT INTO `score` VALUES ('4', '1', '3', '76');

INSERT INTO `score` VALUES ('5', '2', '1', '99');

INSERT INTO `score` VALUES ('6', '2', '3', '88');

INSERT INTO `score` VALUES ('7', '3', '1', '66');

INSERT INTO `score` VALUES ('8', '3', '2', '77');

INSERT INTO `score` VALUES ('9', '3', '3', '88');

INSERT INTO `score` VALUES ('10', '4', '1', '76');

INSERT INTO `score` VALUES ('11', '4', '2', '99');

INSERT INTO `score` VALUES ('12', '4', '3', '88');

INSERT INTO `score` VALUES ('13', '5', '1', '66');

INSERT INTO `score` VALUES ('14', '5', '2', '77');

INSERT INTO `score` VALUES ('15', '5', '3', '88');

INSERT INTO `score` VALUES ('16', '6', '1', '58');

INSERT INTO `score` VALUES ('17', '6', '2', '68');

INSERT INTO `score` VALUES ('18', '6', '3', '78');

INSERT INTO `score` VALUES ('19', '7', '1', '87');

INSERT INTO `score` VALUES ('20', '7', '2', '76');

INSERT INTO `score` VALUES ('21', '7', '3', '65');

INSERT INTO `score` VALUES ('22', '8', '1', '90');

INSERT INTO `score` VALUES ('23', '8', '2', '81');

INSERT INTO `score` VALUES ('24', '8', '3', '77');

INSERT INTO `score` VALUES ('25', '9', '1', '86');

INSERT INTO `score` VALUES ('26', '9', '2', '78');

INSERT INTO `score` VALUES ('27', '9', '3', '97');

-- ----------------------------

-- Table structure for `student`

-- ----------------------------

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`sid` int(11) NOT NULL AUTO_INCREMENT,

`sname` varchar(20) DEFAULT NULL,

`gender` varchar(12) DEFAULT NULL,

`class_id` int(11) DEFAULT NULL,

PRIMARY KEY (`sid`),

KEY `fk_s_c` (`class_id`),

CONSTRAINT `fk_s_c` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of student

-- ----------------------------

INSERT INTO `student` VALUES ('1', '钢蛋', '女', '1');

INSERT INTO `student` VALUES ('2', '铁锤', '女', '1');

INSERT INTO `student` VALUES ('3', '山炮', '男', '2');

INSERT INTO `student` VALUES ('4', '张三', '男', '3');

INSERT INTO `student` VALUES ('5', '李四', '男', '2');

INSERT INTO `student` VALUES ('6', '王五', '男', '3');

INSERT INTO `student` VALUES ('7', '马六', '女', '1');

INSERT INTO `student` VALUES ('8', '霍七', '男', '2');

INSERT INTO `student` VALUES ('9', '贺八', '男', '3');

-- ----------------------------

-- Table structure for `teacher`

-- ----------------------------

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (

`tid` int(11) NOT NULL AUTO_INCREMENT,

`tname` varchar(20) DEFAULT NULL,

PRIMARY KEY (`tid`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------

-- Records of teacher

-- ----------------------------

INSERT INTO `teacher` VALUES ('1', '波多');

INSERT INTO `teacher` VALUES ('2', '苍空');

INSERT INTO `teacher` VALUES ('3', '饭岛');

创建表(结构)

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

来源：https://www.cnblogs.com/lst1010/p/5994159.html