navicat及MySQL练习题参考答案

MySQL练习题参考答案

  navicat：

            　　  注释：--空格  快捷键：Ctrl ？

　　　　       在navicat写sql语句：选中一个数据库---点‘’查询‘’---‘’新建查询‘’----就可以写sql语句然后执行了

　　　　　　转储或运行sql文件：选中一个数据库右键选择转储（可选择储存结构和数据或仅储存结构）或运行sql文件

　　　　　　查看表的sql语句：选中一个表---右键选择‘’对象信息‘’

导出现有数据库数据：

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

导入现有数据库数据：

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

    1 /*
    2  Navicat Premium Data Transfer
    3 
    4  Source Server         : localhost
    5  Source Server Type    : MySQL
    6  Source Server Version : 50624
    7  Source Host           : localhost
    8  Source Database       : sqlexam
    9 
   10  Target Server Type    : MySQL
   11  Target Server Version : 50624
   12  File Encoding         : utf-8
   13 
   14  Date: 10/21/2016 06:46:46 AM
   15 */
   16 
   17 SET NAMES utf8;
   18 SET FOREIGN_KEY_CHECKS = 0;
   19 
   20 -- ----------------------------
   21 --  Table structure for `class`
   22 -- ----------------------------
   23 DROP TABLE IF EXISTS `class`;
   24 CREATE TABLE `class` (
   25   `cid` int(11) NOT NULL AUTO_INCREMENT,
   26   `caption` varchar(32) NOT NULL,
   27   PRIMARY KEY (`cid`)
   28 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
   29 
   30 -- ----------------------------
   31 --  Records of `class`
   32 -- ----------------------------
   33 BEGIN;
   34 INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
   35 COMMIT;
   36 
   37 -- ----------------------------
   38 --  Table structure for `course`
   39 -- ----------------------------
   40 DROP TABLE IF EXISTS `course`;
   41 CREATE TABLE `course` (
   42   `cid` int(11) NOT NULL AUTO_INCREMENT,
   43   `cname` varchar(32) NOT NULL,
   44   `teacher_id` int(11) NOT NULL,
   45   PRIMARY KEY (`cid`),
   46   KEY `fk_course_teacher` (`teacher_id`),
   47   CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
   48 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
   49 
   50 -- ----------------------------
   51 --  Records of `course`
   52 -- ----------------------------
   53 BEGIN;
   54 INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
   55 COMMIT;
   56 
   57 -- ----------------------------
   58 --  Table structure for `score`
   59 -- ----------------------------
   60 DROP TABLE IF EXISTS `score`;
   61 CREATE TABLE `score` (
   62   `sid` int(11) NOT NULL AUTO_INCREMENT,
   63   `student_id` int(11) NOT NULL,
   64   `course_id` int(11) NOT NULL,
   65   `num` int(11) NOT NULL,
   66   PRIMARY KEY (`sid`),
   67   KEY `fk_score_student` (`student_id`),
   68   KEY `fk_score_course` (`course_id`),
   69   CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
   70   CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
   71 ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
   72 
   73 -- ----------------------------
   74 --  Records of `score`
   75 -- ----------------------------
   76 BEGIN;
   77 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'), (