[MySQL & Python] 10. 脚本文件

范例脚本内容

粘贴下面的脚本到data_prepare.sql文件中

​
create table class (
    id int not null primary key auto_increment,
    caption varchar(20) not null) default charset=utf8 ;
​
use day28db;
​
create table student (
    sid int not null primary key auto_increment,
    sname varchar(20),
    gender varchar(3),
    class_id int not null,
    constraint fk_student_class foreign key student(class_id) references class(id)
    ) default charset=utf8;
​
create table teacher( 
    tid int not null primary key auto_increment, 
    tname varchar(20)
    )default charset=utf8;
​
create table course (
    cid int not null primary key auto_increment,
    cname varchar(20),
    teacher_id int ,
    constraint fk_course_teacher foreign key course(teacher_id) references teacher(tid)) default charset=utf8;
​
create table score(
    sid int not null primary key auto_increment,
    student_id int ,
    course_id int,
    number int,
    constraint fk_score_student foreign key score(student_id) references student(sid),
    constraint fk_score_course foreign key score(course_id) references course(cid)
    ) default charset=utf8;
​
​
insert into class (caption) values ('2.6'),('2.7'),('2.8');
​
insert into student (sname, gender, class_id) values  ('张逸轩','男',2);
insert into student (sname, gender, class_id) values  ('顾伯瑞','男',2);
insert into student (sname, gender, class_id) values  ('薛紫萱','女',2);
insert into student (sname, gender, class_id) values  ('张歆艺','女',1);
insert into student (sname, gender, class_id) values  ('于艺','女',1);
insert into student (sname, gender, class_id) values  ('李雪','女',3);
insert into student (sname, gender, class_id) values  ('吴娟','女',2);
insert into student (sname, gender, class_id) values  ('邹新刚','男',3);
insert into student (sname, gender, class_id) values  ('张乐庭','男',2);
​
​
insert into teacher (tname) values ('马老师'),('董老师'),('张老师');
​
insert into course (cname, teacher_id) values ('语文',1),('数学',2),('政治',3);    
​
insert into score (student_id, course_id, number) values 
    (1,1,90), (1,2,89),(1,3,79),(2,1,100),(2,2,90),(2,3,60),(3,1,100),(3,2,100),(3,3,90);
​
insert into score (student_id, course_id, number) values  
    (4,1,90), (4,2,89),(4,3,99),(5,1,100),(5,2,80),(5,3,80),(6,1,100),(6,2,60),(6,3,90);
​
insert into score (student_id, course_id, number) values  
    (7,1,90), (7,2,89),(7,3,99),(8,1,100),(8,2,80),(8,3,80),(9,1,100),(9,2,60),(9,3,90);
​
​
​

 

导入脚本

首先创建脚本所操作的数据库。

create database day28db default charset utf8 collate utf8_general_ci;

在命令行下，使用mysql -u root -p 数据库名 < 脚本文件 来执行脚本作用于数据库。

mysql -u root -p day28db < /Users/leo/Desktop/data_prepare.sql

 

导出数据库到脚本文件

对于已经存在的数据库，可以通过mysqldump命令将它导出到一个脚本，其他人使用脚本就可以创建此数据库。

mysqldump -u root -p day27db > /Users/leo/Desktop/day27.sql

导出的脚本如下：

-- MySQL dump 10.13  Distrib 5.7.31, for macos10.14 (x86_64)
--
-- Host: localhost    Database: day27db
-- ------------------------------------------------------
-- Server version 5.7.31
​
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
​
--
-- Table structure for table `class`
--
​
DROP TABLE IF EXISTS `class`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `class` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `caption` varchar(20) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
​
--
-- Dumping data for table `class`
--
​
LOCK TABLES `class` WRITE;
/*!40000 ALTER TABLE `class` DISABLE KEYS */;
INSERT INTO `class` VALUES (1,'2.6'),(2,'2.7'),(3,'2.8');
/*!40000 ALTER TABLE `class` ENABLE KEYS */;
UNLOCK TABLES;
​
---之后省略---

 

也可以只是导出表结构而不导出数据，使用-d 参数可以控制。

mysqldump -u root -p -d day27db > /Users/leo/Desktop/day27db_structureOnly.sql