MYSQL——《数据库》实验壹——熟悉数据库管理工具、数据库和表的基本操作

一、实验目的：

1. 了解SQL Server或MYSQL数据库的基本知识；
2. 熟悉SQL Server或MYSQL环境和系统结构；
3. 掌握图形化交互工具的基本操作，学会数据库及表的创建与管理；
4. 理解和掌握SQL语言: 能够熟练地使用create/alter/drop语句创建、修改和删除数据库、基本表；能够使用insert语句；理解和掌握基本的Select语句。

二、实验要求：
理解和掌握create, alter, drop, insert, 以及select语句的语法，特别是各种参数的具体含义和使用方法；使用SQL语句创建、修改和删除数据库和基本表。掌握SQL语句常见语法错误的调试方法。

三、实验重点和难点：
重点：创建数据库、基本表，进行基本的查询操作。
难点：Select语句where部分使用的多类运算符。

四、实验内容：（SQL Server 或MYSQL数据库二选一，或同步比较学习）

1. 建立自己的MySQL实验环境，掌握第三方管理工具连接本地和远程数据库服务器的配置过程与方法；
2. 建立与课程相关的Movies数据库；（注意：如果SQL中不接受中文，可以将相关属性值改成相应的英文。在SQL代码中注意要使用英文的标点符号。下同，不再重复。）
3. 自主实践课堂上讲解过的SQL样例；
4. 建立销售表，字段如下：
   产品名称 varchar(20)
   数量 int not null
   是否赠品 bit not null
   其中：字段是否赠品的值为1，表示该条记录的产品是赠品，否则，表示是销售。
5. 在销售表中输入如下记录：
   可口可乐,100,1
   石榴,20,0
   可口可乐,50,0
   百事可乐,120,1
   百事可乐,30,0
6. 查询每个产品的赠送和销售情况统计，要求按如下方式显示：
   产品名称 销售数量 赠送数量
   可口可乐 50 100
   石榴 20 0
   百事可乐 30 120
7. 建立一个表，表结构任意，但至少应有3个字段，现在需要你在这个表内插入1000000条记录（数据任意），尽可能快的完成，看看你的用时。
8. 建立一个数据库，将给的EXCEL文件导入这个数据库，每一个sheet都构建一个表（如果有脏数据，就剔除），提示：可以使用NAVICAT等工具的导入功能，也可以使用其他工具。

五、实验步骤与结果：
1）

2）
建立查询，输入代码如下：

USE my_movie_database;

DROP TABLE IF EXISTS studio;
CREATE TABLE studio (
  name char(30) NOT NULL DEFAULT '',
  address varchar(100) DEFAULT NULL,
  presC int(11) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO studio VALUES ('MGM','MGM Boulevard',123),('Fox','Fox Boulevard',199),('Disney','Disney Boulevard',222),('USA Entertainm','USA Entertainm Boulevard',333),('Paramount','Paramount Boulevard',555);
 
DROP TABLE IF EXISTS movieexec;
CREATE TABLE movieexec (
  name char(30) DEFAULT NULL,
  address varchar(100) DEFAULT NULL,
  cert int(11) NOT NULL DEFAULT '0',
  netWorth int(11) DEFAULT NULL,
  PRIMARY KEY (`cert`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO movieexec VALUES ('Calvin Coolidge','Fast Lane',123,20000000),('Merv Griffin','Riot Rd.',199,112000000),('Stephen Spielberg','123 ET road',222,100000000),('Ted Turner','Turner Av.',333,125000000),('George Lucas','Oak Rd.',555,200000000),('Jane Fonda','Turner Av.',567,200000000);

DROP TABLE IF EXISTS movies;
CREATE TABLE movies (
  title char(100) NOT NULL DEFAULT '',
  year int(11) NOT NULL DEFAULT '0',
  length int(11) DEFAULT NULL,
  movieType char(10) DEFAULT NULL,
  studioName char(30) DEFAULT NULL,
  producerC int(11) DEFAULT NULL,
  PRIMARY KEY (title,year)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO movies VALUES ('Empire Strikes Back',1980,111,'drama','Fox',555),('Gone With the Wind',1938,238,'drama','MGM',123),('Logan\'s run',1977,120,'drama','MGM',888),('Pretty Woman',1990,119,'drama','Disney',999),('Star Trek',1979,132,'sciFic','Paramount',444),('Star Trek: Nemesis',2002,116,'sciFic','Paramount',321),('Star Wars',1977,124,'sciFic','Fox',555),('Terms of Endearment',1983,132,'drama','MGM',123),('The Man Who Wasn\'t There',2001,116,'comedy','USA Entertainm.',777),('The Usual Suspects',1995,106,'drama','MGM',999);
INSERT INTO movies(title,year) VALUES ('Star Wars',2017);
insert into movies(title,year,movieType,studioName)
values('Star Wars',2015,'sciFic','FOX');

DROP TABLE IF EXISTS moviestar;
CREATE TABLE moviestar (
  name char(30) NOT NULL DEFAULT '',
  address varchar(255) DEFAULT NULL,
  gender char(1) DEFAULT NULL,
  birthdate date DEFAULT NULL,
  PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO moviestar VALUES ('Alec Baldwin','Baldwin Av.','M','1977-06-07'),('Debra Winger','Baldwin Av.','F','1978-05-06'),('Harrison Ford','Prefect Rd.','M','1955-05-05'),('Jack Nicholson','X path','M','1949-05-05'),('Jane Fonda','Turner Av.','F','1977-07-07');

DROP TABLE IF EXISTS starsin;
CREATE TABLE starsin (
  movieTitle char(100) NOT NULL DEFAULT '',
  movieYear int(11) NOT NULL DEFAULT '0',
  starName char(30) NOT NULL DEFAULT '',
  PRIMARY KEY (movieTitle,movieYear,starName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO starsin VALUES ('Empire Strikes Back',1980,'Harrison Ford'),('Star Wars',1977,'Carrie Fisher'),('Star Wars',1977,'Harrison Ford'),('Star Wars',1977,'Mark Hamill'),('Star Wars',2017,'Carrie Fisher'),('Terms of Endearment',1983,'Debra Winger'),('Terms of Endearment',1983,'Jack Nicholson'),('The Usual Suspects',1995,'Kevin Spacey');

3）


4）
输入代码如下：

CREATE TABLE  `销售表`  (
  `产品名称` varchar(20) NULL,
  `数量` int(11) NOT NULL,
  `是否赠品` bit(1) NOT NULL
);

5）
输入代码如下：

INSERT INTO `销售表`(`产品名称`, `数量`, `是否赠品`) VALUES ('可口可乐', 100, b'1');
INSERT INTO `销售表`(`产品名称`, `数量`, `是否赠品`) VALUES ('石榴', 20, b'0');
INSERT INTO `销售表`(`产品名称`, `数量`, `是否赠品`) VALUES ('可口可乐', 50, b'0');
INSERT INTO `销售表`(`产品名称`, `数量`, `是否赠品`) VALUES ('百事可乐', 120, b'1');
INSERT INTO `销售表`(`产品名称`, `数量`, `是否赠品`) VALUES ('百事可乐', 30, b'0');

6）
输入代码如下：

SELECT
	t.产品名称,
	COALESCE ( a.销售数量, 0 ) AS 销售数量,
	COALESCE ( b.赠送数量, 0 ) AS 赠送数量 
FROM
	( SELECT DISTINCT 产品名称 FROM 销售表 ) t
	LEFT JOIN ( SELECT 产品名称, COALESCE ( SUM( 数量 ), 0 ) AS 销售数量 FROM 销售表 WHERE 是否赠品 = 0 GROUP BY 产品名称 ) a ON t.产品名称 = a.产品名称
	LEFT JOIN ( SELECT 产品名称, COALESCE ( SUM( 数量 ), 0 ) AS 赠送数量 FROM 销售表 WHERE 是否赠品 = 1 GROUP BY 产品名称 ) b ON t.产品名称 = b.产品名称;

7）
建立表如下：

输入代码如下：

//初始化1000000条数据
        List<Map<String,String>> data = createList(1000000);
        Long startTime = System.currentTimeMillis();
        String addSql = "insert into seven (first, second, third) VALUES(?,?,?)";
        jdbcTemplate.batchUpdate(addSql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, data.get(i).get("first"));
                ps.setString(2, data.get(i).get("second"));
                ps.setString(3, data.get(i).get("third"));
            }
            @Override
            public int getBatchSize() {
                return data.size();
            }
        });
        Long endTime = System.currentTimeMillis();
        System.out.println("插入"+data.size()+"条数据用时：" + (endTime - startTime) + "毫秒");
        System.out.println("插入"+data.size()+"条数据用时：" + (endTime - startTime) / 1000 + "秒");

实际耗时14.632s
8）





六、实验心得：

1. 初步了解mysql数据库；
2. 初步上手navicat软件；
3. 初步掌握图形化交互工具，学会数据库、基本表的创建、查询、导入；
4. 初步掌握SQL语言，能够使用语句创建、修改、选择和删除数据库、基本表；