The main content of this paper is the first time to use MYSQL database in Windows environment, including MYSQL installation, service start, database landing, database creation, view, deletion, table creation, query and deletion. Requirements for a certain basis for the database, such as already familiar with DB2 database.

Environmental Science:
Windows 7 32 bit
MYSQL 5.7.18.1 Community Edition

1. Download and install MYSQL 5.7

To install MYSQL 5.7.18.1 Community Edition, you need to download and install Microsoft.NET Framework 4 first.
http://www.microsoft.com/zh-cn/download/details.aspx?id=17718

Download MYSQL server
https://dev.mysql.com/downloads/windows/installer/5.7.html

After downloading, the file name is mysql-installer-community-5.7.18.1.msi. Double-click installation. The default installation directory and the database file location after installation are as follows:

The default installation directory is as follows:
C:\Program Files\MySQL\MySQL Server 5.7\

The bin directory in the default installation directory stores executable files. After installation, you need to add this directory to the Path environment variable:
C:\Program Files\MySQL\MySQL Server 5.7\bin

Database data files:
C:\Documents and Settings\All users\Application Data\MYSQL\MySQL Server 5.7
MYSQL configuration file my.ini can be found in this directory; there is also a directory name Data, which is the database file storage directory. Every time a database is created, there will be a directory named after the database name in this data directory.


2. Start MYSQL service

Start MYSQL service: Open the control panel, find MYSQL57 in the following order, right-click start service:
Control PanelAll Control Panel ItemsManagement ToolsServicesMYSQL57

3. Log on to MYSQL database and create database, view database, delete database, and view the storage engine supported by MYSQL (the concept of storage engine will be updated later)

C:\Windows\system32>mysql -h 127.0.0.1 -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test1              |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database test1;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

4. Create tables, insert and query data, view the structure and definition of tables, delete tables in SAMPLE database

mysql> create database sample;
Query OK, 1 row affected (0.01 sec)

mysql> use sample;
Database changed

mysql> create table t1(id int primary key, name char(20), sex boolean);
Query OK, 0 rows affected (0.45 sec)

mysql> insert into t1 values(1,'miao',1);
Query OK, 1 row affected (0.07 sec)

mysql> insert into t1 values(2,'qing',0);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values(3,'qing',2);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | miao |    1 |
|  2 | qing |    0 |
|  3 | qing |    2 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> describe t1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | char(20)   | YES  |     | NULL    |       |
| sex   | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table t1;
+-------+-------------------------------------------------------------------
------------------+
| Table | Create Table
                  |
+-------+-------------------------------------------------------------------
------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------
------------------+
1 row in set (0.02 sec)

mysql> show create table T1 \G
*************************** 1. row ***************************
       Table: T1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(20) DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.19 sec)

5. Files corresponding to tables using different engines

mysql> create table t2(id int);
Query OK, 0 rows affected (0.31 sec)

mysql> create table t3(id int);
Query OK, 0 rows affected (0.28 sec)

In C: Documents and Settings All users Application Data MYSQL MySQL Server 5.7 Data sample, you will see the following files:

t2.frm
t2.ibd
t3.frm
t3.ibd

Tables t2 and t3 use the default engine InnoDB, where frm files store table definitions, while ibd files store table data and index data.

========

mysql> create table t4(id int) engine=MyISAM;
Query OK, 0 rows affected (0.17 sec)

You can see the following files in the sample directory:

t4.frm
t4.MYD
t4.MYI

Because t4 uses MyISAM, another storage engine, tables correspond to changes in file structure: frm stores the definition of tables; MYD is the abbreviation of MYData, which stores table data; MYI is the abbreviation of MYIndex, which stores indexes.
=========

mysql> create table t5(id int) engine=MEMORY;
Query OK, 0 rows affected (0.14 sec)

Here T5 uses a new storage engine MEMORY. There is only one more file in the sample directory: t5.frm to store the definition of the table. There is no file to store the data of the table, because the data of the table using this storage engine is only in memory.