本文主要内容为第一次在Windows环境下使用MYSQL数据库,包括MYSQL的安装、服务的启动,数据库的登陆,数据库的创建、查看、删除,表的创建、查询与删除。要求对数据库有一定的基础,比如已经熟悉了DB2数据库。
环境:
Windows 7 32 bit
MYSQL 5.7.18.1 社区版
1.)下载并安装MYSQL 5.7
安装MYSQL 5.7.18.1 社区版需要先下载并安装 Microsoft .NET Framework 4
http://www.microsoft.com/zh-cn/download/details.aspx?id=17718
下载MYSQL server
https://dev.mysql.com/downloads/windows/installer/5.7.html
下载之后的文件名为 mysql-installer-community-5.7.18.1.msi, 双击安装,默认的安装目录和安装完成之后数据库文件位置分别如下:
默认安装目录如下:
C:\Program Files\MySQL\MySQL Server 5.7\
默认安装目录下的bin目录存放的是可执行文件,安装完成之后,需要在Path环境变量里添加这个目录:
C:\Program Files\MySQL\MySQL Server 5.7\bin
数据库数据文件:
C:\Documents and Settings\All users\Application Data\MYSQL\MySQL Server 5.7
可以在这个目录里找到MYSQL的配置文件my.ini;还有一个目录名Data,是数据库文件存放目录,每创建一个数据库,这个Data目录里就会有一个以数据库名子命名的目录
2.)启动MYSQL服务
启动MYSQL服务:打开控制面板,按照下面的顺序找到MYSQL57,右键点击启动服务:
控制面板\所有控制面板项\管理工具\服务\MYSQL57
或者使用命令: net start mysql57
3.)登陆MYSQL数据库并创建数据库、查看数据库、删除数据库,查看MYSQL支持的存储引擎(存储引擎的概念以后会更新)
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.)在SAMPLE数据库里创建表、插入并查询数据、查看表的结构和定义、删除表
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.)使用不同引擎的表对应的文件
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)
在C:\Documents and Settings\All users\Application Data\MYSQL\MySQL Server 5.7\Data\sample会看到以下文件:
t2.frm
t2.ibd
t3.frm
t3.ibd
表t2和t3使用了默认的引擎InnoDB,其中frm文件存放的是表的定义,而ibd文件存放的是表的数据和索引数据
========
mysql> create table t4(id int) engine=MyISAM;
Query OK, 0 rows affected (0.17 sec)
可以看到sample目录下多了以下文件:
t4.frm
t4.MYD
t4.MYI
因为t4使用了另一个存储引擎MyISAM,表对应文件结构发生变化:frm存放的是表的定义;MYD是MYData的缩写,存放的是表数据;MYI是MYIndex缩写,存储的是索引。
=========
mysql> create table t5(id int) engine=MEMORY;
Query OK, 0 rows affected (0.14 sec)
这里t5又使用了一个新的存储引擎MEMORY,sample目录下只多了一个文件:t5.frm,来存放表的定义,并没有文件存放表的数据,因为使用这种存储引擎的表,其数据仅在内存中。